---
title: "DB2 Tutorial For Software Developers (5 of 5)"
description: "The best DB2 tutorial that covers index, synonyms,alias and DCLGEN. Read more."
url: https://srinimf.com/2015/08/16/mainframe-db2-tutorial-for-software-developers-5-of-5/
date: 2015-08-16
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2018/04/pexels-photo-253758-e1522815764716.jpeg?fit=500%2C375&ssl=1
categories: ["DB2 V11"]
tags: ["DB2 Tips and Solutions", "DB2 Tutorial", "DB2 tutorial for mainframe"]
type: post
lang: en
---

# DB2 Tutorial For Software Developers (5 of 5)

Db2 tutorial really good [for Mainframe developers](https://srinimf.com/2015/12/20/15-best-vsam-quiz-questions-for-mainframe-developers/). Many projects DB2 is the core database so they can learn quickly here.

Read my [Part-4 post on DB2 tutorial](http://srinimf.com/2015/08/06/mainframe-db2-tutorial-for-software-developers-4-of-5/)**.**

**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

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

Email Address

Subscribe

**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***

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2015%2F08%2F16%2Fmainframe-db2-tutorial-for-software-developers-5-of-5%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2018%2F04%2Fpexels-photo-253758-e1522815764716.jpeg%3Ffit%3D500%252C375%26ssl%3D1&description=DB2%20Tutorial%20For%20Software%20Developers%20%285%20of%205%29)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2015/08/16/mainframe-db2-tutorial-for-software-developers-5-of-5/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2015/08/16/mainframe-db2-tutorial-for-software-developers-5-of-5/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2015/08/16/mainframe-db2-tutorial-for-software-developers-5-of-5/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2015/08/16/mainframe-db2-tutorial-for-software-developers-5-of-5/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20Tutorial%20For%20Software%20Developers%20%285%20of%205%29&body=https%3A%2F%2Fsrinimf.com%2F2015%2F08%2F16%2Fmainframe-db2-tutorial-for-software-developers-5-of-5%2F&share=email)
-

### Like this:

Like Loading…