DB2 Tutorial For Software Developers (5 of 5)

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)

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

Read more at DB2 Tutorial from Srinimf.com.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.