IBM Support

JR27872: DB2 CAN ABEND WITH A SQL1224N MESSAGE WHEN THE DB2COMPOPT REGISTRY VARIABLE IS SET AND THE QUERY CONTAINS A GROUP BY

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • DB2 can abend with a SQL1224N message when the DB2COMPOPT
    registry variable is set and the query contains a GROUP BY
    clause. The columns or functions listed in the GROUP BY clause
    must be involved in an equality predicate with a host variable
    or special register. The subselect containing the GROUP BY
    clause must be the input operand of an OUTER JOIN.
    
    Possible stacks:
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_estimate_gb_card
    sqlnr_estimate_box_card
    sqlnr_estimate_card
    ...
    
    or:
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_early_distinct_fd_rj_comp
    ...
    
    or
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_sqo_byScalar_cond
    ...
    
    or
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_joinel_byScalarAgg_cond
    ...
    
    or
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_joinppd_ojppd4np_cond
    

Local fix

  • Unset the DB2COMPOPT registry variable.
    

Problem summary

  • USERS AFFECTED  All
    
    PROBLEM DESCRIPTION
    
    DB2 can abend with a SQL1224N message when the DB2COMPOPT
    registry variable is set and the query contains a GROUP BY
    clause. The columns or functions listed in the GROUP BY clause
    must be involved in an equality predicate with a host variable
    or special register. The subselect containing the GROUP BY
    clause must be the input operand of an OUTER JOIN.
    
    Possible stacks:
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_estimate_gb_card
    sqlnr_estimate_box_card
    sqlnr_estimate_card
    ...
    
    or:
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_early_distinct_fd_rj_comp
    ...
    
    or
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_sqo_byScalar_cond
    ...
    
    or
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_joinel_byScalarAgg_cond
    ...
    
    or
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_joinppd_ojppd4np_cond
    
    
    PROBLEM SUMMARY
    
    DB2 can abend with a SQL1224N message when the DB2COMPOPT
    registry variable is set and the query contains a GROUP BY
    clause. The columns or functions listed in the GROUP BY clause
    must be involved in an equality predicate with a host variable
    or special register. The subselect containing the GROUP BY
    clause must be the input operand of an OUTER JOIN.
    
    Possible stacks:
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_estimate_gb_card
    sqlnr_estimate_box_card
    sqlnr_estimate_card
    ...
    
    or:
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_early_distinct_fd_rj_comp
    ...
    
    or
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_sqo_byScalar_cond
    ...
    
    or
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_joinel_byScalarAgg_cond
    ...
    
    or
    
    sqlnr_estimate_qncs_distinctvalue_basic
    sqlnr_estimate_qncs_distinctvalue
    sqlnr_joinppd_ojppd4np_cond
    

Problem conclusion

  • The complete fix for this problem first appears in DB2 UDB
    Version 8.1 FixPak
    

Temporary fix

  • Unset the DB2COMPOPT registry variable.
    

Comments

APAR Information

  • APAR number

    JR27872

  • Reported component name

    DB2 UDB EXE WIN

  • Reported component ID

    5724E4901

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-11-21

  • Closed date

    2008-05-06

  • Last modified date

    2008-05-06

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

    JR27869

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

Fix information

  • Fixed component name

    DB2 UDB EXE WIN

  • Fixed component ID

    5724E4901

Applicable component levels

  • R810 PSN

       UP

  • R820 PSN

       UP

  • R910 PSN

       UP

  • R950 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
06 May 2008