Db2 (On Premises and Cloud)

Expand all | Collapse all

ADMIN_MOVE_TABLE SQL2103N RC=17

  • 1.  ADMIN_MOVE_TABLE SQL2103N RC=17

    Posted 30 days ago

    Hi,

    ADMIN_MOVE_TABLE aborts within the SWAP phase with SQL2103N RC=17 when two identical indexes (one non-unique the other unique)  are on the source table.
    I found APARs from 2012 where this bug was already solved but I am able to reproduce this with Db2 versions 11.1 / 11.5

    http://www-01.ibm.com/support/docview.wss?uid=swg1IC77972
    http://www-01.ibm.com/support/docview.wss?uid=swg1IC84547

    With the current beta drop I cannot reproduce it - so I assume within vNext or one of further fixpacks for the current versions it willbe solved again - am I right?
    Can any developer confirm this?

    Db2 v11.1/v11.5

    CREATE TABLE TEST01 (I INTEGER NOT NULL)
    DB20000I  The SQL command completed successfully.

    CREATE INDEX INDEX01 ON TEST01 (I)
    DB20000I  The SQL command completed successfully.

    CREATE UNIQUE INDEX UINDEX01 ON TEST01 (I)
    DB20000I  The SQL command completed successfully.

    ALTER TABLE TEST01 ADD PRIMARY KEY (I)
    SQL0598W  Existing index "DB2INST3.UINDEX01" is used as the index for the
    primary key or a unique key.  SQLSTATE=01550

    CALL admin_move_table('DB2INST3', 'TEST01',null,null,null,null,null,null,null,'','MOVE')
    SQL2103N  The ADMIN_MOVE_TABLE procedure could not be completed because some
    aspect of either the source table or target table is not supported by the
    ADMIN_MOVE_TABLE procedure.  Reason code: "17".  SQLSTATE=5UA0M

    Current beta s1910021500

    CALL admin_move_table('DB2INST4', 'TEST01',null,null,null,null,null,null,null,'','MOVE')


      Result set 1
      --------------

      KEY                              VALUE                                                                                                                          
      -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
      AUTHID                           DB2INST4                                                                                                                       
      CLEANUP_END                      2019-11-15-11.17.20.100232                                                                                                     
      CLEANUP_START                    2019-11-15-11.16.46.994603                                                                                                     
      COPY_END                         2019-11-15-11.16.39.833894                                                                                                     
      COPY_OPTS                        OVER_INDEX,ARRAY_INSERT,NON_CLUSTER                                                                                            
      COPY_START                       2019-11-15-11.16.07.012217                                                                                                     
      COPY_TOTAL_ROWS                  0                                                                                                                              
      INDEXNAME                        UINDEX01                                                                                                                       
      INDEXSCHEMA                      DB2INST4                                                                                                                       
      INDEX_CREATION_TOTAL_TIME        36                                                                                                                             
      INIT_END                         2019-11-15-11.15.49.068973                                                                                                     
      INIT_START                       2019-11-15-11.15.18.263627                                                                                                     
      ORIGINAL_TBLSIZE                 512                                                                                                                            
      REPLAY_END                       2019-11-15-11.16.46.599060                                                                                                     
      REPLAY_START                     2019-11-15-11.16.39.837192                                                                                                     
      REPLAY_TOTAL_ROWS                0                                                                                                                              
      REPLAY_TOTAL_TIME                1                                                                                                                              
      STATUS                           COMPLETE                                                                                                                       
      SWAP_END                         2019-11-15-11.16.46.841054                                                                                                     
      SWAP_RETRIES                     0                                                                                                                              
      SWAP_START                       2019-11-15-11.16.46.610788                                                                                                     
      UTILITY_INVOCATION_ID            0100000066040000080000000000000000002019111511154907002300000000                                                               
      VERSION                          11.01.0900                                                                                                                     

      23 record(s) selected.

      Return Status = 0

    Thanks in advance.

    Best regards,
    Gerhard



  • 2.  RE: ADMIN_MOVE_TABLE SQL2103N RC=17

    Posted Fri June 05, 2020 12:24 PM
    Hi Gerhard, I see the same behaviour (admin_move_table fails with SQL2103N rc=17) in both v11.1m4fp5 and the latest code-base of v11.5 (soon to be released mod-pack4 which is slightly newer then your EAP level).

    Database Connection Information

    Database server = DB2/LINUXX8664 11.5.4.0
    SQL authorization ID = DSCIARAF
    Local database alias = DB115

    CREATE TABLE TEST01 (I INTEGER NOT NULL)
    DB20000I The SQL command completed successfully.

    CREATE INDEX INDEX01 ON TEST01 (I)
    DB20000I The SQL command completed successfully.

    CREATE UNIQUE INDEX UINDEX01 ON TEST01 (I)
    DB20000I The SQL command completed successfully.

    ALTER TABLE TEST01 ADD PRIMARY KEY (I)
    SQL0598W Existing index "DSCIARAF.UINDEX01" is used as the index for the
    primary key or a unique key. SQLSTATE=01550

    CALL admin_move_table('DSCIARAF', 'TEST01',null,null,null,null,null,null,null,'','MOVE')
    SQL2103N The ADMIN_MOVE_TABLE procedure could not be completed because some
    aspect of either the source table or target table is not supported by the
    ADMIN_MOVE_TABLE procedure. Reason code: "17". SQLSTATE=5UA0M


    I'm guessing you have differences between your DB2INST3 and DB2INST4 environments (maybe differences in db configuration? or db2set registry settings?)
    Note that you can use the FORCE option of admin_move_table to bypass this error.


    ------------------------------
    DAVID SCIARAFFA
    ------------------------------