DSNREXX - failure in EXECSQL

Steve Coalbran · #1 07-23-2010, 12:25 PM

I have an exec that I am working on to list the format of any table.
Here is the essence of it. I'll hide the elaborate call of SQL and it's error trapping...

Code:

/* REXX(DB2DESC)*******************************************************/
/* Desc    : Give struture of the DB2 tables                          */
/* Syntax  : TSO DB2DESC ssid tbcreator.tbname                        */
/**********************************************************************/
TRACE "C"                                                               
CALL MSG "ON"                                                           
ARG parms                                                               
parms = "DB40 SYSIBM.SYSPLANDEP"                                        
-  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  8 Line(s) not Displayed 
/*-------------------------------------------------------------------*/ 
/* processing parameters                                             */ 
/*-------------------------------------------------------------------*/ 
SAY LEFT("*************** DB2DESC ",79,"*")                             
-  -  -  -  -  -  -  -  -  -  -  -  -  -  -  - 11 Line(s) not Displayed 
/*-------------------------------------------------------------------*/ 
/* establish addressability to DSNREXX                               */ 
/*-------------------------------------------------------------------*/ 
ADDRESS TSO "SUBCOM DSNREXX"                                            
IF( RC <> 0 )THEN                                                       
   RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')                             
IF( RC <> 0 )THEN                                                       
   CALL ABEXIT 8,"SUBCOM DSNREXX not available RC="RC                   
ADDRESS DSNREXX                                                         
                                                                        
/*-------------------------------------------------------------------*/ 
/* build query and cursor                                            */ 
/*-------------------------------------------------------------------*/
qry = "SELECT NAME,COLTYPE,LENGTH,SCALE,NULLS,COLNO",                  
      "FROM SYSIBM.SYSCOLUMNS",                                        
      "WHERE TBNAME = '"tbname"'",                                     
      "AND TBCREATOR = '"tbcreator"'",                                 
      "ORDER BY COLNO"                                                 
CALL SQL "EXECSQL DECLARE C1 CURSOR FOR S1"                            
CALL SQL "EXECSQL PREPARE S1 FROM :QRY"                                
CALL SQL "EXECSQL OPEN C1"                                             
. . .

This is the TRACE C output...

Code:

*************** DB2DESC *******************************************************
*                                                                             *
* STARTING    : 2010/07/23 15:07                                              *
* SSID        : DB40                                                          *
* TBCREATOR   : SYSIBM                                                        *
* TBNAME      : SYSPLANDEP                                                    *
*                                                                             *
*******************************************************************************
                                                                               
    35 *-* ADDRESS TSO "SUBCOM DSNREXX"                                        
       >>>   "SUBCOM DSNREXX"                                                  
   114 *-*  ADDRESS DSNREXX STRIP(sqlstmt,"T")                                 
       >>>    "EXECSQL DECLARE C1 CURSOR FOR S1"                               
       *-*  ADDRESS DSNREXX STRIP(sqlstmt,"T")                                 
       >>>    "EXECSQL PREPARE S1 FROM :QRY"                                   
       +++ RC(-1) +++                                                          
*************** STATEMENT ERROR ***********************************************
*                                                                             *
* STATEMENT   = EXECSQL PREPARE S1 FROM :QRY                                  *
* VARIABLES:-                                                                 *
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *
* QRY         = SELECT NAME,COLTYPE,LENGTH,SCALE,NULLS,COLNO FROM             *
*               SYSIBM.SYSCOLUMNS WHERE TBNAME = 'SYSPLANDEP' AND TBCREATOR   *
*               = 'SYSIBM' ORDER BY COLNO                                     *
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *
* RC          = -1                                                            *
*                                                                             *
*************** SQLCA INFORMATION *********************************************
*                                                                             *
* SQLCODE     = -981                                                          *
* SQLSTATE    = 57015                                                         *
* SQLERRMC    = 00C12219:                                                     *
* SQLERRP     = DSNARA00                                                      *
* SQLERRD(1)  = 0                                                             *
*        (4)  = -1                                                            *
* SQLWARN(0)  =                                                               *
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *
* TERMINATED  : 2010/07/23 15:07                                              *
*                                                                             *
*******************************************************************************

From the SQL Codes lookup I get...
-981 THE SQL STATEMENT FAILED BECAUSE THE RRSAF CONNECTION IS NOT IN A STATE THAT ALLOWS SQL OPERATIONS, REASON reason-code.
Explanation: The application attempted to execute an
SQL operation, but the RRSAF connection was not in a
state that allows the processing of SQL statements.
System action: The statement cannot be executed.
Programmer response: The reason code is described in
Codes. Correct the error in the application, REBIND,
and run the application again.
SQLSTATE: 57015

The same query actually works fine in DSNTEP2...

Code:

PAGE    1                                                                       
***INPUT STATEMENT:                                                             
   SELECT NAME,COLTYPE,LENGTH,SCALE,NULLS,COLNO FROM SYSIBM.SYSCOLUMNS          
   WHERE TBNAME = 'SYSPLANDEP' AND TBCREATOR = 'SYSIBM' ORDER BY COLNO          
       +------------------------------------------------------------------------
       !                                                           NAME         
       +------------------------------------------------------------------------
     1_! BNAME                                                                  
     2_! BCREATOR                                                               
     3_! BTYPE                                                                  
     4_! DNAME                                                                  
     5_! IBMREQD                                                                
       +------------------------------------------------------------------------
