《Informix数据装卸技术.ppt》由会员分享，可在线阅读，更多相关《Informix数据装卸技术.ppt（44页珍藏版）》请在三一办公上搜索。
1、IBM Informix Dynamic Server(IDS),IDS 数据装载、卸载技术,CSDL 孙国荣,议程,unload/loadunonload/onloaddbloaddbexport/dbimportHPLExternal table,议程,un load/loadunonload/onloadDbloaddbexport/dbimportHPLExternal table,unload/load 语法,unloadload,UNLOAD TO filename DELIMITER delimiter SELECT Statement;filename 可以为全路径或者相对路径
2、文件名;DELIMITER选项，定义数据列分隔符，默认为|;SELECT 项为SQL子句;,LOAD FROM filename DELIMITER delimiter INSERT INTO Table Name|Synonym Name|View Name(column);filename 可以为全路径或者相对路径文件名;DELIMITER选项，定义数据列分隔符，默认为|;Column 选项，默认为数据库表的字段的顺序，注意要与文件中数据列顺序保持一致;,unload/load 迁移步骤,导出导入,准备充足的磁盘空间；采用unload工具卸载表数据；使用dbschema 工具卸载表的数据模
3、式，生成建表SQL语句；磁盘数据文件及数据模式SQL语句移到目的主机上；,根据需要编辑生成的数据模式SQL语句，并创建空表；采用load工具将的数据文件装载至数据库中；,unload/load 操作内容,卸载内容Unload 只卸载迁移表的数据，不包含表数据模式及索引数据。SELECT子句的灵活方式，导出的数据可以来自一个表或者多个表。卸载粒度unload 卸载粒度包括部分字段或全部字段，部分记录或全表记录，是迁移工具中粒度最小的，也是该工具灵活方便的原因之一输出结果Unload 输出以分隔符分隔各字段的 ASCII 文件。分隔符可在卸载命令 delimiter 选项中显式指定导入数据导入到指
4、定的表,unload/load 锁情况,卸载表的锁定状态Unload：1.在数据库非日志状态下或脏读隔离级别下，可卸载表数据，即使迁移表被独占。2.在日志数据库以非脏读隔离级卸载独占的表时，返回锁冲突错误。3.对卸载表的加锁情况：在非日志数据库、或脏读、或提交读隔离级别下，对卸载表不锁定；在日志数据库中，以游标稳定读、重复读隔离级别卸载表，对表记录不同数据集加共享锁。装载时对表的锁定状况load：1.在日志数据库中，对装载表加排他锁。锁定粒度缺省情况下为行锁或页锁（建表时确定）。当装载数据量较大时，可显示将锁模式更改为表级锁。在装载过程中不能对表进行访问。2.在非日志数据库中，对装载表不加锁，
5、对装载成功的记录可随时访问。数据库日志状态Unload/load 装卸载不受数据库日志状态的限制。,unload/load 锁情况,Load支持事务并发load数据,1.通过事务控制数据的装载过程，失败后可以回滚本次装载的记录；begin work;load from filename insert into tablename;commit work;2.默认情况下，load把装载过程分成多个事务，当失败时回滚最后 一个子事务，已经导入的记录不能自动回滚。,T1,Begin work;Load from t_1.unl insert into t;Commit work;T2,Begin w
6、ork;Load from t_2.unl insert into t;Commit work;,unload/load 如何提高装载性能,提高 unload/load 性能的技巧 1去掉数据库（表）日志，提高 load 速度取掉目的表级日志 ALTER TABLE TABNAME TYPE(RAW);采用 LOAD 工具装载增加目的表表级日志 ALTER TABLE TABNAME TYPE(STANDARD);2采用多进程，加速 unload/load 卸装速度将需要装卸的多张表分配到不同的进程中并发装卸，可减少整体装卸载时间；将海量表分配到多个进程中并发卸装，可减少海量表的卸载时间 采用
7、多进程装载海量表，需要特别注意锁溢出与长事务的出现3去掉参照性约束及索引，加速 load 装载速度在装载前先删除表索引及参照性约束，在装载完毕后重新创建。,unload/load 应用场景,最小操作对象为表导出导入单个表的数据unload输出的文件是ASICC文件可以导入到外部应用进行使用，如EXCEL重建表的模式对 dbschem 卸出的 SQL 语句进行编辑，可改变：表的字段名、约束、权限、表名、封锁模式、初始 EXTENTS、后续 EXTENTS、索引名、分片信息等。不同数据页重新装载数据跨不同平台由于采用文本文件，可以在多个不同操作系统平台，不同数据库版本之间进行数据的迁移,议程,un
8、load/loadunonload/onloaddbloaddbexport/dbimportHPLExternal table,unonload/onload 语法,unonloadonload,unonload/onload 迁移步骤,导出导入,准备磁带，若采用磁盘文件方式，则准备充足的磁盘空间并创建一用于存放卸载数据的空文件。采用onunload卸载表，结果存放在磁带或磁盘文件中。磁带或磁盘文件移到目的主机上。,去掉目的数据库的日志。采用onload创建表结构、装载表数据及索引数据。目的数据库恢复日志。,unonload/onload 操作内容,卸载内容Onunload 卸载迁移表数据模
9、式、数据页、索引页信息。卸载粒度Onunload 卸载粒度为数据库级或表级。输出结果Ononload 输出为二进制数据文件,unonload/onload 锁情况,卸载表的锁定状态Unonload：不能对加有独占锁的表进行卸载，包括任意粒度的锁定：表级锁、行锁、页锁。卸载时对卸载表加表级共享锁。装载时对表的锁定状况onload：对装载表加互斥锁。数据库日志状态onload 要求数据库必须无日志,unonload/onload 应用场景,最小操作对象为表包括表的索引等输出的文件为二进制格式不需重建表的模式由于导出导入过程，不可以对表模式及数据进行调整，只能适应于表及数据的快速迁移。相同数据页大小
10、,议程,unload/loadunonload/onloaddbloaddbexport/dbimportHPLExternal table,dbload,dbload,Usage:dbload-d dbname-c cfilname-l logfile-e errnum-n nnum-i inum-s-p-r|-k-X-d 数据库名-c 命令文件名（也叫控制文件名）-l 保存导入过程中失败的记录-e 导入失败多少条记录后退出-s 仅检查语法，不真正执行任务-n 批量提交记录数定义-p 失败情况：提交或者放弃-i 从文件第几行开始，默认为第一行记录-r 导入过程对表不加排他锁-X 识别16进制
11、数据-k 导入过程对表加排他锁,dbload,命令文件 FILE filename DELIMITER|nfields INSERT INTO tablename(column)values(f01,f02,)Filename 定义输入文件名|分隔符号，这里为“|”，可以定义为其他的符号nfields 定义每行记录的列数tablename 定义导入的目的表(column)values 为可选项 f01为文件的第一列，依次类推,举例说明,导入说明数据库名：demodb控制文件名：load_tab.ctl错误日志文件名：error.log每次提交的数据条数：100000数据文件名：tab.unl数
12、据字段分隔符：“”每行数据的字段数据:14要插入的数据表名：tab应用命令如下：dbload-d demodb-c load_tab.ctl-l error.log-n 100000;控制文件Load_tab.ctl 内容：-file“tab.unl delimiter|14;insert into tab;,dbload优点,由于有容错能力，可以用来重复导入数据，已有记录保留不变，只增加新增记录可以指定分批提交记录数，从而能有效处理大数据量的表的导入对导入失败数据进行记录到指定文件中，可以帮助使用者快速找到有问题的记录信息可以指定文件记录开始行信息通过命令文件，灵活定义导入文件数据的格式,议
13、程,unload/loadunonload/onloaddbloaddbexport/dbimportHPLExternal table,dbexport,dbexport,dbexport-X-c-q-d-ss-o|-t-b-s-f 其中主要选项说明如下：-c 如果没有发生致命错误，则输出全部信息-d 仅输出blob描述符，不输出blob数据-q 在标准输出设备上不显示错误信息，警告和所生成的SQL数据定义语句database 指定卸出的数据库名-o directory 命名存储ASCII数据文件和模式文件的磁盘目录-t tapedevice 指定存储ASCII数据文件和模式文件的磁带设备路
14、径-b blocksize 指定磁带设备块大小（单位：KB）-s tapesize 指定磁带所能存储的数据总量（单位：KB）-f sql-command-file 指定存于磁盘上的模式文件,dbimport,dbimport,dbimport实用程序的语法：-c 指示dbimport在没有发生致命错误的情况下完成装入工作。-q 指示在标准输出设备上不显示错误信息和警告。输入文件位置选项：-i 指定存储ASCII文件和模式文件的磁盘目录，以便dbimport查找。-t 指定含有ASCII文件和模式文件的磁带设备名。-b 指定磁带设备块大小。block以KB为单位，该值必须能为所使用的设备接受。-
15、s 指定磁带的最大数据存储量。-f 指定磁盘上模式文件的路径名，以便dbimport查找模式文件，并以此为输入建立数据库。建库选项：-d 指定用来存储新建数据库的OnLine数据库空间(dbspace)。如果没有使用-d选项指定dbspace，则数据库缺省建立在根dbspace(rootdbs)中。-l 如果没有使用buffered参数，为装入的数据库建立无缓冲事务日志；如果使用了buffered参数，为装入的数据库建立有缓冲事务日志。-l选项等价于CREATEDATABASE语句的WITH LOG字句。如果没有使用-l选项，新建数据库不带日志。-ansi 为装入的数据库建立ANSI方式兼容的
16、事务日志。,举例说明,1、导出，首先创建导出一个目录，示例中在/data/目录下创建dbname的一个文件夹 dbexport dbname-c-ss-o/data/dbname2、导出完成后，在dbname目录下生成一个dbname.exp的目录，找到该目录下的dbname.sql模式脚本文件，需要利用工具（如sqleditor）来验证一下脚本的语法的正确性（导出的脚本可能存在语法上的错误），需要加以修改纠正。3、导入，在导入之前，需要按照存储情况先划分好DBSPACE dbimport dbname-i/data/dbname-c-l buffered-d default_dbspacen
17、ame4、导入完成后，进行数据库级别的统计更新 update statistics;,议程,unload/loadunonload/onloaddbloaddbexport/dbimportHPLExternal table,onpladm create job jobname-d devicename-D databasedb-t tabname flu,Job Name,Device name,Database name,Table name,Job type,usage:onpladm create job-p-d-D-t-flc|Nuadp-z|-n-T-S-M-B where,-p
18、project name-d device name,further qualified by flags-D database name-t table name-f flags,l means load job,default will be both load and unload c means runmode DELUXE with replication,default will be EXPRESS runmode N means runmode DELUXE without replication,default will be EXPRESS runmode u means
19、unload job a means device is a device array,default will be file d means device is a tape p means device is a pipe command-z type of format to be used,default is Delimited type D means DELIMITED_FORMAT FI means FAST_FORMAT FA means FIXED_ASCII_FORMAT FB means FIXED_BINARY_FORMAT C means COBOL_FORMAT
20、 CB means COBOL_BYTE_FORMAT-n no-conversion fast-job-T Target server,where load/unload is to be done-S Onpload server containing the onpload database-M device size,if device is a tape-B block size,if device is a tape,确定表名，定义HPL卸载/装载数据任务,onpladm run job jobname-fu-l unload_logname.log,Job Name,Job ty
21、pe,Log file,进入指定的目录(有足够空间)运行如下的命令,进行数据卸载,usage:onpladm run job-flu-p-l-S-Z where,-p project name-f flags,l means load job u means unload job-l log file name,default is std-out-S Onpload server containing the onpload database-Z Write/read to/from tape until end of media,卸载数据,重命名表，创建新表-调整空间分配,创建索引,增加约
22、束(注意:不要创建触发器),rename table tabname to tabname_old;create table tabname(colums)extent size 1280000 next size 1280000 lock mode row;Create index.其他索引及约束,onpladm run job jobname fl-l load_logname.log,装载数据,Job Name,Job type,Log file,进入数据卸载所在的目录 运行如下的命令,进行数据装载,usage:onpladm run job-flu-p-l-S-Z where,-p p
23、roject name-f flags,l means load job u means unload job-l log file name,default is std-out-S Onpload server containing the onpload database-Z Write/read to/from tape until end of media,后续动作,将TAPEDEV数据库参数设置为/dev/null,执行ontape s L 0进行空备份.若原表上存在触发器的表,删除原先旧表中的触发器,在新表上创建同样的触发器drop trigger create trigger.
24、#删除旧表打开PDQ，执行update statistics对新表进行统计更新,议程,unload/loadunonload/onloaddbloaddbexport/dbimportHPLExternal table,External table,A从filesystem导入数据到数据库B多线程机制C性能优异，对于大数据情况，导入性能比HPL性能高2到3倍，性能测试数据见本文档的3节测试数据。,External table,语法,SELECT FROM INSERT INTO SELECT FROM INSERT INTO SELECT FROM 导出数据select from INTO E
25、XTERNAL USING(FORMAT INFORMIX,DATAFILES(DISK:/data/latest/data.unl);,External table,SQL示例 A采用sameas(根据已有表结构),包含两个文件情况 create external table external_tablename sameas real_tablename USING(DATAFILES(DISK:/opt/IBM/informix/demo/ex_unl1.unl,DISK:/opt/IBM/informix/demo/ex_unl2.unl),FORMAT DELIMITED,REJEC
26、TFILE/opt/IBM/informix/demo/tablename.rej,MAXERRORS 100,DELIMITER|,DELUXE-EXPRESS);,External table,SQL示例 B 定义字段create external table external_tablename（a integer,b integer）USING(DATAFILES(DISK:/opt/IBM/informix/demo/ex_unl1.unl,DISK:/opt/IBM/informix/demo/ex_unl2.unl),FORMAT DELIMITED,REJECTFILE/opt
27、/IBM/informix/demo/server/omcrdb/tablename.rej,MAXERRORS 100,DELIMITER|,DELUXE-EXPRESS);,External table,C.访问外部表SELECT*FROM external_tablenameINSERT INTO real_tablename SELECT*FROM external_tablename INSERT INTO external_tablename SELECT*FROM real_tablename D.导出数据Select*from real_tablename where 1=1
28、INTO EXTERNAL external_tablename_newUSING(FORMAT INFORMIX,DATAFILES(DISK:/opt/IBM/informix/demo/new_data.unl);,External table,3.应用场景外部表适用于通过文本数据文件导入数据库情况。支持SQL语法，可以封装到应用程序代码中进行灵活的定义和调度。也适用于数据迁移，表数据重建。4.约束外部表对应得文件必须存放在数据库服务器端IDS11.5 FC6版本支持,总结,unload/load支持SQL中条用用来重建表、迁移表增量数据加载功能unonload/onload备份、恢复数据库迁移dbexport/dbimport数据库的备份、恢复数据迁移dbload灵活数据加载HPL高性能数据加载，控制较为复杂External table最新特性，性能高，使用简单。,
