CICS DB2 Cursor

[Prakash_Ramnani]

Situation: Current processing is to display data from VSAM to screen.
Issue: We have to replace VSAM with DB2. When the screen is displayed, all cursors are closed hence we are not able to use scrollable cursors and CURSOR WITH HOLD. Is there a way to hold the data and display on the screen page by page. The number of rows fetched might be around 50K or more.

[dhiraj]

Situation: Current processing is to display data from VSAM to screen.
Issue: We have to replace VSAM with DB2. When the screen is displayed, all cursors are closed hence we are not able to use scrollable cursors and CURSOR WITH HOLD. Is there a way to hold the data and display on the screen page by page. The number of rows fetched might be around 50K or more.

Please answer:

1)- Are you using page up-down because you can not show 50K rows at a time(I didn't face it)??

2)- How many rows you want to show at one time on screen?

and what is the meaning of "Hold the data"??


If your requirement is only page up-down then you need to fetch particular rows at a time i.e. same what you want to show at a time.

Example : if you want 18 rows at a time then mention this in program cursor as
FETCH FIRST 18 ROWS ONLY
also for next page, keep last record and fetch next 18 records.

[dbzTHEdinosauer]

replacing vsam logic with db2 is actually rather easy.

1. using a multi-row fetch, you reduce the CALLs necessary for i/o.
e.g. if you have 15 rows on your screen,
in vsam you need a start and 15 read nexts
in db2 you have an open cursor and 1 (ONE) fetch.

2. always keep the keys for the top row and the bottom row from the previous display.
code 2 cursors. one for page up and one for page down.
the page up cursor would be a less than with and desc order by,
the page down would be a greater than with asc order by.

use a fetch for #rows for your screen + 1.
and use the optimize clause for the same number.
db2 9 and 10 (probably 8 also) is smart enough to know from the cursor declarations
(remember, use two)
that you only want the next or the preceding block of rows
and will create such a path.
(the extra is so that you know if there is another page,
so that you can inhibit the pf7 or pf8 when you display)

remember the page up array will be populated in reverse of your screen display.
the number of returned rows will be contained in sqlerrcd(3).
if the sqlcode for the fetch is 100, 0 or 349? (I forget the actual sqlcode, you will find it in the
multi-row fetch discussion in the manual.)
handle as ok and process based on the count in sqlerrcd(3)
any other sqlcode handle as an error.

remember to save the sqslcode after the fetch and then do an immediate close cursor.
then interrogate the saved sqlcode (as discussed above) and process the array that db2 populated
with the fetch as a normal cobol table (which it is)

host variables for a multi row fetch are contained in an 'array'.
the same data area is refered to as a 'table' during your manipulation with cobol code.
array is not a cobol data item, it is a db2 host variable.
just terms and their appropriate usage.

[NicC]

Thanks, Dick. We don't do SQL on the mainframe here but some handy insights/tips should I ever have to change employers in the next couple of years!