REBIND with REOPT ONCE / REBIND with REOPT ALWAYS

[sree]

Hi,

In our DB2 database, we have many stored procedures and functions.
After building these stored procedures and functions to the database,
we do a REBIND using the command

'REBIND PACKAGE '||pkgname||' RESOLVE ANY REOPT ONCE'

and the value for 'pkgname' is picked from syscat.packages using

select pkgname from syscat.packages where pkgschema = '<app_schema_name>' order by pkgschema,pkgname

Here are my questions :-

1. which option is better for good performance, REBIND with REOPT ONCE /
REBIND with REOPT ALWAYS

2. It generates packages with some random names like 'P2014120'
how do we identify which stored procedure/function is in which package?

3. In running rebind of all packages , I get an error

"SQL0901N The SQL statement failed because of a non-severe system error.
Subsequent SQL statements can be processed. (Reason "Sdir len bad:
1171!=1160+9".) SQLSTATE=58004"
SQLSTATE 58004: A system error (that does not necessarily preclude the
successful execution of subsequent SQL statements) occurred."

How do we identify which stored procedure,function is creating this error?


Thanks
Sree

[Knut Stolze]

Post by sree
1. which option is better for good performance, REBIND with REOPT ONCE /
REBIND with REOPT ALWAYS

It depends.

If you don't have any significant data changes and the statistics don't
change either, then a REOPT ALWAYS is overkill because the access plans
would be recompiled every time with no benefit. REOPT ONCE would be better
there. Likewise, if you have changes, then REOPT ALWAYS may be better -
maybe not. It depends on your workload.

Post by sree
3. In running rebind of all packages , I get an error
"SQL0901N The SQL statement failed because of a non-severe system error.
1171!=1160+9".) SQLSTATE=58004"
SQLSTATE 58004: A system error (that does not necessarily preclude the
successful execution of subsequent SQL statements) occurred."
How do we identify which stored procedure,function is creating this error?

SQL0901 means: call IBM. There is nothing you can do about this (only work
around it, possibly).

[sree]

Hi

Thanks for the reply
We have huge data changes happening, but I cant put REOPT ALWAYS since it will try to recompile every time. so we opted for REOPT ONCE.

Can you please answer my qn 2

"2. It generates packages with some random names like 'P2014120'
how do we identify which stored procedure/function is in which package?"

Thanks
Sree

[Knut Stolze]

Post by sree
Hi
Thanks for the reply
We have huge data changes happening, but I cant put REOPT ALWAYS since it
will try to recompile every time. so we opted for REOPT ONCE.

REOPT AUTO may be another option if your DB2 version has that already
available.

Post by sree
Can you please answer my qn 2
"2. It generates packages with some random names like 'P2014120'
how do we identify which stored procedure/function is in which package?"

No, I can't because you didn't tell us which version of DB2 you are using on
which platform.

[sree]

Sorry, I forgot to mention the version and platform

its DB2/NT64 8.2.5 running on windows 2003 server enterprise edition

I think 'REOPT AUTO' is not supported in this version. I got an error ->

db2 REBIND PACKAGE P2014120 RESOLVE ANY REOPT AUTO

SQL0104N An unexpected token "AUTO" was found following "REOPT". Expected
tokens may include: "VARS". SQLSTATE=42601