Wednesday, November 30, 2011
DCLGEN
PROGRAM PREPARATION AND EXECUTION
- Pre compile
- Compile
- Link-edit
- Bind/Rebind
- Execution
* Languages that can be used with SQL :
- Assembler
- BASIC
- COBOL
- FORTRAN
- PL/1
- C
DB2 DATA TYPES AND EQUIVALENT COBOL FIELD DEFINITIONS
DATA TYPES KIND OF DATA COBOL DEFINITION
SMALLINT Half word integer PIC S 9(4) COMP
INTEGER Full word integer PIC S 9(9) COMP
DECIMAL(p,s) Packed decimal PIC S 9(7)V99 COMP-3
FLOAT(n) Floating point data (n < 22) COMP-1
(22<= 53) COMP-2
CHAR Fixed-length character PIC X(254)
VARCHAR Variable-length character L PIC S9(4) COMP
D PIC X(254)
GRAPHIC Fixed-length PIC G(n) DISPLAY
n=127
VARCHAR Variable-length L PIC S 9(4) COMP
T PIC G(127) DISPLAY
DATE Date PIC X(10)
TIME Time PIC X(8)
TIMESTAMP Date and Time PIC X(26)
QMF & SPUFI
QMF
Description: QMF = Query Management Facility
An IBM database management tool that allows extensive interactive query and report writing support. It runs under the control of the Interactive System Productivity Facility (ISPF), which in turn runs under Virtual Machine (VM/CMS) or Time Sharing Option (TSO) on host computers.
SPUFI
Description: SPUFI = SQL Processing Using File Input
A facility of the TSO attachment subcomponent that enables the DB2I user to execute SQL statements without embedding them in an application program.
– SPUFI requires that SQL statements to be executed be stored
in a data set. One way to use SPUFI is to create the data set prior to executing
– SPUFI. We can make any desired changes to the data set using editing
facilities provided by SPUFI before executing the statements stored in it.
– Alternatively, we can begin a SPUFI session and use the editing facilities of SPUFI to create a data set into which we can place our SQL statements.
– As SQL statements are processed by SPUFI, the results they generate
are stored in a data set. The stored data includes a copy of each SQL
statement followed by its results.
– For a SELECT statement, the results include both the data retrieved and the value of SQLCODE that is returned.
– By invoking the BROWSE option, we can cause the output data set to be displayed on the terminal. We can use the ISPF browse facilities to
move around within the displayed data.
– SPUFI also provides limited formatting options that we can use to
specify how the data in the output data set is displayed or printed.
These formatting options can be set using the SPUFI defaults panel.
Tuesday, November 29, 2011
BIND:
BIND takes DBRM as input & generate package & application plan. The package will be loaded to the directory. Plan will be loaded to sysplans.
Bind functions:
Ø Checks authorization for SQL statement
Ø Checks the syntax errors of SQL statements like
1. Missing co name in the select list & used in order by & group by
2. Mismatch columns host variables
3. Data type mismatch of columns & host variables
4. Indicator variables not declared
5. Data truncation.
BIND SUBCOMPONANTS/PARAMETERS:
- OPTIMIZER:
Ø 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.
- ISOLATION LEVEL:
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.
- RUNTIME SUPERVISOR:
Which is to oversee execution of SQL statements.
Statistics like no of tables, columns, indexes, keys
- PLAN/APPLICATION PLAN:
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.
PRECOMPILATION PROCESS
Pre compiler takes COBOL+DB2 program as input & generates DBRM which will be stored in userdefined PDS as separate member.
DSNHPC --- IBM supplied utility used for precompilation.
Precompiler functions:
Ø Separates SQL & COBOL statements
Ø Check SQL syntaxs
Ø Replace all SQL statements with host language call statements in the compiled program.
Ø Which generates timestamp tokens
Union & Joins
Ø 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
UNION/UNIONALL
SELECT CNO,CNAME FROM ITEM WHERE INO=20
JOINS:
Ø JOINS can be used to concatenate columns from one table or multiple tables.
Ø JOIN types are :
1. left outer join : which can be used to retrieve matching, non matching rows from leftside table
2. right outer join: which can be used to retrieve matching, non matching rows from right side table.
3. full outer join: which can be used to retrieve matching, non matching rows from both the tables.
4. self join or inner join : can be achieved by defining alias for the table.
EXPLAIN :
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.
Monday, November 28, 2011
Application Progamming(COBOL+DB2)
DCLGEN :
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.
Host variables:
Ø 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.
Exec sql
Inlcude custDCL
End-exec.
Ø Include & copy have the same functionality
SQLCODE :
Ø 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.
Sample program:
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
INCLUDE CUSTDCL
END-EXEC.
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
PROCEDURE DIVISION.
UPDATE CUST
SET CNAME = :HV-CNAME
WHERE CNO=:HV-CNO
MOVE SQLCODE TO WS-SQLCODE.
EVALUE WS-SQL-CODE
WHEN 88-SUCCESS
DISPLAY “SUCCESSFULLY UPDATED”
WHEN 88-NOTFOUND
DISPLAY “ RECORD NOT FOUND”
WHEN 88-FOREIGNKEYVOILATION
DISPLAY “ FOREIGN KEY VOILATION”
WHEN OTHER
DISPLAY “ ERROR OCCURRED IN UPDATE”
STOP RUN
END-EVALUATE.
STOP RUN.
SUBQUERY @ CO-RELATED SUBQUERY
Ø 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))
CO-RELATED SUBQUERY:
Ø 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)
0 -- max
1 – 2nd max
2 – 3rd max
3 – 4th max
SQL(Structured Query Language)
Ø DDL (Data Definition Language)
Create, alter, drop
Ø DML (Data Manipulation Language)
Insert, update, select & delete
Ø DCL (Data Control Language)
Grant, Revoke
Ø TCL (Transaction Control Language)
Commit, Rollback
Static insertion: Insert into cust(cno, cname, cloc) values (10, “xyz”, “hyd”)
Dynamic insertion: 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%”
Column functions:
Select max(sal) from emp
Select min(sal) from emp
Select avg(sal) from emp
Select sum(sal) from emp
To avoid duplicate rows : select distinct cno,cname from cust
To get total no. of rows : select count(*) from cust
VIEWS:
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:
Ø Data security
Ø Data correctness
Ø Logical data independence
Ø Part of the information can be visible to the sers
Ø Accessing can be faster.
DELETE RULES:
Ø Delete rules can be applied for delete command against Database.
Ø Delete rules are 3 types
1. on delete cascade – all matching child rows will be deleted automatically when we delete parent row.
2. on delete restrict – all matching rows will be restricted when we delete parent row which is default.
3. on delete set null – all matching child row will be set to null when we delete parent row.
Difference between PRIMARY KEY,INDEX,UNIQUE INDEX
PRIMARY KEY INDEX UNIQUE INDEX
1. Uniquely identified row. Record identified based on Records identified based on
the index the index
2. No duplicated rows, no Duplicate rows, null values No duplicate rows
null values are allowed
3. Can consist of single or Dan consist of single or Can consist of single or
multiple columns multiple columns columns
4. Which will be stored in Which is stored in SYSINDEX Which is stored in sysindex
SYSKEYS.CURSOR
To retrieve multiple rows for a given condition.
Let us take the following example:
Exec sql
Select cno,cname,cloc
into :hv-cno,:hv-cname,:hv-cloc
from cust where cloc =:hv-cloc
end-exec.
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
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.
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT CNO,CNAME FROM CUST
WHERE CNAME=:HV-CNAME
END-EXEC.
EXEC SQL
OPEN C1.
END-EXEC.
PERFORM UNTIL SQLCODE= 100
EXEC SQL
FETCH C1 INTO :HV-CNO,:HV-CNAME
END-EXEC
END-PERFORM.
EXEC SQL
CLOSE C1
END-EXEC
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
EXEC SQL
DECLARE C2 CURSOR WITH HOLD FOR
SELECT CNO,CNAME FROM CUST
WHERE CNAME=:HV-CNAME
FOR UPDATE OF CNAME
END-EXEC.
EXEC SQL
OPEN C1.
END-EXEC.
EXEC SQL
FETCH C2 INTO :HV-CNO,:HV-CNAME
END-EXEC
EXEC SQL
UPDATE CUST SET CNAME=”ABC” WHERE CURRENT OF C2.
EMD=EXEC.
EXEC SQL
CLOSE C1
END-EXEC
Saturday, November 26, 2011
Difference between Primary & Secondary Keys
PRIMARY KEY:
- Cannot contain Null values or duplicate rows.
- Cannot be updated.
- Can be defined as a foreign key key of in other table.
- only one primary key can be defined for one table.
FOREIGN KEY:
- Can contain NULL values or rows.
- Can be updated .
- which must be primary key of another table.
- multiple foreign keys can be defined for one table.
Primary Key & Foreign Key
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(
INO INTEGER,
INAME CHAR(15),
CNO INTEGER,
PRIMARY KEY IS INO,
FOREIGN KEY IS CNO
WITH REFERENCES CUST)
Subscribe to:
Posts (Atom)