IBM Support

IC66952: SERVER OPTION DB2_SELECT_SCALAR_SQ COULD CAUSE NICKNAME QUERY WITH A SUBQUERY NOT TO BE PUSHED DOWN TO MVS SERVER

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • db2_select_scalar_sq is set to 'N' for MVS, VM, iSeries data
    sources by default. Federated queries involving subqueries in
    their select list will be blocked from being pushed down if this
    option was set to 'N'. E.g.
    sleect c1, (select max(c2) from NN) where from NN
    
    This query can not be fully pushed down to remote server if
    db2_select_scalar_sq is set to 'N', and hence a fully remote
    query plan can not be generated.
    
    In addition, this option can affect other queries's
    pushdownability, with a subquery in predicate of where-clause.
    The predicate has to be one with a constant and a subquery. E.g.
    select c1 from NN A where 1 >  (select max(B.c2) from NN B where
    B.c3 = A.c3)
    
    Note that, following query can be affect by this option too,
    although the subquery is in predicate with a column A.c2 instead
    of a constant:
    select c1 from NN A where A.c2  =  (select max(B.c2) from NN B
    where B.c3 = A.c3) and A.c2 < 1
    The reason is that, an implicit predicate "1 >  (select
    max(B.c2) from NN B where B.c3 = A.c3)" will be generated by
    DB2, deriving from the existing two predicates. The implicit
    predicate will then prevent the query from being pushed down.
    

Local fix

  • Setting db2_select_scalar_sq to 'Y' if remote server supports a
    scalar subquery present in select list. In fact data sources
    like MVS V9 support it and this option should be set to 'Y' for
    them.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * performance low                                              *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * none                                                         *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Setting db2_select_scalar_sq to 'Y' if remote server         *
    * supports a                                                   *
    * scalar subquery present in select list.                      *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IC66952

  • 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

    2010-03-08

  • Closed date

    2010-09-25

  • Last modified date

    2010-09-25

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

    JR35194

  • 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

  • R970 PSY

       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":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
25 September 2010