Calling different versions of a stored procedure from a single application

You can call different versions of a stored procedure from the same application program, even though those versions all have the same load module name.

Procedure

To call different versions of a stored procedure from a single application:

  1. When you define each version of the stored procedure, use the same stored procedure name but different schema names, different COLLID values, and different WLM environments.
  2. In the program that invokes the stored procedure, specify the unqualified stored procedure name in the CALL statement.
  3. Use the SQL path to indicate which version of the stored procedure that the client program should call. You can choose the SQL path in several ways:
    • If the client program is not an ODBC or JDBC application, use one of the following methods:
      • Use the CALL procedure-name form of the CALL statement. When you bind plans or packages for the program that calls the stored procedure, bind one plan or package for each version of the stored procedure that you want to call. In the PATH bind option for each plan or package, specify the schema name of the stored procedure that you want to call.
      • Use the CALL host-variable form of the CALL statement. In the client program, use the SET PATH statement to specify the schema name of the stored procedure that you want to call.
    • If the client program is an ODBC or JDBC application, choose one of the following methods:
      • Use the SET PATH statement to specify the schema name of the stored procedure that you want to call.
      • When you bind the stored procedure packages, specify a different collection for each stored procedure package. Use the COLLID value that you specified when defining the stored procedure to Db2.
  4. When you run the client program, specify the plan or package with the PATH value that matches the schema name of the stored procedure that you want to call.

Results

For example, suppose that you want to write one program, PROGY, that calls one of two versions of a stored procedure named PROCX. The load module for both stored procedures is named SUMMOD. Each version of SUMMOD is in a different load library. The stored procedures run in different WLM environments, and the startup JCL for each WLM environment includes a STEPLIB concatenation that specifies the correct load library for the stored procedure module.

First, define the two stored procedures in different schemas and different WLM environments: 
CREATE PROCEDURE TEST.PROCX(IN V1 INTEGER, OUT V2 CHAR(9))
  LANGUAGE C
  EXTERNAL NAME SUMMOD
  WLM ENVIRONMENT TESTENV;
CREATE PROCEDURE PROD.PROCX(IN V1 INTEGER, OUT V2 CHAR(9))
  LANGUAGE C
  EXTERNAL NAME SUMMOD
  WLM ENVIRONMENT PRODENV;
When you write CALL statements for PROCX in program PROGY, use the unqualified form of the stored procedure name: 
CALL PROCX(V1,V2);

Bind two plans for PROGY. In one BIND statement, specify PATH(TEST). In the other BIND statement, specify PATH(PROD).

To call TEST.PROCX, execute PROGY with the plan that you bound with PATH(TEST). To call PROD.PROCX, execute PROGY with the plan that you bound with PATH(PROD).