Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Scrollable cursor in pseudo conventional programming (Cobol)

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
mabidin

New User


Joined: 18 Sep 2007
Posts: 5
Location: malaysia

PostPosted: Wed Sep 19, 2007 7:29 am    Post subject: Scrollable cursor in pseudo conventional programming (Cobol)
Reply with quote

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
Back to top
View user's profile Send private message

logeswarank
Warnings : 1

New User


Joined: 15 Oct 2006
Posts: 22
Location: Chennai

PostPosted: Wed Sep 19, 2007 10:16 am    Post subject: Scrollable cursor in pseudo conversation
Reply with quote

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.

Code:
SEARCH-PARA.

EXEC CICS SEND
               
                 MAP('SEARCH')
                 MAPSET('C00123')
END-EXEC.
EXEC CICS RECEIVE
                  MAP('SEARCH')
                  MAPSET('C00123')
END-EXEC.
EXEC SQL
          SELECT * FROM ACCOUNT
END-EXEC.
IF SQLCODE = 0 THEN
 
    move the database fields to MAP fields

ELSE
    GO TO SEARCH-PARA
END-IF.



If its wrong correct me.

[/code]
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 686
Location: Earth

PostPosted: Wed Sep 19, 2007 11:07 am    Post subject:
Reply with quote

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. icon_confused.gif
Back to top
View user's profile Send private message
logeswarank
Warnings : 1

New User


Joined: 15 Oct 2006
Posts: 22
Location: Chennai

PostPosted: Wed Sep 19, 2007 11:16 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 686
Location: Earth

PostPosted: Wed Sep 19, 2007 12:04 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
mabidin

New User


Joined: 18 Sep 2007
Posts: 5
Location: malaysia

PostPosted: Wed Sep 19, 2007 12:37 pm    Post subject:
Reply with quote

thanks for replying. this is all about DB2 cursor(scrollable). let's me quote this
Quote:
How to maintain the db2 cursor position for scrollable purposes (forward & backward) in pseudo conversational programming. As far as i understand the db2 cursor position will lost after "EXEC CICS RETURN" was issued.


let's say i've 1000 records and every page will display 10 records.
Previously (before use scrollable cursor DB2 v6), i store the first key/last key for every page at commarea to move forward & backward purposes.
for example if i want to move forward(PF8) i will read db2 with my last key, that means i need to read DB2 100 times if i want to browse my last 10th records. in pseudo you need to issue EXEC CICS RETURN to release the task in CICS.

From now on, i want to use the benefits of db2 scrollable cursor(DB2 v7) in pseudo conversational programming. [/quote]
Back to top
View user's profile Send private message
mabidin

New User


Joined: 18 Sep 2007
Posts: 5
Location: malaysia

PostPosted: Wed Sep 19, 2007 12:54 pm    Post subject:
Reply with quote

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 ??
Back to top
View user's profile Send private message
UmeySan

Active Member


Joined: 22 Aug 2006
Posts: 755
Location: Germany

PostPosted: Wed Sep 19, 2007 1:25 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Thu Sep 20, 2007 9:47 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
mabidin

New User


Joined: 18 Sep 2007
Posts: 5
Location: malaysia

PostPosted: Sun Sep 23, 2007 9:58 am    Post subject:
Reply with quote

@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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts XML Parse for COBOL 5.2 Bhanu Praveen COBOL Programming 1 Sat Jul 08, 2017 8:58 pm
No new posts Dynamic output file creation in cobol... smileheal COBOL Programming 7 Thu Jun 15, 2017 10:53 pm
No new posts Search utility(3.14) using JCl and Cobol RKS3 COBOL Programming 12 Sat Jun 10, 2017 10:24 pm
No new posts COBOL DB2 program - zIIP eligible vasanthz COBOL Programming 7 Wed May 31, 2017 5:12 am
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us