Performance Database

jack fernicola

Performance Database
Hi,

We are building a Performance Database and then would like to populate it with raw DB2 SMF (100,101,102) records for historical reporting. The latest Omegamon/DB2PM (4.2)product has been installed for this purpose. 
 
Does anyone have any sample JCL/jobs/mapping for extracting the DB2 SMF data records only to be use as input into the LOAD utility jobs?
 
Thanks
Jack        
 

 
 
 




_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Daniel Luksetich

Re: Performance Database
(in response to jack fernicola)
I just did this for a customer. It's very straight forward. The Omegamon doc
is pretty specific on how to do this. All you need to do is set up the table
spaces. Indexes help too. Everything else is provided by Omegamon.



I don't have everything in front of me because I'm not on site today, but
the manuals describe it pretty well. If you get stuck email me directly and
I can give you more info.



Dan



Daniel L Luksetich

IBM Information Champion

IBM Certified Database Administrator - DB2 10 for z/OS

IBM Certified System Administrator - DB2 9 for z/OS

IBM Certified Solutions Expert - DB2 Universal Database V7.1 Database
Administration for UNIX, Windows, and OS/2

IBM Certified Solutions Expert - DB2 UDB V7.1 Family Application Development

IBM Certified Advanced Technical Expert - DB2 Data Replication



Vice President of Global Database Operations

YL&A, Inc.

Database Performance Professionals

http://www.ylassoc.com

http://www.db2expert.com

http://www-01.ibm.com/software/data/champion/profiles/luksetich.html







From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of jack fernicola
Sent: Thursday, January 20, 2011 11:23 AM
To: [login to unmask email]
Subject: [SPAM] Performance Database




Hi,


We are building a Performance Database and then would like to populate it
with raw DB2 SMF (100,101,102) records for historical reporting. The latest
Omegamon/DB2PM (4.2)product has been installed for this purpose.



Does anyone have any sample JCL/jobs/mapping for extracting the DB2 SMF data
records only to be use as input into the LOAD utility jobs?



Thanks

Jack












_____

< http://www-01.ibm.com/software/data/db2/zos/db2-10/ > Introducing IBMR DB2R
10 for z/OS

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please < http://www.idug.org/register > register
here.

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.872 / Virus Database: 271.1.1/3383 - Release Date: 01/19/11
13:34:00


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Carol Anne Sutfin

Re: Performance Database
(in response to Daniel Luksetich)
Jack,

The Performance Database is documented in Chapter 5 of the Installation
Guide.

The DDL, Load statements and table descriptions are in the TKO2SAMP library
Members are DGO***



Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]



From: jack fernicola <[login to unmask email]>
To: [login to unmask email]
Date: 01/20/2011 11:23 AM
Subject: [DB2-L] Performance Database
Sent by: IDUG DB2-L <[login to unmask email]>




Hi,

We are building a Performance Database and then would like to populate it with raw DB2 SMF (100,101,102) records for
historical reporting. The latest Omegamon/DB2PM (4.2)product has been installed for this purpose.

Does anyone have any sample JCL/jobs/mapping for extracting the DB2 SMF data records only to be use as input into
the LOAD utility jobs?

Thanks
Jack
















The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Avram Friedman

Re: Performance Database
(in response to Carol Anne Sutfin)
You might want to think twice or even 3 times about building a DB2 performace database using DB2 tables.

The 2 most common activities against a performace database are
INSERT
DELETE (for purge)

Guess what 2 basic SQL DML commands tend to be the most expensive.

DB2 is in general designed to store data with integerety and recoverability.
It has an associated cost.

You might want to consider Berry Merrils MXG system that will store your data as a SAS file.
It comes with lots of sample querry functions that are easy to modify.
The down side is SAS tends to be sequencial processing..
Create Daily files, they can be concatanated together if you need longer history
and they are easy to discard.

Avram Friedman


On Thu, 20 Jan 2011 09:23:03 -0800, jack fernicola <[login to unmask email]> wrote:

>Hi,
>
>We are�building a�Performance Database and then would like to populate it with raw DB2 SMF�(100,101,102) records for historical reporting. The�latest Omegamon/DB2PM (4.2)product has been installed for this purpose.�
>�
>Does�anyone�have any sample JCL/jobs/mapping for extracting the DB2 SMF data records�only to be use as�input into the LOAD utility jobs?
>�
>Thanks
>Jack��������
>�
>
>�
>�
>�



>
>_____________________________________________________________________
>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
>* Your only source for independent, unbiased, and trusted DB2 information. *
>_____________________________________________________________________
>http://www.IDUG.org/mentor
>Mentoring should be a rewarding experience for everyone...
>IDUG is offering up to 80% off when you both come to the conference!
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Joel Goldstein

