《db2基础操作手册》PPT课件

DB2管理培训,2010年6月,了解db2结构，特点学会db2常规管理维护技能,目的,开关机顺序安全性管理存储管理软件管理网络管理,主要内容,进程管理任务调度性能监控ha简介,主要内容,Databaseglobalmemory,Bufferpool,ExtendedMemory,DBHEAP(LOGBUF,CATALOGCACHE_SZ),UTILITYHEAP(BACKUP,RESTOREBUFFER),PackageCache,LockList,SortHeap,实例,实例相关的命令,创建实例db2icrtdb2icrt-ufenced_user_IDinstance_nameinUNIX删除实例db2idropfinstance_name列出实例db2ilist移植实例db2imigrinstance_name升级实例db2iupdtinstance_name,设置db2环境参数,db2profileregistries操作系统的环境变量db2dbm配置参数db2db配置参数,Db2环境参数控制着db2的操作和功能,Db2profileregistries,可以设置不同的级别Instance/global/user/instancenode/instanceprofile使用db2set命令，可以显示，修改，删除db2setall显示所有设置的db2setlr显示所有可以设置的db2db配置参数,操作系统环境变量,windowsSetparam=valueunixksh和bshExportparam=valueunixcshSetenvparam=value,实例和数据库参数,读取Db2getdatabasemanagerconfigurationDb2getdatabaseconfigurationfordatabase_name设置Db2updatedbmcfgusingparamvalueDb2updatedbcfgfordb_nameusingparamvalue,连接到数据库,服务器端配置设置db2profileregistry的参数DB2COMM,如：Db2setdb2comm=tcpip设置实例级参数Db2updatedbmcfgusingsvcename50000修改该参数，需要重起实例,连接到数据库,客户端可供选择方式之一自动搜索有两种方法：known和search需要server端的管理服务器启动db2adminstart配置管理服务器updateadmincfgusingdiscoversearch配置实例updatedbmcfgusingdiscover_instenable配置数据库updatedbcfgfordb_nameusingdiscover_dbenable,连接到数据库,客户端可供选择方式之二概要文件,连接到数据库,客户端可供选择方式之三手工配置编目节点Catalogtcpipnodenode_nameremoteip地址Server50000编目数据库Catalogdbdb_nameasatnodenode_name,db2命令交互,db2cmd命令行交互模式，可以直接输入Db2命令，操作系统命令前加！命令行模式，可以直接识别操作系统命令，db2命令前加db2执行脚本Db2fmyfile.clp脚本中用-做注释,命令行可执行的命令,命令行选项和获得帮助,命令行选项Db2listcommandoptions获得帮助Db2?Db2?CommandDb2?SqlnnnnDb2?db2nnnn,命令中心,可以在控制中心调用图形化的命令行处理器，功能类似特殊的功能可以得到sql的执行计划可以将sql加到脚本中,控制中心,配置功能，可以显示和修改实例和数据库参数备份、恢复、前滚数据库和表空间管理本地和远程数据库，可以创建/删除、显示、编目远程数据库和去掉编目管理存储。创建、修改、删除表空间。,字符集,Createdatabasedb_nameusingcodesetGBKterritoryzh_CN,模式（schema）,模式是一组数据库对象的集合，他提供了数据库对象的逻辑分类如果不指定模式名，则用当前用户名切换当前模式可以用setcurrentschemaxxx或setcurrentsqlidxxx,系统模式,系统模式：SYSIBM基本的编目信息，不建议直接访问SYSCAT编目信息的只读视图，所有人具有查询权限，推荐的获取编目信息的途径SYSSTAT编目信息可更新视图，可以影响优化器SYSFUN用户定义函数,建表,创建新表：createtabletabname(col1type1notnullprimarykey,col2type2notnull,.)根据已有的表创建新表：A：createtabletab_newliketab_oldB：createtabletab_newasselectcol1,col2fromtab_olddefinitiononly,表空间,三种表空间常规表空间长表空间临时表空间两类表空间DMSSMS默认产生三个表空间：SYSCATSPACE,USERSPACE1,TEMPSPACE1,数据库对象-视图,创建视图：createviewviewnameasselectstatement删除视图：dropviewviewname注：视图唯一能修改的是引用类型列，改变列的范围。其他定义好了都不能修改。当视图基于的基表drop后，视图变为无效。,数据库对象-索引,使用索引的主要原因有两条：确保数据值的惟一性提高SQL查询的性能使用索引时，能够按一种排列好的顺序更迅速地访问数据，从而避免了使用临时存储排序数据这一耗时的任务。创建索引：createuniqueindexidxnameontabname(col.)删除索引：dropindexidxname注：索引是不可更改的，想更改必须删除重新建。索引的使用都是从WHERE中的条件得出的，从谓词是否使用索引可以将谓词分为可索引谓词和不可索引谓词，如下图，我们列出了索引谓词和不可索引谓词。,可索引谓词,数据约束,数据库中数据的完整性或有效性极其重要。确保插入数据库的数据的有效性非常困难，DB2提供了定义某些可并入数据库的基于规则的约束或检查的能力。在DB2中，可使用以下检查来最小化将错误数据插入表中的风险：可检查一行中的一段，看它们是否符合所关联列的数据类型和长度。例如，“Geoff”值不匹配数据类型为INTEGER的列，因而带有该值的行会被拒绝，以这种方式来确保数据库中数据的有效性。若表上定义了主键约束，表中的各行必须在一列或共同构成主键的多个列中具有惟一值。若插入的行中存在与现有键相同的键，则新行将被拒绝。若表上已定义了惟一约束，表中的各行必须遵循此约束，即具有惟一值或构成惟一键的值组合。若已定义了外键约束，表中各行的外键列或多个列必须具有与父表中一行的主键相匹配的值。在某些情况下，若一列或多个列定义为外键的一部分，而这个外键可为空，则空值也是可接受的。若列上已定义了检查约束，各行必须遵循此约束。例如，EMPLOYEE表的SALARY列上的检查约束可能会阻止应用程序或用户插入工资低于0的新员工记录或行。插入表的任何salary值小于0的行都会被拒绝，从而最小化将错误数据插入表中的风险。,检查约束,数据库锁,数据库事务,数据类型,NULL值,尽可能设置为notnull，可以通过default的方式来解决insertnull的情况查询isnull,Identity列,是一个数字型的字段，当新行插入时，db2自动产生一个唯一的数字一个表中只能有一个列被定义有该属性可以定义成always或default的方式Always方式，只能有db2产生，不允许应用提供值Default方式，可以由应用提供值，不能保证唯一性,简单查询,selectdistinctcolumn-listfromtable-listwheregroupbyhavingorderbyase/ease,内连接,外连接表的分类,保留行表左外连接中左边的表。右外连接中右边的表。全外连接中全部的表。替换NULL的表左外连接中右边的表右外连接中左边的表全外连接中全部的表,一个简单的左外连接,一个简单的右外连接,全外连接,子查询,子查询可以由内连接和外连接代替，推荐做法in、exist可以由内连接代替selectprojno,projname,d.deptno,deptnamefromdepartmentdleftouterjoinprojectponp.deptno=d.deptno;selectprojno,projnamefromdepartmentwheredeptnoin(selectdeptnofromproject);notin、notexist可以由外连接代替selectprojno,projname,d.deptno,deptnamefromdepartmentdleftouterjoinprojectponp.deptno=jnameisnull;selectprojno,projnamefromdepartmentwheredeptnonotin(selectdeptnofromproject);,Db2中的函数,内建函数列函数:avg,sum,count,操作函数:+类型转换函数decimal其他用户自定义函数UDF,1、类型转化函数：转化为数字类型的：decimal,double,Integer,smallint,realHex(arg):转化为参数的16进制表示。转化为字符串类型的：char,varcharDigits(arg):返回arg的字符串表示法，arg必须为decimal。转化为日期时间的：date,time,timestamp2、时间日期：year,quarter,month,week,day,hour,minute,seconddayofyear(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,rtrimCoalesce(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,sumCeil(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目录中，可以使用以下命令：BACKUPDATABASEsampleTOd:mybackups要使用其他选项为数据库sample执行完整离线备份，可以使用以下命令：(1)BACKUPDATABASEsample(2)TO/db2backup/dir1,/db2backup/dir2(3)WITH4BUFFERS(4)BUFFER4096(5)PARALLELISM2我们来更仔细观察前面的命令：1.表明要备份的数据库的名称（或别名）。2.指定用于存储备份的位置。3.表明在备份操作期间可以使用多少个内存缓冲区。使用多个缓冲区可以提高性能。4.表明每个缓冲区的大小。5.决定使用多少媒介读/写进程/线程来进行备份。,数据库日志,DB2事务日志对于恢复来说是至关重要的。它们跟踪数据库对象和数据上发生的变化。主日志文件和辅助日志文件主日志文件是在建立第一个数据库连接或者数据库活动时立即分配的。辅助日志文件是在需要时动态分配的。活动日志如果以下两个条件之一得到满足，则一个日志被认为是活动的（active）：它包含关于尚未被提交或回滚的事务的信息。它包含关于已经被提交但是其更改还没有被写（具体化）到数据库磁盘的事务的信息。在线归档日志离线归档日志,数据库日志,1.配置日志空间DB2数据库通过日志文件维护数据的完整性和一致性。DB2数据库的日志空间可通过如下公式计算：日志空间=（主日志文件+辅助日志文件）*日志文件尺寸其中：主日志文件由参数LOGPRIMARY控制，二级日志文件由参数LOGSECOND控制日志文件尺寸由参数LOGFILSIZ控制2.正确的处理日志满根据实际的情况配置以上相关参数。如果出现日志空间满的情况，DB2会将系统挂起。这时，应该增加LOGSECOND参数，而不要尝试db2stopforce或者kill。,启用归档日志,一个数据库的日志记录的类型是由数据库参数LOGARCHMETH1决定的。当LOGARCHMETH1为OFF（默认值）时，归档日志记录被禁用，循环日志记录被启用。为了启用归档日志记录，可以将LOGARCHMETH1设置为以下值中的任何一个值：LOGRETAIN：日志文件将被保留在活动日志目录中USEREXIT：日志的归档和检索是由用户提供的用户出口程序自动执行的，这个出口程序必须由db2uext2调用。这个程序用于将在线归档日志移动到与活动日志目录不同的一个目录中，或者移动到另一个媒介上。当在ROLLFORWARD操作期间需要某些离线归档日志时，这个程序还可以用于将离线归档日志取出到活动日志目录中。在Windows下，db2uext2必须存储在sqllibbin目录中，在UNIX下，db2uext2必须存储在sqllib/adm目录中DISK:directory_name：与USEREXIT使用相同的算法。DB2不调用用户出口程序，而是自动将日志文件从活动日志目录归档到指定的目录TSM:managementclassname：与USEREXIT使用相同的算法。日志被归档到本地TivoliStorageManger(TSM)服务器上。managementclassname参数是可选的。如果没有指定该参数，则使用默认的管理类VENDOR:library_name：与USEREXIT使用相同的算法。日志是使用指定供应商的库来归档的由于向后兼容的原因，数据库配置文件仍然包含参数LOGRETAIN和USEREXIT。从8.2版开始，这两个参数已经被LOGARCHMETH1取代。如果更新USEREXIT或LOGRETAIN参数，那么LOGARCHMETH1将自动被更新，反之亦然。,在线备份,在线备份BACKUPDATABASEsampleONLINETO/dev/rdir1,/dev/rdir2由于在线备份允许用户在执行备份的同时访问数据库，因此这些用户作出的更改很可能不会存储在备份副本中。因此，仅仅凭借在线备份还不足以进行恢复，另外还需要备份操作期间收集到的相应的日志。当在线备份完成时，DB2强制关闭当前活动日志（并将其归档），因此很容易在备份完成时收集当前活动日志。为了将日志也备份到备份副本中，可以使用BACKUPDATABASE命令的INCLUDELOG选项。这样可以确保即使丢失了日志，也仍然可以使用备份镜像中包含的日志来恢复到最小的时间点上。例如，要对sample数据库和日志进行在线备份，并以/dev/rdir1作为目标目录，可以发出：BACKUPDATABASEsampleONLINETO/dev/rdir1INCLUDELOGS,数据库的恢复,使用一个备份文件作为输入，输出是一个新的或已有的数据库。要恢复到已有的数据库，需要SYSADM、SYSCTRL或SYSMAINT权限。要恢复到新的数据库，则需要SYSADM或SYSCTRL权限。,数据库恢复例子,要执行sample数据库的恢复，可以使用以下命令：(1)RESTOREDATABASEsample(2)FROMC:DBBACKUP(3)TAKENAT20060314131259(4)WITHOUTROLLINGFORWARD(5)WITHOUTPROMPTING上面的例子中：1.表明要恢复的数据库镜像的名称。2.指定要从中读取输入备份文件的位置。3.如果该目录中有多个备份镜像，该选项将基于时间戳（备份名称的一部分）标识特定的备份。4.如果一个数据库启用了归档日志记录，那么当该数据库被恢复时，它将自动被置于rollforwardpending状态。这一行告诉DB2不要将该数据库置于rollforwardpending状态。5.当执行RESTORE时，您将看不到任何提示。,包括日志文件的恢复例子,备份镜像中包括日志文件，那么可以使用RESTOREDATABASE命令的LOGTARGET选项恢复日志文件。为了使用C:DBBACKUP目录中的备份镜像恢复SAMPLE数据库，并将日志文件恢复到C:DB2NODE0000SQL00001SQLOGDIR目录，可以发出：RESTOREDATABASEsampleFROMC:DBBACKUPLOGTARGETC:DB2NODE0000SQL00001SQLOGDIR也可以通过使用LOGS关键字只恢复日志文件，而不恢复数据库：RESTOREDATABASEsampleLOGSFROMC:DBBACKUPLOGTARGETC:DB2NODE0000SQL00001SQLOGDIR,Db2move例子,Db2movedbnameexportsndb2inst1-uusernameppasswordDb2movedbnameimportsndb2inst1-uusernameppassword,Db2export,EXPORT实用程序使用一条SQLSELECT语句或XQUERY语句将数据从数据库表提取到一个文件中。数据可以被导出到DEL、IXF或WSF文件中。建议在export中包括MESSAGES子句，以捕获导出过程中遇到的错误、警告和包含有用信息的消息。要想成功地调用EXPORT实用程序，必须拥有SYSADM或DBADM权限，或者拥有EXPORT命令中所访问的表或视图上的CONTROL或SELECT特权。一个简单的导出的例子。下面的命令将SELECT语句的结果导出到一个DEL格式的文件中。消息文件msg.out用于记录有用的信息和遇到的错误或警告：EXPORTTOmyfile.delOFDELMESSAGESmsg.outSELECT,staff.dept,org.locationFROMorg,staffWHEREorg.deptnumb=staff.dept;,Db2import语法,IMPORT实用程序用一个输入文件将数据填充到一个表中，输入文件的文件类型可以是ASC、DEL、IXF或WSF。目标是一个表、一个类型化表（typedtable）或者一个视图。但是，不能导入到系统表、临时表和物化查询表。例子：IMPORTFROMemp.ixfOFIXFMESSAGESmsg.outCREATEINTOemployeeINdatatbspINDEXINindtbsp,Db2import选项,下面显示的IMPORT命令具有五个不同的选项：IMPORTFROMfile_nameOFfile_typeMESSAGESmessage_fileINSERT|INSERT_UPDATE|REPLACE|REPLACE_CREATE|CREATEINTOtarget_table_nameINSERT选项将导入的数据插入表中。目标表必须已经存在。INSERT_UPDATE将数据插入表中，或者更新表中具有匹配主键的行。目标表必须已经存在，并且定义了一个主键。REPLACE选项删除所有已有的数据，并将导入的数据插入到一个已有的目标表中。使用REPLACE_CREATE选项时，如果目标表已经存在，则导入实用程序删除已有的数据，并插入新的数据，就像REPLACE选项那样。如果目标表还没有定义，那么首先创建这个表以及它的相关索引，然后再导入数据。正如您可能想像的那样，输入文件必须是PC/IXF格式的文件，因为那种格式包含对导出表的结构化描述。如果目标表是被一个外键引用的一个父表，那么就不能使用REPLACE_CREATE。CREATE选项首先创建目标表和它的索引，然后将数据导入到新表中。该选项惟一支持的文件格式是PC/IXF。还可以指定新表所在表空间的名称。,Db2Load,LOAD实用程序是用于为表填充数据的另一种方法。该实用程序将格式化的页面直接写入到数据库中。这种机制允许比IMPORT实用程序更有效地移动数据。LOADFROMinput_sourceOFinput_typeMESSAGESmessage_fileINSERT|REPLACE|TERMINATE|RESTARTINTOtarget_tablename,Db2Load,LOAD可以以四种不同的模式执行：INSERT模式将输入数据添加到一个表中，而不更改已有的表数据。REPLACE模式删除表中所有已有的数据，然后用输入数据填充这个表。TERMINATE模式终止装载操作，并回滚到它开始时所在的时间点。一个例外是，如果指定了REPLACE模式，那么表将被删节。RESTART模式用于重新开始之前被中断的装载命令。它将自动从最近的一致点继续。要使用这个模式，可以指定与前一个LOAD命令相同的选项，但是这一次加上RESTART。它使实用程序可以发现在装载处理中生成的所有需要的临时表。因此，千万不要手动删除装载命令所生成的任何临时文件，除非您确信不再需要这些临时文件。一旦装载成功完成，这些临时文件将自动被删除。默认情况下，这些临时文件是在当前的工作目录中创建的。也可以使用TEMPFILESPATH选项指定存放临时文件的目录。,Db2move,db2move是用于在DB2数据库之间移动大量表的一个数据移动工具。这个命令中支持的动作有EXPORT、IMPORT、LOAD和COPY。EXPORT、IMPORT和LOAD这几个动作的行为与前一屏中描述的完全相同。db2move将一组用户表从系统编目表中提取出来，并将每个表以PC/IXF格式导出。然后，PC/IXF文件可以被导入或装载到另一个DB2数据库中。下面是一些例子。这个命令用指定的用户ID和密码以REP