Reading DB2 table sequentially

cksatheesh · New User Joined: 12 Oct 2005 Posts: 5

Could any one throw some light on reading a db2 table sequentially..

Thanks
Satheesh

SREELAKSHMINARAYANAN · New User Joined: 27 Jul 2006 Posts: 7

You can use a cursor to read it sequentially

cksatheesh · New User Joined: 12 Oct 2005 Posts: 5

well I can use that one.. incase of abend, how to restart from the last updatd record. do I have to skip the records one by one until the key values match with the key values of the last updated record at the time of abending..

Thanks
Satheesh

SREELAKSHMINARAYANAN · New User Joined: 27 Jul 2006 Posts: 7

You can have some flag in the table to indicate that what are record processed or not .After processing all the records u can reset all the records to zero.

murmohk1 · Posted: Mon May 28, 2007 2:47 pm

SREELAKSHMINARAYANAN,

SREELAKSHMINARAYANAN · New User Joined: 27 Jul 2006 Posts: 7

No I mean to say in cursor declaration it self that processed indicator can also be a part of condition

suppose u have table A u can have one more column called processed indicator with default value 0 .While declaring cursor
this can also be a part of condition

like this

declare cursor h_cursor
for select * from table A where processed_indicator=0

after processing the processing the record
u can update the processed_indicator with 1

so that if any abend happens the read record won't be processed

cksatheesh · New User Joined: 12 Oct 2005 Posts: 5

I don't have the comfortability to add one new column to the table.. is there any way around..

SREELAKSHMINARAYANAN · New User Joined: 27 Jul 2006 Posts: 7

that would be better option

U can try this option alose

U can have VSAM With Key as Primary Key of your table

after reading each record in the cursor

u can read that VSAM through that primary key

if the file status is 23 u can proceed and u have insert one record with primary key in VSAM

if the file status is 00 then it should be processed (This indicates file is already processed)

enrico-sorichetti · Posted: Mon May 28, 2007 3:40 pm

SREELAKSHMINARAYANAN · New User Joined: 27 Jul 2006 Posts: 7

Yes Exactly

We don't need worry if we give COMMIT TRANSACTION after Update

cksatheesh · New User Joined: 12 Oct 2005 Posts: 5

If I want to do with the table only, I only have the option of skipping the records sequentially that are not matching with the key values of the last abended record.. and start processing from where it matches.. am I right..

Thanks
Satheesh

ashwinreddy · Posted: Tue May 29, 2007 12:00 pm

Hi,

You can try the checkpoint restart logic. Check the following link

ibmmainframes.com/viewtopic.php?t=20544&highlight=

And above solution of updating for each column, i don't think its an optimised solution. If you have 1 million records then you are hitting the database 1 million times which no DBA will approves.

Cheers
Ashwin