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)

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,570 other followers

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

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.