DSNTIAUL
Use the DSNTIAUL program to unload data from Db2® tables into sequential data sets. The data is copied to the data sets and is not deleted from the table.
When multi-row fetch is used, parallelism might be disabled in the last parallel group in the top-level query block for a query. For very simple queries, parallelism might be disabled for the entire query when multi-row fetch is used. To obtain full parallelism when running DSNTIAUL, switch DSNTIAUL to single-row fetch mode by specifying 1 for the number of rows per fetch parameter.
DSNTIAUL uses SQL to access Db2. Operations on a row-level or column-level access control enforced table are subject to the rules specified for the access control. If the table is row-level access control enforced, DSNTIAUL receives and returns only the rows of the table that satisfy the row permissions for the user. If the table is column-level access control enforced, DSNTIAUL receives and returns the values in the column values as modified by the column masks for the user.
DSNTIAUL parameters:
- SQL
- Specify
SQL to indicate that your input data set contains one or more complete
SQL statements, each of which ends with a semicolon. You can include
any SQL statement that can be executed dynamically in your input data
set. In addition, you can include the static SQL statements CONNECT,
SET CONNECTION, or RELEASE. Static SQL statements must be uppercase.
DSNTIAUL uses the SELECT statements to determine which tables to unload and dynamically executes all other statements except CONNECT, SET CONNECTION, and RELEASE. DSNTIAUL executes CONNECT, SET CONNECTION, and RELEASE statically to connect to remote locations.
- number of rows per fetch
- Specify a number from 1 to 32767 to specify the number of rows per fetch that DSNTIAUL retrieves. If you do not specify this number, DSNTIAUL retrieves 100 rows per fetch. This parameter can be specified with the SQL parameter.
- TOLWARN
- Specify NO (the default) or YES to indicate whether DSNTIAUL continues
to retrieve rows after receiving an SQL warning:
- (NO)
- If a warning occurs when DSNTIAUL executes an OPEN or FETCH to retrieve rows, DSNTIAUL stops retrieving rows. If the SQLWARN1, SQLWARN2, SQLWARN6, or SQLWARN7 flag is set when DSNTIAUL executes a FETCH to retrieve rows, DSNTIAUL continues to retrieve rows.
- (YES)
- If a warning occurs when DSNTIAUL executes an OPEN or FETCH to retrieve rows, DSNTIAUL continues to retrieve rows.
(QUIET)
The same as YES except that the program suppresses all SQL warning messages from OPEN or FETCH
statements if the SQLCODE is 0 or greater.
- LOBFILE(prefix)
- Specify LOBFILE to indicate that you want DSNTIAUL to dynamically allocate data sets, each to
receive the full content of a LOB cell. (A LOB cell is the intersection of a row and a LOB column.)
If you do not specify the LOBFILE option, you can unload up to only 32 KB of data from a LOB column.
- prefix
- Specify a high-level qualifier for these dynamically allocated data sets. You can specify up to 17 characters. The qualifier must conform with the rules for TSO data set names.
DSNTIAUL uses a naming convention for these dynamically allocated data sets of prefix.Qiiiiiii.Cjjjjjjj.Rkkkkkkk, where these qualifiers have the following values:- prefix
- The high-level qualifier that you specify in the LOBFILE option.
- Qiiiiiii
- The sequence number (starting from 0) of a query that returns one or more LOB columns
- Cjjjjjjj
- The sequence number (starting from 0) of a column in a query that returns one or more LOB columns
- Rkkkkkkk
- The sequence number (starting from 0) of a row of a result set that has one or more LOB columns.
The generated LOAD statement contains LOB file reference variables that can be used to load data from these dynamically allocated data sets.
table or view name [WHERE conditions] [ORDER BY columns]
Each
input statement must be a valid SQL SELECT statement with the clause
SELECT * FROM omitted and with no ending semicolon. DSNTIAUL generates
a SELECT statement for each input statement by appending your input
line to SELECT * FROM, then uses the result to determine which tables
to unload. For this input format, the text for each table specification
can be a maximum of 72 bytes and must not span multiple lines.You can use the input statements to specify SELECT statements that join two or more tables or select specific columns from a table. If you specify columns, you need to modify the LOAD statement that DSNTIAUL generates.
DSNTIAUL data sets:
- Data set
- Description
- SYSIN
- Input data set.
You cannot enter comments in DSNTIAUL input.
The record length for the input data set must be at least 72 bytes. DSNTIAUL reads only the first 72 bytes of each record.
- SYSPRINT
- Output data set. DSNTIAUL writes informational and error messages
in this data set.
The record length for the SYSPRINT data set is 121 bytes.
- SYSPUNCH
- Output data set. DSNTIAUL writes the LOAD utility control statements in this data set.
- SYSRECnn
- Output data sets. The value nn ranges from 00 to 99. You can have a maximum of 100 output data sets for a single execution of DSNTIAUL. Each data set contains the data that is unloaded when DSNTIAUL processes a SELECT statement from the input data set. Therefore, the number of output data sets must match the number of SELECT statements (if you specify parameter SQL) or table specifications in your input data set.
Define all data sets as sequential data sets. You can specify the record length and block size of the SYSPUNCH and SYSRECnn data sets. The maximum record length for the SYSPUNCH and SYSRECnn data sets is 32760 bytes.
DSNTIAUL return codes:
| Return code | Meaning |
|---|---|
| 0 | Successful completion. |
| 4 | An SQL statement received a warning
code.
|
| 8 | An SQL statement received an error code. If the SQL statement was a SELECT statement, Db2 did not perform the associated unload operation or did not complete it. |
| 12 | DSNTIAUL could not open a data set, an SQL statement returned a severe error code (-144, -302, -804, -805, -818, -902, -906, -911, -913, -922, -923, -924, or -927), or an error occurred in the SQL message formatting routine. |
Example of using DSNTIAUL to unload a subset of rows in a table:
Suppose that you want to unload the rows for department D01 from the project table. Because you can fit the table specification on one line, and you do not want to execute any non-SELECT statements, you do not need the SQL parameter. Your invocation looks like the one that is shown in the following figure:
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIBB1) -
LIB('DSN1110.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *DSN8B10
.PROJ WHERE DEPTNO='D01'
Example of using DSNTIAUL to unload rows in more than one table:
Suppose that you also want to use DSNTIAUL to perform the following actions:
- Unload all rows from the project table
- Unload only rows from the employee table for employees in departments with department numbers that begin with D, and order the unloaded rows by employee number
- Lock both tables in share mode before you unload them
- Retrieve 250 rows per fetch
For these activities, you must specify the SQL parameter and specify the number of rows per fetch when you run DSNTIAUL. Your DSNTIAUL invocation is shown in the following figure:
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIBB1) PARMS('SQL,250') -
LIB('DSN1110.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSREC01 DD DSN=DSN8UNLD.SYSREC01,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
LOCK TABLE DSN8B10.EMP IN SHARE MODE;
LOCK TABLE DSN8B10.PROJ IN SHARE MODE;
SELECT * FROM DSN8B10.PROJ;
SELECT * FROM DSN8B10.EMP
WHERE WORKDEPT LIKE 'D%'
ORDER BY EMPNO;
Example of using DSNTIAUL to obtain LOAD utility control statements:
If you want to obtain the LOAD utility control statements for loading rows into a table, but you do not want to unload the rows, you can set the data set names for the SYSRECnn data sets to DUMMY. For example, to obtain the utility control statements for loading rows into the department table, you invoke DSNTIAUL as shown in the following figure:
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIBB1) -
LIB('DSN1110.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DUMMY
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *DSN8B10
.DEPT
Example of using DSNTIAUL to unload LOB data:
This example uses the sample LOB table with the following structure:
CREATE TABLE DSN8910.EMP_PHOTO_RESUME
( EMPNO CHAR(06) NOT NULL,
EMP_ROWID ROWID NOT NULL GENERATED ALWAYS,
PSEG_PHOTO BLOB(500K),
BMP_PHOTO BLOB(100K),
RESUME CLOB(5K),
PRIMARY KEY (EMPNO))
IN DSN8D91L.DSN8S91B
CCSID EBCDIC;
The following call to DSNTIAUL unloads the sample LOB table. The parameters for DSNTIAUL indicate the following options:
- The input data set (SYSIN) contains SQL.
- DSNTIAUL is to retrieve 2 rows per fetch.
- DSNTIAUL places the LOB data in data sets with a high-level qualifier of DSN8UNLD.
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB91) -
PARMS('SQL,2,LOBFILE(DSN8UNLD)') -
LIB('DSN910.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB
//SYSIN DD *
SELECT * FROM DSN8910.EMP_PHOTO_RESUME;
Given that the sample LOB table has 4 rows of data, DSNTIAUL produces the following output:
- Data for columns EMPNO and EMP_ROWID are placed in the data set that is allocated according to the SYSREC00 DD statement. The data set name is DSN8UNLD.SYSREC00
- A generated LOAD statement is placed in the data set that is allocated according to the SYSPUNCH DD statement. The data set name is DSN8UNLD.SYSPUNCH
- The following data sets are dynamically created to store LOB data:
- DSN8UNLD.Q0000000.C0000002.R0000000
- DSN8UNLD.Q0000000.C0000002.R0000001
- DSN8UNLD.Q0000000.C0000002.R0000002
- DSN8UNLD.Q0000000.C0000002.R0000003
- DSN8UNLD.Q0000000.C0000003.R0000000
- DSN8UNLD.Q0000000.C0000003.R0000001
- DSN8UNLD.Q0000000.C0000003.R0000002
- DSN8UNLD.Q0000000.C0000003.R0000003
- DSN8UNLD.Q0000000.C0000004.R0000000
- DSN8UNLD.Q0000000.C0000004.R0000001
- DSN8UNLD.Q0000000.C0000004.R0000002
- DSN8UNLD.Q0000000.C0000004.R0000003