Column Suffix on Table Column DCLGEN

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

Description:

When I generate a DCLGEN of a table, am I able 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.

Solution:

The CA SQL-EASE 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.

The fastest way to generate a DCLGEN of a table and place the result into the ISPF edit session is with the GEN command. You must get into SQLEASE, then get to the ISPF edit session inside SQLEASE ready to give it commands. In the ISPF edit command line, enter SQLEASE GEN creator.tablename DCL

This will generate a DCLGEN for you and place the result into the ISPF edit session you are in. This can also be accessed via the menu item "GEN - Generate SQL Statements" which takes you in to a list of objects.
Placing an "S" in the "DCL" column of an object and entering the "GEN" command will generate the DCLGEN code into your ISPF session.

The batch JCL to run this facility is provided with the product and can be found on the hlq.CDBASRC(SQEBATCH) location.

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

User PROFILE
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.

******************************************************
*     TABLE DECLARATION FOR
*     creator.AE_STEPS
*     GENERATED ON yyyy/mm/dd AT hh:mm
******************************************************
EXEC SQL DECLARE
creator.AE_STEPS
TABLE
(CODE               CHAR(7) NOT NULL WITH DEFAULT
,STEP               INTEGER NOT NULL WITH DEFAULT
,LEVEL              INTEGER
,ENABLED            CHAR(1)
,COMMENT            CHAR(255)
)
END-EXEC.
************************************************************
* STORAGE DEFINITIONS FOR
 
* creator.AE_STEPS
************************************************************
01 WORK-AE-STEPS.
10 CODE-WK-AE-STEPS       PIC X(7).
10 STEP-WK-AE-STEPS       PIC S9(9) USAGE COMP.
10 LEVEL-WK-AE-STEPS      PIC S9(9) USAGE COMP.
10 ENABLED-WK-AE-STEPS    PIC X(1).
10 COMMENT-WK-AE-STEPS    PIC X(255).
************************************************************
* NULL INDICATOR VARIABLES
************************************************************
01 NULL-IND-STRUC-AE-STEPS.
10 LEVEL-IND-AE-STEPS     PIC S9(4) USAGE COMP.
10 ENABLED-IND-AE-STEPS   PIC S9(4) USAGE COMP.
10 COMMENT-IND-AE-STEPS   PIC S9(4) USAGE COMP.
********************** Bottom of Data **********************
 
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.