db2expln and federated

[Terry]

Hi All,

Im trying to run db2expln over some sql in order to find out why its
so slow.

I receive the following error message.

LINE MESSAGES FOR DYNEXPLN.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
1 SQL1185N FEDERATED "NO" is incorrectly used in binding the
package.
SQL0091W Precompilation or binding was ended with "1"
errors and "0" warnings.

SQL1185N is telling me that FEDERATED isnt set - "...In this case, you
must specify FEDERATED YES to bind the package.". The federated param
is set, as Im running sql at the command line using federated tables.

I set the federated value again, but wil no success.
"db2 update dbm cfg using federated yes"

Can anyone suggest how to resolve this, or a better way to analyse
slow running SQL.

Thanks

Terry

[Gert van der Kooij]

In article <e8**************************@posting.google.com >, Terry
(te************@yahoo.com) says...

I set the federated value again, but wil no success.
"db2 update dbm cfg using federated yes"

Did you stop/start the instance to activate the changed option?

[Larry]

Did you recycle DB2 after changing the Federated Parm to Yes?

Larry Edelstein

Terry wrote:

Hi All,

Im trying to run db2expln over some sql in order to find out why its
so slow.

I receive the following error message.

LINE MESSAGES FOR DYNEXPLN.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
1 SQL1185N FEDERATED "NO" is incorrectly used in binding the
package.
SQL0091W Precompilation or binding was ended with "1"
errors and "0" warnings.

SQL1185N is telling me that FEDERATED isnt set - "...In this case, you
must specify FEDERATED YES to bind the package.". The federated param
is set, as Im running sql at the command line using federated tables.

I set the federated value again, but wil no success.
"db2 update dbm cfg using federated yes"

Can anyone suggest how to resolve this, or a better way to analyse
slow running SQL.

Thanks

Terry

[Serge Rielau]

Smells liek a bind option to me, not the DBM CFG.
You may need to rebind the db2expln bind file with FEDERTED YES.

But you can also do things simpler:
cd sqllib/misc
db2 -tvf EXPLAIN.DDL
db2 EXPLAIN PLAN FOR <my query>.
db2exfmt -d dbname
(default everything else but the out-file)
You will never want to go back to db2expln :-)

Cheers
Serge