Should developers take Index Design course?

Larry Kintisch

Should developers take Index Design course?
Hi Listers,
Some of you may know Tapio Lahdenmäki, who has retired from IBM Finland.
He helped develop QUBE and some of the IBM courses for DB2 performance. He
has written a Finnish book on Designing Better Indexes, soon to be
translated to English, and has developed a course, based on the book, which
he teaches in Europe. I've partnered with Tapio to bring the course to the
USA, and he will teach the first class Feb 4 - 6 in NYC (seats still
available).
Full details are at our website:
http://www.dbindexdesign.com

I wonder if anyone feels strongly as he does that if database
--developers-- knew how to esitimate a best access path and its cost
[timing], then fewer performance problems would hit production status and
fall into the DBA's domain.
Any comments?
Thanks, Larry Kintisch, ABLE Information Services, Nyack NY

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

Michael Ebert

Re: Should developers take Index Design course?
(in response to Larry Kintisch)
I second that. The way our developers design indexes, you'd wonder how
they ever manage to look up a phone number.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



Hi Listers,
Some of you may know Tapio Lahdenmäki, who has retired from IBM Finland.
He helped develop QUBE and some of the IBM courses for DB2 performance. He
has written a Finnish book on Designing Better Indexes, soon to be
translated to English, and has developed a course, based on the book,
which
he teaches in Europe. I've partnered with Tapio to bring the course to
the
USA, and he will teach the first class Feb 4 - 6 in NYC (seats still
available).
Full details are at our website:
http://www.dbindexdesign.com

I wonder if anyone feels strongly as he does that if database
--developers-- knew how to esitimate a best access path and its cost
[timing], then fewer performance problems would hit production status and
fall into the DBA's domain.
Any comments?
Thanks, Larry Kintisch, ABLE Information Services, Nyack NY


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

Nick Cianci

Re: Should developers take Index Design course?
(in response to Michael Ebert)
I concur with Dr Ebert; index design for developers would be a good start.

But just a start, some training in normalisation, and the pitfalls of doing
things in a pure OO world of Factless keys would be nice as well. If doing
indexes please cover cluster sequence and it's impact on cursor with in
cursor processing. The DBA teams I've been in over the years have seen
enough bad performers with root causes of 1, 2, or all 3 of the above to
fill a book.

<Rant mode>
A statement like please avoid the use of COOL:Gen at the start would also
be nice from my point of view.
</Rant mode>


I did a QUBE course many years ago in Melbourne, and liked it for the
background it provided. Haven't used it in a practical sense but I've been
using the information that came out of the course ever since. Thanks to the
developers of that course (if they are listening out there).


Cheers,
Nick F. Cianci
IBM Certified Solutions Expert
- DB2 UDB V7.1 Database Administration for OS/390


Non scholae sed vitae discimus.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Michael Ebert
Sent: Sunday, January 18, 2004 5:46 PM
To: [login to unmask email]
Subject: Re: Should developers take Index Design course?

I second that. The way our developers design indexes, you'd wonder how they
ever manage to look up a phone number.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany


Hi Listers,
Some of you may know Tapio Lahdenmäki, who has retired from IBM Finland.
He helped develop QUBE and some of the IBM courses for DB2 performance. He
has written a Finnish book on Designing Better Indexes, soon to be
translated to English, and has developed a course, based on the book, which
he teaches in Europe. I've partnered with Tapio to bring the course to the
USA, and he will teach the first class Feb 4 - 6 in NYC (seats still
available).


Full details are at our website: http://www.dbindexdesign.com

I wonder if anyone feels strongly as he does that if database
--developers-- knew how to esitimate a best access path and its cost
[timing], then fewer performance problems would hit production status and
all into the DBA's domain.


Any comments?


Thanks, Larry Kintisch, ABLE Information Services, Nyack NY

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

Philip Sevetson

Re: Should developers take Index Design course?
(in response to Nick Cianci)
Cool:Gen has its defenders. I'm one of them. With ***proper training***,
it's an excellent repository for DB2 DDL and (according to my erstwhile
associates at Travelers Insurance and SBC Telecommunications) a good way
to turn complex modeling requirements into executable code and keep the
model in synch with the code.

