Routines are stored in a special table called ROUTINE. It
consists of four columns: NAME, SEQNO, COMMAND, and REMARKS
(optional). An example of a routine table is shown in Figure 43.
Figure 43. Example of a Routine Table
| NAME | SEQNO | COMMAND | REMARKS |
|---|---|---|---|
| EMPLREP | 10 | SELECT PROJNO,ACTNO,ACSTAFF - | GENERATE AND PRINT A |
| EMPLREP | 20 | FROM PROJ_ACT - | REPORT FOR PROJECT |
| EMPLREP | 30 | WHERE PROJNO IN (&1) - | STAFF |
| EMPLREP | 40 | ORDER BY PROJNO,ACTNO |
|
| EMPLREP | 50 | FORMAT GROUP PROJNO |
|
| EMPLREP | 60 | FORMAT SUBTOTAL ACSTAFF |
|
| EMPLREP | 70 | FORMAT TTITLE 'AVERAGE PROJECT STAFF' |
|
| EMPLREP | 80 |
| |
| EMPLREP | 90 | END |
|
| PRINTDEP | 10 | SELECT * FROM DEPARTMENT | FORMAT DEPARTMENT TABLE |
| PRINTDEP | 20 | FORMAT SEPARATOR ' | ' | AND PRINT IT |
| PRINTDEP | 30 | FORMAT COLUMN DEPTNAME WIDTH 30 |
|
| PRINTDEP | 40 |
| |
| PRINTDEP | 50 | END |
|
The NAME column identifies the rows that belong to a particular routine. SEQNO specifies the sequence in which the commands and statements are executed. Use sequence numbers that are increments of ten to allow for later additions. The COMMAND column contains the SQL statements and ISQL commands.
Before creating routines, you must have a routine table. You can create your own routine table if you have Resource authority or your own private dbspace (DB2 Server for VM user only). If you do not have Resource authority, ask the appropriate person to create a routine table for you. The following SQL statement illustrates the creation of a routine table:
create table routine (name char(8) not null, -
seqno integer not null, -
command varchar(254) not null, -
remarks varchar(254))
The CREATE TABLE statement is discussed in detail in Chapter 8, Creating and Managing Tables.
When creating this table, use the CREATE TABLE statement exactly as shown above, except for the REMARKS column which is optional. If it is included, specifying a data type of VARCHAR with a length of 40 is usually sufficient. The size selected for the REMARKS column should accommodate the largest entry used. Allow nulls so that remarks are not required. The COMMAND column can be a maximum length of 254 characters.
Once the table is created, create an index for it so that when the table is referenced, the commands or statements are displayed or executed in the correct order. To create an index, type a statement similar to the following (substitute a name of your choice for RINDEX):
create unique index rindex on routine -
(name, seqno)
For detailed information on the CREATE INDEX statement, refer to Chapter 8, Creating and Managing Tables.