0

I'm currently working on a system that uses COBOL to connect to DB2. A sample browse would be initiated by the following statement:

       EXEC SQL
         DECLARE <cursor name> CURSOR FOR
         SELECT
             <field names>
         FROM <table name>
         WHERE
             <conditions>
         ORDER BY
             <key fields>
         FOR FETCH ONLY
         OPTIMIZE FOR 1 ROW
       END-EXEC.

       EXEC SQL
            OPEN <cursor name>
       END-EXEC.

Once the browse has been determined to be successful, succeeding reads on the table would be made using the following:

       EXEC SQL
         FETCH <cursor name>
         INTO
             <variable names>
       END-EXEC.

If, for example, I'm browsing a table and the resultset returned is around 100,000 rows, that would take hours to process. This would be okay if I can ensure that other users of the system would not encounter deadlocks (-911) if they are processing on the same table that I am browsing (processing would mean selecting, updating and possibly deleting records).

How can I determine if the browse operation I am performing can potentially cause deadlocks for other users?

(NOTE: I'm not doing any updates, just purely retrieving data)

heisenbergman
  • 1,459
  • 4
  • 16
  • 33
  • You are running on a Mainframe. Why on Earth would you thing it it would take "hours" to process that? Try it on a test DB. Talk to your colleagues. Ask your DBA. In situations where there may be deadlocks, they will know how you should be dealing with it, and they can better demonstrate where there may be no deadlock possible. – Bill Woodger Oct 15 '14 at 08:54
  • In CICS, are the 100,000 rows to be processed in one transaction ? I would be worried about this being flagged as a long running transaction once it starts getting over a few seconds. Lock escalation can still occur in these cases. On the face of it, with UR or even CS you are more likely to be a victim. If this is an architecture task could it be done in tranches from interval control ? Or from batch ? Is there logic that can be performed in the Query ? Paging ? – mckenzm Mar 29 '15 at 17:13

4 Answers4

1

One tool to help find potential deadlock issues is the output from an EXPLAIN. Talk to your DBAs.

You say your result set may be 100,000 rows. Don't do that. No user is going to scroll through that many rows. Add additional selection criteria to allow them to filter the result set.

You are not going to maintain locks on your result set. One technique I've seen is to retrieve only enough data to display for the user to make their selection, then retrieve the rest when the selection is made.

cschneid
  • 10,237
  • 1
  • 28
  • 39
  • I think the asker is using "browse" in the CICS sense -- as in STARTBRowse, process set of data, ENDBRowse -- not as in a user browsing. This sounds like background processing, not screen related. – Joe Zitzelberger Nov 28 '14 at 17:47
0

In mainframe environment performance IS ALL! It's not because mainframes are fast we can ignore performance requirements.

In ONLINE program I recomemnd use 

FETCH FIRST N ROWS ONLY

where the user page size is N-1. If you successfully fetch the N row in you cursor there is more pages and you notify your user someway.

in your DB2 QUERIES;

If you're in a BATCH processing is better you UNLOAD your data using an DB2 Utility or DFSORT/ICETOOL/SYNCSORT passing your SQL query with the appropriate DD SORTDBIN.

Anderson Marques
  • 17
  • 3
  • Not quite "ALL" but certainly more important than where amount of data, and direct cost of processing (charging for cpu/io) is non-existent. I missed the `CICS` tag originally. If it is some type of "summation" process in a CICS program, the summation should be done outside of CICS so that the value is just handy to be used. 100,000 DB accesses for a single CICS transaction would not be good. Your first Upvote. – Bill Woodger Oct 16 '14 at 06:38
  • Oh,and SORTDBIN is a SyncSORT-only thing. – Bill Woodger Oct 16 '14 at 07:30
  • Doesn't taking the data out of the database to process it kinda defeat the purpose of having a database? – Joe Zitzelberger Nov 28 '14 at 17:43
  • @JoeZitzelberger By no means. It goes back afterwards.Although the DB command language can do all the stuff that needs to be done, at times it can't be done without unwarranted use of resources (including time). If unload/manipulate/reload gives the same results, but faster... It is a common Mainframe way. I'm not sure the question is so good, anyway. – Bill Woodger Nov 28 '14 at 18:51
0

If you are just doing a browse operation, the "FOR FETCH ONLY" (aka FOR READ ONLY) clause is very helpful. You might also want to look into the "SKIP LOCKED DATA" clause and and an isolation level of "WITH UR" (Uncommitted read) if the isolation level set on your package bind allows it.

All of that assumes that your business rules will allow you to only process dirty rows that other processes are not using at the moment.

If after doing all of that, you still see deadlocks, you might consider turning your curson into a declared temporary table and do your processing that way. That would guarantee that your data had no other readers or writers, at the expense of additional DASD and core resources.

Joe Zitzelberger
  • 4,238
  • 2
  • 28
  • 42
0

If the table contains 100,000 rows, most likely you will be "filtering" the FETCH to select for presentation. If at all possible include the "filtering" information in the SQL SELECT. The transaction statistics your DBA sees will determine if additional INDEX BY statements will make the transaction run better.

R. Whiteside
  • 11
  • 2