Application Optimization and Season's Greetings

Inc. The Fillmore Group

Application Optimization and Season's Greetings
I have a client that is migrating an existing application to DB2 UDB on z/Linux. We are prepping all of the programs with EXPLAIN to determine the access
paths and problem code that needs further attention. My challenge is this: it's easy to tune a single query, but we have hundreds of SQL statements
imbedded in dozens of programs. I can identify the high-cost queries and focus on them. I can use db2exfmt and db2expln to present the data in the
EXPLAIN tables. It just seems a cumbersome, time-consuming approach. My fear is that I will end up with a bunch of indexes that solve one-off problem
queries.

Is there a comprehensive, holistic way to look at the EXPLAIN results and design indexes - or even MQTs - that will optimize multiple SQL statements. The
SQL is imbedded in Cobol and being prepped on another operating system, so I think that extracting the SQL for the Design Advisor would be more trouble
than it's worth. My best bet is to work with the EXPLAIN tables.

Any ideas (including third-party tools)?

Thanks,
Frank

P.S.

Merry Christmas!
Happy Channukah!
Blessed Kwanzaa!
Prayerful Ramadan!
Delightful Diwali!
Joyeux Noel et Bonne Année! (French)
YENI YILINIZ KUTLU OLSUN (Happy New Year in Turkish)
Nadolig Llawen a Blwyddyn Newydd Dda (Welsh)
Folle lok en seine (Frysian)
Gelukkig nieuwjaar (Dutch)

And a safe, happy, healthy, and prosperous 2007 to us all.

Frank

P.P.S. And if I missed a celebration that is important to you, please e-mail it to me and I will honor it next year.


+-------------------------------------+--------------------------------------+
| Frank C. Fillmore, Jr. | Voice/Fax: 410.465.6335 |
| The Fillmore Group, Inc. (TFG) | Class registration: 800-TFG-RDBMs |
| 3213-A Corporate Court | E-mail: [login to unmask email] |
| Ellicott City, Maryland 21042-2247 | URL: http://www.thefillmoregroup.com |
| USA | |
+-------------------------------------+--------------------------------------+
| DB2 Family, Oracle, Client/Server, Distributed Database |
| "Relational Database Solutions"(sm) |
+----------------------------------------------------------------------------+

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

Dan Tuck

Re: Application Optimization and Season's Greetings
(in response to Inc. The Fillmore Group)
"The DB2 Design Advisor advises users on the creation of materialized query
tables (MQTs) and indexes, the repartitioning of tables, the conversion to
multidimensional clustering (MDC) tables, and the deletion of unused
objects. The recommendations are based on one or more SQL statements
provided by the user. A group of related SQL statements is known as a
workload. Users can rank the importance of each statement in a workload and
specify the frequency at which each statement in the workload is to be
executed. The Design Advisor outputs a DDL CLP script that includes CREATE
INDEX, CREATE SUMMARY TABLE (MQT), and CREATE TABLE statements to create the
recommended objects. "

From the IBM manual...


On 12/20/06, The Fillmore Group, Inc. <[login to unmask email]> wrote:
>
> I have a client that is migrating an existing application to DB2 UDB on
> z/Linux. We are prepping all of the programs with EXPLAIN to determine the
> access
> paths and problem code that needs further attention. My challenge is
> this: it's easy to tune a single query, but we have hundreds of SQL
> statements
> imbedded in dozens of programs. I can identify the high-cost queries and
> focus on them. I can use db2exfmt and db2expln to present the data in the
> EXPLAIN tables. It just seems a cumbersome, time-consuming approach. My
> fear is that I will end up with a bunch of indexes that solve one-off
> problem
> queries.
>
> Is there a comprehensive, holistic way to look at the EXPLAIN results and
> design indexes - or even MQTs - that will optimize multiple SQL
> statements. The
> SQL is imbedded in Cobol and being prepped on another operating system, so
> I think that extracting the SQL for the Design Advisor would be more trouble
> than it's worth. My best bet is to work with the EXPLAIN tables.
>
> Any ideas (including third-party tools)?
>
> Thanks,
> Frank
>
> P.S.
>
> Merry Christmas!
> Happy Channukah!
> Blessed Kwanzaa!
> Prayerful Ramadan!
> Delightful Diwali!
> Joyeux Noel et Bonne Année! (French)
> YENI YILINIZ KUTLU OLSUN (Happy New Year in Turkish)
> Nadolig Llawen a Blwyddyn Newydd Dda (Welsh)
> Folle lok en seine (Frysian)
> Gelukkig nieuwjaar (Dutch)
>
> And a safe, happy, healthy, and prosperous 2007 to us all.
>
> Frank
>
> P.P.S. And if I missed a celebration that is important to you, please
> e-mail it to me and I will honor it next year.
>
>
>
> +-------------------------------------+--------------------------------------+
> | Frank C. Fillmore, Jr. | Voice/Fax:
> 410.465.6335 |
> | The Fillmore Group, Inc. (TFG) | Class
> registration: 800-TFG-RDBMs |
> | 3213-A Corporate Court | E-mail: [login to unmask email]
> |
> | Ellicott City, Maryland 21042-2247 | URL:
> http://www.thefillmoregroup.com |
> | USA
> | |
>
> +-------------------------------------+--------------------------------------+
> | DB2 Family, Oracle, Client/Server, Distributed
> Database |
> | "Relational Database
> Solutions"(sm) |
>
> +----------------------------------------------------------------------------+
>
>
> ---------------------------------------------------------------------------------
> 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
>

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

