JR35451: PERFORMANCE ISSUE WHEN USING ORACLE EE OPERATOR AS SPARSE LOOKUP

Download Fix Pack 2 for InfoSphere Information Server Version 8.1

APAR status

• Closed as program error.

Error description

• Performance issue when using Oracle EE operator as Sparse Lookup
  on IS 8.1 Sun Solaris, with patch JR35315 applied. This has been
  isolate the problem to a specific fix, JR34591..
  

Local fix

• This fix is included in 8.0.1 fixpack 3
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  Oracle EE Lookup and Upsert
  ****************************************************************
  PROBLEM DESCRIPTION:
  Slow performance with Oracle EE Lookup and Upsert stages when
  using character columns with indexes for SQL binds.
  ****************************************************************
  RECOMMENDATION:
  Apply this fix to correct performance issues for VARCHAR/CHAR
  indexed columns. Add new environment variable for jobs with
  national DB columns.
  ****************************************************************
  

Problem conclusion

• Default charset bind changed to IMPLICIT because it is most
  likely charset bind form. New Environment variable added to
  support NCHAR bind form for national character columns. If
  binding a national character set columns then user should use
  the new environment variable APT_ORA_BIND_FOR_NCHARS. This
  environment variable must contain a comma delimited list of
  DataStage column names that are National character columns on
  the database side. The reason we need an environment variable is
  because it is impossible to detect/parse complex SQL statements
  and determine correct charset form for binds in every scenario.
  

Temporary fix

Comments

APAR Information

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

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

Fix information

• Fixed component name

  INFO SRVR PLATF

• Fixed component ID

  5724Q3612

Applicable component levels

• R810 PSN

     UP