Stored procedures fail with a CURRENT PATH issue (DB2)
Called stored procedures can fail if the path to the schema is undefined. This error can appear at system startup or when you run an upgrade script.
For example, this message is generated on system startup when a call to SPPR_AGGREGATE_USAGE fails while trying to add licensing information to the database:
Daemon problem while running the procedure in License Daemon:com.pega.pegarules.pub.database.DatabaseException: Database-General
There was a problem getting a list -444 42724 Routine "*_SECONDS" (specific name "MIDNIGHT_SECONDS2") is implemented with code in library or path "...n/db2clifn.a", function "CLI_udfMIDNIGHT_SECONDS2" which cannot be accessed. Reason code: "4".
From: (B381190ABEFC51C0297DBB54D26A6D625:(License Daemon))
SQL: {call sppr_aggregate_usage}
SQL Inserts:
Caused by SQL Problems.
Problem #1, SQLState 42724, Error code -444: com.ibm.db2.jcc.a.SqlException: Routine "*_SECONDS" (specific name "MIDNIGHT_SECONDS2") is implemented with code in library or path "...n/db2clifn.a", function "CLI_udfMIDNIGHT_SECONDS2" which cannot be accessed. Reason code: "4".
Solution
Both the DB2 CurrentFunctionPath property and the DB2 environment variable CURRENT_PATH specify the SQL path used to resolve unqualified data types and function names in SQL statements. CURRENT_PATH overrides CurrentFunctionPath.
To resolve the issue, set the value of CURRENT_PATH even if CurrentFunctionPath is already defined:
- Have your database administrator use the IBM DB2 command-line processor (CLP) to set CURRENT_PATH to include SYSIBM, SYSFUN, SYSPROC, and the data schema name. The data type is string, and the value is a comma-separated list of schema names. All values must be in quotes. For example:
db2> SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","schema"
where schema is the name of your data schema.
Re-create the SPPR_AGGREGATE_USAGE stored procedure.
Previous topic Issue: Update DB2 z/OS stored procedures sppc_data_uniqueid and UNIQID to prevent infinite loop and improve performance Next topic System won't start when using DB2 version 9.5