Example

Assume the following employee table EMPTABLE exists:

Table 260 Contents of EMPTABLE for ON DB2 Statement Example

Field Name	Description	Field Type	Nulls	DB2 Field Length	Control-M/ Analyzer Field Length
EMPNO	Employee #	CHAR	no	8	8
EMPNAME	Employee name	VARCHAR	no	20	22
EMPHIREDATE	Date hired	DATE	yes	4	11
EMPRANK	Employee rank	SMALLINT	no	2	2
EMPSALARY	Gross salary	DECIMAL(10,2)	no	5	5

The following rule definition instructs Control-M/Analyzer to retrieve and print information about employees whose salaries are over 3,000:

Figure 257 ON DB2 Statement Example

LIBRARY : CTBP.PROD.RULES RULE : EMPSAL

COMMAND ===> SCROLL===>CRSR

+-----------------------------------------------------------------------------+

OWNER M43 GROUP PERSONNEL

UPDATED 08/08/00 - 10:20:09 BY M43

DESC

OPTIONS

================================================================================

EXECUTE GETAVG UPON C

ON DB2 SUBSYSTEM ID S111

SQL SELECT EMPNAME,SALARY FROM EMPTABLE WHERE SALARY>3000 C

MODE DS LINECT 0000 DATASTAMP

WHEN LINE 001 - 999 COL 001 - 001 STOP AND/OR

STRING = .AL.

DO EXTRACT = EMPNAME

LEVEL LINE +000 COL 0003 - 0022 PROCESS TYP

DO EXTRACT = EMPSALARY

LEVEL LINE +000 COL 0023 - 0027 PROCESS BUNPK2 TYP

DO PRINT = %%EMPNAME %EMPSALARY F C

DO

WHEN LINE - COL - STOP AND/OR

STRING =

DO

=================================================================================

EXECUTE UPON C

PLEASE FILL IN RULE DEFINITION. 11.42.28

In the example:

Extraction of the EMPNAME field starts at column 3, even though according to its width, it should begin at column 1. This is because the EMPNAME field is of type VARCHAR, and the first two bytes, columns 1 and 2, contain the length of the field.
Built-in process BUNPK2 is used to extract the employee salary and rank.