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
 
a question about DEAD LOCK WHEN OPEN CURSOR

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
haohao

New User


Joined: 23 May 2008
Posts: 35
Location: beijing China

PostPosted: Thu Jul 10, 2008 11:48 am    Post subject: a question about DEAD LOCK WHEN OPEN CURSOR
Reply with quote

HELLO EVERYBODY!
I have a question about OPEN CURSOR. THE Scenario is:

I have an application uses a DB2 table,just name it TB1. there are two online transactions running concurrently named TRA1 AND TRA2.
TRA1 inserts into TB1 and TRA2 read TB1 and update its records. TRA2 USES CURSOR TO get records from TB1(TRA2'S program is BIND with CS isolation level). Sometimes when TRA2 OPEN CURSOR,there is a sql code -913 returned, I am puzzled how can a deadlock occurd in this circumstance.
Can a insert operation also apply X-lock on a table?
Thanks in advance!!
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Jul 10, 2008 12:32 pm    Post subject:
Reply with quote

in batch you would not have this problem, because you could commit - thus freeing locks due to insert,update,fetch cursor. Yeah, the fetch cursor with read only, still will issue a lock (a very small one), but one that nevertheless will lock out x-locks from other tasks. In your case, you are locking in both tasks - insert and update.

but, your problem is that you are batch processing inserts and updates in an online environment which sorta precludes the use of intermediate commits.

why is it suggested not to commit in an online (cics) program? Because cics should not be used to perform batch operations.

what you could do is do a start task for each insert or update (make the cursor read only). thus each task would insert (or update) and then terminate, thus making a commit, thus releasing the locks.

I imagine your read only cursor would probably still issue enough locks to impede your other tasks. A way out of that would be drain the cursor into a TDQueue (recovery....) then process the que, starting tasks to perform the updates.
Back to top
View user's profile Send private message
haohao

New User


Joined: 23 May 2008
Posts: 35
Location: beijing China

PostPosted: Thu Jul 10, 2008 12:58 pm    Post subject: Reply to: a question about DEAD LOCK WHEN OPEN CURSOR
Reply with quote

further information about DECLARE CURSOR:

EXEC SQL
DECLARE D_SELJR01_PK CURSOR WITH HOLD FOR
SELECT
...... some columns
FROM TB1
WHERE
........SOME criterias
END-EXEC
Back to top
View user's profile Send private message
haohao

New User


Joined: 23 May 2008
Posts: 35
Location: beijing China

PostPosted: Thu Jul 10, 2008 1:20 pm    Post subject: Reply to: a question about DEAD LOCK WHEN OPEN CURSOR
Reply with quote

hi,dbzTHEdinosauer

in my case,the two transactions both are cics online task.
TRA2 fetches and updates TB1'S records one by one and commits one by one.
What I am wondering is the -913 is occured at OPEN CURSOR(with CS isolation level),at that time the tables records are not be FETCHed, could a X-LOCK be issued on the table or index at that time?
the whole process is a little complex, I wish I made me clear about it.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Jul 10, 2008 3:23 pm    Post subject:
Reply with quote

well, you could try WITH UR.

I would investigate the reason code that came with -913.
(you are of course issuing a CALL to DSNTIAR when you have a negative sql code).

The reason code explanation in addition to some analysis concerning what was actually going on in the machine at the time will be necessary for you to solve this problem.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jul 10, 2008 4:20 pm    Post subject:
Reply with quote

Code:


What I am wondering is the -913 is occured at OPEN CURSOR



The actual data for a cursor is retrieved from the table and made available for the fetch stmt when you issue the OPEN stmt ... so there is nothing wrong in receiving -913 at the time of issuing an OPEN ... as dick said you cud try with UR ...
Back to top
View user's profile Send private message
haohao

New User


Joined: 23 May 2008
Posts: 35
Location: beijing China

PostPosted: Fri Jul 11, 2008 11:29 am    Post subject: Reply to: a question about DEAD LOCK WHEN OPEN CURSOR
Reply with quote

HI DICK:
I issued DSNTIAR LIKE THAT (the code is copied from DB2 Developer's Guide):
Code:

           EXEC SQL
                OPEN D_SELJR01_PK
           END-EXEC

           IF SQLCODE IS LESS THAN ZERO
           CALL 'DSNTIAR' USING SQLCA,
                                ERROR-MESSAGE,
                                ERROR-TEXT-LENGTH.
           IF RETURN-CODE IS EQUAL TO ZERO
              PERFORM 9999-DISP-DSNTIAR-MSG
                  VARYING ERROR-INDEX FROM 1 BY 1
                  UNTIL ERROR-INDEX > 8
           ELSE
              DISPLAY 'DSNTIAR ERROR'.
              DISPLAY 'RETURN-CODE IS:' RETURN-CODE.
          ............

but from the OUTPUT message from the program ,it's seems the calling is failure:
the output message is:
20080711135454 SQLCODE IS
20080711135454 DSNTIAR ERROR
20080711135454 RETURN-CODE IS:00008

how can the calling be failure and how to resolve it? thanks a lot!!
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jul 11, 2008 7:52 pm    Post subject:
Reply with quote

Hello,

When posting code, it is best to use the "Code" tag to make your post more readable as well as preserve the alignment (your post has been "Code"ed).

Looking at your code, it appears to be incorrect. Review where you have periods. . . .
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Jul 11, 2008 9:41 pm    Post subject:
Reply with quote

what does your data layout look like?

error-message should be an s9(4) comp field containing the total length of the following area.

call using sqlca, db2-message-w40, DB2-LRECL-W40.
Code:

01  DB2-MESSAGE-W40.
    03  DB2-MESS-LENGTH-W40     PIC S9(04) USAGE COMP VALUE 720.
    03  DB2-MESS1-W40           PIC X(72).
    03  DB2-MESS2-W40           PIC X(72).
    03  DB2-MESS3-W40           PIC X(72).
    03  DB2-MESS4-W40           PIC X(72).
    03  DB2-MESS5-W40           PIC X(72).
    03  DB2-MESS6-W40           PIC X(72).
    03  DB2-MESS7-W40           PIC X(72).
    03  DB2-MESS8-W40           PIC X(72).
    03  DB2-MESS9-W40           PIC X(72).
    03  DB2-MESSA-W40           PIC X(72).

01 DB2-LRECL-W40            PIC S9(09) USAGE COMP VALUE 72.


does your DB2-MESS-LENGTH-W40 contain the total length (720 in my example)?
Back to top
View user's profile Send private message
haohao

New User


Joined: 23 May 2008
Posts: 35
Location: beijing China

PostPosted: Fri Jul 11, 2008 9:48 pm    Post subject:
Reply with quote

the data layout look like:

Code:

           77  ERROR-TEXT-LENGTH           PIC S9(9)   COMP VALUE +120.

           01  ERROR-MESSAGE.
               05  ERROR-MSG-LENGTH       PIC S9(9)   COMP VALUE +1200.
               05  ERROR-MSG-TEXT           PIC X(120)  OCCURS 10 TIMES
                                                        INDEXED BY ERROR-INDEX.


now ,the return code from DSNTIAR is 12.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Sat Jul 12, 2008 2:16 pm    Post subject:
Reply with quote

Code:

Possible return codes from DSNTIAR
Code Meaning
0     Successful execution.
4     More data was available than could fit into the provided message area.
8     The logical record length was not between 72 and 240, inclusive.
12    The message area was not large enough. The message length was 240 or greater.
16    Error in TSO message routine.
20    Module DSNTIA1 could not be loaded.
24    SQLCA data error
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Sat Jul 12, 2008 2:21 pm    Post subject:
Reply with quote

and change this:

05 ERROR-MSG-LENGTH PIC S9(9) COMP VALUE +1200

to this:

05 ERROR-MSG-LENGTH PIC S9(4) COMP VALUE +1200
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts question about dfsms management clas... Hervey Martinez IBM Tools 0 Tue Oct 10, 2017 6:19 pm
No new posts Reply MQ open error Vignesh Sid COBOL Programming 6 Thu Sep 07, 2017 6:02 pm
No new posts Question related to Data dictionary f... rexx77 SYNCSORT 3 Thu Aug 31, 2017 7:23 am
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm

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