A variety of ways to interface with DB2 are available, such as with programs using TSO, CAF, CICS, and so on. Two of the most common interfaces provided with the DB2 product are DB2 Interactive (DB2I) and Sequential Processing Using File Input (SPUFI).
The interactive program DB2I can be used to run application programs and to perform many DB2 operations by entering values on panels. DB2I runs under TSO, using ISPF (Interactive System Productivity Facility) services. To use DB2I, follow the local procedures for logging on to TSO, and enter ISPF. Each operation is controlled by entering the parameters that describe it on the panels provided. DB2 also provides help panels to
The following tasks can be performed using DB2I:
An example of the DB2I menu is shown in Figure 2-5.
Figure 2-5. DB2I menu
DB2I PRIMARY OPTION MENU ===> Select one of the following DB2 functions and press ENTER. 1 SPUFI (Process SQL Statements) 2 DCLGEN (Generate SQL and source language declarations) 3 PROGRAM PREPARATION (Prepare a DB2 application program to run) 4 PRECOMPILE (Invoke DB2 precompiler) 5 BIND/REBIND/FREE (BIND, REBIND, or FREE plans or packages) 6 RUN (RUN an SQL program) 7 DB2 COMMANDS (Issue DB2 commands) 8 UTILTIES (Invoke DB2 utilities) D DB2I DEFAULTS (Set global parameters) X EXIT (Leave DB2I) Press : END to exit HELP for more information
Figure 2-6 shows the defaults for DB2I. These defaults can be changed. Some of the parameters that can be changed are the DB2 subsystem on which to run the requests, application language of choice, number or rows returned to the session, and what string delimiter to use.
Figure 2-6. DB2I defaults screen
DB2I DEFAULTS PANEL 1 Command ===> _ Change defaults as desired: 1 DB2 NAME.............. ===> DSN (Subsystem Identifier) 2 DB2 CONNECTION RETRIES ===> 0 (How many retries for DB2 connection) 3 APPLICATION LANGUAGE... ===> COB2 (ASM, C, CPP, COBOL, COB2, IBMCOB, FORTRAN, PLI) 4 LINES/PAGE OF LISTING. ===> 60 (A number from 5 to 999) 5 MESSAGE LEVEL......... ===> I (Information, Warning, Error, Severe) 6 SQL STRING DELIMITER.. ===> DEFAULT (DEFAULT or ' or ") 7 DECIMAL POINT......... ===> . (. or , ) 8 STOP IF RETURN CODE >= ===> 8 (Lowest terminating return code) 9 NUMBER OF ROWS........ ===> 20 (For ISPF Tables) 10 CHANGE HELP BOOK NAMES? ===> NO (YES to change HELP data set names) Press : ENTER to process END to cancel HELP for more information
Sequential Processing Using File Input
SPUFI is a DB2I menu option that allows you to execute SQL statements interactively with DB2. The options on the main menu are as follows.
SPUFI allows you to have multiple queries in one input data set member for execution. The queries can be stacked by using a ; between the statements. Without an explicit COMMIT statement, all the SQL statements are considered a unit of work.
After the SQL has been executed, SPUFI displays both the SQLCODE and the SQLSTATE. Figure 2-7 shows the main screen for SPUFI.
Figure 2-7. SPUFI panel
SPUFI SSID: DSN ===> Enter the input data set name: (Can be sequential or partitioned) 1 DATA SET NAME ... ===> ISPF.SQL(RECURS1) 2 VOLUME SERIAL ... ===> (Enter if not cataloged) 3 DATA SET PASSWORD ===> (Enter if password protected) Enter the output data set name: (Must be a sequential data set) 4 DATA SET NAME ... ===> OUTPUT.DATA Specify processing options: 5 CHANGE DEFAULTS ===> YES (Y/N - Display SPUFI defaults panel?) 6 EDIT INPUT ...... ===> YES (Y/N - Enter SQL statements?) 7 EXECUTE ......... ===> YES (Y/N - Execute SQL statements?) 8 AUTOCOMMIT ...... ===> YES (Y/N - Commit after successful run?) 9 BROWSE OUTPUT ... ===> YES (Y/N - Browse output data set?) For remote SQL processing: 10 CONNECT LOCATION ===> PRESS: ENTER to process END to exit HELP for more information