IDMS: Offloading Data Stored in a single SQL Table

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

Data stored in SQL tables must be offloaded into a flat file. For example, this may be required to load a duplicate copy of the table into another CV or dbname.  Is there a utility to unload or reload the contents of one specific table?

 

Environment

IDMS - All Supported Releases

Resolution

Both Culprit for IDMS and OLQ Online Query for IDMS can create a flat file containing data from a specific table. You can then input that flat file into the LOAD utility statement to load a duplicate copy of the table into another CV or dbname.

The following example demonstrates how to use Culprit for IDMS for this, using the DEMOEMPL.JOB table:

  IN DB(Q) DICTIONARY=SYSDICT SCHEMA=DEMOEMPL
  SQL SELECT * FROM JOB
  01OUT 178 D PS
  01510001 JOB_ID  FN
  01510005 JOB_TITLE
  01510025 MAX_RATE  FN DP=0
  01510035 MIN_RATE   FN DP=0
  01510045 SALARY_IND
  01510045 NUM_OF_POSITIONS  FN
  01510049 EFF_DATE
  01510059 JOB_DESC_LINE_1
  01510119 JOB_DESC_LINE_2 

Compare this syntax to the column definitions in the table to see how to create the specific output formatting parameters.

CREATE TABLE DEMOEMPL.JOB
    ( JOB_ID                    UNSIGNED NUMERIC(4) NOT NULL,
      JOB_TITLE                 CHARACTER(20) NOT NULL,
      MIN_RATE                  UNSIGNED DECIMAL(10,2),
      MAX_RATE                  UNSIGNED DECIMAL(10,2),
      SALARY_IND                CHARACTER(1),
      NUM_OF_POSITIONS          UNSIGNED DECIMAL(4),
      EFF_DATE                  DATE,
      JOB_DESC_LINE_1           VARCHAR(60),
      JOB_DESC_LINE_2           VARCHAR(60) ....  

Note that the OUT line specifies the LRECL of the output file (178), plus the format (PS). The default output file SYS020 can be used, as in this example, or an alternate name can be provided by the DD= parm of the output file. That ddname is defined in the Culprit for IDMS JCL.

Following this, each 015 line identifies how a column will appear in the output file, first indicating the position where the column should begin. Starting with column 1 and adding the length of all previous columns in the table, we can create an output file with no embedded spaces. The LOAD utility can then read this file. The FN specification after numeric column names suppresses any numeric edits, so leading-zero-suppression is not used, and no commas are inserted into integer columns. Adding DP=0 for decimal data type columns tells Advantage CA-Culprit for CA-IDMS to not insert a decimal point into the output. Date, Time, and Timestamp columns will be output in a format that LOAD can use for input. To calculate the starting position of the following column, add the lengths of all previous columns (plus 1), remembering that Date columns will create a 10-byte output field; Time columns will be output as an 8-byte field; and Timestamp columns will be output in a 26-byte format.

In some situations, it may be simpler to use an OLQBATCH job. You will need to specify SET ACCESS OLQ, and use the OLQBATCH OUTPUT parameter to write the output to a file. An example of this, again using the demo employee database, is the following statement:

 SELECT * FROM JOB OUTPUT OUTFILE 

In this example, the JCL would need to contain a DD statement with a ddname of Outfile to contain the resulting file. The file will be produced with no fillers or unused bytes between the data items, and no page breaks or report formatting. However, since it can be run only with SET ACCESS OLQ, it cannot be used for queries that require ANSI-standard SQL.