DB2实例与数据库关系及目录结构
发布时间：2022-10-15 10:59:23 所属栏目：MySql教程 来源：
导读： 1.DB2实例和数据库的目录结构：
.1> [root@demo instance]# ./db2icrt -u fenced test
DBI1070I Program db2icrt completed successfully.
[root@demo instance]# ./db2ilist
d
.1> [root@demo instance]# ./db2icrt -u fenced test
DBI1070I Program db2icrt completed successfully.
[root@demo instance]# ./db2ilist
d
|
1.DB2实例和数据库的目录结构： .1> [root@demo instance]# ./db2icrt -u fenced test DBI1070I Program db2icrt completed successfully. [root@demo instance]# ./db2ilist db2inst1 test 实例test创建成功后，在实例home目录下生成了一个sqllib文件夹/home/test/sqllib,里面是一些配置文件以及软链接 到软件安装目录的文件: [test@demo sqllib]$ ll total 92 drwxr-xr-x 2 test test 4096 Dec 28 16:11 adm lrwxrwxrwx 1 root test 22 Dec 28 16:11 adsm -> /opt/ibm/db2/V9.7/adsm drwxr-x--- 2 test test 4096 Dec 28 16:11 backup lrwxrwxrwx 1 root test 21 Dec 28 16:11 bin -> /opt/ibm/db2/V9.7/bin drwxrwsr-t 2 test test 4096 Dec 28 16:13 cfg lrwxrwxrwx 1 root test 22 Dec 28 16:11 conv -> /opt/ibm/db2/V9.7/conv -rwxr-xr-x 1 test test 10575 Dec 28 16:11 db2cshrc drwxrwsrwt 4 test test 4096 Dec 28 16:18 db2dump ------->db2diag.log存放目录 -rwxr-xr-x 1 test test 6650 Dec 28 16:11 db2profile -rw-rw-r-- 1 test test 16384 Dec 28 16:13 db2systm ---->实例配置文件get dbmcfg读取 lrwxrwxrwx 1 root test 21 Dec 28 16:11 doc -> /opt/ibm/db2/V9.7/doc drwxrwsr-t 4 test test 4096 Dec 28 16:11 function lrwxrwxrwx 1 root test 23 Dec 28 16:11 gskit -> /opt/ibm/db2/V9.7/gskit drwx------ 3 test test 4096 Dec 28 16:13 hmonCache lrwxrwxrwx 1 root test 22 Dec 28 16:11 java -> /opt/ibm/db2/V9.7/java lrwxrwxrwx 1 root test 5 Dec 28 16:11 lib -> lib32 lrwxrwxrwx 1 root test 23 Dec 28 16:11 lib32 -> /opt/ibm/db2/V9.7/lib32 drwxrwsr-t 2 test test 4096 Dec 28 16:11 log lrwxrwxrwx 1 root test 22 Dec 28 16:11 misc -> /opt/ibm/db2/V9.7/misc lrwxrwxrwx 1 root test 25 Dec 28 16:11 samples -> /opt/ibm/db2/V9.7/samples drwxr-xr-x 3 test test 4096 Dec 28 16:11 security drwxr-xr-x 3 test test 4096 Dec 28 16:11 security32 drwxrwxr-x 2 test test 4096 Dec 28 16:12 sqldbdir -------->系统库目录 drwxrwsrwx 2 test test 4096 Dec 28 16:11 tmp lrwxrwxrwx 1 root test 23 Dec 28 16:11 tools -> /opt/ibm/db2/V9.7/tools drwxrwxrwx 2 test test 4096 Dec 28 16:13 uif -rwxr-xr-x 1 test test 0 Dec 28 16:11 usercshrc -rwxr-xr-x 1 test test 0 Dec 28 16:11 userprofile ------>实例环境变量文件 2>[test@demo ~]$ db2start SQL1063N DB2START processing was successful. [test@demo ~]$ db2 create db testdb on /db2data dbpath on /db2cfg DB20000I The CREATE DATABASE command completed successfully. 成功创建数据库testdb后数据库实例，查看数据库的2个目录文件： [test@demo db2data]$ cd /db2cfg -------------->数据库目录 [test@demo db2cfg]$ tree . `-- test ------------------------------------->实例名字 `-- NODE0000 ----------------------------->节点名字 |-- SQL00001 ------------------------->数据库编号 | |-- SQLBP.1 ---------------------->缓冲池控制文件 | |-- SQLDBCON | |-- SQLDBCONF--------------------->数据库参数配置文件get db cfg读取 | |-- SQLINSLK | |-- SQLOGCTL.LFH.2 --------------->数据库日志控制文件,记录LSN可用于崩溃恢复 | |-- SQLOGDIR --------------------->数据库日志目录 | | |-- S0000000.LOG | | |-- S0000001.LOG | | `-- S0000012.LOG | |-- SQLOGMIR.LFH | |-- SQLSGF.1 | |-- SQLSPCS.1 -------------------->表空间控制文件使用list tablespace | |-- SQLTMPLK | |-- db2event | | `-- db2detaildeadlock | | |-- 00000000.evt | | `-- db2event.ctl | |-- db2rhist.asc ------------------>数据库历史文件，使用list history浏览 | `-- db2rhist.bak `-- sqldbdir -------------------------->本地数据库目录 |-- sqldbbak |-- sqldbdir `-- sqldbins [test@demo TESTDB]$ cd /db2data ----------->数据库表等对象数据存放目录 [test@demo db2data]$ tree . `-- test ---------------------------------->实例名 `-- NODE0000 --------------------------->节点名 `-- TESTDB ------------------------->数据库名字 |-- T0000000 ------------------->系统表空间,存放数据字典 | `-- C0000000.CAT |-- T0000001 ------------------->临时表空间，存放join排序等临时数据 | `-- C0000000.TMP | `-- SQLTAG.NAM |-- T0000002 ------------------->用户表空间，存放表索引大对象等数据 | `-- C0000000.LRG `-- T0000003 `-- C0000000.LRG [test@demo ~]$ db2 list tablespace containers for 1 show detail Tablespace Containers for Tablespace 1 Container ID = 0 Name = /db2data/test/NODE0000/TESTDB/T0000001/C0000000.TMP Type = Path Total pages = 1 Useable pages = 1 Accessible = Yes 2.系统库目录与本地库目录 [root@demo instance]# locate sqldbdir|grep test|grep -v 'dir/' /db2cfg/test/NODE0000/sqldbdir ------>本地库目录，db2 list db directory on /db2cfg可见 /home/test/sqllib/sqldbdir ------->系统库目录，db2 list db directory可见 [test@demo NODE0000]$ db2 list db directory on /db2cfg Local Database Directory on /db2cfg ------>本地库目录 Number of entries in the directory = 1 Database 1 entry: Database alias = TESTDB Database name = TESTDB Database directory = SQL00001 Database release level = d.00 Comment = Directory entry type = Home Catalog database partition number = 0 Database partition number = 0 如果/db2cfg/test/NODE0000/sqldbdir目录损坏，上面则不能输出数据库信息 [test@demo ~]$ db2 list db directory System Database Directory ------->系统库目录 Number of entries in the directory = 1 Database 1 entry: Database alias = TESTDB Database name = TESTDB Local database directory = /db2cfg Database release level = d.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number = 如果/home/test/sqllib/sqldbdir目录损坏或者没有对数据库编目，上面则不能输出数据库信息， 也就不能db2 connect to db了，如下： [test@demo NODE0000]$ db2 uncatalog database testdb DB20000I The UNCATALOG DATABASE command completed successfully. DB21056W Directory changes may not be effective until the directory cache is refreshed. [test@demo NODE0000]$ db2 list db directory SQL1057W The system database directory is empty. SQLSTATE=01606 重新编目数据信息，db2 list db directory则显系统库目录里有刚编目的数据库： [test@demo ~]$ db2 catalog database testdb on /db2cfg DB20000I The CATALOG DATABASE command completed successfully. DB21056W Directory changes may not be effective until the directory cache is refreshed. [test@demo ~]$ db2 list db directory System Database Directory Number of entries in the directory = 1 Database alias = TESTDB Database name = TESTDB 面板数据回归模型实例_数据库实例_aspnet ajax进度条实例(批量插入大量数据) Local database directory = /db2cfg 3.DB2的实例与数据库关系 实例也叫数据库管理器(Database Manager)，可以理解为运行时环境包括一组进程/线程 和内存。如果数据库是一个岛屿，实例就是通向岛屿的桥梁，删除db2实例并不会删除数据库， 就是桥塌了岛屿依然在，重建实例就可以再次访问数据库，这与oracle数据库非常不同，而 且db2是一个实例下可以有多个数据库，但一个数据库只能属于一个实例，oracle刚好相反 一个实例只能有一个数据库，但一个数据库可以属于多个实例比如RAC环境. 1>drop掉实例 [root@demo instance]# pwd /opt/ibm/db2/V9.7/instance [root@demo instance]# ./db2idrop test DBI1070I Program db2idrop completed successfully. [root@demo instance]# ./db2ilist db2inst1 2>查看test的home目录下的sqllib文件夹已经被删除，但是并不影响 数据库的/db2data 和 /db2cfg目录 [test@demo ~]$ pwd /home/test [test@demo ~]$ ll total 0 3>重建实例 [root@demo instance]# ./db2icrt -u fenced test DBI1070I Program db2icrt completed successfully. 4>系统库目录下没有任何数据库 [test@demo ~]$ db2 list db directory SQL1057W The system database directory is empty. SQLSTATE=01606 ---->查看本地库目录却还在 [test@demo ~]$ db2 list db directory on /db2cfg Local Database Directory on /db2cfg Number of entries in the directory = 1 Database 1 entry: Database alias = TESTDB Database name = TESTDB Database directory = SQL00001 Database release level = d.00 Comment = Directory entry type = Home 5>编目本地数据库 [test@demo ~]$ db2 catalog database testdb on /db2cfg DB20000I The CATALOG DATABASE command completed successfully. DB21056W Directory changes may not be effective until the directory cache is refreshed. [test@demo ~]$ db2 list db directory System Database Directory ------>再次查看系统库目录也存在了testdb Number of entries in the directory = 1 Database 1 entry: Database alias = TESTDB Database name = TESTDB Local database directory = /db2cfg Database release level = d.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number = 6>连接到数据库，可查之前插入的数据 [test@demo ~]$ db2start SQL1063N DB2START processing was successful. [test@demo ~]$ db2 connect to testdb Database Connection Information Database server = DB2/LINUX 9.7.2 SQL authorization ID = TEST Local database alias = TESTDB [test@demo ~]$ db2 "select * from tb1" ID NAME ----------- ---------- 1 yo1 2 yo2 （编辑：我爱制作网_池州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
浙公网安备 33038102330577号