php扩展db2log,A DB2 Performance Tuning Roadmap --LOG INTRODUCTION_PHP教程

A DB2 Performance Tuning Roadmap --LOG INTRODUCTION本文的内容是在整理报表时，对DB2 STAT LOG AVITIVITY部分的一个补充，主要介绍了DB2 LOG作用，overhead，涉及的部件以及部件之间的工作机制如何利用DB2 log，现有的IBM 提供的 DB2 Log utilities。最后对DB2 log的逻辑给出一个感性的认识。1 LOG OVERVIEW DB2 LOG FUNDAMENTALS LOG AIM:

WHY NEED LOGGING

IN PERFECT WORLD

OVERVIEW OF LOG WORK MECHANISM

2 UNDERSTANDING OF LOG LOG IMPLEMENTATION 1 LOG RBA

2 WHEN DATA SHARING INVOLED-LRSN

3 LOG PAGE DATA SETS FACTORS OF ACTIVE LOG DATA SETS:

BSDS – Boot Strap Data Set

SYSLGRNX-DLD

FACTORS OF ARCHIVE LOG DATASETS

LOG STRUCTURE WHAT IS BEING LOGGED? INFORMATION OF UR

PAGE SET INFORMATION

RECOVERY INFOMATION FOR

SYSTEM CHECKPOINTS

UNDO/REDO LOG EXMPLAE

COMPENSATION OF LOG EXMPLE

RECOVERY INFORMATION EXAMPLE

SYSTEM CHECKPOINTS DETAIL

WHEN IS LOGGING

LOG USAGE Start DB2

Recovery of objects

REPLICATION

SOX Compliance

HEALTH CHECKS

LOG UTILITES,WORKING WITH THE LOG DSN1LOGP

DSNJU004、DSNJU003 DSNJU004

DSNJU003 (a.k.a Change Log Inventory)

DSNJLOGF

RECOVER BSDS #RECOVER BSDS

LOG RELATED DATASET LOG PAGE FORMAT

1 LOG OVERVIEW

DB2 LOG FUNDAMENTALSLOG AIM:

The DB2 log has two main functions: to reapply or back out units of recovery, and to rebuild DB2 back to a consistent state in the event of a failureWHY NEED LOGGINGoverhead

it costs in ters of performance,dasd,dministration,cleanup

it's assurance-just in case of an accidentIN PERFECT WORLDno need to rollback

no need to recover

no program errors

no hardware errors

no power failtures

no hurricances,terror attacks,fraud....

Let’s get the MOST out of the LOG since it’s hereOVERVIEW OF LOG WORK MECHANISM

USAGE OF EACH COMPONENTSActive logsWhere DB2 puts the current log information

Log BuffersWhere log information is held BEFORE externalisation to DASD

Bootstrap DatasetsThese hold information about the current active and archive log datasets

Archive logsThese are copies of ‘old’ active log datasets

SYSLGRNXDB2 keeps track of WHEN objects are (possibly) being updated

2 UNDERSTANDING OF LOG

LOG IMPLEMENTATION1 LOG RBA

BYTE ADDRESSABLE(RBA=RELATIVE BYTE ADDRESS)RBA START=0X00000000000RBA END=0XFFFFFFFFFFFRBA UNIQUELY DEFINE A LOG RECORD2 WHEN DATA SHARING INVOLED-LRSN

LRSN=LOG SEQUENCE RANGE NUMBER,WHICH IS A MODIFIED TIMESTAMP BASE ON SYSPLEX STORCE CLOCK3 LOG PAGE DATA SETS

LOG OUTPUTBUFFERACTIVE LOGARCHIVE LOG

WHEN DB2 GO TO THE LAST ACTIVE DATA SETS ,WRAP ROUND TO FIRST ACTIVE LOG DATA SETSFACTORS OF ACTIVE LOG DATA SETS:ACTIVE LOG DATA SETS NUMBER

ACTIVE LOG DATA SETS SIZE

SINGLE OR DUAL ACTIVE LOG

PERFORMANCE CONSIDERATION:FAST DASD

CONSIDER STRIPPING

DB2 ZPARM OUTBUFFER+ LOGAPSTGBSDS – Boot Strap Data Set

? Name: catalog.BSDS01 and catalog.BSDS02? Must be two identical datasets? Contains highest RBA logged? Contains active log description (begin – end RBA and status)? Contains archive log description? Dataset name? Volume name? RBA range? Contains other necessary system information? System checkpoint history, BP-description, CCSID info etcSYSLGRNX-DLD

