IZ83239: SERVER OPTIONS NEED TO BE ADDED TO ADJUST COST ESTIMATION FOR FETCHING DATA FROM REMOTE SERVER IN CERTAIN ENVIRONMENT

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

APAR status

• Closed as program error.

Error description

• In certain environment, cost for fetching massive rows from
  remote server is over-cost, while execution of a remote
  statement multi-times is under-cost. As a result, IFS chooses a
  NLJN which fetches remote data for multi-times, instead of
  choosing the HSJN which fetches all data of a remote table at
  one time, and the performance could be bad with the NLJN.
  

Local fix

• Use plan hints, explicit selectivity, stats views to force the
  HSJN plan.
  

Problem summary

• Problem Desciption:
  SERVER OPTIONS NEED TO BE ADDED TO ADJUST COST ESTIMATION FOR
  FETCHING DATA FROM REMOTE SERVER IN CERTAIN ENVIRONMENT
  Problem Summary:
  In certain environment, cost for fetching massive rows from
  remote server is over-cost, while execution of a remote
  statement multi-times is under-cost. As a result, IFS chooses a
  NLJN which fetches remote data for multi-times, instead of
  choosing the HSJN which fetches all data of a remote table at
  one time, and the performance could be bad with the NLJN.
  

Problem conclusion

• Problem is first fixed in Version 9.1 Fix Pack 10
  

Temporary fix

• Use plan hints, explicit selectivity, stats views to force the
  HSJN plan.
  

Comments

APAR Information

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

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

  IC70679 IC70680

Fix information

• Fixed component name

  DB2 UDB ESE SOL

• Fixed component ID

  5765F4102

Applicable component levels

• R910 PSY

     UP