IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

filter COMMIT/ROLLBACK statements
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
jzhardy

Active User


Joined: 31 Oct 2006
Posts: 139
Location: brisbane
PostPosted: Fri May 01, 2020 5:27 pm
 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

Active User


Joined: 31 Oct 2006
Posts: 139
Location: brisbane
PostPosted: Fri May 01, 2020 5:29 pm
 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

Senior Member


Joined: 15 Aug 2015
Posts: 1335
Location: Bamberg, Germany
PostPosted: Fri May 01, 2020 5:41 pm
 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

Active User


Joined: 31 Oct 2006
Posts: 139
Location: brisbane
PostPosted: Sat May 02, 2020 5:23 am
 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

Active User


Joined: 31 Oct 2006
Posts: 139
Location: brisbane
PostPosted: Sat May 02, 2020 5:25 am
 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

Active User


Joined: 31 Oct 2006
Posts: 139
Location: brisbane
PostPosted: Sat May 02, 2020 7:30 am
 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

Senior Member


Joined: 15 Aug 2015
Posts: 1335
Location: Bamberg, Germany
PostPosted: Sat May 02, 2020 11:53 am
 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

Senior Member


Joined: 15 Aug 2015
Posts: 1335
Location: Bamberg, Germany
PostPosted: Sun May 03, 2020 2:30 am
 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

Active User


Joined: 31 Oct 2006
Posts: 139
Location: brisbane
PostPosted: Sun May 03, 2020 12:25 pm
 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

Active User


Joined: 31 Oct 2006
Posts: 139
Location: brisbane
PostPosted: Sun May 03, 2020 12:35 pm
 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

Active User


Joined: 31 Oct 2006
Posts: 139
Location: brisbane
PostPosted: Mon May 04, 2020 12:30 pm
 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

Senior Member


Joined: 29 Apr 2008
Posts: 2141
Location: USA
PostPosted: Mon May 04, 2020 4:48 pm
 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

Active User


Joined: 31 Oct 2006
Posts: 139
Location: brisbane
PostPosted: Tue May 05, 2020 2:19 am
 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

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
PostPosted: Tue May 05, 2020 5:30 pm
 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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Dynamically build sort control statem... SYNCSORT 18
No new posts Synchpoint Rollback CICS 1
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
No new posts Help to Filter File Manager Copybook ... DFSORT/ICETOOL 14
No new posts Filter records from a file DFSORT/ICETOOL 4
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us