Using Cool:Gen, BMC Change Manager, and DB2, we designed a flexible
process for a significant part of a Commercial Lines insurance operation,
which successfully processed changes at high "velocity", enabling a few
DBAs to serve a large body of developers with timely and 100%-accurate
change control.

(It should be noted here that there was not a great requirement for
performance modeling associated with initial implementations. However,
the post-implementation performance team had no complaints about our
process.)

Without proper training, it's no more useful than any complex tool. You
might as well rant about DB2's poor performance when implemented without
experts available.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]






"Cianci, Nick" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/18/2004 05:34 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: [DB2-L] Should developers take Index Design course?

[snip]
<Rant mode>
A statement like please avoid the use of COOL:Gen at the start would also
be nice from my point of view.
</Rant mode>
[snip]

Cheers,
Nick F. Cianci
IBM Certified Solutions Expert
- DB2 UDB V7.1 Database Administration for OS/390

[snip]

Hi Listers,
Some of you may know Tapio Lahdenmäki, who has retired from IBM Finland. He helped develop QUBE and some of the IBM courses for DB2 performance. He has written a Finnish book on Designing Better Indexes, soon to be translated to English, and has developed a course, based on the book,
which he teaches in Europe. I've partnered with Tapio to bring the course to
the USA, and he will teach the first class Feb 4 - 6 in NYC (seats still available).

Full details are at our website: http://www.dbindexdesign.com

I wonder if anyone feels strongly as he does that if database --developers-- knew how to esitimate a best access path and its cost [timing], then fewer performance problems would hit production status and
all into the DBA's domain.

Any comments?
Thanks, Larry Kintisch, ABLE Information Services, Nyack NY

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

Paul A Redhead

Re: Should developers take Index Design course?
(in response to Philip Sevetson)





Phil,
You make an excellent point, and in general I agree. I am not a
Cool:Gen expert, but it
is likely there are some situations which can cause some discomfort. I
believe, for example, that
if you have an RI structure with, say 2 parents and one child table, and if
you wanted to insert records
into the child, then in something like COBOL you could perhaps read each
parent table once to
ensure the parent rows were present, and then insert a number of rows into
the child (or insert and
check sqlcode if DB2 RI is present, but that's something else entirely).
My understanding is the best the generated
Cool:Gen code will do is perform a parent check of each parent on every
insert, and I believe there are a few other
similar scenarios. The point being that the generated code, even at it's
optimum, is likely not going to be as good
as what could be coded in a 3GL.This scenario could obviously be taken to
the extreme with a rebuttal of 'so why
not use Assembler', however this would not change the situation from the
perspective of the SQL and calls to DB2.
Of course from an overall company perspective of time, money and having an
enterprise or application model it may
be that it is desirable, but I guess if your the DBA under pressure trying
to squeeze out every I/O and MIP you can tend to
lose sight of that and think of the code as a little foolish.

My 2c.

Paul.





[login to unmask email]@IDUGDB2-L.ORG> on 20/01/2004 01:19:45 AM

Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: Should developers take Index Design course?


Cool:Gen has its defenders. I'm one of them. With ***proper training***,
it's an excellent repository for DB2 DDL and (according to my erstwhile
associates at Travelers Insurance and SBC Telecommunications) a good way
to turn complex modeling requirements into executable code and keep the
model in synch with the code.

Using Cool:Gen, BMC Change Manager, and DB2, we designed a flexible
process for a significant part of a Commercial Lines insurance operation,
which successfully processed changes at high "velocity", enabling a few
DBAs to serve a large body of developers with timely and 100%-accurate
change control.

(It should be noted here that there was not a great requirement for
performance modeling associated with initial implementations. However,
the post-implementation performance team had no complaints about our
process.)

Without proper training, it's no more useful than any complex tool. You
might as well rant about DB2's poor performance when implemented without
experts available.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]






"Cianci, Nick" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/18/2004 05:34 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: [DB2-L] Should developers take Index Design
course?

[snip]
<Rant mode>
A statement like please avoid the use of COOL:Gen at the start would also
be nice from my point of view.
</Rant mode>
[snip]

Cheers,
Nick F. Cianci
IBM Certified Solutions Expert
- DB2 UDB V7.1 Database Administration for OS/390

[snip]

