Database Services Utility DATAUNLOAD processing enables you to unload data from tables and views to a user-defined sequential access method (SAM) file record format. You can also unload data from |remote application servers that support the DRDA flow. The data to be unloaded is selected from the database with an SQL SELECT statement that you supply. The output data file for this utility is a general-use programming interface. See "Programming Interface Information" for a definition of general-use programming interfaces.
In general, each output record resulting from DATAUNLOAD processing contains data for a row of a table. These output data records reside in a sequential file. In a VM system, you must define this file using the CMS FILEDEF command.
The sequential output file can contain fixed, variable-length, or variable-length-spanned records. The records can be blocked or unblocked. A standard SELECT statement in its SQL syntax is used in the DATAUNLOAD command set as a mandatory subcommand. The DATAUNLOAD command and its subcommands can:
A DATAUNLOAD command contains four elements, as shown in Figure 29.
Figure 29. DATAUNLOAD Command Sequence
.-----------------. |The DATAUNLOAD | |command statement+---------> DATAUNLOAD '-----------------' | SELECT EMPNO,PROJNO,ACTNO,EMPTIME .-------------. | FROM EMP-ACT |An SQL SELECT+------------>| WHERE EMPTIME>0.5 |statement | | ORDER BY ACTNO; '-------------' .--------------. | EMPNO 1-6 |DATA FIELD | | PROJNO 8-14 CHAR |IDENTIFICATION+----------->| ACTNO 16-18 INTEGER |subcommands | | EMPTIME 20-25 DECIMAL '--------------' .------------------. |OUTFILE subcommand+--------> OUTFILE(INVOUT) '------------------' |
The DATAUNLOAD command statement consists of a single word and occupies the first record of the command set in the Database Services Utility (input) control file.
The SQL statement, the second element in the command set, occupies one or more control file input records. Its syntax and sequence of keywords are the same as they would be if used outside the Database Services Utility.
| Entering Commands from a Workstation |
|---|
|
Most Database Services Utility commands and all SQL statements must end with a semicolon (;) when the control file is assigned to TERMINAL. In general, use a semicolon to terminate all commands entered through your workstation. |
Data Field Identification (DFI) subcommands, element three of DATAUNLOAD commands, identify the location in the output records for the data of columns specified in the select-list parameter. DFI subcommands are optional; if they are omitted, the resulting output data fields are sequenced according to system defaults. See Unloading Data in System-Defined Format for more information. If DFI subcommands are included, each must occupy a single record of the DB2 Server for VSE input control card file or DB2 Server for VM command-file input record.
The OUTFILE subcommand, the final element in the DATAUNLOAD command set, identifies the sequential output file that is to contain the data unloaded by the preceding DATAUNLOAD command sequence. It tells the Database Services Utility to start unloading data to the file identified by the corresponding ddname parameter. In a VM system, this parameter is defined in the FILEDEF command.
The DATAUNLOAD command and subcommands are contained on more than one input record. If you want to unload all the data from a table in a system-determined sequence and with the default output data field format, the three parts of the Database Services Utility command are:
| Default Output Data Field Formats |
|---|
|
If you do not supply DFI subcommands in a DATAUNLOAD command set and if the source table column contains double-byte character set (DBCS) data, the default data type for the output data fields is CHARACTER or GRAPHIC data type. The overall format of the output data depends on the data type and length (actual or maximum) of the column from which the data is taken. Figure 30 and Figure 31 show the default output data field sequence. |
If you do not supply DFI subcommands, the data fields appear in the output records in the order of occurrence of columns in the SELECT statement's select-list parameter. Each field is separated from the next by a blank position (hex 40).
For fixed-length output records, the data field associated with the first select-list column starts in position 1 of the record, as shown in Figure 30.
Figure 30. Default Data Field Sequence--Fixed-Length Output Records
.-------------.-------.--------------.-------.-------.--------------. | data from | blank | data from | blank | ... | data from | | select-list | | select-list | | ... | select-list | | column 1 | | column 2 | | ... | column n | '-------------'-------'--------------'-------'-------'--------------' . . . Position 1 |
For variable-length output records, the data field associated with the select-list column starts in position 5 of the record because the first 4 bytes are the record length control field. Figure 31 shows the data fields for variable-length output records.
Figure 31. Default Data Field Sequence--Variable-Length Output Records
.----------.-------------.-------.-------------.--------.-----.-------.-------------. | Record | data from | blank | data from | blank | ... | blank | data from | | Length | select-list | | select-list | | ... | | select-list | | Control | column 1 | | column 2 | | ... | | column n | | Field | | | | | | | | '----------'-------------'-------'-------------'--------'-----'-------'-------------' . . . . . . . Position 5 . Position 1 |
In DB2 Server for VSE, proceed as follows to unload data in the default output-field format.
DATAUNLOAD
Figure 32. DATAUNLOAD Command without DFI Subcommands
DATAUNLOAD SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME FROM EMP_ACT,EMPLOYEE WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO ORDER BY EMP_ACT.EMPNO; OUTFILE(OUTPUT1) |
OUTFILE (ddname)
where ddname identifies the output file. Use the same ddname in a TLBL or DLBL statement, depending on whether you want to store the data on tape or in a DASD file.
In DB2 Server for VM, proceed as follows to unload data in the default output-field format.
DATAUNLOAD
Figure 33. DATAUNLOAD Command without DFI Subcommands
DATAUNLOAD SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME FROM EMP_ACT,EMPLOYEE WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO ORDER BY EMP_ACT.EMPNO; OUTFILE(OUTPUT1) |
OUTFILE(ddname)
where ddname identifies the output data file. You need to use the same ddname when you specify the FILEDEF statement for the output file.
As the DATAUNLOAD command is executed, the actual command sequence, the default data-field sequence, and messages are written to the report or message file. Figure 34 and Figure 35 show output that results from running the DATAUNLOAD command shown in Figure 33.
Figure 34. Database Services Utility DB2 Server for VSE Report Output: Default Data Fields
ARI0801I DBS Utility started: 07/24/89 10:26:44.
AUTOCOMMIT = OFF ERRORMODE = OFF
ISOLATION LEVEL = REPEATABLE READ
-------> CONNECT "SQLDBA " IDENTIFIED BY ********;
ARI8004I User SQLDBA connected to database SQLDBA.
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
------->
-------> DATAUNLOAD
-------> SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME
-------> FROM EMP_ACT,EMPLOYEE
-------> WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO
-------> ORDER BY EMP_ACT.EMPNO;
-------> OUTFILE(OUTPUT1)
ARI0852I DATAUNLOAD processing started.
ARI0868I DNAME=OUTPUT1 RECFM=F RECSZ=80 BLKSIZE=80 <-- See Note
ARI0836I Default output record data field positions:
ARI0837I EMPNO 1-6
ARI0837I PROJNO 8-13
ARI0837I EMPTIME 15-21
ARI0835I 74 record(s) written to the output data file.
ARI0855I DATAUNLOAD processing successful.
ARI0802I End of command file input.
ARI8997I ...Begin COMMIT processing.
ARI0811I ...COMMIT of any database changes successful.
ARI0809I ...No error(s) occurred during command processing.
ARI0808I DBS processing completed: 07/24/89 10:26:45.
|
Figure 35. Database Services Utility DB2 Server for VM Message File Output: Default Data Fields
1ARI0801I DBS Utility started: 07/24/89 10:26:44.
AUTOCOMMIT = OFF ERRORMODE = OFF
ISOLATION LEVEL = REPEATABLE READ
0------> DATAUNLOAD
-------> SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME
-------> FROM EMP_ACT,EMPLOYEE
-------> WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO
-------> ORDER BY EMP_ACT.EMPNO;
-------> OUTFILE(OUTPUT1)
ARI0852I DATAUNLOAD processing started.
ARI0868I DNAME=OUTPUT1 RECFM=F RECSZ=80 BLKSIZE=80 <-- See Note
ARI0836I Default output record data field positions:
ARI0837I EMPNO 1-6
ARI0837I PROJNO 8-13
ARI0837I EMPTIME 15-21
ARI0835I 74 record(s) written to the output data file.
ARI0855I DATAUNLOAD processing successful.
ARI0802I End of command file input.
ARI8997I ...Begin COMMIT processing.
ARI0811I ...COMMIT of any database changes successful.
ARI0809I ...No error(s) occurred during command processing.
ARI0808I DBS processing completed: 07/24/89 10:26:45.
|
| Note: | The RECFM, RECSZ, and BLKSIZE information displayed in the message ARI0868I depends on either the JCL data definition statement or the CMS FILEDEF command specifications for the output file with ddname=OUTPUT1. |
The records in the output data file are formatted according to
system-determined (default) criteria. Table 1 shows the data field format resulting from the DATAUNLOAD
command specification given in Figure 33.
Table 1. Output Record Format That is System Determined
| Record Position | Data Value Source (Column or Other) | Output Record Field Data Type |
|---|---|---|
| 1-6 | EMPNO | CHAR |
| 7 | (blank) | CHAR |
| 8-13 | PROJNO | CHAR |
| 14 | (blank) | CHAR |
| 15-21 | EMPTIME | CHAR |
If you want to unload all the data from a table in a specific sequence and with user-specified output data record field formats, the four parts of the Database Services Utility command are:
The data for a DFI-referenced column is in the same positions in all the output data records.
| Provide DFIs for All Table Columns--or None |
|---|
|
A DFI subcommand identifies the location in the output record where you want to place the unloaded data. For example, suppose TABLE1 has five columns and you enter the following SELECT statement: SELECT * FROM TABLE1 or SELECT colname1,colname2,colname3,colname4,colname5 FROM TABLE1 If you supply only three DFI subcommands, the Database Services Utility only unloads the three table columns identified in the subcommands. If you want to unload data for all five columns and you want to specify your own output record format for any of the columns, you must supply five DFI subcommands. |
The difference between unloading data in system-defined and user-defined format is the presence of DFI subcommands. A DFI subcommand identifies the location in the output records where the data for a column specified in the select-list parameter should be placed. The DFI also identifies the data type of its data field in the output record.
Whereas the DATAUNLOAD default output field sequence uses the order of presentation in the select-list parameter, you choose the order of fields in the output records when you supply DFI subcommands. In choosing positions for the output data-record fields, you should leave a blank position between each field for clarity, but this is not mandatory. Do not, however, use character positions 1 through 4 for output data if you have specified variable-length output records for the output file. Record positions 1-4 are reserved for the record length control field.
Figure 36 shows a DATAUNLOAD command sequence using DFI subcommands. The EMPNO field is to occupy positions 1 through 6 in the output records. The PROJNO field goes in positions 8 through 13. The EMPTIME field is to take positions 15 through 21 as data type DECIMAL.
Figure 36. DATAUNLOAD Command with DFI Subcommands
DATAUNLOAD SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME,JOB FROM EMP_ACT,EMPLOYEE WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO ORDER BY EMP_ACT.EMPNO; EMPNO 1-6 PROJNO 8-13 EMPTIME 15-21 DECIMAL OUTFILE(OUTPUT1) |
To unload data in user-specified output-file format, supply DFI subcommands. Use the standard method for unloading data in Unloading Data in System-Defined Format, but supply a DFI subcommand for each column that you want to unload. To construct a DFI subcommand, use the following format:
column-reference startpos-endpos data-type set-null-clause
You can use the set-null-clause to instruct the utility to insert a particular value whenever a null value occurs in a table that you are unloading. In the following example, you instruct the Database Services Utility to write a question mark in position 22 of the output record whenever a null field occurs in the table that is being unloaded.
Figure 37. Unloading NULL Values with DATAUNLOAD Command
DATAUNLOAD SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME,JOB FROM EMP_ACT,EMPLOYEE WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO ORDER BY EMP_ACT.EMPNO; EMPNO 1-6 PROJNO 8-13 EMPTIME 15-21 DECIMAL IF NULL SET POS(22) = '?' OUTFILE(OUTPUT1) |
As is shown in Figure 37, if the value of an EMPTIME column is null, the utility puts a ? value in output record position 22. For more information about using the set-null-clause, see page *** under "Data_Field_Id_Subcommand" in Chapter 8, "Command Reference".
| Periodic Reports during the Processing of Long Jobs |
|---|
|
During DATAUNLOAD processing of a file containing more than 15,000 data records, the message ARI8995I is written every 15,000 records to inform you that the job is running normally and that n records have been unloaded. In a VM system, these messages are written to your workstation, and in a VSE system, they are written to the system operator's console. If the number of records being read from the database is less than 15,000, you do not receive message ARI8995I. |
As the DATAUNLOAD command is executed, the actual command sequence and messages are written to the report or message file. Figure 38 and Figure 39 show report results from running the DATAUNLOAD command as shown in Figure 36.
Figure 38. Database Services Utility Report Output with User-Specified Data Fields
1ARI0801I DBS Utility started: 10/05/89 14:54:41.
AUTOCOMMIT = OFF ERRORMODE = OFF
ISOLATION LEVEL = REPEATABLE READ
1ARI0803I ...Extended DBCS (DBCS=YES) processing now in effect.
0-------> DATAUNLOAD
-------> SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME,JOB
-------> FROM EMP_ACT,EMPLOYEE
-------> WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO
-------> ORDER BY EMP_ACT.EMPNO;
-------> EMPNO 1-6
-------> PROJNO 8-13
-------> EMPTIME 15-21 DECIMAL IF NULL SET POS(22) = '?'
-------> OUTFILE(OUTPUT1)
ARI0852I DATAUNLOAD processing started.
ARI0831I Column JOB data will not be unloaded.
ARI0868I DNAME=OUTPUT1 RECFM=F RECSZ=80 BLKSIZE=80 <-- See Note
ARI0835I 74 record(s) written to the output data file.
ARI0855I DATAUNLOAD processing successful.
ARI0802I End of command file input.
ARI8997I ...Begin COMMIT processing.
ARI0811I ...COMMIT of any database changes successful.
ARI0809I ...No error(s) occurred during command processing.
ARI0808I DBS processing completed: 10/05/89 10:54:44.
|
Figure 39. Database Services Utility Message File Output with User-Specified Data Fields
ARI0801I DBS Utility started: 10/05/89 14:54:41.
AUTOCOMMIT = OFF ERRORMODE = OFF
ISOLATION LEVEL = REPEATABLE READ
-------> CONNECT "SQLDBA " IDENTIFIED BY ********;
ARI8004I User SQLDBA connected to database SQLDBA.
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
------->
ARI8003I ...Extended DBCS (DBCS=YES) processing now in effect.
-------> DATAUNLOAD
-------> SELECT EMP_ACT.EMPNO,PROJNO,EMPTIME,JOB
-------> FROM EMP_ACT,EMPLOYEE
-------> WHERE EMP_ACT.EMPNO=EMPLOYEE.EMPNO
-------> ORDER BY EMP_ACT.EMPNO;
-------> EMPNO 1-6
-------> PROJNO 8-13
-------> EMPTIME 15-21 DECIMAL IF NULL SET POS(22) = '?'
-------> OUTFILE(OUTPUT1)
ARI0831I Column JOB data will not be unloaded.
ARI0868I DNAME=OUTPUT1 RECFM=F RECSZ=80 BLKSIZE=80
ARI0835I 74 record(s) written to the output data file.
ARI0855I DATAUNLOAD processing successful.
ARI0802I End of command file input.
ARI8997I ...Begin COMMIT processing.
ARI0811I ...COMMIT of any database changes successful.
ARI0809I ...No error(s) occurred during command processing.
ARI0808I DBS processing completed: 10/05/89 14:54:44.
|
| Note: | The RECFM, RECSZ, and BLKSIZE information displayed in the message ARI0868I depends on the CMS FILEDEF command specifications for the output file OUTPUT1. |
The Database Services Utility does not unload data for which no DFI subcommand exists (unless all DFIs are omitted), but the report or message file does show you the columns that are not unloaded.
The records in the output data file are formatted according to your
specifications. Table 2 shows the data field format resulting from the DATAUNLOAD
command specification shown in Figure 36.
Table 2. Output Record Format That is User Determined
| Record Position | Data Value Source (Column or Other) | Output Record Field Data Type |
|---|---|---|
| 1-6 | EMPNO | CHAR |
| 7 | (blank) | CHAR |
| 8-13 | PROJNO | CHAR |
| 14 | (blank) | CHAR |
| 15-21 | EMPTIME | DECIMAL |
| 22 |
EMPTIME (null indicator) | CHAR |
A view is a virtual table that is derived from one or more tables, from other views, or from combinations of views and tables. When views are processed and displayed or printed, they are indistinguishable from tables; they have rows and columns and, like tables, views have no inherent order of rows.
You can use the DATAUNLOAD command to unload views as if they were tables. Once unloaded, the output data of a view is the same as the data from a table.
To use the DATAUNLOAD command to unload a view, use the same procedure that you use to unload a table, but specify a view name instead of a table name in the SQL SELECT statement. You can select all the columns of the view (SELECT *), or use the select-list parameter. If you use this parameter, specify the names of view columns.
Suppose you create a view such as this:
CREATE VIEW TOSPIFFY (NUMBER,NAME,MANAGER) AS SELECT DEPTNO,DEPTNAME,MGRNO FROM DEPARTMENT WHERE ADMRDEPT = 'A00'
To unload the view, construct a DATAUNLOAD command like this:
DATAUNLOAD SELECT * FROM TOSPIFFY; NUMBER 1-3 NAME 6-42 MANAGER 45-50 IF NULL SET POS (45-50) = ' ' OUTFILE(SUBSPIF)
The DATAUNLOAD command uses the view column names, not the column names of the founding table, and null entries in the MANAGER column are represented by 6 blanks in the output data file.
The DATAUNLOAD command uses three files: the control file, the message file, and the data output file. You must define all three files, either with the SQLDBSU EXEC or a FILEDEF command. Figure 40 shows the relationship of the three files and the appropriate definition facility (FILEDEF or SQLDBSU) for each.
|
For more information on FILEDEF parameters and options, see Appendix B, FILEDEF Command Syntax and Notes.
In the FILEDEF command defining the Database Services Utility DATAUNLOAD command output data file, all record format (RECFM) values are supported except for undefined (U) or carriage controls (A or M). If you define CMS output files with variable-length spanned records (RECFM=VS or VBS), you must use the file-mode number 4.
A sample CMS FILEDEF command defining a CMS file for DATAUNLOAD command processing is:
FILEDEF DBSFILE DISK DBSFILE DATA A (RECFM FB LRECL 800 BLOCK 1600
where DBSFILE is the ddname used in the DATAUNLOAD command, and it refers to the output file DBSFILE DATA A.
If you want to print some of the data in a table, use the FILEDEF statement to specify the printer as the output device:
FILEDEF PRINTOUT PRINTER
where PRINTOUT is the ddname that you use in the DATAUNLOAD command.