Relational Database Management System(RDBMS)
This is one of the sub systems in Mainframes.
Any number of Sub Systems can be created in Mainframes as per the requirements.
Hierarchy of DB2 Sub system:
TS – Table Space INS – Index Space
T – Table IND – Index
V – view
Maximum storage space for a Table Space is 64 million bytes.
SQL can be used to Create, Delete, and Update and Query the Objects
SQL queries can be executed by the following techniques
Tools like QMF (Query Management Facility)
SPUFI (SQL Processor User File Input)
DATA TYPES :
Integer -- 4 bytes
Small int -- 2 bytes
Char(n) – N bytes
Varchar(n) – N+2 bytes
Graph(n) – 2n bytes
Vargraph(n)– 2N+2 bytes
Date – 10 bytes
Time – 8 bytes
Timestamp – 26 bytes
Arranging the data in the Database in organized manner.
1NF: Avoiding multiple values or set of values in one column.
2NF: Avoiding repeated rows by defining primary key.
3NF: Separating functionally dependent and non-functionally dependent columns
Primary key :-
Uniquely identified row
Which can be formed with single or multiple columns
Does not allow duplicate records
Cannot contain Null
Foreign key : -
Another identifier which can be used to build relationship between the tables
Must be the primary key of parent table with same data type & length
Can consists of single or multiple columns
Can contain Null or duplicate rows
Multiple foreign keys can be defined in one table
Foreign key should be defined at the time of defining child table in the create command by “WITH REFERENCES” option.
CREATE TABLE ITEM(
PRIMARY KEY IS INO,
FOREIGN KEY IS CNO
WITH REFERENCES CUST)
The relationship between two tables which can be achieved by defining foreign key.
Cannot contain Null values or duplicate rows
Can contain Null values or duplicate rows
Cannot be updated
Can be updated
Can be defined as a foreign key in other table
which must be primary key of another table
only one primary key can be defined for one table
multiple foreign keys can be defined for one table
SQL(Structured Query Language)
DDL (Data Definition Language)
Create, alter, drop
DML (Data Manipulation Language)
Insert, update, select & delete
DCL (Data Control Language)
TCL (Transaction Control Language)
Some SQL Quries
Static SQL for Insert: Insert into cust(cno, cname, cloc) values (10, “xyz”, “hyd”)
Dynamic SQL for Insert: Insert into cust(cno, cname, cloc) values (v1, v2, v3)
v1,v2, v3 are host variables to be defined in working storage section.
Delete from cust
Delete from cust where cno = 20
Update cust set cname = “ xyz” where cno = 20
Select cno,cname from cust
Select * from cust
Select * from, cust where cno = v1
Select * from cust wehre cno=v1 and cname =v2
Select * from cust where cno between 20 and 60
Select * from cust where cname like “%y%”
Select max(sal) from emp
Select min(sal) from emp
Select avg(sal) from emp
Select sum(sal) from emp
Above statement returns Null values if no row exits for specified condition
To avoid duplicate rows : select distinct cno,cname from cust
To get total no. of rows : select count(*) from cust
Above statement returns Zeros if no row exits for specified condition
Query within Query
First inner query executes & out query executes based on the result of inner query
Max of 15 sub queries can be coded
To simplify sub queries, logic can be built with combination of COBOL + SQL statements
To retrieve second maximum salary from emp table:
Select max(sal) from emp where sal <(select max(sal) from emp)
To retrieve third maximum salary from emp table:
Select max(sal) from emp
where sal < (select max(sal) from emp
Where sal < (select max(sal) from emp))
For every row of outer query, inner query must executes at least once
First outer query executes & then inner query executes
Practical examples : to fine top 2,3 or n salaries
Select a. sal from emp a
where 0 = (select count(*) from emp b
Where a.sal < b.sal)
– 2nd max
– 3rd max
– 4th max
Declaration Generator . a tool to generates the equivalent COBOL variables.
Which can be used to generate host variables with equivalent data types of DB2 columns.
DB2 Table DCLGEN COBOL
DB2 Table DCLGEN COBOL
Can be used to pass the data from cobol program to DB2 table or DB2 table to COBOL program.
When host variables are coded with sql statements it must be prefixed with : like :hv-cname.
Table name must be supplied as input to DCLGEN & partition dataset should be as output.
After creating DCLGEN variables which must be copied to application program in WORKING-STORAGE SECTION by using include command i.e.
Include & copy have the same functionality
Predefined numeric number which can be used to check SQL statements for successful , unsuccessful execution.
SQLCODE can be stored in SQLCA(SQL Communication Area)
Copy SQLCA in WORKING-STORAGE SECTION
System defined variable
Evaluate or if statement must be coded to check the SQLCODE immediately after SQL statement.
SQLCODE =00 ---- successful
= +100 --- end of table or record not found.
01 WS-SQL-CODE PIC S9(4)
88 88-SUCCESS VALUE 00
88 88-NOTFOUND VALUE 100
88 88-FORIENG KEY VOILATION VALUE –532
88 88- MULITPLE ROW VALUE –811
SET CNAME = :HV-CNAME
MOVE SQLCODE TO WS-SQLCODE.
DISPLAY “SUCCESSFULLY UPDATED”
DISPLAY “ RECORD NOT FOUND”
DISPLAY “ FOREIGN KEY VOILATION”
DISPLAY “ ERROR OCCURRED IN UPDATE”
To retrieve multiple rows for a given condition.
Let us take the following example:
from cust where cloc =:hv-cloc
If the condition satisfy for one row it executes successfully. If the condition satisfy for multiple rows it wont work. It returns –811 as SALCODE. For this we use cursors.
Cursors can be used to retrieve multiple rows for a given condition
Cursor cycle is Declare ---> Open ----> Fetch -----> Close
Declare: declares or define name for cursor against a table
Can be coded in working-storage section or procedure division
For better readability code in working-storage section.
Open: can be used to open a cursor with rows for a given conditions inbuffer.
Retireves data in to buffer
Must be coded in the procedure division only
Where condition value must be supplied before opening a cursor.
Fetch: can be used to retrieve rows one by one from buffer into application prog.
Which must be coded in procedure divison after open.
Must be coded with hostvariables
No of host variables in fetch & no of columns in the declare must be same
Canbe executed multiple times using perform. i.e. till EOT or record not found which can be identified by SQLCODE = 100
Close : used to close the cursor
Must be coded in procedure division only
Must be executed after open statement.
Practical examples : Can be used to retrieve the data based on loc, date, products.
DECLARE C1 CURSOR FOR
SELECT CNO,CNAME FROM CUST
PERFORM UNTIL SQLCODE= 100
FETCH C1 INTO :HV-CNO,:HV-CNAME
For Update of where current of:
Which can be used to update row by row when multiple rows are satisfied.
Before update cursor has to be declared with for update of column option.
Where current of cursor name option must be used with update command
Withhold: this option can be used to remain cursors open even after commit statement.
Must be coded with cursor statement
DECLARE C2 CURSOR WITH HOLD FOR
SELECT CNO,CNAME FROM CUST
FOR UPDATE OF CNAME
FETCH C2 INTO :HV-CNO,:HV-CNAME
UPDATE CUST SET CNAME=”ABC” WHERE CURRENT OF C2.
Index allows duplicate values
unique index doesn’t allow duplicate rows
cross reference between index table & table is called clustered index.
Create index in1 on cust(cno)
Uniquely identified row
Record identified based on the index
Records identified based on the index
No duplicated rows, no null values
Duplicate rows, null values are allowed
No duplicate rows
Can consist of single or multiple columns
Dan consist of single or multiple columns
Can consist of single or columns
This will be stored in SYSKEYS.
This is stored in SYSINDEX
This is stored in SYSINDEX
CREATE VIEW CVIEW(VCNO,VCNAME,VCLOC) AS
(SELECT CNO,CNAME,CLOC FROM CUST WHERE
CNAME LIKE “%X%)
Logical representation of the table
Stored in virtual memory
Can be derived from single table or multiple tables
Views are updateable if they are derived from single table without any column functions , group by
Multiple views can be generated from single table.
Views are stored in sysviews
Advantages of Views:
Logical data independence
Part of the information can be visible to the sers
Accessing can be faster.
Delete rules can be applied for delete command against Database.
Delete rules are 3 types
on delete cascade – all matching child rows will be deleted automatically when we delete parent row.
on delete restrict – all matching rows will be restricted when we delete parent row which is default.
on delete set null – all matching child row will be set to null when we delete parent row.
UNION is used to concatenate rows into one table from single or multiple tables.
Rules : no. of columns & data type of both the queries must be same. column may be different
UNION can be used to eliminate duplicate rows
UNION ALL retrieved duplicate rows also.
SELECT CNO,CNAME FROM CUST WHERE CNO=10
SELECT CNO,CNAME FROM ITEM WHERE INO=20
JOINS can be used to concatenate columns from one table or multiple tables.
JOIN types are :
left outer join : which can be used to retrieve matching, non matching rows from leftside table
right outer join: which can be used to retrieve matching, non matching rows from right side table.
full outer join: which can be used to retrieve matching, non matching rows from both the tables.
self join or inner join : can be achieved by defining alias for the table.
It can be used to evaluate the performance of SQL queries.
It can be used to tune SQL queries.
Input is SQL queries and output is plan-table.
For every SQL query one plan-table will generate.
All plan-tables are stored in physical seq file.
Consists of Table pace, Index space, Index, unique index, Views, Alias, synonyms, keys.
When we create table, the details of table are entered in Systable automatically.
SysIBM.SYSKEYS: All primary & foreign keys.
Grant table syscols to all
Grant table syscols(select/delete/update) to user1,user2
Revoke table syscols from all.
SYSTABLE, SYSCOL, SYSKEYS, SYSINDEX, SYSPKS, SYSFKS, SYSALIAS, SYSSYNONYMS, SYSINDEX, SYSVIEWS,SYSTABLESPACE, SYSINDEXSPACE.
Pre compiler takes COBOL+DB2 program as input & generates DBRM which will be stored in userdefined PDS as separate member of Recln=80
DSNHPC --- IBM supplied utility used for precompilation.
Separates SQL & COBOL statements
Check SQL syntaxs
Replace all SQL statements with host language call statements in the compiled program.
Which generates timestamp tokens
BIND takes DBRM as input & generate package & application plan. The package will be loaded to the directory. Plan will be loaded to sysplans.
Checks authorization for SQL statement
Checks the syntax errors of SQL statements like
Missing co name in the select list & used in order by & group by
Mismatch columns host variables
Data type mismatch of columns & host variables
Indicator variables not declared
It generates optimized access path by analyzing the statistics of SQL statements which will be stored.
RUNSTATS utility is one of the ISPF panel option which is stored in DB2 defaults option.
Optimized path is stored in package which is not executable module.
Which can be used to lock the database at the time of executing SQL statements.
Cusrsor stability(CS): It acquires the address of a row. Sets the pointer to a specified row based on SQL query & acquires the lock against that row. Then releases the klock after the transaction before commit.
Repeatable Read(RR): which acquires the address of a row & acquire lock against the page(1 page -4024 bytes) & then released the lock after the commit statements.
Default is RR.
Which is to oversee execution of SQL statements.
Statistics like no of tables, columns, indexes, keys
It consists of executable module which is actual output of SQL statements which must be specified in the RUNJCL to execute SQL queries if the program is batch program. If the program is online which must be specified in RCT. Application plan will be loaded to load module with time stamp tokens.
The compiler takes COBOL statement as input to generate object program, & loaded to the load module by line/edit with time stamp tokens.
DSNHPC : system utility pre compiler.
IKJEFT01 or IKJEFT01B --- BIND /Run Cob-DB2 Program
IGYCRCTL or IKFCBLOO --- COBOL compilation
IEWL or HEWL --- link/edit
INTERVIEW QUESTIONS: (watch this space for more info on below Qns)
What is RI ? where did u use RI in your project? Explain with example?
What is the difference between primary key, foreign key, index & unique index?
Can we create index when table has duplicate rows?
Can we create unique index when table has duplicate rows?
Can we create index or unique index on empty table?
What happens to the package when index is dropped? What’s the process or steps to be taken?
How to delete package?
Where package is stored? How to retrieve package?
Difference between plan & package?
What are the steps to be taken when SQL statements are changed without changing any COBOL statements?
Do we need to pre compile when index is dropped?
Can we bind when table is dropped?
Can optimized access path consist of multiple DBRMS.
What is the significance of timestamp tokens?
What is the significance of normalization?
Where do we specify run program, plan name, library & DB2 subsystem
IBM supplied utility to run COBOL + DB2
What is difference between DB2 & COBOL files & give some example for COBOL files & DB2 tables related to your project?
Can we load data from sequential file to table or table to sequential file?
What are the steps to be followed to develop DB2+ COBOL program?
Can we prepare a program/compile when DB2 system is down?
How to identify DB2 test or production system by seeing run JCL?
What is the output of explain?
What is the difference between correlated sub query & sub query?
How to find 4th max sal?
How to find nth max sal?
How to evaluate SQLcodes & where it is stored?
How to count total no of unique rows from the table?
How to sum repeated rows?
How to write a cobol program for above query? Retrieve row by row & use cobol logic to sum repeated rows?
What is the significance of DCLGEN?
Where DCLGEN is stored?
Difference between join & union?
difference between UNION & UNIONALL?
Can be have different col names in union?
How do u evaluate/tune/analyze SQL queries?
What are the JCL utilities for compile, pre compile, bind & link edit?
Wha5t is the significance of isolated levels?
Can we alter foreign key?
Can we alter primary key?
Can we alter data type & length?
What are the equivalent cobol variables for varchar?
What is the time stamp & its format?