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.