This chapter is from the book
This chapter is from the book
This chapter is from the book
Interfaces
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).
DB2 Interactive
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
-
Explain how to use each operation
-
Provide the syntax for and examples of DSN subcommands, DB2 operator commands, and DB2 utility control statements
The following tasks can be performed using DB2I:
-
Execute SQL statements using SPUFI
-
Perform a DCLGEN (Declarations Generator)
-
Prepare a DB2 program
-
Invoke the DB2 precompiler
-
Execute BIND/REBIND/FREE commands
-
RUN an SQL program
-
Issue DB2 commands
-
Invoke DB2 utilities
-
Change DB2I defaults
An example of the DB2I menu is shown in .
Example 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
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.
Example 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.
-
Input data set name is the data set that contains one or more SQL statements that are to be executed. The data set needs to exist before SPUFI can be used.
-
Output data set name is the data set that will receive the output from the SQL statement that is executed. This data set does not have to exist before execution.
-
Change defaults allows for changing control values and characteristics of the output data set and the format of SPUFI settings.
-
Edit input a llows for editing the SQL statements.
-
Execute indicates whether to execute the SQL statement in the input file.
-
Autocommit tells DB2 whether to make the changes to the data permanent.
-
Browse output allows for browsing of the query output.
-
Connect location specifies the name of the application server to receive the queries.
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. shows the main screen for SPUFI.
Example 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