Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to fetch the records in table from a particular position

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sandeep k

New User


Joined: 24 Sep 2008
Posts: 4
Location: pune

PostPosted: Sat May 30, 2009 9:12 pm    Post subject: How to fetch the records in table from a particular position
Reply with quote

Is there any command in DB2 like we use START and STARTBR commands in COBOL and CICS respectively for reading the records sequentially from a particular position?
I think, we need it whenever we deal the scrolling concept in DB2 + CICS program.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Sat May 30, 2009 10:16 pm    Post subject:
Reply with quote

look up DECLARE CURSOR or just CURSOR in the application programming guide
vsn 7 appl pgm guide

vsn 8 appl pgm guide
Back to top
View user's profile Send private message
sandeep k

New User


Joined: 24 Sep 2008
Posts: 4
Location: pune

PostPosted: Sat May 30, 2009 11:25 pm    Post subject: Reply to: How to fetch the records in table from a particula
Reply with quote

Thanks for your reply...

I went thru the links as you suggested but I am still in confusion because whenever we use the cursor to fetch the records then it fetches the record from initial. Let's take an example, consider a table as mentioned below

Emp_id(key) Emp_Name Emp_Detail
23 ---- ----
24 ---- ----
15 ---- ----
12 ---- ----
28 ---- ----

If We read the records from 3rd postion and we know the primary key value 15. How we can implement it in program using cursor. Please let me know.
Back to top
View user's profile Send private message
ashishsr123

New User


Joined: 06 May 2008
Posts: 33
Location: Chennai

PostPosted: Sat May 30, 2009 11:47 pm    Post subject:
Reply with quote

read about scrollable cursor ,you will get your answers
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Sat May 30, 2009 11:58 pm    Post subject:
Reply with quote

sandeep k,
Quote:
If We read the records from 3rd postion and we know the primary key value 15
AFAIK, whatever you say is TRUE if it was a "file". If you're handling with "databases", you cannot ensure that the 3rd row from your sql output always belongs to key-15.

What's that you are trying to achieve here?
Back to top
View user's profile Send private message
sandeep k

New User


Joined: 24 Sep 2008
Posts: 4
Location: pune

PostPosted: Sun May 31, 2009 12:59 am    Post subject: Reply to: How to fetch the records in table from a particula
Reply with quote

Actually I am trying to achieve, how to code the scrolling concept in CICS + DB2 Program. Suppose a table has 10 records from 1,2…10 and we press any PF key then first four records (1st ,2,3,4) are displayed in the map. Again press the same key , records (4th ,5,6,7) are displayed on screen. We repeat this process till end of record.

I know that how to code it in case of VSAM database using STARTBR option. But in case of DB2 database, my problem is that after first four read, how to positioned the cursor on forth record for fetching the records 4th, 5, 6, 7. I think, now you got my question.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Sun May 31, 2009 1:22 am    Post subject:
Reply with quote

Just as your VSAM STARTBR for the second (page down or up) invocation, you save a key (key for top of page item, key for bottom of page item). PF7 (up) you have the key at top of page,
OPEN a cursor,
order desc,
where key is > column values.
First fetch is bottom of page, next fetch is second from bottom., etc...

PF8 (down) you open a cursor,
order asc,
where key < column values
First fetch is top of page, next fetch is second from top, etc...

2 cursor declarations, you can use FETCH 10 (map size) ROWS ONLY.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Sun May 31, 2009 9:40 am    Post subject:
Reply with quote

Hi Dick,

Do the "<" and ">" need a swap above?. Or is it that I misunderstood everything. icon_rolleyes.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Sun May 31, 2009 1:41 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
for bottom of page item). PF7 (up) you have the key at top of page,
OPEN a cursor,
order desc,
where key is > column values.
First fetch is bottom of page, next fetch is second from bottom., etc...

PF8 (down) you open a cursor,
order asc,
where key < column values
First fetch is top of page, next fetch is second from top, etc...

I guess it is hard to read, not db2 standard, but accurate.
I should have phrased the WHERE properly - COLUMN on left side of predicate.

PF7: WHERE COLUMN < :HOST-VARIABLE
PF8: WHERE COLUMN > :HOST-VARIABLE
both cases, the HOST-VARIABLE (key) is saved (dfhcommarea) top and bottom values from previous screen.

I should also have indicated using ROWSET retrieval (ROWSET size being equal to the map size).
Back to top
View user's profile Send private message
sandeep k

New User


Joined: 24 Sep 2008
Posts: 4
Location: pune

PostPosted: Sun May 31, 2009 1:43 pm    Post subject: Reply to: How to fetch the records in table from a particula
Reply with quote

If we sort the key in descending or ascending order then we cannot fetch the records in the order as they are placed in db2 table. I feel this can be achieved by using ABSOLUTE and RELATIVE options in fetch.

1. Define the cursor with scroll option.
2. Open the cursor as usual.
3. Fetch the cursor using ABSOLUE n & RELATIVE n options. n is the record position from first and last record respectively.
Use Absolute for forward scrolling,
and use RELATIVE for backward scrolling.

We can code like that
For first invoction.
0001-Fetch-First-Para.
Move 1 to N.
Perform varying I from 1 by 1 until I>4
Compute N = N-I
Exec sql
Fetch Absolute n into host-variables….
End-exec.
If sql-code = 0
Move host-variable value to map out put field.
End-perform.
Store value of n in DFH-N DFHCOMAREA variable
Perform send-map-para.

For second invocation
0002-scroll-DOWN-PARA.
Move DFH-N to N
Compute N = N -1
Perform varying I from 1 by 1 until I>4
Compute N = N + I
Exec sql
Fetch Absolute n into host-variables….
End-exec.
If sql-code = 0
Move host-variable value to map out put field.
End-perform.
Store value of n in DFH-N DFHCOMAREA variable
Perform send-map-Para.


Other things we also need to take care like end of record in table, record not found during invocation. Similarly we can code for scrolling up.

Please let me know, whether I am going towards right direction or not.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun May 31, 2009 10:45 pm    Post subject:
Reply with quote

Hello,

Sounds like the wrong direction. . .

Quote:
If we sort the key in descending or ascending order then we cannot fetch the records in the order as they are placed in db2 table.
What business reason exists to retrieve rows based on their "order in the table".

Transaction screens typically scroll based on some WHERE and ORDER BY criteria.

A properly written transaction takes into account not finding a hit, scrolling past the end, or scrolling before the beginning.
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 21 Sun Aug 27, 2017 10:35 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us