|
View previous topic :: View next topic
|
| Author |
Message |
maheshk84
New User
.jpg)
Joined: 04 Jan 2006
Posts: 22
Location: Chennai
|
|
|
|
Hi,
I am changing a COBOL program.
There is a cursor which reads the whole table and processes it.
Current cursor used:
1)move low-values to host-timestamp
2) cursor -->
select * from table
where timestamp > host-timestamp
order by timestamp;
3) process the rows.
I need to change the program so that it reads the last 40 rows
order by timestamp.
One of the methods that I ve followed.
1) introduce a new cursor - order by timestamp desc
2) loop 40 times and get the timestamp of the last iteration --> host1-timestamp
3) move host1-timestamp to host-timestamp
4) execute the old cursor.
When I do this, the cursor starts from the beginning and not
the start point(timestamp) that I ve created.
Please let me know if I am not clear. |
|
| Back to top |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Low values would be an invalid value for a timestamp! |
|
| Back to top |
|
|
maheshk84
New User
Joined: 04 Jan 2006
Posts: 22
Location: Chennai
|
|
|
|
HI Craq,
They have hard coded the low values as below.
01 H-TIMESTAMP PIC X(26).
01 WS-LOW-TIMESTAMP PIC X(26)
VALUE '0001-01-01-00.00.00.000000'.
MOVE WS-LOW-TIMESTAMP TO H-TIMESTAMP. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
1. if you are going to ask professionals questions about 'their' topic (mainframe), use their jargon.
LOW-VALUES is hex'00' for whatever length required.
your WS-LOW-TIMESTAMP is just the 'earliest' timestamp acceptable to db2.
how about ORDER BY <timestamp column> DESC
optimize for 40 rows?
since you have no idea how 'old' the 40th Timestamp is, your stuck with complete cursor, which is dumb. The must be a way to determine where the cut-off is.
now, I think this is a student project, because I can not see a business reason to select the 'oldest' 40 timestamps. |
|
| Back to top |
|
|
maheshk84
New User
Joined: 04 Jan 2006
Posts: 22
Location: Chennai
|
|
|
|
Hi,
This is not a student project. There IS a business reason behind it. One of the programs in prod is erring as the array is blown. The business doesnot agree either to delete the rows or increase the array size. So I had chosen this one to proceed with the problem. Please let me know if you know anyother way.
When you say "optimize for 40 rows", Is that DB2 SQL command or a general term you are mentioning. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
3) move host1-timestamp to host-timestamp
4) execute the old cursor. |
I'd suggest you display the host-timestamp after 3).
Is there any possibility thet the "old" initial value is being moved back into the host-timestamp before the "old cursor" is executed? |
|
| Back to top |
|
|
maheshk84
New User
Joined: 04 Jan 2006
Posts: 22
Location: Chennai
|
|
|
|
Dick,
Thanks for your suggestion. When I put the displays, all showed the correct values. But for some reason, the cursor did not start from the checkpoint rather it started from the first.
I found that the cursor was already open. This was the reason behind this error. So whatever values moved to the host variables were not picked up. Now, I have just shifted the new lines before opening the cursor. It works now. It's my mistake. Sorry to bother you all. Cheers.  |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
maheshk84,
1. most mistakes are minor, things we overlook or take for granted. I have not found many 'deep technical' problems in a quite a while. Most is violation of syntax or 'poor' logic.
2. thx for getting back with the answer.
3. you did not bother us. you needed some help, we supplied what help we could, you responded by solving your own problem. can not ask for more. |
|
| Back to top |
|
|
|
|
