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.
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.
Database. If implicit connect is enabled, a connection to the default database is established.
db2ApiDf.h
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;
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/foo/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/foo/LOB/path/bar.001.lob, /u/foo/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/foo/lob/path/bar.001.lob
The columns for the external file (from piDataDescriptor), and the database columns from the SELECT statement, are matched according to their respective 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.
Not all options can be used with all of the supported file types. See related link below: "File type modifiers for the export utility."
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/foo/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/foo/xml/path/bar.001.xml, /u/foo/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/foo/xml/path/bar.001.xml
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.
EXPORT :stmt TO datafile OF filetype
[MODIFIED BY :filetmod] [USING :dcoldata]
MESSAGES msgfile [ROWS EXPORTED :number]
CONTINUE EXPORT
STOP EXPORT