tbImport

This chapter describes the tbImport utility and its usage.

Overview

tbImport is Tibero import utility that imports schema objects saved in external files to Tibero database. Like tbExport, it is useful for database backup and data transfer between heterogeneous databases.

When importing a schema object by using tbImport, all relevant schema objects are also imported automatically. If necessary, some of schema objects related to an imported object can be specified not to import.

tbImport has three modes: full database mode, user mode, and table mode. Only DBA can use these modes. If a user who wants to perform import has no DBA privilege, it is recommended to grant the SELECT ANY DICTIONARY privilege to the user. To set log that occurs during import, use the LOG parameter.

The following describes each tbImport result message.

Item	Description
Complete Message	Displayed when import successfully completed.
Alert Message	Displayed when import completed with one or more warnings.
Error Message	Displayed when import cannot continue due to an error.

---

Quick Start

tbImport is automatically installed and uninstalled along with Tibero. It is implemented in Java and can be run on any platform where Java Virtual Machine (JVM) is installed.

Preparations

To use tbImport, check the following requirements.

• JRE 1.4.2 or later must be installed.

• tbImport must be installed in a platform where Tibero database server is installed. Otherwise, it must be connected to the platform through a network.

• The following class libraries are required. (Default location: $TB_HOME/client/lib/jar)

  • tbImport class : expimp.jar

  • Common utility library : toolcom.jar

  • Common utility logger library : msllogger-14.jar

  • JDBC driver : internal-jdbc-14.jar

The class libraries are automatically installed along with Tibero. No separate tasks are required.

Import Modes

There are three import modes: full database mode, user mode, and table mode. Exported data can be imported depending on mode. Each mode can be set with a parameter.

The following shows the relationship between schema objects in each mode.

[Figure 3] Import Modes

[Figure 3] Import Modes

Full Database Mode

Imports entire Tibero database from exported files. Objects of all users except for the SYS user are imported.

To use this mode, set the FULL parameter to Y as follows:

FULL=Y

User Mode

Imports all schema objects of specified users except for the SYS user from exported files. DBA can specify multiple users.

To use this mode, set the USER parameter to the list of users as follows:

USER=SCOTT, USER1, …

Table Mode

Imports schema objects related to one or more specified tables from exported files.

To use this mode, set the TABLE parameter to the list of tables along with their owner as follows:

TABLE=SCOTT.EMP, USER1.TABLE1, …

From User To User Mode

Imports schema objects of a user specified in the FROMUSER parameter after changing the owner of the schema objects to a user specified in the TOUSER parameter. DBA can specify multiple users. This mode is a type of the user mode.

To use this mode, set the FROMUSER and TOUSER parameters as follows:

FROMUSER=SCOTT,USER1 TOUSER=USER2,USER3…

Executing tbImport

To execute tbImport, run the tbimport command in the $TB_HOME/client/bin directory.

The following executes tbImport in full database mode.

Executing tbImport

$ tbimport username=tibero password=tmax sid=tibero file=export.dat full=y
$ tbimport cfgfile=import.cfg

---

Usage

This section describes how to execute import for various cases.

Importing Tables with Constraints

If a table to import has constraints, its rows that violate the constraints are not imported.

If table constraints are saved and then table data is saved during import, rows that violate the constraints are not saved. For example, assume that two tables have a referential integrity constraint. If a child table data is tried to be saved before its parent table data is saved, no row of the child table is saved.

Therefore, to import tables with constraints, import all the table data and then set the table constraints.

Importing Compatible Tables

Before executing tbImport, a user can define a table with the same name of the table to import in a target database. The newly defined table can be defined differently from the table to import if the two tables are compatible.

To maintain compatibility between tables, note the following.

• Table columns A table in a target database must have all columns of the table to import.

• Data types Both tables have the same data types and default values for each column.

• When adding a new column Do not specify a primary key or NOT NULL constraint.

Importing Data to Existing Tables

Data can be imported to an existing table with the same name of the table to import. For this, both tables must be compatible.

If a user defines a table with the same name of the table to import in a target database before executing tbImport, or the target table already exists, some rows can be not imported due to preset constraints.

There are the following two methods to import data to existing tables.

• Not applying constraints Do not apply the constraints during import.

• Changing the processing order Change the import processing order. For example, if two tables have a referential integrity constraint, save the parent table and then its child table.

If the table size is huge, use the second method (changing the processing order) for better performance. The first method (not applying constraints) checks constraints on all table rows after applying constraints, which decreases performance.

---

tbImport Parameters

This section describes tbImport parameters that can be set at the command prompt.

Executing tbImport without any parameters displays the list and usage of the parameters as follows:

tbImport 7.0 101902 TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Usage: tbimport [options] [parameter=value parameter=value ...]

Options:
-h|--help            Display the more detailed information. 
-v|--version         Display the version information.
-p|--patch           Display the binary's patch information.

Parameters:
    BIND_BUF_SIZE      Specify the buffer size of DPL stream, default: 1M(1048576) 
    CFGFILE            Config file name
    COMPRESS           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
    ENCRYPTION_PASSWORD     Specifies a password for accessing encrypted data 
                            in the Dump File
    EXCLUDE_TABLE	    Exclude Imported Table, default: None 
    EXCLUDE_USER	    Exclude Imported User, default: None 
    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
    NOLOGGING	            Import Table's NOLOGGING attribute: Y/N, default: N 
    NO_PACK_DIR	            Import unpacked dump files from specified directory.
                        If this option is specified, FILE parameter will be ignored.
    PACK_TYPE	            Packing algorithm: TAR/ZIP, default: TAR 
    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
    STATISTICS	            Import Statistics: Y/N, default: N 
    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

The following describes each parameter.

