DB2-LAB2-创建数据库并放置数据
实验目的
创建一个新的DB2数据库。创建一个新的存储组。使用DB2命令和SQL来访问数据库空间。
Creating databases and data placement
- Create a new DB2 database.
- Change some of the default database configuration options.
- Create a new Storage Group to support application storage.
- Create a set of tablespaces to support the database objects we plan to create.
- Use SQL queries and db2pd commands to review the options and disk storage associated with the new database.
创建数据库
修复后继续执行。期间请教了同学，这一步window的命令和linux的不同，使用同时成功后的命令。
db2inst1@shahuang-lt:~$ db2 create db musicdb
SQL1032N No start database manager command was issued. SQLSTATE=57019
db2inst1@shahuang-lt:~$
启动数据库
db2start后再执行
db2inst1@shahuang-lt:~$ db2start
05/18/2024 17:21:40 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
db2inst1@shahuang-lt:~$ db2 create db musicdb
DB20000I The CREATE DATABASE command completed successfully.
新建存储组
DDL路径下为是一组脚本，可通过命令行直接调用脚本。执行脚本后，第一行打印为命令详情，我认为里面的命令可以直接使用DB2双引号引用执行。执行期间遇到多个问题，见实现问题
db2inst1@shahuang-lt:~/ddl$ db2 -tvf create_stogroup.ddl
create stogroup app_data on '/home/db2inst1/db2_sample/path1', '/home/db2inst1/db2_sample/path2'
DB20000I The SQL command completed successfully.
db2inst1@shahuang-lt:~/ddl$ db2pd -db musicdb -storage
Database Member 0 -- Database MUSICDB -- Active -- Up 0 days 11:47:57 -- Date 2024-05-19-11.15.06.386477
Storage Group Configuration:
Address SGID Default DataTag Name
0x00007F34733A8820 0 Yes 0 IBMSTOGROUP
0x00007F35215AA000 1 No 0 APP_DATE
0x00007F35215AB000 2 No 0 APP_DATA
Storage Group Statistics:
Address SGID State Numpaths NumDropPen
0x00007F34733A8820 0 0x00000000 1 0
0x00007F35215AA000 1 0x00000000 1 0
0x00007F35215AB000 2 0x00000000 2 0
Storage Group Paths:
Address SGID PathID PathState PathName
0x00007F34733CC000 0 0 InUse /home/db2inst1
0x00007F352160D000 1 1024 NotInUse /home/db2inst1/db2inst1/NODE0000/MUSICDB
0x00007F35215AC000 2 2048 NotInUse /home/db2inst1/db2_sample/path1
0x00007F3521612000 2 2049 NotInUse /home/db2inst1/db2_sample/path2
创建表空间
表空间的概念：https://blog.csdn.net/mydriverc2/article/details/81287476
db2inst1@shahuang-lt:~/ddl$ db2 -tvf create_tablespace_tsp01.ddl
create tablespace TSP01 using stogroup APP_DATA initialsize 1M increasesize 100 K extentsize 4
DB20000I The SQL command completed successfully.
db2inst1@shahuang-lt:~/ddl$ db2pd -db musicdb -tablespaces
Database Member 0 -- Database MUSICDB -- Active -- Up 0 days 11:51:01 -- Date 2024-05-19-11.18.10.791735
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007F347A2E4F40 0 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE
0x00007F347A2F2300 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1
0x00007F347A2FF6C0 2 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1
0x00007F347A30CA80 3 DMS Large 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSTOOLSPACE
0x00007F352176A660 4 DMS Large 4096 4 Yes 8 1 1 Def 2 0 3 Yes TSP01
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState
0x00007F347A2E4F40 0 32768 32764 29984 0 2780 29984 29984 0x00000000 0 0 No n/a
0x00007F347A2F2300 1 1 1 1 0 0 - - 0x00000000 0 0 No n/a
0x00007F347A2FF6C0 2 8192 8160 96 0 8064 96 96 0x00000000 0 0 No n/a
0x00007F347A30CA80 3 8192 8188 152 0 8036 152 152 0x00000000 0 0 No n/a
0x00007F352176A660 4 256 248 12 0 236 12 12 0x00000000 0 0 No n/a
Tablespace Autoresize Statistics:
Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x00007F347A2E4F40 0 Yes Yes 33554432 -1 No None None No
0x00007F347A2F2300 1 Yes No 0 0 No 0 None No
0x00007F347A2FF6C0 2 Yes Yes 33554432 -1 No None None No
0x00007F347A30CA80 3 Yes Yes 33554432 -1 No None None No
0x00007F352176A660 4 Yes Yes 1048576 102400 No None None No
Tablespace Storage Statistics:
Address Id DataTag Rebalance SGID SourceSGID
0x00007F347A2E4F40 0 0 No 0 -
0x00007F347A2F2300 1 0 No 0 -
0x00007F347A2FF6C0 2 -1 No 0 -
0x00007F347A30CA80 3 -1 No 0 -
0x00007F352176A660 4 -1 No 2 -
Containers:
Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
0x00007F347A2CC120 0 0 File 32768 32764 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000000/C0000000.CAT
0x00007F347A398000 1 0 Path 1 1 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000001/C0000000.TMP
0x00007F347A2CCCC0 2 0 File 8192 8160 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000002/C0000000.LRG
0x00007F347A2D5620 3 0 File 8192 8188 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000003/C0000000.LRG
0x00007F3521610000 4 0 File 128 124 2048 0 /home/db2inst1/db2_sample/path1/db2inst1/NODE0000/MUSICDB/T0000004/C0000000.LRG
0x00007F3521610230 4 1 File 128 124 2049 0 /home/db2inst1/db2_sample/path2/db2inst1/NODE0000/MUSICDB/T0000004/C0000001.LRG
批量创建多个tablespace。
注意参数是不同的，最终创造出来的表空间也不同。
DMS:数据库管理的表空间；SMS:操作系统管理的表空间，通常更小，性能不高。
db2inst1@shahuang-lt:~/ddl$ db2 -tvf create_tablespaces.ddl
create tablespace tsp02 managed by database using (file 'tsp02' 128) extentsize 2 autoresize yes maxsize 2 M
DB20000I The SQL command completed successfully.
create tablespace tsp03 managed by database using (file 'tsp03' 1024) extentsize 8 autoresize yes maxsize 10 M
DB20000I The SQL command completed successfully.
create tablespace tsp04 managed by automatic storage using stogroup app_data initialsize 100 K maxsize none extentsize 2
DB20000I The SQL command completed successfully.
create tablespace tsp05 using stogroup app_data initialsize 64 K maxsize 1 M extentsize 2
DB20000I The SQL command completed successfully.
create regular tablespace tsp06 extentsize 4
DB20000I The SQL command completed successfully.
db2inst1@shahuang-lt:~/ddl$ db2pd -db musicdb -tablespaces
Database Member 0 -- Database MUSICDB -- Active -- Up 0 days 11:54:53 -- Date 2024-05-19-11.22.02.627986
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007F347A2E4F40 0 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE
0x00007F347A2F2300 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1
0x00007F347A2FF6C0 2 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1
0x00007F347A30CA80 3 DMS Large 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSTOOLSPACE
0x00007F352176A660 4 DMS Large 4096 4 Yes 8 1 1 Def 2 0 3 Yes TSP01
0x00007F3521777A20 5 DMS Large 4096 2 Yes 2 1 1 Def 1 0 1 Yes TSP02
0x00007F3521784DE0 6 DMS Large 4096 8 Yes 8 1 1 Def 1 0 7 Yes TSP03
0x00007F35217921A0 7 DMS Large 4096 2 Yes 4 1 1 Def 2 0 1 Yes TSP04
0x00007F352179F560 8 DMS Large 4096 2 Yes 4 1 1 Def 2 0 1 Yes TSP05
0x00007F35217AE7A0 9 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes TSP06
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState
0x00007F347A2E4F40 0 32768 32764 29984 0 2780 29984 29984 0x00000000 0 0 No n/a
0x00007F347A2F2300 1 1 1 1 0 0 - - 0x00000000 0 0 No n/a
0x00007F347A2FF6C0 2 8192 8160 96 0 8064 96 96 0x00000000 0 0 No n/a
0x00007F347A30CA80 3 8192 8188 152 0 8036 152 152 0x00000000 0 0 No n/a
0x00007F352176A660 4 256 248 12 0 236 12 12 0x00000000 0 0 No n/a
0x00007F3521777A20 5 128 126 6 0 120 6 6 0x00000000 0 0 No n/a
0x00007F3521784DE0 6 1024 1016 24 0 992 24 24 0x00000000 0 0 No n/a
0x00007F35217921A0 7 24 20 6 0 14 6 6 0x00000000 0 0 No n/a
0x00007F352179F560 8 16 12 6 0 6 6 6 0x00000000 0 0 No n/a
0x00007F35217AE7A0 9 8192 8188 12 0 8176 12 12 0x00000000 0 0 No n/a
Tablespace Autoresize Statistics:
Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x00007F347A2E4F40 0 Yes Yes 33554432 -1 No None None No
0x00007F347A2F2300 1 Yes No 0 0 No 0 None No
0x00007F347A2FF6C0 2 Yes Yes 33554432 -1 No None None No
0x00007F347A30CA80 3 Yes Yes 33554432 -1 No None None No
0x00007F352176A660 4 Yes Yes 1048576 102400 No None None No
0x00007F3521777A20 5 No Yes -4096 -1 No 2097152 None No
0x00007F3521784DE0 6 No Yes -4096 -1 No 10485760 None No
0x00007F35217921A0 7 Yes Yes 102400 -1 No None None No
0x00007F352179F560 8 Yes Yes 65536 -1 No 1048576 None No
0x00007F35217AE7A0 9 Yes Yes 33554432 -1 No None None No
Tablespace Storage Statistics:
Address Id DataTag Rebalance SGID SourceSGID
0x00007F347A2E4F40 0 0 No 0 -
0x00007F347A2F2300 1 0 No 0 -
0x00007F347A2FF6C0 2 -1 No 0 -
0x00007F347A30CA80 3 -1 No 0 -
0x00007F352176A660 4 -1 No 2 -
0x00007F3521777A20 5 0 No - -
0x00007F3521784DE0 6 0 No - -
0x00007F35217921A0 7 -1 No 2 -
0x00007F352179F560 8 -1 No 2 -
0x00007F35217AE7A0 9 -1 No 0 -
Containers:
Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
0x00007F347A2CC120 0 0 File 32768 32764 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000000/C0000000.CAT
0x00007F347A398000 1 0 Path 1 1 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000001/C0000000.TMP
0x00007F347A2CCCC0 2 0 File 8192 8160 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000002/C0000000.LRG
0x00007F347A2D5620 3 0 File 8192 8188 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000003/C0000000.LRG
0x00007F3521610000 4 0 File 128 124 2048 0 /home/db2inst1/db2_sample/path1/db2inst1/NODE0000/MUSICDB/T0000004/C0000000.LRG
0x00007F3521610230 4 1 File 128 124 2049 0 /home/db2inst1/db2_sample/path2/db2inst1/NODE0000/MUSICDB/T0000004/C0000001.LRG
0x00007F352171E480 5 0 File 128 126 - 0 /home/db2inst1/db2inst1/NODE0000/SQL00002/tsp02
0x00007F3521720FA0 6 0 File 1024 1016 - 0 /home/db2inst1/db2inst1/NODE0000/SQL00002/tsp03
0x00007F3521724000 7 0 File 12 10 2048 0 /home/db2inst1/db2_sample/path1/db2inst1/NODE0000/MUSICDB/T0000007/C0000000.LRG
0x00007F3521724230 7 1 File 12 10 2049 0 /home/db2inst1/db2_sample/path2/db2inst1/NODE0000/MUSICDB/T0000007/C0000001.LRG
0x00007F35217AD000 8 0 File 8 6 2048 0 /home/db2inst1/db2_sample/path1/db2inst1/NODE0000/MUSICDB/T0000008/C0000000.LRG
0x00007F35217AD230 8 1 File 8 6 2049 0 /home/db2inst1/db2_sample/path2/db2inst1/NODE0000/MUSICDB/T0000008/C0000001.LRG
0x00007F35217BD000 9 0 File 8192 8188 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000009/C0000000.USR
db2inst1@shahuang-lt:~/ddl$
通过SQL查询表空间
db2inst1@shahuang-lt:~/ddl$ db2 -tvf select_tablespaces.sql
select substr(tbspace,1,18) as tbspace, substr(definer,1,10) as definer, tbspaceid, tbspacetype, datatype, sgname from syscat.tablespaces
TBSPACE DEFINER TBSPACEID TBSPACETYPE DATATYPE SGNAME
------------------ ---------- ----------- ----------- -------- --------------------------------------------------------------------------------------------------------------------------------
SYSCATSPACE SYSIBM 0 D A IBMSTOGROUP
TEMPSPACE1 SYSIBM 1 S T IBMSTOGROUP
USERSPACE1 SYSIBM 2 D L IBMSTOGROUP
SYSTOOLSPACE DB2INST1 3 D L IBMSTOGROUP
TSP01 DB2INST1 4 D L APP_DATA
TSP02 DB2INST1 5 D L -
TSP03 DB2INST1 6 D L -
TSP04 DB2INST1 7 D L APP_DATA
TSP05 DB2INST1 8 D L APP_DATA
TSP06 DB2INST1 9 D A IBMSTOGROUP
10 record(s) selected.
查看table space中container的状态
db2inst1@shahuang-lt:
db2inst1@shahuang-lt:~/ddl$ db2 -tvf select_mon_get_cont.sql
select varchar(container_name, 80) as container_name, varchar(tbsp_name, 20) as tbsp_name, pool_read_time from table(mon_get_container('', -2)) as t order by tbsp_id
CONTAINER_NAME TBSP_NAME POOL_READ_TIME
-------------------------------------------------------------------------------- -------------------- --------------------
/home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000000/C0000000.CAT SYSCATSPACE 107
/home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000001/C0000000.TMP TEMPSPACE1 0
/home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000002/C0000000.LRG USERSPACE1 0
/home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000003/C0000000.LRG SYSTOOLSPACE 2
/home/db2inst1/db2_sample/path1/db2inst1/NODE0000/MUSICDB/T0000004/C0000000.LRG TSP01 0
/home/db2inst1/db2_sample/path2/db2inst1/NODE0000/MUSICDB/T0000004/C0000001.LRG TSP01 0
/home/db2inst1/db2inst1/NODE0000/SQL00002/tsp02 TSP02 0
/home/db2inst1/db2inst1/NODE0000/SQL00002/tsp03 TSP03 0
/home/db2inst1/db2_sample/path1/db2inst1/NODE0000/MUSICDB/T0000007/C0000000.LRG TSP04 0
/home/db2inst1/db2_sample/path2/db2inst1/NODE0000/MUSICDB/T0000007/C0000001.LRG TSP04 0
/home/db2inst1/db2_sample/path1/db2inst1/NODE0000/MUSICDB/T0000008/C0000000.LRG TSP05 0
/home/db2inst1/db2_sample/path2/db2inst1/NODE0000/MUSICDB/T0000008/C0000001.LRG TSP05 0
/home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000009/C0000000.USR TSP06 0
13 record(s) selected.
查看路径的状态
db2inst1@shahuang-lt:~/ddl$ db2 -tvf dbpaths.sql
select substr(type,1,30) as db_path_type, substr(path,1,50) as path_name from sysibmadm.dbpaths order by 1
DB_PATH_TYPE PATH_NAME
------------------------------ --------------------------------------------------
DBPATH /home/db2inst1/db2inst1/NODE0000/SQL00002/
DBPATH /home/db2inst1/db2inst1/NODE0000/SQL00002/MEMBER00
DB_STORAGE_PATH /home/db2inst1/db2_sample/path2/
DB_STORAGE_PATH /home/db2inst1/db2_sample/path1/
DB_STORAGE_PATH /home/db2inst1/db2inst1/NODE0000/MUSICDB/
DB_STORAGE_PATH /home/db2inst1/
LOCAL_DB_DIRECTORY /home/db2inst1/db2inst1/NODE0000/sqldbdir/
LOGPATH /home/db2inst1/db2inst1/NODE0000/SQL00002/LOGSTREA
TBSP_CONTAINER /home/db2inst1/db2inst1/NODE0000/SQL00002/tsp03
TBSP_CONTAINER /home/db2inst1/db2inst1/NODE0000/SQL00002/tsp02
10 record(s) selected.
查看storage和table space的状态
db2inst1@shahuang-lt:~/ddl$ db2pd -db musicdb -storage
Database Member 0 -- Database MUSICDB -- Active -- Up 0 days 00:11:10 -- Date 2024-05-19-11.35.57.083211
Storage Group Configuration:
Address SGID Default DataTag Name
0x00007F3473318820 0 Yes 0 IBMSTOGROUP
0x00007F3473318940 1 No 0 APP_DATE
0x00007F3473318A60 2 No 0 APP_DATA
Storage Group Statistics:
Address SGID State Numpaths NumDropPen
0x00007F3473318820 0 0x00000000 1 0
0x00007F3473318940 1 0x00000000 1 0
0x00007F3473318A60 2 0x00000000 2 0
Storage Group Paths:
Address SGID PathID PathState PathName
0x00007F347333C000 0 0 InUse /home/db2inst1
0x00007F347335E000 1 1024 NotInUse /home/db2inst1/db2inst1/NODE0000/MUSICDB
0x00007F3473382000 2 2048 InUse /home/db2inst1/db2_sample/path1
0x00007F3473380000 2 2049 InUse /home/db2inst1/db2_sample/path2
db2inst1@shahuang-lt:~/ddl$ db2pd -db musicdb -tablespaces
Database Member 0 -- Database MUSICDB -- Active -- Up 0 days 00:12:56 -- Date 2024-05-19-11.37.43.222766
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007F347A02AF40 0 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE
0x00007F347A038300 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1
0x00007F347A0476E0 2 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1
0x00007F351A05E080 3 DMS Large 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSTOOLSPACE
0x00007F351A06B440 4 DMS Large 4096 4 Yes 8 1 1 Def 2 0 3 Yes TSP01
0x00007F351A078800 5 DMS Large 4096 2 Yes 2 1 1 Def 1 0 1 Yes TSP02
0x00007F351A085BC0 6 DMS Large 4096 8 Yes 8 1 1 Def 1 0 7 Yes TSP03
0x00007F351A092F80 7 DMS Large 4096 2 Yes 4 1 1 Def 2 0 1 Yes TSP04
0x00007F351A0A0340 8 DMS Large 4096 2 Yes 4 1 1 Def 2 0 1 Yes TSP05
0x00007F351A0AD700 9 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes TSP06
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState
0x00007F347A02AF40 0 32768 32764 29984 0 2780 29984 29984 0x00000000 0 0 No n/a
0x00007F347A038300 1 1 1 1 0 0 - - 0x00000000 0 0 No n/a
0x00007F347A0476E0 2 8192 8160 96 0 8064 96 96 0x00000000 0 0 No n/a
0x00007F351A05E080 3 8192 8188 152 0 8036 152 152 0x00000000 0 0 No n/a
0x00007F351A06B440 4 256 248 12 0 236 12 12 0x00000000 0 0 No n/a
0x00007F351A078800 5 128 126 6 0 120 6 6 0x00000000 0 0 No n/a
0x00007F351A085BC0 6 1024 1016 24 0 992 24 24 0x00000000 0 0 No n/a
0x00007F351A092F80 7 24 20 6 0 14 6 6 0x00000000 0 0 No n/a
0x00007F351A0A0340 8 16 12 6 0 6 6 6 0x00000000 0 0 No n/a
0x00007F351A0AD700 9 8192 8188 12 0 8176 12 12 0x00000000 0 0 No n/a
Tablespace Autoresize Statistics:
Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x00007F347A02AF40 0 Yes Yes 33554432 -1 No None None No
0x00007F347A038300 1 Yes No 0 0 No 0 None No
0x00007F347A0476E0 2 Yes Yes 33554432 -1 No None None No
0x00007F351A05E080 3 Yes Yes 33554432 -1 No None None No
0x00007F351A06B440 4 Yes Yes 1048576 102400 No None None No
0x00007F351A078800 5 No Yes -4096 -1 No 2097152 None No
0x00007F351A085BC0 6 No Yes -4096 -1 No 10485760 None No
0x00007F351A092F80 7 Yes Yes 102400 -1 No None None No
0x00007F351A0A0340 8 Yes Yes 65536 -1 No 1048576 None No
0x00007F351A0AD700 9 Yes Yes 33554432 -1 No None None No
Tablespace Storage Statistics:
Address Id DataTag Rebalance SGID SourceSGID
0x00007F347A02AF40 0 0 No 0 -
0x00007F347A038300 1 0 No 0 -
0x00007F347A0476E0 2 -1 No 0 -
0x00007F351A05E080 3 -1 No 0 -
0x00007F351A06B440 4 -1 No 2 -
0x00007F351A078800 5 0 No - -
0x00007F351A085BC0 6 0 No - -
0x00007F351A092F80 7 -1 No 2 -
0x00007F351A0A0340 8 -1 No 2 -
0x00007F351A0AD700 9 -1 No 0 -
Containers:
Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
0x00007F347A012120 0 0 File 32768 32764 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000000/C0000000.CAT
0x00007F347A055000 1 0 Path 1 1 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000001/C0000000.TMP
0x00007F347A012CC0 2 0 File 8192 8160 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000002/C0000000.LRG
0x00007F347A01B620 3 0 File 8192 8188 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000003/C0000000.LRG
0x00007F347A024120 4 0 File 128 124 2048 0 /home/db2inst1/db2_sample/path1/db2inst1/NODE0000/MUSICDB/T0000004/C0000000.LRG
0x00007F347A024350 4 1 File 128 124 2049 0 /home/db2inst1/db2_sample/path2/db2inst1/NODE0000/MUSICDB/T0000004/C0000001.LRG
0x00007F347A024A60 5 0 File 128 126 - 0 /home/db2inst1/db2inst1/NODE0000/SQL00002/tsp02
0x00007F3479FEFD80 6 0 File 1024 1016 - 0 /home/db2inst1/db2inst1/NODE0000/SQL00002/tsp03
0x00007F3479FFD780 7 0 File 12 10 2048 0 /home/db2inst1/db2_sample/path1/db2inst1/NODE0000/MUSICDB/T0000007/C0000000.LRG
0x00007F3479FFD9B0 7 1 File 12 10 2049 0 /home/db2inst1/db2_sample/path2/db2inst1/NODE0000/MUSICDB/T0000007/C0000001.LRG
0x00007F3479FF5760 8 0 File 8 6 2048 0 /home/db2inst1/db2_sample/path1/db2inst1/NODE0000/MUSICDB/T0000008/C0000000.LRG
0x00007F3479FF5990 8 1 File 8 6 2049 0 /home/db2inst1/db2_sample/path2/db2inst1/NODE0000/MUSICDB/T0000008/C0000001.LRG
0x00007F3479FF8760 9 0 File 8192 8188 0 0 /home/db2inst1/db2inst1/NODE0000/MUSICDB/T0000009/C0000000.USR
db2inst1@shahuang-lt:~/ddl$
实验问题
创建数据库失败
期间请教了同学，这一步window的命令和linux的不同，使用同时成功后的命令。
db2inst1@shahuang-lt:~$ db2 create database musicdb //windows
db2inst1@shahuang-lt:~$ db2 create database musicdb //linux
could not be retrieved
现象
create失败。尝试db2start，同样报一下错误。
db2inst1@shahuang-lt:~$ db2 create database musicdb on /db_sample_experiment/
SQL10007N Message "-1390" could not be retrieved. Reason code: "3".
db2inst1@shahuang-lt:~$
原因与解决方案
原因是环境变量设置问题，参考了该篇博客：
https://blog.csdn.net/a1010256340/article/details/121750991
在db2inst用户下执行
cd /home/db2inst
vim ~/.bashrc
在文件中填下以下内容：
DB2_HOME=/opt/ibm/db2/V11.1/
export PATH=$PATH:$DB2_HOME/bin:/home/db2inst1/sqllib/bin
if [ -f /home/db2inst1/sqllib/db2profile ]
then
source /home/db2inst1/sqllib/db2profile
fi
使环境变量生效
db2inst@admin:~$ pwd
/home/db2inst
db2inst@admin:~$ source ~/.bash_profile
设置环境变量后解决
ddl文件找不到
现象
直接运行命令报错
db2inst1@shahuang-lt:~$ db2 -tvf create_stogroup.ddl
DB21005E An error occurred while accessing the file "create_stogroup.ddl".
尝试
怀疑是linux命令不同，在网上搜索
db2inst1@shahuang-lt:~$ db2 "CREATE STOGROUP app_date "
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "STOGROUP" was found following "CREATE ".
仍然失败
解决
将ddl文件夹复制到home路径下，并在确认执行脚本在其中。
CREATE STOGROUP失败
开始使用的是根目录下的文件夹，创建存储组一直失败。
因为这个目录没有访问权限，需要指定用户有权限的路径。修改为有权限的路径后成功。
db2inst1@shahuang-lt:~$ db2 "CREATE STOGROUP app_date ON '/home/db2inst1/db2inst1/NODE0000/M
USICDB/' "
DB20000I The SQL command completed successfully.
实验心得
优化
Terminal交互
su切换后的terminal界面十分难用，无法联想，无法使用上下键快速选择历史命令。
使用bash。但可能会出现实现问题中提到的，找不到命令的情况，需要设置一下环境变量
$ bash
db2inst1@shahuang-lt:~$ db2 "CREATE STOGROUP app_date "
db2inst1@shahuang-lt:~$ exit
$
浙公网安备 33010602011771号