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

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
Updated on 2007-10-26T00:53:22Z at 2007-10-26T00:53:22Z by Sreejith
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: REBIND with REOPT ONCE / REBIND with REOPT ALWAYS

    ‏2007-10-24T16:36:11Z  in response to Sreejith
    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
      ACCEPTED ANSWER

      Re: REBIND with REOPT ONCE / REBIND with REOPT ALWAYS

      ‏2007-10-25T02:14:01Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: REBIND with REOPT ONCE / REBIND with REOPT ALWAYS

        ‏2007-10-25T06:15:51Z  in response to Sreejith
        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
          ACCEPTED ANSWER

          Re: REBIND with REOPT ONCE / REBIND with REOPT ALWAYS

          ‏2007-10-26T00:53:22Z  in response to SystemAdmin
          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