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
 
Reading DB2 table sequentially

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Reading DB2 table sequentially
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    Post subject: Re: Reading DB2 table sequentially
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    Post subject:
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    Post subject: Re: Reading DB2 table sequentially
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: 1439
Location: Bangalore,India

PostPosted: Mon May 28, 2007 2:47 pm    Post subject:
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    Post subject: Re: Reading DB2 table sequentially
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    Post subject:
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    Post subject: Re: Reading DB2 table sequentially
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10308
Location: italy

PostPosted: Mon May 28, 2007 3:40 pm    Post subject: Re: Reading DB2 table sequentially
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    Post subject: Re: Reading DB2 table sequentially
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    Post subject:
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    Post subject:
Reply with quote

Hi,

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

http://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    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 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 Adding a userid to SMTP Security table Yolanda Harvey JCL & VSAM 1 Sun Aug 13, 2017 6:16 pm
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm

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