Topic
  • 4 replies
  • Latest Post - ‏2007-10-26T00:53:22Z by Sreejith
Sreejith
Sreejith
121 Posts

Pinned topic REBIND with REOPT ONCE / REBIND with REOPT ALWAYS

‏2007-10-24T08:57:51Z |
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
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: REBIND with REOPT ONCE / REBIND with REOPT ALWAYS

    ‏2007-10-24T16:36:11Z  
    sree wrote:

    > 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.

    > 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?

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

    Knut Stolze
    DB2 z/OS Utilities Development
    IBM Germany
  • Sreejith
    Sreejith
    121 Posts

    Re: REBIND with REOPT ONCE / REBIND with REOPT ALWAYS

    ‏2007-10-25T02:14:01Z  
    sree wrote:

    > 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.

    > 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?

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

    Knut Stolze
    DB2 z/OS Utilities Development
    IBM Germany
    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
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: REBIND with REOPT ONCE / REBIND with REOPT ALWAYS

    ‏2007-10-25T06:15:51Z  
    • Sreejith
    • ‏2007-10-25T02:14:01Z
    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
    sree wrote:

    > 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.

    > 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.

    Knut Stolze
    DB2 z/OS Utilities Development
    IBM Germany
  • Sreejith
    Sreejith
    121 Posts

    Re: REBIND with REOPT ONCE / REBIND with REOPT ALWAYS

    ‏2007-10-26T00:53:22Z  
    sree wrote:

    > 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.

    > 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.

    Knut Stolze
    DB2 z/OS Utilities Development
    IBM Germany
    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