How do I improve the formatting of poorly written SQL with CA DB2 tools for z/OS?

Document ID : KB000011793
Last Modified Date : 14/02/2018
Show Technical Document Details
Question:

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.  Is there a function I can use that could reformat this SQL for me so it is better able to be read?

 

Answer:

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-19.58.36.689754'
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
      = '2007-02-21-19.58.36.689754'
  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 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 ,
   .
   .
   .
   EE0017         A.SEQNO
   
   Then enter.


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.
  

Additional Information:

See the CA SQL-Ease Reference Guide and the Interactive SQL Reference.