This can be done with two CA Database Management for DB2 for z/OS products.
1. Interactive SQL in the Value Pack
2. CA SQL-Ease for DB2 for z/OS
Interactive SQL in the Value Pack comes for free with any other purchased product. CA SQL-Ease for DB2 for z/OS must be licensed to be used.
Example SQL this article will work with as a sample.
This SQL demonstrates how complex SQL can be hard to read when poorly formatted for reading.
000001 SELECT A.COLLID , A.NAME , A.SEQNO , A.STMTNO ,
000002 A.SECTNO , A.STMTNOI ,
000003 A.SECTNOI , A.VERSION , A.STMT , B.OWNER
000004 , A.STATUS , A.EXPLAINABLE ,
000005 A.QUERYNO , B.TYPE
000006 FROM SYSIBM.SYSPACKSTMT A , SYSIBM.SYSPACKAGE B
000007 WHERE ( A.LOCATION = B.LOCATION AND A.COLLID = B.COLLID
000008 AND A.NAME = B.NAME
000009 AND A.CONTOKEN = B.CONTOKEN ) AND A.LOCATION = ' '
000010 AND STRIP (
000011 A.COLLID , T ) LIKE 'PACKVER_TEST' AND
000012 A.NAME = 'PAUTHID' AND
000013 B.OWNER = 'AUTHID' AND
000014 A.VERSION = '2007-02-21-22.214.171.1249754'
000015 ORDER BY A.NAME , A.COLLID , A.VERSION , A.STMTNO
000016 , A.STMTNOI , A.SECTNOI ,
(1) Interactive SQL(ISQL)
At the main menu of ISQL use the "D - Dataset I/O" menu item to go to the Dataset I/O Specification screen.
Then use the "I - read input dataset" function along with your Input dataset: DATA SET NAME to read your SQL into the edit area.
Then use the "E - Edit SQL" to start the edit session.
When on the SQL Editor screen type in the COMMAND ===> STAND
This is the result:
FROM SYSIBM.SYSPACKSTMT A
, SYSIBM.SYSPACKAGE B
WHERE ( A.LOCATION
= B.CONTOKEN )
= ' '
AND STRIP ( A.COLLID , T ) LIKE 'PACKVER_TEST'
ORDER BY A.NAME
The SQL above is laid out in a much more understandable fashion.
At this point you can enter the COMMAND ===> ISPFEDIT
to start up an ISPF edit session to save off this reformatted SQL to another location.
The CA SQL Editor has commands DCOPY, DCREATE, DREPLACE to do this also.
(2) CA SQL-Ease
At the CA SQL-Ease main menu enter the dataset details containing the sql in either the ISPF LIBRARY section or the Data Set Name field.
Next you will see an ISPF EDIT session containing your SQL.
Type in the Command ===> SQLEASE at the top of the screen.
DON'T HIT ENTER YET!
Type "EE" over the first two columns of the ISPF line number of the first and last line of the SQL in the same place as you enter ISPF line commands.
(From the original sample SQL above)
EE0001 SELECT A.COLLID , A.NAME , A.SEQNO , A.STMTNO ,
At the CA SQL-EASE Main Menu select the "8 - STAND - Standardize SQL Format".
The next screen is the CA SQL Editor.
Here again you can either enter into ispf edit using the COMMAND ===> ispfedit
to start up an ISPF edit session and save off this reformatted SQL to another location. The CA SQL Editor commands DCOPY, DCREATE, DREPLACE commands can do this also.
The Formatted result is the same as in METHOD (1) using ISQL above.