The EXPORT utility exports data from a database into an operating system file. The output file has the format specified by the data format parameter.
The following is an example of the command line processor syntax for the EXPORT command:
db2 export to staff.ixf of ixf select * from userid.staff
The following information is required when exporting data:
When working with typed tables, you cannot provide a SELECT statement directly. Instead, you must specify the target subtable name, and optionally a WHERE clause. The EXPORT command uses this information along with the traverse order to generate and execute the needed SELECT statement.
When exporting from LOB columns, the default action is to select the first 32K bytes of data. The data is placed either in the same file as the rest of the column data, or into separate files. In the latter case, each LOB value is placed in separate files by using the FILETMOD option LOBSINFILE and the LOBPATHS/LOBFILE parameters. For more information, see the Command Reference.
| Note: | Extensions from 000 to 999 are automatically added to the base name given in the LOBFILE parameter -- one for each LOB file. |
You may also provide the following information:
You must have SYSADM authority, DBADM authority, CONTROL privilege, or SELECT privilege for each table participating in the export.
A table may be saved by using the EXPORT utility and specifying the IXF file format. The saved table may be re-created using the IMPORT utility. The EXPORT utility will fail if the data you want to export exceeds the space available on the file system on which the exported file will be created. In this case, you should limit the amount of data selected by specifying conditions on the WHERE clause so that the export file will fit on the target file system. You will have to run the EXPORT utility multiple times to export all the data you desire.
| Note: | Before running the export utility, you must be connected or connected implicitly to the database from which the data will be exported. Also, the utility will issue a COMMIT statement; therefore, you should complete all transactions and release all locks by performing either a COMMIT or ROLLBACK before calling it. |
When you want to use the EXPORT utility in a multiple database partition environment, you can use db2_all to have the utility carry out the task at each database partition. The SELECT statement used with EXPORT must be able to only get the data found locally. The selection condition appears as follows:
SELECT * FROM tablename WHERE NODENUMBER(column-name) = CURRENT NODE
Only the rows from tablename found on the local database partition are exported to the filename (like staff.ixf in the previous example) where there is a file with this name at every database partition. The contents of these files are overwritten by the output from the EXPORT command.