Introduction to DB2

Intro to SCT DB2 Concepts

All data is stored in tables

Tables are like VSAM files
Records in the table are called rows
Fields in each record are called columns
Each field has a particular data type, e.g., CHAR
Every record has a value for each column (even if null)
User tables are described in DB2 catalog tables

An overview of an SCT table

Sample table definition (simplified)


       CREATE TABLE SCTINST.HRS_ACCT_DIST_TB
         (CORP                SMALLINT,
          EMP_ID              CHAR     (00009),
          JOB                 CHAR     (00001),
          CD_NUM              SMALLINT,
          DEL                 CHAR     (00001),
          ACCT_FY             CHAR     (00002),
          ACCT_CC             CHAR     (00002),
          ACCT_NUM            CHAR     (00010),
          TYP_EFFRT           CHAR     (00002),
          AMT_PCT_CD          CHAR     (00001),
          AMT_PCT             DECIMAL  (10,05),
          EARN_CD             CHAR     (00003),
          DIST_BGN_DT         DATE,
          DIST_END_DT         DATE,
          BEN_FY              CHAR     (00002),
          BEN_CC              CHAR     (00002),
          BEN_ACCT_NUM        CHAR     (00010),
          POS                 CHAR     (00006)),

SCT tables use two-part qualified names
1. SCTINST.HRS_ACCT_DIST_TB is a table name
2. Low-level qualifiers describe the table
3. High-level qualifiers identify the owner/creator
4. The two together uniquely identify the table
5. Column name and table name uniquely identify columns
Each SCT system has its own set of tables
1. SCTINST.HRS_ACCT_DIST_TB is an INST table
2. SCTTEST.HRS_ACCT_DIST_TB is the corresponding TEST table
3. SCTPROD.HRS_ACCT_DIST_TB is the corresponding PROD table
4. This is similar our VSAM PROD, TEST, INST convention

Examining an SCT table using SPUFI

SPUFI runs under SPF as application 4.25 and under SPX as application DB2
1. SPUFI reads SQL from an input file and executes it
2. SPUFI saves output in a dataset for browsing
First-time setup
1. Create an FB 80 PDS SQL.DATA to save SQL queries
2. Select SPF option 4.25 or SPX option DB2
3. Look over the various options
4. Set input dataset name to SQL.DATA(SAMP1)
5. Set output dataset name to SQL.TEXT
6. Press enter
7. Look over the various options
8. Set isolation level to CS (cursor stability)
9. Press enter
Sample search SAMP1
1. Enter the following:
```
  -- SAMPLE PROGRAM 1
     SELECT *
             FROM SCTINST.HRS_ACCT_DIST_TB;
     COMMIT;
```
2. Press END, then enter
3. Look over the resulting output
4. Press END

Sample search SAMP2, using member SAMP2


  -- SAMPLE PROGRAM 2
     SELECT EMP_ID, ACCT_NUM
            FROM SCTINST.HRS_ACCT_DIST_TB
            WHERE CD_NUM=1;
     COMMIT;

Sample search SAMP3 (join of two tables)


  -- SAMPLE PROGRAM 3
     SELECT X.EMP_ID, ACCT_NUM, EMP_FIRST_NAME, EMP_LAST_NAME
            FROM SCTINST.HRS_ACCT_DIST_TB X,
                 SCTINST.HRS_EMP_ROOT_TB Y
            WHERE CD_NUM=1 AND
                  X.EMP_ID = Y.EMP_ID
            ORDER BY EMP_LAST_NAME, EMP_FIRST_NAME;
     COMMIT;

SCT Table Setup

Why study the underlying setup for SCT tables?

Most SCT programs are free of DB2 (and VSAM) code
1. I/O requests are handled in special I/O modules
2. Programs copy field definitions from copy members
3. Programs issue requests to I/O modules
4. Programs work on the returned fields
5. This approach is used for VSAM and DB2
But EASYTRIEVE and COBOL programmers may need to know more
1. Some local reports may reference underlying tables
2. Others may use locally defined interfaces
3. Safer than VSAM--new fields can be added transparently
4. See your tool provider for more info
And proc builders will need to to know more

SCT abbreviated forms for table names

Unqualified table names can be up 18 characters
But PDS member names can only be 8 characters
SCT abbreviates table names to 4 characters
1. The first two characters match the VSAM abbreviations
2. The last two complete the name and identify the table
The abbreviation for the old VSAM employee file was EM
Abbreviations for the tables replacing it start EM:
- EMAC = (EMployee) HRS_ACCT_DIST_TB
- EMAD = (EMployee) HRS_ADDR_DIST_TB
- EMAP = (EMployee) HRS_APPLICANT_TB
- EMAS = (EMployee) HRS_ASSIGNMENT_TB
- EMAW = (EMployee) HRS_AWARDS_TB
```
               ...
               ...
               ...
```
- EMWM = (EMployee) HRS_WORK_EMP_TB

SCT DCL (data control language) libraries

Overview
1. Created by the install procedure
2. Contain COBOL copy members describing DB2 tables
3. Datasets are ISP00.SCT.INST.DCL, etc.
4. INST, TEST, and PROD DDL PDSs exist
5. HRS, FRS, SIS, and ZSS DDL are consolidated
6. Needed for compiles and inserted by SPX
Browse ISP00.SCT.INST.DCL, select member EMACDCL
1. xxxxDCL contains the xxxx table definition
2. EMACDCL contains the EMAC table definitions
  - Definition in SQL
  - Definition in COBOL

SCT DDL (Data definition language) libraries

Overview
1. Created by the install procedure
2. Contain SQL CREATE statements defining DB2 objects
3. Datasets are ISP00.SCT.INST.DDL, etc.
4. INST, TEST, and PROD DDL PDSs exist
5. HRS, FRS, SIS, and ZSS DDL are consolidated
6. Mostly for documentation and initial install
Browse ISP00.SCT.INST.DDL, select member D2EMACTB
1. D2xxxxTB contains the xxxx table definition
2. D2EMACTB contains the EMAC table definitions
  - CREATE TABLESPACE
  - CREATE TABLE
  - LABEL ON TABLE
  - CREATE (UNIQUE) INDEX

What are all those things in the DDL library?

Lean back--this section is not so important right now
Tablespaces (e.g., EMACTS)
1. DB2 tables live in tablespaces
2. Programs operate on tables
3. DB2 utilities operate on tablespaces
4. SCT tablespaces contain one table each
5. By SCT convention xxxxTS contains table xxxx
6. Tablespaces can be uniquely qualified by database name
7. IAIHRS.EMACTS is the INST EMAC tablespace
Databases (e.g., IAIHRS)
1. Databases are just sets of tablespaces
2. Each tablespace belongs exactly one database
3. Some DB2 commands (STOP, START) work on databases
4. IAIHRS, IAIFRS, IAISIS, IAIZSS are INST databases
5. IATHRS, IATFRS, IATSIS, IATZSS are TEST databases
6. IAPHRS, IAPFRS, IAPSIS, IAPZSS are PROD databases
Indexes (e.g., SCTINST.EMAC00IX)
1. Pretend they're like VSAM for now
2. May be unique (no duplicate keys) or not
3. Key may include multiple nonconsecutive columns
4. SELECT WHERE should use indexed columns
5. Names are like SCTINST.xxxxnnIX
6. Mostly referenced for RECOVER INDEX
Other ISP00.SCT.INST.DDL members
1. DEFDB members for CREATE DATABASE
2. GROUPS members for CREATE STOGROUP
3. REMARK members for COMMENT ON COLUMN
4. LABELS members for LABEL ON COLUMN
5. SETSQLID mostly for installs

DB2's views of SCT tables

Fire up SPUFI (SPF option 4.25 or SPX option DB2)
1. Set input dataset to SQL.DATA(DB2CAT)
2. Copy in SYS1.MVS.PROCLIB(NG)
3. Examine SELECTS on DB2 catalog tables
4. Hit END and ENTER
Browse the resulting output
1. List of the SCT databases
2. List of tables in the INST HRS (IAIHRS) database
3. List of columns in the SCTINST.HRS_EMP_KEY_TB
4. List of indexes over IAIHRS tables
5. List of key columns for the SCTINST.EKRT00IX index

SCT's views of SCT tables

Application Perspectives Of Data (APODs)
1. APODs are like VSAM master files
2. Programs pass keys and requests to I/O routines
3. I/O routines assemble the APOD from DB2 tables
  - Some calls pass back a full master file record
  - Others pass back a view containing fewer columns
  - The fewer columns and tables used, the faster
4. The program processes the resulting data
5. APOD definitions are stored in RMAPs
Browse ISP00.SCT.INST.RMAP(AAAPOD)
1. This is the RMAP for the "AA master file"
2. Enter ONLY ID= on the command line
3. This shows table names AART, AANS, AAAP, and AABS
4. These are the tables used to build the APOD
5. Enter RESET on the command line
6. DB2 column names appear on the left
7. COBOL names appear in the middle
8. SCT data element names appear on the right
9. Views are defined at the bottom
Browse ISP32.PAN.VALET(AZAAIO)
1. This routine implements the AA file APOD
2. Find IMPLEMENTS for a description of the program
3. Note the command codes that can be requested

Comparison of VSAM and DB2

VSAM clusters versus DB2 tables

VSAM
1. Clusters are created by IDCAMS DEFINE CLUSTER
2. Clusters are recorded in VSAM catalogs
3. The detailed record layout is not known to VSAM
4. IDCAMS is used to copy, reorg, and recover
5. VSAM is unaware of backups
DB2
1. Tables are created by SQL CREATE TABLE
2. Tables are recorded in DB2 catalogs
3. DB2 records the record layout (columns) in the catalog
4. DB2 utilities are used to copy, reorg, and recover
5. DB2 knows where all the bodies are buried

VSAM program structure versus DB2 program structure

VSAM
1. Record layouts are copied into programs
2. I/O requests are coded in COBOL (OPEN, READ, ...)
3. I/O requests are compiled as written
4. The entire record is passed to and from the program
DB2
1. Row layouts ("DCL") are copied into programs
2. I/O requests are coded in SQL (SELECT, FETCH, ...)
3. A preprocessor converts SQL into COBOL calls
4. Only requested columns are passed to and from the program

VSAM program execution versus DB2 program execution

VSAM
1. There is no "VSAM" subsystem to be up
2. The job allocates clusters using DD statements
3. The job performs VSAM I/O
DB2
1. DB2 must be up for the job to run (cf. CICS)
2. DB2 allocates tables based on table names
3. DB2 performs I/O when called by the job

Preparing and Running DB2 Programs

How does DB2 execute SQL?

It depends
1. Do keys exist on some columns?
2. Is the table physically in key order?
3. Is it close to being in order?
4. How big is it?
5. How many unique values do columns have?
6. Do predicates use constants or variables?
7. Is a sort required?
8. How many records do you expect to retrieve?
The optimizer takes all this into effect
1. Examines available information
  - SQL passed from preprocessor (DBRM)
  - Definitions (indexes, etc.)
  - Statistics saved by RUNSTATS
2. Develops a PLAN (how to execute the SQL)
3. Timestamps the plan and saves it in the catalog
The plan is used when the program executes
Example
1. Suppose the optimizer knows all this:
  - SQL is SELECT EMP_NAME,PUBLICATIONS FROM TX,TY
    WHERE TX.SSN=TY.SSN AND
    TX.DEPT='PSYCH';
  - EMP_NAME is a column in table TX
  - PUBLICATIONS is a column in table TY
  - TX and TY are indexed on SSN
  - TX has 1000 rows (1 per employee)
  - TY has 77000 rows (1 per publication)
  - Roughly 400 rows have DEPT='PSYCH'
  - TX is sloppy (not well clustered)
