DB2 Tutorials

1.       Introduction –

a)       Database - Access faster, Security high.

b)      File System - Access slow, Security low.

2.       Database Management System(DBMS) –

c)       Four elements – Data, Relationship, Constraints, Schema.

d)      Metadata – Index files, data dictionaries.

e)       Constraint – predicates that define correct database state.

f)       Schema – organization of data & relationship within database.

3.       Advantage of database – Redundancy reduced, Inconsistency avoided, Data can be shared.

4.       Database Model – Hierarchical, Network, Relational, Object-Oriented

a)      Terms – Relation (Table), Tuple (Row, Record) /Cardinality (Number of rows) / Attribute (column, field) / Degree (Number of columns) / Domain (Set of all possible values)

b)      Constraints - Accuracy & integrity of data, Domain Constraint, Entity integrity, Referential integrity, operational constraint.

c)       Normalization –  Process of building database structure to store data (1NF / 2 NF / 3 NF / BCNF (Boyce-codd Normal Form) / 4 NF / 5 NF)

d)      De-Normalization – Domain- Key Normal Form

e)       Relationship – one to one, one to many, many to many.

5.       DB2 DATA Types –

a)       NUMERIC Data - INTEGER (Whole number, not fraction), SMALLINT, DECIMAL (p, q), FLOAT.

b)      STRING Data - CHARACTER(n). VARCHAR(n), GRAPHIC(n), VARGRAPHIC(n), DATE, TIME, TIMESTAMP.

6.       Table Storage Structure –

a)       Set of one or more VSAM Dataset, also called as a page set.

b)      DB2 Objects - Storage group, Database, Tablespace, Index space,  etc.

c)       Two types – Index Space, Table Space. Storage -> Database -> Table space -> Tables, Views etc.

d)      Storage - Collection of same type of volume.

e)       Database - Collection of tables and its indexes, Tablespace & Indexspace.

f)       Tablespace - Contain one or more table, store on one or more VSAM file, divided into equal parts ‘PAGES (4K or 32K bytes)’, CREATE TABLESPACE - to create table space , 3 Types – Simple, Segmented, Partitioned

g)      INDEXSPACE - Contain exactly one Index.

h)      TABLE – Set of rows & Column logically related, qualifier to identify table name ‘SCHEMA’, Create Table – to create table, SYSADM, SYSCTRL authority, if not then -551.

i)        VIEW – table which can be derived from one or more tables (max up to 15 tables), Create View – to create view / SYSCAT.VIEWS all details.

Note – Indexspace automatically built any time an index is created (always 4 KB). Size of tablespace page is based on the page size of bufferpool specified in tablespace creation statement.

7.       Storage Group –

a) CREATE STOGROUP X VOLUMES(VOL1,VOL2) VCAT VSORDB.

b) Details in SYSIBM. SYSSTOGROUP.

c) SYSADM, SYSCTRL authority.

d) One storage group, max 133 volumes, if VOLUMES clause ignored than assigned automatically by SMS (Storage management system).

8.       Database –

a)       CREATE DATABASE X STOGROUP Y BUFFERPOOL Z INDEXBP A

b)      Details in SYSIBM. SYSDATABASE.

c)       DBADM, DBCTRL and DBMAINT authority.

d)      SYSDEFLT – default storage group, default 4k Bufferpool (4K, 8K, 16K etc.), INDEXBP (4k, 8K. 16K etc.)

9.       TABLESPACE –

a) CREATE TABLESPACE X IN database-name USING STOGROUP Y PRIQTY SECQTY ERASE FREEPAGE PCTFREE MAXPARTITION NUMPARTS PARTITION SEGSIZE BUFFERPOOL CLOSE COMPRESS LOCKMAX LOCKSIZE (ANY /TABLESPACE /TABLE /PAGE/ROW) MAXROWS SEGSIZE.

b) SIMPLE - only one VSAM ESDS, one or more table.

c) SEGMENTED - one VSAM dataset, each segment group of 4 to 64 pages, faster table scan and ability to lock a single table within tablespace.

c) PARTITIONED - table divided among tablespace partitions with each partition stored as separate VSAM ESDS dataset.

10.   TABLE –

a) Set of logically related rows and column.

