75 ways to demystify DB2 #77: Techtip : DB2V105 Native Encryption Missing Function and Routine

Body

When customer using native encryption, in DB2V105FP5 and later fix packs, they may find that the following function and routine are missing:

ADMIN_GET_ENCRYPTION_INFO and ADMIN_ROTATE_MASTER_KEY.

db2 "select substr(funcschema,1,12)as funcs, substr(funcname,1,30) as name from syscat.functions where funcname='ADMIN_GET_ENCRYPTION_INFO' order by 1,2"

FUNCS        NAME

------------ ------------------------------

  0 record(s) selected.

db2 "select substr(ROUTINESCHEMA,1,12)as funcs, substr(ROUTINENAME,1,30) as name from syscat.ROUTINES where ROUTINENAME='ADMIN_ROTATE_MASTER_KEY' order by 1,2"

FUNCS        NAME

------------ ------------------------------

  0 record(s) selected.

If customer try to use these function and routine, they will receive the following error:

db2 "SELECT OBJECT_NAME, OBJECT_TYPE, ALGORITHM, ALGORITHM_MODE, KEY_LENGTH, MASTER_KEY_LABEL FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())"

SQL0440N  No authorized routine named ADMIN_GET_ENCRYPTION_INFO of type

      "FUNCTION" having compatible arguments was found.

db2 "CALL SYSPROC.ADMIN_ROTATE_MASTER_KEY (NULL)"

SQL0440N  No authorized routine named "ADMIN_ROTATE_MASTER_KEY" of type "PROCEDURE" having compatible arguments was found.

These function and routine are created with the database and cannot be remove after creation. According to IC page https://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0061758.html, native encryption was introduced in DB2V105FP5, if customer already using DB2V105FP5 or later fix packs, why can't customer find and use these function and routine?

This is because the database that customer is trying to use was created in a DB2 instance before the native encryption was introduced. Normally after the upgrade of instance, it should be follow by upgrade of database too. However, in some cases, especially when the database was created within the same DB2 version (i.e. DB2V105) but earlier fix pack (i.e. FP0, FP1, FP2, FP3 and FP4), customer may not upgrade the database to the latest instance version. So in the case customer is using an instance level of DB2V105FP5 or later, but the database level is DB2V105FP4 or earlier, this issue will occur.

The solution is to run command "db2updv105 -d <dbname>" to upgrade the database to the same level of the instance.

After upgrade the database, check again, the correct outputs are:

db2 "SELECT OBJECT_NAME, OBJECT_TYPE, ALGORITHM, ALGORITHM_MODE, KEY_LENGTH, MASTER_KEY_LABEL FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())"

OBJECT_NAME                 OBJECT_TYPE      ALGORITHM       ALGORITHM_MODE                                 KEY_LENGTH                                                                                 MASTER_KEY_LABEL

---------------------               -------------------     ----------------        ----------------------------
------------------                                                                               ----------------------------

TESTDB02  DATABASE     AES                        CBC                     256                                                                                       DB2_SYSGEN_DB2_03_TESTDB02_2015-11-04-21.32.4        0

  1 record(s) selected.

db2 "CALL SYSPROC.ADMIN_ROTATE_MASTER_KEY (NULL)"

  Value of output parameters

  --------------------------

  Parameter Name  : LABEL

  Parameter Value : DB2_SYSGEN_DB2_03_TESTDB02_2015-11-04-21.45.36

  Return Status = 0

db2 "select substr(funcschema,1,12)as funcs, substr(funcname,1,30) as name from syscat.functions where funcname='ADMIN_GET_ENCRYPTION_INFO' order by 1,2"

FUNCS        NAME

------------ ------------------------------

SYSPROC      ADMIN_GET_ENCRYPTION_INFO

  1 record(s) selected.

db2 "select substr(ROUTINESCHEMA,1,12)as funcs, substr(ROUTINENAME,1,30) as name from syscat.ROUTINES where ROUTINENAME='ADMIN_ROTATE_MASTER_KEY' order by 1,2"

FUNCS        NAME

------------ ------------------------------

SYSPROC      ADMIN_ROTATE_MASTER_KEY

  1 record(s) selected.