NOT ACCOUNT Updated

Renzo Razzetti

NOT ACCOUNT Updated
Hi all

I have more data for this problem.


We are running DB2 V8 datasharing.
We found in production two package with a high Not Account Time. Only
two packages, special packages.They perform a high insert workload.
Make linsert in the journal table.The insert are always at the end
of the table

We already checked all the possible cause that are in the book: CPU
is ok, paging is ok, not online monitoring, is not DRDA, SERV TASK
SWITCH is ok.

The corious is one day have a high not account time, and the following
day have a low not account time. Every day the program use a
different table.(JRN1 o JRN2)
The tables should be equals, but for some historical reason we found
the tablespaces are differnent

JRN1 is Compress NO and Segmented Table. When is used the NOT ACCOUNT
time is high.
JRN2 is Compress YES and SIMPLE table. When is used the NOT ACCOUNT time is low.


In V7, we didn't have any problem with this two tables. But now (one
month ago) we upgrade to V8. Since the upgrade to V8 we have this
behavior.


V8 change the behavior with the compressed tables o with the segmented
table ? And what is the relation with the NOT ACCOUNT time.

Thank you in advance.


RR

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Roger Miller

Re: NOT ACCOUNT Updated
(in response to Renzo Razzetti)
The next dozen or so reasons are noted in this online support document:
http://www.ibm.com/support/docview.wss?rs=64&uid=swg21045823

I don't see any direct reason for this listed there, but some of these cases
might work in your situation.

Roger

On Mon, 24 Nov 2008 11:13:54 +0800, Renzo razzetti
<[login to unmask email]> wrote:

> Hi all
>
>I have more data for this problem.
>
>
> We are running DB2 V8 datasharing.
> We found in production two package with a high Not Account Time. Only
> two packages, special packages.They perform a high insert workload.
> Make linsert in the journal table.The insert are always at the end
>of the table
>
> We already checked all the possible cause that are in the book: CPU
> is ok, paging is ok, not online monitoring, is not DRDA, SERV TASK
> SWITCH is ok.
>
> The corious is one day have a high not account time, and the following
> day have a low not account time. Every day the program use a
>different table.(JRN1 o JRN2)
>The tables should be equals, but for some historical reason we found
>the tablespaces are differnent
>
>JRN1 is Compress NO and Segmented Table. When is used the NOT ACCOUNT
>time is high.
>JRN2 is Compress YES and SIMPLE table. When is used the NOT ACCOUNT
time is low.
>
>
>In V7, we didn't have any problem with this two tables. But now (one
>month ago) we upgrade to V8. Since the upgrade to V8 we have this
>behavior.
>
>
>V8 change the behavior with the compressed tables o with the segmented
>table ? And what is the relation with the NOT ACCOUNT time.
>
>Thank you in advance.
>
>
> RR
>
>_________________________________________________________________
_____

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Deb Ghosh

Re: NOT ACCOUNT Updated
(in response to Roger Miller)

Do you have RMF MON III, what is %workflow value, you can use DELAY command to see what % delay is within the MVS, also you would suggest to drill type 30 subtype 5
I am assuming that this a batch. you can also use report class report from type 72 to where the non DB2 delay is.
> Date: Mon, 24 Nov 2008 18:57:26 +0000> From: [login to unmask email]> Subject: Re: [DB2-L] NOT ACCOUNT Updated> To: [login to unmask email]> > The next dozen or so reasons are noted in this online support document: > http://www.ibm.com/support/docview.wss?rs=64&uid=swg21045823> > I don't see any direct reason for this listed there, but some of these cases > might work in your situation.> > Roger> > On Mon, 24 Nov 2008 11:13:54 +0800, Renzo razzetti > <[login to unmask email]> wrote:> > > Hi all> >> >I have more data for this problem.> >> >> > We are running DB2 V8 datasharing.> > We found in production two package with a high Not Account Time. Only> > two packages, special packages.They perform a high insert workload.> > Make linsert in the journal table.The insert are always at the end> >of the table> >> > We already checked all the possible cause that are in the book: CPU> > is ok, paging is ok, not online monitoring, is not DRDA, SERV TASK> > SWITCH is ok.> >> > The corious is one day have a high not account time, and the following> > day have a low not account time. Every day the program use a> >different table.(JRN1 o JRN2)> >The tables should be equals, but for some historical reason we found> >the tablespaces are differnent> >> >JRN1 is Compress NO and Segmented Table. When is used the NOT ACCOUNT> >time is high.> >JRN2 is Compress YES and SIMPLE table. When is used the NOT ACCOUNT > time is low.> >> >> >In V7, we didn't have any problem with this two tables. But now (one> >month ago) we upgrade to V8. Since the upgrade to V8 we have this> >behavior.> >> >> >V8 change the behavior with the compressed tables o with the segmented> >table ? And what is the relation with the NOT ACCOUNT time.> >> >Thank you in advance.> >> >> > RR> >> >_________________________________________________________________> _____> > ______________________________________________________________________> > * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *> ______________________________________________________________________> > > > The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
_________________________________________________________________
Windows Live Hotmail now works up to 70% faster.
http://windowslive.com/Explore/Hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_faster_112008
______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Renzo Razzetti

Re: NOT ACCOUNT Updated
(in response to Deb Ghosh)
Roger,

From the list you give, I would like to know how we can prove or discard the
following:
1) DB2 internal suspend and resume can cause this not accounted time by
looping when several threads are waiting for the same resource, but this
case is very rare.

Why ? Because we are in a intensive online insert workload. Where the
threads have to wait for the space map page to insert. In V8 we have

- low account time when the table is Simple
- and high account time when the table is segmented.


But, I can not find the theory to support my suspect.

thank you

RR


On Tue, Nov 25, 2008 at 2:57 AM, Roger Miller <[login to unmask email]> wrote:
> The next dozen or so reasons are noted in this online support document:
> http://www.ibm.com/support/docview.wss?rs=64&uid=swg21045823
>
> I don't see any direct reason for this listed there, but some of these
cases
> might work in your situation.
>
> Roger
>
> On Mon, 24 Nov 2008 11:13:54 +0800, Renzo razzetti
> <[login to unmask email]> wrote:
>
>> Hi all
>>
>>I have more data for this problem.
>>
>>
>> We are running DB2 V8 datasharing.
>> We found in production two package with a high Not Account Time. Only
>> two packages, special packages.They perform a high insert workload.
>> Make linsert in the journal table.The insert are always at the end
>>of the table
>>
>> We already checked all the possible cause that are in the book: CPU
>> is ok, paging is ok, not online monitoring, is not DRDA, SERV TASK
>> SWITCH is ok.
>>
>> The corious is one day have a high not account time, and the following
>> day have a low not account time. Every day the program use a
>>different table.(JRN1 o JRN2)
>>The tables should be equals, but for some historical reason we found
>>the tablespaces are differnent
>>
>>JRN1 is Compress NO and Segmented Table. When is used the NOT ACCOUNT
>>time is high.
>>JRN2 is Compress YES and SIMPLE table. When is used the NOT ACCOUNT
> time is low.
>>
>>
>>In V7, we didn't have any problem with this two tables. But now (one
>>month ago) we upgrade to V8. Since the upgrade to V8 we have this
>>behavior.
>>
>>
>>V8 change the behavior with the compressed tables o with the segmented
>>table ? And what is the relation with the NOT ACCOUNT time.
>>
>>Thank you in advance.
>>
>>
>> RR
>>
>>_________________________________________________________________
> _____
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms
>

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Roger Miller

Re: NOT ACCOUNT Updated
(in response to Renzo Razzetti)
I think you forgot the word not in this last description. Do you have the
accounting traces and statistics traces for this application? The first item I'd
want to look at would be the accounting reports to compare one day versus
the other for this variation, with the numbers and more background.

