DB2 Tutorial For Software Developers (2 of 5)

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 :

  • 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

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

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.

Table spaces

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

Also read

Author: Srini

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

One thought

  1. Pingback: Calling Stored Procedure in DB2- best example | Srinimf

Comments are closed.