Default sort order for cursors in COBOL reading from DB2?



IBM's flagship relational database management system

Default sort order for cursors in COBOL reading from DB2?

Postby Harsha_M » Wed Apr 18, 2018 2:56 pm

In a db2-Cobol program with restart logic, I have defined a Cursor to select & Update rows as below -


SELECT  COL_L, COL_M, COL_N, COL_O
FROM    TABLE_1
WHERE  COL_A = VALUE_1
    AND  COL_B = VALUE_2
FOR UPDATE OF
            COL_X,
            COL_Y
 


UPDATE CURSOR_NAME
    SET  COL_X          =  : VALUE_3
        , COL_Y          =  : VALUE_4
WHERE  CURRENT OF  CURSOR_NAME


Depending on the values of COL_L, COL_M, COL_N, COL_O, I’ll update certain values to COL_X and COL_Y

Note: ONLY COL_A & COL_B in WHERE clause are part of INDEX of TABLE_1


Question:
1. Since I didn’t use ORDER BY clause, will the program fetches the data in the same order in the two different runs (- given that there are no inserts or deletes between two runs)?
2. How does row positioned cursor works – On what basis 1st row is selected from the resultant table?
Harsha_M
 
Posts: 2
Joined: Wed Apr 18, 2018 2:22 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Default sort order for cursors in COBOL reading from DB2?

 

Re: Default sort order for cursors in COBOL reading from DB2

Postby expat » Wed Apr 18, 2018 6:17 pm

Q1 - what happened when you tried it
expat
 
Posts: 410
Joined: Sat Jun 09, 2007 3:21 pm
Has thanked: 0 time
Been thanked: 4 times

Re: Default sort order for cursors in COBOL reading from DB2

Postby Harsha_M » Wed Apr 18, 2018 6:33 pm

yes,I did a test run(s) with 100 records as input and got positive results. Data was processed in the same order.

But the final production run will be against 70 million rows, So I wanted to make sure it works in all cases and understand how row positioned cursor works (Pointing the 1st row and order).
For this I tried reffering IBM knowledge centre, where it was stated -

When your program issues a row-positioned FETCH statement, Db2 uses the cursor to point to a row in the result table, making it the current row. Db2 then moves the current row contents into the program host variables that you specified in the INTO clause of the FETCH statement.

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/intro/src/tpc/db2z_rowretrievalwithcursor.html
Harsha_M
 
Posts: 2
Joined: Wed Apr 18, 2018 2:22 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Default sort order for cursors in COBOL reading from DB2

Postby NicC » Wed Apr 18, 2018 6:54 pm

Since I didn’t use ORDER BY clause, will the program fetches the data in the same order in the two different runs (- given that there are no inserts or deletes between two runs)?

Not guaranteed. The database could have changed in other ways - reorganised, moved to different volume etc. The only guarantee is when you use the ORDER BY clause.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2782
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 114 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post