CA RC/Extract generate column names where SELECT(*) is used in a VIEW for a data load
Article ID: 128459
CA RC/MigratorCA Endevor SCM InterfaceDB2 AdministrationCA RC Compare for DB2 for z/OSCA RC Extract for DB2 for z/OSCA RC/QueryCA RC Secure for DB2 for z/OSCA RC Update for DB2 for z/OS
After data has been extracted from a source the next stage is to create a target object and then perform a load of that data to the target objects. CA RC/Extract for DB2 for z/OS is able to extract the DDL of the source objects as well as the source data for processing on the target location.
If a DB2 VIEW is used as a primary object during the extract of the data it can often be that the original VIEW definition does not list a column list.
CREATE VIEW AUTHID.ALL_STAFF_ASTER AS SELECT * FROM AUTHID.TBEMP A ;
When an extract is targeting a new location it may be better to generate VIEW DDL that contains the column list used in the source object to avoid problems during the load since the target table referenced by the view might have a varying column list.
CREATE VIEW AUTHID.ALL_STAFF_ASTER ( EMPNO , FIRST_NAME , MIDDLE_NAME , LAST_NAME , WORK_GROUP , PHONE_NO , SEX ) AS SELECT * FROM AUTHID.TBEMP A ;
Db2 for z/OS
A parameter in the hlq.CDBAPARM(OFS) parmlib called "Generate column names with SELECT(*)" has an impact on how RC/Extract generates DDL. Other functions such as the RC/Query line commands DDL and HDDL are likewise impacted by the parameter.
GENERATE_COLUMN_NAMES_WITH_SELECT(*) (?) It can have three values: O (Original): To retrieve the original CREATE VIEW text that is used by the user to create the view. Y (Yes): To generate all the column names in a CREATE VIEW statement with select * N (No): To suppress all the column names in a CREATE VIEW statement with select *
This new option extracts the original view text from SYSIBM.SYSVIEWS and generates a column list when the original CREATE VIEW statement has a column list. O is the new default and recommended setting.
When GENERATE_COLUMN_NAMES_WITH_SELECT(*) is set to O, the original DDL of the view is extracted. When GENERATE_COLUMN_NAMES_WITH_SELECT(*) is set to "Y" all the columns from the original table are extracted for the view in a column list.