IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Reading DB2 table sequentially


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
cksatheesh

New User


Joined: 12 Oct 2005
Posts: 5

PostPosted: Mon May 28, 2007 12:00 pm
Reply with quote

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

Thanks
Satheesh
Back to top
View user's profile Send private message
SREELAKSHMINARAYANAN

New User


Joined: 27 Jul 2006
Posts: 7

PostPosted: Mon May 28, 2007 12:28 pm
Reply with quote

You can use a cursor to read it sequentially
Back to top
View user's profile Send private message
cksatheesh

New User


Joined: 12 Oct 2005
Posts: 5

PostPosted: Mon May 28, 2007 1:06 pm
Reply with quote

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

New User


Joined: 27 Jul 2006
Posts: 7

PostPosted: Mon May 28, 2007 2:37 pm
Reply with quote

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

Senior Member


Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India

PostPosted: Mon May 28, 2007 2:47 pm
Reply with quote

SREELAKSHMINARAYANAN,

Quote:
After processing all the records u can reset all the records to zero.


Does not this mean, processing the table more than once? First for reading the records and second for resetting the flag.
Back to top
View user's profile Send private message
SREELAKSHMINARAYANAN

New User


Joined: 27 Jul 2006
Posts: 7

PostPosted: Mon May 28, 2007 3:00 pm
Reply with quote

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

New User


Joined: 12 Oct 2005
Posts: 5

PostPosted: Mon May 28, 2007 3:23 pm
Reply with quote

I don't have the comfortability to add one new column to the table.. is there any way around..
Back to top
View user's profile Send private message
SREELAKSHMINARAYANAN

New User


Joined: 27 Jul 2006
Posts: 7

PostPosted: Mon May 28, 2007 3:31 pm
Reply with quote

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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon May 28, 2007 3:40 pm
Reply with quote

Quote:
well I can use that one.. incase of abend, how to restart from the last updatd record


are You planning to commit after every update ???

in case of abend all uncommitted changes will be rolled back, so no need to worry about restart.

Usually when planning restart there are a few thing to be taken into account..
the number of updates
the elapsed time of the update step
the complexity of rewriting the program with intricacies of checkpoint restart,
repositioning of all sequential datasets for example

the acceptable reprocessing time from the beginning is just a matter of service level agreement

Quite often the time savings are usually less than the trouble You are incurring into

regards
regards
Back to top
View user's profile Send private message
SREELAKSHMINARAYANAN

New User


Joined: 27 Jul 2006
Posts: 7

PostPosted: Mon May 28, 2007 3:53 pm
Reply with quote

Yes Exactly

We don't need worry if we give COMMIT TRANSACTION after Update
Back to top
View user's profile Send private message
cksatheesh

New User


Joined: 12 Oct 2005
Posts: 5

PostPosted: Mon May 28, 2007 4:28 pm
Reply with quote

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

Active User


Joined: 16 Sep 2004
Posts: 106
Location: Hyderabad

PostPosted: Tue May 29, 2007 12:00 pm
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top