CA RC/Extract generate column names where SELECT(*) is used in a VIEW for a data load

book

Article ID: 128459

calendar_today

Updated On:

Products

CA RC/Migrator CA Endevor SCM Interface DB2 Administration CA RC Compare for DB2 for z/OS CA RC Extract for DB2 for z/OS CA RC/Query CA RC Secure for DB2 for z/OS CA RC Update for DB2 for z/OS

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. 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
;



 

Environment

Db2 for z/OS

Resolution

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.
 

Additional Information

RC/Extract New Product Features and Enhancements