Column Suffix on Table Column DCLGEN within SQL-Ease
Article ID: 54559
Updated On:
Products
Issue/Introduction
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.
Environment
Release: r20
Component: SQL-Ease for Db2 for z/OS
Resolution
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
CMD NAME CREATOR TYPE L S U D I G J SEQ CURSOR-NAME
___ 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
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.
***********************************************************
*
* authid.TBDEPT
***********************************************************
************************************************************
* TABLE DECLARATION FOR
* authid.TBDEPT
* GENERATED ON yyyy/mm/dd AT hh:mm
************************************************************
<TABLE-DECLARATION>
EXEC SQL DECLARE
authid.TBDEPT
TABLE
(DEPT_ID CHAR(6) NOT NULL
,DEPT_DESC CHAR(50) NOT NULL
,DEPT_MGR CHAR(6)
,DEPT_ADDRESS CHAR(30) NOT NULL WITH DEFAULT
,DEPT_ADDRESS_TWO CHAR(30) NOT NULL WITH DEFAULT
,DEPT_ADDRESS_THREE CHAR(30) NOT NULL WITH DEFAULT
)
END-EXEC.
************************************************************
* STORAGE DEFINITIONS FOR
* authid.TBDEPT
************************************************************
<HOST-VARIABLES>
01 WORK-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).
10 DEPT-ADDRESS-WK-TBDEPT PIC X(30).
10 DEPT-ADDRESS-TWO-WK-TBDEPT
PIC X(30).
10 DEPT-ADDRESS-THREE-WK-TBDEPT
PIC X(30).
***************************************************************
* NULL INDICATOR VARIABLES
***************************************************************
<NULL-INDICATORS>
01 NULL-IND-STRUC-TBDEPT.
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
Feedback
