Mainframes 360
The one stop destination for System Z professionals

Sunday, October 17, 2010

DB2 Fundamentals

Q. What is DB2? How does it compare with VSAM Files?
DB2 is the Data-Management System from IBM. DB2 is a newer technology, whilst VSAM is old. VSAM(which stands for Virtual Sequential Access Method) is just a Data Access-Method. You can browse a VSAM Dataset in Sequential Mode – Record-by-record. You can do random access – jump(hop), fly directly to a particular Record . Skip-Sequential is like the best of both worlds – Jump to 1st record, and then start browsing the subsequent records one-by-one. Sounds cool, ain't it, but hold on and have a look at what DB2 Software offers!

DB2 is a complete Software Package, that allows you to create, store, update and retrieve Data in a Systematic Manner.

1. Data Abstraction : It presents a Logical, Structured view of the Data to the User. The user doesn’t need to worry about the details of how the Data is Physically stored on the DASD-Disk.
2. Data Independence : DB2 provides a layer of Independence, between the application or COBOL Program and the Data. Every-time the structure of the Data changes(say you add a new field to the EMPLOYEE File), you don’t need re-write the entire COBOL Source code again.
3. Data Security : DB2 Software controls the Access to the Data. Not everybody can see all the Data. You can see it, only if you've got all the necessary privileges.
4. Transaction Management and Concurrency : DB2 Manages all the Transactions, and ensures the Data remains consistent. It provides a Locking-Mechanism, for concurrent access to the Data.
5. Data Recovery : When there are Crashes, DB2 allows you to recover swiftly, and restore the original Data.
Q. What are the DB2 Data Structures?
DB2 Software physically stores the Data in VSAM Files on the DASD Disk.

  Image321
STOGROUP : The Set of DASD Volumes that will be used to house the VSAM Files containing the Data. In the example below, I've created a STOGROUP called DSN8G810 with the DASD Volume S4DB21.

Image322

The information about all the STOGROUP's(Storage Groups) is stored in the DB2 Catalog Table SYSIBM.SYSSTOGROUP. The snapshot of all the STOGROUP's is shown in the picture below.

Image323

Database : A Database is a Logical-Grouping of Database Objects, related together by application, or subsystem. A Database contains no Data of its own, but it merely groups like DB2 Objects. To create a New Database, use the CREATE DATABASE Statement. In the example below, I have created a New Database called DSN8G81A on the STOGROUP DSN8G810.

Image324

The information on all the Databases in DB2 System is stored in the DB2 Catalog Table SYSIBM.SYSDATABASE. The below picture is a snapshot of it.

Image325

Table-space
: A Table-Space is a VSAM File which stores or houses the DB2 Table-Data Physically. You can create a Brand-New Table-space File, by using the CREATE TABLESPACE Statement.

Image326

In the picture above, I have created a new Table-space File called DSN8G8TS, which has a Primary-Space of 400KB and Secondary Space of 400KB. Just like the contents in a Book, the data in a Table-Space File is stored in Fixed-Size Pages. DSN8G8TS Table-Space is created under the DSN8G81A Database Umbrella. The new Table-Space File will be stored on DSN8G810 Storage-Group. A buffer-pool is the place in Main Storage, where DB2 will store Pages fetched from the Table-Space.

Image327

The information about all Table-Space Files is stored in the DB2 Catalog Table SYSIBM.SYSTABLESPACE. A snapshot of this is shown in the above picture.

Once the Table-Space File has been newly created, you can actually see it using DSLIST Utility in ISPF Menu 3.4. The VSAM Dataset-name has the format vcat.DSNDBC.dbname.tsname.*.

Image328
Q. What are Tables, Primary Keys and Foreign Keys?
A Table stores Data. Tables look like a spreadsheet, they consist of Rows and Columns, as shown in the picture below. 

  Image330    Image331
The Employee-ID Number is unique for each Employee. No two Employees have the same Employee-ID in the Company. Similarly, the Department-ID is unique for each Department. Employee-ID Column is said to be the Primary-Key or
Primary-Index in the EMPLOYEE Table. Department-ID is said to be the Primary-Key or Primary-Index in the DEPARTMENT Table.

Suppose you want to establish a Relationship, and store the information about, which Employee works for which Department? The EMPLOYEE Table should be expanded and an additional column is added to it, called FK_DEPT_ID. EMPLOYEE.FK_DEPT_ID Column refers to DEPARTMENT_ID Primary-key column of the DEPARTMENT Table. FK_DEPT_ID is called Foreign-Key. Here, is how it works. RAJ has FK_DEPT_ID=10. Look-up Department-No 10 in the DEPARTMENT Table, and it is the HR Department. It means RAM works in HR Department.

 Image332     Image331[3]
Q. How do you write the DDL to create Tables and Index Key-Columns?
As an example, let us build a toy Database-System, called New Business Tracking(NBT), which stores the information about new Insurance Policies(New Business) of an Agent for the first 90 days. This Database System will have three DB2 Tables – CONTRACT, AGENT_CONT_CNTL and INSURED. 

CONTRACT Table Schema
The CONTRACT Table stores the basic Details of the Contract, what is the Contract-No,how many Insured's does this Contract cover(ISRD_ON_CONT_CNT), is it a Life or Annuity Policy(LINE_OF_BUS_DESC), when the Application was received, when was the Policy under-written, when the Policy got Issued, what is the Face Amount(Sum-Assured) and many other details.
 Image329


DDL For CONTRACT Table

You write the CREATE TABLE Statement listing all the Columns followed by their SQL-types, along with whether the Column accepts NULL Values or is it NOT NULL. The List of Columns is a Comma-Separated List.

Image333

Primary Index For CONTRACT Table
Here's, how I have created  the Primary-Index on the CONTRACT_NO, CASE_NO Columns of the CONTRACT Table. I have created a UNIQUE INDEX on the CONTRACT Table.
 
Image337

AGENT_CONT_CNTL Table Schema
AGENT_CONT_CNTL Table stores the basic data about the Insurance Agent, and the Insurance Policies sold by him.
 Image335
 

DDL For AGENT_CONT_CNTL Table

The AGENT_CONT_CNTL Table stores Agent Details. How do you know, which Policy belongs to which Agent-ID? For each Agent, the Policy-No sold by him is stored in FK_CONTRACT_NO Column, and this refers to the CONTRACT_NO Column in CONTRACT Table. Similarly, FK_CASE_NO refers to the CASE_NO in the CONTRACT Table. Thus, the foreign-key(FK_CONTRACT_NO,FK_CASE_NO) refers to the
Primary-Key(CONTRACT_NO,CASE_NO) in the CONTRACT Table. One needs to understand, that you cannot create a Foreign-Key without defining a UNIQUE INDEX.

Image336

Primary Index For AGENT_CONT_CNTL Table
I have created a New Unique-Index IX_AGENT_CONT_CNTL on the columns FK_CONTRACT_NO, FK_CASE_NO and AGENT_CODE of the AGENT_CONT_CNTL Table.
 
Image338

INSURED Table Schema
INSURED Table stores  information  about the Insured, or Policy-Holder. In case of a Join-Policy, a Contract may have two or three Insured's. In that case, each Insured for that Policy has a different ISRD_SEQ_NO. The Insured's Name, Date-of-Birth etc. is stored in  this Table.
  Image339 

DDL For INSURED Table
The CREATE TABLE Statement for the INSURED Table is shown in the picture below. How do you know, who is the Policy-Holder for a particular Contract-No? This information is stored  in the Columns FK_CONTRACT_NO and FK_CASE_NO, which is the  Foreign-Key, and refers to the corresponding columns in the CONTRACT Table.
Image340

Primary Index For INSURED Table
I have created a Primary Unique-Index on the ISRD_SEQ_NO,FK_CONTRACT_NO and FK_CASE_NO Columns of the INSURED Table.

Image341

To many people who are thrown to work at a mainframe computer on their first job, they feel lost. Mainframe people seem to speak a completely different language and that doesn't make life easy. What's more, the books and manuals are incredibly hard to comprehend.

"What on earth is a Mainframe?" is an absolute beginner's guide to mainframe computers. We'll introduce you to the hardware and peripherals. We'll talk about the operating system, the software installed on a mainframe. We'll also talk about the different people who work on a mainframe. In a nutshell, we'll de-mystify the mainframe.

Readers based in India, can buy the e-book for Rs. 50 only or the print book. International readers based in the US and other countries can click here to purchase the e-book.