PAGE    2                                                                       
                                        ----------------------------------------
                                        ! COLTYPE  !  LENGTH   !   SCALE   ! NUL
                                        ----------------------------------------
                                      1_! VARCHAR  !       128 !         0 ! N  
                                      2_! VARCHAR  !       128 !         0 ! N  
                                      3_! CHAR     !         1 !         0 ! N  
                                      4_! VARCHAR  !        24 !         0 ! N  
                                      5_! CHAR     !         1 !         0 ! N  
                                        ----------------------------------------
SUCCESSFUL RETRIEVAL OF          5 ROW(S)                                       
PAGE    1

Any ideas guys? I wondered if I need to compile the exec?

Steve

Michael Simpson · #2 07-23-2010, 12:31 PM

Your DSNTEP2 example.

I wrote a couple of execs to take the results from DSNTEP2 and format them as portrait or landscape results. I got sick of trying to "read" the results and keeping the "wrapped" lines in perspective.

dbzthedinosaur · #3 07-23-2010, 12:53 PM

Steve,
whatever module you are using to establish a db2 connection,
is attempting an RRSAF connect. and failing.

I have never seen a REXX script accessing db2 require a connection via RRS.

a DSNLR.. instead of a DSNLI.. module is being invoked. other than that, I know nothing....

you can modifiy your sql to SELECT substring(name,1,18) as name
and all the rest of the columns. LENGTH AS LEN, etc... and 'squeeze the return & column headings into a 121 format. Unless of course, you have
somany column in your result set that you will wrap, anyway.

petwir · #4 07-23-2010, 12:54 PM

I wrote a REXX here to display the layout of a DB2 table along with RI relationships, indexes, and triggers. It has some site-specific code in it so you would have to deal with ripping that out yourself, but I'll share all of the code with anyone that asks for it. Send an email to "petwir @ saif {dot} com", and put "SFDESC" in the subject line.

In reviewing my REXX code, I noticed it also requires the use of some homegrown software for allocating and de-allocating the DB2 libraries that I don't have access to the source code for. So for my REXX code to be of value to you, you would need to be able to resolve this issue on your own as well.

Here is a sample output;

Code:

 TABLE: PDB2.P11.INCUR_COST_HIST       ASL.P1.DCLGEN(CLIICH@)
  DESC: <none available>

 COLUMN_NAME        COLUMN_TYPE NULL LENGTH SCALE RI_TO_TABLE        INDEX      DEFAULT_VALUE   VALUES
 ================== =========== ==== ====== ===== ================== ========== =============== ==============================
 CLM_ID             DECIMAL     NO    15    0     CLI_INJ_CLM        CLIXICH1
                                                                     CLIXICH0-U
 CLM_COST_TS        TIMESTMP    NO    10    0                        CLIXICH0-U
 RSRV_APPR_ID       CHAR        NO    8     0
 RSRV_INITR_ID      CHAR        NO    8     0
 AUTO_ADJ_RSN_CD    CHAR        NO    2     0     CLM_AUTO_ADJ_RSN
 UPDT_TS            TIMESTMP    NO    10    0                        CLIXICH1
 UPDR_ID            CHAR        NO    8     0
 GEN_ANALYS_TXT1    VARCHAR     NO    200   0
 GEN_ANALYS_TXT2    VARCHAR     NO    200   0
 ORACLE_KEY_SEQ_NUM DECIMAL     NO    15    0                        CLIXICH2-U

 TRIGGERS
 ========
 NAME=CLIICH_D TIME=A EVENT=D
 NAME=CLIICH_I TIME=A EVENT=I
 NAME=CLIICH_U TIME=A EVENT=U

RonB · #5 07-23-2010, 01:03 PM

Don't see it, but did you issue a "CONNECT" to the DB2 Subsystem before trying to run SQL?

Steve Coalbran · #6 07-23-2010, 01:08 PM

Hej Michael,

I really tried the DSNTEP2 only as a double-check to make sure that the SQL did not have any stupidly obvious flaws.
I haven't done any real DB2 development since I worked on RETAIN reports in UK in 1999 (using RXSQL), although I did some data warehouse export work similar to this on a short project about 5 years ago for Ericsson here in Sweden.

I'm keen to get a pukka DSNREXX solution to see if I have made some stupid error using this API, perhaps in initialization of variables, but I cannot see anything.

Steve

petwir · #7 07-23-2010, 01:15 PM

You are failing on the PREPARE, right?

Here is what the PREPARE code looks like in my application. Its a little different than yours;

Code:

Address DSNREXX "EXECSQL PREPARE  S1 INTO :OUTSQLDA  FROM :SQLSTMT"

Steve Coalbran · #8 07-23-2010, 01:32 PM

Thanks Pete,
I do my "INTO" bit after on the "FETCH" ...if I can get that far!

I'm pretty sure that I have done this, just like this before.

Code:

...
CALL SQL "EXECSQL OPEN C1"                                             
DO WHILE(sqlcode=0)                                                    
   "EXECSQL FETCH C1 INTO :NAM :COL :LEN :SCA :NUL"                    
   ...
END                                                                    
CALL SQL "EXECSQL CLOSE C1"

Steve

petwir · #9 07-23-2010, 01:39 PM

Ah, apples and oranges. My FETCH is coded like so;

Code:

Address DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"

I think the reason for the different forms, is that my cursor handling logic is inside a generic REXX subroutine that doesn't know what columns it needs to handle. So by using OUTSQLDA, it dynamically handles whatever columns you choose to select.

Since we've put all of our DB2 access stuff into a subroutine, our REXX applications don't have to deal with any DSNREXX instructions at all. They simply pass the SQL statement to the subroutine and get the results back.

petwir · #10 07-23-2010, 01:42 PM

I wonder if your state-problem is resolved by logging off and back on? It could be that a prior test left a connection open and when you try again, it collides with the prior connection? (just guessing)

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode