SQL-Ease : Reformat existing poorly formatted SQL statements for legibility?

Products

• SQL-Ease for DB2 for z/OS

Issue/Introduction

Is there a function which is able to reformat existing poorly formatted SQL statements so that they are more legible?

The SQL language can be a notoriously hard one to read if formatted poorly by a writer or sometimes by a SQL generating routine. To assist a user as in most computer languages SQL has some accepted layouts that have been shown to improve legibility. Writers of SQL or any computer language should be mindful of the format they use but in some cases this pre-written SQL can be reformatted.

Environment

Release: R20
Component: ISQL, SQE

Resolution

The ISQL product has a subcomponent called the SQL editor. The SQL Editor is also called by SQL-EASE and RC/Update in order to process SQL prior to execution.  The SQL Editor has a command called "STAND" which is able to reformat SQL that has either been written during the current session or in fact exists already on a dataset.

Steps to take:

1. Use Main Menu item "I" to enter ISQL(Interactive SQL)
   
   
2. Use menu item "E" Edit SQL to enter the SQL Editor.
   
   
3. At this point there are two options. Continue to use the SQL Editor or enter ISPF Edit.

 

SQL Editor Steps:

1. In the COMMAND line we need to copy in the existing SQL using the DCOPY command.
   
   
2. Enter the DCOPY 'dataset(member)' in the command line and place an "A" on the first line in the edit area on the first underscore. A__ as you would with a ISPF "C" line command.
   
   
3. When the SQL is displayed on the screen enter the "STAND" command in the command line. This will reformat the SQL to command in the command line. This will reformat the SQL to a standard form and should be more legible.
   
   
4. Then on the command line use the DREPLACE or DCREATE command and specify the target dataset(member) and utilizing C or CC line commands to select the text as would be used for the CREATE or REPLACE ISPF command to store the reformatted text on a dataset.

Additional Information

Standardize SQL

See also : SQL Editor  for the description of the commands available.   

From the online help:

================================================================

COMMAND     : DCOPY

DESCRIPTION : The DCOPY command allows you to import a member of a PDS or
              sequential dataset into the RC/SQL Editor.

              To use the command, you must specify where in the SQL Editor you
              want the member imported using the A (After) or B (Before) line
              command; then enter the DCOPY command in the command area and
              press <Enter>.

              You can specify the dataset and member to be copied in the
              command syntax, if desired. For example,
              DCOPY 'HLQ.KAT.TEST(MEMBER1)'. If you do not specify a member
              name, a member selection list will be displayed. If you do not
              specify a dataset, the SQL Editor Copy screen will appear.

SYNTAX      : DCOPY {'datasetname(member)'}

ABBREVIATION: DCOPY

================================================================

COMMAND     : DCREATE

DESCRIPTION : The DCREATE command allows you to move or copy selected
              text to a new member of a PDS.

              To use the command, block off the text in the SQL Editor. If you
              block off the text using a C command or a set of CC ... CC
              commands, the text will be copied to the dataset. If you block
              off the text using an M command or a set of MM ... MM commands,
              the text will be moved to the dataset.

              You can specify the dataset member to be created in the command
              syntax, if desired. For example,
              DCREATE 'HLQ.Q97.TEST(MEMBER1)'.
              If you do specify a member name, it must be a member that does
              not already exist.

SYNTAX      : DCREATE {'datasetname(member)'}

ABBREVIATION: DCREATE

================================================================

COMMAND     : DREPLACE

DESCRIPTION : The DREPLACE command is used to replace a member of a PDS or
              sequential dataset with lines blocked off in the RC/SQL Editor.

              To use the command, block off the text in the SQL Editor. If you
              block off the text using a C command or a set of CC ... CC
              commands, the text will be copied to the dataset. If you block
              off the text using an M command or a set of MM ... MM commands,
              the text will be moved to the dataset. The current contents of
              the dataset member will be erased.

              You can specify the dataset member to be replaced in the command
              syntax. For example, DREPLACE 'HLQ.Q97.TEST(MEMBER1)'. If you
              do not specify a member name, a member selection list displays.
              If you omit the dataset, the SQL Editor Replace screen appears.

SYNTAX      : DREPLACE {'datasetname(member)'}

ABBREVIATION: DREPLACE

================================================================

COMMAND     : STAND

DESCRIPTION : The STAND command reformats SQL SELECT, UPDATE, INSERT and DELETE
              statements into a more legible format.  Shown below is a sample
              SQL statement before and after standardization:

              BEFORE:   SELECT A.EMPNO, A.LASTNAME, A.WORKDEPT FROM
                        DSN8220.EMP A WHERE A.EMPNO = '000100'

              AFTER:    SELECT  A.EMPNO
                              , A.LASTNAME
                              , A.WORKDEPT
                          FROM  DSN8220.EMP A
                          WHERE A.EMPNO = '000100'

              The STAND command is available only when the SQL editor is
              invoked by SQL-Ease.

ABBREVIATION: STAND

================================================================