By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,256 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

optimize this?

P: n/a
IBM has a product for the VSE operating system called the VSAM Redirector.
It allows you to use VSAM to access RDBMS tables/views as if they were
actual VSAM files. We're doing a comparison right now between Oracle, which
we've been running in production for many years, and DB2, which we are just
starting with. One of the tests I am trying is to see how efficient the
VSAM Redirector works with DB2 versus Oracle.

Below are two types of statements. keygetstmt is used to retrieve a single
row using a unique key. fwdkeyseekstmt is used to get the next row
following the row retrieved by keygetstmt. (Now that I think about it I'm
not sure why keygetstmt is used at all, here, but that's neither here nor
there at this point.)

Anyway, DB2 seems to be significantly slower doing this. I don't know if
it's DB2 itself, how we have it set up, or that its running on different
(slower?) hardware. Below are the two statements followed by a 'dump'
showing the elapsed time. Odd numbered queries execute the keygetstmt
prepared statement. Even numbered queries execute the fwdkeyseekstmt
prepared statement.

The VSAM Redirector server is all written in Java, by the way, using JDBC.
For Oracle we're using oracle.jdbc.driver.OracleDriver ("thin" driver). For
DB2 we're using com.ibm.db2.jcc.DB2Driver.

Oracle
keygetstmt =
"SELECT *
FROM VSAM_POSPAY_ISSUED_CHECKS
WHERE ACCOUNT_SERIAL_NUMBER = ?"

fwdkeyseekstmt =
"SELECT UNIQRBACNT , ACCOUNT_SERIAL_NUMBER
FROM ( SELECT UNIQRBACNT , ACCOUNT_SERIAL_NUMBER
FROM VSAM_POSPAY_ISSUED_CHECKS
WHERE ACCOUNT_SERIAL_NUMBER ?
ORDER BY ACCOUNT_SERIAL_NUMBER )
WHERE ROWNUM < 2"

06:00:38:860 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #3 ---
06:00:38:870 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #4 ---
06:00:38:880 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #5 ---
06:00:38:880 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #6 ---
06:00:38:890 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #7 ---
06:00:38:890 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #8 ---
06:00:38:900 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #9 ---
06:00:38:900 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #10 ---
06:00:38:910 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #11 ---
06:00:38:910 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #12 ---
06:00:38:920 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #13 ---
06:00:38:920 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #14 ---
06:00:38:920 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #15 ---
06:00:38:930 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #16 ---
06:00:38:940 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #17 ---
06:00:38:940 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #18 ---
06:00:38:950 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #19 ---
06:00:38:950 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #20 ---
06:00:38:960 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #21 ---
06:00:38:960 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #22 ---

DB2
keygetstmt =
"SELECT *
FROM VSAM_POSPAY_ISSUED_CHECKS
WHERE ACCOUNT_SERIAL_NUMBER = ?
FETCH FIRST 1 ROW ONLY
OPTIMIZE FOR 1 ROW FOR FETCH ONLY"

fwdkeyseekstmt =
"SELECT UNIQRBACNT , ACCOUNT_SERIAL_NUMBER
FROM VSAM_POSPAY_ISSUED_CHECKS
WHERE ACCOUNT_SERIAL_NUMBER ?
ORDER BY ACCOUNT_SERIAL_NUMBER
FETCH FIRST 1 ROW ONLY
FOR FETCH ONLY
OPTIMIZE FOR 1 ROW "

06:01:00:271 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #3 ---
06:01:00:281 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #4 ---
06:01:00:451 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #5 ---
06:01:00:461 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #6 ---
06:01:00:632 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #7 ---
06:01:00:642 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #8 ---
06:01:00:822 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #9 ---
06:01:00:822 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #10 ---
06:01:00:992 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #11 ---
06:01:01:020 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #12 ---
06:01:01:172 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #13 ---
06:01:01:192 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #14 ---
06:01:01:363 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #15 ---
06:01:01:373 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #16 ---
06:01:01:543 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #17 ---
06:01:01:553 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #18 ---
06:01:01:723 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #19 ---
06:01:01:743 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #20 ---
06:01:01:913 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #21 ---
06:01:01:923 [VSAM_POSPAY_ISSUED_CHECKS,1] --- Database query #22 ---

