DB2 Database Objects

DB2 Database Objects

1. Tables:

1. They are used to save user data in row and column format.
2. CREATE TABLE Command is used to create table, while creating table we can also specify explicitly to which tablespace it should be saved using IN clause as shown below.
1. CREATE TABLE artists
   (
   artno SMALLINT NOT NULL,
   name VARCHAR(50),
   picture BLOB(2M) NOT LOGGED COMPACT
   ) IN dms01
3. Table artists will be saved in tablespace dms01.
4. All the Indexes and LOB data can be saved in different tablespace as shown below
1. CREATE TABLE artists
   (
   artno SMALLINT NOT NULL PRIMARY KEY,
   name VARCHAR(50),
   picture BLOB(2M) NOT LOGGED COMPACT
   )
   IN dms01
   INDEX IN dms02
   LONG IN dms03
   CREATE INDEX idx_name ON artists (name)
5. Table artists will be saved in tablespace dms01, Index of PK and idx_name will be saved in tablespace dms02 and BLOB datatype column value will be saved in dms03.
6. Condition is that all the three tablespaces has to be DMS in nature.
7. ALTER TABLE command can be used to
1. The ALTER TABLE statement modifies existing tables by:
   Adding
    One or more columns to a table
   Adding or dropping
    Primary key
    One or more unique, referential or check constraint definitions
    Drop table restriction
    Partitioning key (DPF)
   Altering columns
    Drop a column
    Alter column data type (to compatible data types)
    Alter column nullability
    Length of a VARCHAR column
    Reference type column to add a scope
    Generation expression of a generated column
    One or more check or referential constraints attributes
   Change table attribute
    DATA CAPTURE, PCTFREE, LOCK SIZE or APPEND MODE.
   Setting table attribute “NOT LOGGED INITIALLY"

2. Partitioned Table:

1. Like Tablespace, Table is also an logical entity. But rows with each table are physical entity as they are physically stored inside Page.(smallest unit of DB2 Database).
2.  When Rows of tables are distributed across multiple containers(storage location) then such tables are called as Partitioned Table.
3. Boundaries are defined for each such partition.
4. CREATE TABLE sales(sale_date DATE, customer INT, …)
   PARTITION BY RANGE(sale_date)
   (
   STARTING MINVALUES IN TBSP1,
   STARTING '3/1/2000' IN TBSP2,
   STARTING '6/1/2000' ENDING '9/30/2000' IN TBSP3
   );
5. In the above Query table sales is partitioned into three parts based upon the column sales_date.
6. All rows before 3/1/00 will be saved in tablespace TBSP1
7. All rows from 3/1/00 till 6/1/00 will be saved in tablespace TBSP2
8. All rows from 6/1/00 till 9/30/00 will be saved in tablespace TBSP3

3. IDENTITY Column

1. If any column of a table is declared as IDENTITY column, then that column will autogenerate UNIQUE numeric values for itself.
2. Generally Primary Key column is declared as IDENTITY column.
3. IDENTITY Column are like sequence object but sequence objects are not dependent on table where as IDENTITY Column are.
4. There are two approaches to declare as column as IDENTITY column
1. GENERATED ALWAYS: Numeric value are automatically generated by DB2 and user can not provide it own value for this column.
   
2. GENERATED BY DEFAULT:If user did not provide it own value for this column, only then numeric value are automatically generated by DB2. Uniqueness is not guaranteed 
   
5. CREATE TABLE inventory (
   partno INTEGER
   GENERATED ALWAYS AS IDENTITY
   (START WITH 100, INCREMENT BY 1),
   description CHAR(20) );
   INSERT INTO inventory VALUES (DEFAULT,'door'); ---> inserts 100,door
   INSERT INTO inventory (description) VALUES ('hinge'); ---> inserts 101,hinge
   INSERT INTO inventory VALUES (102,'window'); ---> ERROR, value always generated
   COMMIT;
   INSERT INTO inventory (description) VALUES ('lock'); ---> inserts 102,lock
   ROLLBACK;
   INSERT INTO inventory (description) VALUES ('frame'); ---> inserts 103,frame
   COMMIT;
6. We can use IDENTITY_VAL_LOCAL() function to retrieve the
   generated identity value.
   Example 1:
   INSERT INTO T1 (ID, ...) VALUES (DEFAULT, ....);
   SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1;
   Example 2:
   INSERT INTO PARENT_TABLE (PK_ID, ... )
   VALUES (DEFAULT, ...);
   INSERT INTO CHILD1_TABLE ,...,FK_ID,...)
   VALUES (...., identity_val_local(),...);
   INSERT INTO CHILD2_TABLE ,...,FK_ID,...)
   VALUES (...., identity_val_local(),...);

4. SEQUENCE

1. Unlike IDENTITY Column,Sequence is an Database Object for generating unique values.
2. CREATE SEQUENCE myseq
   START WITH 1
   INCREMENT BY 1
   NO CYCLE
3. NEXTVAL FOR <seqname>:- It displays Next value within the sequence.
   PREVVAL FOR <seqname>:- It displays Current value of the sequence.
4. Example:- INSERT INTO t1 VALUES (nextval for myseq, ...)
   SELECT prevval for myseq FROM sysibm.sysdummy1

