Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / February 2006

Tip: Looking for answers? Try searching our database.

Explain history

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Yuri - 20 Feb 2006 16:56 GMT
Does anybody have a tool to store/retrieve explained sql? We're running
DB2 8.2 on AIX. We would like to be able to compare old saved explains
to the current ones and see if anything changed.
Thanks,
  Yuri
Pierre Saint-Jacques - 20 Feb 2006 21:54 GMT
The ncurrent expalin facility does that for you in the explain tables.
Look at the db2exfmt command and you can specify how to retrieve what you
need.
The first of the explain table EXPLAIN_INSTANCE has a primary key based on
timestamp so that the same package, section names can be kept by date.
Follows the PK on that table.
D:\SQLLIB\BIN>db2 describe table explain_instance

Column                             Type          Type
name                               schema        name
Length   Scale     Nulls
------------------------------         ---------         ------------------  
         --------     -----        ------
EXPLAIN_REQUESTER     SYSIBM    VARCHAR                 128     0
No
EXPLAIN_TIME                   SYSIBM    TIMESTAMP                10     0
No
SOURCE_NAME                    SYSIBM    VARCHAR                 128     0
No
SOURCE_SCHEMA                  SYSIBM    VARCHAR                 128     0
No
SOURCE_VERSION                 SYSIBM    VARCHAR                  64     0
No

HTH,  Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

> Does anybody have a tool to store/retrieve explained sql? We're running
> DB2 8.2 on AIX. We would like to be able to compare old saved explains to
> the current ones and see if anything changed.
> Thanks,
>   Yuri
Eugene F - 21 Feb 2006 15:47 GMT
If you will, the same is available from the DB2 Control Center GUI as
Show Explained Statements History for a selected database (by right
click :-), but you have to create the explain tables in your schema
prior to using that tool anyways.

-Eugene
Yuri - 21 Feb 2006 21:18 GMT
> If you will, the same is available from the DB2 Control Center GUI as
> Show Explained Statements History for a selected database (by right
> click :-), but you have to create the explain tables in your schema
> prior to using that tool anyways.
>
> -Eugene

I understand that I can run db2exfmt. I wanted to find out if anybody
developed some system that would allow to look at old explain using some
kind of search mechanism. If access path for particular sql changed and
I need to compare the current access path with the old one I need to be
able to find it (for particular sql). Don't see how I can do it with
just explain_tables.
Yuri
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.