Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2013
    Posts
    80

    Unanswered: Uneven Distribution of Disk activities

    Hi All,
    I have db2 9.7.0.6 running in AIX 6.1 using DPF feature (Database Partition Feature with 8 nodes - 4 nodes distributed to 2 servers).
    I have dedicate each node of the db partition to different filesystem (different disk).

    I realize that out of 8 hdisk, there are 3 particular disks are always at the 100% peak. And these 3 hdisks are belong to

    Code:
    hdisk6         100.0     5888.0     184.0      11776         0
    hdisk4         100.0     56096.0     1753.0      17024     95168
    hdisk12          6.0     1848.0      83.5          0      3696
    hdisk14          4.5     170.0      31.5          0       340
    hdisk9           0.0       0.0       0.0          0         0
    hdisk15          0.0       0.0       0.0          0         0
    hdisk5         100.0     15760.0     492.5      17152     14368
    hdisk10          0.0       0.0       0.0          0         0
    hdisk13          6.0     1214.0      65.5          0      2428
    hdisk8           0.0       0.0       0.0          0         0
    Code:
    hdisk4:
    LV NAME               LPs     PPs     DISTRIBUTION          MOUNT POINT
    pdbvg1lv01            345     345     71..70..70..70..64    /db2/BDWDBP/DATA/N1
    
    hdisk5:
    LV NAME               LPs     PPs     DISTRIBUTION          MOUNT POINT
    pdbvg2lv01            345     345     71..70..70..70..64    /db2/BDWDBP/DATA/N2
    
    hdisk6:
    LV NAME               LPs     PPs     DISTRIBUTION          MOUNT POINT
    pdbvg3lv01            345     345     71..70..70..70..64    /db2/BDWDBP/DATA/N3
    Anyone of you guys encounter this before and what's your action if you were resolving this issue ?

    Thanks.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You will need to post the following for one of the tables that has this problem when accessed:
    • database partition group definition
    • tablespace definitions
    • table and index definitions

    You can get the above via db2look
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2013
    Posts
    80
    Thanks for reply Marcus

    Here the output from db2look

    Code:
    CONNECT TO BDWDBP;
    
    -----------------------------------
    -- DDL Statements for DATABASE PARTITION GROUPS --
    -----------------------------------
    
    CREATE DATABASE PARTITION GROUP "SPGBDW" ON DBPARTITIONNUMS
                    (0);
    
    
    CREATE DATABASE PARTITION GROUP "MPGBDW" ON DBPARTITIONNUMS
                    (1,
                     2,
                     3,
                     4,
                     5,
                     6,
                     7);
    
    
    ------------------------------------
    -- DDL Statements for BUFFERPOOLS --
    ------------------------------------
    
    CREATE BUFFERPOOL "BP32K"  DATABASE PARTITION GROUP "IBMDEFAULTGROUP"  SIZE 200000 PAGESIZE 32768 NUMBLOCKPAGES 150000 BLOCK
    SIZE 32;
    
    
    
    ALTER BUFFERPOOL "BP32K" ADD DATABASE PARTITION GROUP "IBMTEMPGROUP";
    
    ALTER BUFFERPOOL "BP32K" ADD DATABASE PARTITION GROUP "MPGBDW";
    
    ALTER BUFFERPOOL "BP32K" ADD DATABASE PARTITION GROUP "SPGBDW";
    
    CONNECT RESET;
    CONNECT TO BDWDBP;
    
    ------------------------------------
    -- DDL Statements for TABLESPACES --
    ------------------------------------
    
    CREATE TEMPORARY TABLESPACE "TEMP32K_TBS" IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 32768 MANAGED BY DATABASE
             EXTENTSIZE 64
             PREFETCHSIZE AUTOMATIC
             BUFFERPOOL BP32K
             OVERHEAD 4.000000
             TRANSFERRATE 0.800000
             AUTORESIZE YES
             MAXSIZE NONE
             NO FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY OFF;
    
    
    CREATE LARGE TABLESPACE "SYSTOOLSPACE" IN DATABASE PARTITION GROUP IBMCATGROUP
             PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE
             AUTORESIZE YES
             INITIALSIZE 100 M
             MAXSIZE NONE
             EXTENTSIZE 64
             PREFETCHSIZE AUTOMATIC
             BUFFERPOOL IBMDEFAULTBP
             OVERHEAD 4.000000
             TRANSFERRATE 0.800000
             NO FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY ON;
    
    
    CREATE USER TEMPORARY TABLESPACE "SYSTOOLSTMPSPACE" IN DATABASE PARTITION GROUP IBMCATGROUP
             PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE
             EXTENTSIZE 64
             PREFETCHSIZE AUTOMATIC
             BUFFERPOOL IBMDEFAULTBP
             OVERHEAD 4.000000
             TRANSFERRATE 0.800000
             NO FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY OFF;
    
    CREATE LARGE TABLESPACE "SP_MD_TBS" IN DATABASE PARTITION GROUP SPGBDW PAGESIZE 32768 MANAGED BY DATABASE
             EXTENTSIZE 64
             PREFETCHSIZE AUTOMATIC
             BUFFERPOOL BP32K
             OVERHEAD 4.000000
             TRANSFERRATE 0.800000
             NO FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY ON;
    
    CREATE LARGE TABLESPACE "SPTBS" IN DATABASE PARTITION GROUP SPGBDW PAGESIZE 32768 MANAGED BY DATABASE
             EXTENTSIZE 64
             PREFETCHSIZE AUTOMATIC
             BUFFERPOOL BP32K
             OVERHEAD 4.000000
             TRANSFERRATE 0.800000
             NO FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY ON;
    
    CREATE LARGE TABLESPACE "STG32K_TBS" IN DATABASE PARTITION GROUP SPGBDW PAGESIZE 32768 MANAGED BY DATABASE
             EXTENTSIZE 64
             PREFETCHSIZE AUTOMATIC
             BUFFERPOOL BP32K
             OVERHEAD 4.000000
             TRANSFERRATE 0.800000
             NO FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY ON;
    
    CREATE LARGE TABLESPACE "AR_TVR_TBS" IN DATABASE PARTITION GROUP MPGBDW PAGESIZE 32768 MANAGED BY DATABASE
             EXTENTSIZE 64
             PREFETCHSIZE AUTOMATIC
             BUFFERPOOL BP32K
             OVERHEAD 4.000000
             TRANSFERRATE 0.800000
             NO FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY ON;
    
    CREATE LARGE TABLESPACE "ASCV_SMY_TBS" IN DATABASE PARTITION GROUP MPGBDW PAGESIZE 32768 MANAGED BY DATABASE
             EXTENTSIZE 64
             PREFETCHSIZE AUTOMATIC
             BUFFERPOOL BP32K
             OVERHEAD 4.000000
             TRANSFERRATE 0.800000
             NO FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY ON;
    
    CREATE LARGE TABLESPACE "ARC_TBS" IN DATABASE PARTITION GROUP MPGBDW PAGESIZE 32768 MANAGED BY DATABASE
             EXTENTSIZE 64
             PREFETCHSIZE AUTOMATIC
             BUFFERPOOL BP32K
             OVERHEAD 4.000000
             TRANSFERRATE 0.800000
             NO FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY ON;
    
    CREATE LARGE TABLESPACE "IDX_TBS" IN DATABASE PARTITION GROUP MPGBDW PAGESIZE 32768 MANAGED BY DATABASE
             EXTENTSIZE 64
             PREFETCHSIZE AUTOMATIC
             BUFFERPOOL BP32K
             OVERHEAD 4.000000
             TRANSFERRATE 0.800000
             NO FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY ON;
    
    CREATE LARGE TABLESPACE "AR_TVR_LARGE_1_TBS" IN DATABASE PARTITION GROUP MPGBDW PAGESIZE 32768 MANAGED BY DATABASE
             EXTENTSIZE 64
             PREFETCHSIZE AUTOMATIC
             BUFFERPOOL BP32K
             OVERHEAD 4.000000
             TRANSFERRATE 0.800000
             NO FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY ON;
    
    
    CREATE LARGE TABLESPACE "AR_TVR_LARGE_2_TBS" IN DATABASE PARTITION GROUP MPGBDW PAGESIZE 32768 MANAGED BY DATABASE
             EXTENTSIZE 64
             PREFETCHSIZE AUTOMATIC
             BUFFERPOOL BP32K
             OVERHEAD 4.000000
             TRANSFERRATE 0.800000
             NO FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY ON;
    
    
    -- Mimic tablespace
    
    ALTER TABLESPACE SYSCATSPACE
          PREFETCHSIZE AUTOMATIC
          OVERHEAD 4.000000
          NO FILE SYSTEM CACHING
          AUTORESIZE YES
          TRANSFERRATE 0.100000;
    
    
    COMMIT WORK;
    
    CONNECT RESET;
    
    TERMINATE;

  4. #4
    Join Date
    Aug 2013
    Posts
    80
    Code:
     
    
      For one of the table in the multi-partition tablespace
    
    
    One of the table that use multi-partition 
    
    --
    -- Using database BDWDBP
    -- Using userid PRDINST1
    -- Database Manager Version DB2/AIX64 Version 9.7.6
    -- Database Codepage 819
    -- Database Collating Sequence is UNIQUE
    --
    --
    --
    --********************************************
    TABLE AR_X_LO
    --********************************************
    --
               CREATOR     DSSBDW
               CARD        49655858
               NPAGES      66108
               FPAGES      66108
               OVERFLOW    6748
               ACTIVE_BLOCKS 0
    --
               COLUMNS
    --
                         NAME    AR_ID
                         COLNO    0
                         TYPE      INTEGER
                         LENGTH    4
                         NULLS    N
                         COLCARD   17543904
                         NUMNULLS   0
                         SUB_COUNT   -1
                         SUB_DELIM_LENGTH   -1
                         AVGCOLLENCHAR   0
                         NFRQ   -1
                         NQUN   -1
                         LOW2KEY 1024564
                         HIGH2KEY 39651190
                         AVGCOLLEN 4
    
                         NAME    LOCN_ID
                         COLNO    1
                         TYPE      INTEGER
                         LENGTH    4
                         NULLS    N
                         COLCARD   2181190
                         NUMNULLS   0
                         SUB_COUNT   -1
                         SUB_DELIM_LENGTH   -1
                         AVGCOLLENCHAR   0
                         NFRQ   -1
                         NQUN   -1
                         LOW2KEY 5149701
                         HIGH2KEY 144590098
                         AVGCOLLEN 4
    --
                         NAME    AR_X_LO_TP_ID
                         COLNO    2
                         TYPE      CHAR
                         LENGTH    6
                         NULLS    N
                         COLCARD   10
                         NUMNULLS   0
                         SUB_COUNT   -1
                         SUB_DELIM_LENGTH   -1
                         AVGCOLLENCHAR   0
                         NFRQ   -1
                         NQUN   -1
                         LOW2KEY '129002'
                         HIGH2KEY '129016'
                         AVGCOLLEN 6
    
                         NAME    EFF_DT
                         COLNO    3
                         TYPE      DATE
                         LENGTH    4
                         NULLS    N
                         COLCARD   19107
                         NUMNULLS   0
                         SUB_COUNT   -1
                         SUB_DELIM_LENGTH   -1
                         AVGCOLLENCHAR   0
                         NFRQ   -1
                         NQUN   -1
                         LOW2KEY '2004-05-23'
                         HIGH2KEY '2013-12-17'
                         AVGCOLLEN 4
    --
                         NAME    END_DT
                         COLNO    4
                         TYPE      DATE
                         LENGTH    4
                         NULLS    Y
                         COLCARD   4093
                         NUMNULLS   2191553
                         SUB_COUNT   -1
                         SUB_DELIM_LENGTH   -1
                         AVGCOLLENCHAR   0
                         NFRQ   -1
                         NQUN   -1
                         LOW2KEY '1998-06-09'
                         HIGH2KEY '2013-11-30'
                         AVGCOLLEN 5
    
                         NAME    REF_NO
                         COLNO    5
                         TYPE      VARCHAR
                         LENGTH    50
                         NULLS    Y
                         COLCARD   23105
                         NUMNULLS   0
                         SUB_COUNT   -1
                         SUB_DELIM_LENGTH   -1
                         AVGCOLLENCHAR   0
                         NFRQ   -1
                         NQUN   -1
                         LOW2KEY 'DSAMM06.DAT13293513'
                         HIGH2KEY 'TISTM01.DAT16769513'
                         AVGCOLLEN 24
    --
                         NAME    ROW_NO
                         COLNO    6
                         TYPE      INTEGER
                         LENGTH    4
                         NULLS    Y
                         COLCARD   523664
                         NUMNULLS   0
                         SUB_COUNT   -1
                         SUB_DELIM_LENGTH   -1
                         AVGCOLLENCHAR   0
                         NFRQ   -1
                         NQUN   -1
                         LOW2KEY 2
                         HIGH2KEY 7159626
                         AVGCOLLEN 5
    
                         NAME    SRC_STM_ID
                         COLNO    7
                         TYPE      CHAR
                         LENGTH    6
                         NULLS    Y
                         COLCARD   5
                         NUMNULLS   0
                         SUB_COUNT   -1
                         SUB_DELIM_LENGTH   -1
                         AVGCOLLENCHAR   0
                         NFRQ   -1
                         NQUN   -1
                         LOW2KEY 'ALZ   '
                         HIGH2KEY 'SPS   '
                         AVGCOLLEN 7
    --
                         NAME    PPN_TM
                         COLNO    8
                         TYPE      TIMESTMP
                         LENGTH    10
                         NULLS    Y
                         COLCARD   72600
                         NUMNULLS   0
                         SUB_COUNT   -1
                         SUB_DELIM_LENGTH   -1
                         AVGCOLLENCHAR   0
                         NFRQ   -1
                         NQUN   -1
                         LOW2KEY '2004-05-23-12.34.32.000000'
                         HIGH2KEY '2013-11-29-10.31.19.478896'
                         AVGCOLLEN 11
    
                         NAME    BK_CODE
                         COLNO    9
                         TYPE      VARCHAR
                         LENGTH    3
                         NULLS    Y
                         COLCARD   4
                         NUMNULLS   0
                         SUB_COUNT   -1
                         SUB_DELIM_LENGTH   -1
                         AVGCOLLENCHAR   0
                         NFRQ   -1
                         NQUN   -1
                         LOW2KEY '044'
                         HIGH2KEY '060'
                         AVGCOLLEN 8
    --
               COLUMN DISTRIBUTION
    --
    
    
    --
               INDICES
    --
                         INDEX_NAME     AR_X_LO_ALZ
                         INDEX_CREATOR  DSSBDW
                         NLEAF          34748
                         NLEVELS        3
                         FULLKEYCARD    37016112
                         FIRSTKEYCARD   17543904
                         FIRST2KEYCARD  37016112
                         FIRST3KEYCARD  37016112
                         FIRST4KEYCARD  -1
                         SEQUENTIAL_PAGES 34741
                         DENSITY        99
                         CLUSTERRATIO   33
                         CLUSTERFACTOR   -1.000000
                         UNIQUERULE   D
                         COLCOUNT   3
                         AVERAGE_SEQUENCE_GAP   0.000000
                         AVERAGE_SEQUENCE_FETCH_GAP   -1.000000
                         AVERAGE_SEQUENCE_PAGES   4963.000000
                         AVERAGE_SEQUENCE_FETCH_PAGES  -1.000000
                         AVERAGE_RANDOM_PAGES   0.000000
                         AVERAGE_RANDOM_FETCH_PAGES   -1.000000
                        NUMRIDS   49655858
                         NUMRIDS_DELETED   0
                         NUM_EMPTY_LEAFS   0
                         INDEX_COLUMNS
                                   AR_ID
                                   AR_X_LO_TP_ID
                                   SRC_STM_ID
    --
                         INDEX_NAME     IDX_AR_X_LO
                         INDEX_CREATOR  DSSBDW
                         NLEAF          36491
                         NLEVELS        3
                         FULLKEYCARD    42113596
                         FIRSTKEYCARD   17543904
                         FIRST2KEYCARD  42113596
                         FIRST3KEYCARD  42113596
                         FIRST4KEYCARD  -1
                         SEQUENTIAL_PAGES 36407
                         DENSITY        87
                         CLUSTERRATIO   33
                         CLUSTERFACTOR   -1.000000
                         UNIQUERULE   D
                         COLCOUNT   3
                         AVERAGE_SEQUENCE_GAP   0.000000
                         AVERAGE_SEQUENCE_FETCH_GAP   -1.000000
                         AVERAGE_SEQUENCE_PAGES   5201.000000
                         AVERAGE_SEQUENCE_FETCH_PAGES  -1.000000
                         AVERAGE_RANDOM_PAGES   0.000000
                         AVERAGE_RANDOM_FETCH_PAGES   -1.000000
                         NUMRIDS   49655858
                         NUMRIDS_DELETED   0
                         NUM_EMPTY_LEAFS   0
                         INDEX_COLUMNS
                                  AR_ID
                                   LOCN_ID
                                   AR_X_LO_TP_ID

  5. #5
    Join Date
    Aug 2013
    Posts
    80
    Hi,
    Sorry I found out that basically all partitions are I/O busy all the time including 2nd node.

    Any command that able to check what db or os process that causing high I/O ?

    Thanks

  6. #6
    Join Date
    Nov 2010
    Posts
    99
    try db2top

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •