Page 1 of 1

use count error (906) in REXX accessing DB2

PostPosted: Wed Dec 21, 2011 3:08 pm
by sathukudi
Hi all am trying to read an input file which has the input for my select query and execute the query everytime using rexxdsn connecct after exectuitng the query for 16383 it abends dur to use count error. could anyone. help me with this.
error.jpg


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

Re: use count error (906) in REXX accessing DB2

PostPosted: Wed Dec 21, 2011 4:07 pm
by enrico-sorichetti
to post code use, as the name hints, the code tags, not the quote tags,
it makes easier for the people willing to help to understand the code snippets posted

and no reason to waste space with jpegs/images/screenshots
when a plain text cut/paste with the code tags will provide the same info

Re: use count error (906) in REXX accessing DB2

PostPosted: Wed Dec 21, 2011 4:21 pm
by sathukudi
sorry man. here it comes again..

error:

16382 ZEGR192D
16383 ZEGR201D
16384 ZEGR252J
IRX0250E System abend code 906, reason code 00000008.
IRX0255E Abend in host command CONNECT or address environment routine DSNREXX.
    75 *-*   ADDRESS DSNREXX "CONNECT" SUBSYS
       +++ RC(-2310) +++


code :

/*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



any possible solution is greatly appreaciated
Thanks in advance

Re: use count error (906) in REXX accessing DB2

PostPosted: Wed Dec 21, 2011 4:33 pm
by enrico-sorichetti
sorry man.

do we know ? ...
this is not a punk/rapper community so addressing people the way You do is just a symptom of bad manners :evil:

System abend code 906, reason code 00000008.

the messages and code manuals explanation is quite clear ...
http://publibz.boulder.ibm.com/cgi-bin/ ... 5350&CASE=

in any case Your logic is wrong...
any reason to connect for every record of Your input data ?

run with a Trace and You will see how many useless statements will be executed