IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

rexx callin stored procedure !!


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
mjadwani2785

New User


Joined: 28 Apr 2009
Posts: 89
Location: Noida , Dublin

PostPosted: Fri Feb 04, 2011 6:31 pm
Reply with quote

Hi,

In my environment I have bound this DSNREXX plan .. but when i am tryin to invoke a stored procedure ADMIN_INFO_SYSPARM it returns an sqlcode of -805 with error mssg of

DB2V..DSNREXX.1847603F1E2C8829:DSNREXX:03

Manual says concistency token is not valid as I have run this query

SELECT COLLID, NAME, HEX(CONTOKEN), VERSION
FROM DB2V.SYSIBM.SYSPACKAGE
WHERE NAME = 'DSNREXX'
AND HEX(CONTOKEN) = '1847603F1E2C8829';

no rows are returned

.. just wanted to know what should be my next plan of action ?
Back to top
View user's profile Send private message
mjadwani2785

New User


Joined: 28 Apr 2009
Posts: 89
Location: Noida , Dublin

PostPosted: Thu Feb 17, 2011 10:54 pm
Reply with quote

Hi,

the above problem solved .. but when the invoking of the code is done it give sqlcode = -471 .

I refreshed the wlm applenv environment. But still the problem persist.

Inputs needed what can be the problem my code as follows:

Code:

 /*rexx  */                                                 
 /*invoke stored procedure to display zparms */             
 trace i                                                   
DB2_SUBSYS='DB2V'                                           
                                                           
/*"tsolib activate dataset('BYS1.DSND00A.SDSNLOAD') " */   
/* address ispexec                                         
"LIBDEF ISPLLIB DATASET ID('SYS1.DSNDB2V.SDSNLOAD')" */     
                                                           
s_rc = 0                                                   
      ADDRESS TSO                                           
"SUBCOM DSNREXX"                                           
                                                           
if rc<>0 then                                               
 S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')                 
                                                           
ADDRESS DSNREXX                                             
                                                     
       
"CONNECT" DB2_SUBSYS                                       
 If rc <> 0 Then Do                                   
        Say 'Cannot connect to ' DB2_subsys ' RC=' rc
        Exit(23)                                     
     End                                             
                                                     
                                                     
   SSID     = LEFT(' ',4)                             
   SSID_IND = -1                                     
RETCD=0                                               
retcd_ind=0                                           
MSSG=LEFT(' ',1331)                                   
mssg_ind=0                                           
/* MSSG=LEFT(' ',4) */                               
/* ENC='EBCDIC' ;                                     
ADDRESS DSNREXX                                       
'EXECSQL SET CURRENT PACKAGESET=:ENC '               
if (sqlcode<>0) then                                 
call sql_error */                                     

ADDRESS DSNREXX                                                       
 'EXECSQL CALL SYSPROC.ADMIN_INFO_SYSPARM(:SSID :SSID_IND',           
                                   ',:RETCD :retcd_ind',               
                                   ',:MSSG :mssg_ind)' /*:mssg_ind)' */
if (sqlcode<>0) then                                                   
call sql_error                                                         
                                                                       
       ADDRESS DSNREXX                                                 
          "DISCONNECT"                                                 
           S_Rc = RXSUBCOM('DELETE','DSNREXX','DSNREXX')               
 exit                                                                 
                                                                       
 sql_error:                                                           
                                                                       
 say sqlcode                                                           
 say sqlstate                                                         
 SAY SQLERRMC                                                         
 SAY SQLERRP                                                           

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 'SQLERRD  = 'SQLERRD.1',' || SQLERRD.2',' || SQLERRD.3',' ||,
                 SQLERRD.4',' || SQLERRD.5',' || SQLERRD.6       
exit (sqlcode)


the code tags... please
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Fri Feb 18, 2011 12:23 am
Reply with quote

What was the Reason Code?
Back to top
View user's profile Send private message
mjadwani2785

New User


Joined: 28 Apr 2009
Posts: 89
Location: Noida , Dublin

PostPosted: Fri Feb 18, 2011 1:04 am
Reply with quote

sqlcode= -471
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Fri Feb 18, 2011 1:06 am
Reply with quote

Hello,

Now, re-read the request and post the reason code. . .
Back to top
View user's profile Send private message
mjadwani2785

New User


Joined: 28 Apr 2009
Posts: 89
Location: Noida , Dublin

PostPosted: Fri Feb 18, 2011 1:09 am
Reply with quote

reason code SYSPROC.ADMIN_INFO_SYSPARM 00E7900C
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Fri Feb 18, 2011 1:28 am
Reply with quote

What do you see when you look up reason code 00E7900C ?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Fri Feb 18, 2011 1:29 am
Reply with quote

Hello,

Read here:
publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnmcj10/4.22.60

At the top of the page is a link to "IBM Manuals". Suggest you practice using this to research problems as you will have nearly instant turnaround on this type of queston rather than waiting on someone to see your topic and respond. You probably should either bookmark the Table of Contents for the manuals you use regularly or download them to a local drive so they can be reached even when there is a network/internet outage.
Back to top
View user's profile Send private message
mjadwani2785

New User


Joined: 28 Apr 2009
Posts: 89
Location: Noida , Dublin

PostPosted: Fri Feb 18, 2011 1:51 am
Reply with quote

db2vwlm environment is active in wlm.

I refreshed it and resume the wlm but still the problem is dere... what to do?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Fri Feb 18, 2011 1:57 am
Reply with quote

publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.codes/00e7900c.htm
Quote:
The WLM application environment name specified for the user-written routine is not defined or is currently not available for DB2® use in the active WLM policy.


I still tend to trust more the db2 judgement than Yours!
what about getting Your support involved ?
Back to top
View user's profile Send private message
mjadwani2785

New User


Joined: 28 Apr 2009
Posts: 89
Location: Noida , Dublin

PostPosted: Fri Feb 18, 2011 2:02 am
Reply with quote

true enricho.. I also trust manuals only i also have doubt on this statement only the one which u quote being a test system here in the company i dont have full authority to check..

Thanks Enrico
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Fri Feb 18, 2011 3:05 am
Reply with quote

Hello,

Quote:
i dont have full authority to check
Someone does. . .

If this is important to management they (someone who can check) will be directed to check.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Fri Feb 18, 2011 3:14 am
Reply with quote

what happened when looking at the manual You found the sentence
Quote:
Use the -ALTER PROCEDURE or -ALTER FUNCTION command to update the WLM ENVIRONMENT to specify an application environment that is defined and active in the active WLM policy, or update the WLM policy to contain a valid application environment name.

???
Back to top
View user's profile Send private message
mjadwani2785

New User


Joined: 28 Apr 2009
Posts: 89
Location: Noida , Dublin

PostPosted: Sat Feb 19, 2011 12:00 am
Reply with quote

I queried sysibm.sysroutines this proc is defined to db2vwlm wlm environment and it is active too...
Back to top
View user's profile Send private message
mjadwani2785

New User


Joined: 28 Apr 2009
Posts: 89
Location: Noida , Dublin

PostPosted: Sat Feb 19, 2011 12:06 am
Reply with quote

and when I display the proc i even getting this

ADMIN_INFO_SYSPARM
STARTED 0 0 1 0 0 DB2VWLM
DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Sun Feb 20, 2011 1:08 am
Reply with quote

Hi,

I started with your code and improvised it a bit and on-the-way i encountered all the errors that you have been encountering and little more also. Below is the fully tested code and executed code.

I resolved the -471 with 00E7900C error by looking into the following places for the correct WLM,
* Catalogs
* -DISPLAY PROCEDURE
* SDSF --> PRE DB2D*** --> for me its DB2D, look for WLM, go into the jesjcl--> look for 'APPLENV=' and also let us know what is your NUMTCB value

All WLM Must be same.

After checking all this, still if you have a problem, check whether you are pointing to the right sub-system.
Code:
/**** REXX **************************************/
/* DB2 ADMIN_INFO_SYSPARM STORED PROCEDURE      */
/************************************************/
ADDRESS TSO "SUBCOM DSNREXX"          /* HOST CMD ENV AVAILABLE    */
IF RC THEN                            /* IF NOT, MAKE IT AVAILABLE */
DO                                    /* ADD HOST CMD ENVIRONMENT  */
   S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END

/* CONNECT TO DB2 SUB SYSTEM */
ADDRESS DSNREXX 'CONNECT DB2D'
IF SQLCODE <> 0 THEN
DO
  CALL SQLCA
  SAY RC
END

 /*TRACE I */
   SSID_IND = -1
   SSID     = LEFT(' ',4)
   SAY SSID

   RETCD_IND=0
   RETCD=0

   MSSG_IND=0
   MSSG=LEFT(' ',1331,' ')

   INSQLDA.SQLD = 1
   INSQLDA.1.SQLTYPE = 449 /* VARCHAR */
   INSQLDA.1.SQLLEN = 32000
   INSQLDA.1.SQLIND = 0
   INSQLDA.1.SQLDATA = ' '

   PROC = 'SYSPROC.ADMIN_INFO_SYSPARM1'
   ADDRESS DSNREXX 'EXECSQL CALL ' PROC,
                              '(:SSID  :SSID_IND',
                              ',:RETCD :RETCD_IND',
                              ',:MSSG  :MSSG_IND)'
SAY 'RETCD   ='RETCD
SAY 'LENGTH = 'LENGTH(MSSG)
IF SQLCODE < 0 THEN CALL SQLCA
ELSE
DO
   ADDRESS DSNREXX 'EXECSQL DESCRIBE PROCEDURE :PROC INTO :SQLDA'
   SAY 'DONE WITH DESCRIBE'

   DO I = 1 TO SQLDA.SQLD
       SAY "SQLDA."I".SQLD ="SQLDA.SQLD";"
       SAY "SQLDA."I".SQLNAME ="SQLDA.I.SQLNAME";"
       SAY "SQLDA."I".SQLTYPE ="SQLDA.I.SQLTYPE";"
       SAY "SQLDA."I".SQLLOCATOR ="SQLDA.I.SQLLOCATOR";"
       SAY "SQLDA."I".SQLESTIMATE="SQLDA.I.SQLESTIMATE";"
   END

   /****************************************************************/
   /* SET UP A CURSOR TO RETRIEVE THE ROWS FROM THE RESULT         */
   /* SET.                                                         */
   /****************************************************************/
   ADDRESS DSNREXX 'EXECSQL ASSOCIATE LOCATOR (:MSSG)',
                   'WITH PROCEDURE :PROC'
   IF SQLCODE <> 0 THEN CALL SQLCA
   SAY 'MSSG ='MSSG

  ADDRESS DSNREXX "EXECSQL ALLOCATE C101 CURSOR FOR RESULT SET :MSSG"
  IF SQLCODE <> 0 THEN CALL SQLCA

  CURSOR = 'C101'
  ADDRESS DSNREXX "EXECSQL DESCRIBE CURSOR :CURSOR INTO :SQLDA"
      SAY 'ADEBUG SQLCODE ='SQLCODE
  IF SQLCODE <> 0 THEN CALL SQLCA
   DO I = 1 TO SQLDA.SQLD
       SAY "SQLDA."I".SQLD ="SQLDA.SQLD";"
       SAY "SQLDA."I".SQLNAME ="SQLDA.I.SQLNAME";"
       SAY "SQLDA."I".SQLTYPE ="SQLDA.I.SQLTYPE";"
       SAY "SQLDA."I".SQLLEN  ="SQLDA.I.SQLLEN";"
       SAY "SQLDA."I".SQLLOCATOR ="SQLDA.I.SQLLOCATOR";"
       SAY "SQLDA."I".SQLESTIMATE="SQLDA.I.SQLESTIMATE";"
   END

  /****************************************************************/
  /* RETRIEVE AND DISPLAY THE ROWS FROM THE RESULT SET, WHICH     */
  /* CONTAIN THE COMMAND OUTPUT MESSAGE TEXT.             */
  /****************************************************************/
  I=0
  DO UNTIL(SQLCODE <> 0)
      ADDRESS DSNREXX 'EXECSQL FETCH C101 ',
               'INTO :ROWNUM, :MACRO, :PARAMETER,',
               ':INSTALL_PANEL       :IND_INSTALL_PANEL,',
               ':INSTALL_FIELD       :IND_INSTALL_FIELD,',
               ':INSTALL_LOCATION    :IND_INSTALL_LOCATION,',
               ':VALUE,',
               ':ADDITIONAL_INFO     :IND_ADDITIONAL_INFO'

      IF SQLCODE = 0 THEN
      DO
        I = I + 1
        RECORD.I = SUBSTR(ROWNUM,1,3) SUBSTR(MACRO,1,8)
        RECORD.I = RECORD.I SUBSTR(PARAMETER,1,32) SUBSTR(INSTALL_PANEL,1,8)
    RECORD.I = RECORD.I SUBSTR(INSTALL_FIELD,1,40) SUBSTR(INSTALL_LOCATION,1,5)
        RECORD.I = RECORD.I SUBSTR(STRIP(VALUE),1,40)
        RECORD.I = RECORD.I SUBSTR(STRIP(ADDITIONAL_INFO),1,20)
