Newbie question - DB2 EXPLAIN via telnet?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mvl_groups_user@yahoo.com - 23 Jun 2006 14:37 GMT

I'm trying to pull data from our corporate DB2 data warehouse via ODBC,
and am trying to optimize a long-running query.

Unfortunately, our company did not install the suite of DB2 tools on my
desktop that would allow me to do anything beyond on ODBC connection.

In a prior role, I had been able to telnet into a Sybase server, turn
showplan on, turn noexecute on, and type in my query. This would help
me analyze and tune queries, without any client side software.

I found I can telnet into my DB2 server, but don't know the syntax to
do an EXPLAIN. All posts and web sites I could find refer to a Visual
Explain, which I assume requires client software that I don't have.

Is there any simple telnet commands that could be used to run an
EXPLAIN? If so, what is the specific syntax?

-MVL

Reply to this Message

Shashi Mannepalli - 23 Jun 2006 14:43 GMT

>From telnet

db2 connect to <your database>
cd $insthome/sqllib/misc
db2 -tvf EXPLAIN.DDL

db2 connect to db;
db2 "explain plan for select * xxxxxx"
db2 terminate;

Note: If you have a big SQL and it is in a file...just put EXPLAIN PLAN
FOR select....
and run the sql ......like db2 -tvf <sqlfile>

db2exfmt -d database -g TIC -e % -n % -s % -w -1 -# 0 -o query.output

cheers...
Shashi Mannepalli

Look for db2exfmt ---Help for further instructions.

mvl_groups_u...@yahoo.com wrote:

> I'm trying to pull data from our corporate DB2 data warehouse via ODBC,
> and am trying to optimize a long-running query.
[quoted text clipped - 14 lines]
>
> -MVL

Reply to this Message

mvl_groups_user@yahoo.com - 23 Jun 2006 16:45 GMT

Thanks Shashi.

One more question, in Sybase I had to do a noexecute statement to
prevent the query from running as it was explained. Will this run the
query at the same time? If so, is there a way to prevent it from
running? It currently takes over 50 hours to run.

-MVL

Reply to this Message

Shashi Mannepalli - 23 Jun 2006 16:58 GMT

In DB2 also the same thing. When u are running EXPLAIN it doesn't
actually
run the SQL. No SQL results will be given.

BTW: You can use dynexpln, db2exfmt, db2expln ...etc to get the EXPLAIN
plan
from a telnet session.

check this

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp

cheers...
Shashi Mannepalli

> Thanks Shashi.
>
[quoted text clipped - 4 lines]
>
> -MVL

Reply to this Message

Pierre Saint-Jacques - 23 Jun 2006 19:42 GMT

I note in your original post that "your company" did not install the tools
on your desk.
If what you describe to be your need is a regular thing, I'd ask them to
look in the CD's that were shipped to them for the DB2 install "license"
that they have.
That CD has all the tools required to connect to several different servers
of DB2 UDB for LUW as well as the ability to use both grphic and command
line support for your remote servers and/or db's.
Yous hould ask them to install it on your platform.
HTH, Pierre.

Signature

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

> Thanks Shashi.
>
[quoted text clipped - 4 lines]
>
> -MVL

Reply to this Message