Troubleshooting
Problem
IBM i commands, programs, and APIs can be called from SQL applications by using external stored procedure calls.
Resolving The Problem
Stored procedures can be used with any SQL-based middleware. For example, IBM i Access Client Solutions Windows Application Package ODBC driver, OLEDB provider, ADO.Net provider, and the IBM Toolbox for Java JDBC driver.
The following techniques can be used. The first uses an external stored procedure definition specific to the program that is called. This method offers the greatest flexibility since it allows output and input/output parameters and even result sets. A second method that uses the IBM i command interpreter, offers a simple way to run operating system commands and programs when only a success or failure return value is required.
External Stored Procedure Call
The preferred method for calling an IBM i program is through a stored procedure call. Virtually any IBM i program written in any language can be called as a stored procedure. This method is required if the program returns output or input/output parameters or if the program is to return an SQL result set.
The first step in calling the program is to define the program to IBM DB2 for i by creating the procedure definition. Define the procedure by the CREATE PROCEDURE SQL statement.
The create procedure statement provides Db2 with the information it needs to safely map the SQL data types to the language-specific data type used by the operating system program. In addition, some client programming tools require the procedure metadata (created by the create procedure) to build the code for the call. The supported and recommend data type mappings for each programming language are described in the SQL Programming Guide. The syntax for the create procedure is defined in the SQL Reference. Both manuals can be found in IBM Documentation.
Even data types not supported directly in SQL (such as structures) can be used in stored procedure calls. To use an unsupported data type, the parameter must be declared as binary. The parameter data must then be converted in the application program. Both IBM i Access Client Solutions Windows Application Package and IBM Toolbox for Java provide APIs to aid in the data type conversion.
Once a definition for the procedure exists, the procedure can be called by using the Db2 syntax or the middleware-specific syntax (such as ODBC or JDBC escape sequences). The IBM Documentation website, the IBM i Access Client Solutions Windows Application Package programmer's toolkit, the IBM i support FTP site provide numerous examples.
Note: Although it is recommended that the procedure definition exists, it is not required. If a procedure definition is not created, the call must follow the rules outlined in the SQL Programming Concepts manual. Failure to follow these rules might result in corrupted data or severity 40 errors during the call. The following is taken from the R450 SQL Programming Concepts manual:
Calling a Program or Command through QCMDEXC
IBM i commands can be executed by using a stored procedure call to the command interpreter (QCMDEXC). Operating system programs that use input-only parameters can be called indirectly by using the CALL CL command. This technique is demonstrated in the ODBC User's Guide and in the following text. One advantage to this technique is that the SMBJOB CL command can be used to have the target program run in batch (asynchronously).
As an example, consider a CL program that accepts a single character string parameter. The first few lines of the CL source might look like the following:
This program can be called by executing the following SQL statement:
This example calls the CL program MYCLP in the MYLIB library with a value of 'ABCDEF' passed as input to the program. Because the program is submitted to a job queue, control is returned to the ODBC application almost immediately rather than after the program finishes processing. Note, parameter values that contain embedded single quotation marks must be escaped.
These alternatives all use a different operating system host server, which is optimized for this type of work (the remote command, program call host server). In general, performance is much better when you use this server; however, keep the following in mind:
The following techniques can be used. The first uses an external stored procedure definition specific to the program that is called. This method offers the greatest flexibility since it allows output and input/output parameters and even result sets. A second method that uses the IBM i command interpreter, offers a simple way to run operating system commands and programs when only a success or failure return value is required.
External Stored Procedure Call
The preferred method for calling an IBM i program is through a stored procedure call. Virtually any IBM i program written in any language can be called as a stored procedure. This method is required if the program returns output or input/output parameters or if the program is to return an SQL result set.
The first step in calling the program is to define the program to IBM DB2 for i by creating the procedure definition. Define the procedure by the CREATE PROCEDURE SQL statement.
The create procedure statement provides Db2 with the information it needs to safely map the SQL data types to the language-specific data type used by the operating system program. In addition, some client programming tools require the procedure metadata (created by the create procedure) to build the code for the call. The supported and recommend data type mappings for each programming language are described in the SQL Programming Guide. The syntax for the create procedure is defined in the SQL Reference. Both manuals can be found in IBM Documentation.
Even data types not supported directly in SQL (such as structures) can be used in stored procedure calls. To use an unsupported data type, the parameter must be declared as binary. The parameter data must then be converted in the application program. Both IBM i Access Client Solutions Windows Application Package and IBM Toolbox for Java provide APIs to aid in the data type conversion.
Once a definition for the procedure exists, the procedure can be called by using the Db2 syntax or the middleware-specific syntax (such as ODBC or JDBC escape sequences). The IBM Documentation website, the IBM i Access Client Solutions Windows Application Package programmer's toolkit, the IBM i support FTP site provide numerous examples.
Note: Although it is recommended that the procedure definition exists, it is not required. If a procedure definition is not created, the call must follow the rules outlined in the SQL Programming Concepts manual. Failure to follow these rules might result in corrupted data or severity 40 errors during the call. The following is taken from the R450 SQL Programming Concepts manual:
The following rules pertain to the processing of a dynamic CALL statement when there is no CREATE PROCEDURE definition:
...the length of the expected argument in the program must be kept in mind. If program MYLIB.P3 expected an argument of only 5 characters, [and seven are passed] the last 2 characters of the constant specified in the example would be lost to the program. For numeric constants passed on a CALL statement, the following rules apply:
|
Calling a Program or Command through QCMDEXC
IBM i commands can be executed by using a stored procedure call to the command interpreter (QCMDEXC). Operating system programs that use input-only parameters can be called indirectly by using the CALL CL command. This technique is demonstrated in the ODBC User's Guide and in the following text. One advantage to this technique is that the SMBJOB CL command can be used to have the target program run in batch (asynchronously).
As an example, consider a CL program that accepts a single character string parameter. The first few lines of the CL source might look like the following:
PGM PARM(&STRING1)
DCL VAR(&STRING1) TYPE(*CHAR) LEN(25)This program can be called by executing the following SQL statement:
CALL QSYS.QCMDEXC('SBMJOB CMD(CALL PGM(MYLIB/MYCLP) PARM(ABCDEF))',0000000058.00000)This example calls the CL program MYCLP in the MYLIB library with a value of 'ABCDEF' passed as input to the program. Because the program is submitted to a job queue, control is returned to the ODBC application almost immediately rather than after the program finishes processing. Note, parameter values that contain embedded single quotation marks must be escaped.
NOTE: Technical Refreshes to Db2 on i provided an enhancement so that the command length parameter is no longer required. For more information, see: QSYS2.QCMDEX() procedure no longer requires a command length
Alternatives
Other alternatives include:
Alternatives
Other alternatives include:
- The IBM i Access Client Solutions Windows Application Package program call APIs and Microsoft ActiveX objects
- The IBM i Access Client Solutions Windows Application Package OLEDB provider's native program call syntax
- The IBM Toolbox for Java ProgramCall, PCML, and Command classes
These alternatives all use a different operating system host server, which is optimized for this type of work (the remote command, program call host server). In general, performance is much better when you use this server; however, keep the following in mind:
- The programs cannot be run under the same commitment control definition as other SQL work. A different connection and different job are used to run the program in this mode.
- The IBM i Access Client Solutions Windows Application Package program call API and ActiveX objects as well as the IBM Toolbox for Java ProgramCall classes require the programmer to handle all data type conversions.
- The IBM i Access Client Solutions Windows Application Packages program call ActiveX objects and OLEDB are apartment threaded. That implies that the objects do perform as well as ODBC in a multithreaded application (such as Microsoft Internet Information Server).
- Applications that frequently connect and disconnect (such as most web applications) can use connection pooling. IBM i Access Client Solutions Windows Application Package does not provide connection pooling; however, the ODBC runtime does. IBM Toolbox for Java does supply a connection pool class, but the application programmer must include extra code to make use of it. This approach requires more programming effort than using an existing pool manager such as IBM WebSphere JDBC connection manager.
Related Information
[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Host Servers","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Historical Number
22415138
Was this topic helpful?
Document Information
More support for:
IBM i
Software version:
Version Independent
Operating system(s):
IBM i
Document number:
641325
Modified date:
12 July 2023
UID
nas8N1017493
Manage My Notification Subscriptions