The import functions use the files generated by export as input in order to re-create the exported tables in a new environment.
The import operation is performed in two stages, object creation and data loading. These are represented by the options, "Import - object creation" and "Import - data load" in the Export/Import main menu, and must be performed separately even if the table definitions and data were exported together in the same file.
Import - Object Creation
This phase creates the objects being imported. The newly created objects are owned by the ident performing the import operation. The "Enter program ident" option in the main menu allows imported files to be processed by a program ident.
The user is prompted for the name of the export file to be used, and also for the name of a log file which will be used if any table names are altered from those given in the export file (see below).
Before the imported tables are created, the table-databank couplings in the export file are displayed, and the user is given an opportunity to redirect the tables to new target databanks. If a target databank is specified which does not exist in the import environment, the user is prompted for the filename, size, and options and a new databank is created. If the target databank name is left blank, Mimer SQL will place the table in the databank which is judged best for the purpose (equivalent to using the SQL statement CREATE TABLE with no IN clause).
Once the table-databank couplings have been accepted, the tables are created from the CREATE TABLE statements in the export file.
The following example shows the creation of the import objects:-- Export / Import utility -- 1. Export - definitions and data 2. Export - definitions only 3. Import - object creation 4. Import - data load 5. Load / Unload table 6. SYSxxGEN 7. Enter program ident 8. Leave program ident 0. Exit Select: 3 -- Import - object creation -- File generated by Export/Convout : BOOKEXP Import log file : BOOKIMP Listing of couplings between tables and target databanks TABLE NAME DATABANK ------------------------------------ HOTELADM.BILL <---> HOTELDB HOTELADM.BOOKFORM <---> HOTELDB HOTELADM.BOOK_GUEST <---> HOTELDB HOTELADM.EXCHANGE_RATE <---> HOTELDB HOTELADM.FREEROOMS <---> HOTELDB HOTELADM.ROOMSTATUS <---> HOTELDB Are the couplings okay <Y>? : Y Making definitions Operation completed
Naming conflicts will arise during import operations if the names of imported tables or domains already exist in the import environment. If this occurs, the user may choose to rename the object in question, skip creation of the particular object, or quit the object creation operation.
If any tables are renamed during the object creation, the altered names are stored in the import log file (from which they are read by the data load operation in the next phase of import).
Domain conflicts need only be resolved once for any import operation. All usage of the domain in the imported table definitions are corrected according to the response to the first reported conflict.
If the user chooses to quit the object creation operation at a naming conflict, any objects created prior to the naming conflict remain in the import environment.
The following example shows how to rename an object (user input is shown in bold).
Note: The duplicate table name is shown with the name of the schema to which it belongs for clarity, but that the new name may not be qualified by a schema name, the tables will be created in a schema with the same name as the ident performing the import operation, in accordance with the default CREATE TABLE behavior.
-- Import - object creation -- File generated by Export/Convout : BOOKEXP Import log file : BOOKIMP Listing of couplings between tables and target databanks TABLE NAME DATABANK ------------------------------------ HOTELADM.BOOK_GUEST <---> HOTELDB Are the couplings okay <Y>? : Y Making definitions Duplicate table name : HOTELADM.BOOK_GUEST Skip/Quit/Rename <S/Q/R>? R Give new name : BOOKGUEST2 Operation completed
Referential conflicts can arise in several ways during an import-object creation:
- depending on the structure of the exported database and the way tables are exported, an attempt may be made during import to create a foreign key for which the reference table does not exist in the import environment
- the order in which the tables were exported may result in an attempt during import to create a foreign key before the reference table is created
- an imported table may have a foreign key which refers to a table which exists in the import environment but which is the "wrong" table (i.e. the primary key of the reference table is not consistent with the foreign key).
It is the responsibility of the user performing the import to avoid referential conflicts in the imported tables, either by importing tables in the correct order or by using the ALTER TABLE statement to establish the referential constraints after the tables have been imported.
The import functionality does not provide any interactive facilities for correcting conflicts of this type.
If referential conflicts do arise, the table(s) affected will be created without the constraint(s) with a message informing the user of the constraint conflict.
Caution: In particularly unfortunate circumstances, a table may be imported with a foreign key clause which happens to coincide with an existing table in terms of the reference table name and column definitions in the import environment, but which the user does not actually want to use as a reference table.
Import - Data Load
This phase of an import operation loads the imported tables with data from the export file.
The operation must be performed separately from the object creation phase.
The user is prompted for the name of the export file and for the name of the log file specified during the object creation phase (see above).
It is important that data load uses the same log file that was written when the objects were created.
The user is also asked if duplicates should be logged. If a duplicate is encountered, a report is written to the session log and a summary appears at the terminal.
Note: Data loaded into tables by the import functionality must have been created by the export utility. The import functionality cannot use data files created by the UNLOAD function (see below).
Tables are loaded in transactions of at most 100 rows each. The progress of the loading is reported by a message every 100th row for the first 10 000 rows and thereafter every 1 000th row.
If an error occurs during the data load operation, a message appears on the terminal and the record is logged. If 100 errors occur the import operation is aborted.
If the import operation is aborted because of errors, the rows which have been reported as loaded will remain in the table.
Tip: If there is a large amount of data to be imported it can be performed faster by setting the databank option to NULL before the load operation takes place. This is because transaction handling affects the performance of the load operation.
The following example shows data being loaded from the export file BOOKEXP:
-- Import - data load -- File generated by Export/Convout : BOOKEXP Log file from 'object creation' : BOOKIMP Log duplicates <Y>? N Loading table : HOTELADM.FREEROOMS 100 rows loaded 195 rows loaded Loading table : HOTELADM.ROOMSTATUS 20 rows loaded Operation completed
The ident performing the object creation phase of the import operation must have TABLE privilege on any databank in which a table is to be created, and also DATABANK privilege if new databanks are to be created. REFERENCES privilege on the appropriate table(s) is required if any foreign keys are to be created.
The ident performing the data load phase must have INSERT privilege on tables being loaded. Normally, the data load phase is performed by the same ident as the object creation phase, in which case INSERT privilege is automatically granted (since the ident owns the schema in which the tables are created).
Enter Program Ident
The "Enter program ident" option in the main menu allows a user to act in the capacity of a program ident. The user running the Export/Import functionality must have EXECUTE privilege on the program ident in order to enter it.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40