IBM Information Management System (IMS)

 View Only

 SQL for IMS

Shashank Dewangan's profile image
Shashank Dewangan IBM Champions posted 06/17/26 12:01 PM

Hi 

Has anyone used SQL statement to access the IMS DB from COBOL program? What all things we need to consider when we use SQL? What about PCB ?

SQL concepts for IMS - IBM Documentation

Don Leahy's profile image
Don Leahy posted 09/23/26 11:47 AM

I have had some limited experience using IMS SQL in COBOL, mostly for queries against the IMS Catalog.   Some notes:

  • Check to see if CATPSBATTCH=Y is in effect at your shop.  When this setting is in play, IMS will attach the Catalog PCBs to your user PSB automatically.  The IMS Catalog contains the metadata needed for IMS SQL to function.  If you are using CATPSBATTCH=N, you will need to manually add the IMS Catalog PCBs to your PSB.  This is harder to do than you might think (IMS team is working on it), so I strongly recommend using CATPSBATTCH=Y
  • In order to be useful, IMS SQL requires a lot of metadata in the catalog. To be addressable, every field in the segment needs to be defined in the DBD, not just the search fields.  IBM has a tool that allows you to import COBOL copybooks into DBD source.
  • The IMS SQL supported by COBOL is a subset of what is supported by Java.  Which itself is a subset of the SQL language.  Some functions that are not available include:
    • JOINS
    • Sub SELECTs
    • BETWEEN predicate
    • IN predicate
    • SELECT DISTINCT
    • various date/time/math/string functions

My next comments should be taken with a grain of salt, because there may be workarounds that I don't know about.  As I mentioned, I am not an expert in this area.

  • In my experience, only one cursor can be open on a PCB at a time. 
  • Since IMS SQL uses dynamic SQL model, all SELECT statements have to be coded as cursors.  This can be a challenge if you are accustomed to a static SQL model in your COBOL/Db2 programs.
  • Closing one cursor seems to close them all.  The reason may be that CLOSE CURSOR generates a DLI RLSE call. 
  • To learn what IMS SQL is doing, run the program under BTS to see the DLI calls that the SQL Coprocessor generates.
  • I am not sure whether IMS SQL in COBOL programs is considered strategic by IBM

A design pattern that I have found useful when mining the IMS Catalog:

  1. Open a cursor on the HEADER. 
  2. Fetch the HEADER 'rows' until you find the one(s) you are interested in
  3. Issue a GUR call via AIBTDLI to obtain the details.
  4. Use COBOL's XML PARSE statement to interpret the results.

You could use a GN call on the HEADER segment, but the nice thing about using a SQL cursor is that the FETCH statement parses the fields into host variables with no effort on your part. 

Related Content