IBM Support

IC78225: ADMIN_MOVE_TABLE PROCEDURE RETURNS SQL0413N WHEN SOURCE TABLE HAS A DECIMAL IDENTITY COLUMN WITH SOME BIG VALUES.

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • ADMIN_MOVE_TABLE procedure returns SQL0413N when the source
    table has decimal identity column, and its values, e.g. START
    WITH and MAXVALUE etc, exceeds the largest value of BIGINT in
    DB2.  During the process of ADMIN_MOVE_TABLE(), an internal
    query casts those values into BIGINT. If any of values greater
    than +9,223,372,036,854,775,807, it returns SQL0413N.
    

Local fix

  • Move table offline.
    
    rename table tab_name to tab_name_temp;
    alter table tab_name_temp alter identity_column DROP IDENTITY;
    call sysproc.admin_move_table('schema',
    'tab_name_temp'......'MOVE');
    alter table tab_name_temp alter identity_column set
                    GENERATED ALWAYS AS IDENTITY (
                        START WITH +1
                        INCREMENT BY +1
                        MINVALUE +1
                        MAXVALUE +9999999999999999999999999999
                        NO CYCLE
                        CACHE 20
                        NO ORDER )
    ;
    
    select max(identity_column) from tab_name_temp
    ;
    -- found max identity value plus 1 as restart_value
    
    alter table tab_name_temp
            ALTER identity_column
            RESTART WITH +restart_value
    ;
    
    rename table tab_name_temp to tab_name;
    
    ;
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 LUW Version 9.7 Fix Pack 6                    *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Version 9.7 Fix Pack 6
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC78225

  • 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

    2011-08-19

  • Closed date

    2012-06-13

  • Last modified date

    2012-06-13

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC78225

Modified date: 13 June 2012