Calling a stored procedure from REXX

[Adam5]

Hello,

I've developed a stored procedure in COBOL, and have tested it with the IBM stored procedure builder. It works fine.

Now I want to call the stored procedure from a REXX-script. Whenever I try to call it I'm getting an SQLCODE -084 (UNACCEPTABLE SQL STATEMENT).

The example from DB2 Programmer Guide doesn't fit to my STP, because my STP does not have a result set. It has only input- & output-parameters.

Here is the defintion of my STP:

CREATE PROCEDURE  MYSCHEMA.MYSTP                
       ( IN    DATEINAM           VARCHAR(1024)
        ,IN    ABTIMEST           TIMESTAMP    
        ,OUT   VORHANDE           CHARACTER(4) 
        ,OUT   ISTTIMES           TIMESTAMP    
        ,OUT   USERID             CHARACTER(8) 
        ,OUT   UEGRUN             VARCHAR(156) 
        )                                      
       RESULT SET 0         
       LANGUAGE            COBOL  
       PARAMETER STYLE     DB2SQL 
       NOT DETERMINISTIC          
       NULL CALL                  
       FENCED                     
       MODIFIES SQL DATA          
       NO DBINFO                  
       COLLID              MYCOLLID
       WLM ENVIRONMENT     DB0TWLM
       ASUTIME LIMIT       244500 
       STAY RESIDENT NO           
       PROGRAM TYPE MAIN          
       SECURITY            DB2    
       COMMIT ON RETURN NO        
       INHERIT SPECIAL REGISTERS

How can I call this STP from REXX?

Thank's

Adam

 

[MarcLodge]

Adam,

What platform are you calling the stored procedure from, and could you post your rexx for us to have a look at.

Marc

 

[Adam5]

Hello,

the stored procedure run's under the WLM of DB2 V8. My REXX-environment is z/OS 1.6.

First I tryed following:

filenam = './pub/viwas/extra.zip'    
abtime = '2005-05-25-14.34.00.493273'
 SQLQUERY = "CALL MYSCHEMA.MYSTP(file, "!!,      
                               "abtime, "!!,    
                               "vorhanden, "!!, 
                               "isttime , "!!,  
                               "buserid  , "!!, 
                               "uegrund )"      
     "REXXDB2 SYSNAME DB0T SQLQUERY ";         
        SAY   '_VERSION:' _version;            
    IF _SQLCODE < 0                            
      THEN DO /* SQL-Fehler */                 
        SAY   '_SQLCODE:' _sqlcode;            
        SAY   '_SQLTEXT:' _sqltext;            
      END                                      
      ELSE do                                  
        say 'vorhanden:' vorhanden             
        say 'isttime:' isttime                 
        say 'buserid:' buserid                 
        say 'uegrunf:' uegrund                 
                                               
        SAY 'Result:' _NONAME1.1               
      end                                      
 exit

REXXDB2 is an DB2-interface written by our sysprogrammers.
The upper example produces following SQL-Error:

DSNT408I SQLCODE = -084, ERROR:  UNACCEPTABLE SQL STATEMENT 
DSNT418I SQLSTATE   = 42612 SQLSTATE RETURN CODE            
DSNT415I SQLERRP    = DSNHAPLY SQL PROCEDURE DETECTING ERROR

I've also tryed this code, but it doesn't work:

    SQLQUERY = "SET ABTIME = '2005-05-25-14.34.00.493273'; ",         
               "SET FILENAM = './pub/viwas/extra.zip'; ",             
               "SET ind1   = 0; ",                                    
               "SET ind2   = 0; ",                                    
               "CALL MYSCHEMA.MYSTP (:FILENAM :ind1, :ABTIME :ind2,"!!,
 ":VORHANDE :ind3, :ISTTIME :ind4, :BUSERID :ind5, :UEGRUND :ind6)"

The upper sqlquery return following result:

DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "ABTIME". SOME SYMBOLS THAT
         MIGHT BE LEGAL ARE: :                                      
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                    
DSNT415I SQLERRP    = DSNHSM5R SQL PROCEDURE DETECTING ERROR

My idea is, that a stored procedure can be called with pure SQL. Therefore I've tryed to write an dynamic SQL-CALL in SPUFI or Platinum Interactive SQL. But I'm getting allways in SQLCODE -084 or -104. Perhaps this idea is false.

Thanks

Adam

 

[MarcLodge]

