capture access plans ...

[Fan Ruo Xin]

Is there a way I can capture the access plans for all the sql stmts
written in a java (jdbc) application?
I reconfigured the db2cli.ini ...
I also tried to rebind the db2cli.lst ...
No one worked ...

Thanks,
FRX

[P. Saint-Jacques]

Look at the Command Ref. for the
SET CURRENT EXPLAIN ...
-SET--CURRENT--EXPLAIN--SNAPSHOT--+---+--+-NO------------+---><
+-YES-----------+
+-EXPLAIN-------+
'-host-variable-'
-SET CURRENT EXPLAIN MODE--+---+--+-NO----------------+------><
+-YES---------------+
+-EXPLAIN-----------+
+-RECOMMEND INDEXES-+
+-EVALUATE INDEXES--+
'-host-variable-----'

Set that on your command line, call the appl and go.

If you want to set this at the db2cli.ini file, look at:
DB2EXPLAIN CLI/ODBC Configuration Keyword

Keyword Description:
Determines whether Explain snapshot and/or Explain table
information will be generated by the server.

db2cli.ini Keyword Syntax:
DB2EXPLAIN = 0 | 1 | 2 | 3

Default Setting:
Neither Explain snapshot nor Explain table information will be
generated by the server.

Equivalent Connection Attribute:
SQL_ATTR_DB2EXPLAIN

HTH, Pierre.

Fan Ruo Xin wrote:

Is there a way I can capture the access plans for all the sql stmts
written in a java (jdbc) application?
I reconfigured the db2cli.ini ...
I also tried to rebind the db2cli.lst ...
No one worked ...

Thanks,
FRX

--
Pierre Saint-Jacques
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

[Fan Ruo Xin]

Hi, Pierre,
Thanks for your help.
(1). I thought "SET CURRENT ... " only for the current session, this will
not pass the information to the java application (another session). How
do you think?
2). I configured the section [common] of db2cli.ini and set db2expalin
as 3.
I expected this should work. But there are no new rows append to
the explain table. Have you done that before? Would you give me an
example?

Thanks,
FRX

"P. Saint-Jacques" wrote:

Look at the Command Ref. for the
SET CURRENT EXPLAIN ...
-SET--CURRENT--EXPLAIN--SNAPSHOT--+---+--+-NO------------+---><
+-YES-----------+
+-EXPLAIN-------+
'-host-variable-'
-SET CURRENT EXPLAIN MODE--+---+--+-NO----------------+------><
+-YES---------------+
+-EXPLAIN-----------+
+-RECOMMEND INDEXES-+
+-EVALUATE INDEXES--+
'-host-variable-----'

Set that on your command line, call the appl and go.

If you want to set this at the db2cli.ini file, look at:
DB2EXPLAIN CLI/ODBC Configuration Keyword

Keyword Description:
Determines whether Explain snapshot and/or Explain table
information will be generated by the server.

db2cli.ini Keyword Syntax:
DB2EXPLAIN = 0 | 1 | 2 | 3

Default Setting:
Neither Explain snapshot nor Explain table information will be
generated by the server.

Equivalent Connection Attribute:
SQL_ATTR_DB2EXPLAIN

HTH, Pierre.

Fan Ruo Xin wrote:

Is there a way I can capture the access plans for all the sql stmts
written in a java (jdbc) application?
I reconfigured the db2cli.ini ...
I also tried to rebind the db2cli.lst ...
No one worked ...

Thanks,
FRX

--
Pierre Saint-Jacques
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

[PM \(pm3iinc-nospam\)]

maybe there are more details here?
http://www-3.ibm.com/software/data/d...l#capture_stmt

Maybe you can feed the capture file to db2expln?

To be sure i get the updated db2cli.ini, i'd
db2 terminate
after the
db2 update cli cfg ... (or other method used)
and before
java progName
(this may not match what your env. though)

PM

"Fan Ruo Xin" <fa*****@sbcglobal.net> a écrit dans le message de
news:3F***************@sbcglobal.net...

Is there a way I can capture the access plans for all the sql stmts
written in a java (jdbc) application?
I reconfigured the db2cli.ini ...
I also tried to rebind the db2cli.lst ...
No one worked ...

Thanks,
FRX