my code here:
/*REXX*/
ADDRESS ISPEXEC
INPUT = 'SJAYADE.REXDB.INPUT'
OUTPUT = 'N01.UH.SJAYADE.REXX.DB2'
OUT_COUNT = 1
OUTROW. = ""
ADDRESS TSO
"ALLOC DA('"INPUT"') FI(INFILE) SHR"
"EXECIO * DISKR INFILE (STEM INSTEM. FINIS"
"FREE FI(INFILE)"
/*DO B = 16383 TO INSTEM.0*/
DO B = 1 TO INSTEM.0
/*DO Z = 16384 TO 17000*/
PARSE VAR INSTEM.B JB_NAME JUNK
SAY B" "JB_NAME
CALL QUERY JB_NAME
OUT_COUNT = OUT_COUNT-1
END
ADDRESS TSO
"ALLOC DA('"OUTPUT"') FI(OUTFILE) SHR"
EXECIO * DISKW OUTFILE (STEM OUTROW. FINIS"
FREE FI(OUTFILE)"
IF RC\=0 THEN DO
SAY 'COMMAND CANCELLED'
END
EXIT
QUERY: PROCEDURE EXPOSE COUNT OUT_COUNT OUTPUT OUTROW.
PARSE ARG JOB_NAME
SQLSTMT1 = ,
"SELECT DISTINCT",
"TABLE1.JB_NAME,",
"TABLE1.ST_NAME,",
"TABLE1.SYS_PLEX,",
"TABLE1.DS_NBR,",
"TABLE1.DD_NAME",
" FROM DEVGEN.JOB_STEP_DATASET A",
" WHERE TABLE1.JB_NAME='"JOB_NAME"'"
SUBSYS = DSNT
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT" SUBSYS
IF SQLCODE /= 0 THEN CALL SQLCA
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
IF SQLCODE /= 0 THEN CALL SQLCA
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT1"
IF SQLCODE /= 0 THEN CALL SQLCA
ADDRESS DSNREXX "EXECSQL DESCRIBE S1 INTO :OUTSQLDA"
IF SQLCODE /= 0 THEN CALL SQLCA
ADDRESS DSNREXX "EXECSQL OPEN C1"
IF SQLCODE /= 0 THEN CALL SQLCA
COUNT = 0
DO UNTIL(SQLCODE/=0)
COUNT = COUNT+1
ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
INV = ""
IF SQLCODE = 0 THEN
DO I = 1 TO OUTSQLDA.SQLD
INV = INV"¦"OUTSQLDA.I.SQLDATA
END
ELSE
DO
/* INV ='NO DATA' */
END
OUTROW.OUT_COUNT = INV
OUT_COUNT = OUT_COUNT+1
END
ADDRESS DSNREXX "EXECSQL CLOSE C1"
/* SAY COUNT */
IF SQLCODE /= 0 THEN CALL SQLCA
RETURN
SQLCA: PROCEDURE EXPOSE COUNT OUT_COUNT OUTPUT OUTROW.
SAY BYE
SAY OUT_COUNT
SAY OUTPUT
SAY OUTROW.1
"ALLOC DA('"OUTPUT"') FI(OUTFILE) SHR"
"EXECIO * DISKW OUTFILE (STEM OUTROW. FINIS"
"FREE FI(OUTFILE)"
SAY "SQLCODE = " SQLCODE
SAY "SQLSTATE = " SQLSTATE
SAY "SQLERRMC = " SQLERRMC
thanks in advance