b) Create TABLE. SYSADM, SYSCTRL authority.

c) Data Types - IS NULL, IS NULL WITH DEFAULT (DB2 assign default value if no value given while inserting), LIKE Parameter (to create new table same as of existing one).

11.   VIEW –

a) Read only - View data only, READONLY column ‘Y’ in SYSCAT.VIEWS.

b) Updatable - View should be derived from single base table and update will be in base table, real table that physically exists, data in MVS files, VSAM datasets.

c) WITH CHECK OPTION  - Insert & update operations against view are to be checked for view defining condition.

12.   Index Storage Structure –

a) Index are physically stored in its index space.

b) Storage group -> Database ->   Index Space -> Index.

c) CREATE INDEX. SYSADM, SYSCTRL authority.

13.   Index –

a) Candidate Key - Primary (cannot have NULL) & Alternate Key (Remaining Key).

b) Foreign Key - Attribute of one relation primary key of another relation.

C) Unique - Can have one NULL.

d) Clustered - Sorted both logically & physically. Only one per table.

e) Composite - More than one column.

f) Candidate Key - Attribute that can uniquely identify a row in a table.

h) Disadvantage of using Index – Occupy extra space as index component also occupy space.

14.   Pre-compilation Process –

a) Comment SQL statement and replace them with COBOL call statements.

b) EXEC SQL, END-EXEC. Generate CONTOKEN, PRECOMPILER, DBRM, PLAN (Object code DB2.

c) BIND - IKJEFTO1, checks table statistics, check for some of syntax error of SQL statement. Load Module (Run time Supervisor / Plan / Object code of COBOL) <- Link Edit Process.

15.   BIND Process –

a) Build access path, DB2 optimizer, Checks for Error using DB2 Cataloge, Syntax & authorization checking. Access strategy for each SQL statements.

b) PACKAGE - optimized access path, contain one or more DBRM, non-executable.

c) PLAN - Executable component, best access path.

d) VALIDATE - Control handling of objects/ two options – RUN, BIND.

e) MEMBER (DBRM Name)

f) ISOLATION - CS/RR/UR/DR.

g) RELEASE - to release acquired lock on DB2,

k) LIB - Library used to store DBRM’s.

l) RUNTIME Supervisor - responsible to validate the time stamps of COBOL and DB2 object codes/ -818 abend), Data Manager, Buffer Manager.

BIND Parameters – Isolation Level, Owner, Qualifier, Collection ID (Group of packages, same DBRM bound to different packages), Release, Acquire (Allocation / Use), Release (Deallocate / Commit), Explain (Yes / No, if DB2 to store information of access path), Member, Library,  Package List, Qualifier, Validate (Authorization BIND), FLAG (Error, Critical etc.) ENABLE or DISABLE

16.   DB2 Catalog & Directory –

a)       Catalog (Accessible by means of SQL).

b)      Directory (not accessed by SQL Statement).

c)       Catalog – Approx. 30 tables, System & User tables, Access path, check authorization, Validate BIND requests etc.

d)      SYSIBM.SYSCOLUMNS - Length and data type of each column in existing table.

e)       SYSIBM.SYSTABLES - Create a table and a row is inserted.

f)       SYSIBM.SYSPLANAUTH - BIND a DBRM into plan, a row is inserted.

g)      RUNSTATS utility gathers statistics about the database and updates tables in catalog / SYSIBM.SYSDBRM.

h)      SYSIBM.SYSSTMTDB2 - DBRM bound to PLAN.

i)        SYSIBM.SYSPACKSTMT - DBRM bound to Package.

j)        SYSIBM.SYSVIEWS

Note – When an Index is dropped DB2 examine catalog, find out dependent packages and marked them invalid.

17.   Isolation Level –

a) Repeatable Read - Most restrictive, complete table locked, all referenced rows locked.

b) Read Stability - All qualifying rows locked, txn scan 1000 rows and retrieve 10 rows, lock will be placed on 10 rows.

c) Cursor Stability – Default, lock one record currently referenced by Cursor.

d) Uncommitted Read - No record locking.

18.   DB2 Program Execution Process – Batch Environment (IKJEFT01), Online Process (after successful BIND, NEW COPY to be issued in CICS environment). RCT entry for program otherwise -922.

