Chapter 4. tbImport

4.1. Overview

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. They can only be used by a DBA. If DBA privilege cannot be granted, it is recommended to grant the SELECT ANY DICTIONARY privilege.

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	Displayed when the import has been successfully processed.
Alert Message	Displayed when the import has been finished, but something is wrong.
Error Message	Displayed when an error occurs during import and the import cannot continue.

4.2. Quick Start

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.

4.2.1. Preparation before Execution

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/client/lib/jar directory) for execution.
- tbImport 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.

4.2.2. Import Mode

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.

[Figure 4.1] Import Mode

Full Database Mode

Full database mode imports the entire Tibero database from the exported files. 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

User mode imports all schema objects of the specified users, except the SYS user, 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

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

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…

4.2.3. Executing tbImport

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 sid=tibero file=export.dat full=y
$ tbimport cfgfile=import.cfg

4.3. Operation Method

4.3.1. Importing the Table that Has Constraints

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.

4.3.2. Importing the Compatible Table

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.

4.3.3. Importing Data into a Table that Already Exists

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.

4.4. Specifying Parameters at the Command Prompt

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 entered at the command prompt, as shown below.

tbImport 6.0 101902 TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Usage: tbimport [PARAMETER1=VALUE] [PARAMETER2=VALUE] ... 
Parameters:    
    BIND_BUF_SIZE     Specify the buffer size of DPL stream, default: 1M(1048576)
    CFGFILE           Config file name
    COMMIT            Commit after the insertion, default: N
    CONSTRAINT        Import Constraint: Y/N, default: Y
    DBLINK            Import DB Link: Y/N, default: Y
    DPL               Use Direct Path Load: Y/N, default: N
    EXP_SERVER_VER    Specify the exported server version, default: 8
    FILE              Import dump file name, default: default.dat
    FROMUSER          FromUser toUser Mode: user name list
                      (must be used with TOUSER parameter) 
    FULL              Full Mode: Y/N, default: N
    GRANT             Import Grant: Y/N, default: Y
    GEOM_ASBYTES      Import the data to the geometry columns as bytes, default: N
    IGNORE            Ignore create error due to object existence: Y/N, default: N
    INDEX             Import Index: Y/N, default: Y
    IO_BUF_SIZE       Specify the buffer size of file I/O, default: 16M(16777216)
    IP                IP address, default: localhost
    LOG               Import script log file name
    LOGDIR            Import log directory
    NATIONAL_CHARSET  Specify the exported national character set, 
                      default is the exported character set 
    NO_PACK_DIR       Import unpacked dump files from specified directory.
                      If this option is specified, FILE parameter will be ignored.
    PASSWORD          User password
    ROLE              Import Role: Y/N, default: Y
    PORT              PORT number, default: 8629
    PSM               Import PSM: Y/N, default: Y
    P_DPL             Use Parallel DPL: Y/N, default: N
    ROWS              Import Table Rows: Y/N, default: Y
    SAVE_CREDENTIAL   Save your username and password to specified file
    SCRIPT            LOG THE DDL SCRIPT: Y/N, default: N
    SEQUENCE          Import Sequence: Y/N, default: Y
    SID               Database name
    SYNONYM           Import Synonym: Y/N, default: Y
    TABLE             Table Mode: table name list
    TEMP_DIR          Directory for the temporary raw dump files.
    THREAD_CNT        Thread Count, default: 4
    TOUSER            FromUser toUser Mode: user name list
                      (must be used with FROMUSER parameter) 
    TRIGGER           Import Trigger: Y/N, default: Y
    USER              User Mode: user name list 
    USERNAME          Database user name

Parameter values do not have to be entered in order. Among the parameter values, CFGFILE can only be entered at the command prompt. Other parameter values can be specified in the configuration file. Parameter names specified in the configuration file must be in uppercase.

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

4.4.1. List of Parameters

The following table describes the tbImport utility parameters, that can be entered at the command prompt.

Item	Description
BIND_BUF_SIZE	Size of the bind buffer that is used in stream when importing data in the DPL mode. (Default value: 1MB (1048576))
CFGFILE	Configuration file name.
COMMIT	Commits after an insert operation. (Default value: N) Units of an insert operation: Commits the data to be imported in CPL when the data exceeds the bind insert buffer size (1MB). If a LONG or LONG RAW column exists, the data in committed in row units. Commits the data to be imported in DPL if the data exceeds BIND_BUF_SIZE.
CONSTRAINT	Option to import the constraint. Y: enable. (Default value) N: disable.
DPL	Option to import in the DPL method. Y: enable. N: disable. (Default value)
DBLINK	Option to import the DBLink. Y: enable. (The default value) N: disable.
EXP_SERVER_VER	Server version that data is exported to. Tibero 6: 8 (Default value) Tibero 5 SP1: 7 Tibero 5: 6
FILE	Name of the dump file that is created while importing. (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.
FROMUSER	Used in the From to User mode. Specifies the original owner of the objects used during export. Usage (number of mapping items must equal those in the TOUSER parameter): FROMUSER=userlist Detailed rules for use: Can set multiple users. FROMUSER=user1,user2 TOUSER=user3,user4 multiple users can be mapped to a single user. FROMUSER=user1,user2 TOUSER=user3,user3 Duplicate users cannot be set to FROMUSER. FROMUSER=user1,user1 TOUSER=user3,user4
FULL	Option to import in full database mode. Y: Imports in full database mode. N: Imports in user or table mode. (Either one of the two modes must be specified). (The default value)
GRANT	Option to import permissions. Y: enable.(Default value) N: disable.
GEOM_ASBYTES	Option to input a geometry column as WKB or bytes. (Default value: Y) This option is unnecessary in Tibero 6 and later versions because geometry columns are stored in the WKB format. Use this option when importing data from Tibero 5 SP1 and earlier. GEOM_ASBYTES must be set to N if the geometry columns were exported in WKB format with geom_asbytes set to 'N' in Tibero 5SP1 and earlier. To import data in DPL, '_DP_IMPORT_GEOM_FROM_OLD_FORMAT' (Default value: N) iparam must be set to 'Y'.
IGNORE	Option to ignore an error, caused by an existing schema object, when processing the import. Y: Ignores a creation error that is caused by a schema object that already exists. N: Does not ignore a creation error that is caused by a schema object that already exists. (Default value)
INDEX	Option to import the index information. Y: enable. (Default value) N: disable.
IO_BUF_SIZE	Controls the size of the buffer that is used to input/output files when processing the import. (Default value: 16MB (16777216))
IP	IP address of theTibero server, that is to be imported. (Default value: localhost)
LOG	Name of the file to record object scripts.
LOGDIR	Name of the directory that contains the file that records import execution logs.
NATIONAL_CHARSET	Character set that was exported. (Default value: Exported character set)
NO_PACK_DIR	Directory which stores the unpacked dump file to run the import. If this option is specified, the value set in FILE parameter is ignored.
PASSWORD	Password of the user, who performs the import.
ROLE	Option to import the ROLE. Y: enable. (The default value) N: disable.
PORT	Port number of Tibero that is to be imported. (The default value: 8629)
PSM	Option to import the PSM object. Y: enable. (Default value) N: disable.
P_DPL	Option to use the parallel DPL method to import. Y: enable. N: disable. (Default value)
ROWS	Option to import the table data. Y: enable. (Default value) N: disable.
SAVE_CREDENTIAL	Option to save encrypted username and password. Usage Creates an encryption file using the SAVE_CREDENTIAL option. SAVE_CREDENTIAL=[EXPIMP_WALLET_FILE_NAME] E.g.) SAVE_CREDENTIAL=/tmp/.expimp USERNAME=username PASSWORD=password Sets the environment variables of the EXPIMP_WALLET file. export SAVE_CREDENTIAL=/tmp/.expimp Parameter Priority The username and password parameters that are entered into the command line. The USERNAME and PASSWORD files in cfgfile. The USERNAME and PASSWORD of the EXPIMP_WALLET file. An error occurs if the above settings are not specified.
SCRIPT	Option to show the DDL script that creates a schema object during import. Y: enable. N: disable. (Default value)
SEQUENCE	Option to import the sequence. Y: enable. (Default value) N: disable.
SID	SID of the Tibero server to be imported.
SYNONYM	Option to import the synonym. Y: enable. (Default value) N: disable.
TABLE	Name of the table to import in table mode. Usage: TABLE=tablelist
TEMP_DIR	Directory that stores temporary dump files when importing data.
THREAD_CNT	Number of threads, which are used to import the table data. (The default value: 4)
TOUSER	List of users (owners) whose objects will be imported. Used in the From to User mode. Usage: TOUSER=userlist
TRIGGER	Option to import the trigger. Y: Imports the trigger. (Default value) N: Does not import the trigger.
USER	Owner of the object to be imported in the user mode. Usage: USER=userlist
USERNAME	User account to perform the import.

4.5. Usage Example

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 6.0 97819 TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Unpacking the file...
the entire database: Mon Jul 14 01:07:43 KST 2014
Import character set: MS949
The version of this tbExport dump file is 5.0.
  importing schema: "TIBERO"
    importing tables
      [M] importing table BONUS     no rows imported.
      [0] importing table DEPT      4 rows imported.
      [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:07:55 KST 2014

Prev		Next
Chapter 3. tbExport	Home	Chapter 5. tbLoader