Monday, January 24, 2005

Accessing iSeries files from within an Oracle application

Accessing iSeries files from within an Oracle application: "We recently had an in-house contractor accessing iSeries files from within an Oracle application. One or two record requests took 10-15 seconds; a 50-record request took 10-15 minutes.
His code showed that he was requesting the records via primary key. But a trace at the iSeries level (actually, just using OpsNav to show current SQL) showed that not all the WHERE clause entries were being passed to the iSeries optimizer and he was doing full table scans.
Eventually, we narrowed it down to the fact that he had defined a work variable used in the WHERE clause as VAR CHAR, a common practice in Oracle. Once that was changed to plain CHAR, the individual requests were processed in less than a second; the request for 50 records was returned in less than two seconds. "

No comments:

Post a Comment