19.   Host Variable –

a)       Equivalent data types in COBOL & DB2. S9 (8) COMP – INTEGER, S9 (4) COMP – SMALLINT, X (n) – NUM (n), X (8) – Time, X (10) – Date, X (26) – TIMESTAMP.

b)      DCLGEN – generate COBOL copybook for specific table, Column name remain same, but underscore replaced by Hyphens, EXEC SQL INCLUDE table-copybook END_EXEC, expanded during compilation process.

20.   SQLCA – SQL Communication Area, EXEC SQL INCLUDE SQLCA END-EXEC. Information like return code, error code etc. SQLCODE, SQLERRD (3) <- contain number of rows executed by given operation, length of SQLCA is 136.

21.   CURSOR –

a)      DECLARE - Query Syntax, EXEC SQL DECLARE cursor-name NO SCROLL, SCROLL DYNAMIC, STATIC CURSOR (WITH/ WITHOUT HOLD) FOR SELECT-STATEMENT FOR UPDATE OF column-list FOR FETCH ONLY END_EXEC.

b)      OPEN - Execute Query. EXEC SQL OPEN cursor-name USINF host-variables END-EXEC.

c)       FETCH - FETCH cursor-name INTO: Host –variable-col1 :Host-variable-col1-NI

d)      CLOSE - CLOSE cursor-name.

e)       SINGLETON Select – Select one row only without where clause (INTO Clause, Fetch First Row Only).

Ex - EXEC SQL DECLARE A CURSOR FOR SELECT NAME, SEQ FROM X WHERE SEQ = :WS_SEQ FOR UPDATE OF NAME END_EXEC.

22.   NULL indicator –

a)       Select query fetching NULL values and NULL indicator not mention then -305.

b)      ‘-NI’ in end of NULL field name. Null Indicator Value -> -1(variable contain NULL, 0 (retrieved column has proper value), -2 (Contain NULL due to numeric or arithmetic conversion error,   > 0 (Truncated value retrieved). 

c)       Value absent in the column and if column declaration ‘NOT NULL’ then will not allow null value in it. NULL indicator automatically generated for NULL columns while generating DCLGEN. Syntax – HOST VARIABLE-NI & S9(04) COMP. While inserting NULL value in Column move ‘-1’ to NULL indicator.

23.   Cursor Types –

a)      Read-Only - FOR FETCH ONLY,

b)      Updatable - FOR UPDATE OF, Field name in declare statement, use always when updating with a cursor.

c)       WITH HOLD OF - Cursor remain open after COMMIT, By default DB2 closes a cursor on COMMIT).

d)      WHERE CURRENT OF - Will pick current row to update, use it whenever delete single row using cursor.

24.   DB2 Statements in COBOL Program – DDL/DML/DCL/ TCL (Transaction Control Language)

a)      DDL (Data Definition language) – CREATE / ALTER / DROP

b)      DML (Data Manipulation Language) – SELECT / INSERT / UPDATE / DELETE

SELECT (Fetch first 20 rows only – to fetch limited records)

c)       DCL (Data Control Language) – GRANT /REVOKE

d)      TCL (Txn Control Language) – ROLLBACK/COMMIT

25.   SQL –

a)       DROP TABLE X; (All indexes & views defined for those tables are also automatically dropped).

b)      CREATE TABLE X (last-name VARCHAR (30) NOT NULL or NOT NULL WITH DEFAULT);

Note – IS NOT NULL (you must provide value) / IS NOT NULL WITH DEFAULT (if null system enter default value)

c)       DECLARE – Similar to Create statement, except that it is used to create temp tables. 

d)      INSERT into X (Column-name 1,) VALUES (‘Value1’,)

e)       DELETE FROM order-table WHERE id =100;

f)       GRANT SELECT, UPDATE ON X TO USER WITH GRANT OPTION.

g)      Like ‘%im’ (will take SLIM etc.) / Like ‘_im’ (will take TIM, KIM etc.)

h)      ALTER TABLE X ADD COLUMN A INT NOT NULL, ADD PRIMARY KEY(contact-id);

