DB2 tutorial is really good for mainframe DB2 programmers. This tutorial specialty is you can read download and questions are useful for interviews and development.
Read my part-1 post on DB2 tutorial.
What is SQL?
- SQL – all data object manipulation, creation and use, involve SQL’s.
- DB2 objects – Database, Tablespaces & Indexspaces – creation & use, and other terminology’s associated with databases.
SQL or Structured Query Language is
- A Powerful language that performs the functions of data manipulation(DML), data definition(DDL) and data control or data authorization(DAL/DCL).
- A Non procedural language – the capability to act on a set of data and the lack of need to know how to retrieve it. An SQL can perform the functions of more than a procedure.
- The De Facto Standard query language for RDBMS
- Very flexible
SQL – Features :-
- Unlike COBOL or 4GL’s, SQL is coded without data-navigational instructions. The optimal access paths are determined by the DBMS. This is advantageous because the database knows better how it has stored data than the user.
- What you want and not how to get it
- Set level processing & multiple row processing
SQL – Types (based on the functionality)
- Data Definition Language (DDL)
-Create, Alter and Drop
- Data Manipulation Language (DML)
-Select, Insert, Update and Delete
- Data Control Language (DCL)
-Grant and Revoke
SQL – Types (Others)
- Static or Dynamic SQL
- Embedded or Stand-alone SQL
The following are the Operations that can be performed by a SQL on the database tables :
Topics dealt with, in DB2 objects
- Stogroup, Databases, Tablespaces (types, creation and modification)
- Indexspaces (creation and modification)
- Some more terms associated with tablespaces
- It is a collection of direct access volumes, all of the same device type
- The option is defined as a part of tablespace definition
- When a given space needs to be extended, storage is acquired from the appropriate stogroup
- A collection of logically related objects – like Tablespaces, Indexspaces, Tables etc.
- Not a physical kind of object – may occupy more than one disk space
- A STOGROUP & BUFFERPOOL (is buffer area used to maintain recently accessed table and index pages) must be defined for each database.
- Stogroup and user-defined VSAM are the two storage allocations for a DB2 dataset definition.
- In a given database, all the spaces need not have the same stogroup
- These are, in a sense, the most physical of various storage objects in DB2
- More than one volume can be defined in a stogroup. DB2 keeps track of which volume was defined first & uses that volume.
- Logical address space on secondary storage to hold one or more tables
- A ‘SPACE’ is basically an extendable collection of pages with each page of size 4K or 32K bytes.
- It is the storage unit for for recovery and reorganizing purpose
- Three Type of Tablespaces – Simple, Partitioned & Segmented
Simple Table space
- Can contain more than one stored table
- Depending on application, storing more than one Table might enable faster retrieval for joins using these tables
- Usually only one is preferred. This is because a single page can contain rows from all tables defined in the database.
- LOAD with replace option deletes all data
Segmented Table spaces
- Can contain more than one stored table, but in a segmented space
- A ‘Segment’ consists of a logically contiguous set of ‘n’ pages.
- Segsize parameter decides the allocation size for the tablespace
- No segment is allowed to contain records for more than one table
- Sequential access to a particular table is more efficient
- Mass Delete is much more efficientthan in any other Tablespace
- Reorganizing the tablespace will restore every table to its clustered order
- Lock Table on table locks only the table, not the entire tablespace
- If a table is dropped, the space for that table can be reclaimed with minimum reorg
Partitioned Table spaces
- Primarily used for Very large tables
- Only one table in a partitioned TS; 1 to 64 partitions/TS
- Num part parameter specifies the no. of partitions
- It is partitioned in accordance with value ranges for single or a combination of columns. Hence these column(s) cannot be updated
- Individual partitions can be independently recovered and reorganized
- Different partitions can be stored on different storage groups for efficient access.
Tablespace parameters to be specified for TS creation
- LOCKSIZE – indicates the type of locking DB2 performs for the given TS
- ANY – DB2 decides the starting page