Table of Contents
This chapter describes the tbImport utility and its usage.
tbImport is the Import utility provided by Tibero. It can import the schema object, which is saved in an external file, into the Tibero database. Along with tbExport utility, this utility is useful when processing a database backup and transmitting a database between different machines. The tbImportutility has functions similar or symmetrical to the tbExport utility.
When you import one schema object using the tbImport utility, related schema objects will also be imported automatically. If necessary, it is possible to block some of the related schema objects from being imported.
Just like the tbExport utility, tbImport has three modes: full database mode, user mode, and table mode. Only a DBA is allowed to use the full database mode.
Specify the log, which is created while importing, by using the LOG parameter.
The following table describes the Complete, Alert, and Error messages created as a result of executing the tbImportutility .
Item | Description |
---|---|
Complete Message | This message is output when the import has been successfully processed. |
Alert Message | This message is output when the import has been finished, but something is wrong. |
Error Message | This message is output when an error occurs during import and the import cannot continue. |
The tbImport utility will be installed or removed when Tibero is installed or removed. Also, the tbImport utility is implemented in the Java language and it can be run on any platform in which JVM (Java Virtual Machine) is installed.
Check the following requirements are met before executing the utility.
If JRE 1.4.2 or higher is installed.
Should be installed on the same platform as Tibero database server or be connected through a network.
Necessary class libraries (the default location: $TB_HOME/lib/jar directory) for execution.
tbExport class: expimp.jar
Common library of the utilities: toolcom.jar
Common Logger library of the utilities: msllogger-14.jar
JDBC driver: internal-jdbc-14.jar
The Class libraries required for execution will be installed automatically when installing Tibero. Therefore, there are no additional tasks required for this.
Import mode has three modes: full database mode, user mode, and table mode. Exported files are the source for the import. Import the data according to the features of each mode. Each mode can be specified by using a parameter.
The following figure shows the relationship of schema objects, which are to be imported, in each mode.
Full database mode imports the entire Tibero database from the exported files. Only a DBA is allowed to use this mode. It imports objects of all users except the SYS user.
To use the full database mode, specify Y for the FULL parameter.
FULL=Y
User mode specifies the target users and imports all their schema objects from the exported files. A DBA can allow more than one users to use this mode.
To use the user mode, specify the userlist for the USER parameter. (USER=userlist format).
USER=SCOTT, USER1, …
Table mode specifies one or more tables and imports all of their schema objects, such as index, from the exported files.
To use table mode, specify the tablelist for the TABLE parameter. (TABLE=tablelist format). Make sure to enter the user who owns the table like SCOTT.EMP as shown below.
TABLE=SCOTT.EMP, USER1.TABLE1, …
From User To User mode imports the schema objects from the exported files after changing their owner from FROMUSER parameter to TOUSER parameter. A DBA can allow more than one user to use this mode. This mode is part of the user mode.
To use the From User To User mode, specify the parameter as in the format below.
FROMUSER=SCOTT,USER1 TOUSER=USER2,USER3…
To execute the tbImport utility, enter the tbimport command in the $TB_HOME/client/bin directory.
The following is an example of how to execute tbImport in full database mode.
[Example 4.1] Executing the tbImport Utility
$ tbimport username=tibero password=tmax file=export.dat full=y $ tbimport cfgfile=import.cfg
The following explains how to import tables in various cases.
If there are constraints specified to the table, the rows that violate these constraints will not be saved.
The proper process order is to save the table data first and then save table constraints. However, if you save the table constraints before saving table data, certain rows that violate the saved constraints will not be saved. For example, if you save the child table data before saving the parent table data in two tables that have referential integrity constraint specified, not even the first row will be saved in the child table.
Therefore, when importing a table that has constraints, import all the table data first, and then specify the table constraints.
Before executing the tbImport utility, a user can define a table in the target database, in advance. This newly defined table does not have to be exactly the same as the table that is to be imported, but has to be compatible.
To maintain compatibility between tables, pay extra attention to the following conditions.
Table to be imported
Should include all the columns the table contains.
Data type
Should have mutual compatibility. Do not change the default values.
When adding a new column
Do not specify a primary key constraint or NOT NULL.
It is possible to import data into a table that already exists in a database. The two tables should be compatible, as mentioned in the previous section.
If a user defines the same table name or a table that already exists in the database, before processing the tbImport utility, there may be some rows that cannot be saved in the tbImport utility due to the constraints specified in the table.
Use the following two methods when importing data into a table that already exists.
Holding the constraints
Hold the constraints while processing the tbImport utility.
Controlling the execution order
Control the execution order while processing the tbImport utility.
For example, if the two tables have referential integrity constraint specified, save the parent tables first and then save the child tables.
If the table size is large, using the controlling execution order method is better in terms of performance. The method of holding constraints is not good for performance. When you hold and then allow constraints again, all rows in the table will be checked to see whether they match the constraints.
If a user executes the tbImport without specifying any values for the parameters, the user will see a list of parameters and their usage, which can be specified in the command prompt, as shown below.
tbImport 5.1 87326 Copyright(c) 2014 TmaxData Co., Ltd. All rights reserved Patch files (none) Usage: tbimport [PARAMETER1=VALUE] [PARAMETER2=VALUE] ... Parameters: CFGFILE Config file name USERNAME Database user name PASSWORD User password IP IP address, default: localhost PORT PORT number, default: 8629 SID Database name FILE Import dump file name, default: default.dat NO_PACK_DIR Import unpacked dump files from specified directory. If this option is specified, FILE parameter will be ignored. LOG Import log file name FULL Full Mode: Y/N, default: N USER User Mode: user name list FROMUSER FromUser toUser Mode: user name list (must be used with TOUSER parameter) TOUSER FromUser toUser Mode: user name list (must be used with FROMUSER parameter) TABLE Table Mode: table name list INDEX Import Index: Y/N, default: Y GRANT Import Grant: Y/N, default: Y CONSTRAINT Import Constraint: Y/N, default: Y ROLE Import Role: Y/N, default: Y DBLINK Import DB Link: Y/N, default: Y PSM Import PSM: Y/N, default: Y SEQUENCE Import Sequence: Y/N, default: Y SYNONYM Import Synonym: Y/N, default: Y TRIGGER Import Trigger: Y/N, default: Y ROWS Import Table Rows: Y/N, default: Y DPL Use Direct Path Load: Y/N, default: N!ImportMain.parameter.P_DPL.usag! SCRIPT LOG THE DDL SCRIPT: Y/N, default: N THREAD_CNT Thread Count, default: 4 IGNORE Ignore create error due to object existence: Y/N, default: N IO_BUF_SIZE Specify the buffer size of file I/O, default: 16M(16777216) BIND_BUF_SIZE Specify the buffer size of DPL stream, default: 1M(1048576) EXP_SERVER_VER Specify the exported server version, default: 6 NATIONAL_CHARSET Specify the exported national character set, default is the exported character set
Parameter values do not have to be entered in order. Among the parameter values, CFGFILE can only be specified in the command prompt. Other parameter values can be specified in the configuration file.
The parameters used in command prompt can be saved in the configuration file in the following two ways. The second type is used when specifying two or more parameter values.
PARAMETER=value PARAMETER=value1, ...
The following is an example of how to specify a number of parameters.
FULL=Y
FILE=TBEXPORT.DAT
GRANT=Y
INDEX=Y
CONSTRAINT=Y
The following table describes the tbImport utility parameters, that can be specified in the command prompt.
Item | Description |
---|---|
CFGFILE | Name of the configuration file. |
USERNAME | The account of the user, who performs the import. |
PASSWORD | The password of the user, who performs the import. |
IP | The IP address of theTibero server, that is to be imported. (The default value: localhost) |
PORT | The port number of Tibero that is to be imported. (The default value: 8629) |
SID | The SID of the Tibero server to be imported. |
FILE | The name of the file that is created while importing. (The default value: default.dat) It is created in the operating system in the type of binary file. If the name is not given, default value will be used. |
NO_PACK_DIR | The directory which stores the unpacked dump file to run the import. If this option is specified, the value set in FILE parameter is ignored. |
LOG | Enters the name of a file, which will contain the import log. |
FULL | Specifies whether or not to import in full database mode.
|
USER | Specifies the owner of the object to be imported in user mode. The format is USER=userlist. |
FROMUSER | This parameter is used in From to User mode. It specifies the original owner of object that is used while exporting. The format is FROMUSER=userlist. |
TOUSER | This parameter is used in the From to User mode. It specifies the owner to be imported. The format is TOUSER=userlist. |
TABLE | Specifies the name of the table to be imported in table mode. The format is TABLE=tablelist. |
INDEX | Decides whether or not to import the index information.
|
GRANT | Decides whether or not to import the authority.
|
CONSTRAINT | Decides whether or not to import the constraint.
|
ROLE | Decides whether or not to import the ROLE.
|
DBLINK | Decides whether or not to import the DBLink.
|
PSM | Decides whether or not to import the PSM object.
|
SEQUENCE | Decides whether or not to import the sequence.
|
SYNONYM | Decides whether or not to import the synonym.
|
TRIGGER | Decides whether or not to import the trigger.
|
ROWS | Decides whether or not to import the table data.
|
DPL | Decides whether or not to import with the DPL method.
|
P_DPL | Decides whether or not to use the parallel DPL method to import.
|
SCRIPT | Decides whether or not to show the DDL script that creates a schema object during import.
|
THREAD_CNT | Enters the number of threads, which are used to import the table data. (The default value: 4) |
IGNORE | Decides whether or not to ignore an error, caused by an existing schema object, when processing the import.
|
IO_BUF_SIZE | Controls the size of the buffer that is used to input/output files when processing the import. (The default value: 16MB(16777216)) |
BIND_BUF_SIZE | Controls the size of the bind buffer, that is used in stream, when processing the import with DPL mode.(The default value: 1MB(1048576)) |
EXP_SERVER_VER | Sets the version of the server that was exported. (Default value: 4) |
NATIONAL_CHARSET | Sets the character set that was exported. (Default value: Exported character set) |
The following is the process order for importing using tbImport utility.
Table definition
Table data
Table index
Table constraint, view, procedure, etc.
The following is an example of importing using the tbImport utility.
[Example 4.2] Executing Import using the tbImport utility
tbImport 5.1 87326 Copyright(c) 2014 TmaxData Co., Ltd. All rights reserved Patch files (none) Import character set: MSWIN949 the entire database: Mon Jul 14 01:04:46 KST 2014 Import national character set: UTF-16 importing schema: "TIBERO" importing tables importing table "BONUS" [M] importing table BONUS no rows imported. importing table "DEPT" [M] importing table DEPT 4 rows imported. importing table "BONUS" importing table "EMP" [0] importing table SALGRADE 5 rows imported. [1] importing table EMP 10 rows imported. importing index importing sequences importing views importing synonyms Import completed successfully: Mon Jul 14 01:04:46 KST 2014