Adam,
I have found the App prog ref guide here: (

http://publib.boulder.ibm.com/infoc...m.ibm.db2.doc.pdf/dsnapj13.pdf?noframes=true)

and page 240 onwards describes in detail how to embed SQL in a REXX program. The examples they give do not look anything like your rexx and I am wondering if you have coded it correctly. I would have expected something along the following lines:

ADDRESS DSNREXX "EXECSQL ASSOCIATE LOCATOR (:LOC1) WITH PROCEDURE :PROC"
IF SQLCODE = 0 THEN CALL SQLCA
SQLSTMT = "ALLOCATE C150 CURSOR FOR RESULT SET ?"
ADDRESS DSNREXX "EXECSQL ALLOCATE C150 CURSOR FOR RESULT SET :LOC1"
IF SQLCODE = 0 THEN CALL SQLCA
DO UNTIL(SQLCODE = 0)
  ADDRESS DSNREXX "EXECSQL FETCH C150 INTO :SEQNO, :TEXT"
  IF SQLCODE = 0 THEN
    DO
      SAY TEXT
    END
END
IF SQLCODE < 0 THEN CALL SQLCA
ADDRESS DSNREXX "EXECSQL CLOSE C150"
IF SQLCODE = 0 THEN CALL SQLCA
RETURN
SQLCA:
TRACE O
SAY 'SQLCODE ='SQLCODE
SAY 'SQLERRM ='SQLERRMC
SAY 'SQLERRP ='SQLERRP
SAY 'SQLERRD ='SQLERRD.1',',
            || SQLERRD.2',',
            || SQLERRD.3',',
            || SQLERRD.4',',
            || SQLERRD.5',',
            || SQLERRD.6
SAY 'SQLWARN ='SQLWARN.0',',
            || SQLWARN.1',',
            || SQLWARN.2',',
            || SQLWARN.3',',
            || SQLWARN.4',',
            || SQLWARN.5',',
            || SQLWARN.6',',
            || SQLWARN.7',',
            || SQLWARN.8',',
            || SQLWARN.9',',
            || SQLWARN.10
SAY 'SQLSTATE='SQLSTATE
EXIT

This is taken from chapter 12.6 of the book here:

http://www.redbooks.ibm.com/redbooks/SG247083/wwhelp/wwhimpl/js/html/wwhelp.htm

Hope some of this helps.

Marc

 

[Adam5]

Hello Marc,

I've found the example in the Redbook, but I'm wondering, because there is no CALL-Statement. It opens a cursor, and fetches the results out of the result set made by the SP. The example below does not fit with my SP, because it does not return a result set.

I want to have an example of calling a stored procedure from REXX only with input- & output-parms without a result set.

Ok, now I've tryed to call my SP with the IBM-DSNREXX-Interface. Here is my code:

/* REXX DB2 with IBM */                                                 
ADDRESS TSO                                                             
"SUBCOM DSNREXX" /* host command environment available? */              
IF rc <> 0 THEN DO                                                      
  rc = RXSUBCOM('ADD','DSNREXX','DSNREXX')                              
  IF rc <> 0 THEN DO /* command environment could not be set */         
    SAY "RXSUBCOM RC:" rc                                               
    EXIT 8 /* fatal error */                                            
  END                                                                   
END                                                                     
                                                                        
ADDRESS DSNREXX                                                         
ssid = 'DB0T' /* DB2 subsystem */                                       
"CONNECT" ssid                                                          
                                                                        
sqlstmt = "CALL MYSCHEMA.MYSTP(?,?,?,?,?,?)"                                   
"EXECSQL PREPARE s1 FROM :sqlstmt"                                      
TestSQL()                                                               
                                                                        
dateinam = './pub/uerz/unterverzeichnis_a/testdatei_a.txt'              
abtimest = '2004-03-15-11.15.13.000094'                                 
vorhande = ''                                                           
isttimes = ''                                                           
buserid  = ''                                                           
uegrun   = ''                                                           
sqlstmt = "EXECUTE s1 USING :dateinam,:abtimest,:vorhande,:isttimes,:buserid,",
":uegrun"                                                               
"EXECSQL" sqlstmt                                                       
TestSQL()                                                               
                                                                        
                                                                        
"DISCONNECT"                                                            
rc = RXSUBCOM('DELETE','DSNREXX','DSNREXX')                             
                                                                        
                                                                        
EXIT 0                                                        
            
TestSQL:                                                                
  IF rc >= 0 THEN RETURN 0                                              
  SAY 'RC:'rc                                                           
  nline = SIGL-1                                                        
  SAY "DSNREXX error at statement" nline                                
  SAY SOURCELINE(nline)m                                                
/*SAY 'SQLCODE:' sqlcode                                                
  SAY 'SQLERRM:' sqlerrmc*/                                             
  SAY 'SQLCODE ='SQLCODE                                                
  SAY 'SQLERRM ='SQLERRMC                                               
  SAY 'SQLERRP ='SQLERRP                                                
  SAY 'SQLERRD ='SQLERRD.1',',                                          
              !! SQLERRD.2',',                                          
              !! SQLERRD.3',',                                          
              !! SQLERRD.4',',                                          
              !! SQLERRD.5',',                                          
              !! SQLERRD.6                                              
  SAY 'SQLWARN ='SQLWARN.0',',                                          
              !! SQLWARN.1',',                                          
              !! SQLWARN.2',',                                          
              !! SQLWARN.3',',
              !! SQLWARN.4',',
              !! SQLWARN.5',',
              !! SQLWARN.6',',
              !! SQLWARN.7',',
              !! SQLWARN.8',',
              !! SQLWARN.9',',
              !! SQLWARN.10   
  SAY 'SQLSTATE='SQLSTATE     
  EXIT 8

I'm getting again SQLCODE -84 ((

What made I wrong?

Thanks

Adam

 

[MarcLodge]

Adam,
Your REXX to my eye looks good. What I would therefore do is to replace the CALL statement with a SELECT * FROM SYSIBM.SYSDUMMY1

If this works it will mean that the problem lies with the CALL statement (it may be the interface doesn't allow it or it is coded incorrectly).

If it does not work, it points to an error in the REXX.

Let us know how you get on.

Marc

 

[Adam5]

Hello,

my coding from above works fine with "normal SQL-statemtents" like SELECT, INSERT or UPDATE. Only when I want to call a SP with CALL I'm getting SQLCODE -084 at the prepare-statement: "EXECSQL PREPARE s1 FROM :sqlstmt"

Maybe I should make a post in the REXX-board :-(

I'm wondering if somebody has realy made it to call a SP from REXX???

cry :-(

Adam

 

[MarcLodge]

Adam,
You've probably seen this, but if you haven't, it gies and example of calling a DB2 stored procedure from REXX.

http://publib.boulder.ibm.com/infoc...p?topic=/com.ibm.db2.doc.apsg/bjnqmstr474.htm

Can you adapt your rexx to this format?

Marc

 

[Adam5]

Hello,

now I've got my COBOL-SP to be called from my REXX-script, and it works! I had to initialize my REXX-variables and use indicator-variables. The hint above was quite good

Here ist my REXX-script:

/*REXX*/                                                                        

'SUBCOM DSNREXX'                                                                
IF RC THEN                                                                      
   S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')                                   
/*                                                                */            
ADDRESS DSNREXX "CONNECT" "DB0T"                                                
IF SQLCODE <> "0" THEN                                                          
   DO                                                                           
    SAY "FAILURE TO CONNECT TO DATABASE"                                        
    EXIT 8                                                                      
   END                                                                          
ADDRESS DSNREXX                                                                 
/* Initialize all input- and output variables.                    */            
dateinam = './pub/uerz/unterverzeichnis_a/testdatei_a.txt'                      
dateinam_ind = 0                                                                
abtimest = '2004-03-15-11.15.13.000094'                                         
abtimest_ind = 0                                                                
vorhande = LEFT(' ',4)                                                          
vorhande_ind = -1                                                               
isttimes = left(' ',26)                                                         
isttimes_ind = -1                                                               
buserid  = left(' ',8)                                                          
buserid_ind  = -1                                                               
uegrun   = left(' ',156)                                                        
uegrun_ind   = -1                                                               
/*                                                                */            
/* NOW CALL THE STORED PROCEDURE.                                 */            
/*                                                                */            
"EXECSQL CALL MYSCHEMA.MYSTP(:DATEINAM :dateinam_ind,",                         
                            ":ABTIMEST :abtimest_ind,",                         
                            ":VORHANDE :vorhande_ind,",                         
                            ":ISTTIMES :isttimes_ind,",                         
                            ":BUSERID :buserid_ind,",                           
                            ":UEGRUN :uegrun_ind)"                              
SAY "SQL CODE IS " SQLCODE                                                      
IF SQLCODE <> "0" THEN                                                          
  DO                                                                            
    SAY "CALL FAILED WITH SQLCODE = " SQLCODE                                   
    SAY "SQLERRM IS " SQLERRMC                                                  
    SAY "SQLSTATE IS" SQLSTATE                                                  
    SAY SQLWARN.0                                                               
    SAY "SQLERRD IS " SQLERRD.1 "," SQLERRD.2 "," SQLERRD.3,                    
        "," SQLERRD.4 "," SQLERRD.5 "," SQLERRD.6                               
    EXIT 8                                                                      
   END                                                                          
SAY " vorhande :" vorhande                                                      
SAY " isttimes :" isttimes                                                      
SAY " buserid  :" buserid                                                       
SAY " uegrun   :" uegrun                                                        
ADDRESS DSNREXX "DISCONNECT"                                                    
EXIT 0

Thank's

Adam