Item	Description
BIND_BUF_SIZE	Size of the bind buffer used in a stream when importing data in DPL. (Default value: 1 MB (1048576))
CFGFILE	Configuration file name.
COMPRESS	Option to import files compressed during export. Y: Imports the files. N: Does not import the files. (Default value) If set to Y, a single thread is used.
COMMIT	Option to execute commit after an insert operation. (Default value: N) Units of an insert operation: Commits data imported in CPL when the data exceeds the bind insert buffer size (1 MB). If a LONG or LONG RAW column exists, the data is committed by row. Commits data imported in DPL when the data exceeds BIND_BUF_SIZE.
CONSTRAINT	Option to import constraints. Y: Imports constraints. (Default value) N: Does not import constraints.
DPL	Option to import data in DPL. Y: Uses DPL. N: Does not use DPL. (Default value)
DBLINK	Option to import DBLink. Y: Imports DBLink. (Default value) N: Does not import DBLink.
ENCRYPTION_PASSWORD	Password used during export. This is used to access encrypted data in a dump file.
EXCLUDE_TABLE	Option to exclude specific tables from import. Usage EXCLUDE_TABLE=tablelist Examples) Commas (,) can be used to exclude multiple tables. When using a comma, there must not be any spaces before or after the comma. EXCLUDE_TABLE=table1,table2 Tablename can be specified along with username. EXCLUDE_TABLE=user1.table1 If only tablename is specified without username, all tables with the same tablename will be imported regardless of users. Tablename and username.tablename can be specified together. EXCLUDE_TABLE=table1,user1.table2 Duplicate tablenames can be specified. All target tables are excluded. EXCLUDE_TABLE=user1.table1,user1.table1,table1
EXP_SERVER_VER	Exported server version. Export version constants are: 8: Tibero 6, Tibero 7 (Default value) 7: Tibero 5 SP1 6: Tibero 5
FILE	Name of a binary dump file created by OS and used during import. (Default value: default.dat)
FROMUSER	Original owners of objects used during export. This is used in From User To User mode. Usage Set in the following format. The number of mappings must be the same. FROMUSER=userlist Example) Multiple users can be set. FROMUSER=user1,user2 TOUSER=user3,user4 Objects of multiple users can be mapped to a single user. FROMUSER=user1,user2 TOUSER=user3,user3 Duplicate users cannot be set in FROMUSER. FROMUSER=user1,user1 TOUSER=user3,user4
FULL	Option to import data in full database mode. Y: Uses full database mode. N: Uses either user or table mode. Either one must be specified. (Default value)
GRANT	Option to import permissions. Y: Imports permissions. (Default value) N: Does not import permissions.
GEOM_ASBYTES	Option to import data to geometry columns as bytes. Y: Imports the data as bytes N: Imports the data as WKB. (Default value) This option is unnecessary in Tibero 6 and later because geometry column data is stored in WKB format. Use this option when importing data from Tibero 5 SP1 and earlier. When exporting data from Tibero 5 SP1 or earlier, if GEOM_ASBYTES is set to N and geometry column data is exported in WKB format, set this option to N. To import data in DPL, set the _DP_IMPORT_GEOM_FROM_OLD_FORMAT' iparam (default value: N) to Y.
IGNORE	Option to ignore an import error that is caused due to already existing schema objects. Y: Ignores the error. N: Does not ignore the error. (Default value)
INDEX	Option to import indexes. Y: Imports indexes. (Default value) N: Does not import indexes.
IO_BUF_SIZE	Size of a buffer used for file I/O during import. (Default value: 16 MB (16777216))
IP	IP address of the Tibero server to import. (Default value: localhost)
LOG	Name of the import log file.
LOGDIR	Directory that includes the import log file.
NATIONAL_CHARSET	Exported language set. (Default value: exported character set)
NOLOGGING	Option to add the NOLOGGING attribute for import tables. Y: Adds the NOLOGGING attribute for the tables. N: Does not add the NOLOGGING attribute for the tables. (Default value)
NO_PACK_DIR	Directory that includes a decompressed dump file used for import. If this option is specified, the value set in FILE is ignored.
PACK_TYPE	Option to select a packing algorithm for compressed file import. TAR: Imports compressed TAR files. (Default value) ZIP: Imports compressed ZIP files.
PASSWORD	Password for the user who performs import.
ROLE	Option to import roles. Y: Imports roles. (Default value) N: Does not import roles.
PORT	Port number of the Tibero server to import. (Default value: 8629)
PSM	Option to import PSM objects. Y: Imports PSM objects. (Default value) N: Does not import PSM objects.
P_DPL	Option to use the parallel DPL method for import. Y: Uses the method. N: Does not use the method. (Default value)
ROWS	Option to import table data. Y: Imports table data. (Default value) N: Does not import table data.
SAVE_CREDENTIAL	Specified when using encrypted username and password. Use this item to create EXPIMP_WALLET encryption file. Usage SAVE_CREDENTIAL=[EXPIMP_WALLET_FILE_NAME] Example) SAVE_CREDENTIAL=/tmp/.expimp USERNAME=username PASSWORD=password The following sets environment variables of the EXPIMP_WALLET file. export EXPIMP_WALLET=/tmp/.expimp The following are priorities for the previous setting. If all of them are not specified, an error occurs. 1. Username and password parameters entered at the command line. 2. USERNAME and PASSWORD files in cfgfile. 3. USERNAME and PASSWORD in the EXPIMP_WALLET file.
SCRIPT	Option to show the DDL script that creates schema objects during import. Y: Shows the DDL script. N: Does not show the DDL script. (Default value)
SEQUENCE	Option to import sequences. Y: Imports sequences. (Default value) N: Does not import sequences.
SID	SID of the Tibero server to import.
STATISTICS	Option to import statistics about the file to import. Recalculating the statistics such as SAFE and RECALCULATE is not supported. Y: Imports the statistics about the target file. N: Does not import the statistics about the target file. (Default value)
SYNONYM	Option to import synonyms. Y: Imports synonyms. (Default value) N: Does not import synonyms.
TABLE	Table names to import in table mode. Usage TABLE=tablelist
TEMP_DIR	Directory that includes temporary dump files generated during import.
THREAD_CNT	Number of threads used to import table data. (Default value: 4)
TOUSER	New owners of objects to import in From User To User mode. Usage TOUSER=userlist
TRIGGER	Option to import triggers. Y: Imports triggers. (Default value) N: Does not import triggers.
USER	Users of objects to import in user mode. Usage USER=userlist
USERNAME	User account that performs import.

The order of entered parameters does not matter. CFGFILE can be set only at the command prompt, but the other parameters can be set in a configuration file as well as at the command prompt. Note that parameter names set in the file must be in uppercase.

The following shows how to set parameters in a configuration file. Some parameters can have multiple values as shown in the second line.

PARAMETER=value 
PARAMETER=value1, ...

The following is an example of setting parameters.

FULL=Y
FILE=TBEXPORT.DAT 
GRANT=Y
INDEX=Y 
CONSTRAINT=Y

---

Usage Example

The following are the steps for importing data by using tbImport

1. Table definitions

2. Table data

3. Table indexes

4. Table constraints, views, procedures, etc.

The following is an example of importing data by using tbImport.

tbImport Usage Example

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