vectorwise数据库用copydb备份恢复测试

〇〇

从高版本到低版本也可以导入，但直接用默认值报错
[ingres@redflag11012602 ~]$ sql tpch <copy.in
INGRES TERMINAL MONITOR Copyright 2010 Ingres Corporation
VectorWise Linux Version VW 1.5.0 (a64.lnx/141)NPTL login
Sat Oct 27 04:07:24 2012
Enter \g to execute commands, "help help\g" for help, \q to quit

continue
* * * * go
* * set autocommit on
Executing . . .

continue
* * set nojournaling
Executing . . .

continue
* go
* * set session with privileges=all
Executing . . .

continue
* * set nocreate_compression
Executing . . .

continue
* * * * * * * * * *
        /* TABLES */

create table t2(
        id integer,
        name varchar(20) collate ucs_basic
)
with structure=vectorwise,
location = (ii_database)
Executing . . .

E_PS04F0 CREATE TABLE: A table declared as structure = vectorwise may not
    have  any other WITH clause options.
    (Sat Oct 27 04:07:24 2012)

- Terminated by Errors
VectorWise Version VW 1.5.0 (a64.lnx/141)NPTL logout
Sat Oct 27 04:07:24 2012

〇〇

要在执行copydb时不指定位置，这样在create table就没有location子句，和1.5兼容了
[ingres@redflag11012602 ~]$ copydb -no_loc tpch2 t2
INGRES COPYDB Copyright 2012 Ingres Corporation
Unload directory is '/home/ingres'.
Reload directory is '/home/ingres'.
There are 0 sequences owned by user 'ingres'.
There is one table owned by user 'ingres'.

COPYDB has created the scripts copy.out and copy.in. From an Ingres
prompt, run sql tpch2 < copy.out to copy the data.
[ingres@redflag11012602 ~]$ sql tpch2 < copy.out
INGRES TERMINAL MONITOR Copyright 2012 Ingres Corporation
Vectorwise Linux Version VW 2.5.1 (a64.lnx/162)NPTL login
Sat Oct 27 04:25:43 2012
Enter \g to execute commands, "help help\g" for help, \q to quit

continue
* * * * go
* * set autocommit on
Executing . . .

continue
* * set lockmode session where readlock=nolock
Executing . . .

continue
* go
* * set session with privileges=all
Executing . . .

continue
* * copy t2 () into '/home/ingres/t2.ingres'
Executing . . .

(1 row)
continue
*
Vectorwise Version VW 2.5.1 (a64.lnx/162)NPTL logout
Sat Oct 27 04:25:43 2012
[ingres@redflag11012602 ~]$ exit
logout

[root@redflag11012602 bin]# su - ingres
[ingres@redflag11012602 ~]$ . .ingVWsh
[ingres@redflag11012602 ~]$ sql tpch < copy.in
INGRES TERMINAL MONITOR Copyright 2010 Ingres Corporation
VectorWise Linux Version VW 1.5.0 (a64.lnx/141)NPTL login
Sat Oct 27 04:26:08 2012
Enter \g to execute commands, "help help\g" for help, \q to quit

continue
* * * * go
* * set autocommit on
Executing . . .

continue
* * set nojournaling
Executing . . .

continue
* go
* * set session with privileges=all
Executing . . .

continue
* * set nocreate_compression
Executing . . .

continue
* * * * * * * * *
        /* TABLES */

create table t2(
        id integer,
        name varchar(20) collate ucs_basic
)
with structure=vectorwise
Executing . . .

continue
* * * * *
        /* VW_TABLE COPIES */
copy t2 () from '/home/ingres/t2.ingres'
with row_estimate = 1
Executing . . .

(1 row)
continue
*
VectorWise Version VW 1.5.0 (a64.lnx/141)NPTL logout
Sat Oct 27 04:26:08 2012
[ingres@redflag11012602 ~]$

〇〇

如果使用-c选项可以使导出的是ascii文件
* select * from t2;
* \g
Executing . . .


+-------------+--------------------+-------+
|id           |name                |comment|
+-------------+--------------------+-------+
|            1|name1               |comment|
|            2|name11              |comment|
|            3|name11              |comment|
|            4|name112             |comment|
+-------------+--------------------+-------+
(4 rows)
continue
* \q
Your SQL statement(s) have been committed.

Vectorwise Version VW 2.5.1 (a64.win/162) logout
Sun Sep 09 22:02:25 2012

c:\Users\db2admin\backup>sql test_cpdb < copy.out
INGRES TERMINAL MONITOR Copyright 2012 Ingres Corporation
Vectorwise Microsoft Windows Version VW 2.5.1 (a64.win/162) login
Sun Sep 09 22:02:34 2012
Enter \g to execute commands, "help help\g" for help, \q to quit

continue
* * * * go
* * set autocommit on
Executing . . .

continue
* * set lockmode session where readlock=nolock
Executing . . .

continue
* go
* * set session with privileges=all
Executing . . .

continue
* * set decimal '.'
Executing . . .

continue
* * set date_format 'us'
Executing . . .

continue
* * set money_format 'l:$'
Executing . . .

continue
* * set money_prec '2'
Executing . . .

continue
* * * * * * * copy t2(
        id= c0tab with null(']^NULL^['),
        name= varchar(0)tab with null(']^NULL^['),
        comment= varchar(0)nl,
        nl= d0nl)
into 'c:/Users/db2admin/backup/t2.db2admin'
Executing . . .

(4 rows)
continue
*
Vectorwise Version VW 2.5.1 (a64.win/162) logout
Sun Sep 09 22:02:34 2012

c:\Users\db2admin\backup>type t2.db2admin
            1       5name1          7comment

            2       6name11         7comment

            3       6name11         7comment

            4       7name112        7comment

〇〇

大量数据的copy和vwload速度差不多
导出
c:\Users\db2admin\backup>copydb -c tpch2 part
INGRES COPYDB Copyright 2012 Ingres Corporation
Unload directory is 'c:\Users\db2admin\backup'.
Reload directory is 'c:\Users\db2admin\backup'.
There are 0 sequences owned by user 'db2admin'.
There is one table owned by user 'db2admin'.

COPYDB has created the scripts copy.out and copy.in. From an Ingres
prompt, run sql tpch2 < copy.out to copy the data.

c:\Users\db2admin\backup>sql tpch2 < copy.out
INGRES TERMINAL MONITOR Copyright 2012 Ingres Corporation
Vectorwise Microsoft Windows Version VW 2.5.1 (a64.win/162) login
Sun Sep 09 23:10:46 2012
Enter \g to execute commands, "help help\g" for help, \q to quit

continue
* * * * go
* * set autocommit on
Executing . . .

continue
* * set lockmode session where readlock=nolock
Executing . . .

continue
* go
* * set session with privileges=all
Executing . . .

continue
* * set decimal '.'
Executing . . .

continue
* * set date_format 'us'
Executing . . .

continue
* * set money_format 'l:$'
Executing . . .

continue
* * set money_prec '2'
Executing . . .

continue
* * * * * * * * * * * * * copy part(
        p_partkey= c0tab,
        p_name= varchar(0)tab,
        p_mfgr= varchar(0)tab,
        p_brand= varchar(0)tab,
        p_type= varchar(0)tab,
        p_size= c0tab,
        p_container= varchar(0)tab,
        p_retailprice= varchar(0)tab,
        p_comment= varchar(0)nl,
        nl= d0nl)
into 'c:/Users/db2admin/backup/part.db2admin'
Executing . . .

(2000000 rows)
continue
*
Vectorwise Version VW 2.5.1 (a64.win/162) logout
Sun Sep 09 23:10:53 2012

c:\Users\db2admin\backup>dir part.*
驱动器 C 中的卷没有标签。
卷的序列号是 3CE2-73F3

c:\Users\db2admin\backup 的目录

2012/09/10  11:10       353,548,318 part.db2admin

〇〇

大量数据的copy和vwload速度差不多
导出
c:\Users\db2admin\backup>copydb -c tpch2 part
INGRES COPYDB Copyright 2012 Ingres Corporation
Unload directory is 'c:\Users\db2admin\backup'.
Reload directory is 'c:\Users\db2admin\backup'.
There are 0 sequences owned by user 'db2admin'.
There is one table owned by user 'db2admin'.

COPYDB has created the scripts copy.out and copy.in. From an Ingres
prompt, run sql tpch2 < copy.out to copy the data.

c:\Users\db2admin\backup>sql tpch2 < copy.out
INGRES TERMINAL MONITOR Copyright 2012 Ingres Corporation
Vectorwise Microsoft Windows Version VW 2.5.1 (a64.win/162) login
Sun Sep 09 23:10:46 2012
Enter \g to execute commands, "help help\g" for help, \q to quit

continue
* * * * go
* * set autocommit on
Executing . . .

continue
* * set lockmode session where readlock=nolock
Executing . . .

continue
* go
* * set session with privileges=all
Executing . . .

continue
* * set decimal '.'
Executing . . .

continue
* * set date_format 'us'
Executing . . .

continue
* * set money_format 'l:$'
Executing . . .

continue
* * set money_prec '2'
Executing . . .

continue
* * * * * * * * * * * * * copy part(
        p_partkey= c0tab,
        p_name= varchar(0)tab,
        p_mfgr= varchar(0)tab,
        p_brand= varchar(0)tab,
        p_type= varchar(0)tab,
        p_size= c0tab,
        p_container= varchar(0)tab,
        p_retailprice= varchar(0)tab,
        p_comment= varchar(0)nl,
        nl= d0nl)
into 'c:/Users/db2admin/backup/part.db2admin'
Executing . . .

(2000000 rows)
continue
*
Vectorwise Version VW 2.5.1 (a64.win/162) logout
Sun Sep 09 23:10:53 2012

c:\Users\db2admin\backup>dir part.*
驱动器 C 中的卷没有标签。
卷的序列号是 3CE2-73F3

c:\Users\db2admin\backup 的目录

2012/09/10  11:10       353,548,318 part.db2admin

〇〇

导出5秒导入18秒
c:\Users\db2admin\backup>sql test_cpdb < copy.in
INGRES TERMINAL MONITOR Copyright 2012 Ingres Corporation
Vectorwise Microsoft Windows Version VW 2.5.1 (a64.win/162) login
Sun Sep 09 23:12:04 2012
Enter \g to execute commands, "help help\g" for help, \q to quit

continue
* * * * go
* * set autocommit on
Executing . . .

continue
* * set nojournaling
Executing . . .

continue
* go
* * set session with privileges=all
Executing . . .

continue
* * set decimal '.'
Executing . . .

continue
* * set date_format 'us'
Executing . . .

continue
* * set money_format 'l:$'
Executing . . .

continue
* * set money_prec '2'
Executing . . .

continue
* * set nocreate_compression
Executing . . .

continue
* * * * * * * * * * * * * * * * * *
        /* TABLES */

create table part(
        p_partkey integer not null,
        p_name varchar(55) not null collate ucs_basic,
        p_mfgr char(25) not null collate ucs_basic,
        p_brand char(10) not null collate ucs_basic,
        p_type varchar(25) not null collate ucs_basic,
        p_size integer not null,
        p_container char(10) not null collate ucs_basic,
        p_retailprice decimal(15,2) not null,
        p_comment varchar(23) not null collate ucs_basic,
         PRIMARY KEY(p_partkey)
)
with structure=vectorwise,
location = (ii_database)
Executing . . .

continue
* * * * * * * * * * * * * * * *
        /* VW_TABLE COPIES */
copy part(
        p_partkey= c0tab,
        p_name= varchar(0)tab,
        p_mfgr= varchar(0)tab,
        p_brand= varchar(0)tab,
        p_type= varchar(0)tab,
        p_size= c0tab,
        p_container= varchar(0)tab,
        p_retailprice= varchar(0)tab,
        p_comment= varchar(0)nl,
        nl= d0nl)
from 'c:/Users/db2admin/backup/part.db2admin'
with row_estimate = 2000000
Executing . . .

(2000000 rows)
continue
*
Vectorwise Version VW 2.5.1 (a64.win/162) logout
Sun Sep 09 23:12:22 2012

wlx678

学习，学习 。

tolywang

〇〇 发表于 2012-10-26 07:09
创建导入的数据库
执行上一步产生的导入脚本，自动将刚才实际导出的文件导入
c:\Users\db2admin\backup>c ...

在导入copy.in 到另外一个数据库的时候报错  ：

E_US191A CREATE/ALTER TABLE: Named collation 'ucs_basic' can only be used
with 'NCHAR, NVARCHAR' types

- terminated by Errors

是字符集的问题 ？