Rebind fails with SQL0440N on routine "SNAPSHOT_CNTRFS", after Fix Pack upgrade
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).
------------------------------------------------------------------------------------------
如果你觉得文章有用，欢迎打赏
