How to access a CA IDMS USAGE BIT network database bitmap element using SQL?

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

CA IDMS network database elements with a USAGE BIT clause contain data used as bitmaps. These can be accessed and interpreted via SQL, but this requires a few steps. This document outlines the procedure required to do so.

Background:

Accessing data in CA IDMS network records via SQL can be accomplished using native SQL DML; in some cases accessing the data via a procedure or table procedure is more advisable. In this case, the access via native SQL can be accomplished so that is what we recommend. Solutions for both native SQL and using a table procedure are detailed below.

Environment:
The need to access BITMAP elements using SQL can arise in any IDMS environment.
Instructions:

To access network data containing a bitmap using SQL DML, take the following steps:

  1. In IDD, define the element with PIC X(nn) USAGE IS BIT. For example, take the following definition:
    *+   ADD RECORD NAME IS SSR-032 VERSION IS 1 .
    ......
    *+ RECORD ELEMENT IS FLAG VERSION 0
    *+ PICTURE IS X(08)
    *+ USAGE IS BIT
    *+ .
  2. Create an SQL schema to reference the network schema containing this record element. In this example, that definition would be:
    *+   CREATE SCHEMA SYSDICT FOR NONSQL SCHEMA SYSDIRL.IDMSNTWK VERSION 1 ;
  3. Issue a SELECT <columns> FROM <record> ; and the value in the bitmap column will be the hex representation of the bitmap. For this example, the result looks like the following:
    SELECT SS_NAM_032, FLAG_032 FROM  SYSDICT."SSR-032";
    *+
    *+ SS_NAM_032 FLAG_032
    *+ ---------- --------
    *+ DAPSS01 00
    ...
    *+ CJDSS01 40
    As this example shows, for a bitmap of 00000000 you will see X'00'; for a bit map of 01000000 you will see X'40'.

  4. Each byte of the hex representation of the bitmap will represent 4 bits from the map, in standard hex format. Use the hex value to interpret the bitmap value.

If for some reason a table procedure is required instead of using direct SQL access, that can also be used to retrieve the bitmap value. However, COBOL is somewhat clumsy in defining and displaying bitmap data items. Because of that, if you are using CA IDMS Quick-Bridge to generate the table procedure, the generated code will require modification in order to run, as follows:

 

  1. Ensure that all of the data field representations in the program have a reference with the character description of this field. In our example, that will be field FLAG-D and should be defined with a PICTURE IS X(08). Some sites define bitmap fields as an array; if that is what your record layout looks like, it will be included in the table procedure as a series of references in the form BITMAP-ELEMENT-nnn-D where nnn is a numeric value that represents the occurrence in the array. This series of references in the program should be removed and replaced with a single reference defined like this: BITMAP-ELEMENT-D PIC X(mm), where mm is the number of bytes required to contain the entire array. Also replace all of the BITMAP-ELEMENT-nnn-I (indicator) occurrences with a single indicator BITMAP-ELEMENT-I.

  2. The above definitions will need to be reviewed and verified (and possibly replaced, if the table procedure was generated by CA IDMS Quick-Bridge) in three places:

    1. The LINKAGE SECTION, where it lists the parameters to be processed by the table procedure;

    2. The PROCEDURE DIVISION USING clause, so that it matches the list of parameters in the LINKAGE section;

    3. In the source code wherever it has MOVE statements. If an array was used in the original IDD definition, this means replacing multiple move statements that look like this:
      MOVE BITMAP-ELEMENT OF <record-name>(nnn)   TO BITMAP-ELEMENT-nnn-D.
      MOVE 0 TO BITMAP-ELEMENT-nnn-I.
      with one statement that looks like this:
      MOVE BITMAP-ELEMENT OF <record-name>        TO BITMAP-ELEMENT-D.
      MOVE 0 TO BITMAP-ELEMENT-I.
  3. The CREATE TABLE PROCEDURE statement will need to match the LINKAGE SECTION list. If the table procedure was generated by Quick-Bridge, or if the original elements was defined as an array, that may mean replacing multiple column definitions (BITMAP_ELEMENT_nnn) with a single column defined as BITMAP_ELEMENT CHAR(mm).

  4. Compile and link the program, and vary it NCI or use a DCMT VARY DYN PROGRAM to define it to the system.

  5. When issuing a query against the table procedure, you will need to use the CAST scalar function to see the hex representation of the bitmap of this field. You can use this syntax:
     SELECT ...  CAST(BITMAP_ELEMENT AS BIN(mm)) AS BITMAP_ELEMENT, ...
    This will show you the hex translation of all mm bytes of the bitmap.

  6. If using a CAST in the query as outlined above seems cumbersome for end users, you can define a view that selects from the table procedure and uses this CAST syntax, and grant the end users access to the view instead of to the table procedure itself.

    Implementing either or both of these approaches will provide a working native SQL DML statement, and a working table procedure, that end users can employ to see the hex representation of a network element used to store a bitmap.