DB2 Tutorial For Software Developers (5 of 5)

Db2 tutorial really good for Mainframe developers. Many projects DB2 is the core database so they can learn quickly here.

Read my Part-4 post on DB2 tutorial.

Indexes: What is an Index ?

‘An index is an ordered set of pointers to rows of a base table’. Or ‘An Index is a balanced B-tree structure that orders the values of columns in a table’

Why an Index ?

One can access data directly and more efficiently
Each index is based on the values of data in one or more columns. An index is an object that is separate from the data in the table.
When you define an index using the CREATE INDEX statement, DB2 builds this structure and maintains it automatically.
Indexes can be used by DB2 to improve performance and ensure uniqueness.
In most cases, access to data is faster with an index.
A table with a unique index cannot have rows with identical keys.

Syntax : For creation of an Index

CREATE INDEX <indexname> ON <tabname> (colname ASC/DESC)

Subscribe to Blog via Email

Other DB2 Objects:-

VIEWS

It is a logical derivation of a table from other table/tables. A View does not exist in its own right.
They provide a certain amount if logical independence
They allow the same data to be seen by different users in different ways
In DB2 a view that is to accept a update must be derived from a single base table

Aliases

Mean ‘another name’ for the table.
Aliases are used basically for accessing remote tables (in distributed data processing), which add a location prefix to their names.
Using aliases creates a shorter name.

Synonym

Also means another name for the table, but is private to the user who created it.

Syntax:

CREATE VIEW <Viewname> (<columns>) AS Subquery (Subquery -
 SELECT
 FROM other Table(s))
 CREATE ALIAS <Aliasname>
 FOR <Tablename>
 CREATE SYNONYM <Synonymname>
 FOR <Tablename>

Application programming using DB2

Application environments supporting DB2 :

IMS(Batch/Online), CICS, TSO(Batch/Online)
CAF – Call Attachment Facility
All DB2 application types can execute concurrently
Host Language support – COBOL, PL/1, C, Fortran or Assembly lang

Steps involved in creating a DB2 application

Coding the application

using Embedded SQL
using Host variables (DCLGEN)
using SQLCA
pre-compile the program
compile & link edit the program
bind

Note : Cursors can also be used

Embedded SQL statements

It is like the file I/O
Normally the embedded SQL statements contain the host variables coded with the INTO clause of the SELECT statement.
They are delimited with EXEC SQL …… END EXEC.
E.g. EXEC SQL

SELECT Empno, Empname INTO :H-empno, :H-empname
FROM EMPLOYEE
WHERE empno = 1001
END EXEC.

Host Variables

These are variables(or rather area of storage) defined in the host language to use the predicates of a DB2 table. These are referenced in the SQL statement.
A means of moving data from and to DB2 tables
DCLGEN produces host variables, the same as the columns of the table

Host variables can be used

In WHERE Clause of Select, Insert, Update & Delete
‘INTO’ Clause of Select & Fetch statements
As input of ‘SET’ Clause of Update Statements
As Input for the ‘VALUES’ Clause of Insert statements
As Literals in Select list of a Select Statement

Example-

SELECT Cust_No,
 Cust_name,
 Cust_addr INTO :H-CUST-NO,
 :H-CUST-NAME,
 :H-CUST-ADDR
 FROM CUSTOMER
 WHERE CUST_NO = :H-CUST-NO;

DCLGEN

Issued for a single table
Prepares the structure of the table in a COBOL copybook
The copybook contains a ‘SQL DECLARE TABLE’ statement along with a working storage host variable definition for the table

Also read