Rebind fails with SQL0440N on routine "SNAPSHOT_CNTRFS", after Fix Pack upgrade

https://www.ibm.com/support/pages/rebind-fails-sql0440n-routine-snapshotcntrfs-after-fix-pack-upgrade

Troubleshooting

Problem

After upgrading DB2 version 9.7 to Fix Pack 3 or later, a database rebind using the db2rbind command may fail with error messages such as the following:

Symptom

Package 'NULLID.STADMG01' of database 'SAMPLE' did not succeed to rebind. UniqueID = 4141414141494157

sqlcode = -440; sqlerrmc = SNAPSHOT; sqlrrp = SQLNQ25ESNAPSHOT

SQL0440N No authorized routine named "SNAPSHOT_CNTRFS" of type

"FUNCTION" having compatible argumements was found.

Cause

In version 9.7, the IBM system function "SNAPSHOT_CNTRFS" was altered, and the package used by this function (STADMG01 or STADMG02 in version 9.5) was replaced by a new version (STADMH02 in version 9.7 GA, STADMH03 beginning in Fix Pack 1).
However, during the database upgrade, until Fix Pack 3 the function was not replaced if it already existed. In an upgrade of an earlier Fix Pack (GA, Fix Pack 1 or 2) to Fix Pack 3 or later, the existing package is invalidated, and the existing function has been dropped and replaced by a new version with a different unique ID. A subsequent rebind will fail to rebind the existing package because the function it references no longer exists.

Resolving The Problem

To bind the new version of this package, do the following:

On Linux/Unix: (as instance owner)

db2 CONNECT TO dbname

db2 BIND $HOME/sqllib/bnd/st_admin.bnd BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE

db2 terminate

On Windows: (from a DB2 Command Window, for the current DB2 Copy)

db2 CONNECT TO <database_name>

db2 BIND %DB2PATH%\bnd\st_admin.bnd BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE

db2 terminate

where dbname represents the name of a database to which the files should be bound. On Unix or Linux, $HOME refers to the instance home directory. On Windows, %DB2PATH% refers to the DB2 installation path (e.g. C:\Program Files\IBM\SQLLIB).

------------------------------------------------------------------------------------------
如果你觉得文章有用，欢迎打赏