EXPORT TABLE exports a base table from a SESAM/SQL database to a BS2000 file. This BS2000 file is known as an export file. The export file is created with a coded character set name (CCSN) in accordance with the database’s character set. When it is imported the export file’s CCSN is checked against the database’s coded character set.
The table's metadata is stored in the export file. User data can also be exported. IMPORT TABLE can then be used to create a new base table from this export file. The new base table can also be a partitioned table.
You can use the EXPORT/IMPORT TABLE statements to perform the following tasks:
To copy a base table within a database or from one database to another:
Using an export file is an easy way to copy a base table since the structure of the target table is defined via the metadata in the export file. Significantly fewer operating steps are required compared to the use of the pair of statements LOAD/UNLOAD because, when LOAD is used, it is necessary to reconstruct the table structure. You can specify a search constraint in the EXPORT TABLE statement to define which user data is to be transferred to the new table.
However, unlike the files used with LOAD/UNLOAD, the export file cannot be processed manually.
The export file is considerably smaller than a file generated using UNLOAD (see section “Unloading user data with UNLOAD”).To move a base table from one space to another:
To do this, you first export the base table and then delete it with DROP TABLE. You can then import into a space of your choice using IMPORT TABLE. Views, reference constraints and access rights for this table may have to be adapted as appropriate.
- To convert a base table to a partitioned table or to modify the number and boundaries of partitions of a partitioned table:
To do this, you first export the base table and then delete it if required with DROP TABLE. It can be imported as a new, partitioned table with the same or a different name, specifying the (modified) partition structure with IMPORT TABLE.
The user-friendly utility statement ALTER PARTITIONING FOR TABLE is also provided for this purpose, see section “Changing the partitioning of a base table”.
- To restructure a base table:
A base table can be split into a number of different new tables.
To do this, you must export it and then import it as often as required under different names. These new tables are initially identical but can be edited using ALTER TABLE in order to achieve the required subdivision.
- To re-assign the row numbers in a base table:
You can re-assign the row numbers of base tables with primary keys.
To do this, you must export the table, delete it and then re-import it into the same space while specifying NEW ROW_IDS. - To reorganize a table:
You can use the IMPORT TABLE statement to reorganize a table since IMPORT TABLE takes account of the space's block utilization. To do this, you must export the table, delete it and then re-import it into the same space.
A table can also be reorganized with REORG ONLINE TABLE. To archive a single base table:
Export files created with EXPORT TABLE can be used as backup copies of individual tables. They can also be saved to magnetic tape cartridge. Unlike backups created using the COPY statement, it is not the granular space but the higher-resolution granularity table that is used. A compact backup is created because less administrative information has to be saved. It is, however, not possible to run a RECOVER on this type of backup. EXPORT files can only be processed by IMPORT TABLE.