Example programs: Using Db2 for i statements
Here is a sample application that shows how to code SQL statements in each of the languages that Db2® for i supports.
The sample application gives raises based on commission.
Each sample program produces the same report, which is shown at the end of this topic. The first part of the report shows, by project, all employees working on the project who received a raise. The second part of the report shows the new salary expense for each project.
Notes about the sample programs
The following notes apply to all the sample programs:
- SQL statements can be entered in uppercase or lowercase.
- 1
- This host language statement retrieves the external definitions
for the SQL table PROJECT. These definitions can be used as host variables
or as a host structure. Notes:
- In RPG/400®, field names in an externally described structure that are longer than 6 characters must be renamed.
- REXX does not support the retrieval of external definitions.
- 2
- The SQL INCLUDE SQLCA statement is used to include the SQLCA for PL/I, C, and COBOL programs. For RPG programs, the SQL precompiler automatically places the SQLCA data structure into the source at the end of the Input specification section. For REXX, the SQLCA fields are maintained in separate variables rather than in a contiguous data area mapped by the SQLCA.
- 3
- This SQL WHENEVER statement defines the host language label to which control is passed if an SQLERROR (SQLCODE < 0) occurs in an SQL statement. This WHENEVER SQLERROR statement applies to all the following SQL statements until the next WHENEVER SQLERROR statement is encountered. REXX does not support the WHENEVER statement. Instead, REXX uses the SIGNAL ON ERROR facility.
- 4
- This SQL UPDATE statement updates the SALARY column, which contains the employee salary by the percentage in the host variable PERCENTAGE (PERCNT for RPG). The updated rows are those that have employee commissions greater than 2000. For REXX, this is PREPARE and EXECUTE since UPDATE cannot be run directly if there is a host variable.
- 5
- This SQL COMMIT statement commits the changes made by the SQL
UPDATE statement. Record locks on all changed rows are released. Note: The program was precompiled using COMMIT(*CHG). (For REXX, *CHG is the default.)
- 6
- This SQL DECLARE CURSOR statement defines cursor C1, which joins two tables, EMPLOYEE and EMPPROJACT, and returns rows for employees who received a raise (commission > 2000). Rows are returned in ascending order by project number and employee number (PROJNO and EMPNO columns). For REXX, this is a PREPARE and DECLARE CURSOR since the DECLARE CURSOR statement cannot be specified directly with a statement string if it has host variables.
- 7
- This SQL OPEN statement opens cursor C1 so that the rows can be fetched.
- 8
- This SQL WHENEVER statement defines the host language label to which control is passed when all rows are fetched (SQLCODE = 100). For REXX, the SQLCODE must be explicitly checked.
- 9
- This SQL FETCH statement returns all columns for cursor C1 and places the returned values into the corresponding elements of the host structure.
- 10
- After all rows are fetched, control is passed to this label. The SQL CLOSE statement closes cursor C1.
- 11
- This SQL DECLARE CURSOR statement defines cursor C2, which joins the three tables, EMPPROJACT, PROJECT, and EMPLOYEE. The results are grouped by columns PROJNO and PROJNAME. The COUNT function returns the number of rows in each group. The SUM function calculates the new salary cost for each project. The ORDER BY 1 clause specifies that rows are retrieved based on the contents of the final results column (EMPPROJACT.PROJNO). For REXX, this is a PREPARE and DECLARE CURSOR since the DECLARE CURSOR statement cannot be specified directly with a statement string if it has host variables.
- 12
- This SQL FETCH statement returns the results columns for cursor C2 and places the returned values into the corresponding elements of the host structure described by the program.
- 13
- This SQL WHENEVER statement with the CONTINUE option causes processing to continue to the next statement regardless if an error occurs on the SQL ROLLBACK statement. Errors are not expected on the SQL ROLLBACK statement; however, this prevents the program from going into a loop if an error does occur. REXX does not support the WHENEVER statement. Instead, REXX uses the SIGNAL OFF ERROR facility.
- 14
- This SQL ROLLBACK statement restores the table to its original condition if an error occurred during the update.