output records are duplicating while restarting a cursor



IBM's flagship relational database management system

output records are duplicating while restarting a cursor

Postby loke_mainframe » Tue Apr 24, 2012 9:14 pm

Hi All,
This is regarding restarting a Cursor in a batch environment(Cobol).

We have a set of cursors used for data processing. We are using the cursors to fetch the data from the table and store in a local varaible.
we donot use any update or insert in cursors.

problem: When the cursor is getting an Sqlcode -911(deadlock) all the opened cursors are closing and the job is abending with Sqlcode -501.
To avoid this we have written a restart logic like
WHEN SQLCODE = -501
MOVE DFC-SQL-RETRY TO DFC-SAVE-RETRY
ADD DFC-Z50040-READ TO DFC-Z50040-SKIP
PERFORM Z50040-SETUP-LCCNSEL-CURSOR
SET PF-END-OF-LCCNSEL-NOT TO TRUE
MOVE DFC-SAVE-RETRY TO DFC-SQL-RETRY

but this restart was creating duplicate records in the file.
the cursor structure is as follows:
Cursor1(fetch a part number) --> Cursor 2 (get order details of the part) --> Cursor 3(get time details)--> Cursor 4.... etc.

Cursor 1 will be closed only after all eligible parts are processed.

if in the middle the cursor is closed the cursor 1 fetching the starting part which resulting the duplicating of output records.

Can some one please help me to avoid this duplication while restarting a cursor.

i look forward for your response.
loke_mainframe
 
Posts: 2
Joined: Sat Apr 21, 2012 5:24 pm
Has thanked: 0 time
Been thanked: 0 time

Re: output records are duplicating while restarting a cursor

Postby dick scherrer » Tue Apr 24, 2012 9:37 pm

Hello and welcome to the forum,

Why bother with "restarting" the cursor? When a task deadlocks, completely restart the process re-creating any output that was partially created during the run. All sequential files (including reports) have to be restarted anyway to prevent duplication.

Another thought is that processes that are properly designed will not cause a deadlock - but this is a different matter.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: output records are duplicating while restarting a cursor

Postby loke_mainframe » Tue Apr 24, 2012 10:34 pm

Hello Dick,
Actually batch time this program was abending several times with Sqlcode -501. To avoid the job abend we have done these changes.
Now, we moved the code to production and after moving we faced this duplication problem.

now our goal is to stop the batch abends as well as the duplication.

Please let me know if i am not clear.
loke_mainframe
 
Posts: 2
Joined: Sat Apr 21, 2012 5:24 pm
Has thanked: 0 time
Been thanked: 0 time

Re: output records are duplicating while restarting a cursor

Postby NicC » Tue Apr 24, 2012 10:36 pm

I am not clear as to why the duplicate problem was not picked up in testing - I presume it WAS tested!
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: output records are duplicating while restarting a cursor

Postby dick scherrer » Tue Apr 24, 2012 10:40 pm

Hello,

To avoid the job abend we have done these changes.
I believe this "solution" will not work. . .

Suggest you study WHY the deadlocks and prevent them.

One way is to change the sequence of how locks are acquired. Another might be by scheduling these jobs so they do not conflict and cause the deadlock.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post