|
View previous topic :: View next topic
|
| Author |
Message |
msa4004
New User
Joined: 25 Feb 2022
Posts: 4
Location: Malaysia
|
|
|
|
Hi Rexx Gurus,
I am pretty new coding Rexx and a first time poster. I am trying to create a Rexx program where it read input from DSN provided as DD in JCL. Extract record from that input DSN and then create a SQL statement as output DSN . I will use that SQL statements as input in DB2 utility program.
Having logic error with my Rexx.
Below Rexx code I wrote.
| Code: |
parse arg CUSTMR
/*====================================================================*/
/* GENERATE SQL TO EXTRACT data from a table */
/*====================================================================*/
CONN = SUBSTR(CUSTMR,4,3)
SAY 'CONN: ' CONN
DBNAME = DWR||CONN||E
SAY 'DBNAME: ' DBNAME
SQLREC.1=" SELECT * "
SQLREC.2=" FROM "DBNAME".TESTT"
SQLREC.3=" WHERE"
SQLREC.4=" CONN="CONN
SQLREC.5=" SSNN IN ( "
CALL GET_SSNN
SAY 'AFTER CALL CNT : ' LINE_CNT
SQLREC.LINE_CNT=" );"
SQLREC.0=LINE_CNT
"EXECIO * DISKW SQLOUT ( FINIS STEM SQLREC."
if RC = 0 then
do
say '<--- SQL GENERATED --->'
i = 0
do while i < SQLREC.0
i = i + 1
say SQLREC.i
end
end
else
RC=12
exit RC
END
/*====================================================================*/
/* SPLIT SSNN FOUND IN FILE into new and old */
/*====================================================================*/
GET_SSNN:
"EXECIO * DISKR SYSIN(FINIS STEM XACT."
LINE_CNT = 5
DO REC_CNT = 1 TO XACT.0
LINE_CNT = LINE_CNT + 1
OLD_SSNN.REC_CNT = SUBSTR(XACT.REC_CNT,6,14)
NEW_SSNN.REC_CNT = SUBSTR(XACT.REC_CNT,45,53)
SAY 'A LINE_CNT :' LINE_CNT
SAY 'A XACT:' XACT.REC_CNT
SAY 'A OLD SSNN :' OLD_SSNN.REC_CNT ' NEW SSNN : ' NEW_SSNN.REC_CNT
SAY 'A REC_CNT :' REC_CNT
SQLREC.LINE_CNT=" "OLD_SSNN.REC_CNT","NEW_SSNN.REC_CNT
END
RETURN LINE_CNT |
input file
| Code: |
000001 50698111111111 50698111112222
000002 50618333333333 50698333334444
000003 50618555555555 50698555556666
|
I have 3 records in input DSN for testing however only 2 out of 3 record are write/display as per below screen. It seem to skip the last record and only take the 1st two record in the DSN. I display it , Rexx clearly able to read all 3 records however on the SQL GENERATED only 2 of them appeared.
Currently logic to fix the bug escape me probably someone kind enough to point the way. Much appreciated
My Display
| Code: |
CONN: 157
DBNAME: DWR157E
A LINE_CNT : 6
A XACT: 50698111111111 50698111112222
A OLD SSNN : 111111111 NEW SSNN : 111112222
A REC_CNT : 1
A LINE_CNT : 7
A XACT: 50618333333333 50698333334444
A OLD SSNN : 333333333 NEW SSNN : 333334444
A REC_CNT : 2
A LINE_CNT : 8
A XACT: 50618555555555 50698555556666
A OLD SSNN : 555555555 NEW SSNN : 555556666
A REC_CNT : 3
AFTER CALL CNT : 8
<--- SQL GENERATED --->
SELECT *
FROM DWR157E.TESTT
WHERE
CONN=157
SSNN IN (
111111111 ,111112222
333333333 ,333334444
);
READY
END |
|
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
There are so many general misunderstanding, logical errors, and just typos, that it is much easier to write it from scratch, rather than explaining every mistake done...
| Code: |
Arg CUSTMR . /* same as PARSE UPPER ARG - for uppercase */
/*====================================================================*/
/* GENERATE SQL TO EXTRACT data from a table */
/*====================================================================*/
CONN = SUBSTR( CUSTMR, 4, 3 )
DBNAME = 'DWR' || CONN || 'E'
"EXECIO * DISKR SYSIN (FINIS"
#Lines = Queued() /* the number of input lines in the stack */
/* add lines for further output at the end of stack */
Queue " SELECT * "
Queue " FROM" DBNAME || ".TESTT"
Queue " WHERE CONN =" CONN
Queue " AND SSNN IN ( "
/* convert input records to the part of SQL statement */
LastSep = ','
Do i = 1 To #Lines /* stop at the last input line (below are the lines for output */
/* pull top line from stack, and parse it into two separate values */
Parse Pull =6 OLD_SSNN =14 . =45 NEW_SSNN =53
If i = #Lines Then /* change the last separator character from ',' to ')' */
LastSep = ')'
/* append new output line at the end of stack */
Queue " " OLD_SSNN',' NEW_SSNN LastSep
End i
/* write the whole output SQL text at once */
"EXECIO" Queued() "DISKW SQLOUT (FINIS"
Return 0
|
|
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland
|
|
|
|
In routine GET_SSNN, you set LINE_CNT before building the line. When you exit this routine, LINE_CNT is 8 and you then overwrite the last line in the second of these lines :
| Code: |
SAY 'AFTER CALL CNT : ' LINE_CNT
SQLREC.LINE_CNT=" );"
SQLREC.0=LINE_CNT |
Garry. |
|
| Back to top |
|
|
msa4004
New User
Joined: 25 Feb 2022
Posts: 4
Location: Malaysia
|
|
|
|
Thanks all for the fast reply..
sergeyken
your code is clean and much more efficient than mine
i will give a try. The is my second rexx coding program i done. Its kindna a prototype and a messed
Garry,
Thanks for the reply i will take a look at the logic |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
If your input values are in fact standard SSN numbers, they must be 9 digits in length.
In that case you need update the parsing:
| Code: |
| Parse Pull =6 OLD_SSNN =15 . =45 NEW_SSNN =54 |
or
| Code: |
| Parse Pull =6 OLD_SSNN +9 . =45 NEW_SSNN +9 |
|
|
| Back to top |
|
|
|
|
