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)
Other DB2 Objects:-
- 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
- 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.
- Also means another name for the table, but is private to the user who created it.
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
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.
- 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
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;
- 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