DBM1 TCB Time - any solution?

Lisa Ouellette

DBM1 TCB Time - any solution?
I have a CICS application that uses declared temporary tables. On some of
them the application creates an index. So on my DB2 statistics report for a
day in one of the members I see:

SQL DDL QUANTITY
------------------------- --------
CREATE INDEX 18125.00
DROP TABLE 76506.00

In my TEMP database buffer pool I see:

BP9 GENERAL QUANTITY
--------------------------- --------
CURRENT ACTIVE BUFFERS 248.66
NUMBER OF DATASET OPENS 36242.00

The dataset opens are for the indexes.

I believe the dataset opens are the cause of what I consider high TCB time in
my DBM1 address space:

CPU TIMES TCB TIME SRB TIME
------------------------------- --------------- ---------------
SYSTEM SERVICES ADDRESS SPACE 1:41.601988 51:51.377789
DATABASE SERVICES ADDRESS SPACE 1:00:30.741648 1:14:18.637333

Is there anything I can do to do?

Seems odd there are twice as many opens as index creations? Why isn't the
index in the TEMP tablespaces like the table? Can I get it there somehow?

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on December 7th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Joel Goldstein

Re: DBM1 TCB Time - any solution?
(in response to Lisa Ouellette)
Lisa,

What is the elapsed time for those statistics?

It looks like your MSTR times are high too.

How do you know all the opens are for the indexes?

Regards,
Joel


Joel Goldstein
Responsive Systems
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works......
Predicts Group Buffer Pool performance too!
www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: "Lisa Ouellette" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Friday, December 07, 2007 9:53 AM
Subject: [DB2-L] DBM1 TCB Time - any solution?


I have a CICS application that uses declared temporary tables. On some of
them the application creates an index. So on my DB2 statistics report for a
day in one of the members I see:

SQL DDL QUANTITY
------------------------- --------
CREATE INDEX 18125.00
DROP TABLE 76506.00

In my TEMP database buffer pool I see:

BP9 GENERAL QUANTITY
--------------------------- --------
CURRENT ACTIVE BUFFERS 248.66
NUMBER OF DATASET OPENS 36242.00

The dataset opens are for the indexes.

I believe the dataset opens are the cause of what I consider high TCB time
in
my DBM1 address space:

CPU TIMES TCB TIME SRB TIME
------------------------------- --------------- ---------------
SYSTEM SERVICES ADDRESS SPACE 1:41.601988 51:51.377789
DATABASE SERVICES ADDRESS SPACE 1:00:30.741648 1:14:18.637333

Is there anything I can do to do?

Seems odd there are twice as many opens as index creations? Why isn't the
index in the TEMP tablespaces like the table? Can I get it there somehow?

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the overall
user experience of DB2-L. These changes are coming on December 7th.
Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on December 7th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Lisa Ouellette

Re: DBM1 TCB Time - any solution?
(in response to Joel Goldstein)
>What is the elapsed time for those statistics? 24 hours.

>How do you know all the opens are for the indexes? The only thing in BP9 is
the TEMP database. In the DBM1 system messages I see bunches of these:

IGD103I SMS ALLOCATED TO DDNAME SYS00001
IGD104I D4DP40.DSNDBC.DBD4DP41.TIX00035.I0001.A001 RETAINED,
DDNAME=SYS00001
IGD104I D4DP40.DSNDBC.DBD4DP41.TIX00035.I0001.A001 RETAINED,
DDNAME=A00002E4
IGD104I D4DP40.DSNDBC.DBD4DP41.TIX00035.I0001.A001 RETAINED,
DDNAME=A00002E5
IGD103I SMS ALLOCATED TO DDNAME SYS00002
IGD104I D4DP40.DSNDBC.DBD4DP41.TIX00035.I0001.A001 RETAINED,
DDNAME=SYS00002
IGD104I D4DP40.DSNDBC.DBD4DP41.TIX00035.I0001.A001 RETAINED,
DDNAME=A00002E9
IGD104I D4DP40.DSNDBC.DBD4DP41.TIX00035.I0001.A001 RETAINED,
DDNAME=A00002EA

Where DBD4DP41 is the temp database name.

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on December 7th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Deb Ghosh

Re: DBM1 TCB Time - any solution?
(in response to Lisa Ouellette)