2. A plan might be
  - Sequentially read the rows in TX
  - Filter out rows not containing PSYCH
  - Read TY by SSN key
  - Return EMP_NAME and PUBLICATIONS

How do you prepare a DB2 program?

Short answer: use SPX
Preprocessor
1. Takes COBOL with embedded SQL as input
2. Brings in more code from SQL INCLUDEs
3. Inserts a version identifier or timestamp
4. Saves out the SQL and version as a "DBRM"
Compiler
1. Compiles COBOL passed from preprocessor
2. Passes object code to linkage editor
Linkage editor
1. Accepts object code and program libraries
2. Includes interface stubs to DB2 and COBOL libraries
3. Saves load module to be executed
DB2 BIND step
1. Accepts DataBase Request Module (DBRM) from preprocessor
2. Develops plan using DBRM and catalog information
3. Saves plan (or package) in DB2 catalog and directory

How do SCT DB2 programs run?

The program begins running
It reads its plan name from ISP00.SCT.*.CNTLLIB
It calls DB2 to establish a connection
It calls DB2 to open the plan
DB2 matches and verifies the versions
Execution continues with more DB2 calls

What was that about plans?

SCT plans are just package lists
Each CICS (INST, TEST,...) runs under a single plan
Batch main programs have individualized plans
Rebind plans only if programs use additional I/O routines
TOSes should have instructions for rebinding plans

What was that about packages?

NODYNAM COBOL programs
1. All subroutines reside in a single load module
2. Program executes as a single unit linked together
3. Fine for simple program (no subroutines)
4. Maintenance is difficult with many subroutines
5. Changes must be propagated by relinking all calling programs
Old-style DB2 plans (plans with DBRMs)
1. All subroutine DBRMs reside in a single plan
2. Plan executes as a single unit bound together
3. Fine for simple program (no subroutines)
4. Maintenance is difficult with many subroutines
5. Changes must be propagated by rebinding all calling plans
DYNAM COBOL programs
1. Subroutines reside in their own load module
2. Main program dynamically loads and executes subroutines
3. Fine for simple and complex programs
4. Maintenance is easy
5. Changes are picked up automatically
New-style DB2 plans (plans with package lists)
1. Subroutine DBRMs reside in their own "packages"
2. Plan loads all packages using a package list
3. Fine for simple and complex programs
4. Maintenance is easy
5. Changes are picked up automatically

What's the point?

DB2 routines have load modules, packages and plans
They need to be in sync

How do you keep everthing in sync?

Non-I/O routines
1. No preprocessing, no binding packages
2. The source could be the same or different across environments
3. There are no packages
4. To apply TOSes
  - Compile and link into the appropriate library
  - EXEC CICS NEWCOPY if CICS routine
5. To mass apply fixes
  - Compile and link into a migration library
  - Cycle CICS SCT regions to pick up CICS changes
DB2 I/O routines
1. Preprocessor and bind steps are required
2. The source could be the same or different across environments
3. The packages would differ because they point to different tables
4. To apply TOSes
  - Compile and link into the appropriate library
  - Bind using a different version
  - Perform CICS NEWCOPY if CICS routine
5. To mass apply fixes
  - Compile and link into a migration library
  - Bind using a different version
  - Cycle CICS IA regions to pick up CICS changes

What was that about versions?

A version is just an identifying character string
Version is passed to preprocessor as a parm
Preprocessor sets up versions for packages
Version is stored as a character string in program
Multiple versions of packages can be stored in DB2 catalog
Approproate version will be matched with load modules
Migration libraries should be built with their own version
If you break a production version, you break SCT
Check local standards for versions
Versions supplied by SCT
1. SIS_INST_001_BATCH for batch packages
2. SIS_INST_001_CICS for online packages
3. SIS_TEST_001_BATCH
4. SIS_TEST_001_CICS
5. SIS_PROD_001_BATCH
6. SIS_PROD_001_CICS
7. Six more HRS_...

OIT Applications Support

Last Modified: