|
View previous topic :: View next topic
|
| Author |
Message |
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 150
Location: brisbane
|
|
|
|
I have a need to comment out / remove all COMMIT / ROLLBACK statements in a set of SQL members.
I'll admit this is easiest done using REXX (which I have done), with or without a lexical parser for SQL. But as an academic exercise I was wondering if this is achievable for DFSORT or ICETOOL.
eg, suppose input is some (meaningless) SQL like the following:
----- COMMIT
coMmiT
-- some comment here
;
rollBAck ; commit ;
create session table xyz(...) on
commit
preserve
rows;
select 'COMMIT' from sysibm.sysdummy;
---
then, for output I would like the same, but with *only* the actual transaction commits and rollbacks blanked out, obviously with any statement terminators included (ie, ';'). The end result should be a bit of SQL that runs but has no embedded COMMIT or ROLLBACK. |
|
| Back to top |
|
 |
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 150
Location: brisbane
|
|
|
|
| one final comment: our shop does not have the latest version of SORT, so no regex findrep is possible |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1443
Location: Bamberg, Germany
|
|
|
|
What DFSORT level do you have?
Give a sample Input and a sample Output and PLEASE use code tags! |
|
| Back to top |
|
|
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 150
Location: brisbane
|
|
|
|
input is:
| Code: |
----- COMMIT
coMmiT
-- some comment here
;
rollBAck ; commit ;
create session table xyz(...) on
commit
preserve
rows;
select 'COMMIT' from sysibm.sysdummy;
--- |
output would be:
| Code: |
----- COMMIT
-- some comment here
create session table xyz(...) on
commit
preserve
rows;
select 'COMMIT' from sysibm.sysdummy;
--- |
|
|
| Back to top |
|
|
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 150
Location: brisbane
|
|
|
|
SORT version is V2R2.
(no Regex support in this one unfortunately) |
|
| Back to top |
|
|
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 150
Location: brisbane
|
|
|
|
one correction to the above. The comment within the commit statement should also be removed, so:
| Code: |
----- COMMIT
create session table xyz(...) on
commit
preserve
rows;
select 'COMMIT' from sysibm.sysdummy;
---
|
|
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1443
Location: Bamberg, Germany
|
|
|
|
Input:
| Code: |
//SIMPLE EXEC PGM=ICEMAN
//SORTIN DD *
----- COMMIT
coMmiT
-- some comment here
;
rollBAck ; commit ;
create session table xyz(...) on
commit
preserve
rows;
select 'COMMIT' from sysibm.sysdummy;
---
----- COMMIT
coMmiT
-- some comments here
-- more comments here
;
rollBAck ; commit ;
create session table xyz(...) on
commit
preserve
rows;
select 'COMMIT' from sysibm.sysdummy;
---
/*
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INREC IFTHEN=(WHEN=INIT,OVERLAY=(1:1,80,TRAN=LTOU)),
IFTHEN=(WHEN=INIT,OVERLAY=(50:+0,ZD,LENGTH=4)),
IFTHEN=(WHEN=GROUP,BEGIN=(1,12,CH,EQ,C'----- COMMIT'),
END=(1,12,CH,EQ,C'---'),PUSH=(45:SEQ=4)),
IFTHEN=(WHEN=GROUP,BEGIN=(1,20,CH,EQ,C'ROLLBACK ; COMMIT ;'),
END=(1,12,CH,EQ,C'---'),PUSH=(50:SEQ=4))
OUTFIL FNAMES=(SORTOUT),
INCLUDE=(45,9,CH,EQ,C'0001 0000',OR,50,4,ZD,GE,+2),
REMOVECC,
BUILD=(1,39)
END
/* |
Output:
| Code: |
----- COMMIT
CREATE SESSION TABLE XYZ(...) ON
COMMIT
PRESERVE
ROWS;
SELECT 'COMMIT' FROM SYSIBM.SYSDUMMY;
---
----- COMMIT
CREATE SESSION TABLE XYZ(...) ON
COMMIT
PRESERVE
ROWS;
SELECT 'COMMIT' FROM SYSIBM.SYSDUMMY;
--- |
|
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1443
Location: Bamberg, Germany
|
|
|
|
Minor update to the above. This leaves the original upper and lower case letters unchanged.
Input:
| Code: |
OPTION COPY
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(81:1,80,1:1,80,TRAN=LTOU,1:1,80,SQZ=(SHIFT=LEFT))),
IFTHEN=(WHEN=GROUP,BEGIN=(1,16,CH,EQ,C'ROLLBACK;COMMIT;'),
END=(1,12,CH,EQ,C'-----COMMIT'),PUSH=(161:SEQ=4))
OUTFIL FNAMES=(SORTOUT),
INCLUDE=(1,12,CH,EQ,C'-----COMMIT',OR,161,4,ZD,GE,+2),
REMOVECC,
BUILD=(81,80)
END |
Output:
| Code: |
----- COMMIT
create session table xyz(...) on
commit
preserve
rows;
select 'COMMIT' from sysibm.sysdummy;
---
----- COMMIT
create session table xyz(...) on
commit
preserve
rows;
select 'COMMIT' from sysibm.sysdummy;
--- |
|
|
| Back to top |
|
|
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 150
Location: brisbane
|
|
|
|
worked ok, but noticed this removed all comments with commit/rollback, not just those within commit/rollback,
so I changed the INCLUDE as follows:
| Code: |
INCLUDE=(1,12,CH,EQ,C'-----COMMIT',OR,161,4,ZD,GE,+2,OR,
1,2,CH,EQ,C'--'),
|
which worked fine ... however, maybe related, or maybe not, i noticed this does not handle final COMMIT/ROLLBACK (where the semi-colon is optional) - eg:
| Code: |
//SORTIN DD *
rollback; commit; rollback; -- retain comment
create session table xyz(...) on
commit
preserve
rows;
select 'COMMIT;' from sysibm.sysdummy;
commit ;
/* |
yielded:
| Code: |
-- retain comment
create session table xyz(...) on
commit
preserve
rows;
select 'COMMIT;' from sysibm.sysdummy;
commit ;
|
|
|
| Back to top |
|
|
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 150
Location: brisbane
|
|
|
|
thinking a bit further about this, i have probably made it overly complex.
possibly a safer and easier approach is to :
1. replace all COMMIT/ROLLBACK with a minimal NOP (no operation) command ... *unless* it is preceeded by a char that is not ";"
so :
| Code: |
-- some comment
commit
-- comment
; |
becomes:
| Code: |
-- some comment
NOP
-- comment
; |
NOP could be something like :select "COMMIT removed" from sysibm.sysdummy1.
Only issue here is that we may force line overflow; and I doubt very much whether there is a NOP that is less than 6 chars! |
|
| Back to top |
|
|
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 150
Location: brisbane
|
|
|
|
I have a sketch of a possible solution, but it does seem rather inelegant:
1. Remove comments
| Code: |
OPTION COPY
OUTFIL PARSE=(%00=(ENDBEFR=C'--',FIXLEN=80,PAIR=APOST)),
REMOVECC,
BUILD=(%00)
|
2. Convert to upper and remove spaces (as you did in your solution), put this @ 81,80
3. Remove multiple statements on the one line, something like :
| Code: |
OPTION COPY
OUTFIL PARSE=(%00=(STARTAT=NONBLANK,ENDBEFR=C';',FIXLEN=80,
PAIR=APOST,REPEAT=3),
%03=(FIXLEN=80)),
REMOVECC,
|
(what I cant work on the above is how to conditionally build based on whether a %NN parameter is all spaces or not)
so if input is:
I want:
| Code: |
a
a
;
a
;
b
;
a
;
b
;
|
4. Use GROUP/PUSH to isolate ‘real’ COMMIT/ROLLBACK statements |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
Just an advice.
When the input control statements do not follow any coding standards, but are in fact a free text mixed with some garbage, then it makes much sense to handle it using tools like REXX, rather than turning yourself inside out with the SORT utility. |
|
| Back to top |
|
|
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 150
Location: brisbane
|
|
|
|
Agree! hence my original comment
that aside, it's been a useful exercise as it's taught me a great deal about the capabilities of the sort utility |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3112
Location: NYC,USA
|
|
|
|
DFSORT/SYNCSORT capabilities are great for real data processing for large volumes and not really for formatting something which has so many variations.
One could do it with one or many steps but it could get ugly. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
