|
View previous topic :: View next topic
|
| Author |
Message |
sandeep k
New User
Joined: 24 Sep 2008
Posts: 4
Location: pune
|
|
|
|
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 |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
| Back to top |
|
|
sandeep k
New User
Joined: 24 Sep 2008
Posts: 4
Location: pune
|
|
|
|
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 |
|
|
ashishsr123
New User
Joined: 06 May 2008
Posts: 33
Location: Chennai
|
|
|
|
| read about scrollable cursor ,you will get your answers |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
sandeep k
New User
Joined: 24 Sep 2008
Posts: 4
Location: pune
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Hi Dick,
Do the "<" and ">" need a swap above?. Or is it that I misunderstood everything.  |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
sandeep k
New User
Joined: 24 Sep 2008
Posts: 4
Location: pune
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
|
|