Re: Performance Database
(in response to Avram Friedman)
I have to second Avram's caveat.
If you have a small to medium size DB2 system, it may be fine.
If you have a large high volume system, the PDB may become one of your major resource consumers.

Regards,
Joel

Joel Goldstein
Responsive Systems
IBM Gold Consultant
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works...... Predicts IO Rate !!
Predicts Group Buffer Pool performance too
www.responsivesystems.com

Buffer Pool Tool for DB2 on www.LinkedIn.com
Watch the 3-Minute Buffer Pool Tool Movie at: www.responsivesystems.com/Movie1

tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: "Avram Friedman" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, January 20, 2011 3:00 PM
Subject: Re: [DB2-L] Performance Database


You might want to think twice or even 3 times about building a DB2 performace database using DB2 tables.

The 2 most common activities against a performace database are
INSERT
DELETE (for purge)

Guess what 2 basic SQL DML commands tend to be the most expensive.

DB2 is in general designed to store data with integerety and recoverability.
It has an associated cost.

You might want to consider Berry Merrils MXG system that will store your data as a SAS file.
It comes with lots of sample querry functions that are easy to modify.
The down side is SAS tends to be sequencial processing..
Create Daily files, they can be concatanated together if you need longer history
and they are easy to discard.

Avram Friedman


On Thu, 20 Jan 2011 09:23:03 -0800, jack fernicola <[login to unmask email]> wrote:

>Hi,
>
>We are�building a�Performance Database and then would like to populate it with raw DB2 SMF�(100,101,102) records for historical reporting. The�latest Omegamon/DB2PM (4.2)product has been installed for this purpose.�
>�
>Does�anyone�have any sample JCL/jobs/mapping for extracting the DB2 SMF data records�only to be use as�input into the LOAD utility jobs?
>�
>Thanks
>Jack��������
>�
>
>�
>�
>�



>
>_____________________________________________________________________
>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
>* Your only source for independent, unbiased, and trusted DB2 information. *
>_____________________________________________________________________
>http://www.IDUG.org/mentor
>Mentoring should be a rewarding experience for everyone...
>IDUG is offering up to 80% off when you both come to the conference!
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Carol Anne Sutfin

Re: Performance Database
(in response to Joel Goldstein)
I cannot argue with the statement either.

Even with MXG there is too much data to look at.
I only use this to look at small pieces of time to investigate problems
with performance or conflicts.

Otherwise I could spend all of my time loading and reporting on the data.

It would also require more DASD that I am willing to expend.

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]



From: Joel Goldstein - Responsive Systems
<[login to unmask email]>
To: [login to unmask email]
Date: 01/20/2011 02:57 PM
Subject: Re: [DB2-L] Performance Database
Sent by: IDUG DB2-L <[login to unmask email]>



I have to second Avram's caveat.
If you have a small to medium size DB2 system, it may be fine.
If you have a large high volume system, the PDB may become one of your
major resource consumers.

Regards,
Joel

Joel Goldstein
Responsive Systems
IBM Gold Consultant
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works...... Predicts IO Rate !!
Predicts Group Buffer Pool performance too
www.responsivesystems.com

Buffer Pool Tool for DB2 on www.LinkedIn.com
Watch the 3-Minute Buffer Pool Tool Movie at:
www.responsivesystems.com/Movie1

tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: "Avram Friedman" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, January 20, 2011 3:00 PM
Subject: Re: [DB2-L] Performance Database

You might want to think twice or even 3 times about building a DB2
performace database using DB2 tables.

The 2 most common activities against a performace database are
INSERT
DELETE (for purge)

Guess what 2 basic SQL DML commands tend to be the most expensive.

DB2 is in general designed to store data with integerety and
recoverability.
It has an associated cost.

You might want to consider Berry Merrils MXG system that will store your
data as a SAS file.
It comes with lots of sample querry functions that are easy to modify.
The down side is SAS tends to be sequencial processing..
Create Daily files, they can be concatanated together if you need longer
history
and they are easy to discard.

Avram Friedman


On Thu, 20 Jan 2011 09:23:03 -0800, jack fernicola <[login to unmask email]>
wrote:

>Hi,
>
>We are�building a�Performance Database and then would like to populate it
with raw DB2 SMF�(100,101,102) records for historical reporting. The�latest
Omegamon/DB2PM (4.2)product has been installed for this purpose.�
>�
>Does�anyone�have any sample JCL/jobs/mapping for extracting the DB2 SMF data
records�only to be use as�input into the LOAD utility jobs?
>�
>Thanks
>Jack��������
>�
>
>�
>�
>�



>
>_____________________________________________________________________
>* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
>* Your only source for independent, unbiased, and trusted DB2
information. *
>_____________________________________________________________________
>http://www.IDUG.org/mentor
>Mentoring should be a rewarding experience for everyone...
>IDUG is offering up to 80% off when you both come to the conference!
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's Listserv








