Skip to main content

Search

Welcome to Tibero GTS!

How to Migrate Tibero Virtual Column

Document Type | Technical Information

Category | Migration

Applicable Product Version | 6F

Document Number | TMITI017

 

Overview

This guide explains the Virtual Column provided by Tibero and the migration method.
 

Virtual Column

A virtual column whose value is automatically calculated using other column values or other expressions. It is used to generate new values based on values from other columns.
 
Constraints
  • The value is not stored in the DB but calculated at runtime and returned.
  • It can be used in Update and Delete conditions, but the column value itself cannot be changed.
  • You cannot directly input data into a virtual column.
 
 

Method

Note

Testing was conducted in the following environment.
  • Virtual Machine: Virtual Box
  • OS: Centos7
  • Tibero: Tibero6 FS06 CS1902

Creating a Virtual Column

COLUMN_NAME [DATATYPE] [GENERATED ALWAYS] AS [EXPRESSION] [VIRTUAL]
  • COLUMN_NAME: Name of the virtual column
  • DATATYPE: Data type of the virtual column (if omitted, uses the data type of the expression)
  • AS EXPRESSION: Expression applied to the virtual column
  • GENERATED ALWAYS, VIRTUAL: Syntax indicating that the column is a virtual column; this can be omitted
 
Example
CREATE TABLE EMP10 (
 EMPNO NUMBER CONSTRAINTS PK_EMP10 PRIMARY KEY,
 ENAME VARCHAR2(50),
 PAY NUMBER,
 ANNUAL_INCOME NUMBER GENERATED ALWAYS AS (PAY * 12) VIRTUAL
);

 

Checking Virtual Columns

View: desc dba_tbl_columns
SQL> desc dba_tbl_columns
COLUMN_NAME TYPE CONSTRAINT
-------------------------------------------------------------------
OWNER VARCHAR(128)
TABLE_NAME VARCHAR(128)
COLUMN_NAME VARCHAR(128)
DATA_TYPE VARCHAR(65532)
DATA_TYPE_OWNER VARCHAR(128)
DATA_LENGTH NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR(1)
COLUMN_ID NUMBER
DATA_DEFAULT LONG --expression check
DEFAULT_LENGTH NUMBER
CHAR_COL_DECL_LENGTH NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR(1)
VIRTUAL_COLUMN VARCHAR(1) --whether it is a virtual column

 

Example

create table tb_virtual (c1 number, c2 number, c3 number generated always as (c1 + c2)
virtual);

SQL> select table_name, column_name, data_type, data_default, virtual_column from
dba_tbl_columns where virtual_column='Y';

TABLE_NAME COLUMN_NAM DATA_TYPE DATA_DEFAULT VIRTUAL_COLUMN
---------- ---------- ---------- -------------------- --------------------
TB_VIRTUAL C3 NUMBER ("C1" + "C2") Y

1 row selected.

 

Migrating Virtual Columns

Since Virtual Columns cannot have data directly input, during data migration you will encounter the error TBR-8143: Cannot insert a value in a virtual column. However, creating a duplicate table via CTAS is an exception, and in general, data migration is possible using the methods below.
 

tbexport, tbimport

Migration of tables with virtual columns is possible using the Tibero utilities tbexport and tbimport.
 

DB link

When migrating via DB link, it is possible to perform migration normally by selecting and inserting only the columns excluding the Virtual Column.
 
Example
--Create table
create table tb_virtual (c1 number, c2 number, c3 number generated always as (c1
+ c2) virtual );

--Load data via DB link
insert /*+ append parallel(8) */ into tb_virtual (c1, c2) select /*+ parallel(8) */ c1,
c2 from tb_virtual@link_t;
commit;

 

Tablemigrator

Migration of tables with Virtual Columns is possible using Tablemigrator versions that provide column order change functionality.
 
Example
--Create table
create table tb_virtual (c1 number, c2 number, c3 number generated always as (c1
+ c2) virtual );

--Load data
insert into tb_virtual(c1, c2) values (10, 10);
insert into tb_virtual(c1, c2) values (10, 10);
insert into tb_virtual(c1, c2) values (10, 10);
commit;

--migrator.properties configuration
SOURCE_TYPE=DEFAULT
SOURCE_DRIVER=com.m.internal.jdbc.MInternalDriver
SOURCE_URL=jdbc:internal:thin:@localhost:8629:tibero
SOURCE_USER=tibero
SOURCE_PASSWORD=tmax
SOURCE_SCHEMA=TIBERO
SOURCE_TABLE=tb_virtual(c1, c2)

TARGET_TYPE=TIBERO
TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver
TARGET_URL=jdbc:internal:thin:@localhost:8629:tibero
TARGET_USER=tibero
TARGET_PASSWORD=tmax
TARGET_SCHEMA=TIBERO
TARGET_TABLE=tb_virtual(c1, c2)

--Run Tablemigrator
sh migrator.sh PROPERTY_FILE=migrator.properties

--Execution result
## [WARNING] Ignored parameters:
INSERT_THREAD_COUNT

=== Parameters ===
PROPERTY_FILE=migrator.properties
SOURCE_TYPE=DEFAULT
SOURCE_DRIVER=com.m.internal.jdbc.MInternalDriver
SOURCE_URL=jdbc:internal:thin:@localhost:8629:tibero
SOURCE_USER=tibero
SOURCE_PASSWORD=************
SOURCE_SCHEMA=TIBERO
SOURCE_SCHEMAORIGN=TIBERO
SOURCE_TABLE=tb_virtual(c1, c2)
SELECT_SUBPARTITION=
TARGET_TYPE=DEFAULT
TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver
TARGET_URL=jdbc:internal:thin:@localhost:8629:tibero
TARGET_USER=tibero
TARGET_PASSWORD=************
TARGET_SCHEMA=TIBERO
TARGET_TABLE=tb_virtual(c1, c2)
SELECT_FETCH_SIZE=1024
SELECT_AS_BYTE=N
INSERT_BATCH=Y
INSERT_PARALLEL=N
INSERT_THREAD_COUNT=1
INSERT_ZERO_LENGTH_STRING_AS_NULL=Y
==================
SOURCE_SCHEMA : TIBERO
SOURCE_TABLE : tb_virtual(c1, c2)
TARGET_SCHEMA : TIBERO
TARGET_TABLE : tb_virtual(c1, c2)
[E0]1674180265486 - STARTED
[E0]1674180265490 - TOTAL Extracted ROWS: 3 (source schema = TIBERO, source
table = tb_virtual)
[L0]1674180265491 Loader started
[L0]1674180265514 TOTAL Loaded ROWS: 3 (target schema = TIBERO, target table
= tb_virtual)
Loader finished
[VERIFICATION] Source Info : TIBERO.tb_virtual : 3 Rows, Target Info :
TIBERO.tb_virtual : 3 Rows [OK]
Loading is ended.
Elapsed Time (milliseconds) : 160
ADD BATCH : 6
EXECUTE BATCH : 0

--Check result
SQL> select * from tb_virtual;
        C1         C2         C3
---------- ---------- ----------
        10         10         20
        10         10         20
        10         10         20
        10         10         20
        10         10         20
        10         10         20

6 rows selected.

 

 

 

Related to