这个演示使用了几种方法将数据加载到DB2表中，包括IMPORT、INGEST和load命令。在使用LOAD命令之后，您将使用SET INTEGRITY语句来解析约束检查。
实验目的：
1、使用DB2 IMPORT命令将数据加载到DB2表中。
2、运行INGEST命令以有效地将数据加载到DB2表中。
3、调用LOAD实用程序来处理输入文件并将数据加载到DB2表中。
4、运行SET INTEGRITY命令来解决由于将数据加载到使用LOAD工具定义约束的表中而导致的SET INTEGRITY pending条件。
准备工作
首先需要连接实例
Linux系统先连接LInux用户并登录，然后切换到实例所在的用户：
su - db2inst1
输入密码即可
然后换到ddl脚本所在的目录，并连接到数据库
cd /home/inst23/ddl
db2 connect to musicdb
IMPORT导入命令
/home/inst23/artists.del
1,"Alabama","C",,
2,"Bogguss, Suzy","C",,
3,"Black, Clint","C",,
4,"Brooks, Garth","C",,
5,"Chapin-Carpenter, Mary","C",,
6,"Gill, Vince","C",,
7,"Jackson, Alan","C",,
8,"Judds","C",,
9,"McEntire, Reba","C",,
10,"Sawyer Brown","C",,
11,"Strait, George","C",,
12,"Bernstein, Leonard","S",,
13,"Pavarotti, Luciano","S",,
14,"Segovia, Andres","S",,
15,"Chapman, Steven Curtis","G",,
16,"Abdul, Paula","B",,
17,"Bell Biv DeVoe","B",,
18,"Boyz II Men","B",,
19,"Cole, Natalie","B",,
20,"Color Me Badd","B",,
21,"Earth, Wind, and Fire","B",,
22,"Houston, Whitney","B",,
23,"King, B.B.","B",,
24,"Priest, Maxi","B",,
25,"Sade","B",,
26,"Turner, Tina","B",,
27,"UB40","B",,
28,"Wonder, Stevie","B",,
29,"Ponty, Jean-Luc","J",,
30,"Marsalis, Branford","J",,
31,"Sanborn, David","J",,
32,"Spyro Gyra","J",,
33,"DC Talk","G",,
34,"Duncan, Bryan","G",,
35,"4 Him","G",,
36,"Ashton, Susan","G",,
37,"Grant, Amy","G",,
38,"Winans","G",,
39,"Petra","G",,
40,"Smith, Michael W.","G",,
41,"Beach Boys","P",,
42,"Beatles","P",,
43,"Bolton, Michael","P",,
44,"Boston","P",,
45,"Carey, Mariah","P",,
46,"Chicago","P",,
47,"Clapton, Eric","P",,
48,"Collins, Phil","P",,
49,"Creedence Clearwater Rival","P",,
50,"Deep Purple","P",,
51,"Doors","P",,
52,"Eagles","P",,
53,"Estefan, Gloria","P",,
54,"Foreigner","P",,
55,"Gariel, Peter","P",,
56,"Grateful Dead","P",,
57,"Heart","P",,
58,"John, Elton","P",,
59,"Joel, Billy","P",,
60,"Journey","P",,
61,"Kansas","P",,
62,"Led Zeppelin","P",,
63,"McCartney, Paul","P",,
64,"Cougar Mellencamp, John","P",,
65,"Mike and the Mechanics","P",,
66,"Miller, Steve","P",,
67,"The Police","P",,
68,"Queen","P",,
69,"Rolling Stones","P",,
70,"Simon, Carley","P",,
71,"Springsteen, Bruce","P",,
72,"Steely Dan","P",,
73,"Stewart, Rod","P",,
74,"Van Halen","P",,
75,"Yes","P",,
76,"ZZ Top","P",,
77,"Livid Ids","P",,
99,"Double Dare","R",,
100,"Patti & Cartwheels","S",,把/home/inst23/artists.del里的数据导入music模式下的artists表里，即插入79条数据
db2 IMPORT from /home/inst23/artists.del of del insert into music.artists输出：alter table music.albums add constraint ARTNO_FK foreign key (artno) references music.artists (artno) on delete cascade on update no action
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0601N The name of the object to be created is identical to the existing
name "ARTNO_FK" of type "FOREIGN KEY". SQLSTATE=42710
alter table music.stock foreign key ITEMNO_FK (itemno) references music.albums on delete restrict
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0601N The name of the object to be created is identical to the existing
name "ITEMNO_FK" of type "FOREIGN KEY". SQLSTATE=42710
alter table music.stock add constraint cctype check (type in ('D', 'C', 'R'))
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0601N The name of the object to be created is identical to the existing
name "CCTYPE" of type "CHECK CONST". SQLSTATE=42710
$ db2 IMPORT from /home/inst23/artists.del of del insert into music.artists
SQL3109N The utility is beginning to load data from file
"/home/inst23/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
使用ingest命令加载数据
call sysinstallobjects ('INGEST','C',NULL,NULL) ;执行文件：
db2 -tvf cr_toolspace.ddl
输出：
call sysinstallobjects ('INGEST','C',NULL,NULL)
Return Status = 0
返回status=0表示成功了。
"Greatest Hits",1,1
"American Pride",1,2
"Pass It All Down",1,3
"Something Up My Sleeve",2,4
"Voice in the Wind",2,5
"The Hard Way",3,6
"Put Yourself in My Shoes",3,7
"No Time to Kill",3,8
"Killin' Time",3,9
"Ropin' the Wind",4,10
"No Fences",4,11
"In Pieces",4,12
"Hometown Girl",5,13
"Come On Come On",5,14
"State of the Heart",5,15
"When I Call Your Name",6,16
"I Still Believe in You",6,17
"Pocket Full of Gold",6,18
"Here in the Real World",7,19
"Don't Rock the Jukebox",7,20
"Rockin' with the Rhythm",8,21
"Talk About Love",8,22
"Greatest Hits",8,23
"Rumor Has It",9,24
"For My Broken Heart",9,25
"The Last One to Know",9,26
"The Boys are Back",10,27
"Cafe on the Corner",10,28
"The Dirt Road",10,29
"Holding My Own",11,30
"Easy Come Easy Go",11,31
"Chill of an Early Fall",11,32
"The Royal Edition",12,33
"Favorites 20th Century",12,34
"A Tribute",12,35
"Opera Classics",13,36
"Great Studio Recordings",13,37
"Volare",13,38
"My Favorite Works",14,39
"The Romantic Guitar",14,40
"The Baroque Guitar",14,41
"For the Sake of the Call",15,42
"The Great Adventure",15,43
"Spellbound",16,44
"Hootie Mack",17,45
"Cooleyhighharmony",18,46
"Take a Look",19,47
"Unforgettable",19,48
"Everlasting",19,49
"Live at the Apollo",23,50
"Bonefide",24,51
"Best of Me",24,52
"feReal",24,53
"Diamond Life",25,54
"Stronger Than Pride",25,55
"Love Deluxe",25,56
"Simply the Best",26,57
"What's Love Got to Do with It",26,58
"Break Every Rule",26,59
"Higher Ground",27,60
"Labour of Love",27,61
"Rat in the Kitchen",27,62
"Innervisions",28,63
"Jungle Fever",28,64
"Songs in the Key of Life",28,65
"Greatest Hits",28,66
"Cosmic Messenger",29,67
"Enigmatic Ocean",29,68
"Story Telling",29,69
"Dreams Beyond Control",32,70
"Three Wishes",32,71
"Nu Thang",33,72
"DC Talk",33,73
"Mercy",34,74
"Basics of Life",35,75
"4 Him",35,76
"Face the Nation",35,77
"Wakened by the Wind",36,78
"Angels of Mercy",36,79
"Susan Ashton",36,80
"Lead Me On",37,81
"Heart in Motion",37,82
"Let My People Go",38,83
"Bebe Cece",38,84
"Decisions",38,85
"The Rock Cries Out",39,86
"Unseen Power",39,87
"Petrafied",39,88
"The Live Set",40,89
"I 2 Eye",40,90
"The Big Picture",40,91
"Project",40,92
"Go West Young Man",40,93
"Endless Summer",41,94
"Pet Sounds",41,95
"In Concert",41,96
"1962 - 1966",42,97
"1967 - 1970",42,98
"Help!",42,99
"Rubber Soul",42,100
"Abbey Road",42,101
"Sgt. Pepper's ...",42,102
"Time Love and Tenderness",43,103
"The Hunger",43,104
"Michael Bolton",43,105
"Timeless",43,106
"Boston",44,107
"Don't Look Back",44,108
"Unplugged",45,109
"Mariah Carey",45,110
"Music Box",45,111
"Chicago",46,112
"Chicago II",46,113
"Chicago III",46,114
"Chicago IV",46,115
"Chicago V",46,116
"Chicago VI",46,117
"Chicago VII",46,118
"Chicago VIII",46,119
"Chicago IX",46,120
"Chicago X",46,121
"The Survivor",47,122
"Unplugged",47,123
"Journeyman",47,124
"No Jacket Required",48,125
"Both Sides",48,126
"12 Inchers",48,127
"Hello, I Must Be Going",48,128
"The Concert",49,129
"Gold",49,130
"Green River",49,131
"Come Taste the Band",50,132
"The Very Best of Deep Purple",50,133
"The Battle Rages On",50,134
"Made in Japan",50,135
"Waiting for the Sun",51,136
"Doors",51,137
"The Soft Parade",51,138
"Hotel California",52,139
"Live",52,140
"Desperado",52,141
"Greatest Hits",52,142
"One of These Nights",52,143
"Into the Light",53,144
"Mi Tierra",53,145
"Let it Loose",53,146
"Unusual Heat",54,147
"IV",54,148
"Records",54,149
"Foreigner",54,150
"Agent Provocateur",54,151
"So",55,152
"Us",55,153
"Peter Gabriel",55,154
"Still Truckin'",56,155
"Blues for Allah",56,156
"The Best of the Grateful Dead",56,157
"Desire Walks On",57,158
"Magazine",57,159
"Dreamboat Annie",57,160
"Little Queen",57,161
"Bebe Le Strange",57,162
"Dog and Butterfly",57,163
"Animal Notes",57,164
"Greatest Hits",58,165
"The Fox",58,166
"Captain Fantastic",58,167
"Elton John",58,168
"Carribou",58,169
"River of Dreams",59,170
"The Stranger",59,171
"Turnstiles",59,172
"52nd Street",59,173
"Captured",60,174
"Evolution",60,175
"Infinity",60,176
"Frontiers",60,177
"Escape",60,178
"Kansas",61,179
"Leftoverture",61,180
"Point of Know Return",61,181
"Houses of the Holy",62,182
"Led Zeppelin",62,183
"Led Zeppelin II",62,184
"In Through the Out Door",62,185
"Ram",63,186
"Band on the Run",63,187
"Off the Ground",63,188
"Nothin' Matters and What if It Did",64,189
"American Fool",64,190
"Human Wheels",64,191
"Word of Mouth",65,192
"Living Years",65,193
"Mike and the Mechanics",65,194
"The Joker",66,195
"Book of Dreams",66,196
"Fly Like an Eagle",66,197
"Every Breath You Take",67,198
"The Police",67,199
"Zenyetta Mondetta",67,200
"Ghost in the Machine",67,201
"Synchronicity",67,202
"Innuendo",68,203
"A Night at the Opera",68,204
"A Day at the Races",68,205
"News of the World",68,206
"Jump Back",69,207
"High Tides and Green Grass",69,208
"Let It Bleed ",69,209
"Best of Carley Simon",70,210
"Have You Seen Me Lately",70,211
"My Romance",70,212
"Lucky Town",71,213
"The River",71,214
"Born to Run",71,215
"Tunnel of Love",71,216
"Katy Lied",72,217
"Pretzel Logic",72,218
"Aja",72,219
"Gaucho",72,220
"Can't Buy a Thrill",72,221
"Unplugged",73,222
"The Best of Rod Stewart",73,223
"Sing It Again, Rod ",73,224
"Every Picture Tells a Story",73,225
"1984",74,226
"Women and Children First",74,227
"Van Halen",74,228
"5150",74,229
"II",74,230
"OU812",74,231
"Fair Warning",74,232
"Diver Down",74,233
"Fragile",75,234
"90125",75,235
"Close to the Edge",75,236
"Antenna",76,237
"Afterburner",76,238
"Fandango",76,239
"Time and Chance",20,240
"The Best of Earth, Wind, and Fire",21,241
"Let's Groove",21,242
"That's the Way of the World",21,243
"I'm Your Baby Tonight",22,244
"Whitney",22,245
"Whitney Houston",22,246
"Swing Low Sweet Chariot",23,247
"Lucille",23,248
"A Blues Night",23,249
"Tchokola",29,250
"No Absolute Time",29,251
"Trio Jeepy",30,252
"Bloomington",30,253
"Royal Garden Blues",30,254
"As We Speak",31,255
"Another Hand",31,256
"A Change of Heart",31,257
"Straight to the Heart",31,258
"Point of View",32,259
"The First 13",77,260
"Live at the Psychidelly",77,261
"Hand Stands",100,300
"Flips",100,301
"I Dare You",99,302ingest from file /home/inst23/albums.del
format delimited messages ingest_albums.txt
RESTART NEW 'ingest_alb' INSERT INTO music.albums ;执行文件：
db2 -tvf ingest_albums.ddl
输出：
ingest from file /home/inst23/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/31/2024
23:33:38.990389"
ingest from file /home/inst23/albums.del format delimited messages ingest_albums.txt RESTART NEW 'ingest_alb' INSERT INTO music.albums
SQL1024N A database connection does not exist. SQLSTATE=08003
但是使用db2 connect to musicdb报SQL1032N No start database manager command was issued. SQLSTATE=57019
说明没有实例启动，要用db2start启动实例，再连接数据库后再执行文件。
db2 connect to musicdb
db2 -tvf ingest_albums.ddl
使用LOAD命令
load_stock1.ddl
CALL SYSPROC.ADMIN_CMD (
'LOAD FROM "/home/inst23/stock.del" of del
MODIFIED BY GENERATEDMISSING METHOD P (1,2,3,4) MESSAGES ON SERVER
INSERT INTO MUSIC.STOCK (ITEMNO,TYPE,PRICE,QTY) ' ) ;运行文件：
db2 -tvf load_stock1.ddl输出：CALL SYSPROC.ADMIN_CMD ( 'LOAD FROM "/home/inst23/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('1519842474_588671658_DB2INST1')) AS MSG CALL SYSPROC.ADMIN_REMOVE_MSGS('1519842474_588671658_DB2INST1')
1 record(s) selected.
Return Status = 0
返回status=0表示执行成功。
integrity命令
SET INTEGRITY FOR MUSIC.STOCK
ALLOW NO ACCESS IMMEDIATE CHECKED
;
运行文件：
db2 -tvf set_integrity_stock.sql输出：SET INTEGRITY FOR MUSIC.STOCK ALLOW NO ACCESS IMMEDIATE CHECKED
DB20000I The SQL command completed successfully.
LOAD 命令与SET INTEGRITY 语句解决主表与子表挂起与复原的问题
create table music.artexp like music.artists in userspace1;
alter table music.artexp
add column ts timestamp
add column msg clob (32 k) not logged;
create table music.albexp like music.albums in userspace1;
alter table music.albexp
add column ts timestamp
add column msg clob (32 k) not logged; 执行文件：
db2 -tvf create_exception_tables.ddl输出每条SQL执行结果为“DB20000I The SQL command completed successfully.”说明执行成功。
db2
输出：
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 11.5.4.0
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
执行命令：
LOAD FROM "/home/inst23/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/inst23/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.
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL3600N The IMMEDIATE CHECKED option of the SET INTEGRITY statement is not
valid because the table "MUSIC.ARTISTS" is a user-maintained materialized
query table, a shadow table, or is not in Set Integrity Pending state.
SQLSTATE=51027
解决办法：
先用命令查看挂起情况：
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';
发现有两个表同时挂起了，这两个都是artists表的子表
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.
此时再查看表挂起结果：
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
------------------ ------ ---------- ----------
STOCK C N Y
1 record(s) selected.
此时发现就stock一个表挂起了。
然后就对stock执行重复操作：
执行命令：
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.
此时全部操作完成。
这个地方有些复杂，笔者出错了几次，后续会单独写个博客，在此先附上本次操作截图。
以上操作的复杂还是在于主表与子表的复杂，artists是主表，其有两个子表分别为albums与stock。所以在复原的过程中要先复原子表，再复原主表。
笔者试过同时复原主表、外表报错，表示先要复原外表。
SET INTEGRITY FOR MUSIC.ARTISTS,MUSIC.STOCK ALLOW NO ACCESS IMMEDIATE CHECKED
输入quit退出CIP模式。
5409
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