The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Daniel Luksetich

Re: Performance Database
(in response to Carol Anne Sutfin)
You can condense the thread data before loading. I find a performance DB critical. Especially when the application is capturing performance metrics which you can correlate to the performance DB. I call that a super performance DB, and the information it delivers is awesome.

So many people do fire fighting and never look at the big picture. They miss major tuning opportunities. A performance DB helps get that picture.

Dan

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of [login to unmask email]
Sent: Thursday, January 20, 2011 3:11 PM
To: [login to unmask email]
Subject: [SPAM] Re: Performance Database

I cannot argue with the statement either.

Even with MXG there is too much data to look at.
I only use this to look at small pieces of time to investigate problems
with performance or conflicts.

Otherwise I could spend all of my time loading and reporting on the data.

It would also require more DASD that I am willing to expend.

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]



From: Joel Goldstein - Responsive Systems
<[login to unmask email]>
To: [login to unmask email]
Date: 01/20/2011 02:57 PM
Subject: Re: [DB2-L] Performance Database
Sent by: IDUG DB2-L <[login to unmask email]>



I have to second Avram's caveat.
If you have a small to medium size DB2 system, it may be fine.
If you have a large high volume system, the PDB may become one of your
major resource consumers.

Regards,
Joel

Joel Goldstein
Responsive Systems
IBM Gold Consultant
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works...... Predicts IO Rate !!
Predicts Group Buffer Pool performance too
www.responsivesystems.com

Buffer Pool Tool for DB2 on www.LinkedIn.com
Watch the 3-Minute Buffer Pool Tool Movie at:
www.responsivesystems.com/Movie1

tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: "Avram Friedman" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, January 20, 2011 3:00 PM
Subject: Re: [DB2-L] Performance Database

You might want to think twice or even 3 times about building a DB2
performace database using DB2 tables.

The 2 most common activities against a performace database are
INSERT
DELETE (for purge)

Guess what 2 basic SQL DML commands tend to be the most expensive.

DB2 is in general designed to store data with integerety and
recoverability.
It has an associated cost.

You might want to consider Berry Merrils MXG system that will store your
data as a SAS file.
It comes with lots of sample querry functions that are easy to modify.
The down side is SAS tends to be sequencial processing..
Create Daily files, they can be concatanated together if you need longer
history
and they are easy to discard.

Avram Friedman


On Thu, 20 Jan 2011 09:23:03 -0800, jack fernicola <[login to unmask email]>
wrote:

>Hi,
>
>We are?building a?Performance Database and then would like to populate it
with raw DB2 SMF?(100,101,102) records for historical reporting. The?latest
Omegamon/DB2PM (4.2)product has been installed for this purpose.?
>?
>Does?anyone?have any sample JCL/jobs/mapping for extracting the DB2 SMF data
records?only to be use as?input into the LOAD utility jobs?
>?
>Thanks
>Jack????????
>?
>
>?
>?
>?



>
>_____________________________________________________________________
>* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
>* Your only source for independent, unbiased, and trusted DB2
information. *
>_____________________________________________________________________
>http://www.IDUG.org/mentor
>Mentoring should be a rewarding experience for everyone...
>IDUG is offering up to 80% off when you both come to the conference!
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's Listserv








The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.872 / Virus Database: 271.1.1/3383 - Release Date: 01/19/11 13:34:00

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Ted MacNEIL

Re: Performance Database
(in response to Daniel Luksetich)
>The down side is SAS tends to be sequencial processing..

Have you used SAS?
Sequential is as sequential does.

>Create Daily files, they can be concatanated together if you need longer history and they are easy to discard.

Concatenation is programmatic only.
You can not concatenate with JCL.

And, with UPDATE/MERGE you don't need daily files.

-
Ted MacNEIL
[login to unmask email]

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Ted MacNEIL

Re: Performance Database
(in response to Ted MacNEIL)
>If you have a large high volume system, the PDB may become one of your major resource consumers.

As a capacity/performance analyst for 30+ years, I have to chime in.

If you can't measure it, you can't manage it.

Instead of complaining, because you may not know, about the amount of space required, find an analyst who knows how to clear out timeframes when the performance statistics are meaningful.
In other words, you may only need an hour or so a day, instead of every minute of every day.
Also, depending on weekly/monthly and seasonality quirks you may need even less.

And, based on service levels, you may want an exception database.

So, instead of dismissing the so-called cost of DASD, find an expert who can manage the entire performance/capacity requirement in an optimal way.

You may be skilled as DBA's, but capacity/performance is a different discipline.

And, the cost of disk is not that high, anymore.
In Canada, $0.07/GB.

Of course, if you don't care about performance and productivity, none of this argument matters.

I mean nothing negative by this, but you have to take more into account than just the disk requirement.
-
Ted MacNEIL
[login to unmask email]

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv