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:
or CONTROL or SELECT privilege on each participating table or view.
Required Connection
Database. If implicit connect is enabled, a connection to the default database is established.
Version
sqlutil.h
C API Syntax
/* File: sqlutil.h */
/* API: Export */
/* ... */
SQL_API_RC SQL_API_FN
sqluexpr (
char * pDataFileName,
sqlu_media_list * pLobPathList,
sqlu_media_list * pLobFileList,
struct sqldcol * pDataDescriptor,
struct sqlchar * pActionString,
char * pFileType,
struct sqlchar * pFileTypeMod,
char * pMsgFileName,
short CallerAction,
struct sqluexpt_out* pOutputInfo,
void * pReserved,
struct sqlca * pSqlca);
/* ... */
|
Generic API Syntax
/* File: sqlutil.h */
/* API: Export */
/* ... */
SQL_API_RC SQL_API_FN
sqlgexpr (
unsigned short DataFileNameLen,
unsigned short FileTypeLen,
unsigned short MsgFileNameLen,
char * pDataFileName,
sqlu_media_list * pLobPathList,
sqlu_media_list * pLobFileList,
struct sqldcol * pDataDescriptor,
struct sqlchar * pActionString,
char * pFileType,
struct sqlchar * pFileTypeMod,
char * pMsgFileName,
short CallerAction,
struct sqluexpt_out* pOutputInfo,
void * pReserved,
struct sqlca * pSqlca);
/* ... */
|
API Parameters
When file space is exhausted on the first path in this list, the API will use the second path, and so on.
For more information, see SQLU-MEDIA-LIST.
When the name space is exhausted using the first name in this list, the API will use the second name, and so on.
For more information, see SQLU-MEDIA-LIST.
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 pLobFilePath), and then appending a 3-digit sequence number. For example, if the current LOB path is the directory /u/foo/lob/path, and the current LOB file name is bar, the created LOB files will be /u/foo/lob/path/bar.001, /u/foo/lob/pah/bar.002, and so on.
For more information, see SQLDCOL.
The columns for the external file (from pDataDescriptor), 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.
For more information, see SQLCHAR.
| Note: | The syntax that is to be used for typed tables is described in the Command Reference. |
Not all options can be used with all of the supported file types.
For more information, see SQLCHAR, and the Command Reference.
If the initial call or any subsequent call returns and requires the calling application to perform some action prior to completing the requested export operation, the caller action must be set to one of the following:
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
If this parameter is NULL, or a value for dcoldata has not been specified, the utility uses the column names from the database table.
Sample Programs
Usage Notes
Be sure to complete all table operations and release all locks before starting an export operation. This can be done by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK.
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.
The export utility produces a warning message whenever a character column with a length greater than 254 is selected for export to DEL format files.
A warning message is issued if the number of columns (dcolnum) in the external column name array, pDataDescriptor, 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.
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 (moving, for example, between OS/2 and AIX systems), fields containing the row separators will shrink or expand.
PC/IXF file format specifications permit migration of data between OS/2 (IBM Extended Services for OS/2, OS/2 Extended Edition and DB2 for OS/2) databases and DB2 for AIX databases via export, binary copying of files between OS/2 and AIX, and import. The file copying step is not necessary if the source and the target databases are both accessible from the same client.
DB2 Connect can be used to export tables from DRDA servers such as DB2 for OS/390, DB2 for VM and VSE, and DB2 for OS/400. Only PC/IXF export is supported.
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 pActionString beginning with SELECT * FROM tablename, and the pDataDescriptor parameter specifying default names. Indexes are not saved for views, or if the SELECT clause of the pActionString includes a join. A WHERE clause, a GROUP BY clause, or a HAVING clause in the pActionString 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. |
DB2 Data Links Manager Considerations
To ensure that a consistent copy of the table and the corresponding files referenced by the DATALINK columns are copied for export, do the following:
This ensures that no update transactions are in progress when EXPORT is run.
This makes the table available for updates.
EXPORT is executed as an SQL application. The rows and columns satisfying the SELECT statement conditions are extracted from the database. For the DATALINK columns, the SELECT statement should not specify any scalar function.
Successful execution of EXPORT results in generation of the following files:
The dlfm_export utility is provided to export files from a
Data Links server. This utility generates an archive file, which can be
used to restore files in the target Data Links server.
Table 7. Valid File Type Modifiers (Export)
| Modifier | Description | ||
|---|---|---|---|
| All File Formats | |||
| lobsinfile | lob-path specifies the path to the files containing LOB values. | ||
| DEL (Delimited ASCII) File Format | |||
| chardelx | x is a single character string delimiter. The default
value is a double quotation mark ("). The specified character is
used in place of double quotation marks to enclose a character
string.a
The single quotation mark (') can also be specified as a character string delimiter as follows: modified by chardel'' | ||
| coldelx | x is a single character column delimiter. The default
value is a comma (,). The specified character is used in place of
a comma to signal the end of a column.a
In the following example, coldel; causes the export utility to interpret any semicolon (;) it encounters as a column delimiter:
db2 "export to temp of del modified by coldel;
select * from staff where dept = 20"
| ||
| datesiso | Date format. Causes all date data values to be exported in ISO format. | ||
| decplusblank | Plus sign character. Causes positive decimal values to be prefixed with a blank space instead of a plus sign (+). The default action is to prefix positive decimal values with a plus sign. | ||
| decptx | x is a single character substitute for the period as a decimal point character. The default value is a period (.). The specified character is used in place of a period as a decimal point character.a | ||
| dldelx | x is a single character DATALINK delimiter. The default
value is a semicolon (;). The specified character is used in place
of a semicolon as the inter-field separator for a DATALINK value. It is
needed because a DATALINK value may have more than one sub-value.
ab
| ||
| nodoubledel | Suppresses recognition of double character delimiters. | ||
| WSF File Format | |||
| 1 | Creates a WSF file that is compatible with Lotus 1-2-3 Release 1, or Lotus 1-2-3 Release 1a.b This is the default. | ||
| 2 | Creates a WSF file that is compatible with Lotus Symphony Release 1.0.b | ||
| 3 | Creates a WSF file that is compatible with Lotus 1-2-3 Version 2, or Lotus Symphony Release 1.1.b | ||
| 4 | Creates a WSF file containing DBCS characters. | ||
|
Notes:
| |||
It is the user's responsibility to ensure that the chosen delimiter character is not part of the data to be moved. If it is, unexpected errors may occur. The following restrictions apply to column, string, DATALINK, and decimal point delimiters when moving data:
" (0x22, double quotation mark; string delimiter) , (0x2c, comma; column delimiter)
" (0x7F, double quotation mark; string delimiter) , (0x6B, comma; column delimiter)
db2 load from ... modified by chardel0x0C coldelX1e ...
The following information about support for double character delimiter recognition in DEL files applies to the export, import, and load utilities:
"What a ""nice"" day!"
will be imported as:
What a "nice" day!
In the case of export, the rule applies in reverse. For example,
I am 6" tall.
will be exported to a DEL file as:
"I am 6"" tall."
See Also