5. INDEXES

1. Following are the types of indexes available in DB2
1. ascending or descending
2. unique or non-unique
3. bi-directional (no storage overhead, see notes)
4. compound
5. cluster
6. include columns
2. By default all the indexes generated are Ascending Order
3. They can be unique or Non unique, it depends on the column on which Index is being generated.
4. Bi-directional Indexes allow application to search from both end(top-bottow).
5. Compound Indexes consists of multiple column as Primary Key.
6. A clustering index determines how rows are physically ordered (clustered) in a table space. Clustering indexes provide significant performance advantages in some operations, particularly those that involve many records. 
7. When an index contains include columns, it may help performance. If the include column is involved in a
   SELECT that only selects columns from the index, and the index is used
   to access the rows, by having the column INCLUDED in the index, you can
   avoid accessing the table data.

6. VIEWS

1. A view provides a different way of looking at the data in one or more
   tables; it is a named specification of a query result
2. DB2 uses the CHECK OPTION to specify a constraint to be
   enforced on every row being inserted or updated through the
   view.
3. CREATE VIEW emp_view2 (id, empname, deptno)
   AS (SELECT empno, lastname, workdept
   FROM employee WHERE dept = 10)
   WITH CHECK OPTION;
4. Condition "deptno = 10" will be checked for insert and update operations
   against this view
5. CREATE VIEW emp_view3 AS
   ( SELECT empno, empname, deptno FROM emp_view2
   WHERE empno > 20 ) WITH CASCADED CHECK OPTION;
6. Conditions deptno = 10 AND empno > 20 will be checked for insert and
   update operations against this view
7. CREATE VIEW emp_view4 AS
   ( SELECT empno, empname, deptno FROM emp_view3
   WHERE name = 'Smith' ) WITH LOCAL CHECK OPTION;
8. Only condition name='Smith' (defined in emp_view4) is checked for inserts
   and updates
7. 
   

Hierarchy of Storage Units

Hierarchy of Storage Units

1. In DB2 smallest storage units is Page, then comes extent, container and largest of all is tablespace.
2. 
3. Lets start with Page:-
1. Page is considered to be the smallest unit for storage, which is available in 4K,8K,16K and 32K size.
2. Basic Page structure consists of
1. Page Header(Fixed Length):- It stores location of continuous free space(i.e its offset), free space(Hole Chain) and total amount of free space within the page. 
2. Page Trailer(Variable Length):- It stores an array of pointers, pointing to the address of the starting point of each row saved within the page. Value of this pointers is called as RBA(Relative Byte Address). As the size of RBA is 2 bytes, page is limited to save only 255 entries(rows).
3. Page Content(Variable Length):- It stores row entries of either table or index but not of both.
3. When there is more than 4 bytes of free space available in Page, it is called as Large Hole and maintained by Hole chain List. If free space available is less than 4 bytes its not maintained.
4. 
4.  Next is Extents:-
1. An Extent is defined as continuous collection of pages.
2. All the Pages in an Extent must belong to single Table or Single Index only.
3. It's basic IO unit for writing data from bufferpool(in RAM) to database files(on hard disk).
4. Page Size and Extent Size are declared during the creation of tablespace and can't be modified later.
5. Typically an OLTP environment will have small pages (4k,8k), and a small number of pages per extent: 4,6,16. Decision Support and Data Warehousing applications will have larger pages (16 or 32K), and larger extents (16, 32, 64 or 128). The extent size should be chosen based on the table size and anticipated usage, depending on whether the application is query intensive, transaction intensive or a mixture of both. Smaller tables are handled more efficiently with smaller extents.
5. Next is Containers:-
1. Containers define the storage path(location) on the OS where data is being saved. It also defines the directory structure in case of partition storage / distributed storage configuration.
2. In Automatic Storage the database has a number of storage paths associated with them, and the tablespaces (and hence containers) in the database are assigned and allocated based on those storage paths by DB2. 
3. In this way, you don’t need to explicitly define the containers. They will grow automatically in their directory paths, and if the directory paths are on different disks, the table data will be spread across these disks. DB2 will create new containers if they are needed.
4. Automatic storage gives the best options of DMS (speed and flexibility) and SMS (ease of administration)
6.  Next is Tablespace:-
1. Tablespace can be defined as collection of containers. Containers basically provide location to save data.
2. For each tablespace, you have to define the page size, and each tablespace must have access to a bufferpool of the same page size, and a system temporary tablespace of the same page size.
3. You have to specify which buffer pool the table space is associated with.
4.  Extents are stored within containers. Depending on how the containers are set up (either with Automatic Storage, DMS or SMS) there is a path associated with each container to part of the physical file system. If the tablespace (and hence containers) are on a disk array, the extent size should be set to the disk stripe size
5.  
6. There are three types of tablespace in DB2; Regular, Large, and Temporary.
7. A default database will have 1 bufferpool and 3 tablespace as follows
1. SYSCATSPACE Tablespace(REGULAR)
2. TEMPSPACE1 Tablespace(TEMPORARY)
3. USERSPACE1 Tablespace(LARGE)
4. IBMDEFAULTBP