IBM Support

IC91921: WHEN USING ADMIN_MOVE_TABLE, THE STAGING AND TARGET TABLE IS NOT CLEANED DURING THE CANCEL OPERATION WHEN INIT PHASE FAILS.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When using the ADMIN_MOVE_TABLE procedure, there are occasions
    where it would fail due to various errors. To recover/cancel the
    ADMIN_MOVE_TABLE procedure, a CANCEL option will be used.
    However the CANCEL option will not do anything to fix the
    problem as it would fail to remove the staging table created by
    the ADMIN_MOVE_TABLE procedure.
    So far this issue has only occured during if the problem occurs
    during the INIT phase of the ADMIN_MOVE_TABLE procedure.
    .
    The problem can be reproduced using the following steps...
    .
    $ db2trc on -crash
    DB2.DBAPPEXT_UTIL.adminotm_Otm_createStagingTable.exit
    Trace is turned on
    .
    $ db2 "call
    admin_move_table('TEST','TAB1','','','','','','','','','INIT,TRA
    CE')"
    SQL1224N  The database manager is not able to accept new
    requests, has
    terminated all requests in progress, or has terminated the
    specified request
    because of an error or a forced interrupt.  SQLSTATE=55032
    .
    $ db2trc off
    Trace is already off; no action was taken.
    .
    $ db2 list tables for schema test
    .
    Table/View                      Schema          Type  Creation
    time
    ------------------------------- --------------- -----
    --------------------------
    TAB1                            TEST            T
    2013-04-25-01.49.38.397285
    TAB1AABYVRs                     TEST            T
    2013-04-25-02.10.29.760057
    TAB1AABYVRt                     TEST            T
    2013-04-25-02.10.29.499973
    .
      3 record(s) selected.
    .
    $ db2 "select substr(key,1,30), substr(value,1,30) from
    systools.admin_move_table"
    .
    1                              2
    ------------------------------ ------------------------------
    STAGING                        TAB1AABYVRs
    LOCK                           2013-04-25-02.10.29.243344
    STATUS                         INIT
    AUTHID                         TANJINXU
    VERSION                        09.07.0007
    INIT_START                     2013-04-25-02.10.29.337336
    INDEXSCHEMA
    INDEXNAME
    TARGET                         TAB1AABYVRt
    .
      9 record(s) selected.
    .
    $ db2 "call
    admin_move_table('TEST','TAB1','','','','','','','','','cancel,t
    race')"
    .
      Result set 1
      --------------
    .
    
    
      KEY                              VALUE
      -------------------------------- ---------------------------
    .
      0 record(s) selected.
    .
      Return Status = 0
    .
    $ db2 "select substr(key,1,30), substr(value,1,30) from
    systools.admin_move_table"
    .
    1                              2
    ------------------------------ ------------------------------
    .
      0 record(s) selected.
    .
    $ db2 list tables for schema test
    .
    Table/View                      Schema          Type  Creation
    time
    ------------------------------- --------------- -----
    --------------------------
    TAB1                            TEST            T
    2013-04-25-01.49.38.397285
    TAB1AABYVRs                     TEST            T
    2013-04-25-02.10.29.760057
    TAB1AABYVRt                     TEST            T
    2013-04-25-02.10.29.499973
    .
      3 record(s) selected.
    .
    $ db2 "call
    admin_move_table('TEST','TAB1','','','','','','','','','INIT,TRA
    CE')"
    SQL0601N  The name of the object to be created is identical to
    the existing
    name "TEST.TAB1AABYVRt" of type "TABLE".  SQLSTATE=42710
    

Local fix

  • Identify the table used in the ADMIN_MOVE_TABLE and the equiva-
    leng old target and old stagin table.
    Example : Table used in ADMIN_MOVE_TABLE = TAB1
    Do a "list tables for schema <schema name>".
    The output will show...
    $ db2 list tables for schema test
    .
    Table/View                      Schema          Type  Creation
    time
    ------------------------------- --------------- -----
    --------------------------
    TAB1                            TEST            T
    2013-04-25-01.49.38.397285
    TAB1AABYVRs                     TEST            T
    2013-04-25-02.10.29.760057
    TAB1AABYVRt                     TEST            T
    2013-04-25-02.10.29.499973
    .
      3 record(s) selected.
    .
    Where : TAB1        = Table specified in the ADMIN_MOVE_TABLE
            TAB1AABYVRs  and TAB1AABYVRt
                        = Is the old target and old stagin table.
    

Problem summary

  • Users Affected : All
    Problem Description :
       When using ADMIN_MOVE_TABLE, the staging and target table
       is not cleaned during the cancel operation when INIT phase
       fails.
    Problem Summary :
    When using the ADMIN_MOVE_TABLE procedure, there are occasions
    where it would fail due to various errors. To recover/cancel the
    ADMIN_MOVE_TABLE procedure, a CANCEL option will be used.
    However the CANCEL option will not do anything to fix the
    problem as it would fail to remove the staging table created by
    the ADMIN_MOVE_TABLE procedure.
    So far this issue has only occured during if the problem occurs
    during the INIT phase of the ADMIN_MOVE_TABLE procedure.
    .
    The problem can be reproduced using the following steps...
    .
    $ db2trc on -crash
    DB2.DBAPPEXT_UTIL.adminotm_Otm_createStagingTable.exit
    Trace is turned on
    .
    $ db2 "call
    admin_move_table('TEST','TAB1','','','','','','','','','INIT,TRA
    CE')"
    SQL1224N The database manager is not able to accept new
    requests, has
    terminated all requests in progress, or has terminated the
    specified request
    because of an error or a forced interrupt. SQLSTATE=55032
    .
    $ db2trc off
    Trace is already off; no action was taken.
    .
    $ db2 list tables for schema test
    .
    Table/View Schema Type Creation
    time
    ------------------------------- --------------- -----
    --------------------------
    TAB1 TEST T
    2013-04-25-01.49.38.397285
    TAB1AABYVRs TEST T
    2013-04-25-02.10.29.760057
    TAB1AABYVRt TEST T
    2013-04-25-02.10.29.499973
    .
    3 record(s) selected.
    .
    .
    $ db2 "select substr(key,1,30), substr(value,1,30) from
    systools.admin_move_table"
    .
    1 2
    ------------------------------ ------------------------------
    STAGING TAB1AABYVRs
    LOCK 2013-04-25-02.10.29.243344
    STATUS INIT
    AUTHID TANJINXU
    VERSION 09.07.0007
    INIT_START 2013-04-25-02.10.29.337336
    INDEXSCHEMA
    INDEXNAME
    TARGET TAB1AABYVRt
    .
    9 record(s) selected.
    .
    $ db2 "call
    admin_move_table('TEST','TAB1','','','','','','','','','cancel,t
    race')"
    .
    Result set 1
    --------------
    .
    KEY VALUE
    -------------------------------- ---------------------------
    .
    0 record(s) selected.
    .
    Return Status = 0
    .
    $ db2 "select substr(key,1,30), substr(value,1,30) from
    systools.admin_move_table"
    .
    1 2
    ------------------------------ ------------------------------
    .
    0 record(s) selected.
    .
    $ db2 list tables for schema test
    .
    Table/View Schema Type Creation
    time
    ------------------------------- --------------- -----
    --------------------------
    TAB1 TEST T
    2013-04-25-01.49.38.397285
    TAB1AABYVRs TEST T
    2013-04-25-02.10.29.760057
    TAB1AABYVRt TEST T
    2013-04-25-02.10.29.499973
    .
    3 record(s) selected.
    .
    $ db2 "call
    admin_move_table('TEST','TAB1','','','','','','','','','INIT,TRA
    CE')"
    SQL0601N The name of the object to be created is identical to
    the existing
    name "TEST.TAB1AABYVRt" of type "TABLE". SQLSTATE=42710
    

Problem conclusion

  • Problem was first fixed in V9.7 FP9 (s131204).
    

Temporary fix

  • Identify the table used in the ADMIN_MOVE_TABLE and the equiva-
    leng old target and old stagin table.
    Example : Table used in ADMIN_MOVE_TABLE = TAB1
    Do a "list tables for schema <schema name>".
    The output will show...
    $ db2 list tables for schema test
    .
    Table/View Schema Type Creation
    time
    ------------------------------- --------------- -----
    --------------------------
    TAB1 TEST T
    2013-04-25-01.49.38.397285
    TAB1AABYVRs TEST T
    2013-04-25-02.10.29.760057
    TAB1AABYVRt TEST T
    2013-04-25-02.10.29.499973
    .
    3 record(s) selected.
    .
    Where : TAB1 = Table specified in the ADMIN_MOVE_TABLE
    TAB1AABYVRs and TAB1AABYVRt
    = Is the old target and old stagin table.
    

Comments

APAR Information

  • APAR number

    IC91921

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-04-26

  • Closed date

    2014-08-18

  • Last modified date

    2014-08-18

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

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

    IC95520 IC95655

Modules/Macros

  • ENGN_SQT
    

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
18 August 2014