DB2 for z/OS
IBM's Relational Database
DB2 is IBM's Relational Database Technology

Relational Database Management Systems (RDBMS) are used to manage a massive amount of data
being simultaneously accessed by thousands of people, devices, and other applications.

Structured Query Language (SQL) is the common relational database programming language. Once you have learned to use SQL, the knowledge directly applies to other relational database management systems.


Understanding SQL is the foundation for learning any relational database management system

A popular z/OS RDBMS is DB2

There are 4 categories of SQL:

  1. DML -- Data Manipulation Language
    • SELECT, UPDATE, INSERT and DELETE (CRUD applications)
  2. DDL -- Data Definition Language
    • CREATE, ALTER and DROP data base structures (DB architecture)
  3. DCL -- Data Control Language
    • GRANT and REVOKE privileges (security)
  4. TCL -- Transaction Control Language
    • COMMIT and ROLLBACK work

While DML and TCL are the primary SQL categories used by developers, DDL and DCL are the primary SQL categories used by the Database Administrator, DBA.

Here are some excellent sources to learn the capabilities of SQL applied to all relational database management systems:


DB2 for z/OS SQL can be executed a variety of ways
  • Programming Language APIs
  • ISPF panels
  • JCL
  • Many eclipsed based GUI tools with relational DB workbench
  • FTP

DB2 for z/OS Data Structures
  • Storagegroup - a name associated with a collection disk volumes
  • Database - a name associated with a collection of DB2 tablespaces and indexspaces
  • Tablespace - VSAM Linear Data Set formatted to contain DB2 tables
  • Table - a name to reference the rows and columns of DB2 controlled data in a tablespace
  • Indexspace - VSAM Linear Data Set formatted to contain an index for a DB2 table
  • Index - a name to reference the indexed data for DB2 table access

The following is a diagram of DB2 for z/OS Data Structures.

A few potentially confusing DB2 for z/OS words are Storage Group and Catalog.

  • SMS can define storage group names where the SMS storage group name is a collection of disk volumes labels for new data set allocations.
  • DB2 can create storage group names where the DB2 storage group name is a collection of disk volume labels exclusively for new DB2 tablespace and indexspace allocations without any dependency on SMS.
  • If a DB2 storage group name volume label is asterisk *, then DB2 is told that SMS ACS routine will manage new allocations and use SMS storage group defined volume labels for new DB2 tablespace and indexspace allocations.
  • z/OS has a Master Catalog and numerous User Catalogs containing data set names and a disk volume label for each data set name.
  • DB2 has its own Catalog. The DB2 Catalog is a manifest of the DB2 controlled resources, e.g. metadata which is data about data such as connecting DB2 table name with a DB2 tablespace name and finally a z/OS VSAM data set name.
  • However, DB2 uses the z/OS Master Catalog and User Catalogs for locating the VSAM data set names that are DB2 controlled tablespaces and DB2 indexspaces.

Relationship between DB2 and VSAM data set names

  • A DB2 DDL CREATE TABLESPACE statement results in new allocation of a VSAM Linear Data Set (LDS).
  • The DB2 formatted VSAM LDS name has a specific data set name pattern.
    1st DSN qualifier , a.k.a High Level Qualifier (HLQ), is determined by a VCAT operand on a DB2 DDL create statement.
    2nd DSN qualifier for the VSAM cluster is always DSNDBC.
    3rd DSN qualifier for the VSAM cluster is the DB2 data base name. The DB2 database name is just a name that represents a collection of tables in tablespaces.
    4th DSN qualifier for the VSAM cluster is an abbreviated 8 characters associated with a tablespace name or indexspace name. The DB2 Catalog keeps a cross reference of the abbreviated 8 characters in the VSAM cluster data set name and the full tablespace or indexspace name.

DDL steps used by a DBA to create a DB2 database table follows: