Exports data from a database to one
of several external file formats.
The user specifies the
data to be exported by supplying an SQL SELECT statement, or by providing
hierarchical information for typed tables.
Authorization
One of the following authorities:
- DATAACCESS authority
- CONTROL or SELECT privilege on each participating table or view
Label-based access control (LBAC) is enforced for this
function. The data that is exported may be limited by the LBAC credentials
of the caller if the data is protected by LBAC.
Required connection
Database. If implicit
connect is enabled, a connection to the default database is established.
API include file
db2ApiDf.h
API and data structure syntax
SQL_API_RC SQL_API_FN
db2Export (
db2Uint32 versionNumber,
void * pParmStruct,
struct sqlca * pSqlca);
typedef SQL_STRUCTURE db2ExportStruct
{
char *piDataFileName;
struct sqlu_media_list *piLobPathList;
struct sqlu_media_list *piLobFileList;
struct sqldcol *piDataDescriptor;
struct sqllob *piActionString;
char *piFileType;
struct sqlchar *piFileTypeMod;
char *piMsgFileName;
db2int16 iCallerAction;
struct db2ExportOut *poExportInfoOut;
struct db2ExportIn *piExportInfoIn;
struct sqlu_media_list *piXmlPathList;
struct sqlu_media_list *piXmlFileList;
} db2ExportStruct;
typedef SQL_STRUCTURE db2ExportIn
{
db2Uint16 *piXmlSaveSchema;
} db2ExportIn;
typedef SQL_STRUCTURE db2ExportOut
{
db2Uint64 oRowsExported;
} db2ExportOut;
SQL_API_RC SQL_API_FN
db2gExport (
db2Uint32 versionNumber,
void * pParmStruct,
struct sqlca * pSqlca);
typedef SQL_STRUCTURE db2gExportStruct
{
char *piDataFileName;
struct sqlu_media_list *piLobPathList;
struct sqlu_media_list *piLobFileList;
struct sqldcol *piDataDescriptor;
struct sqllob *piActionString;
char *piFileType;
struct sqlchar *piFileTypeMod;
char *piMsgFileName;
db2int16 iCallerAction;
struct db2ExportOut *poExportInfoOut;
db2Uint16 iDataFileNameLen;
db2Uint16 iFileTypeLen;
db2Uint16 iMsgFileNameLen;
struct db2ExportIn *piExportInfoIn;
struct sqlu_media_list *piXmlPathList;
struct sqlu_media_list *piXmlFileList;
} db2gExportStruct;
db2Export API parameters
- versionNumber
- Input. Specifies the version and release level of the structure
passed as the second parameter pParmStruct.
- pParmStruct
- Input. A pointer to the db2ExportStruct structure.
- pSqlca
- Output. A pointer to the sqlca structure.
db2ExportStruct data structure parameters
- piDataFileName
- Input. A string containing the path and the name of the external
file into which the data is to be exported.
- piLobPathList
- Input. Pointer to an sqlu_media_list structure
with its media_type field set to SQLU_LOCAL_MEDIA, and its sqlu_media_entry structure listing paths
on the client where the LOB files are to be stored. Exported LOB data
will be distributed evenly among all the paths listed in the sqlu_media_entry structure.
- piLobFileList
- Input. Pointer to an sqlu_media_list structure
with its media_type field set to SQLU_CLIENT_LOCATION, and its sqlu_location_entry structure containing
base file names.
When the name space is exhausted using the first
name in this list, the API will use the second name, and so on. When
creating LOB files during an export operation, file names are constructed
by appending the current base name from this list to the current path
(from piLobPathList), and then appending a 3-digit
sequence number and the .lob extension. For example, if the current
LOB path is the directory /u/test01/lob/path,
the current LOB file name is bar, and the LOBSINSEPFILES file type modifier is set, then the created LOB files will be /u/test01/LOB/path/bar.001.lob, /u/test01/LOB/path/bar.002.lob, and so on. If the LOBSINSEPFILES file type
modifier is not set, then all the LOB documents will be concatenated
and put into one file /u/test01/lob/path/bar.001.lob
- piDataDescriptor
- Input. Pointer to an sqldcol structure specifying
the column names for the output file. The value of the dcolmeth field determines how the remainder of the information provided in
this parameter is interpreted by the export utility. Valid values
for this parameter (defined in sqlutil header
file, located in the include directory) are:
- SQL_METH_N
- Names. Specify column names to be used in the output file.
- SQL_METH_D
- Default. Existing column names from the table are to be used in
the output file. In this case, the number of columns and the column
specification array are both ignored. The column names are derived
from the output of the SELECT statement specified in piActionString.
- piActionString
- Input. Pointer to an sqllob structure containing
a valid dynamic SQL SELECT statement. The structure contains a 4-byte
long field, followed by the characters that make up the SELECT statement.
The SELECT statement specifies the data to be extracted from the database
and written to the external file.
The columns for the external
file (from piDataDescriptor), and the database
columns from the SELECT statement, are matched according to their
corresponding list/structure positions. The first column of data selected
from the database is placed in the first column of the external file,
and its column name is taken from the first element of the external
column array.
- piFileType
- Input. A string that indicates the format of the data within the
external file. Supported external file formats (defined in sqlutil header file) are:
- SQL_DEL
- Delimited ASCII, for exchange with dBase, BASIC, and the IBM® Personal Decision Series programs,
and many other database managers and file managers.
- SQL_IXF
- PC version of the Integration Exchange Format, the preferred method
for exporting data from a table. Data exported to this file format
can later be imported or loaded into the same table or into another
database manager table.
- piFileTypeMod
- Input. A pointer to an sqldcol structure containing
a 2-byte long field, followed by an array of characters that specify
one or more processing options. If this pointer is NULL, or the structure
pointed to has zero characters, this action is interpreted as selection
of a default specification.
Not all options can be used with all
of the supported file types. See related link: "File type modifiers
for the export utility."
- piMsgFileName
- Input. A string containing the destination for error, warning,
and informational messages returned by the utility. It can be the
path and the name of an operating system file or a standard device.
If the file already exists, the information is appended . If it does
not exist, a file is created.
- iCallerAction
- Input. An action requested by the caller. Valid values (defined
in sqlutil header file, located in the include directory) are:
- SQLU_INITIAL
- Initial call. This value must be used on the first call to the
API. If the initial call or any subsequent call returns and requires
the calling application to perform some action before completing the
requested export operation, the caller action must be set to one of
the following values:
- SQLU_CONTINUE
- Continue processing. This value can only be used on subsequent
calls to the API, after the initial call has returned with the utility
requesting user input (for example, to respond to an end of tape condition).
It specifies that the user action requested by the utility has completed,
and the utility can continue processing the initial request.
- SQLU_TERMINATE
- Terminate processing. This value can only be used on subsequent
calls to the API, after the initial call has returned with the utility
requesting user input (for example, to respond to an end of tape condition).
It specifies that the user action requested by the utility was not
performed, and the utility is to terminate processing the initial
request.
- poExportInfoOut
- A pointer to the db2ExportOut structure.
- piExportInfoIn
- Input. Pointer to the db2ExportIn structure.
- piXmlPathList
- Input. Pointer to an sqlu_media_list structure
with its media_type field set to SQLU_LOCAL_MEDIA, and its sqlu_media_entry structure listing paths
on the client where the XML files are to be stored. Exported XML data
will be distributed evenly among all the paths listed in the sqlu_media_entry structure.
- piXmlFileList
- Input. Pointer to an sqlu_media_list structure
with its media_type field set to SQLU_CLIENT_LOCATION, and its sqlu_location_entry structure containing
base file names.
When the name space is exhausted using the first
name in this list, the API will use the second name, and so on. When
creating XML files during an export operation, file names are constructed
by appending the current base name from this list to the current path
(from piXmlFileList), and then appending a 3-digit
sequence number and the .xml extension. For example, if the current
XML path is the directory /u/test01/xml/path,
the current XML file name is bar, and the XMLINSEPFILES file type modifier is set, then the created XML files will be /u/test01/xml/path/bar.001.xml, /u/test01/xml/path/bar.002.xml, and so on. If the XMLINSEPFILES file type modifier
is not set, then all the XML documents will be concatenated and put
into one file /u/test01/xml/path/bar.001.xml
db2ExportIn data structure parameters
- piXmlSaveSchema
- Input. Indicates that the SQL identifier of the XML schema used
to validate each exported XML document should be saved in the exported
data file. Possible values are TRUE and FALSE.
db2ExportOut data structure parameters
- oRowsExported
- Output. Returns the number of records exported to the target file.
db2gExportStruct data structure specific parameters
- iDataFileNameLen
- Input. A 2-byte unsigned integer representing the length in bytes
of the data file name.
- iFileTypeLen
- Input. A 2-byte unsigned integer representing the length in bytes
of the file type.
- iMsgFileNameLen
- Input. A 2-byte unsigned integer representing the length in bytes
of the message file name.
Usage notes
Before starting an export operation,
you must complete all table operations and release all locks in one
of two ways:
- Close all open cursors that were defined with the WITH HOLD clause,
and commit the data changes by executing the COMMIT statement.
- Roll back the data changes by executing the ROLLBACK statement.
Table aliases can be used in the SELECT statement.
The messages placed in the message file include the information
returned from the message retrieval service. Each message begins on
a new line.
If the export utility produces warnings, the message
will be written out to a message file, or standard output if one is
not specified.
A warning message is issued if the number of
columns (dcolnum field of sqldcol structure) in the external column name array, piDataDescriptor, is not equal to the number of columns generated by the SELECT statement.
In this case, the number of columns written to the external file is
the lesser of the two numbers. Excess database columns or external
column names are not used to generate the output file.
If the db2uexpm.bnd module or any other shipped .bnd files are
bound manually, the format option on the binder must not be used.
DB2 Connect™ can be
used to export tables from DRDA® servers such as DB2® for z/OS® and OS/390®, DB2 for VM and VSE, and DB2 for System i®. Only PC/IXF export
is supported.
PC/IXF import should be used to move data between
databases. If character data containing row separators is exported
to a delimited ASCII (DEL) file and processed by a text transfer program,
fields containing the row separators will shrink or expand.
The export utility will not create multiple-part PC/IXF files when
invoked from an AIX® system.
Index definitions for a table are included in the PC/IXF file
when the contents of a single database table are exported to a PC/IXF
file with a piActionString parameter beginning
with SELECT * FROM tablename, and the piDataDescriptor parameter specifying default names. Indexes are not saved for views,
or if the SELECT clause of the piActionString includes a join. A WHERE clause, a GROUP BY clause, or a HAVING
clause in the piActionString parameter will not
prevent the saving of indexes. In all of these cases, when exporting
from typed tables, the entire hierarchy must be exported.
The
export utility will store the NOT NULL WITH DEFAULT attribute of the
table in an IXF file if the SELECT statement provided is in the form:
SELECT * FROM tablename.
When exporting typed tables, subselect
statements can only be expressed by specifying the target table name
and the WHERE clause. Fullselect and select-statement cannot be specified
when exporting a hierarchy.
For file formats other than IXF,
it is recommended that the traversal order list be specified, because
it tells DB2 how to traverse
the hierarchy, and what sub-tables to export. If this list is not
specified, all tables in the hierarchy are exported, and the default
order is the OUTER order. The alternative is to use the default order,
which is the order given by the OUTER function.
Note: Use the same
traverse order during an import operation. The load utility does not
support loading hierarchies or sub-hierarchies.
REXX API syntax
EXPORT :stmt TO datafile OF filetype
[MODIFIED BY :filetmod] [USING :dcoldata]
MESSAGES msgfile [ROWS EXPORTED :number]
CONTINUE EXPORT
STOP EXPORT
REXX API parameters
- stmt
- A REXX host variable containing a valid dynamic SQL SELECT statement. The
statement specifies the data to be extracted from the database.
- datafile
- Name of the file into which the data is to be exported.
- filetype
- The format of the data in the export file. The supported file
formats are:
- DEL
- Delimited ASCII.
- IXF
- PC version of Integration Exchange Format.
- filetmod
- A host variable containing additional processing options.
- dcoldata
- A compound REXX host variable containing the column names to be used in
the export file. In the following, XXX represents
the name of the host variable:
- XXX.0
- Number of columns (number of elements in the remainder of the
variable).
- XXX.1
- First column name.
- XXX.2
- Second column name.
- XXX.3
- and so on.
If this parameter is NULL, or a value
for dcoldata has not been specified, the utility
uses the column names from the database table.
- msgfile
- File, path, or device name where error and warning messages are
to be sent.
- number
- A host variable that will contain the number of exported rows.
DB2 Version 10.1 for Linux, UNIX, and Windows