IBM Support

IC92574: ADMIN_MOVE_TABLE FAILED WITH SQL0104N IF THE SOURCE TABLE HAS NO INDEX AND THE 1ST COLUMN SIZE EXCEEDS THE MAX KEY LENGTH LIMIT

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as fixed if next.

Error description

  • If the source table has no index and the first column size
    exceeds the max key size limit, then AMT would fail with
    SQL0104N. For example, the table below has DESCRIPTION column
    with VARCHAR(4000) which exceeds the max key length limit (2048
    bytes for the table space with 8K page size) as the 1st column,
    and there is no index defined on the table:
    
    CREATE TABLE "WCS     "."PARTROLEDS"  (
            "DESCRIPTION" VARCHAR(4000) ,
                      "PARTROLE_ID" INTEGER NOT NULL ,
                      "LANGUAGE_ID" INTEGER NOT NULL ,
                      "OPTCOUNTER" SMALLINT )
                     IN "TAB8K" ;
    
    $ db2 "select substr(colname,1,30), colno FROM SYSCAT.COLUMNS
    WHERE tabname='PARTROLEDS' order by colno"
    
    1                              COLNO
    ------------------------------ ------
    DESCRIPTION                         0
    PARTROLE_ID                         1
    LANGUAGE_ID                         2
    OPTCOUNTER                          3
    
      4 record(s) selected.
    
    $ db2 "call sysproc.admin_move_table( 'WCS', 'PARTROLEDS',
    'TBS16', 'TBS16_I', 'TBS16_L', '', '', '', '', '', 'MOVE,TRACE'
    )"
    SQL0104N  An unexpected token "," was found following
    ""WCS"."PARTROLEDS"(".
    Expected tokens may include:  "<space>".  SQLSTATE=42601
    
    And the ADMIN_MOVE_TABLE trace like:
    . . (10600)EXIT: adminotm_Otm_getColumns                 | 0 | 4
    . . (3116)ENTRY: adminotm_Otm_generateIdent              |
    0x2aaac1be7fc0 | 129 | PARTROLEDS | g | 128
    . . (3141)EXIT: adminotm_Otm_generateIdent               | 0 |
    PARTROLEDSAJd0#jg
    . . (2292)ENTRY: adminotm_StoredProcedure_execDirect
    | CREATE INDEX "WCS"."PARTROLEDSAJd0#jg" ON
    "WCS"."PARTROLEDS"(,"PARTROLE_ID","LANGUAGE_ID","OPTCOUNTER") |
    0x2aabcea07d70
    . . . (711)ENTRY: adminotm_fillSqlca             | 3 | 65542 |
    0x2aabcea07d70
    . . . . (728)DATA: adminotm_fillSqlca            | 1 |  SQL0104N
    An unexpected token "," was found following
    ""WCS"."PARTROLEDS"(".  Expected tokens may include:  "<space>".
    SQLSTATE=42601
    

Local fix

  • To create an index before the move.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * users who use ADMIN_MOVE_TABLE procedure                     *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 V9.7FP9                                       *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IC92574

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-05-24

  • Closed date

    2013-11-26

  • Last modified date

    2013-11-26

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

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

    IC97955 IC97983

Fix information

Applicable component levels

  • R970 PSN

       UP

  • RA10 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC92574

Modified date: 26 November 2013