An EXEC is a file with a file type of EXEC. It contains a series of commands and statements that are executed when the file name of the EXEC file is typed.
You can use EXECs to stack SQL statements and ISQL commands before you begin your ISQL session. As soon as you start ISQL, this information is read from the stack and executed. By using EXEC processing in this way, you can predefine all default settings to be used during your display session, establish PF key values, and designate print specifications. This method can also be used to automatically start procedures tailored to the needs of specific users.
The EXEC examples in this chapter do not include the CONNECT statement as part of the stacked set; it is assumed that you are working with your own sample tables. If you must gain access to another user ID to use ISQL, use a CONNECT statement as the first stacked statement. See the DB2 Server for VSE & VM SQL Reference manual for additional information on the CONNECT statement.
For information on creating CMS EXEC files, see the VM/ESA: CMS User's Guide manual. For information on the REXX language, see the VM/ESA REXX/VM User's Guide and the VM/ESA REXX/VM Reference manuals.
In the CMS environment, you can run EXECs that stack SQL statements and ISQL commands to be processed automatically as soon as ISQL begins. If the EXEC also starts ISQL, the user of the EXEC need not know anything about ISQL.
You can write specialized EXECs for your own use or for other users. Figure 54 shows an EXEC that is written in the REXX language:
Figure 54. Example of Stacked ISQL Commands in a REXX EXEC
/* this exec develops a report for project mean numbers */ Queue 'SELECT * FROM PROJ_ACT ORDER BY PROJNO' Queue 'FORMAT GROUP PROJNO' Queue 'FORMAT SUBTOTAL ACSTAFF' Queue "FORMAT TTITLE 'PROJECT MEAN EMPLOYEES'" Queue 'PRINT' Queue 'END' Queue 'EXIT' 'EXEC ISQL' Exit /* end of exec */ |
To create a prompt environment for a casual SQL user, you can write an EXEC like the one shown in Figure 55. The user would run the EXEC from the CMS environment without starting ISQL.
Figure 55. Example of an EXEC That Prompts the User
/* This exec prompts for a table to be viewed or printed */ Trace Value 'OFF' Say 'WHICH TABLE WOULD YOU LIKE TO SEE?' Parse Upper Pull tablename Say 'WOULD YOU LIKE TO HAVE THIS TABLE PRINTED? (Y OR N)' Parse Upper Pull printoption Say 'YOU MUST ENTER END TO LEAVE THE DISPLAY OF THE TABLE' If printoption = 'Y' then Do Queue 'SELECT * FROM' tablename Queue 'DISPLAY' Queue 'PRINT' Queue 'END' End Else If printoption = 'N' then Do Queue 'SELECT * FROM' tablename Queue 'DISPLAY' Queue 'END' End If printoption = 'Y' | printoption = 'N' then Do Queue 'EXIT' 'EXEC ISQL' End Exit /* End of exec */ |
When you run this EXEC, the following user/system dialog occurs. If the EXEC has a file name of MYTABLES, you type:
mytables
The system prompts you with the message WHICH TABLE WOULD YOU LIKE TO SEE?. You type:
department
The system prompts you with the message WOULD YOU LIKE TO HAVE THIS TABLE PRINTED? (Y OR N). You type:
y
The system prompts you with the message YOU MUST ENTER END TO LEAVE THE DISPLAY OF THE TABLE.
The ISQL startup messages immediately follow. Then the partial display in Figure 56 should appear.
Figure 56. Query Result from an EXEC
+--------------------------------------------------------------------------------+ | DEPTNO DEPTNAME MGRNO ADMRDEPT | | ------ -------------------- ------ -------- | | A00 SPIFFY COMPUTER SERV< 000010 A00 | | B01 PLANNING 000020 A00 | | C01 INFORMATION CENTER 000030 A00 | | D01 DEVELOPMENT CENTER ? A00 | | D11 MANUFACTURING SYSTEM< 000060 D01 | | D21 ADMINISTRATION SYSTE< 000070 D01 | | E01 SUPPORT SERVICES 000050 A00 | | E11 OPERATIONS 000090 E01 | | E21 SOFTWARE SUPPORT 000100 E01 | | * End of Result *** 9 Rows Displayed ***Cost Estimate is 1*********************| +--------------------------------------------------------------------------------+
You see the ISQL signoff messages when you end the display.