To use DB/Assist to create the sample program, follow the procedure in this section.
This procedure provides general steps only. For more detailed information about a particular step, refer to the appropriate chapter in this manual.
To create the SAM in DB/Assist, follow these steps:
1.Start DB/Assist.
2.Create a new SAM. Name it POSITION, and associate it with the SAMPLE database.
To build the required SQL statements, continue with these steps:
1. Build the following multi-row SELECT statement named SelectStaffUpdate:
SELECT STAFF.ID, STAFF.NAME, STAFF.DEPT, STAFF.JOB, STAFF.YEARS, STAFF.SALARY, STAFF.COMM FROM DB2ADMIN.STAFF STAFF
1.Add the following FOR UPDATE OF clause (shown in bold) to the SELECT statement by typing it in the SQL Editor window.
SELECT STAFF.ID, STAFF.NAME, STAFF.DEPT, STAFF.JOB, STAFF.YEARS, STAFF.SALARY,
STAFF.COMM
FROM DB2ADMIN.STAFF STAFF
FOR UPDATE OF NAME, DEPT, JOB, YEARS, SALARY,
COMM
2.In the Code Generation - Statement Options dialog box, check the With Hold check box
This generates a cursor for the SELECT statement that will remain open after a COMMIT.
3.Build the following positioned UPDATE statement named UpdateCurrentStaff
UPDATE DB2ADMIN.STAFF
SET NAME = :NAME_SV, DEPT = :DEPT_IV, JOB = :JOB_SV, YEARS = :YEARS_IV, SALARY = :SALARY_FV, COMM = :COMM_FV
WHERE CURRENT OF SQL_CUR_1
Make sure that the cursor you specify in the WHERE CURRENT OF clause is the same one created for the SELECT statement.
4.Build the following positioned DELETE statement named DeleteCurrentStaff:
DELETE FROM STAFF
WHERE CURRENT OF SQL_CUR_1
Make sure the cursor you specify in the WHERE CURRENT OF clause is the same one created for the SELECT statement.
To compile the SAM, continue with these steps:
1.For each SQL statement, complete the Link Variables dialog box to link ESL variables to each column.
2.Compile the SAM
Generate the resulting ESL action routines to a file named Position.act. You must include this file in the sample program shown in Sample ESL Code.
3.Create a variable file named Position.var. This file will also be included in the sample program shown in Sample ESL Code.
