db2基础操作手册.ppt

DB2管理培训,2010年6月,了解db2结构，特点 学会db2常规管理维护技能,目的,开关机顺序 安全性管理 存储管理 软件管理 网络管理,主要内容,进程管理 任务调度 性能监控 ha简介,主要内容,Database global memory,Bufferpool,Extended Memory,DBHEAP (LOGBUF, CATALOG CACHE_SZ),UTILITY HEAP (BACKUP, RESTORE BUFFER),Package Cache,Lock List,Sort Heap,实例,实例相关的命令,创建实例 db2icrt db2icrt -u fenced_user_ID instance_name in UNIX 删除实例 db2idrop f instance_name 列出实例 db2ilist 移植实例db2imigr instance_name 升级实例 db2iupdt instance_name,设置db2环境参数,db2 profile registries 操作系统的环境变量 db2 dbm 配置参数 db2 db 配置参数,Db2环境参数控制着db2的操作和功能,Db2 profile registries,可以设置不同的级别 Instance/global/user/ instance node/instance profile 使用db2set命令，可以显示，修改，删除 db2set all显示所有设置的 db2set lr显示所有可以设置的 db2 db 配置参数,操作系统环境变量,windows Set param=value unix ksh和bsh Export param=value unix csh Setenv param=value,实例和数据库参数,读取 Db2 get database manager configuration Db2 get database configuration for database_name 设置 Db2 update dbm cfg using param value Db2 update db cfg for db_name using param value,连接到数据库,服务器端配置 设置db2 profile registry的参数DB2COMM,如： Db2set db2comm=tcpip 设置实例级参数 Db2 update dbm cfg using svcename 50000 修改该参数，需要重起实例,连接到数据库,客户端可供选择方式之一 自动搜索 有两种方法：known 和 search 需要server端的管理服务器启动db2admin start 配置管理服务器update admin cfg using discover search 配置实例update dbm cfg using discover_inst enable 配置数据库update db cfg for db_name using discover_db enable,连接到数据库,客户端可供选择方式之二 概要文件,连接到数据库,客户端可供选择方式之三 手工配置 编目节点 Catalog tcpip node node_name remote ip地址 Server 50000 编目数据库 Catalog db db_name as at node node_name,db2命令交互,db2cmd 命令行交互模式，可以直接输入Db2命令，操作系统命令前加！ 命令行模式，可以直接识别操作系统命令，db2命令前加db2 执行脚本 Db2 f myfile.clp 脚本中用-做注释,命令行可执行的命令,命令行选项和获得帮助,命令行选项 Db2 list command options 获得帮助 Db2 ? Db2 ? Command Db2 ? Sqlnnnn Db2 ? db2nnnn,命令中心,可以在控制中心调用 图形化的命令行处理器，功能类似 特殊的功能 可以得到sql的执行计划 可以将sql加到脚本中,控制中心,配置功能，可以显示和修改实例和数据库参数 备份、恢复、前滚数据库和表空间 管理本地和远程数据库，可以创建/删除、显示、编目远程数据库和去掉编目 管理存储。创建、修改、删除表空间。,字符集,Create database db_name using codeset GBK territory zh_CN,模式（schema）,模式是一组数据库对象的集合，他提供了数据库对象的逻辑分类 如果不指定模式名，则用当前用户名 切换当前模式可以用set current schema xxx或set current sqlid xxx,系统模式,系统模式： SYSIBM 基本的编目信息，不建议直接访问 SYSCAT 编目信息的只读视图，所有人具有查询权限，推荐的获取编目信息的途径 SYSSTAT 编目信息可更新视图，可以影响优化器 SYSFUN 用户定义函数,建表,创建新表： create table tabname(col1 type1 not null primary key,col2 type2 not null,) 根据已有的表创建新表： A：create table tab_new like tab_old B：create table tab_new as select col1,col2 from tab_old definition only,表空间,三种表空间 常规表空间 长表空间 临时表空间 两类表空间 DMS SMS 默认产生三个表空间：SYSCATSPACE,USERSPACE1,TEMPSPACE1,数据库对象-视图,创建视图：create view viewname as select statement 删除视图：drop view viewname 注：视图唯一能修改的是引用类型列，改变列的范围。其他定义好了都不能修改。当视图基于的基表drop后，视图变为无效。,数据库对象-索引,使用索引的主要原因有两条： 确保数据值的惟一性 提高 SQL 查询的性能 使用索引时，能够按一种排列好的顺序更迅速地访问数据，从而避免了使用临时存储排序数据这一耗时的任务。 创建索引：create unique index idxname on tabname(col.) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 索引的使用都是从WHERE中的条件得出的， 从谓词是否使用索引可以将谓词分为可索引谓词和不可索引谓词， 如下图，我们列出了索引谓词和不可索引谓词。,可索引谓词,数据约束,数据库中数据的完整性或有效性极其重要。确保插入数据库的数据的有效性非常困难，DB2 提供了定义某些可并入数据库的基于规则的约束或检查的能力。在 DB2 中，可使用以下检查来最小化将错误数据插入表中的风险： 可检查一行中的一段，看它们是否符合所关联列的数据类型和长度。例如，“Geoff” 值不匹配数据类型为 INTEGER 的列，因而带有该值的行会被拒绝，以这种方式来确保数据库中数据的有效性。 若表上定义了主键约束，表中的各行必须在一列或共同构成主键的多个列中具有惟一值。若插入的行中存在与现有键相同的键，则新行将被拒绝。 若表上已定义了惟一约束，表中的各行必须遵循此约束，即具有惟一值或构成惟一键的值组合。 若已定义了外键约束，表中各行的外键列或多个列必须具有与父表中一行的主键相匹配的值。在某些情况下，若一列或多个列定义为外键的一部分，而这个外键可为空，则空值也是可接受的。 若列上已定义了检查约束，各行必须遵循此约束。例如，EMPLOYEE 表的 SALARY 列上的检查约束可能会阻止应用程序或用户插入工资低于 0 的新员工记录或行。插入表的任何 salary 值小于 0 的行都会被拒绝，从而最小化将错误数据插入表中的风险。,检查约束,数据库锁,数据库事务,数据类型,NULL值,尽可能设置为not null，可以通过default的方式来解决insert null的情况 查询 is null,Identity列,是一个数字型的字段，当新行插入时，db2自动产生一个唯一的数字 一个表中只能有一个列被定义有该属性 可以定义成always或default的方式 Always方式，只能有db2产生，不允许应用提供值 Default方式，可以由应用提供值，不能保证唯一性,简单查询,select distinct column-list from table-list where group by having order by ase/ease,内连接,外连接表的分类,保留行表 左外连接中左边的表。 右外连接中右边的表。 全外连接中全部的表。 替换 NULL 的表 左外连接中右边的表 右外连接中左边的表 全外连接中全部的表,一个简单的左外连接,一个简单的右外连接,全外连接,子查询,子查询可以由内连接和外连接代替，推荐做法 in、exist可以由内连接代替 select projno,projname,d.deptno,deptname from department d left outer join project p on p.deptno=d.deptno; select projno,projname from department where deptno in (select deptno from project); not in、not exist可以由外连接代替 select projno,projname,d.deptno,deptname from department d left outer join project p on p.deptno=d.deptno where jname is null; select projno,projname from department where deptno not in (select deptno from project);,Db2中的函数,内建函数 列函数:avg,sum,count, 操作函数:+ 类型转换函数decimal 其他 用户自定义函数UDF,1、类型转化函数： 转化为数字类型的： decimal, double, Integer, smallint,real Hex(arg):转化为参数的16进制表示。 转化为字符串类型的： char, varchar Digits(arg):返回arg的字符串表示法，arg必须为decimal。 转化为日期时间的： date, time,timestamp 2、时间日期： year, quarter, month, week, day, hour, minute ,second dayofyear(arg):返回arg在年内的天值 Dayofweek(arg):返回arg在周内的天值 days(arg):返回日期的整数表示法，从0001-01-01来的天数。 midnight_seconds(arg):午夜和arg之间的秒数。 Monthname(arg):返回arg的月份名。 Dayname(arg):返回arg的星期。,常用函数介绍,烟草产品事业部培训中心,3、字符串函数： length,lcase, ucase, ltrim, rtrim Coalesce(arg1,arg2.):返回参数集中第一个非null参数。 Concat (arg1,arg2):连接两个字符串arg1和arg2。 insert(arg1,pos,size,arg2):返回一个，将arg1从pos处删除size个字符，将arg2插入该位置。 left(arg,length):返回arg最左边的length个字符串。 locate(arg1,arg2,):在arg2中查找arg1第一次出现的位置，指定pos，则从arg2的pos处开始找arg1第一次出现的位置。 posstr(arg1,arg2):返回arg2第一次在arg1中出现的位置。 repeat(arg1 ,num_times):返回arg1被重复num_times次的字符串。 replace(arg1,arg2,arg3):将在arg1中的所有arg2替换成arg3。 right(arg,length):返回一个有arg左边length个字节组成的字符串。 space(arg):返回一个包含arg个空格的字符串。 substr(arg1,pos,):返回arg1中pos位置开始的length个字符，如果没指定length，则返回剩余的字符。,常用函数介绍,4、数学函数： Abs, count, max, min, sum Ceil(arg):返回大于或等于arg的最小整数。 Floor(arg):返回小于或等于参数的最小整数。 Mod(arg1,arg2):返回arg1除以arg2的余数，符号与arg1相同。 Rand():返回1到1之间的随机数。 Power(arg1,arg2):返回arg1的arg2次方。 Round(arg1,arg2):四舍五入截断处理，arg2是位数，如果arg2为负，则对小数点前的数做四舍五入处理。 Sigh(arg):返回arg的符号指示符。-1,0,1表示。 truncate(arg1,arg2):截断arg1，arg2是位数，如果arg2是负数，则保留arg1小数点前的arg2位。 5、其他： nullif(arg1,arg2)：如果2个参数相等，则返回null，否则，返回参数1,烟草产品事业部培训中心,常用函数介绍,数据库的备份,数据库备份 是数据库的一个完整的副本。除了数据外，备份副本还包含关于表空间、容器、数据库配置、日志控制文件和恢复历史文件的信息。注意，备份不会存储数据库管理器配置文件或注册表变量。只有数据库配置文件才会得到备份。 要执行备份，需要 SYSADM、SYSCTRL 或 SYSMAINT 权限,离线备份,要为数据库 sample 执行完整的离线备份，并将备份副本存储在 d:mybackups 目录中，可以使用以下命令： BACKUP DATABASE sample TO d:mybackups 要使用其他选项为数据库 sample 执行完整离线备份，可以使用以下命令： (1) BACKUP DATABASE sample (2) TO /db2backup/dir1, /db2backup/dir2 (3) WITH 4 BUFFERS (4) BUFFER 4096 (5) PARALLELISM 2 我们来更仔细观察前面的命令： 1.表明要备份的数据库的名称（或别名）。 2.指定用于存储备份的位置。 3.表明在备份操作期间可以使用多少个内存缓冲区。使用多个缓冲区可以提高性能。 4.表明每个缓冲区的大小。 5.决定使用多少媒介读/写进程/线程来进行备份。,数据库日志,DB2 事务日志对于恢复来说是至关重要的。它们跟踪数据库对象和数据上发生的变化。 主日志文件和辅助日志文件 主日志文件 是在建立第一个数据库连接或者数据库活动时立即分配的。辅助日志文件 是在需要时动态分配的。 活动日志 如果以下两个条件之一得到满足，则一个日志被认为是活动的（active）： 它包含关于尚未被提交或回滚的事务的信息。 它包含关于已经被提交但是其更改还没有被写（具体化）到数据库磁盘的事务的信息。 在线归档日志 离线归档日志,数据库日志,1.配置日志空间 DB2数据库通过日志文件维护数据的完整性和一致性。DB2 数据库的日志空间可通过如下公式计算： 日志空间 = （主日志文件 +辅助日志文件） * 日志文件尺寸 其中： 主日志文件由参数 LOGPRIMARY 控制， 二级日志文件由参数 LOGSECOND 控制 日志文件尺寸由参数 LOGFILSIZ 控制 2. 正确的处理日志满 根据实际的情况配置以上相关参数。 如果出现日志空间满的情况，DB2 会将系统挂起。 这时，应该增加 LOGSECOND 参数，而不要尝试 db2stop force 或者 kill。,启用归档日志,一个数据库的日志记录的类型是由数据库参数 LOGARCHMETH1 决定的。当 LOGARCHMETH1 为 OFF（默认值）时，归档日志记录被禁用，循环日志记录被启用。 为了启用归档日志记录，可以将 LOGARCHMETH1 设置为以下值中的任何一个值： LOGRETAIN：日志文件将被保留在活动日志目录中 USEREXIT：日志的归档和检索是由用户提供的用户出口程序自动执行的，这个出口程序必须由 db2uext2 调用。这个程序用于将在线归档日志移动到与活动日志目录不同的一个目录中，或者移动到另一个媒介上。当在 ROLLFORWARD 操作期间需要某些离线归档日志时，这个程序还可以用于将离线归档日志取出到活动日志目录中。在 Windows 下，db2uext2 必须存储在 sqllibbin 目录中，在 UNIX 下，db2uext2 必须存储在 sqllib/adm 目录中 DISK:directory_name：与 USEREXIT 使用相同的算法。DB2 不调用用户出口程序，而是自动将日志文件从活动日志目录归档到指定的目录 TSM:management class name：与 USEREXIT 使用相同的算法。日志被归档到本地 Tivoli Storage Manger (TSM) 服务器上。management class name 参数是可选的。如果没有指定该参数，则使用默认的管理类 VENDOR:library_name：与 USEREXIT 使用相同的算法。日志是使用指定供应商的库来归档的 由于向后兼容的原因，数据库配置文件仍然包含参数 LOGRETAIN 和 USEREXIT。从 8.2 版开始，这两个参数已经被 LOGARCHMETH1 取代。如果更新 USEREXIT 或 LOGRETAIN 参数，那么 LOGARCHMETH1 将自动被更新，反之亦然。,在线备份,在线备份 BACKUP DATABASE sample ONLINE TO /dev/rdir1, /dev/rdir2 由于在线备份允许用户在执行备份的同时访问数据库，因此这些用户作出的更改很可能不会存储在备份副本中。因此，仅仅凭借在线备份还不足以进行恢复，另外还需要备份操作期间收集到的相应的日志。 当在线备份完成时，DB2 强制关闭当前活动日志（并将其归档），因此很容易在备份完成时收集当前活动日志。 为了将日志也备份到备份副本中，可以使用 BACKUP DATABASE 命令的 INCLUDE LOG 选项。这样可以确保即使丢失了日志，也仍然可以使用备份镜像中包含的日志来恢复到最小的时间点上。 例如，要对 sample 数据库和日志进行在线备份，并以 /dev/rdir1 作为目标目录，可以发出： BACKUP DATABASE sample ONLINE TO /dev/rdir1 INCLUDE LOGS,数据库的恢复,使用一个备份文件作为输入，输出是一个新的或已有的数据库。 要恢复到已有的数据库，需要 SYSADM、SYSCTRL 或 SYSMAINT 权限。 要恢复到新的数据库，则需要 SYSADM 或 SYSCTRL 权限。,数据库恢复例子,要执行 sample 数据库的恢复，可以使用以下命令： (1)RESTORE DATABASE sample (2) FROM C:DBBACKUP (3) TAKEN AT 20060314131259 (4) WITHOUT ROLLING FORWARD (5) WITHOUT PROMPTING 上面的例子中： 1.表明要恢复的数据库镜像的名称。 2.指定要从中读取输入备份文件的位置。 3.如果该目录中有多个备份镜像，该选项将基于时间戳（备份名称的一部分）标识特定的备份。 4.如果一个数据库启用了归档日志记录，那么当该数据库被恢复时，它将自动被置于 rollforward pending 状态。这一行告诉 DB2 不要将该数据库置于 rollforward pending 状态。 5.当执行 RESTORE 时，您将看不到任何提示。,包括日志文件的恢复例子,备份镜像中包括日志文件，那么可以使用 RESTORE DATABASE 命令的 LOGTARGET 选项恢复日志文件。 为了使用 C:DBBACKUP 目录中的备份镜像恢复 SAMPLE 数据库，并将日志文件恢复到 C:DB2NODE0000SQL00001SQLOGDIR 目录，可以发出： RESTORE DATABASE sample FROM C:DBBACKUP LOGTARGET C:DB2NODE0000SQL00001SQLOGDIR 也可以通过使用 LOGS 关键字只恢复日志文件，而不恢复数据库： RESTORE DATABASE sample LOGS FROM C:DBBACKUP LOGTARGET C:DB2NODE0000SQL00001SQLOGDIR,Db2move例子,Db2move dbname export sn db2inst1 -u username p password Db2move dbname import sn db2inst1 -u username p password,Db2 export,EXPORT 实用程序使用一条 SQL SELECT 语句或 XQUERY 语句将数据从数据库表提取到一个文件中。数据可以被导出到 DEL、IXF 或 WSF 文件中。建议在 export 中包括 MESSAGES 子句，以捕获导出过程中遇到的错误、警告和包含有用信息的消息。 要想成功地调用 EXPORT 实用程序，必须拥有 SYSADM 或 DBADM 权限，或者拥有 EXPORT 命令中所访问的表或视图上的 CONTROL 或 SELECT 特权。 一个简单的导出的例子。下面的命令将 SELECT 语句的结果导出到一个 DEL 格式的文件中。消息文件 msg.out 用于记录有用的信息和遇到的错误或警告： EXPORT TO myfile.del OF DEL MESSAGES msg.out SELECT , staff.dept, org.location FROM org, staff WHERE org.deptnumb = staff.dept;,Db2 import语法,IMPORT 实用程序用一个输入文件将数据填充到一个表中，输入文件的文件类型可以是 ASC、DEL、IXF 或 WSF。目标是一个表、一个类型化表（typed table）或者一个视图。但是，不能导入到系统表、临时表和物化查询表。 例子： IMPORT FROM emp.ixf OF IXF MESSAGES msg.out CREATE INTO employee IN datatbsp INDEX IN indtbsp,Db2 import选项,下面显示的 IMPORT 命令具有五个不同的选项： IMPORT FROM file_name OF file_type MESSAGES message_file INSERT | INSERT_UPDATE | REPLACE | REPLACE_CREATE | CREATE INTO target_table_name INSERT 选项将导入的数据插入表中。目标表必须已经存在。 INSERT_UPDATE 将数据插入表中，或者更新表中具有匹配主键的行。目标表必须已经存在，并且定义了一个主键。 REPLACE 选项删除所有已有的数据，并将导入的数据插入到一个已有的目标表中。 使用 REPLACE_CREATE 选项时，如果目标表已经存在，则导入实用程序删除已有的数据，并插入新的数据，就像 REPLACE 选项那样。如果目标表还没有定义，那么首先创建这个表以及它的相关索引，然后再导入数据。正如您可能想像的那样，输入文件必须是 PC/IXF 格式的文件，因为那种格式包含对导出表的结构化描述。如果目标表是被一个外键引用的一个父表，那么就不能使用 REPLACE_CREATE。 CREATE 选项首先创建目标表和它的索引，然后将数据导入到新表中。该选项惟一支持的文件格式是 PC/IXF。还可以指定新表所在表空间的名称。,Db2 Load,LOAD 实用程序是用于为表填充数据的另一种方法。该实用程序将格式化的页面直接写入到数据库中。这种机制允许比 IMPORT 实用程序更有效地移动数据。 LOAD FROM input_source OF input_type MESSAGES message_file INSERT | REPLACE | TERMINATE | RESTART INTO target_tablename,Db2 Load,LOAD 可以以四种不同的模式执行： INSERT 模式将输入数据添加到一个表中，而不更改已有的表数据。 REPLACE 模式删除表中所有已有的数据，然后用输入数据填充这个表。 TERMINATE 模式终止装载操作，并回滚到它开始时所在的时间点。一个例外是，如果指定了 REPLACE 模式，那么表将被删节。 RESTART 模式用于重新开始之前被中断的装载命令。它将自动从最近的一致点继续。要使用这个模式，可以指定与前一个 LOAD 命令相同的选项，但是这一次加上 RESTART。它使实用程序可以发现在装载处理中生成的所有需要的临时表。因此，千万不要手动删除装载命令所生成的任何临时文件，除非您确信不再需要这些临时文件。一旦装载成功完成，这些临时文件将自动被删除。默认情况下，这些临时文件是在当前的工作目录中创建的。也可以使用 TEMPFILES PATH 选项指定存放临时文件的目录。,Db2move,db2move 是用于在 DB2 数据库之间移动大量表的一个数据移动工具。这个命令中支持的动作有 EXPORT、IMPORT、LOAD 和 COPY。EXPORT、IMPORT 和 LOAD 这几个动作的行为与前一屏中描述的完全相同。 db2move 将一组用户表从系统编目表中提取出来，并将每个表以 PC/IXF 格式导出。然后，PC/IXF 文件可以被导入或装载到另一个 DB2 数据库中。 下面是一些例子。这个命令用指定的用户 ID 和密码以 REPLACE 模式导入 sample 数据