IBM Support

IC87147: AFTER DATA TYPE IS ALTERED ON A COLUMN WITH DEFAULT VALUE, DDLS AND DMLS MIGHT FAIL WITH SQL0901N "BAD DEFAULT"

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Under rare scenarios, DB2 may produce a SQL0901N error message
    if
    
    1. A table was created with a column for which the default value
    would depend on a special register, e.g. current timestamp,
    current date, etc.
    
    and then
    
    2. The table was altered such that the said column took on a new
    datatype that was different from the datatype of the special
    register, e.g. from timestamp to char
    
    
    All subsequent DDLs and DMLs which reference this column
    directly or indirectly might fail with SQL0901 - "bad Default"
    error message, e.g. in the db2diag.log
    
    2012-09-28-20.58.05.666018-240 E14336357E1356      LEVEL: Info
    (Origin)
    PID     : 6895                 TID  : 46919510387008PROC :
    db2sysc 1
    INSTANCE: db2inst1             NODE : 001          DB   : SAMPLE
    APPHDL  : 1-7452               APPID: *N1.db2inst1.120929083238
    AUTHID  : DB2INST1
    EDUID   : 37040                EDUNAME: db2agent (SAMPLE) 1
    FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
    MESSAGE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS
              "unexpected error but state is OK"
    DATA #1 : String, 62 bytes
    An unexpected error was detected during statement compilation.
    DATA #2 : Boolean, 1 bytes
    true
    DATA #3 : Boolean, 1 bytes
    true
    DATA #4 : Boolean, 1 bytes
    true
    DATA #5 : Boolean, 1 bytes
    false
    DATA #6 : Hex integer, 4 bytes
    0x00000000
    DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
     sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 11
     sqlerrmc: bad Default
     sqlerrp : SQLNQBA9
     sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3)
    0x00000000
               (4) 0x00000000      (5) 0xFFFFFF83      (6)
    0x00000001
     sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
               (7)      (8)      (9)      (10)        (11)
     sqlstate:
    DATA #8 : Hex integer, 4 bytes
    0x00000040
    DATA #9 : String, 91 bytes
    Compiler error stack for rc = -2144272209:
    sqlnn_cmpl[565]
    sqlnq_fcs::populate_defaul[125]
    
    
    The relevant stack file for the -901 might contain the following
    functions:
    
    <StackTrace>
    -----FUNC-ADDR---- ------FUNCTION + OFFSET------
    ...
    0x00002AAAAC12F8F7 _Z10sqlnn_erdsiiiiiz + 0x014b
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAC24B1D9
    _ZN9sqlnq_fcs22populate_default_valueEP8sqlrg_cdP3loc + 0x0267
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAC24ACDE
    _ZN9sqlnq_fcs16populate_from_cdEP8sqlrg_cdP3loc + 0x0162
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAE6EAE85 _ZN9sqlnq_fcsC9EP9sqlnq_ftbiRiP3locb + 0x03d9
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAE6EAAAA _ZN9sqlnq_fcsC1EP9sqlnq_ftbiRiP3locb + 0x0006
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAE6EB967 _ZN9sqlnq_ftb7num2fcsEiPP9sqlnq_fcsiP3loc +
    0x0121
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAC24E5F4 _ZN9sqlnq_ftb4copyEPPS_P3locii + 0x071c
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAD3AEF96
    _Z15sqlnq_table_oldP20sqlnq_multipart_name21sqlnq_hierarchy_usag
    e + 0x0220
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAD41C6B6
    _Z17sqlnq_auth_objectPP8stknode_i10actiontypePhP3loc + 0x00fc
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAE6E6CA3 _Z12sqlnp_smactnP8sqlnp_cbi + 0x079f
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAC1B3252 _Z12sqlnp_parserP8sqlnp_cb + 0x0816
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAC1B4ADB
    _Z10sqlnp_mainP12sqlnq_stringbP3locPP9sqlnq_qur + 0x02b3
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAC1313D9
    _Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_compileModesP14sq
    lrr_cmpl_enviiPP9sqlnq_qur + 0x1317
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    0x00002AAAAC1300BD
    _Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_compileModesP14sq
    lrr_cmpl_env + 0x0021
                    (/var/ibmdb2/nytxt980/sqllib/lib64/libdb2e.so.1)
    ...
    </StackTrace>
    
    
    
    The following query can be issued to help verify the problem
    
    select cast(name as varchar(25)), cast(tbname as varchar(25)),
    coltype, nulls, codepage, length, colno, typename, default,
    hidden, generated, inline_length from sysibm.syscolumns where
    tbname = '<tablename>'
    
    If coltype is different from the data type of 'generated', then
    this APAR is relevant.
    

Local fix

  • None. The problem cannot be corrected using DDLs. Please contact
    DB2 Support.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update to v9.7 Fix Pack 8                                    *
    ****************************************************************
    

Problem conclusion

  • First fixed in v9.7 Fix Pack 8
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC87147

  • 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

    2012-10-10

  • Closed date

    2013-04-19

  • Last modified date

    2013-04-19

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

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

    IC87431 IC87433

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

  • R980 PSN

       UP

  • RA10 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC87147

Modified date: 19 April 2013