PI42508: SQLCODE=-148 RETURNED ALT PRIMARY/FOREIGN KEY ON PBG TABLESPACE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• When altering parant and child table, PRIMATY KEY column
  and FOREING KEY in a PBG tablespace.
  
  Running the change fails with:
  ADB5044I *** INPUT STATEMENT:
      ALTER TABLE  ...
  sqlerror on ALTER      command, EXECUTE
  function
    DSNT408I SQLCODE = -148, ERROR:  THE SOURCE TABLE OR
                       TABLESPACE ...
                       CANNOT BE ALTERED. REASON 01
  DSNT418I SQLSTATE   = 42809 SQLSTATE RETURN CODE
  DSNT415I SQLERRP    = DSNXIRIC SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD    = 240 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
  DSNT416I SQLERRD    = ... SQL DIAGNOSTIC INFORMATION
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1-Users of the DB2 Object Comparison Tool    *
  *                 for z/OS who compare tables. The source      *
  *                 and target both have two tables that are     *
  *                 in partitioned table spaces.  The tables     *
  *                 have referential integrity and the           *
  *                 referenced column in the parent table is     *
  *                 also the primary key for the parent table.   *
  *                                                              *
  *                 2- Users of the DB2 Administration Tool      *
  *                 for z/OS who use 'ALT Alter Table' (ALT)     *
  *                 to alter the column type which is also the   *
  *                 primary key for the table.  The table is     *
  *                 in a partitioned table space and the         *
  *                 altered column is also in a referential      *
  *                 integrity relationship.                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1 - When comparing tables the source    *
  *                      and target have two tables with the     *
  *                      following attributes:                   *
  *                                                              *
  *                      - Both tables are in partitioned        *
  *                      table spaces.                           *
  *                                                              *
  *                       - The tables have the referential      *
  *                      integrity and the referenced column     *
  *                      in the parent table is also the         *
  *                      primary key for the parent table.       *
  *                                                              *
  *                      The 'ALTER TABLE DROP FOREIGN           *
  *                      KEY' statement is generated after       *
  *                      the 'ALTER TABLE >child_table_name<     *
  *                      ALTER >column_name<' statement,         *
  *                      which causes a SQLCODE=-148 /           *
  *                      SQLCODEN148 error.                      *
  *                                                              *
  *                      2- When using ALT to alter the column   *
  *                      type which is also the primary key for  *
  *                      for the table, the table is in a        *
  *                      partitioned tablespace, and the         *
  *                      altered column is also in a             *
  *                      referential integrity relationship,     *
  *                      the 'ALTER TABLE >parent_table_name<    *
  *                      ALTER >column_name<' statement          *
  *                      is generated after the 'ALTER TABLE     *
  *                      >child_table_name< FOREIGN KEY'         *
  *                      statement.  As a result, the            *
  *                      information message ADB5044I /          *
  *                      msgadb5044i and SQLCODE=-148 /          *
  *                      SQLCODEN148 error is displayed          *
  *                      after running the change:               *
  *                                                              *
  *                      ADB5044I *** INPUT STATEMENT:           *
  *                          ALTER TABLE  ...                    *
  *                      sqlerror on ALTER command,              *
  *                      EXECUTE function                        *
  *                        DSNT408I SQLCODE = -148,              *
  *                      ERROR:  THE SOURCE TABLE                *
  *                      OR TABLESPACE ...CANNOT BE              *
  *                      ALTERED. REASON 01                      *
  *                      DSNT418I SQLSTATE   = 42809             *
  *                      SQLSTATE RETURN CODE                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The 'ALTER TABLE >table_name< ALTER >column_name<' statement
  can not be executed when the tables' referential integrity
  relationship exists..
  

Problem conclusion

• Problem has been resolved.
  

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:

  UI30631 UI30632

Modules/Macros

•    ADB2CMF
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA20 PSY UI30631

     UP15/08/29 P F508

• RB10 PSY UI30632

     UP15/08/29 P F508

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.