Originally the DB2 statements did not include the "fetch first 1 row only"
clause. By adding that it sped it up quite a bit, actually, but still not
near Oracle's speed.

Any other things I might be able to use to speed things up? Because we are
emulating VSAM I don't think that doing a single select statement for all
rows and then fetching them from the cursor is what we want. Then again,
perhaps it is. But I'd like to keep things as close as possible to how they
are now, since it's not really our code anyway.

In the end we only really do random access (select for one an only one row
using the unique key), but I thought that this would be a somewhat
worthwhile test between the two. Perhaps not. If I simply select all rows
and be done with it then the speed is pretty comparable.

Thanks!
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Aug 5 '06 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Frank, is this Db2 for zOS?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 5 '06 #2

P: n/a
No, DB2 LUW v8.2.

Frank
>>Serge Rielau<sr*****@ca.ibm.com08/04/06 7:39 PM >>>
Frank, is this Db2 for zOS?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 7 '06 #3

P: n/a
Care to post the db2exfmt output?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 7 '06 #4

P: n/a
This appears to be a command that must be run from the server machine...?
If I only have client access is there any way I can run this command (and
other "server" commands) without actually logging in to the server machine?
Actually, maybe it's good if the answer is no, so I have more reason to
pressure for getting access to the machine. But anyway...

Thanks,
Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
>>Serge Rielau<sr*****@ca.ibm.com08/07/06 10:56 AM >>>
Care to post the db2exfmt output?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 7 '06 #5

P: n/a
Frank Swarbrick wrote:
This appears to be a command that must be run from the server machine...?
If I only have client access is there any way I can run this command (and
other "server" commands) without actually logging in to the server machine?
Actually, maybe it's good if the answer is no, so I have more reason to
pressure for getting access to the machine. But anyway...
Well.. no data, no help.
db2exfmt is a tool which can run on the client, but needs a connection.
Also you need to create some meta-tables by running
sqllib/misc/EXPLAIN.DDL on the server.
Anyway the idea is to get the access plan. For all I care you may just
be missing an index....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 8 '06 #6

P: n/a
Serge Rielau<sr*****@ca.ibm.com08/07/06 8:03 PM >>>
>Frank Swarbrick wrote:
>This appears to be a command that must be run from the server machine...?
>If I only have client access is there any way I can run this command
(and
>other "server" commands) without actually logging in to the server
machine?
>Actually, maybe it's good if the answer is no, so I have more reason to
pressure for getting access to the machine. But anyway...
Well.. no data, no help.
db2exfmt is a tool which can run on the client, but needs a connection.
Also you need to create some meta-tables by running
sqllib/misc/EXPLAIN.DDL on the server.
Anyway the idea is to get the access plan. For all I care you may just
be missing an index....
Haha, that was it. Missing an index. Well, I had an index, but it wasn't
quite the correct index. Now that I've added one it works like a charm. In
fact, it's now faster than the Oracle version! Which brings up another
slight difference...

Here is a describe of the table

Oracle:
SQLdescribe icm_pospay_issued_checks;
Name Null? Type
----------------------------------------- -------- ----------------
UNIQRBACNT NOT NULL NUMBER
GROUP_ID NOT NULL NUMBER
COMPANY_ID NOT NULL NUMBER
ACCOUNT_NUMBER NOT NULL VARCHAR2(16)
SERIAL_NUMBER NOT NULL NUMBER
AMOUNT NOT NULL NUMBER(11,2)
PAYEE_NAME VARCHAR2(150)
ISSUED_DATETIME NOT NULL DATE
CREATION_DATETIME NOT NULL DATE
LAST_MTCE_USER_PROFILE_ID NOT NULL NUMBER
LAST_MTCE_DATETIME NOT NULL DATE
LOCKED_USER_PROFILE_ID NUMBER
LOCKED_DATETIME DATE
LOCKED_EXPIRY_DATETIME DATE
ACCOUNT_TYPE NOT NULL CHAR(2)

DB2:
db2 =describe table icm_pospay_issued_checks

