Re: ODBC question...

I would first review how the they doing the overall sql sequence. By that 
I mean if they have written the code for say Oracle and made changes to 
the fit the 400 syntax there may be some performance issues.

It's been awhile but I was involved with a project where the SQL guys were 
"COMMITing" after every update. The process in question was taking over 
4hrs. The commit was causing a full open/update/close of over 100 files 
per transaction. I was able eventually to only commit every 300 
transaction and the job went to 45 minutes. 

Bring up the ODBC setting screens (either thru CA or Controll Panel) look 
in the following tabs.

Packages
        Check for "Enable extended dynamic (package) support"  (if not 
checked try that)
        Check "Cache package locally" 

Performance
        Test all the options like "lazy write support" etc 

If you want to TRACE the actual cmds etc flying thru the ODBC use the 
DIAGNOSTIC tab to setup a trace etc

Hope this gets you started
 
_____________________
Kirk Goins CCNA
Systems Engineer, Manage Inc.
IBM Certified iSeries Solutions Expert
IBM Certified iSeries e-Business Infrastructure
IBM Certified Designing IBM e-business Solutions 
Office 503-353-1721 x106 Cell 503-577-9519
kirkg@xxxxxxxxxxxxx      www.manageinc.com



"Steve Landess" <sjl_123@xxxxxxxxxxx> 
Sent by: midrange-l-bounces+kirkg=manageinc.com@xxxxxxxxxxxx
12/02/2003 01:19 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<midrange-l@xxxxxxxxxxxx>
cc

Subject
ODBC question...






My client is using SeeBeyond EAI (Enterprise Application Integration) 
software to move data between platforms, from a SQL server 2000 sales 
front end to an iSeries with JDE for billing, then from the iSeries to an 
Oracle manufacturing system on the back end.

We ran a stress test a couple of weeks ago and found some fairly severe 
performance problems when inserting data into the iSeries database files. 
SeeBeyond uses the CAE ODBC provider to connect to the iSeries, and it 
appears that it is quite the dog, performance-wise.

Here is a sample of our results:

File   Time for                   Record 
        insert (seconds)     Length (bytes)

A        4                                  707
B        .2                                   69
C        7                                1370
D        1                                  208

It appears that the average data transfer rate is around 200 bytes/second. 
 

All told, it took around 30 minutes to load the data for ONE sales order 
into the iSeries database files.  This is *not* good. 

The QZDASOINIT jobs run in the QUSRWRK subsystem, which currently uses 
only the *BASE memory pool.  I'm not sure that creating a private memory 
pool for this subsystem will help any...I think that the bottleneck is the 
CAE ODBC driver.

Any ideas on improving the performance of this application? 

TIA for your advice,
Steve Landess
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.