IBM Support

JR34344: SYSPROC.NNSTAT CAUSES FEDERATED PACKAGES INVALID

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • SYSPROC.NNSTAT is used to update nickname statistics, and it
    will also alter the nickname table option to mark the statistics
    updating time with the following statement:
         ALTER NICKNAME test OPTIONS (SET REMOTE_STATS_TIME
    '<timestamp>').
    The "ALTER NICKNAME" statement will invalidate the federated
    packages which depends on the nickname. Invalid packages are
    implicitly (or automatically) rebound by the database manager
    when they are executed.
    
    For example, if a package named DBTEST is referencing a nickname
    belonging to server "MARKETDB", and we use SYSPROC.NNSTAT to
    update statistics of all nicknames on that server :
    db2 "CALL SYSPROC.NNSTAT('MARKETDB',NULL, NULL, NULL, NULL, 0,
    NULL, ?)"
    
    Then you will find the package is invalid:
    db2 => select substr(PKGNAME, 1, 10) as PKGNAME, substr(OWNER,
    1, 10) as owner, VALID from syscat.PACKAGES
    PKGNAME    OWNER      VALID
    ---------- ---------- -----
    DBTEST     TESTER     N
    
    Normally NNSTAT invalidating packages and implicitly rebinding
    won't lead to any serious problem.
    
    However the potential problem is that package catalog table
    SYSIBM.SYSPLANDEP may experience lock timeout when packages are
    being rebound concurrently.
    Especially this may happen when automatic statistics collection
    feature is turned on.  This is a new feature in V9.5. For
    federation, nickname statistics are automatically checked and if
    they are out-of-date, SYSPROC.NNSTAT is called to update them.
    This cause many packages dependent on nicknames become
    invalidated periodically.  When all those packages are executed
    , they will be rebound implicitly concurrently -which means
    catalog table SYSIBM.SYSPLANDEP needs to be updated. You may
    encounter lock timeout on rows of that table. The message in
    db2diag.log is as below
    
    2009-08-26-11.55.59.205000-240 E574672439H615     LEVEL: Info
    PID     : 5748                 TID  : 7076        PROC :
    db2syscs.exe
    INSTANCE: DB2                  NODE : 000         DB   : FEDDB
    APPHDL  : 0-47446              APPID: APPN.TEST.C4AFD5DC4AC6
    AUTHID  : DB2ADMIN
    EDUID   : 7076                 EDUNAME: db2agent (FEDDB) 0
    FUNCTION: DB2 UDB, lock manager, sqlplnfd, probe:80
    DATA #1 : String, 185 bytes
    Request for lock "REC: (0, 14) RID x0500390100000000" in mode
    ".NW"
    timed out
    Application caused the lock wait is "APPN.TEST.C4B1A5FB52E5"
    Package name: PRDOBJ
    Section: 4
    
    2009-08-26-11.55.59.205000-240 I574673056H527     LEVEL: Info
    PID     : 5748                 TID  : 7076        PROC :
    db2syscs.exe
    INSTANCE: DB2                  NODE : 000         DB   : FEDDB
    APPHDL  : 0-47446              APPID: APPN.TEST.C4AFD5DC4AC6
    AUTHID  : DB2ADMIN
    EDUID   : 7076                 EDUNAME: db2agent (FEDDB) 0
    FUNCTION: DB2 UDB, catalog services, sqlrlCatalogScan::insert,
    probe:60
    RETCODE : ZRC=0x80100044=-2146435004=SQLP_LTIMEOUT
              "LockTimeOut - tran rollback Reason code 68"
    
    2009-08-26-11.55.59.205000-240 I574673585H626     LEVEL: Info
    PID     : 5748                 TID  : 7076        PROC :
    db2syscs.exe
    INSTANCE: DB2                  NODE : 000         DB   : FEDDB
    APPHDL  : 0-47446              APPID: APPN.TEST.C4AFD5DC4AC6
    AUTHID  : DB2ADMIN
    EDUID   : 7076                 EDUNAME: db2agent (FEDDB) 0
    FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:670
    MESSAGE : ZRC=0x80100044=-2146435004=SQLP_LTIMEOUT
              "LockTimeOut - tran rollback Reason code 68"
    DATA #1 : String, 81 bytes
    Compiler error stack for rc = -2146435004:
    sqlnn_cmpl[445]
    sqlnn_pkg_deprec[400]
    
    2009-08-26-11.55.59.220000-240 I574674213H836     LEVEL: Info
    PID     : 5748                 TID  : 7076        PROC :
    db2syscs.exe
    INSTANCE: DB2                  NODE : 000         DB   : FEDDB
    APPHDL  : 0-47446              APPID: APPN.TEST.C4AFD5DC4AC6
    AUTHID  : DB2ADMIN
    EDUID   : 7076                 EDUNAME: db2agent (FEDDB) 0
    FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
     sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -911   sqlerrml: 2
     sqlerrmc: 68
     sqlerrp : SQLRL009
     sqlerrd : (1) 0x80100044      (2) 0x00000044      (3)
    0x00000000
               (4) 0x00000000      (5) 0x00000000      (6)
    0x00000000
     sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
               (7)      (8)      (9)      (10)        (11)
     sqlstate: 40001
    

Local fix

  • Avoid executing multiple invalid packages concurrently - as
    stated previously, it means implicitly rebinding concurrently
    ,which may introduce lock timeout.
    For the lock timeout issue caused by automatic nickname
    statistic update feature, you need do:
    - Explicitly rebind the invalid packages one by one.
    - Turn off the automatic nickname statistic update feature to
    avoid automatically packages invalidation.
    
    There are a couple of ways to turn off auto statistics:
    (1)Create a policy that specifies only local tables are to have
    their statistics updated (not nicknames).  This can be done
    using either an IBM-provided stored procedures .  For
    information on the stored procedure, see
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.i
    bm.db2.luw.admin.ha.doc/doc/t0051354.html.
    (2) If you do not mind turning off the automatic updating of
    nickname and local table statistics, turn off db cfg
    parameter auto_runstats.  (See
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?t
    opic=/com.ibm.db2.luw.admin.config.doc/doc/r0011479.html)
    The command is:
       UPDATE DB CFG FOR dbname USING auto_runstats OFF
    However, using this option  will also turn off the automatic
    updating of local table statistics.
    

Problem summary

  • Users affected:
       Users of the DB2 for LUW Homogeneous Federation Feature or
    InfoSphere Federation Server
    Problem description and summary:
       See error description.
    

Problem conclusion

  • Problem was first fixed in Version 9.5 FixPak 6 (s100805 ). This
    fix should be applied on Federation Server.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR34344

  • Reported component name

    FEDERATION SERV

  • Reported component ID

    5724N9700

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-09-22

  • Closed date

    2010-09-19

  • Last modified date

    2010-09-19

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    JR36518

Fix information

  • Fixed component name

    FEDERATION SERV

  • Fixed component ID

    5724N9700

Applicable component levels

  • R950 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCAVPY","label":"General Issues"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.5","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
19 September 2010