DB2-LAB4-迁移数据
实验目的
• Use the DB2 IMPORT command to load data into a DB2 table.
• Run the INGEST command to efficiently load data into a DB2 table.
• Invoke the LOAD utility to process input files and load data into DB2
tables.
• Run SET INTEGRITY commands to resolve the set integrity pending
conditions resulting from loading data into tables with constraints
defined using a LOAD utility.
实验步骤
使用IMPORT组件向表格中导入数据。
常用于少量数据的导入，效率低，底层实际走的injest的SQL语句。源文件中如果有违反目的地表的约束的情况，IMPORT会失败。
db2inst1@shahuang-lt:~$ db2 IMPORT from course_file/artists.del of del insert into music.artists
SQL3109N The utility is beginning to load data from file
"course_file/artists.del".
SQL3110N The utility has completed processing. "79" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "79".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "79" rows were processed from the input file. "79" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read = 79
Number of rows skipped = 0
Number of rows inserted = 79
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 79
db2inst1@shahuang-lt:~$
使用INGEST组件导入数据
常用于大量数据的导入，效率高。
db2inst1@shahuang-lt:~/course_file/ddl$ db2 -tvf cr_toolspace.ddl
call sysinstallobjects ('INGEST','C',NULL,NULL)
Return Status = 0
db2inst1@shahuang-lt:~/course_file/ddl$
db2inst1@shahuang-lt:~/course_file/ddl$ db2 -tvf ingest_albums.ddl
ingest from file /home/db2inst1/course_file/albums.del format delimited messages ingest_albums.txt RESTART NEW 'ingest_alb' INSERT INTO music.albums
Number of rows read = 264
Number of rows inserted = 264
Number of rows rejected = 0
SQL2980I The ingest utility completed successfully at timestamp "05/19/2024
14:36:40.904638"
db2inst1@shahuang-lt:~/course_file/ddl$
使用DB2 LOAD组件导入数据到带约束的表格中。
常用于大量数据导入，效率高。
源文件中如果有违反目的地表的约束的情况，LOAD会成功，为了解决这个问题，引入了SET INTEGRITY来解决，失败的转移到异常表。
db2inst1@shahuang-lt:~/course_file/ddl$ db2 -tvf load_stock1.ddl
CALL SYSPROC.ADMIN_CMD ( 'LOAD FROM "/home/db2inst1/course_file/stock.del" of del MODIFIED BY GENERATEDMISSING METHOD P (1,2,3,4) MESSAGES ON SERVER INSERT INTO MUSIC.STOCK (ITEMNO,TYPE,PRICE,QTY) ' )
Result set 1
--------------
ROWS_READ ROWS_SKIPPED ROWS_LOADED ROWS_REJECTED ROWS_DELETED ROWS_COMMITTED ROWS_PARTITIONED NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL MSG_REMOVAL
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
777 0 777 0 0 777 - - SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('1834635460_515652502_DB2INST1')) AS MSG CALL SYSPROC.ADMIN_REMOVE_MSGS('1834635460_515652502_DB2INST1')
1 record(s) selected.
Return Status = 0
db2inst1@shahuang-lt:~/course_file/ddl$ db2 -tvf set_integrity_stock.sql
SET INTEGRITY FOR MUSIC.STOCK ALLOW NO ACCESS IMMEDIATE CHECKED
DB20000I The SQL command completed successfully.
db2inst1@shahuang-lt:~/course_file/ddl$
使用脚本批处理使用LOAD和SET INTEGRITY导入数据
db2inst1@shahuang-lt:~/course_file/ddl$ db2 -tvf create_exception_tables.ddl
create table music.artexp like music.artists in userspace1
DB20000I The SQL command completed successfully.
alter table music.artexp add column ts timestamp add column msg clob (32 k) not logged
DB20000I The SQL command completed successfully.
create table music.albexp like music.albums in userspace1
DB20000I The SQL command completed successfully.
alter table music.albexp add column ts timestamp add column msg clob (32 k) not logged
DB20000I The SQL command completed successfully.
db2inst1@shahuang-lt:~/course_file/ddl$
db2inst1@shahuang-lt:~/course_file/ddl$ db2 -tvf load_tables_clp.ddl
LOAD FROM "/home/db2inst1/course_file/concerts.del" OF del METHOD P (1, 2, 3) MESSAGES load_concert.txt REPLACE INTO MUSIC.CONCERTS (ARTNO, DATE, CITY)
Number of rows read = 10
Number of rows skipped = 0
Number of rows loaded = 10
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 10
select substr(tabname,1,18), status, substr(const_checked,1,1) as FK_CHECKED, substr(const_checked,2,1) as CC_CHECKED from syscat.tables where status='C'
1 STATUS FK_CHECKED CC_CHECKED
------------------ ------ ---------- ----------
0 record(s) selected.
load from "/home/db2inst1/course_file/artists.del" of del messages load_art.txt replace into music.artists for exception music.artexp
Number of rows read = 79
Number of rows skipped = 0
Number of rows loaded = 79
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 79
select substr(tabname,1,18), status, substr(const_checked,1,1) as FK_CHECKED, substr(const_checked,2,1) as CC_CHECKED from syscat.tables where status='C'
1 STATUS FK_CHECKED CC_CHECKED
------------------ ------ ---------- ----------
ARTISTS C Y Y
1 record(s) selected.
SET INTEGRITY FOR MUSIC.ARTISTS ALLOW NO ACCESS IMMEDIATE CHECKED FOR EXCEPTION in MUSIC.artists use MUSIC.artexp
SQL3601W The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state. SQLSTATE=01586
select substr(tabname,1,18), status, substr(const_checked,1,1) as FK_CHECKED, substr(const_checked,2,1) as CC_CHECKED from syscat.tables where status='C'
1 STATUS FK_CHECKED CC_CHECKED
------------------ ------ ---------- ----------
ALBUMS C N Y
STOCK C N Y
2 record(s) selected.
SET INTEGRITY FOR MUSIC.ALBUMS ALLOW NO ACCESS IMMEDIATE CHECKED FOR EXCEPTION IN MUSIC.albums use MUSIC.albexp
DB20000I The SQL command completed successfully.
SET INTEGRITY FOR MUSIC.STOCK ALLOW NO ACCESS IMMEDIATE CHECKED
DB20000I The SQL command completed successfully.
select substr(tabname,1,18), status, substr(const_checked,1,1) as FK_CHECKED, substr(const_checked,2,1) as CC_CHECKED from syscat.tables where status='C'
1 STATUS FK_CHECKED CC_CHECKED
------------------ ------ ---------- ----------
0 record(s) selected.
db2inst1@shahuang-lt:~/course_file/ddl$
浙公网安备 33010602011771号