restart logic that starts the updation from the 201th record

t1nt1n · New User Joined: 08 Jul 2005 Posts: 31

Hello,

I hv one qtn. can i get the answer for it pls.

I hv an application that performs only updates. Say it updates 1000 rows of data, after updating say 200 records my program abends. I would like to have an restart logic that starts the updation from the 201th record.

i hope ive posted this in the right forum..if not kindly bear with me.

Thanks in advance..

prasad_yadav20 · New User Joined: 12 Mar 2005 Posts: 13 Location: hyderabad

hi friend..
this restart control can be done by ,using absolute key word with SCROLLABLE key word in cursor declaration...

t1nt1n · New User Joined: 08 Jul 2005 Posts: 31

Thx..

but if we do not know how many records were updated before abend ??? is there any other method ??

somasundaran_k · Active User Joined: 03 Jun 2003 Posts: 134

Check this link.

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/2.2.2?DT=20010710165542

Regds
-Som

vijayamadhuri · Active User Joined: 06 Apr 2005 Posts: 180

HOPE THIS HELPS

To make a cursor scrollable, you declare it as scrollable. To use a scrollable cursor, you execute FETCH statements that indicate where you want to position the cursor. If you want to order the rows of the cursor's result set, and you also want the cursor to be updatable, you need to declare the cursor as scrollable, even if you use it only to retrieve rows sequentially. You can use the ORDER BY clause in the declaration of an updatable cursor only if you declare the cursor as scrollable. Declaring a scrollable cursor: To indicate that a cursor is scrollable, you declare it with the SCROLL keyword. The following examples show a characteristic of
scrollable cursors: the sensitivity.

EXEC SQL DECLARE C1 INSENSITIVE SCROLL CURSOR FOR
SELECT DEPTNO, DEPTNAME, MGRNO
FROM DSN8710.DEPT
ORDER BY DEPTNO
END-EXEC.

Declaring a scrollable cursor with the INSENSITIVE keyword has the following effects:
The size, the order of the rows, and the values for each row of the result table do not change after you open the cursor.
The result table is read-only. Therefore, you cannot declare the cursor with the FOR UPDATE clause, and you cannot use the cursor for positioned update or delete operations.

EXEC SQL DECLARE C2 SENSITIVE STATIC SCROLL CURSOR FOR
SELECT DEPTNO, DEPTNAME, MGRNO
FROM DSN8710.DEPT
ORDER BY DEPTNO
END-EXEC.

Declaring a cursor as SENSITIVE has the following effects:
When you execute positioned UPDATE and DELETE statements with the cursor, those updates are visible in the result table.
When the current value of a row no longer satisfies the SELECT statement for the cursor, that row is no longer visible in the result table.
When a row of the result table is deleted from the underlying table, the row is no longer visible in the result table. Changes that are made to the underlying table by other cursors or other application processes can be visible in the result table, depending on whether the FETCH statements that you use with the cursor are FETCH INSENSITIVE or FETCH SENSITIVE statements.

ovreddy · Posted: Fri Aug 05, 2005 1:01 pm

Hi Friends,

Restart Logic in DB2 is more important in huge Databases like DB2. I don't know how many of us are using this logic in our programs. But I think it is important to know something about it as a Mainframe programmer. As I know some tools like SMART/RESTART are available for RESTART logic but we can implement it on our own as follows. If you find time please go through this mail and let me know if you have more information on this.

See the attached document for Restart Logic.

Thanks,
Reddy.

badal · New User Joined: 05 Sep 2004 Posts: 21

Hi somsundaram,
i have got u r link,it was very nice. i thought i could have downloaded that full book.but i couldn't . can u tell me what is the procedure for IBM manuals to download?
it will be a great help if u tell me how to download all the books of IBM Sites.
thanks a lot.
badal.

anv2005 · New User Joined: 14 Jul 2005 Posts: 44 Location: US

Your RESTART document is very impressive.

A few questions:

1. How expensive would it be to reposition a single file during RESTART for a few million records -- read; insert into table; write back?

2. How would you recover the randomly accessed VSAM files?

3. Smart/RESTART offers automatic recovery for many ABEND and SQL conditions, e.g. S0C7 and resource unavailable conditions. It is a handy feature would you agree?

Best regards,
Alex.

anadhamohan · New User Joined: 27 Sep 2005 Posts: 25 Location: india

I hope there is a way to do this within the application programs itself.
Hope that you know the check table, ie., we can have a table which used to store the rows that has updated still now.
That table should store only the Primary key value of the main table.
If abend happens at the 200th position so it'll update the check table also with the prime value of main table till that 200th position.
Next time you can do the checking for that check table to know how many records are updated..this can be done only using cursors and you should commit the updated records after it insert's value into the check table..

Impossible is often Untried!!!