Only way to find which datsets are being closed would be from SMF record type 64 easily.> Date: Fri, 7 Dec 2007 10:41:25 -0500> From: [login to unmask email]> Subject: Re: [DB2-L] DBM1 TCB Time - any solution?> To: [login to unmask email]> > Lisa,> > What is the elapsed time for those statistics?> > It looks like your MSTR times are high too.> > How do you know all the opens are for the indexes?> > Regards,> Joel> > > Joel Goldstein> Responsive Systems> Buffer Pool Tool for DB2, the worldwide industry standard> Performance software that works......> Predicts Group Buffer Pool performance too!> www.responsivesystems.com> tel. (732) 972-1261> fax.(732) 972-9416> ----- Original Message ----- > From: "Lisa Ouellette" <[login to unmask email]>> Newsgroups: bit.listserv.db2-l> To: <[login to unmask email]>> Sent: Friday, December 07, 2007 9:53 AM> Subject: [DB2-L] DBM1 TCB Time - any solution?> > > I have a CICS application that uses declared temporary tables. On some of> them the application creates an index. So on my DB2 statistics report for a> day in one of the members I see:> > SQL DDL QUANTITY> ------------------------- --------> CREATE INDEX 18125.00> DROP TABLE 76506.00> > In my TEMP database buffer pool I see:> > BP9 GENERAL QUANTITY> --------------------------- --------> CURRENT ACTIVE BUFFERS 248.66> NUMBER OF DATASET OPENS 36242.00> > The dataset opens are for the indexes.> > I believe the dataset opens are the cause of what I consider high TCB time > in> my DBM1 address space:> > CPU TIMES TCB TIME SRB TIME> ------------------------------- --------------- ---------------> SYSTEM SERVICES ADDRESS SPACE 1:41.601988 51:51.377789> DATABASE SERVICES ADDRESS SPACE 1:00:30.741648 1:14:18.637333> > Is there anything I can do to do?> > Seems odd there are twice as many opens as index creations? Why isn't the> index in the TEMP tablespaces like the table? Can I get it there somehow?> > IMPORTANT NOTICE:> > IDUG is pleased to announce a series of upgrades to the DB2-L discussion > listserv that are being implemented to improve reliability and the overall > user experience of DB2-L. These changes are coming on December 7th. > Details at http://www.idug.org> > ---------------------------------------------------------------------------------> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home > page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select > "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. > The IDUG List Admins can be reached at [login to unmask email] Find > out the latest on IDUG conferences at http://conferences.idug.org/index.cfm> > IMPORTANT NOTICE:> > IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on December 7th. Details at http://www.idug.org > > ---------------------------------------------------------------------------------> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
_________________________________________________________________
You keep typing, we keep giving. Download Messenger and join the i’m Initiative now.
http://im.live.com/messenger/im/home/?source=TAGLM
IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on December 7th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: DBM1 TCB Time - any solution?
(in response to Deb Ghosh)
Lisa,
In the case where you can potencially have many tables per table space as in
the case of TTs and there indexes comparing Creates to Opens is like
comparing apples to oranges.

How many times does "DBD4DP41" appear in your DBM1 log? compared to the
number of opens?

I think a OPEN after a psudo close counts as an open but would not have a
corresponding IGD104I created by a real close and deallocate.

Judging from the spacename TIX00035 it seems you may have a lot of TEMP
tablespaces. I just dealt wth a problem where too few TEMP tablespaces
resulted in suspend delays at TT create time. I wonder if too many TEMP
tablespaces are causeing the OPEN problem due to round robin use and lack of
long periods between reuse.

Regards
Avram Friedman

On Fri, 7 Dec 2007 10:55:04 -0500, Lisa Ouellette
<[login to unmask email]> wrote:

>>What is the elapsed time for those statistics? 24 hours.
>
>>How do you know all the opens are for the indexes? The only thing in BP9 is
>the TEMP database. In the DBM1 system messages I see bunches of these:
>
>IGD103I SMS ALLOCATED TO DDNAME SYS00001
>IGD104I D4DP40.DSNDBC.DBD4DP41.TIX00035.I0001.A001 RETAINED,
>DDNAME=SYS00001
>IGD104I D4DP40.DSNDBC.DBD4DP41.TIX00035.I0001.A001 RETAINED,
>DDNAME=A00002E4
>IGD104I D4DP40.DSNDBC.DBD4DP41.TIX00035.I0001.A001 RETAINED,
>DDNAME=A00002E5
>IGD103I SMS ALLOCATED TO DDNAME SYS00002
>IGD104I D4DP40.DSNDBC.DBD4DP41.TIX00035.I0001.A001 RETAINED,
>DDNAME=SYS00002
>IGD104I D4DP40.DSNDBC.DBD4DP41.TIX00035.I0001.A001 RETAINED,
>DDNAME=A00002E9
>IGD104I D4DP40.DSNDBC.DBD4DP41.TIX00035.I0001.A001 RETAINED,
>DDNAME=A00002EA
>
>Where DBD4DP41 is the temp database name.
>
>IMPORTANT NOTICE:
>
>IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the overall user
experience of DB2-L. These changes are coming on December 7th. Details at
http://www.idug.org
>
>--------------------------------------------------------------------------
-------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-
l.org. The IDUG List Admins can be reached at [login to unmask email]
l.org. Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on December 7th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm