SQL is often written by hand but also sometimes generated by software. When it becomes substantial in size it can also be poorly written and therefore made difficult to read with the result that it may become difficult to maintain. Well formatted SQL can display the logical structure and aid in understanding.
Release: R20
Component: SQE
Poorly written or generated SQL.
This can be done with two Database Management for DB2 for z/OS products.
1. Interactive SQL in the Value Pack
2. SQL-Ease for DB2 for z/OS
Interactive SQL in the Value Pack comes for free with any other purchased product. 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 = 'yyyy-mm-dd-hh.mm.ss.mmmmmm'
000015 ORDER BY A.NAME , A.COLLID , A.VERSION , A.STMTNO
000016 , A.STMTNOI , A.SECTNOI ,
000017 A.SEQNO
Method:
(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:
SELECT A.COLLID
, A.NAME
, A.SEQNO
, A.STMTNO
, A.SECTNO
, A.STMTNOI
, A.SECTNOI
, A.VERSION
, A.STMT
, B.OWNER
, A.STATUS
, A.EXPLAINABLE
, A.QUERYNO
, B.TYPE
FROM SYSIBM.SYSPACKSTMT A
, SYSIBM.SYSPACKAGE B
WHERE ( A.LOCATION
= B.LOCATION
AND A.COLLID
= B.COLLID
AND A.NAME
= B.NAME
AND A.CONTOKEN
= B.CONTOKEN )
AND A.LOCATION
= ' '
AND STRIP ( A.COLLID , T ) LIKE 'PACKVER_TEST'
AND A.NAME
= 'PAUTHID'
AND B.OWNER
= 'AUTHID'
AND A.VERSION
= 'yyyy-mm-dd-hh.mm.ss.mmmmmm'
ORDER BY A.NAME
, A.COLLID
, A.VERSION
, A.STMTNO
, A.STMTNOI
, A.SECTNOI
, A.SEQNO
;
The SQL above is laid out in a much more legible 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 SQL Editor has commands DCOPY, DCREATE, DREPLACE to do this also.
More information about these commands is on the online help when in the SQL Editor screen using the "?" on the command line returns the list of commands. Enter "?" on the one you want to read about to obtain the full help text.
(2) SQL-Ease
At the 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 ,
.
.
.
EE0017 A.SEQNO
Then enter.
At the SQL-EASE Main Menu select the "8 - STAND - Standardize SQL Format".
The next screen is the 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 SQL Editor commands DCOPY, DCREATE, DREPLACE can do this also.
More information about these commands is on the online help. When in the SQL Editor screen using the "?" in the command line returns a list of commands. Enter "?" on the one you want to read about to obtain the full help text.
The Formatted result is the same as in METHOD (1) using ISQL above.
See the SQL-Ease Reference Guide and the Interactive SQL Reference.