|
View previous topic :: View next topic
|
| Author |
Message |
priyankakir
New User
Joined: 12 Jun 2019
Posts: 7
Location: INDIA
|
|
|
|
Hi All,
My requirement is to check if WITH UR is present in all the select queries or not in a program. If WITH UR is missing in any query then display the line number with error statement. I tried using loops. First I am searching EXEC and then if EXEC found then I am searching for SELECT. If SELECT found inside EXEC then it should search for END-EXEC. Then it search if WITH UR present inside EXEC and END-EXEC or not. I am facing issue in the below code. Please help me in figuring out the issue or any alternative solution.
| Code: |
/* REXX */
"ALLOC F(INPUT) DA(‘INPUT PS FILE’) SHR "
"EXECIO * DISKR INPUT (FINIS STEM LINE."
"FREE F(INPUT)"
EXECSTMT = 0
EXECSTMT1 = 0
CNT = 0
CNT1 = 0
CNT2 = 0
TAIL = 0
HDR = 0 0
D = 1
DO I = D TO LINE.0
IF WORDPOS("EXEC",LINE.I) > 0 THEN HDR = 1
IF HDR = 1 THEN
DO
A = I
HDR = 0
SAY A
END
DO B = A TO LINE.0
IF (WORDPOS("SELECT",LINE.B)>0) THEN HDR = 1
IF HDR = 1 THEN
DO
CNT = B
HDR = 0
SAY "SELECT FOUND"
END
END
DO CNT2 = CNT TO LINE.0
IF (WORDPOS("END-EXEC",LINE.CNT2) > 0) THEN HDR=1
IF HDR = 1 THEN
DO
CNT3 = CNT2
HDR = 0
B = LINE.0
CNT2 = LINE.0
SAY "END-EXEC FOUND"
END
END
DO CNT4 = CNT TO LINE.0
IF (WORDPOS("WITH UR",LINE.CNT4) > 0) THEN HDR=1
IF HDR = 1 THEN
IF CNT4 < CNT3 THEN
DO
HDR = 0
CNT5 = CNT4
SAY "WITH UR FOUND"
END
IF CNT4 > CNT3 THEN
DO
CNT4 = LINE.0
CNT5 = CNT4
C=CNT3 + 1
NEW.D = LINE.C
SAY "WITH UR NOT FOUND"
END
END
END
|
|
|
| Back to top |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
I have coded up your code - please do it yourself next time. In doing so I standardised the indentation so it is now easier to see what goes with what.
What issues are you having? Error messages, sample input, actual output from that sample should be supplied.
What did your trace show? Post that (the relevant portion only)
In your HDR initialisation you have an extra 0.
You have no comments in your code to let people, including yourself, know what each part is supposed to do. |
|
| Back to top |
|
|
priyankakir
New User
Joined: 12 Jun 2019
Posts: 7
Location: INDIA
|
|
|
|
Input:- I am having below input which is present in input file (‘INPUT PS FILE’
1)EXEC
2)SELECT FROM TABLE
3)WITH UR
4)END-EXEC
5)EXEC
6)SELECT FROM TABLE2
7)END-EXEC
now I want my output to be
Output Expected:- WITH UR NOT FOUND Before Line 7
The output for the above code is coming as
| Code: |
1
SELECT FOUND
END-EXEC FOUND
WITH UR NOT FOUND
2
SELECT FOUND
END-EXEC FOUND
WITH UR NOT FOUND
3
END-EXEC FOUND
WITH UR NOT FOUND
4
END-EXEC FOUND
WITH UR NOT FOUND
5
END-EXEC FOUND
WITH UR NOT FOUND
|
|
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| The main problem is that you are executing each DO clause against EVERY line. They should be executed within the DO clause that finds the EXEC keyword line. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
This example (for OORexx (I recommend you get it)) does what you want. It has not been cleaned up to get rid of some spurious coding or use better logic but it works.
| Code: |
/* REXX */
/* Initialisations */
input = '"300records.txt"'
extract = '"with_ur.txt"'
exit_msg = "withur.rex ended OK"
exec_line_no = 0
with_ur_line_no = 0
/* Read the source into stem "line.". */
Address hostemu "EXECIO * DISKR "INPUT" (FINIS STEM line."
If rc <> 0
Then Do
exit_msg = "EXECIO failed with rc = "rc
Signal door
End
Do i = 1 To line.0
If Wordpos("EXEC",line.I) > 0
Then Call process_exec_group
End
door:
Drop line.
Say exit_msg
Exit
/*===================================================================*/
process_exec_group:
/* Say where we are */
exec_line_no = i
Say "EXEC found at line "exec_line_no
/* Find the END-EXEC associated with this EXEC */
Do i = (i + 1) To line.0
If (Wordpos("END-EXEC", line.i) > 0)
Then Do
end_exec_line_no = i
Leave
End
End
/* Find if this EXEC executes a SELECT */
Do x = exec_line_no To end_exec_line_no
If (Wordpos("SELECT",line.x) > 0)
Then Do
select_line_no = x
Say "SELECT FOUND at line "select_line_no
End
End
/* If SELECT was found then see if this block contains WITH UR */
If select_line_no > 0
Then Do
Do x = select_line_no To end_exec_line_no
If (Wordpos("WITH UR",line.x) > 0)
Then Do
with_ur_line_no = x
Say "WITH UR found at line "x
End
End
If with_ur_line_no = 0
Then Say "WITH UR not found for EXEC at line "exec_line_no
End
/* reset all line positions used in procedure */
exec_line_no = 0
select_line_no = 0
with_ur_line_no = 0
end_exec_line_no = 0
Return
/*===================================================================*/
::requires "hostemu" library
|
|
|
| Back to top |
|
|
Willy Jensen
Active Member
Joined: 01 Sep 2015
Posts: 734
Location: Denmark
|
|
|
|
May I suggest a more compact alternative:
| Code: |
parse value 0 0 with selln urln
do ln=1 to line.0
select
when word(line.ln,1)='SELECT' then selln=ln
when space(line.ln)='WITH UR' then urln=ln
when space(line.ln)='END-EXEC' then do
if urln>0 then say 'WITH UR found in line' urln,
'of' line.selln 'in line' selln
else say line.selln 'in line' selln 'has no WITH UR'
parse value 0 0 with selln urln
end
otherwise nop
end
end |
The code generate these lines:
| Code: |
WITH UR found in line 3 of SELECT FROM TABLE in line 2
SELECT FROM TABLE2 in line 6 has no WITH UR |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
Some notes on previous sample of code.
1) the code itself is overcomplicated, while important cases of specific coding are not taken into account
2) inaccurate usage of common variables (especially: variable "i") inside and outside of the procedure with open namespace (e.g., no "procedure" statement)
3) the chances of coding the SQL statement in single/multiple lines is not considered:
| Code: |
EXEC SQL
SELECT
FROM TABLE
WITH UR
END-EXEC |
versus
| Code: |
| EXEC SQL SELECT FROM TABLE WITH UR END-EXEC |
4) The chances of free used spaces are not considered:
| Code: |
| EXEC SQL SELECT FROM TABLE WITH UR END-EXEC |
versus
| Code: |
| EXEC SQL SELECT FROM TABLE WITH UR END-EXEC |
5) Incorrect keyword sequence is not considered:
| Code: |
EXEC SQL
SELECT
FROM TABLE
WITH UR
END-EXEC |
versus
| Code: |
EXEC SQL
SELECT
EXEC SQL
EXEC SQL
WITH UR
END-EXEC |
I'd like to suggest a different style of analyzer logic, and its coding
| Code: |
/* REXX */
/* Read the source into stem "Code." */
err_code = 0
"EXECIO * DISKR "INPUT" (FINIS STEM Code."
If rc <> 0
Then Do
exit_msg = "EXECIO failed with rc = "rc
err_code = 12
Signal door
End
/* reset indicators before scanning the code */
exec_line_no = 0
select_line_no = 0
with_ur_line_no = 0
/* ensure any keyword detected in both separate, and single lines */
Do iLine = 1 To Code.0
NewLine = Space( Code.iLine, 1 ) /* re-format to single spaces */
If Wordpos( "EXEC", NewLine ) > 0 Then Do
exec_line_no = iLine
If exec_line_no > 0 Then
Say exec_line_no": duplicate EXEC found before END-EXEC"
Else Do
Say exec_line_no": EXEC found"
End
End
If exec_line_no > 0 Then Do
If Wordpos( "SELECT", NewLine ) > 0 Then Do
select_line_no = iLine
Say "SELECT found within EXEC"
End
If select_line_no > 0 ,
& Wordpos( "WITH UR", NewLine ) > 0 Then Do
with_ur_line_no = iLine
Say "WITH UR found within SELECT"
End
If Wordpos( "END-EXEC", NewLine ) > 0 Then Do
Say "END-EXEC found after EXEC"
If select_line_no = 0 Then
Say "SELECT not found within EXEC"
Else If with_ur_line_no = 0 Then
Say "WITH UR not found within SELECT"
/* reset indicators before new EXEC group */
exec_line_no = 0
select_line_no = 0
with_ur_line_no = 0
End
End
End iLine
door:
Drop Code.
Say exit_msg
Exit err_code
/*====================================================================*/ |
|
|
| Back to top |
|
|
priyankakir
New User
Joined: 12 Jun 2019
Posts: 7
Location: INDIA
|
|
|
|
Thank you Nic, sergeyken and Willy for replying to the post. I am new to REXX all answers were of great help.
I tried Nic's code and it is working fine. Yet to try other two code.
Nic I have a doubt. We are not increasing the value of "I" anywhere in code. Is it automatically increased after one Exec found and then goes to next Exec line in the file? |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
increments i in the mainline
| Code: |
| Do i = (i + 1) To line.0 |
increments i in the subroutine where it is looking for END-EXEC. Note that at this line should read
to capture those cases where the entire block of code is coded on one line (not a good coding practice). |
|
| Back to top |
|
|
Willy Jensen
Active Member
Joined: 01 Sep 2015
Posts: 734
Location: Denmark
|
|
|
|
Another version which will handle the EXEC statement in one or more lines:
| Code: |
line.1='EXEC'
line.2='SELECT FROM TABLE'
line.3='WITH UR'
line.4='END-EXEC'
line.5='EXEC SELECT FROM TABLE 1 WITH UR END-EXEC'
line.6='EXEC'
line.7='SELECT FROM TABLE2'
line.8='END-EXEC'
line.9='EXEC SELECT FROM TABLE 1 END-EXEC'
line.0=9
parse value 0 0 with exln wuln
do ln=1 to line.0
l=translate(space(line.ln))
if word(l,1)='EXEC' then exln=ln
if pos('WITH UR',l)>0 then wuln=ln
if right(l,8)='END-EXEC' then do
if wuln>0 then say 'WITH UR found in line' wuln,
'of EXEC in line' exln
else say 'EXEC in line' exln 'has no WITH UR'
parse value 0 0 with exln wuln
end
end |
Will produce this list:
| Code: |
WITH UR found in line 3 of EXEC in line 1
WITH UR found in line 5 of EXEC in line 5
EXEC in line 6 has no WITH UR
EXEC in line 9 has no WITH UR |
|
|
| Back to top |
|
|
priyankakir
New User
Joined: 12 Jun 2019
Posts: 7
Location: INDIA
|
|
|
|
I am trying to read data from PDS where I have a member and inside that I want to check if WITH UR is missing inside EXEC and END-EXEC or not.
code has input as PDS member which is not working. it is not reading line of PDS member
| Code: |
/* REXX */
exec_line_no = 0
with_ur_line_no = 0
"ALLOC F(INFILE) DSN('INPUT.PDS(MEMBER)') SHR REU"
"EXECIO * DISKR INPUT (FINIS STEM LINE."
"FREE F(INPUT)"
Do i = 1 To line.0
If Wordpos("EXEC",line.I) > 0
Then Call process_exec_group
End
|
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
| priyankakir wrote: |
| code has input as PDS member which is not working. |
Does this mean, the computer is turning off? Got fire and destroyed? Speaks in English: "I'm not working"?
Many of us are able to read/write PDS members from REXX for years, and years...
What exactly is your problem???
P.S.
I can see what your problem is, but would not answer unless you are able to ask your questions correctly. |
|
| Back to top |
|
|
Willy Jensen
Active Member
Joined: 01 Sep 2015
Posts: 734
Location: Denmark
|
|
|
|
of course it doesn't work,
"ALLOC F(INFILE) DSN('INPUT.PDS(MEMBER)') SHR REU"
specifies INFILE as DDname,
"EXECIO * DISKR INPUT (FINIS STEM LINE."
specifies INPUT as DDname. |
|
| Back to top |
|
|
priyankakir
New User
Joined: 12 Jun 2019
Posts: 7
Location: INDIA
|
|
|
|
Code is not working correctly if END-EXEC is having dot after it.
It is throwing bad arthmetic error.
For example let's say I in below code I want to find if select query has WITH UR or not. So it will search first for first EXEC SQL and search if select is there inside EXEC SQL AND END- EXEC block. if select found then it will search for WITH UR. But I am getting bad arthmetic error as we are having dot after END- EXEC.
| Code: |
1)IDENTIFICATION DIVISION.
2)PROGRAM ID. TEST.
3)ENVIRONMENT SECTION.
4)DATA DIVISION.
5)WORKING STORAGE SECTION.
6)EXEC SQL
7)SQLCA
8)END-EXEC.
9)PROCEDURE DIVISION.
10)EXEC SQL
11)SELECT
12)FROM TABLE
13)END-EXEC.
|
|
|
| Back to top |
|
|
Willy Jensen
Active Member
Joined: 01 Sep 2015
Posts: 734
Location: Denmark
|
|
|
|
| which code? |
|
| Back to top |
|
|
priyankakir
New User
Joined: 12 Jun 2019
Posts: 7
Location: INDIA
|
|
|
|
Hi All,
Now code is working fine I have removed '.' after END-EXEC statement so now it is working fine.
I want to get the output in a PS file could you please let me know if it is possible to write the below string output in PS file.
| Code: |
"WITH UR not found for EXEC at line "200
"WITH UR not found for EXEC at line "350
|
Below is full code
| Code: |
/* REXX ACT */
/* INITIALISATIONS */
EXIT_MSG = "WITHUR.REX ENDED OK"
EXEC_LINE_NO = 0
WITH_UR_LINE_NO = 0
/* READ THE SOURCE INTO STEM "LINE.". */
"ALLOC F(INPUT) DSN('INPUT.PDS (MEMBER)') SHR"
/*"EXECIO * DISKR INPUT (FINIS STEM LINE." */
"EXECIO * DISKR INPUT (STEM LINE. FINIS"
"FREE F(INPUT)"
IF RC <> 0
THEN DO
EXIT_MSG = "EXECIO FAILED WITH RC = "RC
SIGNAL DOOR
M
END
DO I = 1 TO LINE.0
IF WORDPOS("EXEC SQL",LINE.I) > 0
THEN
CALL PROCESS_EXEC_GROUP
END
DOOR:
DROP LINE.
SAY EXIT_MSG
EXIT
/*===================================================================*/
PROCESS_EXEC_GROUP:
/* SAY WHERE WE ARE */
EXEC_LINE_NO = I
/* SAY "EXEC FOUND AT LINE "EXEC_LINE_NO */
/* FIND THE END-EXEC ASSOCIATED WITH THIS EXEC */
DO I = (I + 1) TO LINE.0
TEMP = TRANSLATE(LINE.I," ",".")
IF (WORDPOS("END-EXEC", TEMP) > 0)
THEN DO
END_EXEC_LINE_NO = I
LEAVE
END
END
/* FIND IF THIS EXEC EXECUTES A SELECT */
DO X = EXEC_LINE_NO TO END_EXEC_LINE_NO
IF (WORDPOS("SELECT",LINE.X) > 0)
THEN DO
SELECT_LINE_NO = X
/* SAY "SELECT FOUND AT LINE "SELECT_LINE_NO */
END
END
/* IF SELECT WAS FOUND THEN SEE IF THIS BLOCK CONTAINS WITH UR */
IF SELECT_LINE_NO > 0
THEN DO
DO X = SELECT_LINE_NO TO END_EXEC_LINE_NO
IF (WORDPOS("WITH UR",LINE.X) > 0)
THEN DO
WITH_UR_LINE_NO = X
IND=Y
/* SAY "WITH UR FOUND AT LINE "WITH_UR_LINE_NO */
END
END
IF WITH_UR_LINE_NO = 0
THEN SAY "WITH UR NOT FOUND FOR EXEC AT LINE "EXEC_LINE_NO
END
/* RESET ALL LINE POSITIONS USED IN PROCEDURE */
EXEC_LINE_NO = 0
SELECT_LINE_NO = 0
WITH_UR_LINE_NO = 0
END_EXEC_LINE_NO = 0
RETURN
/*===================================================================*/
|
|
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
Write your message to a stem (message.msgno - "the message to write").
You have to initialise msgno to 0 at the program start and increment it before assigning the message to message.msgno. Before exiting the program, if msgno > 0 allocate your output dataset and use EXECIO to write the stem.
Keep a count of message lines in message.0 and use that in you EXECIO as the number of lines to write. |
|
| Back to top |
|
|
priyankakir
New User
Joined: 12 Jun 2019
Posts: 7
Location: INDIA
|
|
|
|
Thank you Nic I am able to write the lines not having WITH UR in PS file.
But when I am running the code again the file is having old data it is not getting refreshed. I have used Delete file command also but it is throwing error. Please find code below.
| Code: |
/* REXX ACT */
/* INITIALISATIONS */
ADDRESS TSO
EXIT_MSG = "WITHUR.REX ENDED OK"
EXEC_LINE_NO = 0
WITH_UR_LINE_NO = 0
QRYCNT = 0
/* READ THE SOURCE INTO STEM "LINE.". */
"ALLOC F(INPUT) DSN('INPUT.PDS(MEMBER)') SHR"
/*"EXECIO * DISKR INPUT (FINIS STEM LINE." */
"EXECIO * DISKR INPUT (STEM LINE. FINIS"
"FREE F(INPUT)"
IF RC <> 0
THEN DO
EXIT_MSG = "EXECIO FAILED WITH RC = "RC
SIGNAL DOOR
M
END
DO I = 1 TO LINE.0
IF WORDPOS("EXEC SQL",LINE.I) > 0
THEN
CALL PROCESS_EXEC_GROUP
END
DOOR:
DROP LINE.
SAY EXIT_MSG
EXIT
/*===================================================================*/
PROCESS_EXEC_GROUP:
/* SAY WHERE WE ARE */
EXEC_LINE_NO = I
/* SAY "EXEC FOUND AT LINE "EXEC_LINE_NO */
/* FIND THE END-EXEC ASSOCIATED WITH THIS EXEC */
DO I = (I + 1) TO LINE.0
TEMP = TRANSLATE(LINE.I," ",".")
IF (WORDPOS("END-EXEC", TEMP) > 0)
THEN DO
END_EXEC_LINE_NO = I
LEAVE
END
END
/* FIND IF THIS EXEC EXECUTES A SELECT */
DO X = EXEC_LINE_NO TO END_EXEC_LINE_NO
IF (WORDPOS("SELECT",LINE.X) > 0)
THEN DO
SELECT_LINE_NO = X
/* SAY "SELECT FOUND AT LINE "SELECT_LINE_NO */
END
END
/* IF SELECT WAS FOUND THEN SEE IF THIS BLOCK CONTAINS WITH UR */
IF SELECT_LINE_NO > 0
THEN DO
DO X = SELECT_LINE_NO TO END_EXEC_LINE_NO
IF (WORDPOS("WITH UR",LINE.X) > 0)
THEN DO
WITH_UR_LINE_NO = X
IND=Y
/* SAY "WITH UR FOUND AT LINE "WITH_UR_LINE_NO */
END
END
IF WITH_UR_LINE_NO = 0
THEN
DO
QRYCNT = QRYCNT + 1
QRYREC.QRYCNT="WITH UR NOT FOUND FOR EXEC AT LINE ",
EXEC_LINE_NO
"ALLOC F(OUTDD) DA('HLQ.TEST) SHR"
"EXECIO * DISKW OUTDD (STEM QRYREC. FINIS"
"FREE F(OUTDD)"
END
END
/* RESET ALL LINE POSITIONS USED IN PROCEDURE */
EXEC_LINE_NO = 0
SELECT_LINE_NO = 0
WITH_UR_LINE_NO = 0
END_EXEC_LINE_NO = 0
STRING=" "
RETURN
/*===================================================================*/
|
|
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| You need to re-read my post carefully and place your output statements after the loop in the main program but before the door: label. Do not write * records but the number in your counter (which should be your output_stem.0). When finding out if the WITH UR is present or not use an IF/THEN/ELSE construct to write to one stem for' WITH UR' and another stem for without 'WITH UR'. One run covers both options. You can write the two stems to the same, or different datasets. If the same then do not use FINIS when writing the first stem, otherwise your second write will over-write the first. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
If syntax parsers were designed in such manner then none of compilers would ever work correctly...  |
|
| Back to top |
|
|
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1315
Location: Vilnius, Lithuania
|
|
|
|
I'm sure the DB2 precompiler generates different parameters for a call to the DB2 interface, for those cases where "WITH UR" is or isn't present. Scan the listing, and not a free-format source file!
And maybe use that obscure IBM utility, SRCHFOR...
Sheesh, another thread going nowhere.
AND REXX DOES NOT THROW ANYTHING! |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| I may be late to this thread, but is there any reason why the TS cannot look at the ISOLATION column in SYSIBM.SYSSTMT? Easier than parsing source code, IMHO. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
Yes, well. I do not see in the SRCHFOR documentation how it can: find a keyword on one line, then find another keyword on a different line, ensure that the second keyword comes before a third keyword and, finally, then write to 2 different report data sets.
As to using the SYSSTMT table, I do not know. Maybe need another hour of research. |
|
| Back to top |
|
|
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1315
Location: Vilnius, Lithuania
|
|
|
|
ISRSUPC has an option to find lines and context, but just doing a search for EXEC, END-EXEC and "WITH UR" should already greatly reduce the amount of data to be processed.
Anyway, the Db2 catalog tables should have all the info in a much easier way! |
|
| Back to top |
|
|
|
|
