Scrollable cursor in pseudo conventional programming (Cobol)

mabidin · New User Joined: 18 Sep 2007 Posts: 5 Location: malaysia

Is there a technique to manage/control the cursor. Because as far as i know, when perform cics return we will lost the data. Using scrollabe to browse data forward & backward.

If u have a sample progams and want to share with me, i really2 appreciate it.

TQVM

logeswarank · New User Joined: 15 Oct 2006 Posts: 22 Location: Chennai

Hi Mabdin,

IF you are using DB2 as a backend means then you fetch the cursor on that corresponding record.

Below I mentioned the code to retrive the data from databse thru CICS.You lost the data when you are performing the CICS return.Dont pass the low values for that MAP or MAPSET.Write the code for FORWARD/BACKWARD movement.

agkshirsagar · Posted: Wed Sep 19, 2007 11:07 am

Mabidin,
Please explain your quetion clearly as it is really confusing. Please quote some example of what you really want to do so.

Logeswaran,
I don't see any 'cursor' involved in the program that you have posted.

logeswarank · New User Joined: 15 Oct 2006 Posts: 22 Location: Chennai

Hi Abhijit Kshirsagar ,

I didn't declared CURSOR.I told him to use cursor to retrive the records one by one.But I gave sample code to retrive the records in CICS. The code is correct na.

agkshirsagar · Posted: Wed Sep 19, 2007 12:04 pm

Logeswaran,
Ok lets assume you declared cursor somewhere, but still to fetch records one by one you need to use fetch command , not select *.
Second thing- we are not sure if OP is talking about DB2 cursor or Cursor on CICS screen.
We can position cursor by moving -1 in length field of map variable but I am not sure that if it is the same thing that OP has asked. So lets just wait for his response.

mabidin · New User Joined: 18 Sep 2007 Posts: 5 Location: malaysia

thanks for replying. this is all about DB2 cursor(scrollable). let's me quote this

mabidin · New User Joined: 18 Sep 2007 Posts: 5 Location: malaysia

because i can't edit my post, sorry .. let's me correct my explanation above. Previously i'm using DB2 V6 without scrollable features and now my company just update to V7.

Is there any sample progams with DB2 scrollable cursor that i could refer ??

UmeySan · Posted: Wed Sep 19, 2007 1:25 pm

Hi Mabidin !

You will need to be running at least DB2 V7 in order to accomplish this. A scrollable cursor provides the ability to scroll forward and backward through the data once the cursor is open. This can be achieved using nothing but SQL -- no host language code (e.g., COBOL, C) is required to facilitate a scrollable cursor in DB2 V7. A scrollable cursor makes navigating through SQL result sets much easier. There are two types of DB2 scrollable cursors:

SENSITIVE -- updateable; can access data changed by the user or other users

INSENSITIVE -- not updateable; will not show changes made

To use scrollable cursors you must use declared temporary tables, another new feature of DB2 Version 7.

Scrollable cursors allow developers to move through the results of a query in multiple ways. The following key words are supported when fetching data from a scrollable cursor:

NEXT -- will FETCH the next row, the same way that the pre-V7 FETCH statement functioned

PRIOR -- will FETCH the previous row

FIRST -- will FETCH the first row in the results set

LAST -- will FETCH the last row in the results set

CURRENT -- will re-FETCH the current row from the result set

BEFORE -- positions the cursor before the first row of the results set

AFTER -- positions the cursor after the last row of the results set

ABSOLUTE n -- will FETCH the row that is n rows away from the first row in the results set

RELATIVE n -- will FETCH the row that is n rows away from the last row fetched

For both ABSOLUTE and RELATIVE, the number n must be an integer. It can be either a positive or a negative number and it can be represented as a numeric constant or as a host variable.

All of the FETCH options for scrollable cursors also reposition the cursor before fetching the data.

Example:

DECLARE CUR1 SENSITIVE STATIC SCROLL CURSOR
FOR SELECT FIELD1, FIELD2
FROM TABLE01
ORDER BY FIELD2;

OPEN CUR1
FETCH LAST CUR1 INTO :X1, :X2

But as helpful as scrollable cursors are, do not make every cursor a scrollable cursor. Scrollable cursors require substantially more overhead than a traditional, non-scrollable cursor. Analyze the requirements of your applications and deploy scrollable cursors only where it makes sense to do so.

Regards & good luck
UmeySan

TG Murphy · Posted: Thu Sep 20, 2007 9:47 pm

2 Approaches

1. TS Queue

Retrieve X rows from DB2. Let's say that X is 100. Immediately write them to a TS queue. Page 1 shows the first 10 rows. When your user scrolls to page 2 you satisfy the request by going to the TS queue - you don't hit DB2 again.

Only when the user scrolls to page 11 to see rows 101 thru 110 do you need to go back out to DB2 to get the next 100 rows. Now you have 200 rows in your TS queue. All backward scrolling is satisfied via TS queue.

The DBAs love this approach because it reduces DB2 activity. There are potential "stale data" issues with this approach. But this is the say we do it here and it works fine.

2. Launch a cursor everytime the user scrolls. ORBER BY ASC when scrolling forward. ORBER BY DESC when scrolling backward. This may require the definition of an extra index that you wouldn't otherwise have needed to define.

I used this approach at another place I worked at. Worked well there too.

mabidin · New User Joined: 18 Sep 2007 Posts: 5 Location: malaysia

@TG Murphy .. thx for reply. it's interesting about TS Queue but i'm lack of knowledge about it. Coud u share with me a sample code, how to read & write