db2 cour mat
DESCRIPTION
cvxhcvghfxTRANSCRIPT
![Page 1: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/1.jpg)
DB2
![Page 2: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/2.jpg)
INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS
![Page 3: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/3.jpg)
DefinitionData Base is a collection of data stored in a well
organized manner
Advantages of databaseRedundancy can be reducedInconsistency can be avoidedData can be sharedStandards can be enforcedSecurity restrictions can be appliedIntegrity can be maintainedconflicting requirements can be balanced
![Page 4: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/4.jpg)
Characteristics of DBMSData IndependenceSpeedy handling of spontaneous information
requestsNon-RedundancyVersatility in representing relationships
between data itemsSecurity protectionReal-time accessibility
![Page 5: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/5.jpg)
Types of data bases Hierarchical Database Network Database Relational Database
Hierarchical DatabaseThe Data in this database is stored in the form
of Segments. The Segments will have Parent-Child relationship.
One Parent segment can have more than one
Child Segments and one Child Segment can not have more than one Parent Segment,
![Page 6: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/6.jpg)
I.e., One-To-Many approachEx. IMS/DB
Network DatabaseThe data stored in this type of database in the
form of records and linksHere one record can have more dependent
records and the same record can have more superior records also, I.e., Many-To-Many approach
Ex. IDMS
![Page 7: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/7.jpg)
Relational DatabaseThe data in this type of database is stored in
the form of Tables.The relationship between Tables can be
maintained by using Primary key and Foreign Key relationship
Ex. DB2, Oracle, Sybase etc..
![Page 8: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/8.jpg)
RELATIONAL DATABASE MANAGEMENT SYSTEM
![Page 9: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/9.jpg)
DB2DB2 is a relational database developed by
IBM for computers running under MVSDB2 can be connected to CICS, IMS, TSO
(These three environments provide services
like data communication and control of
transactions)
![Page 10: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/10.jpg)
RDBMS TerminologyThe data in Relational data base is stored in
Table/Relation in terms of rows and columnsRows are called TUPLES and columns are
called ATTRIBUTESNo.of attributes is called as ORDERNo.of Tuples is called as CARDINALITY
![Page 11: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/11.jpg)
Primary key It represents a single column or group of columns
and performs the unique identification of rows(records) in table
Alternate key It is a alternate key to search table, need not maintain
uniqueness
Referential Integrity If table-1 includes a foreign key FK matching the
primary key PK of table-2 , then every value of FK in the first table must either be equal to the value of PK in the second table, then Referential integrity is ensured b/w two tables
![Page 12: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/12.jpg)
Relational OperatorsUnion, Union All
Combining two or more tables or part of tables in row fashion
ex. Select empname from emp where deptno=10
UNION
select empname from emp where deptno=20
Union All eliminates duplicates
![Page 13: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/13.jpg)
JoinCombining two or more tables or part of
tables in column fashionThere is no Join operator in DB2, but we can
achieve it through ‘Select’ statementsThere are Internal Join and Outer Join, Internal
Join looks for matching values of both columns of two tables where as Outer Join does not look for matching.
ex. Select empsal.*, emppsnl.* from empsal,
emppsnl where empid.empsal= empid.emppsnl
![Page 14: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/14.jpg)
Normalization:
It is a formal process of developing data structures in a manner that eliminates redundancy and promotes integrity.
This normalization can be achieved generally by using three forms
1) Elimination of repeating groups
2) Elimination of Partial key dependencies
3) Elimination of inter key dependencies
![Page 15: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/15.jpg)
The following ex. For attaining first normal form:
CREATE TABLE Contacts
(contact_id Number(9,0) Not Null,
l_name Varchar(20) Not Null,
f_name varchar(20),
contact_date1 Date,
contact_desc1 varchar(50),
contact_date2 Date,
contact_desc2 varchar(50));
![Page 16: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/16.jpg)
The elimination of repeating groups can be
done as follows
CREATE TABLE Contacts
(contact_id Number(9,0) Not Null,
l_name Varchar(20) Not Null,
f_name varchar(20));
CREATE TABLE Conversation
(contact_id Number(9,0) Not Null,
contact_date Date,
contact_desc varchar(50));
![Page 17: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/17.jpg)
The following ex. For attaining second normal form:
CREATE TABLE Employee
(emp_no Number(9,0) Not Null,
l_name Varchar(20) Not Null,
f_name Varchar(20)
dept_code Number(3,0)
description varchar(50));
![Page 18: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/18.jpg)
The second normal form can be obtained as follows
CREATE TABLE Employee
(emp_no Number(9,0) Not Null,
l_name Varchar(20) Not Null,
f_name Varchar(20)
dept_code Number(3,0));CREATE TABLE Department
Dept_code Number(3,0) Not Null,
Description varchar(50) not null);
![Page 19: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/19.jpg)
Ex. For third normal form CREATE TABLE Contacts
(contact_id Number(9,0) Not Null,
l_name Varchar(20) Not Null,
f_name varchar(20)
company_name varchar(20)
company_location varchar(50));
![Page 20: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/20.jpg)
The third normal form can be obtained as follows
CREATE TABLE Contacts
(contact_id Number(9,0) Not Null,
l_name Varchar(20) Not Null,
f_name varchar(20)
company_id varchar(20));CREATE TABLE Company
(company_id Number(3,0) Not Null,
company_name Varchar(20) Not Null,
company_location varchar(50));
![Page 21: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/21.jpg)
OVER VIEW OF DB2
![Page 22: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/22.jpg)
Major Components of DB2• System service component which supports system operation(system start start up and shut down), operator communication, system log etc.• Locking service component which provides necessary controls for managing concurrent access to data• Data base service component which supports the definition, retrieval and update of user and system data
![Page 23: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/23.jpg)
• Distributed data facility component It provides access to distributed data
Runtime Supervisor• It is resident in the main memory when the application program is executing.•When the program requests some database operation to be performed, control first goes to the runtime supervisor, which uses the control information in the application plan to request the appropriate on the part of the Data Manager.
![Page 24: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/24.jpg)
Data Manager• It performs all the normal access method functions- search, retrieval, update, index maintenance, etc., and also manages the Physical Databases. • It invokes other system components as necessary in order to perform detailed functions such as locking, logging, I/O operations, etc.
![Page 25: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/25.jpg)
Data types in BD2String:
char(x)--> max. val.of ‘x’ is 255
varchar(x)--> max. val.of ‘x’ is 4096 bytes
Graphic(x)--> max. val.of ‘x’ is 127 bytes
Vargraphic(x)
char,varchar are single byte characters set and
graphic and vargraphic are duble byte character set.
![Page 26: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/26.jpg)
Numeric:
Small int(two bytes), Integer(four bytes), Decimal(x,y), float(n)
Date and time:
Date --> mmddccyy
Time --> hhmmss
Time stamp --> mmddccyyhhmmssnnnnnn
![Page 27: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/27.jpg)
STRUCTURED QUERY LANGUAGE (SQL)
![Page 28: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/28.jpg)
SQL (Structured query language) It is used to communicate with database.we can
use this language in two ways I.e., one way is DB2 Interactive another way is by embedding the sql code in application program
Sql is divided into three departments, those areDDL (Data definition language)
operations: CREATE, ALTER, DROP DML (Data manipulation language)
operations: INSERT, UPDATE, DELETE,
SELECT
Contd...
![Page 29: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/29.jpg)
DCL (Data control language)
operations: GRANT, REVOKE
Explanation with ExamplesDDL is used for objects like storage group,
database,table, view, index, Alies,synonyms
ex. CREATE TABLE table name
(col1 data type null constraint,
col2 datatype null constraint, ..)
PRIMARY KEY (key field),
FOREIGN KEY (keyfield)
REFERENCES base table
ON RESTRICT/CASCADE/SET NULL
![Page 30: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/30.jpg)
ALTER TAABLE tablename
ADD colx data type
PRIMARY KEY(key field)
FOREIGN KEY (key field)
REFERENCES base-table
ON DELETE RESTRICT/CASCADE/ SET
NULL DROP TABLE table name
![Page 31: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/31.jpg)
DML is used for manipulation of data in a table
ex.a) INSERT into emp(empno,deptno,name)
values(‘102’,’30’,’xyz’)
b)SELECT * from emp where deptno = 10
c)UPDATE emp SET salary= 10000
where dept.no in (10,20)
d) DELETE from emp where d.no=10DCL is used for to give and take back privileges
from users
ex. REVOKE privileges ON object FROM user
privileges are like select, update, delete, insert
![Page 32: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/32.jpg)
Usage of IN, BETWEEN, LIKE, SUBSTR in sql
IN:
SELECT * FROM emp WHERE deptno
IN(‘10’,’20’)
BETWEEN:
SELECT * FROM emp WHERE salary BETWEEN(1000,3000)
SUBSTR:
SELECT SUBSTR(empname,1,4) from emp
WHERE empno=10
![Page 33: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/33.jpg)
LIKE:
For partial search of a columns LIKE is used.
ex.a)Select empid from emp where empname
LIKE ‘-am-’
b)select empid from emp where empmane
LIKE sub%
![Page 34: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/34.jpg)
Usage of mathematical functions in sql MIN,MAX,COUNT,SUM,AVG
ex. Select MAX(salary) FROM emp
Select MIN(salary) FROM emp
Select COUNT(*) FROM emp
Select AVG(salary) FROM emp
Select SUM(salary) FROM emp
![Page 35: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/35.jpg)
Subquery
It is a query having another query inside the
main query is called subquery
Ex.SELECT ename FROM emp WHERE d.no=(SELECT d.no FROM dept WHERE Loc =
‘chennai’)Correlated subquery
It is a subquery executed for each qualified row
in the outer query.
Ex.1 SELECT name, salary FROM emp WHERE
emp.salary > (SELECT AVG(dept.salary) FROM
dept WHERE emp.deptno=dept.deptno)
![Page 36: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/36.jpg)
Note:
The execution of a subquery happens from inner query to outer and in case of correlated subquery, the execution happens in reverse order, I.e., from outer query to inner query
Ex.2 Select name, deptno, salary from emp x
where salary > (select AVG(salary) from emp GROUP BY deptno HAVING deptno=x.deptno)
![Page 37: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/37.jpg)
DB2 APPLICATION DEVELOPMENT
![Page 38: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/38.jpg)
Steps involved in Execution of an application program
Source Code
Precompiler
Modified source code DBRM
Compile Bind
Link Package or Plan
Load Module
![Page 39: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/39.jpg)
DBRM: (Database request module)It contains the Sql stmt’s extracted from host
language program in precompilation step.DBRM is stored in a member of PDS. This DBRM is the input for the Bind process. A token is placed in DBRM& load module in precompiler step
BIND: It involves in creating executable code for the
sql stmt.’s in DBRM with optimised data access path for sql queries
![Page 40: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/40.jpg)
The out put of bind step, is generating PACKAGE (executable code of one DBRM) or PLAN (executable code of more than one DBRM’s)
performs authorization checks Isolation level parameters, lock size
parameters are declared in this step
![Page 41: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/41.jpg)
Some more notes to run DB2 application program
We run a DB2 application program by submitting JCL or through TSO environment
We have to supply parameters like DB2 subsystem name, plan or package name, name of the program, library of load module etc.
generally to run a db2 application program we use IKJEFT001 utility program
![Page 42: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/42.jpg)
SPUFI(sql processing using file input)It reads the sql statements contained as a text in
sequential file, processes those statements and places in output dataset specified in browse mode
QMF(Query management facility) It is an interactive query tool used to produce
formatted query output.we can type sql query after choosing option 6 from QMF main panel and press PF2 to run the query, for formating the report press PF9 before getting the printout.qmf is for single query execution. QMFPROC can be used to executed multiple sql queries.
![Page 43: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/43.jpg)
Host variable It is a storage space provided by the host language
for the variables declared in sql queries. Purpose is to store the query result in the corresponding host variable.
NULL, NOT NULL, NOT NULL WITH
DEFAULTIn a table, all columns of every record always
can’t contain values, in such cases we can use Null, Not Null, Not Null with default instead of supplying values. These options we should define while creation of Table for particular column.
![Page 44: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/44.jpg)
Nullwhen we don’t supply values for a column of
specified record, null will be supplied by the system.
Not NullThis option specifies that the column can’t have
nulls, always values to be supplied for the columns
declared as Not Null. Generally KEY fields(columns) are defined with
NOT NULL option.
![Page 45: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/45.jpg)
Not Null With DefaultThis option specifies that default values will
be supplied by the system when ever the values are not supplied by us for the columns defined as NOT NULL WITH DEFAULT.
The values generally supplied are Spaces, Zeroes, Time stamp for Character data, Numeric data and Date data respectively.
![Page 46: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/46.jpg)
Insertion of a null indicator By moving -1 null indicator variable
ex. EXEC SQL
INSERT INTO emp
VALUES(:ws-empno, :ws-sex,
:ws-age :ws-age-ind)
END-EXEC
![Page 47: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/47.jpg)
Singleton Select and
CURSOR Technique
We can use sql in interactive mode and batch mode
(by embedding sql statements in the application
program)Embedded sql is of two types one is singleton select
and cursor type In Singleton select we can select only one row,
if the result containing more than one row abends the program
![Page 48: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/48.jpg)
Ex. For singleton Select statement EXEC SQL
SELECT empno, name, sex
INTO :ws-emp-rec FROM emp WHERE
empno = :ws-empno
END-EXEC
CURSOR TechniqueIn CURSOR technique, we can process
multiple rows unlike one row in singleton select. Cursor technique can be achieved by four steps , those are
![Page 49: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/49.jpg)
1)Declare cursor
EXEC SQL
DECLARE empcursor CURSOR FOR SELECT empno,sex,age FROM employee
END-EXEC2)Open cursor
EXEC SQL
OPEN empcursor
END-EXEC
![Page 50: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/50.jpg)
3)Fetch cursor
EXEC SQL
FETCH empcursor INTO : ws-emp-no,
ws-sex, ws-age
END-EXEC4) Close cursor
EXEC SQL
CLOSE empcursor
END-EXEC
![Page 51: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/51.jpg)
FOR UPDATE OF, WHERE
CURRENT OF, WHEN EVERFor Updating any record in the table by using
embedded sql we will use ‘FOR UPDATE OF’ and ‘WHERE CURRENT OF’ phrases
To handle the error conditions in an application program, ‘WHEN EVER’ option will be used.
For the above phrases examples are given in next slide.
Contd.…
![Page 52: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/52.jpg)
EXEC SQL
DECLARE empcursor CURSOR FOR SELECT empno, salary FROM emp FOR UPDATE OF salary
END-EXECEXEC SQL
UPDATE emp SET salary=:ws-salary
WHERE CURRENT OF empcursor
END-EXECEXEC SQL
WHENEVER condition action
END-EXEC
![Page 53: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/53.jpg)
Conditions are like SQL WARNING, SQL
ERROR, NOT FOUND and Actions are like CONTINUE, GO TO
SQLCA(sql communication area)It contains fields (SQLCODE, SQLERRD)
which are used for communicating information describing the success or failure of SQL statement.
EXEC SQL
INCLUDE SQLCA
END-EXEC
![Page 54: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/54.jpg)
Model DB2 application program
(with COBOL)working storage section
01 ws-fetch-flag pic 9(1).
88 ws-fetch-over-cn value 0.
88 ws-fetch-not-over-cn value 1.
01 ws-salnewEXEC SQL
DECLARE empcursor CURSOR FOR
SELECT EMPNO,NAME,DEPT,SALARY
FROM employee WHERE dept= :pa-cry-dept
FOR UPDATE OF salary
END-EXEC
![Page 55: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/55.jpg)
EXEC SQL
INCLUDE SQLCA
END-EXEC
procedure Division.MOVE ‘consultancy’ TO pa-cry-dept.
EXEC SQL
OPEN empcursor
END-EXEC.
SET ws-fetch-not-over-cn TO TRUE.
PERFORM Modify-sal-para
THRU Modify-sal-para-exit
UNTIL ws-fetch-over-cn.
![Page 56: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/56.jpg)
EXEC SQL
CLOSE empcursor
END-EXEC.
STOP RUN.
Modify-sal-para.
EXEC SQL
FETCH empcursor
INTO :empno, :name, :dept, :salary
END-EXEC.
EVALUATE SQLCODE
WHEN SQLCODE < 0
PERFORM X-error-para
THRU X-error-para-exit
![Page 57: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/57.jpg)
WHEN SQLCODE < +100
SET ws-fetch-over-cn TO TRUE WHEN OTHER
COMPUTE ws-salnew = salary * 1.25
EXEC SQL
UPDATE employee
SET salary = ws-salnew
WHERE CURRENT OF empcursor
END-EXEC
END-EVALUATE.
Modify-sal-para-exit.
EXIT.
![Page 58: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/58.jpg)
Important SQL CODES If SQLCODE = 0 --> execution successful
> 0 --> execution successful with warning
< 0 --> execution not
successful
100 --> No rows are matching for a specified
condition
-811 --> Result of an embeded sql stmt’s resulted in retrieval of more than one row
-803 --> Duplicate rows inserted
![Page 59: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/59.jpg)
-603 --> Unique index creation failure due to
duplicate row inserted
-913 --> Dead lock
-918 --> Mismatch of time stamp b/w DBRM & Load module
![Page 60: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/60.jpg)
DB2 INTERNALS
![Page 61: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/61.jpg)
Database ObjectsStorage Group
It is set of DASD volumes given a particular name By using CREATE STORAGE GROUP we can create storage group
Data Base
It is collection of table spaces and index spaces.Byusing CREATE DATABASE we create data base.
![Page 62: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/62.jpg)
Table space
Table spaces are used to store the tables. These are three types, Simple table space, Segmented table spaces, Partitioned table spaces
Table
Combination of rows and columns INDEX
Index is an ordered set of pointers to rows of a base
table. Key has to be supplied for creation of an index
To create index, use CREATE INDEX syntax
![Page 63: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/63.jpg)
VIEW
It is window to the table, may be combination of tables or part of a table. Views are not updatable if the view is combination two tables
ex. CREATE VIEW empview (empno, name) AS
(SELECT empno, name FROM emp) WHERE
salary < 3000SYNONYM
It is an alternate name for a table. The purpose is to
hide the information location of the table It can be
accessed only by the user who creates it, mainly used
in local sub systems, it will be dropped when table is dropped
![Page 64: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/64.jpg)
Ex. CREATE SYNONYM synonym name FOR table name
ALIAS
It is similar to synonym, the differences are, it can be accessed by any user, can be used for local as well as remote subsystems and it will not get dropped even when the table is dropped
![Page 65: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/65.jpg)
Locking Mechanism and Isolation LevelsLock Locking is a mechanism to maintain the
integrity of data in database
Types of locks Sharable --> Read only Update --> Read only Exclusive --> No access to others
Lock levels we can get lock at Row level or at page level
or at Table level or at Table space level we can
![Page 66: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/66.jpg)
specify what lock size to use, while creationof
table space or in bind operation or through
application program.
Isolation levels These specifies lock to be held whether until
next fetch or until end of unit of work There
are two types of Isolation Levels. These Isolation Level will be specified at
BIND time.
![Page 67: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/67.jpg)
1) Cursor Stability In this type of isolation level, lock held until
next fetch By using this we can avoid last update
problem2) Repeatable read In this type of isolation level, lock held until
end of unit of work Incorrect summary is eliminated
![Page 68: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/68.jpg)
Dead lock It is a situation in which two or more
transactions are in simultaneous wait state, each wait for one of others to release a lock on object before it can proceed. Transaction manager will take care this problem suggests for roll back of the task
![Page 69: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/69.jpg)
DB2 RESTART AND RECOVERY SCENARIOS
![Page 70: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/70.jpg)
RecoveryThe data in database will be recovered by
using Recover utility, when any problem occurs like system crash, is called Recover of database.
Active LogIt is DASD dataset used to write the DB2 log
record (containing data changes and significant changes as they occur) is called Active Log.
![Page 71: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/71.jpg)
Archive logWhen Active log is full DB2 copies its
contents to DASD or TAPE dataset called Archive log.
Off-LoadingThe process of transferring data from Active
log to Archive log is called Off-Loading.
![Page 72: Db2 Cour Mat](https://reader035.vdocuments.us/reader035/viewer/2022062313/55cf850e5503465d4a8b4b97/html5/thumbnails/72.jpg)
COMMIT
By issuing this command the database changes
will be updated and establishes a syncpointRoll back
When any unsuccessful end-of-transaction happens, to undo all the changes upto the last syncpoint (boundary b/w two transactions), roll back command is used.