LinkedIn
Copied!

Table of Contents

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:

  1. 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.

  2. Re-create the SPPR_AGGREGATE_USAGE stored procedure.

Suggest Edit

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.