PM34738: GEN/DDL CREATE VIEW HAS SCHEMA FROM TABLE NOT VIEW FOR OBJECTS DEFINED WITHOUT A QUALIFIER

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Administration Tool for z/OS' GEN function creates
  CREATE VIEW syntax which uses the tables schema rather than
  the original view schema when the objects are defined like the
  following:
  SET CURRENT SQLID = 'P34738A';
  CREATE TABLE P34738A.P34738T (C1 INT);
  SET CURRENT SQLID = 'P34738B';
  CREATE SYNONYM P34738T FOR P34738A.P34738T;
  CREATE VIEW P34738B.P34738VW AS SELECT * FROM P34738T;
  

Local fix

• Recreate VIEW without a qualifier, or recreate the VIEW on DB2
  9 ( in new function mode ) or higher.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration Tool for     *
  *                 z/OS who use the Generate (GEN) function     *
  *                 to generate DDL statements for a view that   *
  *                 was created on a synonym, the synonym has    *
  *                 the same name as its dependent table, and    *
  *                 the view was created prior to DB2 9.         *
  ****************************************************************
  * PROBLEM DESCRIPTION: In DB2 Administration Tool for z/OS,    *
  *                      when using the GEN function to          *
  *                      generate DDL statements for a view      *
  *                      that was created on a synonym, the      *
  *                      synonym had the same name as it's       *
  *                      dependent table, and the view was       *
  *                      created prior to DB2 9, the             *
  *                      generated DDL statements used the       *
  *                      qualifier of the table as the SQLID     *
  *                      rather than the qualifier of the        *
  *                      synonym.                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  In DB2 Administration Tool for z/OS, when using the GEN function
  to generate DDL statements for a view that was created on a
  synonym, the synonym had the same name as it's dependent table,
  and the view was created prior to DB2 9, the generated DDL
  statements used the qualifier of the table as the SQLID rather
  than the qualifier of the synonym.
  

Problem conclusion

• A new parameter IMPLQUALMETHOD was added, and this parameter
  assists the GEN function to determine the run SQLID for the
  generated CREATE VIEW DDL statement, if the view has the
  following two attributes:
  
  1-The view was created in a DB2 release prior to DB2 9.
  2-The view was created based on a synonym or an alias and
    the synonym or alias was unqualified when the view was
    created or the synonym or alias has the same name as it's
    dependent table.
  
  There are two valid values for IMPLQUALMETHOD: 'C' and 'O'.
  The customer can specify or not specify this parameter
  in their GEN JCL. Do NOT specify this parameter except
  when dealing with the VIEW's defined as described earlier.
  
  When specifying value 'O' for the IMPLQUALMETHOD parameter
  or if no value is specified, the GEN function will find all
  objects in the CATALOG that have the same name with the
  unqualified dependent object, including the type of table,
  synonym or alias.  If multiple objects are found, GEN will
  use the dependency table qualifier as the RUN SQLID for
  the CREATE VIEW statement.
  
  When specifying value 'C' for the IMPLQUALMETHOD parameter,
  the GEN function will also find all objects that have the same
  name with the unqualified dependent object. But if multiple
  objects are found, GEN will use the view's qualifier as the RUN
  SQLID for the CREATE VIEW statement instead of the dependent
  table's qualifier.
  
  Customers are required to add the new parameter in their GEN
  JCL manually, for example:
  
  //SYSTSIN  DD *
    DSN SYSTEM(ssid)
    RUN PROG(ADB2GEN) PLAN(ADB2GEN) -
    PARMS('/REBIND')
    END
  
  //SYSPRINT DD SYSOUT=*
  //SQL      DD SYSOUT=*,DCB=(RECFM=FB,LRECL=80)
  //IN       DD *
    DB2SYS   = 'ssid',
    DB2ALOC  = '',
    DB2SERV  = 'serv',
    IMPLQUALMETHOD = 'C',
    ......
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK78293 UK78294

Modules/Macros

•    ADBHGENO ADBHP    ADBPGENO ADB2GEN  ADB2GENS
  ADB2GEN2 ADB2GEN6 ADB2P    ADB2REE  ADB2REM  ADB2REP
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA10 PSY UK78293

     UP12/04/28 P F204

• R720 PSY UK78294

     UP12/04/28 P F204

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.