/*      SAY 'ROWNUM='ROWNUM
        SAY 'MACRO ='MACRO
        SAY 'PARAMETER ='PARAMETER
        SAY 'INSTALL_PANEL ='INSTALL_PANEL
        SAY 'INSTALL_FIELD ='INSTALL_FIELD
        SAY 'INSTALL_LOCATION ='INSTALL_LOCATION
        SAY 'VALUE ='VALUE
        SAY 'ADDITIONAL INFO ='ADDITIONAL_INFO
        SAY RECORD.I
    */END
  END
  RECORD.0=I
  IF SQLCODE <> 0 THEN CALL SQLCA
  ADDRESS DSNREXX "EXECSQL CLOSE C101"
  IF SQLCODE <> 0 THEN CALL SQLCA

  ADDRESS DSNREXX "EXECSQL COMMIT"
  IF SQLCODE <> 0 THEN CALL SQLCA

  SAY RC
END
IF SQLCODE <> 0 THEN
DO
  CALL SQLCA
  SAY RC
END

ADDRESS DSNREXX 'DISCONNECT'

/* WHEN DONE WITH DSNREXX, REMOVE IT */
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')

   "DELETE '"HXSULL.DB2.DB2D.SYS.PARM"'"
   "ALLOC FI(DB2SYSP) DA('HXSULL.DB2.DB2D.SYS.PARM') MOD KEEP REUSE",
   "CYL SPACE(1,1) LRECL(200) RECFM(F,B,A) DSORG(PS)"
   "EXECIO *  DISKW DB2SYSP (STEM RECORD. FINIS"
EXIT

SQLCA:
SAY 'SQLCODE   = ' SQLCODE
SAY 'SQLSTATE  = ' SQLSTATE
SAY 'SQLERRD.1 = ' SQLERRD.1
SAY 'SQLERRD.2 = ' SQLERRD.2
SAY 'SQLERRD.3 = ' SQLERRD.3
SAY 'SQLERRD.4 = ' SQLERRD.4
SAY 'SQLERRD.5 = ' SQLERRD.5
SAY 'SQLERRD.6 = ' SQLERRD.6
SAY 'SQLERRMC  = ' SQLERRMC
RETURN


I have a doubt, why is the cursor C101 is used, what attribute does it have.
Code:
EXECSQL ALLOCATE C101 CURSOR FOR RESULT SET

From the redbook i got to know that C1 to C100 are declared with a default attribute of WITH RETURN. What attribute does the cursor C101 have.


Thanks,
Sushanth
Back to top
View user's profile Send private message
mjadwani2785

New User


Joined: 28 Apr 2009
Posts: 89
Location: Noida , Dublin

PostPosted: Wed May 04, 2011 10:42 pm
Reply with quote

Again after so many days...Today I again started working on this one...

The problem is db2vwlm proc is not able to start.. so something strike my mind and I tried to start it manually to look for error...

What I did was /s db2vwlm to look for the error in the log

and the proc started and abended..the error generated is

DSNX967I DSNX9WLM ATTEMPT TO PERFORM WLM FUNCTION IWMCONN FAILED
WITH WLM RC = 0000000C RSN = 11580C1B SSN = DB2V PROC= DB2VWLM
ASID = 0039 WLM_ENV = DB2VWLM

Does this refer to any db2 -wlm racf authority problem ?
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA

PostPosted: Wed May 04, 2011 10:49 pm
Reply with quote

Google is your friend. Googling dsnx967i returned 116 hits, including at leat one with the 0000000C RC and 11580C18 RSN.
Back to top
View user's profile Send private message
mjadwani2785

New User


Joined: 28 Apr 2009
Posts: 89
Location: Noida , Dublin

PostPosted: Thu May 26, 2011 11:23 pm
Reply with quote

The wlm application environment defined at my side was wrong. Today I defined the new one and it worked for me.

icon_smile.gif

Thanks all icon_smile.gif
Back to top
View user's profile Send private message
leela_krsna

New User


Joined: 09 Aug 2006
Posts: 3

PostPosted: Fri May 27, 2011 3:05 am
Reply with quote

What's the result of VARY?

"Specify an application environment that is defined and active in the active WLM policy, or update the WLM policy to contain a valid application environment name."
Back to top
View user's profile Send private message
mjadwani2785

New User


Joined: 28 Apr 2009
Posts: 89
Location: Noida , Dublin

PostPosted: Fri May 27, 2011 11:47 am
Reply with quote

Actual problem was in the wlm application definition the subsystem type was defined as subsystem name instead of just DB2 . Took almost weeks to figure it out but finally got through.

Then from the WLM ISPF panel only activated the policies.

Its a test environment out here just doing my R&D and learning.

Thanks!!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Run rexx with JCL Job CLIST & REXX 1
No new posts Run rexx in batch job CLIST & REXX 7
No new posts Does anyone know rexx for VSE CLIST & REXX 3
No new posts Excuting store procedure via JCL batch JCL & VSAM 1
No new posts Executing DB2 SQL store procedure via... DB2 0
Search our Forums:

Back to Top