Relational Database Administration (DB2 for z/OS)
Part Three - Challenge #10

Background:

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
  • A variety of eclipsed based GUI tools
  • FTP

You might recall using JAVA programming language API to access the DB2 for z/OS favorites table in a previous challenge.

The z/OS DB2 DBA has numerous responsibilities which include:

  • Knowledge of DB2 features and functionality
  • Tasks related to database design and implementation
  • Tasks related to operation and recovery
  • Security and auditing
  • Performance
  • Installation and migration/upgrades specific to the z/OS operating system

A DB2 for z/OS DBA needs specialized knowledge about z/OS learned in previous challanges such as VSAM, Virtual Storage Access Method, SMS, Storage Management Subsystem, and the System Catalog.

DB2 for z/OS Data Structures:

  1. Storagegroup - a name associated with a collection disk volumes
  2. Database - a name associated with a collection of DB2 tablespaces and indexspaces
  3. Tablespace - VSAM Linear Data Set formatted to contain DB2 tables
  4. Table - a name to reference the rows and columns of DB2 controlled data in a tablespace
  5. Indexspace - VSAM Linear Data Set formatted to contain an index for a DB2 table
  6. 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:

Learning System IDs Only (AU0####), click on link below for special instructions.
Learning System ID special instructions for this challenge.

Submit JCL to allocate hlq.SQL data set with members and copy member DB2LOAD into hlq.JCL as follows:

tso submit 'zos.public.jcl(p3ch10)'

DDL illustrating the above steps is available in the References drop down with title of DB2 Example. The document includes JCL to run a DB2 utility to load data into a newly allocated DB2 table, then a series of DB2 SELECT statements to extract result sets from the data loaded in the DB2 table.

The DB2 Example in the References drop down is a model for accomplishing the challenge.

Challenge:

  1. Create a tablespace using an existing DB2 storage group name (MTM2017), database name (MTM2017), and VCAT name (MTM2017)
      While unnecessary, for convenience MTM2017 was used for all 3 names as seen in example
  2. Create a table in tablespace previously created
  3. Create an indexspace and index for the table previously created
  4. Use DB2 utility to load data into the new DB2 table and index
  5. Use DML SELECT statements to verify new table and index
  6. Locate the z/OS VSAM LDS cluster names associated with the newly created tablespace and indexspace

DB2 Example in References drop down walks you through learning the steps necessary to complete the challenge.

The challenge is to create a tablespace, table, indexspace, index, then load the new table and index using a different data using challenge details that follow.

Challenge Details:
  • Client data is from the Flour, Sugar, Oil Company located in data set name FSO.CLIENT.PROFILE
  • Use S##### as the tablespace name - where ##### is 5 numeric digits of your ID
  • Use T##### as the table name - where ##### is 5 numeric digits of your ID
  • Use X##### as the indexspace name - where ##### is 5 numeric digits of your ID
    NOTE: You must DROP TABLESPACE S##### if you previously used that name while executing the example instructions.
      See line 1 and 2 in SQL(STEP2)
  • Browse b FSO.CLIENT.PROFILE and enter cols to see column ruler to verify the following:
      New table layout where table column names are in UPPERCASE in parenthesis:
      1) Column 1-6 .....unique client number (CID) and the DB2 primary key for the index
      2) Column 7-41 ....client name (CLIENT)
      3) Column 42-66 ...client street address (STREET)
      4) Column 67-77 ...client city (CITY)
      5) Column 78-79 ...client state abbreviation (STATE)
      6) Column 80-83 ...client credit card type used for payment (CARD)
      7) Column 84-85 ...client credit card expiration month (MO)
      8) Column 86-87 ...client credit card expiration year (YR)
      9) Column 88-106 ..client credit card primary account number (PAN)
    CID is the DB2 table Primary Key
    All the above columns are data type of CHAR with column attribute NOT NULL

Modifications to SQL members STEP2, DB2LOAD JCL member, and SQL member STEP4 can be used to complete the challenge.

Last step to get full credit for completing challenge 10:

  1. Edit JCL data set
  2. Select new member name p3ch10x
  3. Copy 'zos.public.jcl(p3ch10x)' into new member p3ch10x
  4. Modify JCL embedded SQL SELECT statements using COUNT function to:
    • Count total number of records in your table
    • Count total number of records WHERE the CARD is VISA
    • Count total number of records WHERE the CARD is MSTR
    • Count total number of records WHERE the CARD is AMEX
    Note: Each SELECT statement in the JCL must terminate with a semi-colon (;)
  5. Submit JCL(P3CH10X) to write requested result sets from your new DB2 table to P3.OUTPUT(#10)

Browse or View P3.OUTPUT(#10) and verify information you expected, then move on to the next challenge!

Next: Challenge #11