DB2 Server for VSE & VM: Interactive SQL Guide and Reference

Storing a Routine

When you insert new commands or statements for a routine into the ROUTINE table, always assign the same routine name in the NAME column. Commands and statements are inserted into the routine table in the same manner as data is inserted into any database manager table by using SQL INSERT statements or the ISQL INPUT command.

The use of ampersands (&) in a routine is allowed only for creating placeholders (see the description of the RUN command for more information on placeholders).

In the COMMAND column of the routine, be sure to:

• Put single quotation marks around any placeholder that stands for a character data item. Enclose the COMMAND column (CHARACTER data type) in single quotation marks, and use a pair of single quotation marks for each single quotation mark that is to appear inside an SQL statement. This is shown in the example below.
• Use the continuation character as if you were typing a long SQL statement.

In the following example, the INPUT command inserts the commands and statements for a routine named QREPORT into the routine table.

   input routine
   'qreport',10,'select projno,acstaff -','begin:'
   'qreport',20,'from proj_act -',null
   'qreport',30,'where projno = '&1'' -',null
   'qreport',40,'or actno = &2 -',null
   'qreport',50,'order by projno',null
   'qreport',60,'format group projno',null
   'qreport',70,'format subtotal acstaff',null
   'qreport',80,'print',null
   'qreport',90,'end','done!'
    end

The stored information should resemble Figure 44.

Figure 44. Routine Statements Inserted in the Routine Table

NAME	SEQNO	COMMAND	REMARKS
QREPORT QREPORT QREPORT QREPORT QREPORT QREPORT QREPORT QREPORT QREPORT	10 20 30 40 50 60 70 80 90	SELECT PROJNO,ACSTAFF - FROM PROJ_ACT - WHERE PROJNO = '&1' - OR ACTNO = &2 - ORDER BY PROJNO FORMAT GROUP PROJNO FORMAT SUBTOTAL ACSTAFF PRINT END	BEGIN:               DONE!

To display your stored QREPORT routine, type:

   select * -
   from routine -
   where name='qreport'

The display resembles Figure 45.

Figure 45. Display of Routine Statements Inserted in the Routine Table

+--------------------------------------------------------------------------------+
| NAME             SEQNO  COMMAND                REMARKS                         |
| --------  ------------  ---------------------  --------------------            |
| QREPORT             10  SELECT PROJNO,ACSTAF<  BEGIN:                          |
| QREPORT             20  FROM PROJ_ACT          ?                               |
| QREPORT             30  WHERE PROJNO = '&1'    ?                               |
| QREPORT             40  OR ACTNO = &2 -        ?                               |
| QREPORT             50  ORDER BY PROJNO        ?                               |
| QREPORT             60  FORMAT GROUP PROJNO    ?                               |
| QREPORT             70  FORMAT SUBTOTAL ACST<  ?                               |
| QREPORT             80  PRINT                  ?                               |
| QREPORT             90  END                    DONE!                           |
| * End of Result *** 9 Rows Displayed ***Cost Estimate is 1 ********            |
+--------------------------------------------------------------------------------+