Using SQL SELECT statements in OLQ

Products

• IDMS
• IDMS - Database

Issue/Introduction

How can SQL SELECT statements be used in OLQ?

There are two methods for using SQL SELECT statements in OLQ. Both methods can be used online and in OLQBATCH jobs.

Environment

Release: All supported releases.
Component: OLQ.

Resolution

Native OLQ SQL … “SET ACCESS OLQ” (the default)

By just using record names as table names, and element names as column names, simple SELECT statements can be coded. For example, an OLQBATCH input stream for the demonstration database provided at install such as this could be used:-

//SYSIPT DD * 
SET USER your-user-name
SET ACCESS OLQ
SET DICTNAME APPLDICT
SET DBNAME EMPDEMO
SIGNON SS EMPSS01 SCHEMA EMPSCHM ( 100)
SELECT CLAIM-YEAR-0405, PATIENT-LAST-NAME-0405 FROM DENTAL-CLAIM -
ORDER BY 2
DISPLAY

IDMS/SQL option … “SET ACCESS IDMS”

The same SELECT statement can be executed in such a way that OLQ simply passes the syntax to the IDMS SQL/Option for execution. Some small changes need to be made to the syntax for this to work:-

• Provide the SQL schema name on the table name.
• Hyphens, "-", in column names must be changed to underscores, "_".
• Record (table) names with hyphens in them must be double-quoted.

For example:-

//SYSIPT DD *
SET USER your-user-name
SET ACCESS IDMS
SET DICTNAME APPLDICT
SELECT CLAIM_YEAR_0405, PATIENT_LAST_NAME_0405 -
FROM EMPNET."DENTAL-CLAIM" ORDER BY 2
DISPLAY

In the above example, the following definition for EMPNET would have to be made in the APPLDICT SQL catalog:-

     CREATE SCHEMA EMPNET
         FOR NONSQL SCHEMA APPLDB.EMPSCHM VERSION 100
             DBNAME EMPDEMO
         ;

The second method can also be used in OLQ to execute SELECT statements against native SQL-defined tables. This is a more robust and function rich implementation of SQL than native OLQ.

Additional Information

Introducing the OLQ SELECT statement
SELECT (from the SQL/Option)
Accessing Network-Defined Databases