Mainframe: DB2 Tutorial For Software Developers (2 of 5)

(DB2+Tutorial)

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 :

  • Select
  • Union
  • Join

Topics dealt with, in DB2 objects

  • Stogroup, Databases, Tablespaces (types, creation and modification)
  • Indexspaces (creation and modification)
  • Some more terms associated with tablespaces

DB2 Objects:-

Storage group

  • 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

Database

  • 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.

Tablespaces

  • 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 Tablespace

  • 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 Tablespaces

  • 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 Tablespaces

  • 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
  • Page
  • Table
  • Tablespace
  • ANY – DB2 decides the starting page

Read my part-3 post on DB2 tutorial.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.