Re: MIDRANGE-L Digest, Vol 16, Issue 1066 -- MIDRANGE-L

Hi Gio,

The same SQL statement can perform differently from one day to another. The
SQL engine takes into account how much competition is occurring for
resources when execution of an SQL statement is requested. If the level of
competition has changed by a large degree, it may choose to use a different
access plan to satisfy the statement. If the competition has increased by a
large amount, it may choose an access plan that uses less resources, in
order to be a good neighbor to the other jobs competing for resources. A
different access plan using less resources will likely run slower. If the
competition has decreased, it may choose an access plan that uses more
resources, since competition for resources is less of a concern during
periods of light system usage. A different access plan using more
resources will likely run faster.

If job competition increases on Fridays and Saturdays, this may be your
performance scenario problem.

I suggest approaching this like any other SQL statement tuning task. Start
by checking for index advisories present in the QSYS2/SYSIXADV file or
the QSYS2/CONDENSEDINDEXADVICE view.

Mike

date: Mon, 3 Jul 2017 12:11:53 +0200
from: "gio.cot" <gio.cot@xxxxxxxxxxx>
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

Notes :
IBM I run V7R2

PTF Status :
ID TITLE INSTALLED
SF99776 HIGH AVAILABILITY FOR IBM I 6
SF99775 HARDWARE AND RELATED PTFS 19
SF99767 720 TCP/IP PTF 3
SF99766 PRINT PTFS 3
SF99720 CUMULATIVE PTF PACKAGE C6306720 16306
SF99719 GROUP HIPER 77
SF99718 GROUP SECURITY 39
SF99717 TECHNOLOGY REFRESH 5
SF99716 JAVA 11
SF99715 BACKUP RECOVERY SOLUTIONS 27
SF99714 PERFORMANCE TOOLS 4
SF99713 IBM HTTP SERVER FOR I 19
SF99702 DB2 FOR IBM I 14

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

message: 4
date: Mon, 3 Jul 2017 07:52:16 -0500
from: Diego Kesselman <diegokesselman@xxxxxxxxx>
subject: Re: Help with DB2 and DOT.Net performance

Have you checked disk response time? (PDI is a good starting point)
Are you using *BASE for qzdasoinit jobs? What about paging?

El 3 jul. 2017 5:12 AM, "gio.cot" <gio.cot@xxxxxxxxxxx> escribi?:

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

Notes :
IBM I run V7R2

PTF Status :
ID TITLE INSTALLED
SF99776 HIGH AVAILABILITY FOR IBM I 6
SF99775 HARDWARE AND RELATED PTFS 19
SF99767 720 TCP/IP PTF 3
SF99766 PRINT PTFS 3
SF99720 CUMULATIVE PTF PACKAGE C6306720 16306
SF99719 GROUP HIPER 77
SF99718 GROUP SECURITY 39
SF99717 TECHNOLOGY REFRESH 5
SF99716 JAVA 11
SF99715 BACKUP RECOVERY SOLUTIONS 27
SF99714 PERFORMANCE TOOLS 4
SF99713 IBM HTTP SERVER FOR I 19
SF99702 DB2 FOR IBM I 14

--
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.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

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

message: 5
date: Mon, 3 Jul 2017 08:53:31 -0400
from: a4g atl <a4ginatl2@xxxxxxxxx>
subject: How does a SQL result set return pack fields to the caller

I am supporting a site that uses result sets to return data to the caller
which is usually a external system but within the enterprise network.

I see they have a mix of result sets (data structures) that have packed
fields in them. My practice has always been to define the fields in a data
structure for a result set as character.

The developer of the calling apps could not give me a proper answer so I am
going to bounce the question here.

Does the system convert packed fields to zoned or character fields?

I assume it can based on the CCSIDs in use.

What is the best practice?

TIA

Darryl Freinkel

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

message: 6
date: Mon, 3 Jul 2017 15:04:50 +0200
from: "gio.cot" <gio.cot@xxxxxxxxxxx>
subject: Re: Help with DB2 and DOT.Net performance

Hi diego

Thanks for your reply

Sorry but I ask to explain better what you ask :

..(PDI is a good starting point)

What do you mean for ..(PDI is a good starting point) ?

..Are you using *BASE for qzdasoinit jobs?

How can I see if *BASE o not ?

For paging and disk response time I have not checked .. this problem
happened only (for now) on Friday or Saturday a for only 15/20 minutes .

Thanks in advance

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

Subject: Digest Footer

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) digest 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.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

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

End of MIDRANGE-L Digest, Vol 16, Issue 1066
********************************************