DB2 does support stand-alone codes, also known as SQL PL scripting. It is very straightforward to develop DB2 stand-alone codes because you have just learned about DB2 UDFs and triggers. DB2 stand-alone codes, which are the same as UDFs and triggers, are supported by DB2 inline SQL PL. Figure B.5 shows an example of the DB2 stand-alone code. The example requires a supporting table duplicate_empno, which is defined as the following:
CREATE TABLE duplicate_empno(empno CHAR(6))
Figure B.5. An example of DB2 stand-alone code.
BEGIN ATOMIC -- (1) DECLARE v_empno CHAR(6); DECLARE v_prev_empno CHAR(6); FOR c_emp AS -- (2) SELECT empno FROM employee ORDER BY empno DO SET v_empno = empno; IF (v_empno = v_prev_empno) THEN -- (3) INSERT INTO duplicate_empno VALUES (v_empno); END IF; SET v_prev_empno = v_empno; END FOR; END -- (4) @
The example in Figure B.5 is a utility script that can be used to quickly find out the duplicate employee number in the employee table. Assume that the data in the department table is found to be corrupted. For some reason, there are few cases of employees with the same employee ID. The code is used to quickly find the bad employee IDs for further investigation.
A piece of stand-alone code must be encased within the BEGIN ATOMIC and END clause on Lines (1) and (4). An implicit cursor is used with a FOR loop on Line (2). The algorithm to find the duplicate IDs is to sort the IDs first and then compare each ID with the previous ID. All IDs matching their previous IDs are captured on Line (3) for further investigation.
The '@' character at the last line is the statement terminator. To execute the code, you need to save it to a file, say find_dup.db2. Then, execute the following at the command prompt:
db2 -td@ -f find_dup.db2
For more information on the statement terminator and on how to execute statements in files, refer to Appendix A, "Getting Started with DB2."
Please note that there are no duplicate employee numbers in the employee table. You need to manually insert a few rows of bad data in order to capture the duplicate employee numbers.