Page 1 of 1

Default sort order for cursors in COBOL reading from DB2?

PostPosted: Wed Apr 18, 2018 2:56 pm
by Harsha_M
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?

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

PostPosted: Wed Apr 18, 2018 6:17 pm
by expat
Q1 - what happened when you tried it

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

PostPosted: Wed Apr 18, 2018 6:33 pm
by Harsha_M
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

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

PostPosted: Wed Apr 18, 2018 6:54 pm
by NicC
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.