Chapter 4. tbImport

Table of Contents

4.1. Overview
4.2. Quick Start
4.2.1. Preparation before Execution
4.2.2. Import Mode
4.2.3. Executing tbImport
4.3. Operation Method
4.3.1. Importing the Table that Has Constraints
4.3.2. Importing the Compatible Table
4.3.3. Importing Data into a Table that Already Exists
4.4. Specifying Parameters in the Command Prompt
4.4.1. List of Parameters
4.5. Operation Example

This chapter describes the tbImport utility and its usage.

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. 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 .

ItemDescription
Complete MessageThis message is output when the import has been successfully processed.
Alert MessageThis message is output when the import has been finished, but something is wrong.
Error MessageThis message is output 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/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.

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

Import Mode

Full Database 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

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

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

4.3. Operation Method

The following explains how to import tables in various cases.

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 in 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 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

4.4.1. List of Parameters

The following table describes the tbImport utility parameters, that can be specified in the command prompt.

ItemDescription
CFGFILEName of the configuration file.
USERNAMEThe account of the user, who performs the import.
PASSWORDThe password of the user, who performs the import.
IPThe IP address of theTibero server, that is to be imported. (The default value: localhost)
PORTThe port number of Tibero that is to be imported. (The default value: 8629)
SIDThe 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_DIRThe directory which stores the unpacked dump file to run the import. If this option is specified, the value set in FILE parameter is ignored.
LOGEnters the name of a file, which will contain the import log.
FULL

Specifies whether or not 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)

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.

  • Y: Imports the index. (The default value)

  • N: Does not import the index.

GRANT

Decides whether or not to import the authority.

  • Y: Imports the authorities. (The default value)

  • N: Does not import the authorities.

CONSTRAINT

Decides whether or not to import the constraint.

  • Y: Imports the constraint. (The default value)

  • N: Does not import the constraint.

ROLE

Decides whether or not to import the ROLE.

  • Y: Imports the ROLE. (The default value)

  • N: Does not import the ROLE.

DBLINK

Decides whether or not to import the DBLink.

  • Y: Imports the DBLink. (The default value)

  • N: Does not import the DBLink.

PSM

Decides whether or not to import the PSM object.

  • Y: Imports the PSM object. (The default value)

  • N: Does not import the PSM object.

SEQUENCE

Decides whether or not to import the sequence.

  • Y: Imports the sequence. (The default value)

  • N: Does not import the sequence.

SYNONYM

Decides whether or not to import the synonym.

  • Y: Imports the synonym. (The default value)

  • N: Does not import the synonym.

TRIGGER

Decides whether or not to import the trigger.

  • Y: Imports the trigger. (The default value)

  • N: Does not import the trigger.

ROWS

Decides whether or not to import the table data.

  • Y: Imports the table data. (The default value)

  • N: Does not import the table data.

DPL

Decides whether or not to import with the DPL method.

  • Y: Uses the DPL method.

  • N: Does not use the DPL method. (The default value)

P_DPL

Decides whether or not to use the parallel DPL method to import.

  • Y: Uses the parallel DPL method.

  • N: Does not use the parallel DPL method. (The default value)

SCRIPT

Decides whether or not to show the DDL script that creates a schema object during import.

  • Y: Shows the DDL script that creates a schema object.

  • N: Does not show the DDL script that creates a schema object. (The default value)

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.

  • 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.

    (The default value)

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_SIZEControls 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_VERSets the version of the server that was exported. (Default value: 4)
NATIONAL_CHARSETSets the character set that was exported. (Default value: Exported character set)

4.5. Operation Example

The following is the process order for importing using tbImport utility.

  1. Table definition

  2. Table data

  3. Table index

  4. 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