Note – New columns can be added, primary or foreign key specification can be added or removed. Alternate key specification cannot be changed. Deletion, change to width or data type of an existing column not possible. You cannot Alter an Index, have to drop and recreate, can alter Buffer pools, Tables, Tablespace and views.

i)        GROUP BY/ ORDER BY (Default Ascending else write DESC) -  Select first-name, SUM(sales) FROM X GROUP BY first-name HAVING SUM(sales) > 10 ORDER BY SUM(sales) DESC;

j)        Note – HAVING is used to eliminate groups just as WHERE is used to eliminate rows.

k)      UNION – Combine two sets of rows into single set composed of all rows in either or both of two original sets / same no. of column, same data type or convertible to same data type /  UNION (remove duplicates) / UNION ALL (retains duplicates) / INTERSECT (only in both query), EXCEPT (in 1^st query not in second) ß like Union.

l)        JOIN - CROSS / INNER (Only matched value get selected) /OUTER (Unmatched values of a table get   selected) / RIGHT OUTER JOIN (Right table whole selected) / LEFT OUTER JOIN (Left table whole selected) / FULL OUTER JOIN

Note – INNER Join is a CROSS join with same result rows removed by a condition in Query, Max no. of tables that can be joined ‘15’ / EQUIJOIN (result containing two identical columns, one of these column is eliminated then Natural JOIN.

m)    AGGREGATE FUNCTION - MIN / MAX / AVG / SUM / COUNT

26.   DB2 SQL Return Codes –

= 0, Execution was successful

0 >, Execution was successful with warning

< 0, Execution was not successful

a)       +000 - Successful Execution

b)      +100 - Row not found for fetch

c)       -180 – Date Format Error

d)      -181 – Date Interval Values

e)       -305 – NULL Value Exception

f)       -310 - Missing Decimal Values

g)      -313 – Mismatch between number of host variable & column selected

h)      -502 – Cursor not opened

i)        -503 - Cursor already opened

j)        -532 – Foreign Key Violation

k)      -904 - Unavailable resource

l)        -911 - Current unit of work has been rolled back due to deadlock or timeout (with rollback)

m)    -913 - Unsuccessful Execution caused by deadlock or timeout (without rollback)

n)      -922 - Authorized Failure

o)      -803 - Inserted / updated value invalid, no 2 rows can be duplicate

p)      -805 - Program not found in plan

q)      -811 - Select statements has resulted in retrieval of more than one row

r)       -818 - Consistency tokens in DBRM and local modules are different

s)       -160 - Empty file open in input mode.

t)        Unavailable - Empty file open in output mode.

27.   TABLE Loading

a)       COPY Pending – Image Copy Job fails while copying data to a tape or DASD, take image Copy, REPAIR Utility with NO COPYPEND, Force Start Tablespace.

a.       REPAIR SET TABLESPACE <x> NO COPYPEND

b.       START DATABASE <X> SPACE <Y> ACCESS(FORCE)

b)      CHECK Pending – Referential integrity violation (Child table loaded before parent table), Table loaded with ENFORCE NO Option (load utility did not perform constraint checking), REPAIR Utility with NO CHECKPEND, Force Start tablespace.

LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE LOAD DATA REPLACE YES LOG NO NOCOPY PEND INDDN SYSREC00 INTO TABLE

For REPLACING Data – REPLACE YES

For APPENDING Data – RESUME YES

28.   Multiple row fetch

DECLARE CURSOR X WITH ROWSET POSITIONING, SQLERRD(3)

29.   Simple TABLESPACE to Partitioned by Growth TABLESPACE

User managed (VCAT defined) / DB2 Managed                                                 

CREATE STOGROUP VASUSG VOLUMES (*) VCAT DB2 DATACLASS USEDEXT

ALTER TABLESPACE U170.URSPRO USING STOGROUP VASUSG;

ALTER INDEX U.URSPR01 USING STOGROUP VASUSG;

ALTER TABLESPACE U170.URSPR01 MAXPARTITIONS 2;

‘RW,AREO’ (Advisory reorg pending)

Reorg, SHRLEVEL REFERENCE or CHANGE (to apply definition change). [Not SHRLEVEL NONE]

SYSPENDINGDDL

