Improve formatting of poorly written/generated SQL

Products

• SQL-Ease for DB2 for z/OS
• Database Management for DB2 for z/OS - Administration Suite

Issue/Introduction

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.

Environment

Release: R20
Component: SQE

Cause

Poorly written or generated SQL. 

Resolution

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.
  

Additional Information

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