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.
Authorization
One of the following:
or CONTROL or SELECT privilege on each participating table or view.
Required Connection
Database
API Include File
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.
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, 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, then the LOB files created will be /u/foo/lob/path/bar.001, /u/foo/lob/pah/bar.002, and so on.
If dcolmeth is SQL_METH_N, specified names are given for the columns in the external file.
If pDataDescriptor is NULL, or dcolmeth is set to SQL_METH_D, default names are used for the columns in the external file. In this case, the number of columns and the column specification array are both ignored. The column names in the external file are derived from the processing of the SELECT statement specified in pActionString.
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.
Not all options can be used with all of the supported file types.
For more information, see Command Reference.
If the initial call or any subsequent call returns and requires the calling application to perform some service prior to completing the requested export, 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.
Sample Programs
Usage Notes
Be sure to complete all table operations and release all locks before calling the EXPORT API. This can be done either by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK. A COMMIT is performed during the export process.
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.
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 db2uexpm.bnd module or any other shipped .bnd files are bound manually, the format option on the binder must not be used.
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.
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 MVS, SQL/DS, and 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.
Export 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.
See Also