470,826 Members | 2,018 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,826 developers. It's quick & easy.

Explain history

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
Feb 20 '06 #1
3 1608
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.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Yuri" <yu**@prodigy.net> a écrit dans le message de news:
Uw***************@newssvr33.news.prodigy.com...
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


Feb 20 '06 #2
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

Feb 21 '06 #3
Eugene F wrote:
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
Feb 21 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by pmelanso | last post: by
14 posts views Thread by Ina Schmitz | last post: by
20 posts views Thread by Dan | last post: by
4 posts views Thread by Bruno Alexandre | last post: by
3 posts views Thread by pentisia | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.