IBM Support

IT02233: QUERY REFERENCING A VIEW MIGHT PRODUCE SQL0901N IF ONE OR MORE OF THE DEPENDENT TABLES HAVE BEEN ALTERED

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Under rare scenarios, DB2 might produce a SQL0901N error message
    if the following conditions are true:
    1) The query references one or more views which is marked as
    VALID = "Y".
    2) The view has one or more dependent tables which have been
    altered after the view creation time
    3) These altered tables happen to be CGTT or partitioned table.
    4) The alter command on the tables dropped one or more columns.
    5) None of the dropped columns were referenced in the view
    
    Query gets the following error message:
    
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed.  (Reason "column
    number out of
    range".)  SQLSTATE=58004
    
    
    The stack file generated in FODC_AppErr* directory has the
    following stack:
    
    0x0900000000706B50 pthread_kill + 0xB0
    0x0900000006969B48 sqloDumpEDU + 0xA4
    0x09000000064D2568 sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 +
    0xD4
    0x09000000057A64E0
    sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2@glue5AF + 0x98
    0x09000000062ABE28 sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x320
    0x0900000007ADC664 sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x50
    0x0900000005D62700 sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc +
    0x264
    0x090000000721E180 sqlnn_erds__FiN41e + 0x33C
    0x0900000006F009C0 sqlnn_erds__FiN41e@glue70A + 0xC0
    0x0900000007D229A4 num2fcs__9sqlnq_ftbFiPP9sqlnq_fcsT1P3loc +
    0x80
    0x0900000007D35268 get_col_num__9sqlnq_qtbF12sqlnq_stringPiT2i +
    0x144
    0x0900000007D246D0
    @106@sqlnq_opr_nrs_def__F20sqlnq_multipart_nameiP9sqlnq_oprPP9sq
    lnq_qtbPP9sqlnq_qunPP9sqlnq_qncPi + 0x254
    0x09000000097F27EC
    @106@sqlnq_qb_named_ref_in_lowbox__F20sqlnq_multipart_nameiP9sql
    nq_qunPP9sqlnq_qtbPP9sqlnq_qunPP9sqlnq_qncPi + 0x104
    0x0900000007DAD8A4
    .@106@sqlnq_handle_qb_named_ref.fdpr.clone.99__F20sqlnq_multipar
    t_nameiP9sqlnq_oprPP9sqlnq_qtbPP9sqlnq_qunPP9sqlnq_qncPiT4 +
    0x3C
    0x0900000007D23780
    @106@sqlnq_handle_named_ref__F20sqlnq_multipart_nameiP9sqlnq_qtb
    P9sqlnq_oprT2PP9sqlnq_qtbPP9sqlnq_qunPP9sqlnq_qncT6Pi21sqlnq_hie
    rarchy_usage + 0x334
    0x0900000007D230F0
    .sqlnq_sem_col_ref.fdpr.clone.219__FPP8stknode_i10actiontypePUcP
    3locb + 0x1D8
    0x0900000007D2B664
    .sqlnq_sem_objname1.fdpr.clone.218__FPP8stknode_i10actiontypePUc
    P3locb + 0x164
    0x0900000007D1E07C sqlnq_sem__FPP8stknode_i10actiontypePUcP3loc
    + 0x2FC
    0x0900000007D1E3C8 sqlnp_parser__FP8sqlnp_cb + 0x1A0
    0x0900000007D14C78 sqlnp_parser__FP8sqlnp_cb + 0x90
    0x0900000007D154DC
    sqlnp_main__FP12sqlnq_stringbP3locPP9sqlnq_qur + 0x254
    0x0900000007CFA81C sqlnq_handle_new_view__FP9sqlnq_qtb + 0x27C
    0x0900000007CFF820
    sqlnq_check_referenced_qtb__FPP9sqlnq_qtbP9sqlnq_qunP3loc21sqlnq
    _hierarchy_usage + 0x7C
    0x0900000007CFF45C
    sqlnq_check_referenced_qtb__FP9sqlnq_qunP3loc21sqlnq_hierarchy_u
    sage + 0x9C
    0x0900000007CFF2AC
    sqlnq_handle_from_table_ref__FP20sqlnq_multipart_namePUciPP9sqln
    q_qunP9sqlnq_qtbP3locP9sqlnq_opr21sqlnq_hierarchy_usagePP8stknod
    e_ + 0x130
    0x0900000007CFEF48
    sqlnq_handle_from_table_ref__FP20sqlnq_multipart_namePUciPP9sqln
    q_qunP9sqlnq_qtbP3locP9sqlnq_opr21sqlnq_hierarchy_usagePP8stknod
    e_@glue12C4 + 0x88
    0x0900000007CFEE3C sqlnq_sem__FPP8stknode_i10actiontypePUcP3loc
    + 0x38
    0x0900000007D1E3C8 sqlnp_parser__FP8sqlnp_cb + 0x1A0
    0x0900000007D14C78 sqlnp_parser__FP8sqlnp_cb + 0x90
    0x0900000007D151B0
    sqlnp_main__FP12sqlnq_stringbP3locPP9sqlnq_qur + 0xB8
    0x0900000007CDEFF0
    sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
    lrr_cmpl_enviT7PP9sqlnq_qur + 0x488
    0x0900000007C9F764
    sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
    lrr_cmpl_env + 0x40
    0x0900000007A5DE5C
    sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14SQLP_
    LOCK_INFOP16sqlra_cached_varPiPUl + 0x80C
    0x0900000007A5B63C
    sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
    T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14
    SQLP_LOCK_INFOPP16sqlra_cac!
    0x0900000007A58060 sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x6CC
    0x09000000075BE590
    sqlrr_prepare__FP14db2UCinterfaceP16db2UCprepareInfo + 0xC0
    .............
    

Local fix

  • 1) Drop and recreate the view
    2) Starting DB2 V101, Regeneration of the valid views can be
    forced using the ADMIN_REVALIDATE_DB_OBJECTS
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 Fix Pack 10 or higher             *
    ****************************************************************
    

Problem conclusion

  • First fixed in Version 9.7 Fix Pack 10
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT02233

  • 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

    2014-06-04

  • Closed date

    2014-12-01

  • Last modified date

    2014-12-01

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

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

    IT06421 IT10517

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 #: IT02233

Modified date: 01 December 2014