DB2 UTILITIES

                                    

LOAD:

Use LOAD to load one or more tables of a table space. LOAD loads records    into the tables and builds or extends any indexes defined on them. If the   table space already contains data, you can choose whether you want to add   the new data to the existing data or replace the existing data. The loaded  data is processed by any edit or validation routine associated with the     table, and any field procedure associated with any column of the table.

REORG:

The REORG online utility reorganizes a table space or index to improve access performance and reclaim fragmented space. In addition, the utility   

can reorganize a single partition of either a partitioned index or a        

partitioned table space. You can specify the degree of access to your data  

during reorganization. If you specify REORG UNLOAD ONLY, the data is        

unloaded in a format that is acceptable to the LOAD utility of the same     

DB2 subsystem.                                                               

Output: If the table space or partition has the COMPRESS YES attribute,    

then the data is compressed when reloaded. If you specify the              

KEEPDICTIONARY option of REORG, the current dictionary is used; otherwise  

a new dictionary is built.                                                 

                                                                           

REORG TABLESPACE can be executed on the table spaces in the DB2 catalog    

database (DSNDB06) and some table spaces in the directory database         

(DSNDB01).  It cannot be executed on any table space in the DSNDB07        

database.                                                                  

                                                                            

Authorization Required: To execute this utility on a user table space or   

index, the privilege set of the process must include one of the following: 

                                                                            

o   REORG privilege for the database                                       

o   DBADM or DBCTRL authority for the database                             

o   SYSCTRL authority                                                      

o   SYSADM authority.                                      

LOCKS

         *  DB2 uses locks to control concurrence and prevent lost updates.

         *  Locks prevent one program from accessing data that another  program has changed but not yet committed.

         *  DB2 acquires all locks implicitly under DB2 control.

         Lock can be obtained  ==>

            *  Implicitly - by the system

            *  Explicitly - by the user

         There are 2 kinds of locks :

            Shared             S

            Exclusive         X

SHARE MODE Lock :

            *  Is used for querying consistent data

            *  Prevents other users from updating data, you are reading

            *  Prevents other users from putting EXCLUSIVE mode lock  on the table

            *  Lets other users read the data

            *  Locks the whole table

         Example  :

            LOCK TABLE EMPLOYEE

                        IN  SHARE  MODE ;

            EXCLUSIVE MODE lock :

            *  Is used for changing data

            *  Prevents other users from updating data, you’re reading

            *  Prevents other users from putting ANY lock on the table

            *  Lets other users read the data

            *  Does not guarantee current data

            *  Locks the whole table

         Example :

         LOCK TABLE EMPLOYEE       IN                          EXCLUSIVE MODE ;

Locks can be obtained implicitly or explicitly.

         Implicit lock is issued by the system itself.

         DB2 provides facilities for explicit locking :

         *  The SQL statement LOCK TABLE

         *  The ISOLATION parameter on the BIND command

         *  The tablespace LOCKSIZE parameter

         *  The ACQUIRE/RELEASE parameters on the BIND command

DCLGEN

            DCLGEN is a facility that is used to generate SQL statements that describe a table or view. These table descriptions are then used to check the validity of other SQL statements at precompile time. DCLGEN  uses table declaration to build a host language structure, which  is used by the DB2 precompiler  to verify that correct column names and data types have been specified in the SQL statement

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.

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:

1. 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.

2. 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.

3. RUNTIME SUPERVISOR:

Which is to oversee execution of SQL statements.

Statistics like no of tables, columns, indexes, keys

4. 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:

Ø  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.

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

 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        – 2^nd max

2        – 3^rd max

3        – 4^th 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.