Generate column names where SELECT(*) is used in a VIEW for a data load using RC/Extract
Article ID: 128459
Updated On:
Products
Issue/Introduction
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.
RC/Extract for Db2 for z/OS (RCX) 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.view AS
SELECT * FROM authid.table 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.view
( col1
, col2
, col3
, col4
, col5
, col6
, col7
) AS
SELECT * FROM authid.table A
;
Resolution
A parameter in the hlq.CDBAPARM(OFS) parmlib member 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.
Additional Information
Feedback
