DB2 10.5 for Linux, UNIX, and Windows

SQL PL samples

UNIX directory: sqllib/samples/sqlpl.

Windows directory: sqllib\samples\sqlpl.

Table 1. SQL PL sample program files
Sample program name	Program description
`basecase.db2`	The UPDATE_SALARY procedure raises the salary of an employee identified by the "empno" IN parameter in the "staff" table of the "sample" database. The procedure determines the raise according to a CASE statement that uses the "rating" IN parameter.
`basecase.sqc`	Calls the UPDATE_SALARY procedure.
`baseif.db2`	The UPDATE_SALARY_IF procedure raises the salary of an employee identified by the "empno" IN parameter in the "staff" table of the "sample" database. The procedure determines the raise according to an IF statement that uses the "rating" IN parameter.
`baseif.sqc`	Calls the UPDATE_SALARY_IF procedure.
`defaultparam.db2`	Demonstrates the use of keyword DEFAULT as a parameter.
`dynamic.db2`	The CREATE_DEPT_TABLE procedure uses dynamic DDL to create a new table. The name of the table is based on the value of the IN parameter to the procedure.
`dynamic.sqc`	Calls the CREATE_DEPT_TABLE procedure.
`iterate.db2`	The ITERATOR procedure uses a FETCH loop to retrieve data from the "department" table. If the value of the "deptno" column is not 'D11', modified data is inserted into the "department" table. If the value of the "deptno" column is 'D11', an ITERATE statement passes the flow of control back to the beginning of the LOOP statement.
`iterate.sqc`	Calls the ITERATOR procedure.
`leave.db2`	The LEAVE_LOOP procedure counts the number of FETCH operations performed in a LOOP statement before the "not_found" condition handler invokes a LEAVE statement. The LEAVE statement causes the flow of control to exit the loop and complete the stored procedure.
`leave.sqc`	Calls the LEAVE_LOOP procedure.
`loop.db2`	The LOOP_UNTIL_SPACE procedure counts the number of FETCH operations performed in a LOOP statement until the cursor retrieves a row with a space (' ') value for column "midinit". The loop statement causes the flow of control to exit the loop and complete the stored procedure.
`loop.sqc`	Calls the LOOP_UNTIL_SPACE procedure.
`modules.db2`	Creates modules, public and private modules, associative arrays and parameterized cursors.
`nestcase.db2`	The BUMP_SALARY procedure uses nested CASE statements to raise the salaries of employees in a department identified by the dept IN parameter from the "staff" table of the "sample" database.
`nestcase.sqc`	Calls the BUMP_SALARY procedure.
`nestif.db2`	The BUMP_SALARY_IF procedure uses nested IF statements to raise the salaries of employees in a department identified by the dept IN parameter from the "staff" table of the "sample" database.
`nestif.sqc`	Calls the BUMP_SALARY_IF procedure.
`NestedSP.java`	The OUT_AVERAGE, OUT_MEDIAN, and MAX_SALARY procedures return average, median and max values from the "staff" table of the sample database.
`nestedspdrop.db2`	Drops the OUT_AVERAGE, OUT_MEDIAN and MAX_SALARY SQL procedures that are created with the nestedsp.db2 script.
`nestedsp.db2`	Calls the OUT_AVERAGE procedure.
`repeat.db2`	The REPEAT_STMT procedure counts the number of FETCH operations performed in a repeat statement until the cursor can retrieve no more rows. The condition handler causes the flow of control to exit the repeat loop and complete the stored procedure.
`repeat.sqc`	Calls the REPEAT_STMT procedure.
`rsultset.c`	Calls the MEDIAN_RESULT_SET procedure, displays the median salary, then displays the result set generated by the SQL procedure. This client is written using the CLI API, which can accept result sets.
`rsultset.db2`	The MEDIAN_RESULT_SET procedure obtains the median salary of employees in a department identified by the "dept" IN parameter from the "staff" table of the "sample" database. The median value is assigned to the salary OUT parameter and returned to the "rsultset" client. The procedure then opens a WITH RETURN cursor to return a result set of the employees with a salary greater than the median. The procedure returns the result set to the client.
`spserver.db2`	The SQL procedures in this CLP script demonstrate basic error-handling, nested stored procedure calls, and returning result sets to the client application or the calling application. You can call the procedures using the "spcall" application, in the CLI samples directory. You can also use the "spclient" application, in the C and CPP samples directories, to call the procedures that do not return result sets.
`tbfn.db2`	Creates the tables and table functions used in the tbfnuse sample. After the tbfnuse script is run, all changes are rolled back and the tables and functions created in this file are dropped.
`tbfnuse.db2`	Demonstrates the use of table functions created in the tbfnuse sample. At the end of this script, statements are rolled back and the tables and functions created in tbfn.db2 are dropped.
`tbsel.sqc`	How to select from each of: insert, update, delete. This sample calls an SQL procedure, BUY_COMPANY, created from tbselcreate.db2. BUY_COMPANY contains usage examples of a SELECT from a data change statement.
`tbselcreate.db2`	How to create the tables and the procedure used in the tbsel program.
`tbseldrop.db2`	How to drop the tables and the procedure used in the tbsel program.
`whiles.db2`	The DEPT_MEDIAN procedure obtains the median salary of employees in a department identified by the "dept" IN parameter from the "staff" table of the "sample" database. The median value is assigned to the salary OUT parameter and returned to the "whiles" client. The whiles client then prints the median salary.
`whiles.sqc`	Calls the DEPT_MEDIAN procedure.
`arrays_sqlpl.db2`	Use ARRAY variable in SQL stored procedure.
`array_stack.db2`	Use ARRAY variable in SQL stored procedure. Implement stack functions.