JR46399: NESTED TABLE EXPRESSIONS WITH OUTER JOIN NOT PUSH DOWN

DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as requirement.

Error description

• By default, internal server option NESTED_TAB_EXPR_W_OJ is set
  to 'N' for AS400 data source, this may cause nested table
  expressions with out join cannot be pushed down to data source.
  
  Below is an example of the statement will encounter this issue:
  
  SELECT C.COL3 FROM (SELECT COL1,COL2,COL3
  FROM NICK1 WHERE COL1 = 'TEST') A, NICK1 B, NICK2 C
  WHERE B.COL1(+) = A.COL1 AND B.COL2(+) = A.COL2 AND
  B.COL4(+) =A.COL3 AND C.COL1(+) = B.COL1 AND C.COL2(+) = B.COL3;
  

Local fix

• alter server os400serv options(add db2_nested_tab_expr_w_oj 'Y')
  

Problem summary

Problem conclusion

Temporary fix

Comments

• NA
  

APAR Information

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

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

  IC91671 IC97533

Fix information

Applicable component levels