Hi Listers,
Some of you may know Tapio Lahdenmäki, who has retired from IBM Finland.
He helped develop QUBE and some of the IBM courses for DB2 performance. He
has written a Finnish book on Designing Better Indexes, soon to be
translated to English, and has developed a course, based on the book,
which he teaches in Europe. I've partnered with Tapio to bring the course
to
the USA, and he will teach the first class Feb 4 - 6 in NYC (seats still
available).

Full details are at our website: http://www.dbindexdesign.com

I wonder if anyone feels strongly as he does that if database
--developers-- knew how to esitimate a best access path and its cost
[timing], then fewer performance problems would hit production status and
all into the DBA's domain.

Any comments?
Thanks, Larry Kintisch, ABLE Information Services, Nyack NY

---------------------------------------------------------------------------------

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
(See attached file: C.htm)


************************************************************
Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.
************************************************************


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

Philip Sevetson

Re: Should developers take Index Design course?
(in response to Paul A Redhead)
Paul,

I'm pretty sure you're wrong about the inflexibility of Cool:Gen's RI
handling. That's one of the properties you can set for a Cool:Gen model
if I recall correctly. There are two or three different settings for
handling RI involving various levels of application code vs.
DBMS-implemented rules, each of which leads to different code being
generated. One of those settings is as you describe it, but the other two
are not.

I'd guess (Okay, okay, just a guess) that things have gotten even better
in the DB2 world since the implementation of Triggers in V7/zOS, but I
haven't been a Cool:Gen coder for several years -- I've been a Cool:Gen
DBA more recently. CASE tools are no longer "in vogue" the way they were
in the late '80s and early '90s, but Cool:Gen was and (I think) remains
the best of that breed.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]






[login to unmask email]
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/19/2004 06:47 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: [DB2-L] Should developers take Index Design course?
Phil,
You make an excellent point, and in general I agree. I am not a
Cool:Gen expert, but it is likely there are some situations which can
cause some discomfort.

I believe, for example, that if you have an RI structure with, say 2
parents and one child table, and if you wanted to insert records into the
child, then in something like COBOL you could perhaps read each parent
table once to ensure the parent rows were present, and then insert a
number of rows into the child (or insert and check sqlcode if DB2 RI is
present, but that's something else entirely). My understanding is the
best the generated Cool:Gen code will do is perform a parent check of each
parent on every insert, and I believe there are a few other similar
scenarios. The point being that the generated code, even at it's optimum,
is likely not going to be as good
as what could be coded in a 3GL.This scenario could obviously be taken to
the extreme with a rebuttal of 'so why
not use Assembler', however this would not change the situation from the
perspective of the SQL and calls to DB2.

Of course from an overall company perspective of time, money and having an
enterprise or application model it may be that it is desirable, but I
guess if your the DBA under pressure trying to squeeze out every I/O and
MIP you can tend to lose sight of that and think of the code as a little
foolish.

My 2c.

Paul.



[login to unmask email]@IDUGDB2-L.ORG> on 20/01/2004 01:19:45 AM

To: [login to unmask email]
cc:
Subject: Re: Should developers take Index Design course?


Cool:Gen has its defenders. I'm one of them. With ***proper training***,
it's an excellent repository for DB2 DDL and (according to my erstwhile
associates at Travelers Insurance and SBC Telecommunications) a good way
to turn complex modeling requirements into executable code and keep the
model in synch with the code.

Using Cool:Gen, BMC Change Manager, and DB2, we designed a flexible
process for a significant part of a Commercial Lines insurance operation,
which successfully processed changes at high "velocity", enabling a few
DBAs to serve a large body of developers with timely and 100%-accurate
change control.

(It should be noted here that there was not a great requirement for
performance modeling associated with initial implementations. However,
the post-implementation performance team had no complaints about our
process.)

Without proper training, it's no more useful than any complex tool. You
might as well rant about DB2's poor performance when implemented without
experts available.

--Phil Sevetson


"Cianci, Nick" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/18/2004 05:34 PM
Please respond to DB2 Database Discussion list at IDUG

[snip]
<Rant mode>
A statement like please avoid the use of COOL:Gen at the start would also
be nice from my point of view.
</Rant mode>
[snip]

Cheers,
Nick F. Cianci
IBM Certified Solutions Expert
- DB2 UDB V7.1 Database Administration for OS/390
[snip]


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