Getting io statistics for query

nigelrivett · #1 (**permalink**) 03-08-10, 07:05

I need to get the IO involved in a query - mainly because I think the data in a very large table isn't compressing very well for recent data (any suggestions about how to view that would be welcome too).

The explain plan gui on the client doesn't work - I am told because of the amount of data - bigints and thousands of millions of rows. Would prefer a text output anyway but anything would be good.

What I would really like is a statement I can run to get the statistics from a query in a resultset.

I've tried
SET CURRENT EXPLAIN MODE EXPLAIN
but this gives an error - I'm guessing due to missing or inaccessible explain plan tables - but it doesn't look that easy to get the output anyway.
error is DB2 SQL Warning: SQLCODE=217, SQLSTATE=01604, SQLERRMC=null, DRIVER=3.57.86

nvk@vhv · #2 (**permalink**) 03-08-10, 07:22

Hi

your session is already in Explain mode.

Code:

> db2 ? SQL0217N


SQL0217W  The statement was not executed as only Explain information
      requests are being processed.

Explanation:

The current value of one of the Explain special registers has been set
to EXPLAIN. This value allows dynamic SQL statements to be prepared and
explained but prevents any dynamic statement from being executed.

User response:

Change the value of the appropriate Explain special register to a
setting other than EXPLAIN by issuing the appropriate SET statement from
the interface or application that is encountering this condition.

 sqlcode: +217

 sqlstate: 01604

Which Version of DB2 are you using? Which OS?
What is your request? Do you want to create an explain plan from the db2 explain format tool (db2exfmt; text based)?

Cheers
nvk

nigelrivett · #3 (**permalink**) 03-08-10, 07:47

I would like to get the disk io from a query.
Would prefer it in text but gui would do if that's easier.

OS = Linux
DB2 version 9.5

I thought the query would show the io from the execution not estimated from the plan.
Is it possible to get the actual io?

I get an error from the explain plan gui in data studio due to a bug which has been raised with IBM.

nigelrivett · #4 (**permalink**) 03-08-10, 07:51

It looks like
SET CURRENT EXPLAIN MODE YES
will execute the query - does that mean the io will be from the actual execution?

nigelrivett · #5 (**permalink**) 03-08-10, 08:00

After running
SET CURRENT EXPLAIN MODE YES
query
SET CURRENT EXPLAIN MODE NO

how do I access the explain plan results?
Is the only way via something like
db2exfmt -d decipher -# 0 -w -1 -g TIC -n % -s % -o test_expl.txt
Which I guess has to be run on my client machine to produce a file.

I don't have db2exfmt installed on my m/c anyway.

nvk@vhv · #6 (**permalink**) 03-08-10, 09:26

Yep,

you have to run the db2exfmt-tool. I usally run it from the same session where i issued the SQL-Statement. Also i just use "db2exfmt -d <DBNAME>". If you skip the Questions except for the output file, then the Access Plan for last inserted Query is generated.

The Values for the Access Plan is based on the statistics for the used tables. They aren't very acurate and don't show the real number of I/Os . The same goes for the number of timerons. A smaller number of timerons does not may that the query is faster.

nigelrivett · #7 (**permalink**) 03-08-10, 09:38

I'm running the queries from a script window in data studio. Don't think db2exfmt will work from there.

Is there any way of getting the actual IO?

ARWinner · #8 (**permalink**) 03-08-10, 10:06

Use the snapshot view sysibmadm.snapdyn_sql Find your query in the STMT_TEXT column. Then look at columns POOL_DATA_L_READS (pages already in the bufferpool) and POOL_DATA_P_READS (pages read from the table on disk).

Andy

nigelrivett · #9 (**permalink**) 03-08-10, 10:25

Quote:

Originally Posted by ARWinner

Use the snapshot view sysibmadm.snapdyn_sql Find your query in the STMT_TEXT column. Then look at columns POOL_DATA_L_READS (pages already in the bufferpool) and POOL_DATA_P_READS (pages read from the table on disk).

Andy

That sounds like exactly what I want.
Unfortunately

Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID").. SQLCODE=-30082, SQLSTATE=08001, DRIVER=3.57.86

I'll see if I can get access.

nvk@vhv · #10 (**permalink**) 03-08-10, 11:29

O.O How did you manage to run your queries?

24 (USERNAME AND/OR PASSWORD INVALID)

The username specified, password specified, or both, are
invalid. Some specific causes are:
1. If you have recently changed permissions on DB2 critical
files such as db2ckpw or moved to a new Fixpak, the db2iupdt
command which updates the instance might not have been run.
2. The username being used might be in an invalid format. For
example, on UNIX and Linux platforms, usernames must be all
be lowercase.
3. An error might have been made in specifying the catalog
information. For example, the correct authentication type
might not have been specified or, if applicable, the remote
server might not have been cataloged on the local system.
For more information on authentication, search the DB2
Information Center
(DB2 Database for Linux, UNIX, and Windows) using
terms such as "authentication".

How could you log in before?

The snap_dyn is also not acurate since it's a snapshot at a given point in time. If you want precise data, you have to set up a event monitor for statements.