Column Type Type
name schema name Length Scale
Nulls
------------------------------ --------- ------------------ -------- -----
-----
UNIQRBACNT SYSIBM DECIMAL 31 0
No
GROUP_ID SYSIBM DECIMAL 31 0
No
COMPANY_ID SYSIBM DECIMAL 31 0
No
ACCOUNT_NUMBER SYSIBM VARCHAR 16 0
No
SERIAL_NUMBER SYSIBM DECIMAL 31 0
No
AMOUNT SYSIBM DECIMAL 11 2
No
PAYEE_NAME SYSIBM VARCHAR 150 0
Yes
ISSUED_DATETIME SYSIBM TIMESTAMP 10 0
No
CREATION_DATETIME SYSIBM TIMESTAMP 10 0
No
LAST_MTCE_USER_PROFILE_ID SYSIBM DECIMAL 31 0
No
LAST_MTCE_DATETIME SYSIBM TIMESTAMP 10 0
No
LOCKED_USER_PROFILE_ID SYSIBM DECIMAL 31 0
Yes
LOCKED_DATETIME SYSIBM TIMESTAMP 10 0
Yes
LOCKED_EXPIRY_DATETIME SYSIBM TIMESTAMP 10 0
Yes
ACCOUNT_TYPE SYSIBM CHARACTER 2 0
No
ACCOUNT_SERIAL_NUMBER SYSIBM VARCHAR 19 0
No

The main difference between the two tables is that in DB2 there is an
additional column, ACCOUNT_SERIAL_NUMBER. This is a "generated" column
defined as follows:
"ACCOUNT_SERIAL_NUMBER" VARCHAR(19) NOT NULL GENERATED ALWAYS AS
(CAST((SUBSTR(ACCOUNT_NUMBER, 1, 10) CONCAT SUBSTR(DIGITS(SERIAL_NUMBER),
23, 9)) AS SYSIBM.VARCHAR(19))) )

My index (the relevant one) is defined as:
ADD CONSTRAINT "ICM_ACCT_SERIAL" UNIQUE (("ACCOUNT_SERIAL_NUMBER");

On the Oracle side they are doing it a bit differently. There is no
"generated" column. Instead they do what they call a "function-based index"
where instead of indexing on a column you index on a function, such as
CAST((SUBSTR(ACCOUNT_NUMBER, 1, 10) CONCAT SUBSTR(DIGITS(SERIAL_NUMBER), 23,
9)) AS VARCHAR(19))
(though with slightly different wording)

Does DB2 have something similar to this "function-based indexing"? Using
the control center I couldn't see any option to do something like that.

The only reason I want to is to make my comparison as close to 'apples to
apples' as possible.

My original problem was I had my constraint defined as follows:
CONSTRAINT ICM_ACCT_SERIAL UNIQUE (ACCOUNT_NUMBER , SERIAL_NUMBER)
But it wasn't being used because the SELECT was on
CAST((SUBSTR(ACCOUNT_NUMBER, 1, 10) CONCAT SUBSTR(DIGITS(SERIAL_NUMBER), 23,
9)) AS VARCHAR(19)) (via a view).
Anyway...

One other question... In the above DB2 table, is ACCOUNT_SERIAL_NUMBER a
logical column only, or does it actually physically exist in the database?

Hope I'm making some sense here.
Thanks!
Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Aug 8 '06 #7

P: n/a
Serge Rielau<sr*****@ca.ibm.com08/07/06 8:03 PM >>>
>Frank Swarbrick wrote:
>This appears to be a command that must be run from the server machine...?
>If I only have client access is there any way I can run this command
(and
>other "server" commands) without actually logging in to the server
machine?
>Actually, maybe it's good if the answer is no, so I have more reason to
pressure for getting access to the machine. But anyway...
Well.. no data, no help.
db2exfmt is a tool which can run on the client, but needs a connection.
Also you need to create some meta-tables by running
sqllib/misc/EXPLAIN.DDL on the server.
Anyway the idea is to get the access plan. For all I care you may just
be missing an index....
Oh, one other thing. I cannot locate db2exfmt anywhere in the SQLLIB on my
PC. The docs say that it should be in the MISC folder, but all I have in
there is psapi.dll. Is this perhaps just a bad install?

Thanks,
Frank
DB2 beginner
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Aug 8 '06 #8

P: n/a
Frank Swarbrick wrote:
Haha, that was it. Missing an index. Well, I had an index, but it wasn't
quite the correct index. Now that I've added one it works like a charm. In
fact, it's now faster than the Oracle version!
That's working as designed then :-)
Which brings up another slight difference...

Here is a describe of the table

Oracle:
SQLdescribe icm_pospay_issued_checks;
Name Null? Type
----------------------------------------- -------- ----------------
UNIQRBACNT NOT NULL NUMBER
....
UNIQRBACNT SYSIBM DECIMAL 31 0
Each of these DECIMAL(31, 0) takes 16 Bytes. I don't believe all these
columns
use 31 digits right? You can greatly reduce the footprint of the row by
At least reducing teh scale to whats really required. Perhaps even go to
a native type (like INTEGER). That will increase your bufferpool hit
ratio and make DB2 faster. (Then again you can go to DB2 9 and buy
compression if you are lazy but rich or otherwise charitably inclined to
help pay my mortgage ;-)
The main difference between the two tables is that in DB2 there is an
additional column, ACCOUNT_SERIAL_NUMBER. This is a "generated" column
defined as follows:
"ACCOUNT_SERIAL_NUMBER" VARCHAR(19) NOT NULL GENERATED ALWAYS AS
(CAST((SUBSTR(ACCOUNT_NUMBER, 1, 10) CONCAT SUBSTR(DIGITS(SERIAL_NUMBER),
23, 9)) AS SYSIBM.VARCHAR(19))) )

My index (the relevant one) is defined as:
ADD CONSTRAINT "ICM_ACCT_SERIAL" UNIQUE (("ACCOUNT_SERIAL_NUMBER");

On the Oracle side they are doing it a bit differently. There is no
"generated" column. Instead they do what they call a "function-based index"
where instead of indexing on a column you index on a function, such as
CAST((SUBSTR(ACCOUNT_NUMBER, 1, 10) CONCAT SUBSTR(DIGITS(SERIAL_NUMBER), 23,
9)) AS VARCHAR(19))
(though with slightly different wording)

Does DB2 have something similar to this "function-based indexing"? Using
the control center I couldn't see any option to do something like that.
"expression generated" columns are the closest match to a function based
index. So you are doing apples to apples.
>
The only reason I want to is to make my comparison as close to 'apples to
apples' as possible.

My original problem was I had my constraint defined as follows:
CONSTRAINT ICM_ACCT_SERIAL UNIQUE (ACCOUNT_NUMBER , SERIAL_NUMBER)
But it wasn't being used because the SELECT was on
CAST((SUBSTR(ACCOUNT_NUMBER, 1, 10) CONCAT SUBSTR(DIGITS(SERIAL_NUMBER), 23,
9)) AS VARCHAR(19)) (via a view).
Right.
One other question... In the above DB2 table, is ACCOUNT_SERIAL_NUMBER a
logical column only, or does it actually physically exist in the database?
It is a regular column.
In Oracle you would achieve the same effect by combining two before
triggers (insert and update) and a check constraint.
The advantage of the generated column over the function index is that
you have the perhaps expensive expression precomputed even when the
optimizer goes for a table scan (perhaps to do a hash join).
The downside is that it takes space in the row.
The imaginary logical column would be:
GENERATED BY REFERENCE (<expression>) which is not (yet) implemented. :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 8 '06 #9

P: n/a
Serge Rielau<sr*****@ca.ibm.com08/08/06 3:45 PM >>>
>Frank Swarbrick wrote:
>Haha, that was it. Missing an index. Well, I had an index, but it
wasn't
>quite the correct index. Now that I've added one it works like a charm.
In
>fact, it's now faster than the Oracle version!
>That's working as designed then :-)
Well obviously! :-)
Which brings up another slight difference...

Here is a describe of the table

Oracle:
SQLdescribe icm_pospay_issued_checks;
Name Null? Type
----------------------------------------- -------- ----------------
UNIQRBACNT NOT NULL NUMBER
...
>UNIQRBACNT SYSIBM DECIMAL 31
0
>Each of these DECIMAL(31, 0) takes 16 Bytes. I don't believe all these
columns
use 31 digits right? You can greatly reduce the footprint of the row by
At least reducing teh scale to whats really required. Perhaps even go to
a native type (like INTEGER). That will increase your bufferpool hit
ratio and make DB2 faster. (Then again you can go to DB2 9 and buy
compression if you are lazy but rich or otherwise charitably inclined to
help pay my mortgage ;-)
Probably a good idea. The only reason I defined it this way is because the
Oracle version had the column defined as "NUMBER" which can be up to (38,0),
I think. And is actually, I believe, floating-point. Which is perhaps one
reason why the Oracle version was slower. If we were going to put the DB2
version in production I would trim down the size of these numeric fields.
>The main difference between the two tables is that in DB2 there is an
additional column, ACCOUNT_SERIAL_NUMBER. This is a "generated" column
defined as follows:
"ACCOUNT_SERIAL_NUMBER" VARCHAR(19) NOT NULL GENERATED ALWAYS AS
(CAST((SUBSTR(ACCOUNT_NUMBER, 1, 10) CONCAT
SUBSTR(DIGITS(SERIAL_NUMBER),
>23, 9)) AS SYSIBM.VARCHAR(19))) )

My index (the relevant one) is defined as:
ADD CONSTRAINT "ICM_ACCT_SERIAL" UNIQUE (("ACCOUNT_SERIAL_NUMBER");

On the Oracle side they are doing it a bit differently. There is no
"generated" column. Instead they do what they call a "function-based
index"
>where instead of indexing on a column you index on a function, such as
CAST((SUBSTR(ACCOUNT_NUMBER, 1, 10) CONCAT SUBSTR(DIGITS(SERIAL_NUMBER),
23,
>9)) AS VARCHAR(19))
(though with slightly different wording)

Does DB2 have something similar to this "function-based indexing"?
Using
>the control center I couldn't see any option to do something like that.
"expression generated" columns are the closest match to a function based
index. So you are doing apples to apples.
Cool. Just wanted to make sure I wasn't missing something.
>>
The only reason I want to is to make my comparison as close to 'apples
to
>apples' as possible.

My original problem was I had my constraint defined as follows:
CONSTRAINT ICM_ACCT_SERIAL UNIQUE (ACCOUNT_NUMBER , SERIAL_NUMBER)
But it wasn't being used because the SELECT was on
CAST((SUBSTR(ACCOUNT_NUMBER, 1, 10) CONCAT SUBSTR(DIGITS(SERIAL_NUMBER),
23,
>9)) AS VARCHAR(19)) (via a view).
Right.
>One other question... In the above DB2 table, is ACCOUNT_SERIAL_NUMBER
a
>logical column only, or does it actually physically exist in the
database?
>It is a regular column.
In Oracle you would achieve the same effect by combining two before
triggers (insert and update) and a check constraint.
The advantage of the generated column over the function index is that
you have the perhaps expensive expression precomputed even when the
optimizer goes for a table scan (perhaps to do a hash join).
The downside is that it takes space in the row.
I'm guessing that's one reason why DB2 is performing better here. Speed at
the expense of space.
>The imaginary logical column would be:
GENERATED BY REFERENCE (<expression>) which is not (yet) implemented. :-)
Hmm!

Thanks for all of the info! I am learning more every day. Hopefully can
retain it all. :-)

Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Aug 8 '06 #10

P: n/a
Frank Swarbrick wrote:
Oh, one other thing. I cannot locate db2exfmt anywhere in the SQLLIB on my
PC. The docs say that it should be in the MISC folder, but all I have in
there is psapi.dll. Is this perhaps just a bad install?
sqllib/bin/db2exfmt
and
sqllib/misc/EXPLAIN.DDL

If you want to "explain" a query you instrument first (you do this once
in the lifetime of the database) the database by running the DDL script.
e.g. db2 -tvf EXPLAIN.DDL
THen you can e.g. from the command line explain a query:
EXPLAIN PLAN FOR SELECT 1 FROM SYSIBM.SYSDUMMY1
This command will dump all sorts of optimizer information into the
tables created in EXPLAIN.DDL
db2exfmt -d mydb -o myplan.exfmt -1
will now pretty print the meta information into a fancy graph and tell
you everything there is to know about the chosen plan.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 9 '06 #11

