IDMS: Offloading Data Stored in a single SQL Table
Article ID: 55926
Updated On:
Products
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
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.
Feedback
