Page 2 of 2

Re: declare cursor in CICS and sqlcode = -904

PostPosted: Fri Nov 02, 2012 5:42 am
by c62ap90
-904 Unavailable resource. Possibly data/row is locked.
Usually a restart of your application will correct the problem.
What I do in my DB2 applications is execute the same code, for example,
3-times for -904, -911, and -913. Basically give your application 3-tries before abendinig.
It works something like this:
01  WS-COUNTS.
    05  WS-904-911-913-COUNT    PIC  9(02)  VALUE 0.
    05  WS-904-911-913-MAX      PIC  9(01)  VALUE 3.


01  WS-SQLCODE                  PIC S9(03)          VALUE ZERO.
    88  SQL-UNAVAILABLE-RESOURCE                    VALUE -904.
    88  SQL-ROLLBACK-DEADLOCK-TIMEOUT               VALUE -911.
    88  SQL-DEADLOCK-TIMEOUT                        VALUE -913.
    88  SQL-904-911-913                             VALUE -904
                                                          -911
                                                          -913.
    MOVE ZERO   TO WS-904-911-913-COUNT
    PERFORM 850-DECLARE-CURSOR
       THRU 859-EXIT

...

*----------------------------------
  850-DECLARE-CURSOR.
 *---------------------------------

      EXEC  SQL
          DECLARE CUR1 INSENSITIVE SCROLL CURSOR
           FOR SELECT GOODID,INDATE,INPIECE
           FROM STSTORAGE
           WHERE GOODID = :COMM-GOID
      END-EXEC

      MOVE SQLCODE        TO WS-SQLCODE

 *--- ALLOW FOR  WS-904-911-913-MAX  OCCURANCES (IE 3)
 *--- OF -904, -911, -913.
      IF  SQL-904-911-913
          ADD 1   TO WS-904-911-913-COUNT
          IF  WS-904-911-913-COUNT    <  WS-904-911-913-MAX
              GO TO 850-DECLARE-CURSOR
          END-IF
      END-IF
     IF  SQL-SUCCESSFUL
    CONTINUE
 ...
*------------
 859-EXIT.
*------------
     EXIT.

Re: declare cursor in CICS and sqlcode = -904

PostPosted: Fri Nov 02, 2012 6:08 am
by ChenTao
premkrishnan wrote:What I would say is try to identify the database and tablespace of your table STSTORAGE you can get

So to get that you need to write a query to SYSIBM.SYSTABLES to get DBNAME and TSNAME of your table

So query will be

SELECT DBNAME , TSNAME FROM SYSIBM.SYSTABLES WHERE NAME ='STSTORAGE'


After that you get the name of your table's Database and Tablespace

With that value

Substitute in the job I gave earlier and execute it.

and let me know the output you get

hers is the dbname and tsname:\
---------+---------+---------+---------+---------+---------
   SELECT DBNAME,TSNAME FROM SYSIBM.SYSTABLES             
   WHERE  NAME = 'STSTORAGE';                             
---------+---------+---------+---------+---------+---------
DBNAME    TSNAME                                           
---------+---------+---------+---------+---------+---------
DSNDB04   STSTORAG                                         
DSNE610I NUMBER OF ROWS DISPLAYED IS 1     

here is the result of the first command :
 DSNT360I  @ ***********************************                       
 DSNT361I  @ *  DISPLAY DATABASE SUMMARY                               
            *    GLOBAL                                                 
 DSNT360I  @ ***********************************                       
 DSNT362I  @     DATABASE = DSNDB04  STATUS = RW                       
                DBD LENGTH = 92864                                     
 DSNT397I  @                                                           
 NAME     TYPE PART STATUS             PHYERRLO PHYERRHI CATALOG  PIECE
 -------- ---- ---- ------------------ -------- -------- -------- -----
 STSTORAG TS        RW                                                 
 ******* DISPLAY OF DATABASE DSNDB04  ENDED      **********************
 DSN9022I  @ DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION             
 *** 

Re: declare cursor in CICS and sqlcode = -904

PostPosted: Fri Nov 02, 2012 9:15 am
by ChenTao
c62ap90 wrote:-904 Unavailable resource. Possibly data/row is locked.
Usually a restart of your application will correct the problem.
What I do in my DB2 applications is execute the same code, for example,
3-times for -904, -911, and -913. Basically give your application 3-tries before abendinig.
It works something like this:
01  WS-COUNTS.
    05  WS-904-911-913-COUNT    PIC  9(02)  VALUE 0.
    05  WS-904-911-913-MAX      PIC  9(01)  VALUE 3.


01  WS-SQLCODE                  PIC S9(03)          VALUE ZERO.
    88  SQL-UNAVAILABLE-RESOURCE                    VALUE -904.
    88  SQL-ROLLBACK-DEADLOCK-TIMEOUT               VALUE -911.
    88  SQL-DEADLOCK-TIMEOUT                        VALUE -913.
    88  SQL-904-911-913                             VALUE -904
                                                          -911
                                                          -913.
    MOVE ZERO   TO WS-904-911-913-COUNT
    PERFORM 850-DECLARE-CURSOR
       THRU 859-EXIT

...

*----------------------------------
  850-DECLARE-CURSOR.
 *---------------------------------

      EXEC  SQL
          DECLARE CUR1 INSENSITIVE SCROLL CURSOR
           FOR SELECT GOODID,INDATE,INPIECE
           FROM STSTORAGE
           WHERE GOODID = :COMM-GOID
      END-EXEC

      MOVE SQLCODE        TO WS-SQLCODE

 *--- ALLOW FOR  WS-904-911-913-MAX  OCCURANCES (IE 3)
 *--- OF -904, -911, -913.
      IF  SQL-904-911-913
          ADD 1   TO WS-904-911-913-COUNT
          IF  WS-904-911-913-COUNT    <  WS-904-911-913-MAX
              GO TO 850-DECLARE-CURSOR
          END-IF
      END-IF
     IF  SQL-SUCCESSFUL
    CONTINUE
 ...
*------------
 859-EXIT.
*------------
     EXIT.

it's my own program and the cursor have never been successfully declared :( thank you all the same,and your code will be helpful in the future.

Re: declare cursor in CICS and sqlcode = -904

PostPosted: Fri Nov 02, 2012 10:37 am
by Pandora-Box
You mean you got that problem since your cursor not declared properly?

Re: declare cursor in CICS and sqlcode = -904

PostPosted: Fri Nov 02, 2012 1:07 pm
by ChenTao
premkrishnan wrote:You mean you got that problem since your cursor not declared properly?

i have to say yes.i wanted to realize scrollable screen, and first thought is that i can use
fetch absolute or fetch relative ,but when i compile my program i found that i can't use these
sentence in my program, i can only use before,after,first,current,next etc. than i think that if the cursor that i defined can keep usable until next time i come to the transation ,that is return transid(), but i'm not sure, so i want to have a try ,but i was stuck in the definition of the cursor.
today i found that i can use temprary storage to implement the function and just two minites ago i make the program run successfully, but i used none scrollable cursor and this time i come across no problem.
but why i can't define a scrollable cursor i still don't know..