PI32291: RENAME TABLE ENHANCEMENT

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• RENAME TABLE does not work when VIEW exists on table
  

Local fix

• drop view before rename table
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 11 for z/OS users of RENAME TABLE        *
  ****************************************************************
  * PROBLEM DESCRIPTION: A DB2 restriction causes RENAME TABLE   *
  *                      to fail with SQLCODE -750 when the      *
  *                      table is referenced by a view or SQL    *
  *                      table function.                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 currently restricts renaming a table that is referenced in
  the definition of a view or SQL table function. Users will
  receive SQLCODE -750 when attempting to execute RENAME TABLE on
  such a table.
  

Problem conclusion

Temporary fix

Comments

• DB2 is modified to:
  - add a new subsystem parameter in DSN6SPRM called RENAMETABLE,
  - remove the restriction and allow renaming a table referenced
    by a view or SQL table function if RENAMETABLE is set to
    ALLOW_DEP_VIEW_SQLTUDF. The traditional behavior is maintained
    when RENAMETABLE is set to the default of
    DISALLOW_DEP_VIEW_SQLTUDF.
  
  The DB2 online reference is updated to describe:
  - the behavior of successfully renaming a table referenced by a
    view or SQL table function,
  - the valid RENAMETABLE subsystem parameter settings and their
    individual behavior.
  
  The following occurs if: subsystem parameter RENAMETABLE is set
  to ALLOW_DEP_VIEW_SQLTUDF, the user attempts to rename a table
  that is referenced in the definition of a view or SQL table
  function, and no other restrictions apply:
  - SQLCODE -750 is not issued,
  - the renamed table's record in SYSTABLES is updated with the
    new table name,
  - dependency records in the catalog are updated to refer to the
    new table name,
  - dependent view and SQL table function records in SYSVIEWS and
    SYSROUTINES are updated to reflect the new table name,
  - dependent views and functions are regenerated,
  - packages that refer to the table being renamed are
    invalidated,
  - statements in the dynamic statement cache that refer to the
    table being renamed are invalidated,
  - the text representation of the CREATE statements in
    SYSVIEWS_STMT and SYSROUTINESTEXT are not updated.
  
  Because the text representation of the CREATE statements in
  SYSVIEWS_STMT and SYSROUTINESTEXT are not updated after a
  successful RENAME TABLE, any user applications and tools that
  utilize SYSVIEWS_STMT and SYSROUTINESTEXT may not function
  correctly.
  
  This PTF adds a new DB2 subsystem parameter in DSN6SPRM called
  RENAMETABLE that can be used to specify whether the RENAME TABLE
  statement should extend to tables that are referenced in a view
  definition or the definition of an SQL table function.  Valid
  settings are:
   - DISALLOW_DEP_VIEW_SQLTUDF: Means that DB2 will issue SQLCODE
       -750 in response to a rename request for a table that is
       referenced in a view definition or the definition of an SQL
       table function.
       ==> This is the default setting.
   - ALLOW_DEP_VIEW_SQLTUDF   : Means that DB2 will honor a rename
       request for a table that is referenced in a view definition
       or the definition of an SQL table function.  Note that
       packages with statements that refer to the table being
       renamed will be invalidated.  In addition, such statements
       in the dynamic statement cache will be invalidated.  Also,
       in the SYSVIEWS_STMT catalog table, the text representation
       of the CREATE VIEW statement (or CREATE FUNCTION statement
       for an SQL table function) in the catalog will not be
       updated.  The SQL text is considered information only.
  
  If you have already installed or migrated to DB2 11, you need to
  take the following actions after applying this PTF:
  
  (1) Update customized copies of DB2 installation CLIST members
  (2) Update your customized copy of job DSNTIJUZ
  (3) Update private copies of the DSNTIDxx CLIST input member
  (4) Update the package for DSNTXAZP
  
  Detailed guidance for these actions follows:
  
  ----------------------------------------------------------------
  (1) Update customized copies of DB2 installation CLIST members
  ==> This action is required for all customers
  
      This PTF modifies CLIST member DSNTINST in the SDSNCLST
      target library only. You need to redo any record format
      changes and reapply any tailoring you have done to your
      copies of this CLIST. You may also want to move it to the
      prefix.NEW.SDSNCLST data set, where the CLISTs processed by
      job DSNTIJVC reside.
  
  ----------------------------------------------------------------
  (2) Update your customized copy of job DSNTIJUZ
  ==> This action is required for all customers
  
      This PTF modifies DB2 installation job DSNTIJUZ in the
      SDSNSAMP target library. After applying this PTF, you need
      to update your customized copy of this job as follows:
  
      * Add the keyword parameter RENAMETABLE=<x>, where <x> is
        DISALLOW_DEP_VIEW_SQLTUDF or ALLOW_DEP_VIEW_SQLTUDF, to
        the invocation of the DSN6SPRM macro.  Make sure to add
        a continuation character in column 72 if needed.  If you
        omit adding RENAMETABLE here, the value will be set to
        the default of DISALLOW_DEP_VIEW_SQLTUDF when you assemble
        the DSNZPxxx module.
  
      * Run the first two steps of the DSNTIJUZ job you modified.
  
      * After the job completes, you must either use the -SET
        SYSPARM command or stop and restart DB2 for the change to
        take effect.
  
  ----------------------------------------------------------------
  (3) Update private copies of the DSNTIDxx CLIST input member
  ==> This action is required for all customers
  
       This PTF adds an entry for RENAMETABLE to the
       CLIST default input member, DSNTIDXA, in the SDSNSAMP
       target library.  You need to add this entry to all private
       copies of your CLIST output DSNTIDxx member.  In each such
       copy, add the following line:
  
  RENAMETABLE CHAR  G  NONE        NONE          <x>
  
       Change <x> to the value you specified for RENAMETABLE in
       step (2), above.
  
  ----------------------------------------------------------------
  (4) Update the package for DSNTXAZP
  ==> This action is required for all customers who use DSNTXAZP
      to update DB2 CLIST input (DSNTIDXX) members:
  
      Submit a job that contains the following BIND statement:
  
          BIND PACKAGE(DSNTXAZP) MEMBER(DSNTXAZP) -
               ACTION(ADD) ENCODING(EBCDIC) -
               LIBRARY('prefix.SDSNDBRM')
  
      where <prefix> is the high-level qualifier for the
      DB2 subsystem target libraries.
  
  Additional Keywords:
  SQLRENAME
  SQLCODE750
  SQLDYNSTMTCACHE
  SQLVIEW
  SQLFUNCTION
  SQLTUDF
  SQLTABLEUDF
  ×**** PE16/01/13 FIX IN ERROR. SEE APAR PI36922  FOR DESCRIPTION
  

APAR Information

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

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

  UI27709

Modules/Macros

•    DSN@XAZP DSNDQWPZ DSNDSPRM DSNTIDXA DSNTIDXB
  DSNTIJUZ DSNTINST DSNTXAZP DSNWZIFB DSNXIRCO DSNXIRFN DSNXIRTB
  DSNXIRVW DSNXISB1 DSNXISB2 DSNXISUB DSNXOADJ DSNXOADT DSNXOATV
  DSNXOSV  DSNXOTDA DSN6SPRM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI27709

     UP15/06/02 P F506

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.