Column Suffix on Table Column DCLGEN within SQL-Ease
search cancel

Column Suffix on Table Column DCLGEN within SQL-Ease


Article ID: 54559


Updated On:


SQL-Ease for DB2 for z/OS


When a DCLGEN of a table is generated, is it possible to specify that the generated host variables and null indicator
variable names have a suffix appended instead of a prefix?

When a DCLGEN is generated with the IBM supplied DCLGEN facility, a user has the option to place a prefix before the host variable name.
eg. Where the Db2 column name is ACCT_CO_NBR the user might choose to have "WK-" appended to the front of it thus: WK-ACCT-CO-NBR

The user may choose any prefix they require. This question relates to being able to place "WK-" as a suffix thus: ACCT-CO-NBR-WK
The IBM DCLGEN facility does not provide this option either for host variables or null indicator variables.


Release: r20
Component: SQL-Ease for Db2 for z/OS


The SQL-Ease for Db2 for z/OS (SQE) DCLGEN facility is able to place a suffix at the end of the column name so
that ACCT-CO-NBR can be generated as ACCT-CO-NBR-WK or any suffix required by the user. This suffix option
also extends to the situation where a user has chosen to generate host variables as a column number. So if ACCT-CO-NBR
is the first column in the data structure SQLEASE can produce WK-1 or 1-WK as a host variable where standard DCLGEN
will only produce WK-1.

Ensure that the Default Host Language in the General Execution Parameters of the SQL-EASE Profile is set to COB. 

Generate DCLGEN using a command line command

The fastest way to generate a DCLGEN of a table and place the result into the ISPF edit session is with the GEN command.

Enter into SQLEASE, enter a PDS dataset and NEW member name, and an ISPF edit session is started in an empty edit session ready to give it commands.

In the ISPF edit command line, enter SQLEASE GEN creator.tablename DCL where creator.tablename is an existing table on the subsystem. 

This will generate a DCLGEN and place the result into the existing ISPF edit session. It can then be saved and used.

Generate DCLGEN using a the GEN menu item

Access the main SQLEASE menu item "GEN - Generate SQL Statements" using the SQLEASE command in the ISPF command line as before.

Select "1 - GEN     - Generate SQL Statements". This screen can produce a list of objects based on the selection criteria.

Enter an Item Name, Creator and correct SSID. When the item is listed, place an "S" in the "DCL" column of an object and entering
the "GEN" command will generate the DCLGEN code into an ISPF session.

PF3 two screens to the ISPF session to view the results.

SQGLST1 rr.r  --------- SQL-EASE Statement Generator --------- yyyy/mm/dd hh:mm
COMMAND ===>                                                  SCROLL ===> CSR
                                                              LINE 1 OF 1  >
Item Name ===> TBDEPT             Creator ===> authid  Where ===> N
DB2 SSID  ===> ssid                                     Version:   121M500
---------------------------------------------------------------------- authid
 Select the SQL statement(s) that are to be generated by entering
 an S or a C at the appropriate "Statement Type" prompts.
 S - include all columns in the SQL stmt, C - view a column selection list

    ------ Object Information -------  ----------- Statement Type ------------
                                       D         M
                                       C         R
___ TBDEPT             authid   T     S _ _ _ _ _  _ ___  __________________

Generate DCLGEN using the SQLEASE Batch JCL

The batch JCL to run this facility is provided with the product and can be found on the hlq.CDBAJCL(SQEBATCH) location.
Instructions for using it are included in the member.

Individually prefixed/suffixed Null indicator variables.

One other function available with SQLEASE not included on the standard DCLGEN includes individually prefixed/suffixed Null indicator
variables. Normal DCLGEN does not provide a prefix/suffix but has an array rather than individual host variables for null indicators.
So with SQLEASE for each column in the table that can be NULL an individually named NULL indicator host variable is generated
in it's own host variable structure.

Other Relevant Information

The host variable tags(prefix/suffix) information to be used for online DCLGEN generation can be found on the SQLEASE PROFILE
under the menu item "SQL-Ease Host Variable Generation Parameters". This is valid for the online user only.

SQL GEN Administration
Using the "SQL-Ease Host Variable Generation Parameters" function a user can create a record for each individual table containing
individually saved preferences for these host variable and null indicator variable tags. These preferences are automatically used for
that table when a DCLGEN or other statements are generated. These are also available to other users!!!

Both the Online DCLGEN and the batch SQEBATCH facilities take notice of the settings defined in the "SQL-Ease Host Variable Generation Parameters".
Settings made in the PROFILE or in the batch JCL are OVERRIDDEN by the "SQL-Ease Host Variable Generation Parameters" settings for that OBJECT ALONE.

The symbolics %TBNAME and %TBCREATOR are available to be used in any of the USER PROFILE, SQL-Ease Host Variable Generation
Parameters or SQEBATCH page settings and host structure name settings.

Example Output showing the table which has three nullable columns. The settings have been set to SUFFIX the column names in the WS
with the table name. The two 01 levels have also been suffixed with the table name and a piece of text to indicate what they are.

* authid.TBDEPT
*   authid.TBDEPT
*   GENERATED ON yyyy/mm/dd AT hh:mm
     (DEPT_ID            CHAR(6)     NOT NULL
     ,DEPT_DESC          CHAR(50)    NOT NULL
     ,DEPT_MGR           CHAR(6)
*   authid.TBDEPT
     10  DEPT-ID-WK-TBDEPT      PIC X(6).
     10  DEPT-DESC-WK-TBDEPT    PIC X(50).
     10  DEPT-MGR-WK-TBDEPT     PIC X(6).
                                PIC X(30).
                                PIC X(30).
     10  DEPT-MGR-IND-TBDEPT    PIC S9(4)        USAGE COMP.
 ************************************************************** This is what it looks like in the PROFILE, SQL-Ease Host Variable Generation Parameters Host Variable Generation Parameters:
Host Structure Name ===> WORK-%TBNAME
Host Variable Tag ===> -WK-%TBNAME
Merge Variable Tag ===>
Prefix / Suffix ===> S P/S - Tag Position on Host Variable Name.
Indicator Structure Name ===> NULL-IND-STRUC-%TBNAME
Indicator Variable Tag ===> -IND-%TBNAME
Merge Indicator Tag ===> _MI
Prefix / Suffix / Host ===> S P/S/H - Tag Position on Indicator Name.
Generate Column Names ===> Y Y/N - Use column names NOT column numbers.
Attach Structure Name ===> Y Y/N - Attach structure to host variable.
XML Preference ===> C B/C/D - XML Vars as BLOB, CLOB or DBCLOB.

Additional Information

Generate SQL

SQL Statement Generator Commands

Set the Profile Variables