Re: Help with DB2 and DOT.Net performance

ADO, OLEDB and ODBC pretty much use the same plumbing.

They all perform well with well-formed queries in my experience unless used with bad queries.

Is the POS vendor written or in-house written software ?

Sounds like some potential table scanning could be occurring. Would be good to do some query profiling perhaps to make sure indexes exist for all of the queries.

Seems like something may have changed in the coding in the new version of the POS perhaps.

Regards,


Richard Schoen
Director of Document Management
e. richard.schoen@xxxxxxxxxxxxxxx
p. 952.486.6802
w. helpsystems.com

----------------------------------------------------------------------

message: 1
date: Tue, 4 Jul 2017 01:15:37 +0000
from: Matt Olson <Matt.Olson@xxxxxxxx>
subject: Re: Help with DB2 and DOT.Net performance

Why are you using the ODBC driver in .NET? You should use the IBM i .NET Driver for best performance.

ODBC has a lot of overhead (aka, its SLOW).

________________________________________
From: gio.cot <gio.cot@xxxxxxxxxxx>
Sent: Monday, July 3, 2017 5:11 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Help with DB2 and DOT.Net performance

Hi all we have performance problem with Dot.Net Sql reads on IBM I ; this is the scenario



1. Our Windows software POS, in some procedures, reads IBM I DB2 for
retrieve inventory quantity or other information ; this reads are done with Sql statement , via Client Access ODBC driver .
2. From about two weeks , only in some days (Friday and Saturday) we
have big trouble with performance , as the Sql reads consume in some moment (QZDASOINIT Jobs) higth CPU percentage and IBM I is very slowwwww !!
3. Keep in mind that :
a. the IBM I run IPL only Sunday morning
b. that Friday and Saturday , the logistic and accounting offices
don't use IBM I , so the performance should be better, also if probably, the shops have more transaction and so more reads SQL at IBM I
4. The only thing that was changed from two week to today, is that was
installed a new version of Windows software POS ; the new version respect to the previous , is that was used the new Framework of dot.net (I think the
4.5 or 5 .. sorry but I don't know the windows/dot.net world.. I can ask to my colleagues) but the SQL statement are not changed (this SQL statement are many year the work with out performance any problem )
5. We found and followed the instruction at this link
http://www-01.ibm.com/support/docview.wss?uid=swg21572206 (CHGPJE
SBSD(QUSRWRK) PGM(QSYS/QZDASOINIT) MAXUSE(1)) but nothing changed, the problem remains ..

Can some one help/suggest some link for find the problem Thanks in advance

Gio