IZ47429: SQL0901 REASON "SQLNO_CRULE_JOIN [350]" RETURNED WHEN OPTIMIZATION GUIDELINE WITH FIRST ATTRIBUTE IS USED

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

APAR status

• Closed as program error.

Error description

• Compiling a query with an optimization guideline that includes a
  join request with the FIRST attribute may result in the
  following error:
  
      SQL0901N  The SQL statement failed because of a non-severe
  system error.
      Subsequent SQL statements can be processed.  (Reason
  "sqlno_crule_join [350]:rc(     0)                ".)
  SQLSTATE=58004
  
  
  Here is an example of such an optimization guideline:
  
      <OPTGUIDELINES>
        <NLJOIN FIRST="TRUE">
          <ACCESS TABLE="TABLE1"/>
          <ACCESS TABID="Q10"/>
        </NLJOIN>
      </OPTGUIDELINES>
  
  This can happen if no valid query plan can be generated using
  the guideline.  Under normal circumstances, if a guideline
  cannot be used, the optimizer should ignore the guideline and
  generate a valid plan.
  

Local fix

• Correct the optimization guideline so it can be used to generate
  a valid query plan.  Alternatively, disable optimization
  guidelines for the query.
  

Problem summary

• Users Affected:
  
  The users who are compiling a query with an optimization
  guideline that includes a join request with the FIRST attribute.
  
  This can happen if no valid query plan can be generated using
  the guideline.
  
  PROBLEM:
  
  Compiling a query with an optimization guideline that
  includes a join request with the FIRST attribute may result in
  the following error:
  
      SQL0901N  The SQL statement failed because of a non-severe
  system error.
      Subsequent SQL statements can be processed.  (Reason
  "sqlno_crule_join [350]:rc(     0)                ".)
  SQLSTATE=58004
  
  
  Here is an example of such an optimization guideline:
  
      <OPTGUIDELINES>
        <NLJOIN FIRST="TRUE">
          <ACCESS TABLE="TABLE1"/>
          <ACCESS TABID="Q10"/>
        </NLJOIN>
      </OPTGUIDELINES>
  
  This can happen if no valid query plan can be generated using
  the guideline.  Under normal circumstances, if a guideline
  cannot be used, the optimizer should ignore the guideline and
  generate a valid plan.
  

Problem conclusion

• Problem was first fixed in Version 9.5 Fix Pack 5
  

Temporary fix

• Correct the optimization guideline so it can be used to generate
  a valid query plan.  Alternatively, disable optimization
  guidelines for the query.
  

Comments

APAR Information

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

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

  IZ47430 IC62675

Fix information

• Fixed component name

  DB2 UDB WSE SOL

• Fixed component ID

  5765F3502

Applicable component levels

• R950 PSN

     UP