View previous topic :: View next topic
Author
Message
abdulrafi Active User Joined: 14 Sep 2009Posts: 184 Location: Coimbatore
Hi,
I have written a REXX code which has to fetch the program using the table.
Code:
/* REXX */ 00000100
00000200
/* Get table name and region id as the input */ 00000300
00000400
PARSE UPPER ARG TBL_NM SID 00000500
PARSE VAR TBL_NM CREATOR '.' TBLNAME 00000600
00000700
outrow.1="-------------------------------------------------------------"00000800
outrow.2="LIST OF PROGRAMS USING THE " || TBL_NM || " TABLE" 00000900
outrow.3="-------------------------------------------------------------"00001000
outrow.4="PGM_NAME " || "|DELETE" || "|INSERT" || "|SELECT" 00001100
outrow.4=outrow.4 || "|UPDATE|" 00001200
outrow.5="------------|------|------|------|------|--------------------"00001300
stemval=6 00001400
00001500
/* Initialize Environment */ 00001600
00001700
Address ISPEXEC 00001800
"CONTROL ERRORS RETURN" 00001900
SSID = SID 00002000
00002100
/* Check for Environment availablity */ 00002200
00002300
Address TSO "SUBCOM DSNREXX" /* HOST CMD ENV AVAILABLE? */ 00002400
IF RC THEN /* NO, LET S MAKE ONE */ 00002500
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX') /* ADD HOST CMD ENV */ 00002600
00002700
00002800
/* Connect to DB2 environment */ 00002900
00003000
sqlcall = "connecting to " SSID 00003100
Address DSNREXX "CONNECT" SSID 00003200
IF SQLCODE < 0 THEN 00003300
rc=check_sqlcode(SQLCODE); /* check SQL return code */ 00003400
00003500
00003600
/* Format the SQL to be Executed */ 00003700
00003800
SQLSTMT = "SELECT DISTINCT A.GRANTEE, A.DELETEAUTH,", 00003900
"A.INSERTAUTH, A.SELECTAUTH, A.UPDATEAUTH", 00004000
"FROM SYSIBM.SYSTABAUTH A WHERE", 00004100
"AND A.SCREATOR = 'TMMPROD' AND", 00004200
"A.DATEGRANTED = (SELECT MAX(B.DATEGRANTED)", 00004300
"FROM SYSIBM.SYSTABAUTH B WHERE B.STNAME = A.STNAME", 00004400
"AND B.SCREATOR = A.SCREATOR AND B.GRANTEE = A.GRANTEE)", 00004500
"AND A.GRANTEE IN (SELECT DISTINCT C.GRANTEE", 00004600
"FROM SYSIBM.SYSTABAUTH C", 00004700
"WHERE C.SCREATOR = 'TMMPROD')", 00004800
"WITH UR;" 00004900
00005000
/* Declare the cursor */ 00005100
00005200
sqlcall = "declaring cursor c1" 00005300
Address DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1" 00005400
IF SQLCODE < 0 THEN 00005500
rc=check_sqlcode(SQLCODE); /* check SQL return code */ 00005600
00005700
00005800
/* Prepare the SQL */ 00005900
00006000
sqlcall = "preparing sql statement" 00006100
Address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT" 00006200
IF SQLCODE < 0 THEN 00006300
rc=check_sqlcode(SQLCODE); /* check SQL return code */ 00006400
00006500
00006600
/* Open Cursor */ 00006700
00006800
sqlcall = "opening cursor c1" 00006900
Address DSNREXX "EXECSQL OPEN C1" 00007000
IF SQLCODE < 0 THEN 00007100
rc=check_sqlcode(SQLCODE); /* check SQL return code */ 00007200
Else 00007300
Call FetchCursor /* Fetch Cursor subrout */ 00007400
00007500
00007600
/* Close Cursor */ 00007700
00007800
sqlcall = "closing cursor c1" 00007900
Address DSNREXX "EXECSQL CLOSE C1" 00008000
IF SQLCODE < 0 THEN 00008100
rc=check_sqlcode(SQLCODE); /* check SQL return code */ 00008200
00008300
/* Disconnect from DB2 */ 00008400
00008500
sqlcall = "disconnect" 00008600
Address DSNREXX "DISCONNECT" 00008700
IF SQLCODE < 0 THEN 00008800
rc=check_sqlcode(SQLCODE); /* check SQL return code */ 00008900
00009000
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX') /* ADD HOST CMD ENV */ 00009100
00009200
00009300
/* Create a temporary file to display the output */ 00009400
00009500
/*Address TSO "ALLOC DATASET('"UID"."TBL"."USAG"') DDNAME(ODD)",*/ 00009600
Address TSO "ALLOC DATASET(DB2TABLE.USAGE) DDNAME(ODD)", 00009700
" NEW SPACE(1 1) TRACKS" , 00009800
" LRECL(80) BLKSIZE(80) RECFM(F B) MGMTCLAS(TSODEL)" 00009900
00010000
/* Write the data extracted from table to file */ 00010100
00010200
Address TSO "EXECIO * DISKW ODD (STEM outrow. FINIS" 00010300
/*Address TSO "EXECIO 0 DISKW ODD (FINIS" */ 00010400
00010500
Address TSO "FREE F(ODD)" 00010600
00010700
/* Open the data set in browse mode for display */ 00010800
00010900
ISPEXEC "VIEW DATASET(DB2TABLE.USAGE)" 00011000
00011100
/* Suppress confirmation messages for deleting the dataset */ 00011200
00011300
stat=MSG('off') 00011400
00011500
/* Delete the dataset */ 00011600
00011700
Address TSO "DELETE DB2TABLE.USAGE" 00011800
00011900
Exit 00012000
/*************************************************************/ 00012100
/* Fetch the cursor and write to a temporary file. */ 00012200
/*************************************************************/ 00012300
FetchCursor: 00012400
firsttime = 'Y'; 00012500
Do until (SQLCODE = 100) 00012600
sqlcall = "fetch cursor c1" 00012700
Address DSNREXX "EXECSQL FETCH C1 INTO " , 00012800
":pname, :delath, :insath, :selath, :updath" 00012900
IF SQLCODE < 0 THEN 00013000
rc=check_sqlcode(SQLCODE); /* check SQL return code */ 00013100
Else 00013200
Do 00013300
IF SQLCODE ¬= 100 THEN 00013400
do 00013500
firsttime = 'N'; 00013600
Call buildline 00013700
end 00013800
Else 00013900
do 00014000
if firsttime = 'Y' then 00014100
do 00014200
outrow.stemval =" no records to display" 00014300
stemval=stemval+1 00014400
end 00014500
end 00014600
End 00014700
End 00014800
00014900
Return 00015000
00015100
buildline: 00015200
/*************************************************************/ 00015300
/* Build the output line. */ 00015400
/* Check for DB2 NULL in the columns that allow NULLS */ 00015500
/*************************************************************/ 00015600
dl = STRIP(delath) 00015700
in = STRIP(insath) 00015800
sl = STRIP(selath) 00015900
up = STRIP(updath) 00016000
pg = SUBSTR(pname,1,2) 00016100
del =" " || dl || " " 00016200
ins =" " || in || " " 00016300
sel =" " || sl || " " 00016400
upd =" " || up || " " 00016500
select 00016600
when dl == 'G' | in == 'G' | sl == 'G' | up == 'G' then 00016700
nop 00016800
when pg == 'U0' then 00016900
nop 00017000
otherwise 00017100
do 00017200
outrow.stemval= substr(pname,1,12,' ') || '|' 00017300
outrow.stemval= outrow.stemval || substr(del,1,6,' ') || '|' 00017400
outrow.stemval= outrow.stemval || substr(ins,1,6,' ') || '|' 00017500
outrow.stemval= outrow.stemval || substr(sel,1,6,' ') || '|' 00017600
outrow.stemval= outrow.stemval || substr(upd,1,6,' ') || '|' 00017700
stemval=stemval+1 00017800
end 00017900
end 00018000
Return 00018100
00018200
FetchCursor: 00018300
/**********************************************************************/00018400
/* Check SQLCODE */ 00018500
/* check value of SQLCODE. If 0, fine just leave. Otherwise display 00018600
the error messages, issue a ROLLBACK and exit with the bad code */ 00018700
/**********************************************************************/00018800
check_sqlcode: 00018900
IF SQLCODE = 0 THEN RETURN 0; 00019000
if SQLCODE = '-924' & SID = 'DSN1' THEN 00019100
do 00019200
SAY " * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * " 00019300
SAY "* * * * * * * * * * " 00019400
SAY " * * * * * If you are trying in DSYS try in ASYS * * * * * " 00019500
SAY "* * * * * * * * * * " 00019600
SAY " * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * " 00019700
ADDRESS DSNREXX "EXECSQL ROLLBACK" 00019800
exit SQLCODE 00019900
end 00020000
if SQLCODE = '-924' & SID \= 'DSN1' THEN 00020100
do 00020200
SAY " * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * " 00020300
SAY "* * * * * * * * * * " 00020400
SAY " * * * * * If you are trying in ASYS try in DSYS * * * * * " 00020500
SAY "* * * * * * * * * * " 00020600
SAY " * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * " 00020700
ADDRESS DSNREXX "EXECSQL ROLLBACK" 00020800
exit SQLCODE 00020900
end 00021000
SAY "Error detected at " sqlcall 00021100
SAY "SQLCODE =" SQLCODE 00021200
SAY "RETCODE =" RETCODE 00021300
SAY "SQLSTATE =" SQLSTATE 00021400
SAY "SQLERRMC =" SQLERRMC 00021500
SAY "SQLERRP =" SQLERRP 00021600
SAY "SQLERRD ="SQLERRD.1',', 00021700
||SQLERRD.2',', 00021800
||SQLERRD.3',', 00021900
||SQLERRD.4',', 00022000
||SQLERRD.5',', 00022100
||SQLERRD.6 00022200
SAY "SQLWARN ="SQLWARN.0',', 00022300
||SQLWARN.1',', 00022400
||SQLWARN.2',', 00022500
||SQLWARN.3',', 00022600
||SQLWARN.4',', 00022700
||SQLWARN.5',', 00022800
||SQLWARN.6',', 00022900
||SQLWARN.7',', 00023000
||SQLWARN.8',', 00023100
||SQLWARN.9',', 00023200
||SQLWARN.10 00023300
ADDRESS DSNREXX "EXECSQL ROLLBACK" 00023400
exit SQLCODE 00023500
Error while executing the code,
Code:
Error detected at preparing sql statement
SQLCODE = -805
RETCODE = RETCODE
SQLSTATE = 51002
SQLERRMC = DSN..DSNREXX.0E4C9F4F04F3F1F2:DSNREXX:03
SQLERRP = DSNXEPM
SQLERRD =-251,0,0,-1,0,0
SQLWARN = , , , , , , , , , ,
It is erroring out while preapring the sql.
I checked the forum. For return code 3 on DSNREXX, it tells to check the SSID. But I give the correct one only. I am unable to find the issue.
I executed the SQL statement seperately and it executed fine.
Could you please help me resolve this ?
Back to top
Nic Clouston Global Moderator Joined: 10 May 2007Posts: 2455 Location: Hampshire, UK
Are you executing this on the same system as the DB2 system that you are querying against?
As an aside: you have 2 FetchCursor labels.
Back to top
sergeyken Senior Member Joined: 29 Apr 2008Posts: 2022 Location: USA
abdulrafi wrote:
Could you please help me resolve this ?
Why not to try using trace r / trace I / say <var> - to verify exactly : what values have been prepared/passed throughout the suspicious part of your code (and to compare with what you expect to be passed)?
Back to top
Pedro Global Moderator Joined: 01 Sep 2006Posts: 2547 Location: Silicon Valley
Per this:
Code:
SQLERRMC = DSN..DSNREXX.0E4C9F4F04F3F1F2:DSNREXX:03
along with -805, the DBRM for DSNREXX is not correct. It has the wrong consistency token.
Have you been able to use DSNREXX before? Check that your DBRMLIB concatenation contains the correct member for DSNREXX. Try REBIND-ing it.
This is not really a rexx question, but a DB2 question. See ibmmainframes.com/viewforum.php?f=13
Back to top
abdulrafi Active User Joined: 14 Sep 2009Posts: 184 Location: Coimbatore
Hi Pedro/Nic,
I even used the below statement where the DSNREXX is present but I could not resolve the issue. I gave few displays to check if my code is able to establish the connectivity and it works fine. It gives return code 0 and tells DSN is the SID which is correct. So it works fine when connecting to DB, fetching the cursor, but failing in prepare statement. I browsed through the forum, but could get only the below clue. Please let me know if I need to give any other library.
Code:
sdsnload='DSN.SDSNLOAD'
Address ISPEXEC
"LIBDEF ISPLLIB DATASET ID('"||sdsnload||"')"
Yes. DSNREXX is used only in my rexx module. I couuld not find it any other member in CLIST library.
Back to top
Rohit Umarjikar Global Moderator Joined: 21 Sep 2010Posts: 3053 Location: NYC,USA
Quote:
I browsed through the forum, but could get only the below clue. Please let me know if I need to give any other library
Besides, How about make an attempt to talk to DBA to get right library and the sequence or matter of the fact to get an advise from your DBA's first? Most of the times that saves time and get you straight to the answers.
Back to top
abdulrafi Active User Joined: 14 Sep 2009Posts: 184 Location: Coimbatore
Yes Rohit. I have dropped a mail to my DBA as well. I was thinking if I could fix it by any other way. Let me wait and see my DBA's response.
Back to top
Pedro Global Moderator Joined: 01 Sep 2006Posts: 2547 Location: Silicon Valley
Maybe you did not understand my earlier post... this is not a question for rexx experts. This is a question for DB2 experts. You should ask your question in the DB2 forum. See ibmmainframes.com/viewforum.php?f=13
Back to top
Pedro Global Moderator Joined: 01 Sep 2006Posts: 2547 Location: Silicon Valley
Quote:
DSNREXX is used only in my rexx module. I couuld not find it any other member in CLIST library.
This indicates to me that DSNREXX has not been used at your shop before and is probably missing some setup that the installer needs to do.
Back to top
don.leahy Active Member Joined: 06 Jul 2010Posts: 765 Location: Whitby, ON, Canada
Check the DB2 catalog to make sure that the DSNREXX package exists. If not present, talk to your DB2 DBA or System Programmer.
Back to top
enrico-sorichetti Superior Member Joined: 14 Mar 2007Posts: 10873 Location: italy
IIRC the <the_db2_hllq>.SDSNLOAD library should be in the STEPLIB concatenation
read the relevant ISPF manuals to find the differences between the STEPLIB and the ISPLLIB
logic for the load modules search
Back to top
Please enable JavaScript!