P: n/a
Serge Rielau<sr*****@ca.ibm.com08/09/06 6:11 AM >>>
>Frank Swarbrick wrote:
>Oh, one other thing. I cannot locate db2exfmt anywhere in the SQLLIB on
my
>PC. The docs say that it should be in the MISC folder, but all I have
in
>there is psapi.dll. Is this perhaps just a bad install?
>sqllib/bin/db2exfmt
and
sqllib/misc/EXPLAIN.DDL
Nope. Neither of those exist for me in those directories...
C:\Program Files\IBM\SQLLIB\BIN>dir db2e*.*
Volume in drive C is programming-14-ntfs
Volume Serial Number is 4C05-FC30

Directory of C:\Program Files\IBM\SQLLIB\BIN

01/22/2006 11:44 AM 65 db2env.bat
01/22/2006 11:44 AM 628 db2eva.bat
01/22/2006 11:44 AM 3,638 db2eva.ico
01/22/2006 02:48 PM 46,632 db2expln.exe
4 File(s) 50,963 bytes

C:\Program Files\IBM\SQLLIB\MISC>dir
Volume in drive C is programming-14-ntfs
Volume Serial Number is 4C05-FC30

Directory of C:\Program Files\IBM\SQLLIB\MISC

06/08/2006 11:16 AM <DIR .
06/08/2006 11:16 AM <DIR ..
01/22/2006 12:03 PM 45,136 psapi.dll
1 File(s) 45,136 bytes
Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Aug 9 '06 #12

P: n/a
Ian
Frank Swarbrick wrote:
Serge Rielau<sr*****@ca.ibm.com08/09/06 6:11 AM >>>
>Frank Swarbrick wrote:
>>Oh, one other thing. I cannot locate db2exfmt anywhere in the SQLLIB on
my
>>PC. The docs say that it should be in the MISC folder, but all I have
in
>>there is psapi.dll. Is this perhaps just a bad install?
>sqllib/bin/db2exfmt
and
sqllib/misc/EXPLAIN.DDL

Nope. Neither of those exist for me in those directories...
db2exfmt is not included with the runtime client or admin client.
Not sure about the app dev client.

So you should probably be running db2exfmt on the server.

Aug 10 '06 #13

P: n/a
Ian<ia*****@mobileaudio.com08/10/06 6:10 AM >>>
>Frank Swarbrick wrote:
>Serge Rielau<sr*****@ca.ibm.com08/09/06 6:11 AM >>>
>>Frank Swarbrick wrote:
Oh, one other thing. I cannot locate db2exfmt anywhere in the SQLLIB
on
>my
>>>PC. The docs say that it should be in the MISC folder, but all I have
in
>>>there is psapi.dll. Is this perhaps just a bad install?
>>sqllib/bin/db2exfmt
and
sqllib/misc/EXPLAIN.DDL

Nope. Neither of those exist for me in those directories...

db2exfmt is not included with the runtime client or admin client.
Not sure about the app dev client.

So you should probably be running db2exfmt on the server.
Can you tell from the below which one I have installed?
================================================== ==========
About DB2 Administration Tools Environment
================================================== ==========
DB2 administration tools level:
Product identifier SQL08024
Level identifier 03050106
Level DB2 v8.1.11.973
Build level s060120
PTF WR21365
================================================== ==========
Java development kit (JDK):
Level IBM Corporation 1.4.1
================================================== ==========

I'm really a developer, so I probably should have the app dev client, but
then again I'm sort of acting as administrator, at least for development
databases, so I need that as well.

Actually, I just found this on the web:
"These are the types of DB2 clients you can install:

DB2 Run-Time Client provides the ability to access DB2 databases and a basic
application execution environment.

DB2 Administration Client provides all the features of the DB2 Run-Time
Client and includes all the DB2 Administration GUI tools, documentation, and
support for Thin Clients.

DB2 Application Development Client provides all the features of the DB2
Administration Client and also includes libraries, header files, documented
APIs, and sample programs to build character-based, multimedia, and
object-oriented applications."

Considering I do have the libraries, header files and APIs I'm going to
assume I must have the dev client. In which case I'd have to say that
db2exfmt is not included with the development client either.

Is there anything that shows which admin applications are not included with
the clients?

Thanks,
Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Aug 10 '06 #14

This discussion thread is closed

Replies have been disabled for this discussion.