数据库移动数据总结一附录
创建sample数据库
db2sampl
db2look -d sample -z DB2V97FP7 -e -o sample.sql schema 大小写区分
使用数据移动的场景及问题
1.在对某表做大量修改时，特别是CR时，检查数据库的备份记录，是否有最近的备份文件，如果有，请客户确认如果出现问题是否可用；另一种，就是做数据的临时备份，这时我们需要DataMovement Tools
如何评估表中的数据量大小
如何方便的查看表中是否含有标识列
db2 "select distinct identity from syscat.columns where tabname = ‘XXXX‘"
如何方便的查看表中是否含有lob列
db2 "select TABSCHEMA concat ‘.‘||TABNAME from syscat.columns where typename like ‘%LOB‘ and TABSCHEMA not like ‘%IBM%‘ and not like ‘SYS%‘" | grep -i xxxxx
另一种方式是查看表的定义
db2look -d db_name -t t1 t2 -e -o db2look_tables.out;cat db2look_tables.out
e.g. db2look -d sample -t dbv97fp7.employee -e
NOTE:如果在-t选项做了某表的alias，那么将不会导出数据
2. 在跨操作系统情况下，恢复数据库，在这种情况下，无法使用offline 或online 备份文件进行数据库恢复
似乎export 无法完成这样的工作啊，因为语法上是需要select 语句导出数据的，该如何实现的呢？
db2look+db2move
1)导出对象定义文件 db2look -d sample -e -l -o db2look.ddl 2)导出数据到文件 mkdir data db2move sample export 3) 打包数据文件和定义文件上传到对应的服务器上 4）创建数据库对象 db2look.ddl 文件中有创建表空间语句，如果表空间路径不一致，需要在db2look.ddl语句中修改 db2 -tvf db2look.ddl 5) 导入数据 db2move sample load 6) 后续处理 a)) Identity 标识列处理，load 不支持identity 标识列导入，需要单独导入 打开db2move.lst，找到对应表的数据文件 b)) load 并不会进行检查参数完整性约束和检查约束检查 db2 "SELECT substr(TABNAME,1,30) as TAB_NAME,STATUS,ACCESS_MODE,SUBSTR(CONST_CHECKED,1,1) AS FP_CHECKED,SUBSTR(CONST_CHECKED,2,1) AS CC_CHECKED FROM SYSCAT.TABLES WHERE STATUS=‘C‘" 数据库最佳实践中，有如下处理方法 db2 connect to db db2 -tx +w "with gen(tabname,seq) as (select rtrim(tabschema) || ‘.‘ || rtrim(tabname) as tabname, row_number() over (partition by status ) as seq from syscat.tables where status = ‘C‘),r(a, seq1) as (select CAST(tabname as VARCHAR(3900)), seq from gen, r where (r.seq1+1)=gen.seq), r1 as (select a, seq1 from r) select ‘SET INTEGRITY FOR ‘|| a || ‘IMMEDIATE CHECKED;‘ from r1 where seq1=(SELECT MAX(SEQ1) FROM R1)" > db2FixCheckPending.sql db2 -tvf db2FixCheckPending.sql
3. 表数据迁移，有时，客户需要将一个表中的数据迁移到另一个表中，其中表的定义是不一致的
1)原数据中没有标识列的情况下，导入到含有标识列的目标表
db2 " create table tbdeiden (c1 char(30), c2 int generated by default as identity, c3 real, c4 char(1))" import.del Robert, 45.2, J Mike, 76.9, K Leo, 23.4, I db2 "import from import.del of del replace into tbdeiden (c1, c3, c4)" db2 "truncate table tbdeiden immediate"
当在目标表中有较多的列时，我们可以选用
db2 import from import.del of del modified by identitymissing replace into tbdeiden
2）在原数据中带有标识列的情况下导入到含有标识列的表
import_id.del Robert, 1, 45.2, J Mike, 2, 76.9, K Leo, 3, 23.4, I db2 "import from import_id.del of del method P(1, 3, 4) replace into tbdeiden (c1, c3, c4)" db2 load from import_id.del of del method P(1, 3, 4) replace into tbdeiden (c1, c3, c4)" 当目标表中有较多的列时，我们可以选用 db2 import from import_id.del of del modified by identityignore replace into tbdeiden db2 load from import_id.del of del modified by identityignore replace into tbdeiden
3）带有 generated always 的标识列，不需要导入文件中的标识列值
db2 " create table tbgeiden (c1 char(30), c2 int generated always as identity (start with 500, increment by 1), c3 real, c4 char(1))" db2 import from import_id.del of del modified by identityignore replace into tbgeiden db2 import from import.del of del modified by identitymissing replace into tbgeiden
4）带有 generated always 的标识列，需要使用载入原数据中标识列的值
db2 import from import.del of del modified by identityoverride replace into tbgeiden --error
db2 load from import_id.del of del modified by identityoverride replace into tbgeiden
5）导入文件中含有标识列，但是表中不包含标识列
标识列(Identity column)
什么是标识列？如何创建含有标识列的表？
generated by default as identity
generated always as identity(start with 500, increment by 1）
标识列导出注意事项
可以使用EXPORT 从含有标识列的表中导出数据。但是标识列会限制您输出 文件格式的选择；
如果导出操作指定的SELECT 格式为select * from tablename,并且未使用METHOD选项，那么支持将标识列属性导出只IXF文件。然后可以使用IMPORT命令的REPLACE——CREATE 和CREATE 选项重新创建该表，包括其标识列属性。
如果通过包含GENERATED ALWAYS 类型的标识列的表创建的IXF文件，那么成功导入数据文件的唯一方法是在导入操作期间指定identityignore文件类型修饰符，否则拒绝所有行，导致SQL3550W
标识列导入注意事项
生成列（Generated column）
什么是生成列？
如何定义含有生成列的表？
generated always as
db2 "create table tbge (c1 int,
c2 int,
g1 int generated always as (c1 + c2),
g2 int generated always as (2 * c1),
c3 char(1))"
import_noge.del
1, 5, J
2, 6, K
3, 7, I
import_ge.del
1, 5, 10, 15, J
2, 6, 11, 16, K
3, 7, 12, 17, I
含有生成列的表导出数据没有任何限制，类同于普通表
生成列导入注意事项
没有使用文件类型修饰符时，将会按照如下规则进行
1）当数据文件中相应的行缺少生成列的值或提供了NULL值时，将创建生成列值；
db2 import from import_noge.del of del replace into tbge
直接导入将导致生成列后的列的值，无法导入必须使用如下方法
db2 "import from import_ge_null.del of del replace into tbge(c1,c2,c3)"
or
db2 "import from import_ge_null.del of del modified by generatedmissing replace into tbge"
2）如果为生成列提供了非空值，那么将拒绝该行（SQL3550W）
db2 import from import_ge.del of del replace into tbge
1, 5, 10, 15, J
SQL3550W The field value in row "3" and column "3" is not NULL, but the target column has been defined as GENERATED ALWAYS.
db2 "import from import_ge.del of del method P(1, 2, 5) replace into tbge (c1, c2, c3)"
db2 import from import_ge.del of del modified by generatedignore replace into tbge
or
db2 load from import_ge.del of del modified by generatedoverride replace into tbge
对于load生成列并使用generatedoverride,需要注意的事项
-bash-4.1$ db2 "select * from tbge"
C1 C2 G1 G2 C3
----------- ----------- ----------- ----------- --
SQL0668N Operation not allowed for reason code "1" on table "DBV97FP7.TBGE".SQLSTATE=57016 ????
SET INTEGRITY FOR table-name GENERATED COLUMN IMMEDIATE UNCHECK
SET INTEGRITY FOR table-name IMMEDIATE CHECKED
db2 => SET INTEGRITY FOR tbge immediate checked
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL3603N Integrity processing through the SET INTEGRITY statement has found
an integrity violation involving a constraint, a unique index, a generated
column, or an index over an XML column. The associated object is identified by
"DBV97FP7.TBGE.G2". SQLSTATE=23514
3）如果为不可空生成列创建了NULL值，例如如果将列定义为两个表列之和，但这两个表列的文件中含有NULL值，那么会发生这种情况，那么将拒绝整行（SQL0407N）
db2 "create table tbgenull (c1 int,
c2 int,
g1 int generated always as (c1 + c2),
g2 int generated always as (2 * c1) not null,
c3 char(1))"
db2 "import from import_ge_null.del of del replace into tbge(c1,c2,c3)"
generatedignore,generatedmissing,generatedoverride
大对象
导出大对象（LOB）列的表时，默认操作是对每个LOB值导出最多32KB，以便将其与列数据的余下部分放在同一个文件中。如果要导出超过32KB的LOB值，那么应将LOB数据写至单独文件以便截断。
要指定应将LOB写至自己的文件，可以使用lobsinfile文件修饰符。此文件类型修饰符指示EXPORT使用程序将LOB数据放置在LOBS TO 子句指定的目录中。使用LOBS TO 或LOBFILE会隐式激活lobsinfile文件类型修饰符。
默认情况下，LOB值与导出的关系数据将写至同一路径。如果LOBS TO 选项指定了一个或多个路径，那么EXPORT使用程序将循环使用这些LOB路径，以便每个成功的LOB值写入相应的LOB文件。
可以使用lobfile选项对输出LOB文件指定名称。如果指定了LOBFILE选项，那么LOBFILENAME的格式为lobfilespecxxx.lob,其中lobfilespec是为LOBFILE选项指定的值，而xxx是 EXPORT使用程序生存的lob文件的序号，否则，lobfilename的格式为exportfilename.xxx.lob,其中exportfilename是为EXPORT命令指定的已输出文件格式名称，而xxx是export实用程序输出的lob文件的序列号
默认情况下，多个LOB将写至单个文件，如果将各个LOB存储在不同的文件中，可以使用lobsinfepfiles文件修饰符已将每个LOB写至单独的文件
mkdir mylobs mylobs1 mylobs2
db2 "create table newemp_photo like emp_photo"
db2 "alter table newemp_photo add smallpic blob(102400)"
db2 "update newemp_photo np set np.smallpic = (select picture from newemp_photo np1 where np1.empno = np.empno)"
导出数据
db2 "export to emp_pho.del of del lobs to mylobs/ lobfile lobs1 modified by lobsinfile select * from emp_photo"
or
db2 "export to myfile.del of del lobs to mylobs1/, mylobs2/ modified by lobsinfile select * from emp_photo"
导入数据
db2 "import from myfile.del of del lobs from mylobs1/, mylobs2/ modified by lobsinfile insert into newemp_photo"
空值处理
在移动数据中，NULL值是最难处理的
nullindchar=x 指定x的值将用于替换空值 --完成将表 default值的功能
striptnulls 当将一个数据装入到一个变长字段时，将截断任何尾部的NULL（0x00）字符，如果没有指定此选项则会保留NULL，不能将次选项与striptblanks同时指定。他们是互斥的。次选项代替过去的padwithzero选项
striptblanks 当将一个数据装入到一个变长字段时，将截断任何尾部的空格，如果没有指定此选项则会保留空格，不能将次选项与striptnulls同时指定。他们互斥的。次选项代替过去的t选项
keepblanks 指定在执行导入或载入操作时，要保留CHAR，VARCHAR，LONG VARCHAR或CLOB等类型列中开头或结尾的空白字符（不包含在字符定界符中）。如果所定义的非空列包含一个或多个空白字符，且这些空白字符表示有效的数据，那么使用该修饰符会有用。
如果在导入时，未指定该修饰符，导入工具将用一个空值来替代空白字符，如果指定的列为非空，那么会报错；在载入CHAR列时，总会在结尾的空白中填入该列长度，但是，keepblanks 修饰符对于保留CHAR列中开头的空白是有必要的。
db2 "create table names(firstname varchar(12), lastname varchar(12))"
db2 "import from null.txt of asc modified by nullindchar=N method l( 1 12, 13 24) null indicators(0,13) insert into names"
-bash-4.1$ db2 "import from null.txt of asc modified by nullindchar=a insert into names"
SQL3400N The method specified in METHOD is not valid for Non-Delimited ASCII
files. It must be ‘L‘ for locations.
db2 create table orgtemp like org
db2 load from orgdata.asc of asc modified by striptnulls method l(1 8,9 22,23 30, 31 40, 41 53) messages load.msg insert into orgtemp
keepblanks
db2 create table newnames like names
db2 "insert into names values(‘Tallerico ‘, ‘ Teresa ‘)"
db2 "export to names.del of del modified by nochardel messages export.msg select * from names"
db2 load from names.del of del modified by keepblanks messages load.msg insert into newnames
db2 load from names.asc of asc modified by striptblanks method l(1 12,13 20) messages load.msg insert into newnames
移动定界ASC（DEL）文件时，一定要保证移动的数据不会因为定界符识别问题导致无意中发生改变。为帮助避免发生这些错误，DB2 会强制实施若干限制并提供了较多的文件修饰符。
DB2 的定界符使用规则：
定界符互斥
定界符不能是二进制零，换行符、回车或空格，默认小数点不能是字符串的定界符。
在DBCS环境中，不支持|为字符串定界符
空格（X‘20’）永远不能作为有效定界符
在导入期间将删除第一个字符前面的和最后一个字符后面的空格，不会删除单元格值中间插入的空格
由于句点与时间戳中的句点冲突，所以不能作为定界符
对于纯DBCS、混合DBCS和EUC来说，定界符范围为X00到X3F
双字符定界符
提供了文件修饰符：nodoubledel
db2 "create table words (mw varchar(100))"
nodoubledel.del
I am 6" tall
I love "you"
db2 load from nodoubledel.del of del modified by nodoubledel insert into words
doubledel.del
"I am 6"" tall"
"I love ""you"""
db2 load from doubledel.del of del insert into words;db2 "select * from words"
字符定界符
nochardel 字符字段不会用字符定界符扩起来
db2 "export to staffdata.del of del select * from staff"
db2 "export to staffdata_nc.del of del modified by nochardel select * from staff"
chardel 指定其他字符作为定界符，避免与数据冲突，例如数据中有“数据，此时不用默认的”“作为字符定界符，使用‘
db2 "export to staffdata_c.del of del modified by chardel‘‘ select * from staff"
coldel 修改默认的列定界符“，"为其他
db2 "export to staffdata_col.del of del modified by coldel- select * from staff"
delprioritychar 如何使用
移动DEL文件时另一个需要注意的问题是保留定界符的正确优先顺序。定界符的默认优先级为：行，字符，列。某些应用程序依赖于一下优先级：字符，行，列
"Vincent <row delimiter> is a manager", <row delimiter>
将被解释为两行：“Vincent" 和“is a manager"。如果使用字符定界符（“”）优先与行定界符（<row delimiter>),这意味着同意DEL文件将被正确的解释为一行“Vincent is a manager"
原文：
The current default priority for delimiters is: record delimiter, character delimiter, column delimiter. This modifier protects existing applications that depend on the older priority by reverting the delimiter priorities to: character delimiter, record delimiter, column delimiter.
DEMO:
delprioritychar.del
"Vincent
is a manager",
"Bao
is a employee"
测试如下导入语句：
db2 load from delprioritychar.del of del modified by delprioritychar insert into words
db2 load from delprioritychar.del of del insert into words
PC/IXF
典型导出操作包括插入到现有表中所选数据的输出，当然也可以导出整个表，以备以后使用import重建表。这种情况，必须指定文件格式为PC/IXF，然后通过CREATE 方式创建已保存表（包括索引）。但是出现以下任何情况，某些数据将不会保存到IXF文件中
1. 索引列名包含0x2B（+）或0x2D（-），索引信息不会保存在IXF文件中
2. 该表包含XML列
3. 该表是多维集群表（MDC）
4. 该表包含表分区键
5. 由于代码页转换而导致索引名长度超过128个字节
6. 该表是受保护的
7. EXPORT命令包含select * from tablename 以外的操作字符串
8. 对EXPORT指定了METHOD参数
空间局限性
在导出的数据文件所在的磁盘空间不够用的情况下，那么导出操作会失败，在这种情况下，如何处理
通过where子句指定条件来选择的数据量进行限制，以使一道出文件能够存放在目标文件系统中。多次调用EXPORT导出所有数据。
indexixf
对import可用，load不可用 指定导入工具删除表上所定义的所有索引，并由PC/IXF文件中的索引定义创建新的。该文件修饰符只能在表中内容要被替换时使用
db2 "create table newemp like employee"
db2 "ALTER TABLE newemp ADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPNO)"
db2 "export to empdata.ixf of ixf messages export.msg select * from employee"
db2 import from empdata.ixf of ixf modified by indexixf messages import.msg replace_create into newemp
db2 "export to empdata_noidx.ixf of ixf messages export.msg select * from employee where 1 = 1"
nochecklengths
import/load 均可用，指定即使输入数据超过目标列的大小，也应该尝试导入或载入每一行。在知道所有数据适合所有情况的情况下，可以使用该修饰符
db2 "create table resumes like emp_resume"
db2 "export to emp_resumedata.ixf of ixf select * from emp_resume"
db2 "import from emp_resumedata.ixf of ixf modified by nocheckklengths messages import.msg insert into resumes"
日期格式
在不同平台和系统上进行数据移动时，日期格式容易出问题。
这几个文件修饰符仅用于ASC和DEL格式
dateformat
x is the format of the date in the source file.2 Valid date elements are:
YYYY - Year (four digits ranging from 0000 - 9999)
M - Month (one or two digits ranging from 1 - 12)
MM - Month (two digits ranging from 01 - 12;
mutually exclusive with M)
D - Day (one or two digits ranging from 1 - 31)
DD - Day (two digits ranging from 01 - 31;
mutually exclusive with D)
DDD - Day of the year (three digits ranging
from 001 - 366; mutually exclusive
with other day or month elements)
A default value of 1 is assigned for each element that is not specified. Some examples of date formats are:
"D-M-YYYY"
"MM.DD.YYYY"
"YYYYDDD"
datesiso
timeformat
timestampformat
代码页不同注意事项
当导入数据时，表的代码页和输入文件的代码页不匹配，那么数据将无法导入，报SQL0332N.
forcein 该文件修饰符 不会由于代码页不匹配而拒绝数据，并取消代码页之间的转换。
XML
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