Aurora Dell'Anno

Re: Application Optimization and Season's Greetings
(in response to Dan Tuck)
Frank,

how about using Strobe? You can analyse your application, pinpoint which are the most CPU intensive queries, use the EXPLAIN functionality in iStrobe, and generally manage your DB2 applications' performance (and have a look at outside/around DB2, too).

Oh, and happy holidays to you too...

ciao!

Aurora Emanuela Dell'Anno
Compuware Ltd.
Systems Engineer, Mainframe pre-Sales
___________________________________________________________________________________________

email: [login to unmask email]
tel. : +44 (0)1753 444331
cell.: +44 (0)7779 881331

___________________________________________________________________________________________

No trees were killed in the sending of this message. However - a large number of electrons were terribly inconvenienced.

-----Original Message-----
From: The Fillmore Group, Inc. [mailto:[login to unmask email]
Sent: 20 December 2006 21:19
Subject: Application Optimization and Season's Greetings

I have a client that is migrating an existing application to DB2 UDB on z/Linux. We are prepping all of the programs with EXPLAIN to determine the access paths and problem code that needs further attention. My challenge is this: it's easy to tune a single query, but we have hundreds of SQL statements imbedded in dozens of programs. I can identify the high-cost queries and focus on them. I can use db2exfmt and db2expln to present the data in the EXPLAIN tables. It just seems a cumbersome, time-consuming approach. My fear is that I will end up with a bunch of indexes that solve one-off problem queries.

Is there a comprehensive, holistic way to look at the EXPLAIN results and design indexes - or even MQTs - that will optimize multiple SQL statements. The SQL is imbedded in Cobol and being prepped on another operating system, so I think that extracting the SQL for the Design Advisor would be more trouble than it's worth. My best bet is to work with the EXPLAIN tables.

Any ideas (including third-party tools)?

Thanks,
Frank

P.S.

Merry Christmas!
Happy Channukah!
Blessed Kwanzaa!
Prayerful Ramadan!
Delightful Diwali!
Joyeux Noel et Bonne Année! (French)
YENI YILINIZ KUTLU OLSUN (Happy New Year in Turkish) Nadolig Llawen a Blwyddyn Newydd Dda (Welsh) Folle lok en seine (Frysian) Gelukkig nieuwjaar (Dutch)

And a safe, happy, healthy, and prosperous 2007 to us all.

Frank

P.P.S. And if I missed a celebration that is important to you, please e-mail it to me and I will honor it next year.


+-------------------------------------+--------------------------------------+
| Frank C. Fillmore, Jr. | Voice/Fax: 410.465.6335 |
| The Fillmore Group, Inc. (TFG) | Class registration: 800-TFG-RDBMs |
| 3213-A Corporate Court | E-mail: [login to unmask email] |
| Ellicott City, Maryland 21042-2247 | URL: http://www.thefillmoregroup.com |
| USA | |
+-------------------------------------+--------------------------------------+
| DB2 Family, Oracle, Client/Server, Distributed Database |
| "Relational Database Solutions"(sm) |
+----------------------------------------------------------------------------+

---------------------------------------------------------------------------------
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
The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

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