[SPAM] Re: Performance Database

Daniel Luksetich

[SPAM] Re: Performance Database
No reason why PDB can't be used to condense information for input into DB2
tables. One pass daily through PDB and the result loaded into DB2. I've seen
this as well.

Being able to query a set of DB2 performance tables is pretty valuable.

As far as taking a meaningful sample. That works too. The bottom line is
reporting on that meaningful stuff and then taking meaningful action.

Dan

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

>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

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

Avram Friedman

Re: Performance Database
(in response to Daniel Luksetich)
On Thu, 20 Jan 2011 21:56:27 +0000, Ted MacNEIL <[login to unmask email]> wrote:

>>The down side is SAS tends to be sequencial processing..
>
>Have you used SAS?
>Sequential is as sequential does.
>
Yes I have used SAS, my first experience was beta testing the original commercial version when it could be obtained for $10 to pay for the tape and postage. This may make me one of the oldest SAS users around. My beta test results were if you forget ';' at the end of statements SAS tends to loop. It got fixed in 1972.

Sequential is the industry standard for performace data.
SMF is sequencial
Most monitors like TMON DB2, Mainview DB2, Insight DB2, Omegamon DB2, and PM before it was combined with Omegamon capture trace information and write it to a sequencial short term history file to handle interactive requests. Just like SMF when the short term file fills or a switch occurs the short term information is written to a longer term sequential repository usually not available to the online monitor. The original question was about the optional PE step about storing information in a DB2 based performace database. Lots of intelectual interest, few if any practial applications.

>>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.
>
yes and no.
The first time a raw data file is processed a SAS file is created.
This is actually one ofs the more expensive parts of SAS processing because fields are converted into one of SASs internal formats and a data dictionary is built. Apending several identically structured SAS files is programatic but fast and easy

>And, with UPDATE/MERGE you don't need daily files.
>.
The reason for the daily or short term files is to support querries like tell me about instances of PLAN xyz where get pages greater than 1000.
You don't want to processing a years worth of data when you are only interested in yesterday.
You don't want to build a timestamp index as it complicates database utilities and is not going to work very well at best.
Almost any use of timestamp as a search condition involves inequalties
Can bet big money that there is no recent runstats or properly coded runstats
Will usally require several indexes and each record / row will have to be fetched and examined any way
In our example above PLAN xyz where get pages greater than 1000.
I bet get pages is not indexed
And I could of just as easy suggest SQL calls or Elapsed Time or USER ID or CPU time or etc etc etc
Sequencial is as Sequencial is and will be much faster than DB2 direct access via several indexs if file sizes are managed by using reasonable time ranges. In classic terms father son sequencial processing almost always beats random access in batch processing.
I does suffer from the lack of a kewlness factor.

Some one in this thread suggested a Performace Database could be one of a shops biggest applications.
A peformance database for a medium to large shop using DB2 as a repository and well indexed will not be one of the biggest applications in the shop. That is too kind, It will be the biggest application by a long shot.

Avram Friedman


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

_____________________________________________________________________
* 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

Max Scarpa

Re: Performance Database
(in response to Avram Friedman)
Hi Dan

How're thing going ? Hope you and your bos are OK :-)

I created a performance database in the past (based on some papers from
Conferences) and it's relatively simple to create & use it and is simple
to create programs/REXX execs to extract critical informations. I tried to
automate warnings/alerts at maximum as we were few people working in Db2
area (actually only 1 ) but we had small to medium DB2s so we were able
to use it for tuning, almost every day.
On the contrary in a big shop I found a useful a performance DB but only
few people looked at data in the database so we were missing a good
performance tool. We used a lot of CPU to extract/load data but no one was
able/had the time to use that data because she/he was doing some more
important things. What I want to say is a performance db is useful if
there's a department/office/whatever who can analyze and apply results
from performance db data analysis, otherwise it's a mere exercise and a
cost.
I witnesssed the build of another database, used a lot for some months,
then left in a corner with jobs running every day but after a while (when
consultants left that company) no one remembered what these jobs were
running for :-(. It's what I call 'jobs stratigraphy', or the successive
'deposition' of strata of jcl in a automatic scheduler, where after years
you find 'fossil jcls' and you've to understand what they do and if you
can remove them from schedule (and of course doc was
missing/inadequate/never written). Sometimes with bad results. This is
very common in medium to big shops, but even in the small.

I used MXG for DDF performance as well, I really liked it, it's powerful
and PDBs help. But it uses SAS, who's a big cpu consumer (we had to run
jobs in a dedicated windows) and it cost a nice amount of money. So again
it's worth if you have a structure in your company, looking from time to
time to db solve some problems, but only few of many.

Or at least this was my experience. I knew some big banks in Italy have a
structure for performance analysis, so YMMV, as usual

Regards

Max Scarpa




_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

Mike Vaughan

Re: Performance Database
(in response to Max Scarpa)
I agree that having the ability to look at the smf records is critical. I personally don't consider it a "performance database" since I use these records as much for trouble-shooting as I do for performance analysis and trending. Yes, depending on your volume, the amount of data can be overwhelming if you are processing/loading detailed statistics, so I would suggest determining if summarized records would meet your needs (and what level of summarization). I would also recommend looking closely at the "out of the box" method of handling this data (including performing the summarization) using your vendor tools and determine if there's more efficient methods. For example, some of the comments regarding cost of insert/delete can be easily resolved with utilities.

Several years ago we did load all of the detailed records to tables and I agree it was overwhelming -- great to use when you wanted to see what was going on, but the volume was not maintainable. Currently we process/load a summarized version of the DB2 SMF accounting data (hourly summary based on planname, correlation-id, and a handful of other columns) which results in loading a fraction of the number of records we normally would load. This meets the majority of our needs, and we also do have the option of loading the detailed records on an as-needed basis when a closer look is required. We store these in a tables partitioned by date, so the "purge" is just a matter of a load-replace on the partition we want to eliminate (followed by a rotate).

Regarding the thought of just keeping the data in a flat file -- these records can be very useful if people look at them and the harder you make it to view these records, the less likely it is that they will be used. For any kind of long-term analysis and trending, it's pretty hard to beat having the information readily available in a table for querying. Realistically while this information is valuable for a DBA, it can be extremely valuable for the developer who is supporting applications as well.

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

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


-----Message Disclaimer-----

This e-mail message is intended only for the use of the individual or
entity to which it is addressed, and may contain information that is
privileged, confidential and exempt from disclosure under applicable law.
If you are not the intended recipient, any dissemination, distribution or
copying of this communication is strictly prohibited. If you have
received this communication in error, please notify us immediately by
reply email to [login to unmask email] and delete or destroy all copies of
the original message and attachments thereto. Email sent to or from the
Principal Financial Group or any of its member companies may be retained
as required by law or regulation.

Nothing in this message is intended to constitute an Electronic signature
for purposes of the Uniform Electronic Transactions Act (UETA) or the
Electronic Signatures in Global and National Commerce Act ("E-Sign")
unless a specific statement to the contrary is included in this message.

While this communication may be used to promote or market a transaction
or an idea that is discussed in the publication, it is intended to provide
general information about the subject matter covered and is provided with
the understanding that The Principal is not rendering legal, accounting,
or tax advice. It is not a marketed opinion and may not be used to avoid
penalties under the Internal Revenue Code. You should consult with
appropriate counsel or other advisors on all matters pertaining to legal,
tax, or accounting obligations and requirements.

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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