IBM Support

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

Fixes are available

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

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 with DB2COMPOPT set

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 9.1 FixPak 5.

Temporary fix

  • Unset the DB2COMPOPT registry variable.

Comments

  • 



APAR Information




    

  • APAR number
    JR27869
    • 

  • Reported component name
    DB2 UDB EXE WIN
    • 

  • Reported component ID
    5724E4901
    • 

  • Reported release
    910
    • 

  • Status
    CLOSED  PER
    • 

  • PE
    NoPE
    • 

  • HIPER
    NoHIPER
    • 

  • Special Attention
    NoSpecatt
    • 

  • Submitted date
    2007-11-21
    • 

  • Closed date
    2008-06-24
    • 

  • Last modified date
    2008-06-24
    • 





    

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

    • 

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

    
JR27872
    

    • 



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
    • 








      
              
                            

              

              [{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910"}]
              

                          

          
 
        

      

    

      

        

          

            
Document Information


            

            


                        

            Modified date:
            

            07 October 2021
            

            
                      

        


        

        


      

    

  



    

  

  
    
            

          
Page Feedback