? Table in Directory so no user access available? Contains the RBA when any tablespace orpartition is opened or closed for update? Note this information is also in DB2 log? SYSLGRNGX is used by DB2 to speed uprecovery by limiting the amount of log data which needs to be scanned? MODIFY utility removes this information along with SYSCOPY rowsSYSLGRNGX is key to speeding up log processing during recovery Contains RBAs when tablespaces are open for update. Log ranges out ranges cannot contain updates for this tablespace and therefore we needed during the recovery process.SYSLGRNGX is also updated by running the Modify Recovery utilityFACTORS OF ARCHIVE LOG DATASETSALWAYS PRODUCE TWO FILES FOR ONE ACTIVE LOGSIT IS AN ASYNCHRONOUS PROCESSVCAT.ARCHLOGN.BNNNNNNCONTAINDS THE BSDS INFORMATION BEFORE ARCHIVE IS WRITTEN FIRST LOG BEING ARCHIVED,IS STILL KNOWN AS ACTIVE LOG IN BSDSVCAT.ARCHIVEN.ANNNNNNCONTAINS THE ACTUAL LOG FOR LOG RBA RANGE OF THAN LOG DATA SETSUPDATES BSDS WHEN COMPLETE

DB2 ZPARMS ARCHPFX1+ARCHPFX2+TSTAMP=YES|NO|EXTVCAT.ARCHIVEN.DXXXXX.TXXXXX.BNNNNNNNN

WHEN ARCHIVE:ACTIVE LOG FULLTRUNCATED:ARCHIVE LOG COMAMND OR ERROR

LOG STRUCTUREWHAT IS BEING LOGGED?INFORMATION OF URBEGIN/END UR INFORMATION

UNDO/REDO STATEMENTS and compensation log rec

commit rollback processingPAGE SET INFORMATIONOPEN/CLOSE PAGE SET

PENDING STATUS INFORMATION(COPY,CHECK,REORG)

START/STOP INFORMATION(include which mode ut)

DBD INFORECOVERY INFOMATION FORINVOLED TABLES:SYSIBM.SYSUTILX SYSIBM.DBD01 SYSIBM.SYSCOPYSYSTEM CHECKPOINTSUNDO/REDO LOG EXMPLAE

UPDATE TSET COL='BBB'WHERE ID=1LRH004A002F 06000001 0E800006 CEA48CC9 0006CEA4 8D590526 0006CEA4 8D59C7C0AF504B23 0000*LG** 80010C00 02000000 02000006 CEA4049E 2B02 0000 00120101 00030900 000600C2 C2C200C1 C1C1

C2C2C2='BBB'C1C1C1='AAA'URID(0006CEA48CC9)RBA OF UR STARTED IN LOG010C=DBID0002==PAGE SET OBID000000 02=PAGE IDCOMPENSATION OF LOG EXMPLE

In case of the example the current value is BBB and after the update it should become AAA, but for some reason a ROLLBACK occurs, in thatcase DB2 needs to undo this update. This undo by itself is also being logged. These log records are called compensation log records.SUBTYPE(UPDATE IN-PLACE IN A DATA PAGE) CLR(YES)*LG** 80010C00 02000000 02000006 CEA550BF AB00 0000 00120101 00030900 000600C2 C2C200C1 C1C1

CLR(YES) which indicates it is a compensation log recordRECOVERY INFORMATION EXAMPLE

DB2 writes “syscopy” information for certain system tables to the log instead of writing it to syscopy. The reason is simple. In case of adisaster recovery, we need to recover the system in precise steps.Meaning that certain system tables have to be recovered before we can recover sysibm.syscopy. Therefore the backup information of those objects is written to the log.SYSTEM CHECKPOINTS DETAIL

SYSTEM CHECKPOINT (snapshot of activity on system)WHAT IS IN SYSTEM CHECKPOINTEntry per active thread (amongst other status inflight/in commit etc)

Entry per open page set (including exception status)

Page externalization

How often should one be taken ?Every 3-5 minutes (many sites are at 15-20 minutes)

CHKFREQ : V9 : # LOG records or # secondsV10: Minimum of # LOG records and # seconds

Important for (re)start up performance? Start up ALWAYS from LAST CHECKPOINTWHEN IS LOGGING

As the work is being done? In Log output buffer (OUTBUFF)? Regularly flushed to Active Log DatasetSequentially first active copy1 then active copy2 (DB2 V10 应该是双写)

? At Commit? At Rollback? Log Write threshold (WRTHRSH 20 pages without commit)? Archive log command? System Checkpoint? Log Write Ahead Force

LOG USAGEStart DB2

4 phases :Log initializationRead/compare BSDSFind current active log dataset and end of log

Current status rebuildRead last system checpoint

Forward log recoveryDo all work for INCOMMIT and INDOUBT threads

Backward log recoveryDo all work for INABORT and INFLIGHT threads

ALWAYS start from LAST SYSTEM CHECKPOINTAmount of work varies (ab)normal shutdowNRecovery of objects

DB2 新增了一个参数BACKOUT(YES),这个参数应该不是太常用，更多的情况应该是使用imgcopy+log的方式去追，即point-in-time,这里不进行介绍。REPLICATION

现在IDC内相当火的一个topic,它是实现双活或是多活的基础，平台版本的DBMS也有实现，比如mysql的mater-slave方式，即mysql本身就支持，不需要新增软件，但是是单线程进行处理，为此很多vendors提供了增强版的版本使slave上支持并发。zos 上使用QREP+MQ实现,支持异构数据库，延迟可以控制在分钟以内。RPT=2MIN，RTO=2HOUR,SOX Compliance

这个更多是的是内审，外审的用途。相对来说使用审计LOG的方式，成本还是比较高的，有alternative software。HEALTH CHECKS

BEST PRACTICE，根据系统现在的运行情况，查看时候ACTIVE LOG,ACHIVE LOG是否运行异常。

LOG UTILITES,WORKING WITH THE LOG

多少一句，这个应该是目前平台比较欠缺的，可能目前也存在，只是我孤陋寡闻了。DSN1LOGP

? Formats DB2 log in a readable output? Detailed or summarized (SUMMARY(YES/NO/ONLY)? Include page set status (DATAONLY(YES/NO)? Include SYSCOPY info (SYSCOPY(YES/NO)? Limit range (from – to)? Limit scope (e.g. URID,DBID,)? Cannot read current active log? Cannot read compressed logs by DFSMSDSNJU004、DSNJU003DSNJU004

Will list certain parts of the BSDS information? Log data set name(s), log ranges, volume(s) etc? Active log status? Conditional restart history? System checkpoint history? Backup system history? Archive log history? CCSID information? Does not show Buffer pool configuration? Be careful time values are in GMT except LTIME columnDSNJU003 (a.k.a Change Log Inventory)

? Only runs when DB2 is down? Allows to change BSDS content, USE WITH CARE!? Add/Delete active and archive Logs? Add/Delete system checkpoints? Create conditional restart record (CRESTART)? STARTRBA? ENDRBA? COLD START ? STARTRBA=ENDRBA? Causes most of the time DATA LOSSDSNJLOGF

DSNJLOGF? Pre-formats new active log dataset? Avoids delay at first use !!RECOVER BSDS#RECOVER BSDS

? MUST have TWO identical BSDS to start up? What if one BSDS is broken ?? DSNJ126I I/O ERROR FORCED SINGLE BSDS? Do NOT stop DB2? Issue command –RECOVER BSDS ? automate this!? What if one BSDS is broken at start up?? DB2 does not start? IDCAMS rename/define, REPRO

LOG RELATED DATASET

LOG PAGE FORMAT

对LOG PAGE 有一个感性认识，LOG 的逻辑结构复杂了，后续如果有需要在补充上吧。1 BYTE=8BITS1 NIBBLE=4BITSLOG PAGE SIZE=4KBLOG PAGE AVAIABLE SPACE=4075LOG PAGE CONTROL INTERVALSIZE: LAST 21/X'15' BYTE OF PAGEUSAGE:CI DESCRIBE THE LOG PAGESTART RAB OF THE PAGELOG RBA DO NOT HAVE RBA-----IT'S START RBA of the page plus offset into page of the record1 log rec maybe span may log pages

本文所有的内容均整理自互联网，仅供参考学习，如有涉及版权问题，请自行删除本文，谢谢。

http://www.bkjia.com/PHPjc/1097746.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/1097746.htmlTechArticleA DB2 Performance Tuning Roadmap --LOG INTRODUCTION 本文的内容是在整理报表时，对DB2 STAT LOG AVITIVITY部分的一个补充，主要介绍了DB2 LOG作用，overhead，...