db2. 2 copyright © 2005, infosys technologies ltd er/corp/crs/db01/003 version no:2.0a session plan...
TRANSCRIPT
![Page 1: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/1.jpg)
DB2
![Page 2: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/2.jpg)
2Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Session Plan
• SPUFI Hands On
• Introduction to Embedded SQL
• DCLGEN Hands On
• SQLCA Copy Book and its Use
• Single Row Manipulation
• Multiple Row Manipulation
• Cursors
![Page 3: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/3.jpg)
3Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 4: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/4.jpg)
4Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 5: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/5.jpg)
5Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 6: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/6.jpg)
6Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 7: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/7.jpg)
7Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 8: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/8.jpg)
8Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 9: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/9.jpg)
9Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
SPUFI
SQL Processor Using File Input
![Page 10: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/10.jpg)
10Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 11: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/11.jpg)
11Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 12: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/12.jpg)
12Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 13: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/13.jpg)
13Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 14: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/14.jpg)
14Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 15: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/15.jpg)
15Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 16: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/16.jpg)
16Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 17: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/17.jpg)
17Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 18: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/18.jpg)
18Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 19: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/19.jpg)
19Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
EMBEDDEDSQL
![Page 20: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/20.jpg)
20Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Embedded SQL
• Embedded SQL refers to the use of standard SQL statements within a
high-level language program such as COBOL, PL1, C, PASCAL, etc,.
• The high level language is called Host language
• Embedded SQL statements are preprocessed by SQL Pre processor
before the application program is compiled
![Page 21: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/21.jpg)
21Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Embedded SQL
• Embedded SQL syntax is almost same as the SQL syntax used in interactive
mode.
• The output of a query is directed to a pre-defined set of variables instead of the
terminal.
• These variables are defined in the host language and are referred to as the
host variables.
• An additional INTO clause is placed after the SQL SELECT statement.
![Page 22: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/22.jpg)
22Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Types of SQL
1. Static SQL: The application programmer knows in advance the SQL statement
completely.
2. Dynamic SQL: The application programmer is unaware of the SQL statement in
advance. This requires dynamic compilation and binding.
![Page 23: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/23.jpg)
23Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Host Variables(1 of 2)
• Host variables are just like any other variable of the high level language
• They are referred as host variables because they are used for receiving data
from the table or inserting data to the table.
• One must declare host variables for all values that are to be passed between
the application program and DB2.
• The host variables may appear anywhere in the WORKING-STORAGE
SECTION.
• The data types of the DB2 columns and corresponding host variables must be
compatible.
![Page 24: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/24.jpg)
24Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Host variables (2 of 2)
• The host variables can not be group items, the only exception to this rule is the
variable corresponding to VARCHAR.
• The host variables may be grouped together in a host structure.
• The host variables can be Redefined
• The host variable cannot be Renamed.
![Page 25: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/25.jpg)
25Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Declaring Host Variables
• Need to declare in the working-storage section of your program
EXEC SQL BEGIN DECLARE SECTION
END-EXEC.
01 EMPLOYEE-REC.
03 EMP-NO PIC S9(4) COMP.
03 EMP-NAME PIC X(15).
03 EMP-ADDRESS.
49 EMP-ADDRESS-LEN PIC S9(4) COMP.
49 EMP-ADDRESS-TEXT PIC X(25).
EXEC SQL END DECLARE SECTION
END-EXEC.
![Page 26: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/26.jpg)
26Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
DCLGEN
Generating copybook of host variables
![Page 27: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/27.jpg)
27Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 28: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/28.jpg)
28Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 29: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/29.jpg)
29Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 30: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/30.jpg)
30Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 31: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/31.jpg)
31Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 32: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/32.jpg)
32Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 33: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/33.jpg)
33Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 34: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/34.jpg)
34Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 35: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/35.jpg)
35Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 36: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/36.jpg)
36Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 37: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/37.jpg)
37Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
![Page 38: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/38.jpg)
38Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Using DCLGEN copybook
EXEC SQL INCLUDE STUD627END-EXEC
![Page 39: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/39.jpg)
39Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
SQLCA
• This is the communication area (a copybook containing some variables)
through which DB2 passes the feedback of SQL execution to the program
• In the working-storage section use
WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC.
![Page 40: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/40.jpg)
40Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
SQLCA Copy Book (1 Of 3)
01 SQLCA. 03 SQLCAID PIC X(8). 03 SQLCABC PIC S9(9) COMP. 03 SQLCODE PIC S9(9) COMP VALUE 0. 03 SQLERRM. 49 SQLERRML PIC S9(4) COMP. 49 SQLERRMC PIC X(70). 03 SQLERRP PIC X(8). 03 SQLERRD OCCURS 6 PIC S9(9) COMP. 03 SQLWARN. 05 SQLWARN0 PIC X. 05 SQLWARN1 PIC X. 05 SQLWARN2 PIC X. 05 SQLWARN3 PIC X. 05 SQLWARN4 PIC X. 05 SQLWARN5 PIC X. 05 SQLWARN6 PIC X. 05 SQLWARN7 PIC X. 03 SQLSTATE PIC X(5)
![Page 41: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/41.jpg)
41Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
03 SQLCAID For Reading dumps.03 SQLCABC Length of SQLCA (136 bytes).03 SQLCODE = 0 (Successful)
= +ve (Exceptional condition)
= -ve (Failure)03 SQLERRM. 49 SQLERRML Error Message Length. 49 SQLERRMC Error Message Text.03 SQLERRP Info about internal Error.03 SQLERRD(1) Internal Error Code.
03 SQLERRD(2) Internal Error Code.
03 SQLERRD(3) Number of Rows affected by
INSERT, UPDATE and DELETE.
03 SQLERRD(4) Estimate of resources needed
by dynamic SQL statement.
03 SQLERRD(5) Info about dynamic SQL.
03 SQLERRD(6) Internal Error Code.
SQLCA Copy Book (2 of 3)
![Page 42: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/42.jpg)
42Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
05 SQLWARN0 ‘W’ if any other SQLWARNx fields are
set to ‘W’.
05 SQLWARN1 ‘W’ indicates truncation of character
strings.
05 SQLWARN2 ‘W’ indicates null values were ignored.
Ex: AVG, COUNT.
05 SQLWARN3 ‘W’ indicates more columns than host variables.
05 SQLWARN4 ‘W’ indicates no WHERE clause for
UPDATE or DELETE.
05 SQLWARN5 ‘W’ indicates SQL/DS statement.
05 SQLWARN6 ‘W’ for adjustment of DATE.
05 SQLWARN7 Reserved for Future use.
SQLCA Copy Book (3 of 3)
![Page 43: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/43.jpg)
43Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Programming guidelines (1 of 2)
• Every SQL statement must be coded between columns 12 and 72.
• Every SQL statement must be delimited between EXEC SQL and END-EXEC.
• All the tables that are used in a program are to be declared in the WORKING-
STORAGE SECTION. This can be done using INCLUDE statement
• All SQL statements other than INCLUDE and DECLARE TABLE must
appear in PROCEDURE DIVISION.
• Anything on a line within an SQL statement following two hyphens is
treated as a comment. Even * can be coded in column 7 for comments.
![Page 44: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/44.jpg)
44Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Single Row Manipulation (1 of 6)
EXEC SQL SELECT EMPNO, SALARY INTO :WS-EMPNO, :WS-SALARY FROM EMMPLOYEE WHERE NAME = :WS-NAME
END-EXEC
SELECT
IF SQLCODE = 0 CONTINUEELSE IF SQLCODE = -811 DISPLAY “MULTIPLE ROWS”ELSE PERFORM C9000-ERROR-PARA.
Error Handling
![Page 45: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/45.jpg)
45Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Single Row Manipulation (2 of 6)
EXEC SQL INSERT INTO EMPLOYEE (EMPNO, NAME, SALARY) VALUES (:WS-EMPNO, :WS-NAME, :WS-SALARY)END-EXEC
INSERT
IF SQLCODE = 0 CONTINUEELSE DISPLAY “UNIDENTIFIED ERROR OCCURRED”.
Error Handling
![Page 46: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/46.jpg)
46Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Single Row Manipulation (3 of 6)
EXEC SQL UPDATE EMPLOYEE SET SALARY = :WS-SALARY WHERE EMPNO = 10878END-EXEC
UPDATE
IF SQLCODE = 0 CONTINUEELSE DISPLAY “UNIDENTIFIED ERROR OCCURRED”.
Error Handling
![Page 47: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/47.jpg)
47Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Single Row Manipulation (4 of 6)
EXEC SQL DELETE FROM EMPLOYEE WHERE EMPNO = :WS-EMPNOEND-EXEC
DELETE
IF SQLCODE = 0 CONTINUEELSE DISPLAY “UNIDENTIFIED ERROR OCCURRED”.
Error Handling
![Page 48: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/48.jpg)
48Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Single Row Manipulation (5 of 6)
EXEC SQL SELECT NAME, SALARY INTO :WS-NAME, :WS-SALARY:WS-SALARY-IND FROM EMPLOYEE WHERE EMPNO = :WS-EMPNOEND-EXEC
Selecting Nullable columns(Columns which can take NULL values)
Indicator variable values and their meanings
(1) 0 Not Null
(2) -1 Null
(3) -2 Exceptional condition
![Page 49: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/49.jpg)
49Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Single Row Manipulation (6 of 6)
MOVE -1 TO WS-SALARY-INDEXEC SQL INSERT INTO EMPLOYEE(SALARY) VALUES:WS-SALARY:WS-SALARY-INDEND-EXEC
Inserting Null values to columns
IF SQLCODE = 0 CONTINUEELSE DISPLAY “UNIDENTIFIED ERROR OCCURRED”.
Error Handling
![Page 50: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/50.jpg)
50Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Cursors (1 of 7)
• Are memory structures used to handle multiple row selections at a time.
• Conceptually they is a results table used by DB2 to contain the multiple results
of a query.
• They are data structures which hold some/all the results of a query.
• Are defined in the WORKING- STORAGE SECTION/ PROCEDURE
DIVISION.
![Page 51: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/51.jpg)
51Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Cursors (2 of 7)Operations associated with a Cursor
1. DECLARE
2. OPEN
3. FETCH
4. CLOSE
![Page 52: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/52.jpg)
52Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
EXEC SQL DECLARE EMPCUR CURSOR FOR
SELECT EMPNO,NAME,SALARY FROM EMPLOYEEWHERE EMPNO > :WS-EMPNO
END-EXEC
Declaring a Cursor
Note: This is just the definition, DB2 executes this statement when we open it.
Cursors (3 of 7)
![Page 53: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/53.jpg)
53Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
EXEC SQL OPEN EMPCUREND-EXEC
Opening a Cursor
Cursors (4 of 7)
EXEC SQLCLOSE EMPCUR
END-EXEC
Closing a Cursor
![Page 54: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/54.jpg)
54Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
EXEC SQL FETCH EMPCUR INTO :WS-EMPNO,:WS-NAME,:WS-SALARYEND-EXEC
Fetching results from a cursor
Cursors (5 of 7)
• Remarks:
1. We get one row at a time.
2. Only forward READ till SQLCODE = 100.
![Page 55: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/55.jpg)
55Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
EXEC SQLDECLARE EMPCUR CURSOR SELECT EMPNO, NAME, SALARY FROM EMPLOYEE WHERE EMPNO > :WS-EMPNO FOR UPDATE OF SALARYEND-EXEC
Fetching to Update
Cursors (6 of 7)
Note: This gives a U lock on the records.
![Page 56: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/56.jpg)
56Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
EXEC SQLUPDATE EMPLOYEESET SALARY = :WS-SALARYWHERE CURRENT OF EMPCUR
END-EXEC
Updating a row fetched from a cursor
Cursors (7 of 7)
Note: This updates exactly one row even if we do not
give the key value.
![Page 57: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/57.jpg)
57Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Summary
• SPUFI Hands On
• Introduction to Embedded SQL
• DCLGEN Hands On
• SQLCA Copy Book and its Use
• Single Row Manipulation
• Multiple Row Manipulation
• Cursors
![Page 58: DB2. 2 Copyright © 2005, Infosys Technologies Ltd ER/CORP/CRS/DB01/003 Version No:2.0a Session Plan SPUFI Hands On Introduction to Embedded SQL DCLGEN](https://reader036.vdocuments.us/reader036/viewer/2022062716/56649dc75503460f94abc10a/html5/thumbnails/58.jpg)
58Copyright © 2005, Infosys Technologies Ltd
ER/CORP/CRS/DB01/003 Version No:2.0a
Thank You!