How to export data using IDMS/SQL
Article ID: 120606
Updated On: 10-28-2021
Products
Issue/Introduction
The standard output in a SELECT used in an IDMSBCF batch job can't be used as an export file for two reasons:
1. There are column headings, and
2. The "wrap around" effect of the column output due to the record length restriction.
Environment
Resolution
With the SET OPTIONS OUTPUT directive in IDMSBCF, it is possible to direct the output to a specific DDNAME which can then be pointed to a dataset with the necessary LRECL, and so there is no wrap around effect.
The width of each column is considered to be the greater of:-
* the length of the column data, and
* the length of the column name in the SQL result set output.
Typically the length of a column name is quite large and so by default (e.g. SELECT *), the output file contains columns that are probably much wider than desired.
By using very small alias names in the SQL statement, the output will always be the width of the data in each column.
Even though they don't appear in the output, their length dictates the length of the column.
For example, use this in IDMSBCF to export the data in the EMPLOYEE record (using EMPNET as the SQL schema for EMPSCHM):
//SYSIPT DD *
SET OPTIONS OUTPUT TO SQLOUT;
CONNECT TO APPLDB;
SELECT
EMP_ID_0415 AS C1,
EMP_FIRST_NAME_0415 AS C2,
EMP_LAST_NAME_0415 AS C3,
EMP_STREET_0415 AS C4,
EMP_CITY_0415 AS C5,
EMP_STATE_0415 AS C6,
EMP_ZIP_FIRST_FIVE_0415 AS C7,
EMP_ZIP_LAST_FOUR_0415 AS C8,
EMP_PHONE_0415 AS C9,
STATUS_0415 AS CA,
SS_NUMBER_0415 AS CB,
START_YEAR_0415 AS CC,
START_MONTH_0415 AS CD,
START_DAY_0415 AS CE,
TERMINATION_YEAR_0415 AS CF,
TERMINATION_MONTH_0415 AS CG,
TERMINATION_DAY_0415 AS CH,
BIRTH_YEAR_0415 AS CI,
BIRTH_MONTH_0415 AS CJ,
BIRTH_DAY_0415 AS CK
FROM EMPNET.EMPLOYEE;
//SQLOUT DD DSN=your.dataset.name,SPACE=(CYL,(10,10)),
// DCB=(RECFM=FB,LRECL=300,BLKSIZE=3000),STORCLAS=xxxx,
// DISP=(NEW,CATLG,DELETE)
The only other problem is that there is a two-blank gap between each column and there is no way to change that.
One way of addressing that is to use the concatenation operator - || - to concatenate all the columns of the output to one long field, however that can get messy with numeric fields.
Use the user-written function in KD 117769: Sample IDMS user written SQL scalar function to convert numerics to a character string of a particular length.
With that in place, the following syntax will create comma-separated data:-
//SYSIPT DD *
SET OPTIONS OUTPUT TO SQLOUT;
CONNECT TO APPLDB;
SELECT
SAMPSQL.RJZP(EMP_ID_0415,4)
|| ',' || EMP_FIRST_NAME_0415
|| ',' || EMP_LAST_NAME_0415
|| ',' || EMP_STREET_0415
|| ',' || EMP_CITY_0415
|| ',' || EMP_STATE_0415
|| ',' || EMP_ZIP_FIRST_FIVE_0415
|| ',' || EMP_ZIP_LAST_FOUR_0415
|| ',' || SAMPSQL.RJZP(EMP_PHONE_0415,10)
|| ',' || STATUS_0415
|| ',' || SAMPSQL.RJZP(SS_NUMBER_0415,9)
|| ',' || SAMPSQL.RJZP(START_YEAR_0415,4)
|| ',' || SAMPSQL.RJZP(START_MONTH_0415,2)
|| ',' || SAMPSQL.RJZP(START_DAY_0415,2)
|| ',' || SAMPSQL.RJZP(TERMINATION_YEAR_0415,4)
|| ',' || SAMPSQL.RJZP(TERMINATION_MONTH_0415,2)
|| ',' || SAMPSQL.RJZP(TERMINATION_DAY_0415,2)
|| ',' || SAMPSQL.RJZP(BIRTH_YEAR_0415,4)
|| ',' || SAMPSQL.RJZP(BIRTH_MONTH_0415,2)
|| ',' || SAMPSQL.RJZP(BIRTH_DAY_0415,2)
AS C
FROM EMPNET.EMPLOYEE;
Feedback