30.   Level of locking – Page, Table & TABLESPACE (DB2 decides the starting page), hierarchy to be maintained (for page first get a Tablespace Lock). Lock [Handled using IRLM(IMS Resource Lock Manager)], Latch (Without IRLM).

LOCK	Access Required	Access Allowed to Others
S (SHARE)	Read Only	Read Only
U (UPDATE)	Read with Intent to Update	Read Only
X (EXCLUSIVE)	Update	No Access
IS (INTENT SHARE)	Read Only	Update
IX (INTENT EXCLUSIVE)	Update	Update
SIX (SHARE INTENT EXCLUSIVE)	Read or Update	Read Only

31.    CATALOUGE Tables –

SYSIBM.SYSCOLDIST

SYSIBM.SYSCOLSTATS

SYSIBM.SYSCOLUMNS

SYSIBM.SYSINDEXES

SYSIBM.SYSINDEXPART

SYSIBM.SYSTABLES

SYSIBM.SYSTABLESPACE

SYSIBM.SYSTABSTATS

32.   DB2 DBA Activities

a)       Assisting with creation & modification of primary storage structures(tablespace) & primary object (Tables, views, indexes)

b)      Implement data security guidelines, data available only to those authorized to have access.

c)       Regular DBA activities like Access related issue, BIND issues, Space issues etc.

d)      Proactively monitor performance diagnose problems & tune queries & database.

e)       Helped in redesigning many existing tables, which in turn improve the over all system & hence reduced the cost.

33.   Difference between SQL code -805 & -818 à Pgm is directly bound to Plan (Compile & link edit new DB2 pgm, not did BIND, ‘- 805’, next time same then ‘-818’ / Pgm is directly bound to Package (same above, ‘-805’, next time also ‘-805’.

34.   To check Pgm is bound to which all tables or Pgm is using which all tables.

SELECT TTNAME FROM SYSIBM.SYSTABAUTH WHERE GRANTEE TYPE = ‘P’ AND GRANTEE IN (LIST OF PGMS) WITH UR;

35.   What is better subquery or Join and why? à Join is better as for subquery intermediate memory is required to store result of subquery.

36.   SOX Amends

37.   If DB2 is working slow what are the problems? How you check if which SQL query is taking long time?

38.   If we can unload multiple tables?

39.   Interactive access to Data – SPUFI (SQL processing using file input) , DB2I (DB2 interactive) , QMF (Query management facility)

40.   DB2 application running under IMS/VS or CICS/VS can access both DB2 data (via SQL Statements) and IMS data (via DL/I calls). CICS and IMS Txn manager.

Online Programs (TSO, CICS or IMS/DC)

Batch Programs (TSO, IMS/DB)

DB2 GOVERNOR / DB2 SUBSYSTEM / Distributed Data Facility / DB2 Attachments, DSN Threads / System Services Component [SMF (System management facility) / GTF (Generalized Trace Facility) / DB2PM (DB2 Performance monitor - that produces batch reports and interactive graphics from SMF or GTF)]

Locking Services Component – IMS Resource Lock Manager(IRLM) / Database Services Component [Relational Data system(RDS), the manager and Buffer Manager – Buffering technique to minimize amount of physical I/O]

41.   Select MAX(COL) FROM TABLE WHERE COL < (SELECT MAX(COL) FROM TABLE);

Select MAX(COL) FROM TABLE WHERE COL NOT IN (SELECT MAX(COL) FROM TABLE);

42.   DEADLOCK – Situation in which two or more transactions are in simultaneous wait state, each one waiting for one of other to release a lock before it can proceed.

TIMEOUT – Unavailability of given resource.

43.   Data Consistency Utilities

CHECK (Check referential integrity, index etc.) / REPAIR (Test DBD information and synchronize DB2 directory and catalog) / REPORT / DIAGNOSE.

Backup and Recovery Utilities

COPY (create an Image Copy backup dataset) / MERGECOPY / QUIESEC / RECOVER

Data Organization Utilities

LOAD / REORG

CATALOG Manipulation Utilities

CATMAINT / MODIFY / RUNSTATS / STOPSPACE

44.   If we can define UNION / UNION ALL in Cursor? – Yes

45.   Master Catalog à VSAM Dataset, Non VSAM Dataset, USER Catalog (VSAM Dataset, NON VSAM Dataset)