Column names still generated in a view generated by DDL line command using RC/Query

Products

• RC/Query for DB2 for z/OS
• Database Management for DB2 for z/OS - Administration Suite

Issue/Introduction

Column names are still generated in the select statement for a view when the RC/Query for Db2 for z/OS (RCQ) DDL line command is used
even when the hlq.CDBAPARM(OFS) option  GENERATE_COLUMN_NAMES_WITH_SELECT(*) is set to "N".

Cause

Column names are suppressed only when a full select is used with SELECT * and GENERATE_COLUMN_NAMES_WITH_SELECT(*) is (N) in hlq.CDBAPARM(OFS) member.

Resolution

If column names are present in the select statement it is difficult to decide if all the columns from specified tables are used in the view or not.
So column names can be suppressed only if SELECT * is used in the full-select.

Example 1:
DDL line command output with GENERATE_COLUMN_NAMES_WITH_SELECT(*) (Y) where the original statement is:

CREATE VIEW AUTHID.TESTVIEW

        ( C1
        , C2
        , C3
        ) AS
SELECT 1 AS C1 , 2 AS C2 , 3 AS C3 FROM AUTHID.TESTTBL;

The column names are still generated because there was no SELECT * used.

CREATE VIEW AUTHID.TESTVIEW

        ( C1
        , C2
        , C3
        ) AS
SELECT 1 AS C1 , 2 AS C2 , 3 AS C3 FROM 
AUTHID.TESTTBL;

Example 2:

DDL line command output with with GENERATE_COLUMN_NAMES_WITH_SELECT(*) (Y) where the original statement is:

CREATE VIEW AUTHID.TESTVIEW AS
SELECT * from AUTHID.TESTTBL;

The column names are generated because there was a SELECT * used.


CREATE VIEW AUTHID.TESTVIEW

        ( C1
        , C2
        , C3
        , C4
        , C5
        , C6
        , C7
        , C8
        ) AS
SELECT * from AUTHID.TESTTBL;

Example 3:

DDL Output with with GENERATE_COLUMN_NAMES_WITH_SELECT(*) (N) where the original statement is:


CREATE VIEW AUTHID.TESTVIEW AS
SELECT * from AUTHID.TESTTBL;

The column names are not generated because there was a SELECT * used and GENERATE_COLUMN_NAMES_WITH_SELECT(*) is (N).

CREATE VIEW AUTHID.TESTVIEW AS
SELECT * from AUTHID.TESTTBL;

Additional Information

Edit the OFS Product Parmlib Member

"Generate or suppress the column names in a CREATE VIEW statement with SELECT(*) "

The online documentation shows this:

Field:  Generate column names with SELECT(*)
Description:  Specify whether to generate or suppress the column
              names in a CREATE view statement with SELECT(*)

The following values are valid:
              O - Retrieve the original CREATE VIEW text that is
                  used by user to create the view.

              Y - Generate column names in a CREATE VIEW statement
                  with select(*).

              N - Suppress column names in a CREATE VIEW statement
                  with select(*).

Default    :  O

This is reflected in the hlq.CDBAPARM(OFS) member.

GENERATE_COLUMN_NAMES_WITH_SELECT(*) (O)  /* 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 *           */