At first glance, I don't think your hypothesis is workable. The space map
entries are larger for segmented structures, so you generally get more space
map pages, with a little better space search technique, lots better if the data
length is highly variable.

What are the all the class 3 wait times, especially lock and latch? How many
indexes do these tables have? Are you data sharing? Are your inserts random
or ascending or descending or something else? What is the free space
distribution in data, in indexes? Are you getting page splits? Is the data
variable length? How variable? How many threads are inserting
concurrently? What is the aggregate insert rate? How much logging are you
doing?

How much disk space are you using for the compressed table? for the
uncompressed one? What is the compression ration you are getting for this
table? How frequently do these tables get reorganized?

I suspect that the best approach is to gather up the performance traces you
have, the DDL, the catalog queries, the service level, and open a problem
record, PMR, or ETR. It's likely to require some additional tracing.

The big changes for insert performance are in DB2 9. You might want to
check section 5.4.1 Insert improvements with DB2 9 for z/OS in the book
SG24-7473 DB2 9 Performance Topics to see the page split, larger page size,
section 5.6 for logging improvements, 5.8 for preformat and prefetch, universal
table spaces, ...

Roger Miller

On Tue, 25 Nov 2008 09:44:40 +0800, Renzo razzetti
<[login to unmask email]> wrote:

>Roger,
>
>From the list you give, I would like to know how we can prove or discard the
>following:
>1) DB2 internal suspend and resume can cause this not accounted time by
>looping when several threads are waiting for the same resource, but this
>case is very rare.
>
>Why ? Because we are in a intensive online insert workload. Where the
>threads have to wait for the space map page to insert. In V8 we have
>
> - low account time when the table is Simple
> - and high account time when the table is segmented.
>
>
>But, I can not find the theory to support my suspect.
>
>thank you
>
>RR
>
>
>On Tue, Nov 25, 2008 at 2:57 AM, Roger Miller <[login to unmask email]> wrote:
>> The next dozen or so reasons are noted in this online support document:
>> http://www.ibm.com/support/docview.wss?rs=64&uid=swg21045823
>>
>> I don't see any direct reason for this listed there, but some of these
>cases
>> might work in your situation.
>>
>> Roger
>>
>> On Mon, 24 Nov 2008 11:13:54 +0800, Renzo razzetti
>> <[login to unmask email]> wrote:
>>
>>> Hi all
>>>
>>>I have more data for this problem.
>>>
>>>
>>> We are running DB2 V8 datasharing.
>>> We found in production two package with a high Not Account Time. Only
>>> two packages, special packages.They perform a high insert workload.
>>> Make linsert in the journal table.The insert are always at the end
>>>of the table
>>>
>>> We already checked all the possible cause that are in the book: CPU
>>> is ok, paging is ok, not online monitoring, is not DRDA, SERV TASK
>>> SWITCH is ok.
>>>
>>> The corious is one day have a high not account time, and the following
>>> day have a low not account time. Every day the program use a
>>>different table.(JRN1 o JRN2)
>>>The tables should be equals, but for some historical reason we found
>>>the tablespaces are differnent
>>>
>>>JRN1 is Compress NO and Segmented Table. When is used the NOT
ACCOUNT
>>>time is high.
>>>JRN2 is Compress YES and SIMPLE table. When is used the NOT ACCOUNT
>> time is low.
>>>
>>>
>>>In V7, we didn't have any problem with this two tables. But now (one
>>>month ago) we upgrade to V8. Since the upgrade to V8 we have this
>>>behavior.
>>>
>>>
>>>V8 change the behavior with the compressed tables o with the segmented
>>>table ? And what is the relation with the NOT ACCOUNT time.
>>>
>>>Thank you in advance.
>>>
>>>
>>> RR
>>>
>>>______________________________________________________________

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms