How can I run SQL SELECT statements in OLQ?

Document ID : KB000010129
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

How can SQL SELECT statements be used in OLQ?

Background:

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

Environment:
CA-IDMS, all supported releases.
Instructions:

Method 1: 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

Method 2: CA-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 CA-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
         ;

Method 2 can also be used in OLQ to execute SELECT statements against native SQL-defined tables.

 

Method 2 is also a more robust and function rich implementation of SQL than method 1.

Additional Information: