How to access “IMS DB” database using SQL Queries(1 of 2)

SQL queries to access IMS DB
SQL queries to access IMS DB

Like DB2 you can access IMS DB database using SQL queries. How you can access, basic steps required are as follows:

  1. Changes required in DBD (database descriptor)
  2. Need IMS connect
  3. IMS V13 is needed, which supports accessing IMS DB through SQL Queries
  4. As of now this facility available, only programs that can be run as MPP, BMP and IFP
  5. Concurrently you can give SQL Queries and DL/I calls in the same program
  6. To access both SQL and DLI, you need to use IMSSQL compiler
  7. For accessing SQL, the executable program is DFSCAT00 Program
  8. This facility as of now not supporting for IMS Batch applications (DBCTL).

The following SQL statements supported in IMS DB program:

CLOSE- The CLOSE statement closes a cursor.

DECLARE CURSOR -The DECLARE CURSOR statement defines a cursor.

DECLARE STATEMENT -The DECLARE STATEMENT statement is used for application program documentation. It declares names that are used to identify prepared SQL statements.

DELETE -The DELETE statement deletes rows from a table.

DESCRIBE OUTPUT -The DESCRIBE OUTPUT statement obtains information about a prepared statement.

EXECUTE -The EXECUTE statement runs a prepared SQL statement.

FETCH -The FETCH statement positions a cursor on a row of its result table. It can return zero or one and assigns the values of the rows to host variables if there is a target specification.

INCLUDE -The INCLUDE statement inserts application code (including declarations and statements) into a source program.

INSERT -The INSERT statement inserts rows into a table.

OPEN -The OPEN statement opens a cursor so that it can be used to process rows from its result table.

PREPARE -The PREPARE statement creates an executable SQL statement from a string form of the statement. The character-string form is called a statement string. The executable form is called a prepared statement.

SELECT -The SELECT statement is used to retrieve data from one or more tables. The result is returned in a tabular result set.

UPDATE -The UPDATE statement updates the values of specified columns in rows of a table.

WHENEVER -The WHENEVER statement specifies the host language statement to be run when a specified exception condition occurs.

SQL Query examples

Author: Srini

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