程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> 網頁編程 >> PHP編程 >> 關於PHP編程 >> A DB2 Performance Tuning Roadmap --LOG INTRODUCTION

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 FUNDAMENTALS

LOG 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 failure

WHY NEED LOGGING
  1. overhead
  2. it costs in ters of performance,dasd,dministration,cleanup
  3. it's assurance-just in case of an accidentIN PERFECT WORLD
    1. no need to rollback
    2. no need to recover
    3. no program errors
    4. no hardware errors
    5. no power failtures
    6. no hurricances,terror attacks,fraud....

Let’s get the MOST out of the LOG since it’s here

OVERVIEW OF LOG WORK MECHANISM


USAGE OF EACH COMPONENTS

  1. Active logs
    Where DB2 puts the current log information
  2. Log Buffers
    Where log information is held BEFORE externalisation to DASD
  3. Bootstrap Datasets
    These hold information about the current active and archive log datasets
  4. Archive logs
    These are copies of ‘old’ active log datasets
  5. SYSLGRNX
    DB2 keeps track of WHEN objects are (possibly) being updated

2 UNDERSTANDING OF LOG

LOG IMPLEMENTATION

1 LOG RBA

BYTE ADDRESSABLE(RBA=RELATIVE BYTE ADDRESS)
RBA START=0X00000000000
RBA END=0XFFFFFFFFFFF
RBA UNIQUELY DEFINE A LOG RECORD

2 WHEN DATA SHARING INVOLED-LRSN


LRSN=LOG SEQUENCE RANGE NUMBER,WHICH IS A MODIFIED TIMESTAMP BASE ON SYSPLEX STORCE CLOCK

3 LOG PAGE DATA SETS

LOG OUTPUTBUFFER
ACTIVE LOG
ARCHIVE LOG


WHEN DB2 GO TO THE LAST ACTIVE DATA SETS ,WRAP ROUND TO FIRST ACTIVE LOG DATA SETS

FACTORS OF ACTIVE LOG DATA SETS:
  1. ACTIVE LOG DATA SETS NUMBER
  2. ACTIVE LOG DATA SETS SIZE
  3. SINGLE OR DUAL ACTIVE LOG
  4. PERFORMANCE CONSIDERATION:
    1. FAST DASD
    2. CONSIDER STRIPPING
    3. DB2 ZPARM OUTBUFFER+ LOGAPSTG
BSDS – 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 etc

SYSLGRNX-DLD

? Table in Directory so no user access available
? Contains the RBA when any tablespace or
partition is opened or closed for update
? Note this information is also in DB2 log
? SYSLGRNGX is used by DB2 to speed up
recovery by limiting the amount of log data which needs to be scanned
? MODIFY utility removes this information along with SYSCOPY rows

SYSLGRNGX 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 utility 
FACTORS OF ARCHIVE LOG DATASETS
  1. ALWAYS PRODUCE TWO FILES FOR ONE ACTIVE LOGS
    IT IS AN ASYNCHRONOUS PROCESS
    VCAT.ARCHLOGN.BNNNNNN
    CONTAINDS THE BSDS INFORMATION BEFORE ARCHIVE IS WRITTEN FIRST LOG BEING ARCHIVED,IS STILL KNOWN AS ACTIVE LOG IN BSDS 
    VCAT.ARCHIVEN.ANNNNNN
    CONTAINS THE ACTUAL LOG FOR LOG RBA RANGE OF THAN LOG DATA SETSUPDATES BSDS WHEN COMPLETE 
  2. DB2 ZPARMS ARCHPFX1+ARCHPFX2+TSTAMP=YES|NO|EXT
    VCAT.ARCHIVEN.DXXXXX.TXXXXX.BNNNNNNNN
  3. WHEN ARCHIVE:
    ACTIVE LOG FULL
    TRUNCATED:ARCHIVE LOG COMAMND OR ERROR

LOG STRUCTURE

WHAT IS BEING LOGGED?INFORMATION OF UR
  1. BEGIN/END UR INFORMATION
  2. UNDO/REDO STATEMENTS and compensation log rec
  3. commit rollback processing

PAGE SET INFORMATION
  1. OPEN/CLOSE PAGE SET
  2. PENDING STATUS INFORMATION(COPY,CHECK,REORG)
  3. START/STOP INFORMATION(include which mode ut)
  4. DBD INFORECOVERY INFOMATION FORINVOLED TABLES:
    SYSIBM.SYSUTILX SYSIBM.DBD01 SYSIBM.SYSCOPY 
    SYSTEM CHECKPOINTSUNDO/REDO LOG EXMPLAE

UPDATE T
SET COL='BBB'
WHERE ID=1
LRH004A002F 06000001 0E800006 CEA48CC9 0006CEA4 8D590526 0006CEA4 8D59C7C0
AF504B23 0000

*LG** 80010C00 02000000 02000006 CEA4049E 2B02 0000 00120101 00030900 000600C2 C2C200C1 C1C1 

C2C2C2='BBB'
C1C1C1='AAA'
URID(0006CEA48CC9)RBA OF UR STARTED IN LOG
010C=DBID
0002==PAGE SET OBID
000000 02=PAGE ID

COMPENSATION 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 that
case 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 record

RECOVERY 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 a
disaster 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)

  1. WHAT IS IN SYSTEM CHECKPOINT
    1. Entry per active thread (amongst other status inflight/in commit etc)
    2. Entry per open page set (including exception status)
    3. Page externalization
  2. How often should one be taken ?
    1. Every 3-5 minutes (many sites are at 15-20 minutes)
    2. CHKFREQ : V9 : # LOG records or # seconds
       V10: Minimum of # LOG records and # seconds 
  3. Important for (re)start up performance
    ? Start up ALWAYS from LAST CHECKPOINT
WHEN IS LOGGING

As the work is being done
? In Log output buffer (OUTBUFF)
? Regularly flushed to Active Log Dataset
Sequentially 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 USAGE

Start DB2

4 phases :

  1. Log initialization
    Read/compare BSDS
    Find current active log dataset and end of log
  2. Current status rebuild
    Read last system checpoint
  3. Forward log recovery
    Do all work for INCOMMIT and INDOUBT threads
  4. Backward log recovery
    Do all work for INABORT and INFLIGHT threads
  5. ALWAYS start from LAST SYSTEM CHECKPOINT
    Amount of work varies (ab)normal shutdowN
Recovery 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 DFSMS

DSNJU004、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 column

DSNJU003 (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 LOSS

DSNJLOGF

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=8BITS
1 NIBBLE=4BITS
LOG PAGE SIZE=4KB
LOG PAGE AVAIABLE SPACE=4075
LOG PAGE CONTROL INTERVAL
SIZE: LAST 21/X'15' BYTE OF PAGE
USAGE:CI DESCRIBE THE LOG PAGE
START RAB OF THE PAGE
LOG RBA DO NOT HAVE RBA-----IT'S START RBA of the page plus offset into page of the record
1 log rec maybe span may log pages
本文所有的內容均整理自互聯網,僅供參考學習,如有涉及版權問題,請自行刪除本文,謝謝。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved