ITPub博客

本文主要介绍了db2数据库在遇到故障及性能问题时运用什么方法去快速诊断定位问题，希望本文内容可以为从事db2的网友提供一些指导和参考 。1、在db2数据库主机遇到重大故障时我们可以通过db2support收集数据库诊断日志数据#在可以连接的时候#db2support . -d sample -c -g -s#不能连接的时候#...

db2监控工具     db2pd （最推荐使用）     snapshot 命令行监控 （受监视器开关控制） snapshot 管理视图     （受实例级监视器开关控制）     db2top     监视器表函数 MON_   （受DB参数 MON_REQ...

i_basedir=/archlog/cvbackup                            # backup base diri_dbnm=SRPDB    KEEP_BACKUP_NUMS=0         &nb...

--TIMESTAMP_FORMAT   to_datedb2 "WITH temp1 (ts1) AS                                      &nbsp...

#--导出模式 MBS 下的所有表 i_dbname=MBDB1i_schema=MBSi_expdir=/db2data1/export i_time=`date +%Y%m%d%H%M`i_schema_len=`echo ${#i_schema}` i_tab_file=/tmp/${i_dbname}_${i_schema}.txtdb2 connect to $i_dbnamei_tab_len=`db2 -x "&...

#--循环导表 i_dbname=testdbset -A I_EXP db2inst1.t1 s1.t1 i_time=`date +%Y%m%d%H%M`i_expdir=/db2/export cd $i_expdirdb2 connect to $i_dbnamefor i_tab in ${I_EXP[*]} do  db2 "export to&nb...

#------------------------------------------------------## function: backup mbdb1 for cvbackup# creator:  fjz# date:     20140812#------------------------------------------------------#DB_SRV_IP="40.7.66.3"srv_ip=`/usr/sbin/ifconfig -a | grep "$DB_SRV_IP" | cut -d...

Sometimes it's useful to have the possibility to suspend a session for a specific time.In other languages like Java, C, Shell, etc. is a sleep command available.But as default there's no sleep command in DB2.However it's very simple to create a stored procedure with exacly this behaviour:db2...

This stored procedure example is performing a runstats on all tables in the database.The two output parameters are the total number of tables on the database and the number of successful runstats.The procedure don't have any condition handlers and will stop immediately after an error occurs.Th...

This stored procedure example returns the number of tables for which is a reorganisation is recommended.The procedure is using the ALLOCATE CURSOR and ASSOCIATE LOCATOR commands to reference tothe output of the REORGCHK_TB_STATS procedure.create procedure check_reorg_tables (in v_schema varcha...

CREATE PROCEDURE bonus_increase (IN p_bonusFactor DECIMAL (3,2), IN p_bonusMaxSumForDept DECIMAL (9,2)  , OUT p_deptsWithoutNewBonuses VARCHAR(255)  , OUT p_countDeptsViewed INTEGER   ,OUT p_countDeptsBonusChanged INTEGER   ,OUT p_errorMsg VARCHAR(255))SPECIFIC ...

#------------------------------------------------------## function: backup for db2# creator:  fjz# date:     20141225#------------------------------------------------------##------------------------------------------------------## init for special db#-----------...

i_date=`date +'%Y%m%d'`i_dbnm=ZZDB2i_inst=db2inst2i_logdir=/db2log2/$i_inst/$i_dbnm/NODE0000/C0000000i_cvbackup=/db2log2i_logbakdir=${i_dbnm}_archlog_${i_date}mkdir -p $i_logdir/$i_logbakdiri_first_active_log=`db2 get db cfg for $i_dbnm | grep -i "first active" | awk '{print $6}'`echo "--...

AIX:chdev -l inet0 -a route='net,-hopcount,0,,,,,,-static,40.1.18.0,40.1.243.254'redhat linux:route add -net 40.1.18.0/24 gw 40.1.243.254 dev eth1echo "route add -net 40.1.18.0/24 gw 40.1.243.254 dev eth1" >> /etc/rc.local suse linux: route add -net 40.1.18.0/24 gw 40.1.243.254 de...

假设 数据库 testdb 只包含表空间tbs1,tbs2,tbs31）t1 时刻db2 backup db testdb tablespace(tbs1) to /data2）t2 时刻db2 backup db testdb tablespace(tbs2,tbs3) to /data则重建数据库：1）db2 restore db testdb rebuild with all tablespaces in database taken at t12）db2 rollfor...

如果最早和最晚日志之间的日志文件丢失，则这个在线备份是无效的。因为在恢复时，必须至少前滚到备份结束的时间点，这个点就是前滚操作结束的最小恢复时间。1）查看恢复历史文件，找出最早日志和最晚日志db2inst1@sles11:~/backup>  db2 list history backup since 201412031946 for testdb...

1）设置循环归档模式# db2 "update db cfg using LOGARCHMETH1 DISK:/home/db2inst1/archivelog";2）连接报错# db2 connect to sampleSQL1116N  A connection to or activation of database "SAMPLE" cannot be madebecause of BACKUP PENDING.  SQLSTATE=570193）离线备份    &nbsp...

--全备份db2 backup db testdb online to /data/backup/testdb compress备份成功。此备份映像的时间戳是：20131218084437--删除表db2 drop table emp1--列出备份中已经删除的表，包含建表语句db2 list history dropped table  all for testdb--使用删除表之前的全备份进行恢复db2 "resto...

rec_his_retentn － 恢复历史记录保留期 配置参数此参数用以指定应保留关于备份的历史信息的天数。如果不需要恢复历史记录文件来跟踪备份、复原以及装入，则可将此参数值设置为一个较小的数。如果此参数的值为 -1，则指示完全数据库备份（以及与数据库备份相关联的任何表空间备份）的条目数对应于num_db_backups 参数指...

-----------------------------------------------------------------------------------------------------------------------------db2 "backup db zzdb1 online to /home/db2inst1/backup compress include logs"db2 "drop table t1"db2 "create table t1 (id int , c2 varchar(200)) "db2 "begindeclare i...