DB2 UDB FOR Z/OS
Overview
DB2 Application Programming
Using Embedded SQL
Program Structure
Overview
Why we need Embedded SQL ?.
Question
Where to Embedded SQL Statement ?.
Question
Host Languages
COBOL
PL/1
C Assembler FORTRAN
Supporting Embedded SQL
Use delimiters to identify SQL statements in the application
Use host variables to provide values on the WHERE clause or receive values from SELECT statements
Use appropriate techniques to process null data
Use communications from DB2 to determine success or failure of SQL statements
Embedded SQL Types
Overview
Embedded SQL Types
Static SQL
SQL Statements are presented in Program with same
function and same table
Dynamic SQL
SQL Statements are called at Run Time , Every time new
Function , New Table
Embedded SQL
Delimited all SQL statements
Declare a Communication Area
Describe Host Variables Code SQL statements to access DB2 data Handle Exceptional Conditions
SQL Delimiters
Used by PRECOMPILER to identify SQL statements to be translated.
Must indicate beginning and end of each embedded SQL statement.
The EXEC SQL must not be coded before column 12.
COBOL comments are allowed any where within the delimiters
Host Variables and Declaration
Overview
Host Variables
OPTIONAL, used to allow setting a value before statement is executed Host language label in SQL statement preceded by a colon Host variable must match column data type
Not usable for DB2 names (Tablename, columnname, . . .)
Host Variables Example
MOVE 2120 TO WS-EMPNO
EXEC SQL
SELECT EMPNO, LASTNAME,SEX
INTO :WS-EMPNO, :WS-LAST,:WS-SEX FROM EMPLOYEE
WHERE EMPNO= : WS-EMPNO
END-EXEC
Host Variables - Insert - COBOL Program
Host Variables - Update - COBOL Program
Host Variables - To Receive a Value
Receiving a Value - COBOL Program
Declaring Host Variables Language Definitions
Declaring COBOL Host Variables
All COBOL host variables must be explicitly declared in the DATA DIVISION.
A colon ( : ) must precede all host variables in an SQL statement The names of the host variables must be unique within the program.
Host variables, other than indicator variables, cannot be defined as arrays.
An OCCURS clause must only be specified when defining an indicator structure.
SQL Data Types Mapped to Typical COBOL Declarations
COBOL and SQL data types: Datetime
DATE TIME
PICX(10) PICX(08)
- Format specified by an option - Format specified by an option
TIMESTAMP PIC X (26) String representation of timestamp has the format: yyy-mm-dd-hh.mm.ss.nnnnnn
Host Structure for COBOL Program
MOVE 2120 TO WS-EMP.
Working-Storage Section. 01 WS-EMP-STRUCT.
EXEC SQL SELECT EMPNO, LAST, SEX INTO : WS-EMP-STRUCT FROM EMPLOYEE WHERE EMPNO = :WS-EMP
05 WS-EMPNO PIC S9(9) USAGE COMP. 05 WS-LAST PIC X (30). 05 WS-SEX PIC X(01).
END-EXEC.
DCLGEN (Generating Host Variable )
Example COBOL DCLGEN output:
Example COBOL DCLGEN output:
How to include Host variable Declaration and SQL Communication Area predefined Declaration in Program
Overview
INCLUDE
EXEC SQL
INCLUDE SQLCA | SQLCA |member-name
END-EXEC
Member-name
Member of the partitioned data set . The member can contain any COBOL variable declarations, source statements and any SQL statements other than an INCLUDE statement.
SQLCA (SQL Communication Area)
Overview
SQLCA
SQLCODE and SQLSTATE
SQLSTATE is set by the database manager after execution of each SQL statement. Application programs can check the success of the execution of SQL statements by testing SQLSTATE instead of SQLCODE.
SQLCA ( SQLCODE & SQLSTATE) Examples
What is SQLCA Warring and why we require in Embedded SQL program
Question
SQLCA Warnings
Example Program
Overview
SPUFI screen to request DCLGEN:
Embedded SQL Program to Insert data into Employee Table
Overview
DCLGEN-Input
DCLGEN- Output
DCLGEN- Output
DCLGEN - Output
EMBEDED SQL INSERT-COBOL Program
EMBEDED SQL INSERT-COBOL Program
EMBEDED SQL INSERT-COBOL Program
DB2 PRE-COMPILATION
BIND JCL
BIND JCL
Program Preparation
What This Unit Is About
How the Embedded SQL Program is going to be Processed by
PRECOMPILER
COMPILER BINDER PLAN and PACKAGE
DB2 Program Preparation
Four Major steps for Program Preparation
PRECOMPILE COMPILE
LINK-EDIT
BIND & RUN
What does DB2 Pre-compile does when you submit a Embedded SQL Program as Source ?.
Question
DB2 PRE-COMPILE Process
PRE-COMPILE Process
Embedded SQL Program has Application Code SQL Statements Before the program can be
compiled by the appropriate source code language compiler.
EXEC SQL statements
embedded in the program must be removed and replaced by
CALL Statements in COBOL
DB2 Pre-compile Process
PRECOMPILER prepares the source program for compilation by replacing EXEC SQL by a CALL and commenting out the EXEC SQL statement.
Verifies the SQL syntax.
PRECOMPILER process will include any files included with an
EXEC SQL INCLUDE
statement
Including files produced
by DCLGEN.
What is the Purpose of Database Request Module (DBRM) in DB2 Program Preparation ?.
Question
DBRM (Database Request Module )
COBOL Compile and LinkEdit Process
Overview
Compile and Link
After DB2 PRE-COMPILATION the modified Cobol program with CALL statements ( instead of EXEC SQL) is now ready to be compiled and link-edited. LINK-EDIT process will have to include the necessary modules for the call to work properly. Modified Embedded Cobol source file is
Compiled and linked
normally.
Produce an executable file
or load module.
Compile and Link
After DB2 PRECOMPILATION the modified Cobol program with CALL statements ( instead of EXEC SQL) is now ready to be compiled and link-edited. LINK-EDIT process will have to include the necessary modules for the call to work properly. Modified Embedded Cobol source file is
Compiled and linked
normally.
Produce an
executable file or load module .
Program Preparation : Bind Process
Overview
Bind Process
When the bind file or DBRM is bound to the database, a package is created in the database. package contains instructions about how to execute the SQL statements that were included in the source code.
Bind Process
Run
Run
Run Process
Accepts Input
Executes Load Module Retrieves Information from DB2 Package when program call SQL statements
Bind Plan and Package
Embedded SQL Program to Update data into Employee Table
Overview
EMBEDED SQL Update-COBOL Program
EMBEDED SQL Update-COBOL Program
EMBEDED SQL Update-COBOL Program
PRE-COMPILE
BIND JCL
OUTPUT
Embedded SQL Program to Delete data into Employee Table
EMBEDED SQL DELETE-COBOL Program
EMBEDED SQL DELETE-COBOL Program
EMBEDED SQL DELETE-COBOL Program
PRE-COMPILE
BIND JCL
OUTPUT
Embedded SQL Program to SELECT data FROM Employee Table
Overview
EMBEDED SQL SELECT-COBOL Program
EMBEDED SQL SELECT-COBOL Program
EMBEDED SQL SELECT-COBOL Program
PRE-COMPILE
BIND JCL
BINDJCL
BINDJCL
OUTPUT
OUTPUT
Special Registers
EXEC SQL SET :WS-TS = CURRENT TIMESTAMP END-EXEC. EXEC SQL SET : WS-USER = USER END-EXEC.
EXEC SQL SET CURRENT SQLID = : WS - SQLID END - EXEC.
Embedded SQL Program to handle NULL values (INDICATOR Variables )
Overview
Indicator Variables
Indicator variables are small integers that are used to :
Indicate whether the values of the associated host variables are NULL Verify that the value of a retrieved character string has not been truncated
Insert and update NULL values from host variables into columns.
Indicator Variables Example - COBOL Program
INDICATOR VARIABLES
A variable of SMALLINT data type associated with a host variable Must be declared. Is preceded by a colon and directly follows its associated host variables, when used in an SQL statement. EXEC SQL SELECT INTO FROM WHERE END EXEC.
HIREDATE :HV HIREDATE : IV HIREDATE EMPLOYEE EMPNO = :HV EMPNO
Note *
IV HIREDATE is the indicator variable associated with the host variable HV - HIREDATE
INDICATOR VARIABLES
DB2 returns one of the following values to an indicator variable:
if the vale returned to the associated host variable by DB2
is NOT NULL, not truncated
-1
is NULL
>0 is a truncated character string; the value in the indicator variable is the length of the character string before truncation.
Receiving NULL Values
MOVE ZEROES EXEC SQL
TO WS-LASTNAME-IND.
SELECT LASTNAME
INTO :WS-LAST :WS-LASTNAME-IND
FROM EMPLOYEE WHERE EMPNO = :WS-EMPNO END-EXEC. IF WS-LASTNAME-IND < ZEROES ..... END-IF.
Updating Columns with NULL
MOVE
MOVE
2120
SPACES
TO WS-EMPNO.
TO WS-PHONE-NO. TO WS-PHONE-NO-IND
MOVE - 1
EXEC SQL UPDATE EMPLOYEE SET PHONE = :WS-PHONE-NO :WS-PHONE-NO-IND WHERE EMPNO = :WS-EMPNO END-EXEC.
Inserting NULL values
MOVE -1 TO WS-PHONE-NO-IND.
EXEC SQL INSERT INTO EMPLOYEE (EMPNO, SEX, AGE,PHONE) VALUES ( :WS-EMPNO , :WS-SEX, :WS-AGE, :WS-PHONE-NO :WS-PHONE-NO-IND) END-EXEC.
Indicator Variables
Updating Valid Values
MOVE 2120 MOVE 616161 TO WS-EMPNO TO WS-PHONE-NO
MOVE ZEROES
..... UPDATE-PARA.
TO WS-PHONE-NO-IND.
PERFORM UPDATE-PARA.
EXEC SQL
UPDATE EMPLOYEE SET PHONE = :WS-PHONE-NO :WS-PHONE-NO-IND WHERE EMPNO = :WS-EMPNO
END-EXEC.
EXAMPLE Embedded SQL Program to handle NULL values (INDICATOR Variables )
Overview
EMBEDED SQL Handling NULL Values
EMBEDED SQL Handling NULL Values
EMBEDED SQL Handling NULL Values
EMBEDED SQL Handling NULL Values
PRE-COMPILE
BIND JCL
output
OUTPUT
SQL CODE
EXEC SQL SELECT EMPNO, LASTNAME, SEX INTO :WS-EMPNO, :WS-LAST, :WS-SEX
FROM EMPLOYEE WHERE EMPNO= :WS-EMPNO END-EXEC. EVALUATE SQLCODE WHEN 0 DISPLAY LAST NAME IS WS-LAST WHEN 100 DISPLAY WS-EMPNO DOES NOT EXIST END-EVALUATE
Processing Multiple Rows
Processing Multiple Rows
Structured Programming - Sequential File Read
SELECT with FETCH - COBOL Program
Processing Multiple Rows
Multi-row SELECT TABLE
RESULT TABLE
Retrieve One row at a time
DECLARE CURSOR
EXEC SQL DECLARE CUREMP01 CURSOR FOR SELECT EMPNO, SEX, AGE, NAME FROM EMPLOYEE WHERE JOIN_DATE > : WS-JOIN-DATE END-EXEC.
OPEN
EXEC SQL OPEN CURSOR-NAME END EXEC.
101 02/01/1996 102 11/01/1995 103 15/03/1995 104 15/02/1996
EXEC SQL OPEN CUR END-EXEC
105 15/02/1996 101 02/01/1996 102 05/04/1996
FETCH
Syntax : EXEC SQL FETCH CURSOR-NAME INTO :host-variable1, :host-variable2, .. END-EXEC.
Example : EXEC SQL FETCH CUREMP01 INTO :WS-EMPNO, :WS-SEX, :WS-AGE END-EXEC.
End - of - Data Processing
RESULT TABLE
105 101 102 15/02/1996 02/01/1996 04/05/1996
WS-EMPNO WS-JOIN-DATE
SQLCODE
FETCH
105 101 102 102
02/05/1996 01/02/1996 04/05/1996 04/05/1996
0 0 0 100
Handling End-of-data
MAIN-PARA.
MOVE 01-01-1996 TO WS-JOIN-DATE.
PERFORM OPEN-PARA
PERFORM FETCH-PARA
PERFORM UNTIL SQLCODE = 100
PERFORM PROCESS-ROW PERFORM END-PERFORM
FETCH-PARA
....
CLOSE
Syntax : EXEC SQL
CLOSE cursor-name
END-EXEC. Example : EXEC SQL
CLOSE CUREMP01
END-EXEC.
EMBEDDED SQL PROGRAM -CURSOR
EMBEDDED SQL PROGRAM -CURSOR
EMBEDDED SQL PROGRAM -CURSOR
EMBEDDED SQL PROGRAM -CURSOR
PRE-COMPILE
BIND
OUTPUT
Output
Output
OUTPUT
FOR UPDATE OF
EXEC SQL
DECLARE CUREMP01 CURSOR FOR
SELECT EMPNO, AGE FROM EMPLOYEE FOR UPDATE OF SALARY END-EXEC.
UPDATE WHERE CURRENT OF
PERFORM FETCH-PARA PERFORM UNTIL SQLCODE =100 .. EXEC SQL UPDATE EMPLOYEE SET SALARY = :WS-SALARY
WHERE CURRENT OF CUREMPO1
END-EXEC PERFORM FETCH-PARA END-PERFORM
DELETE WHERE CURRENT OF
PERFORM OPEN-PARA
PERFORM FETCH-PARA. PERFORM UNTIL SQLCODE =100 EXEC SQL DELETE FROM EMPLOYEE WHERE CURRENT OF CUREMP01 END-EXEC PERFORM FETCH-PARA
END-PERFORM.
DECLARE WITH HOLD
EXEC SQL DECLARE cursor-name CURSOR WITH HOLD FOR SELECT column-name-list FROM table-name WHERE search-condition FOR UPDATE OF column-name END-EXEC.
Dynamic SQL
Statement: Acquired during execution,and function can vary and can be done to different tables and columns
Bind: One single statement
At statement execution Access strategy not saved..
Static SQL vs. Dynamic SQL
Statement: Static : Object and Action known
Dynamic : Object or Action not known Bind: Static Dynamic : is bound once : is bound every time.
Coding Dynamic SQL
Translate the input data into a SQL statement Prepare the SQL statement to execute Execute the SQL statement Process the information returned Handle SQL return codes
EXECUTE IMMEDIATE
Prepare and Execute the SQL statement present in the COBOL host variable WS-STMT MOVE DELETE FROM EMPLOYEE TO WS-STMT-TEXT MOVE 50 EXEC SQL EXECUTE IMMEDIATE :WS-STMT END-EXEC. TO WS-STMT-LEN
Parameters Markers
Dynamic SQL statements cannot use Host Variables
Parameter Markers are used to substitute a parameter
DELETE FROM EMPLOYEE WHERE EMPNO = ?
Prepare and Execute
PREPARE once EXEC SQL PREPARE S1 FROM :WS-STMT EXEC-EXEC. EXECUTE many times EXEC SQL
PREPARE S1 FROM :WS-EMPNO
EXEC-EXEC.
DB2 LOCKS
Overview
DB2 LOCKS
DB2 LOCKS * * *
Are managed by IMS Resource Lock Manager, IRLM Are used to control Concurrency and guarantee the integrity of each users data Prevent access to uncommitted data
CONCURRENCY CONTROL
Concurrency is a situation wherein DB2 allows more than one application program to access same data at the same time.
Concurrency needs to be controlled due to many undesirable effects such as i LOST UPDATES (or Double Updating),
ii
iii
ACCESS TO UNCOMMITTED DATA
UNREPEATABLE READS
DB2 makes up use of locks to control Concurrency
Concurrency Control Problem
Lost Updates
Without Concurrency control two application programs X and Y
Both read the same Row from the table Both supply new values for one of its columns based on what they read.
PROBLEM:
If X updates the row with its value and then Y updates the same row, Xs updates are lost
Access To Uncommitted Data
Without concurrency control
Application program X might update a value in the table
Application program Y might read that value before it was committed
PROBLEM
If Xs value is not later committed but backed out, then Ys calculations are based on uncommitted and presumably invalid data
Concurrency control Problem
Unrepeatable Reads
Without concurrency control
An application program X reads a row form the table and goes on to process other SQL statements. X again reads the row it read the first time and must find the same values in it.
PROBLEM
Without Concurrency control, application program Y could have changed the same row between two reads of program X.
FACTORS CONTROLLING LOCKING
Overview
FACTORS CONTROLLING LOCKING
The Object Of A Lock
Is the resource being locked by DB2
Can be databases, table spaces, tables, tablespace page, index, indexspace, page/subpage and plans
The Size Of A Lock
Determines how large a portion of the resource is being locked The LOCKSIZE parameter of CREATE TABLESPACE statement defines the locking protocol DB2 being locking levels are:
Tablespace locking
Page locking
The Duration Of A Lock
Determines the length of time the lock is held Varies according to when the lock is acquired and when it is released
FACTORS CONTROLLING LOCKING
Duration Of Tablespace Locks
The duration of any tablespace lock held by an application program can be controlled by means of ACQUIRE and RELEASE parameters of BIND ACQUIRE specifies when resources should be allocated, I.e., when the locks should be acquired
RELEASE specifies when resources should be deallocated, I.e., when the locks should e freed or released. Both the above parameters have two sub-parameters:
For ACQUIRE
USE ALLOCATE
For RELEASE
COMMIT DEALLOCATE
Duration Of Page Locks
ISOLATION parameters controls the PAGE level locks
Bind Isolation Parameters
Influence the initial lock chosen by DB2 especially its duration Ensure data consistency, an application program can be bound with any of the two values Page lock is held only while the cursor is positioned on at page. When the cursor moves to another page, the lock is leased. When a page is locked concurrent application programs cannot update or delete a row of locked page If an application program updates or deletes data, the lock is held until the data is committed Applies only to data is read. All changed data remains locked until COMMIT or ROLLBACK
Cursor Stability
Duration Of Page Locks
Repeatable Read (RR)
Page lock is held until the next commit point.
With this option if the application program returns to the same page and reads the same row again, the values would remain the same It means that pages containing all the rows retrieved as well as the pages containing the current row are locked. Concurrent application programs cannot insert, update,or delete a row in the locked page until the unit of recovery completes.
Modes of Locks for Table or Tablespace
S(SHARE)
Lock owner and concurrent application programs may only.
Lock owner can read for updating it; TABLE SHARING Concurrent application programs may only read data. Lock owner can only read or change data in the table or tablespace; No other concurrent application programs can is allowed access in the Tablespace; Lock owner can only read data in the table or tablespace; Concurrent application programs can only read data in the locked page but can read and update data of other pages. the lock owner can read and update in the table ir tablespace
U(UPDATE)
X(EXCLUSIVE)
TABLE EXCLUSIVE IS(INDENT SHARE)
IX(INDENT EXCLUSIVE)
PAGE EXCLUSIVE : Concurrent application programs can only read and update data of other pages.
