DB2 Tutorial - 1

Storage Group(STOGROUP) - A uniquely named collection of DASD ( Direct Access Storage Device ) volumes used to place the VSAM files containing the data.
- The tables and its indexes are actually stored in these VSAM files.
- One STOGROUP can have a maximum of 133 volumes.
- Storage group details will be stored in SYSIBM.SYSSTOGROUP table 

Syntax: 

CREATE STOGROUP storage-group-name

VOLUMES (Volume-1,volume-2…)

VCAT vcat-name

VCAT identifies the system integrated catalog facility for the storage group

Database - It is collection of information that is organized so that it can easily be accessed, managed and updated.
Database details can be stored in SYSIBM.SYSDATABASE table.

Create Database: To define the database

CREATE DATABASE database-name

STOGROUP storage-group-name

BUFFERPOOL buffer-pool-name

INDEXBP index-bp-name

INDEXBP is the bufferpool to place indexes.

Tablespace  - A VSAM file which stores the DB2 table data physically. Just like contents in the book, the data in this file is stored in fixed size pages.

One table space can contain one or more tables

One table space can be stored in more than one VSAM file.

Tablespaces are categorized into 3 types, they are

Simple – Table space is represented as pages and data will be stored in the form of pages.

Segmented – Tables space is represented as segments and data will be stored in the form of segments. Segment is nothing but set of pages.

Partitioned – Table space is represented as partitions and each partition can have only one table.

Syntax:

CREATE TABLESPACE tablespace-name

IN database-name

USING STOGROUP storage-group-name

Bufferpool - An area in the main storage where db2 stores pages fetched from tablespace. 

Bufferpool in detail - 

It is portion of a main memory space which is allocated by the database manager. The purpose of bufferpools is to cache table and index data from disk.

All databases have their own bufferpools. A default bufferpool is created at the time of creation of new database. 
Bufferpool can be multiples of 4k like 4k,8k,16k etc.

Table - A table is a data structure that organizes information into rows and columns. It can be used to both store and display data in the structured format.

- SYSADM and SYSCTRL authority is required to create a table. If the user id don't have this authority, then -551 error will be returned while creating CREATE TABLE.

Syntax:

CREATE TABLE table-name

( column1       datatype [not][null],

  Column2       datatype [not][null],

  Column3       datatype [not][null],

   .

   .

   .

 PRIMARY KEY(column-name))

 IN DATABASENAME.TABLESPACENAME

View is a table which can be derived from one or more tables based on selection criteria.
A maximum of 15 tables can be used to create a view.
A view can be created on single table or more than one table.

Syntax:

CREATE VIEW view-name AS

       (Select query)

ON DATABASENAME.TABLESPACENAME

Index is a key which is used to directly access a particular row.
INDEXSPACE is used to store indexes.
When the index is created, indexspace is created automatically.

Syntax:

CREATE UNIQUE INDEX index-name

ON table-name column-name

DDL Commands

CREATE

ALTER

DROP

ALTER is used to add/delete/modify any db2 object created by using CREATE

ALTER syntax:

ALTER TABLE table-name

ADD column-name declaration;

ALTER can be performed on any DB2 object

ALTER DB2-object-name

[Parameters newly added/modified]

DROP is used to drop the db2 objects created

Syntax:

DROP db2-object-name.

Ex. DROP TABLE table-name

DML Commands

SELECT

INSERT

DELETE

UPDATE

SELECT: To retrieve the data from one or more tables

SELECT * from table-name

INSERT: To insert the data into the table

INSERT INTO table-name(column-1, column-2..) VALUES(hostvar1,hostvar2..)

DELETE: To delete one or more rows from table

DELETE FROM table-name

WHERE conditions;

UPDATE: To update one or more rows from a table

UPDATE table-name

SET column-1 = value1,

    Column-2 = value2,

    .

    .

WHERE conditions

TCL – Transaction control language is used to control the transactions performed on the database.

COMMIT – Used to save all the transactions performed on the database

EXEC SQL

     COMMIT

END-EXEC

ROLLBACK – Used to revert back all the transactions performed on the database

EXEC SQL

     ROLLBACK

END-EXEC

DCL (Data control language) is used the control the data by giving/revoking the access for accessing the database based on the levels of users.

GRANT is used to grant the permissions to the user and also to add the additional permissions

Syntax: GRANT [statement] ON [db object] TO PUBLIC/group-of-users

Example: GRANT SELECT ON TABLE1 TO PUBLIC

REVOKE is used revert back the access which was granted earlier.

REVOKE SELECT TABLE1 ON PUBLIC

JOIN:

SELECT * FROM table1-name JOIN table2-name

INNER JOIN:

The similar values in both the tables will be displayed. For eg, i have emp table and dept table where emp_id is a common attribute in both.

In emp table, emp_id values are 101,102,103,104,105 and in dept table emp_id values are 101, 102, 103 and 104. So when you INNER join both the tables

using emp_id then four rows will be displayed with emp_id 101,102, 103 and 104 as they are common in both the tables.

SELECT col1,col2

FROM table1

INNER JOIN table2

ON joining cols

WHERE conditions

OUTER JOIN will join all the columns from left table and right table based on the conditions.

SELECT A-col-list, B-col-list FROM table-A OUTER JOIN table-B

WHERE condition

ON joining cols

Right outer join:

Displays all rows from the right table(table2) with the matching rows from the left table(table1). 

SELECT A-col-list, B-col-list FROM table-A 
RIGHT OUTER JOIN 
table-B

ON joining cols

WHERE condition

Left outer join:

Displays all rows from the left table (table1) and selected rows from the right table (table2)

SELECT cols-list FROM table-A WHERE condition LEFT 

OUTER JOIN

SELECT cols-list FROM table-b WHERE condition on Joining-cols