Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
filter COMMIT/ROLLBACK statements

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 73
Location: brisbane

PostPosted: Fri May 01, 2020 5:27 pm    Post subject: filter COMMIT/ROLLBACK statements
Reply with quote

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
View user's profile Send private message

jzhardy

New User


Joined: 31 Oct 2006
Posts: 73
Location: brisbane

PostPosted: Fri May 01, 2020 5:29 pm    Post subject:
Reply with quote

one final comment: our shop does not have the latest version of SORT, so no regex findrep is possible
Back to top
View user's profile Send private message
Joerg.Findeisen

Active User


Joined: 15 Aug 2015
Posts: 250
Location: Bamberg, Germany

PostPosted: Fri May 01, 2020 5:41 pm    Post subject:
Reply with quote

What DFSORT level do you have?
Give a sample Input and a sample Output and PLEASE use code tags!
Back to top
View user's profile Send private message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 73
Location: brisbane

PostPosted: Sat May 02, 2020 5:23 am    Post subject:
Reply with quote

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
View user's profile Send private message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 73
Location: brisbane

PostPosted: Sat May 02, 2020 5:25 am    Post subject:
Reply with quote

SORT version is V2R2.

(no Regex support in this one unfortunately)
Back to top
View user's profile Send private message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 73
Location: brisbane

PostPosted: Sat May 02, 2020 7:30 am    Post subject:
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

Active User


Joined: 15 Aug 2015
Posts: 250
Location: Bamberg, Germany

PostPosted: Sat May 02, 2020 11:53 am    Post subject:
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

Active User


Joined: 15 Aug 2015
Posts: 250
Location: Bamberg, Germany

PostPosted: Sun May 03, 2020 2:30 am    Post subject:
Reply with quote

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
View user's profile Send private message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 73
Location: brisbane

PostPosted: Sun May 03, 2020 12:25 pm    Post subject:
Reply with quote

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
View user's profile Send private message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 73
Location: brisbane

PostPosted: Sun May 03, 2020 12:35 pm    Post subject:
Reply with quote

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
View user's profile Send private message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 73
Location: brisbane

PostPosted: Mon May 04, 2020 12:30 pm    Post subject:
Reply with quote

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:
Code:
a
a;
a;b
;
a;b;

I want:
Code:
a
a
;
a
;
b
;
a
;
b
;


4. Use GROUP/PUSH to isolate ‘real’ COMMIT/ROLLBACK statements
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 778
Location: Maryland

PostPosted: Mon May 04, 2020 4:48 pm    Post subject:
Reply with quote

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
View user's profile Send private message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 73
Location: brisbane

PostPosted: Tue May 05, 2020 2:19 am    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2387
Location: NY,USA

PostPosted: Tue May 05, 2020 5:30 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Please help - SORT with SUM fields an... clearskynot DFSORT/ICETOOL 6 Thu Apr 09, 2020 11:01 am
No new posts SMS:- Efficient filter lists in ACS Ravi GA All Other Mainframe Topics 6 Tue Mar 31, 2020 11:02 pm
No new posts Rexx to create VSAM define statements Dinesh Mani CLIST & REXX 10 Fri Nov 29, 2019 9:26 pm
No new posts Summing records conditional statements. chillmo SYNCSORT 13 Wed May 15, 2019 2:12 am
No new posts Remove all Parenthetical Statements -... JayaprakashT DB2 2 Fri Apr 27, 2018 3:57 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us