Interface Mechanisms
There are four basic interface mechanisms that can be used with DB2:
- Language extension (embedded SQL) -- you can embed static or dynamic SQL statements amongst the program statements of a high-level language such as ADA, APL2, Assembler, BASIC, C, C++, COBOL, FORTRAN, Java, PL/I, PROLOG, REXX, or Smalltalk.
- APIs (application programming interfaces) -- DB2 has an API that uses function calls to pass dynamic SQL statements as function arguments.
- Interactive tools -- the user can issue an SQL statement directly from a command console or application tools. And then after the DB2 executes the SQL statement, the result will be return to the command console or application tools from which user issued the SQL statement.
- Stored procedures -- business logic can also be stored and managed by DB2 as stored procedures. These stored procedures can be used to access tables stored in DB2.
We will next look at each of these mechanisms in turn.
Static SQL -- C Example
...
EXEC SQL INCLUDE SQLCA;
...
EXEC SQL BEGIN DECLARE SECTION;
short int hvc1, hvc2;
EXEC SQL END DECLARE SECTION;
...
hvc2 = input_variable;
EXEC SQL SELECT MAX(C1) INTO :hvc1 FROM T1 WHERE C2>:hvc2;
...
- This is pretty similar to the COBOL example.
- Recall when the SELECT statement is pre-compiled into a CALL to DSNHLI, a place holder for the "input host variable" is reserved as part of the call arguments.
- This example shows that the output of the selected column function MAX(C1) is to be returned in the "output host variable" :hvc1 area.
- In C, the host variables referenced by the SQL statements must be declared in between "EXEC SQL BEGIN DECLARE SECTION ;: and "EXEC SQL END DECLARE SECTION;".
A Dynamic SQL Example
DATA DIVISION.
...
01 STMT 49 STMT-LENGTH PIC S9(4) COMP VALUE +255.
49 STMT-TEXT PIC X(255).
01 VDEPNO PIC X(6).
...
PROCEDURE DIVISION.
...
Move "DELETE FROM EMP WHERE DEPTNO = (CAST ? AS PICX(6))" TO STMT
...
EXEC SQL PREPARE S1 FROM :STMT END-EXEC
...
MOVE 'A00' TO VDEPNO
...
EXEC SQL EXECUTE S1 USING :VDEPNO END-EXEC .
...
- This dynamic SQL example uses PREPARE and EXECUTE to issue SQL statements.
- This form of dynamic SQL cannot be used for the cursor SELECT statement.
- For the latter, you should prepare the "DECLARE CURSOR" statement, and then issue a "OPEN CURSOR" followed by the "FETCH" repetitively until data is exhausted, and finally issue a "CLOSE CURSOR" statement.
- When an embedded dynamic SQL program is pre-compiled and bound, a dummy section is generated for each set of dynamic SQL statements.
- In the example in this slide, the "PREPARE" and "EXECUTE" is bound to the same dummy section. They are tied together by "S1".
- Notice you put a "?" in the position of the dynamic SQL statement where a host variable value will be substituted at the execution time.
- The "?" is called a parameter marker.
Dynamic SQL Statements
There are four forms of statements for embedded dynamic SQL: PREPARE, DESCRIBE, EXECUTE, and EXECUTE IMMEDIATE. In addition, we have the auxiliary forms: "OPEN", "FETCH" and "CLOSE" statements to support the cursor select statement.
- PREPARE -- prepares but does not execute a SQL statement. This statement can later be referred to using stmt-name.
PREPARE stmt-name (INTO descriptor) FROM host-var
PREPARE s1 FROM :mystmt
PREPARE t1 INTO :mysqlda FROM :myquery
- DESCRIBE -- gets a description of the data items in the result set of a query that has been prepared and puts
it into a host language variable. A descriptor is essentially a struct with slots to hold each column of data that might come in.
DESCRIBE stmt-name INTO descriptor
DESCRIBE t1 INTO :mysqlda
- EXECUTE -- executes a previously prepared SQL statement, substituting the values in the host variable list or descriptor for the parameter markers in the prepared statement. The host variable list or the descriptor must specify one value for each parameter marker.
EXECUTE stmt-name USING host-var1, host-var2...
or
EXECUTE stmt-name USING DESCRIPTOR name
- EXECUTE IMMEDIATE -- prepares an SQL statement and execute it immediately, all in one statement. The host variable must contain a valid SQL statement that contains no parameter marker.
EXECUTE IMMEDIATE host-var
EXECUTE IMMEDIATE :mystmt
