You can call SQL procedures by using the command line processor (CLP) call command or by building client applications.
To use the call command, you must enter the stored procedure name plus any IN or INOUT arguments that are required by that stored procedure. You do not enter OUT parameters.
First, create the SQL procedure by following the steps in Creating SQL Procedures.
To call the SQL procedure, you must first connect to the database:
db2 connect to sample user userid using password
where userid and password are the user ID and password of the instance where the sample database is located.
The parameters for a stored procedure are given in the CREATE PROCEDURE statement for the stored procedure in the program source file. For example, in the source file, whiles.db2, the CREATE PROCEDURE statement for the DEPT_MEDIAN procedure begins:
CREATE PROCEDURE DEPT_MEDIAN (IN deptNumber SMALLINT, OUT medianSalary DOUBLE)
To call this procedure, you need to put in a valid SMALLINT value for the IN parameter, deptNumber. You can obtain a valid value from the corresponding table in the sample database, or by checking the client calling program source file for the value it uses. In whiles.sqc, you will find the value "51" is used:
printf("Use CALL with Host Variables to invoke the Server Procedure "
"named %s\n", procname);
dept = 51; /* get median for dept. 51 */
Enter the call command with the procedure name and the value for the IN parameter. The procedure's parameters must be enclosed in parentheses, and quotes must be used, as follows:
db2 "call DEPT_MEDIAN (51)"
You should receive this result:
MEDIANSALARY: 1.76545000000000e+04
Keep the following points in mind when using the call command:
The command file bldcli.cmd in %DB2PATH%\samples\sqlproc contains the commands to build a DB2 CLI client application for SQL procedures. See "DB2 CLI Applications" for detailed information on bldcli.cmd.
To build the DB2 CLI client application, rsultset, from the source file rsultset.c, enter:
bldcli rsultset
This command creates the executable file, rsultset.
To call the stored procedure, run the sample client application by entering the executable file name, the name of the database to which you are connecting, and the user ID and password of the database instance:
rsultset database userid password
The command file bldapp.cmd in %DB2PATH%\samples\sqlproc contains the commands to build an embedded SQL client application for SQL procedures. See "DB2 API and Embedded SQL Applications" for detailed information on bldapp.cmd.
To build the embedded SQL client application, basecase, from the source file basecase.sqc, enter the command file name, the executable name, the database to which you are connecting, and the user ID and password of the database instance:
bldapp basecase database userid password
The result is an executable file, basecase.
To call the stored procedure, run the client application by entering:
basecase database userid password
The script file bldcli in sqllib/samples/sqlproc contains the commands to build a DB2 CLI client application for SQL procedures. For detailed information on the bldcli script file, see the "DB2 CLI Applications" section in the "Building Applications" chapter for your UNIX platform.
To build the DB2 CLI client application, rsultset, from the source file rsultset.c, enter:
bldcli rsultset
This command creates the executable file, rsultset.
To call the stored procedure, run the sample client application by entering the executable file name, the name of the database to which you are connecting, and the user ID and password of the database instance:
rsultset database userid password
The script file bldapp in sqllib/samples/sqlproc contains the commands to build an embedded SQL client application for SQL procedures. For detailed information on the bldapp script file, see the "DB2 API and Embedded SQL Applications" section in the "Building Applications" chapter for your UNIX platform.
To build the embedded SQL client application, basecase, from the source file basecase.sqc, enter the script file name, the executable name, the database to which you are connecting, and the user ID and password of the database instance:
bldapp basecase database userid password
The result is an executable file, basecase.
To call the stored procedure, run the sample client application by entering:
basecase database userid password
The %DB2PATH%\samples\sqlproc directory contains two build files for building DB2 CLI client applications: bldmcliis for the Microsoft Visual C++ compiler, and bldvcli is for the IBM VisualAge C++ compiler. For detailed information on bldmcli, see "DB2 CLI Applications". For detailed information on bldvcli, see "DB2 CLI Applications".
To build the DB2 CLI client application, rsultset, from the source file rsultset.c, depending on the compiler you are using, enter either:
bldmcli rsultset
or
bldvcli rsultset
These commands create the executable file, rsultset.
To call the stored procedure, run the sample client application by entering the executable file name, the name of the database to which you are connecting, and the user ID and password of the database instance:
rsultset database userid password
The %DB2PATH%\samples\sqlproc directory contains two build files for building embedded SQL client applications: bldmapp is for the Microsoft Visual C++ compiler, and bldvapp is for the IBM VisualAge C++ compiler. For detailed information on bldmapp, see "DB2 API and Embedded SQL Applications". For detailed information on bldvapp, see "DB2 API and Embedded SQL Applications".
To build the embedded SQL client application, basecase, from the source file basecase.sqc, enter script file name, the executable name, the database to which you are connecting, and the user ID and password of the database instance. Depending on the compiler you are using, this command would be either:
bldmapp basecase database userid password
or
bldvapp basecase database userid password
The result is an executable file, basecase.
To call the stored procedure, run the sample client application by entering:
basecase database userid password