|
Problem IC62048
|
Status: Closed |
INCORRECT OUTPUT OF DIO_ON IN DB2TOP TABLESPACES MONITORING |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The output of Dio_On in db2top Tablespaces monitoring screen is
not correct. And the name is also misleading. Changing the name
to FSC_On, which is more clearer to show if the File System
Caching is ON or OFF.
Example :
1) Create 3 tablespaces.
CREATE LARGE TABLESPACE DEMO1 IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY DATABASE
USING (FILE '/home/skharche/D344' 7376)
EXTENTSIZE 8
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 12.670000
TRANSFERRATE 0.180000
AUTORESIZE YES
MAXSIZE NONE
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
CREATE LARGE TABLESPACE DEMO2 IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY DATABASE
USING (FILE '/home/skharche/D345' 7376)
EXTENTSIZE 8
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 12.670000
TRANSFERRATE 0.180000
AUTORESIZE YES
MAXSIZE NONE
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
CREATE LARGE TABLESPACE DEMO3 IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY DATABASE
USING (FILE '/home/skharche/D346' 7376)
EXTENTSIZE 8
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 12.670000
TRANSFERRATE 0.180000
AUTORESIZE YES
MAXSIZE NONE
DROPPED TABLE RECOVERY ON;
=======================================
i.e.
demo1 - NO FILE SYSTEM CACHING
demo2 - FILE SYSTEM CACHING
demo3 - default - NO FILE SYSTEM CACHING ( on JFS2 filesystem)
=======================================
From "db2pd"
Address Id Type Content PageSz ExtentSz Auto
Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg
Name
0x0700000042DB9F00 4 DMS Large 8192 8 Yes 8
1 1 Off 1 0 7 DEMO1
0x0700000042DBCB00 6 DMS Large 8192 8 Yes 8
1 1 On 1 0 7 DEMO2
0x0700000032DAE400 9 DMS Large 8192 8 Yes 8
1 1 Off 1 0 7 DEMO3
===================================================
From "db2 get snapshot for tablespaces"
DEMO1: File system caching = No
DEMO2: File system caching = Yes
DEMO3: File system caching = No
===================================================
From "db2top -d sample -b t"
demo1 -> DIO_ON -> YES
demo2 -> DIO_ON -> NO
demo3 -> DIO_ON -> NO
i.e.
Time;Tablespace_Name;Delta_l_reads/s;Delta_p_reads/s;Hit_Ratio%;
Async_Reads%;Pages_Aread;Delta_Writes/s;Delta_a_reads/s;Delta_a_
writes/s;Direct_writes/s;Data_writes/s;Index_writes/s;Avg_Reads_
Dir_Reqs;Avg_RdTime;Avg_WrTime;Tbsp_Type;# of_DBP;Avg_PrefSize;S
pace_Used;Total_Size;#
of_Extents;High_WaterMark;%_Full;Data_Skew;Tbspce_Status;Tbspce_
Type;Dio_On;Auto_Stor;Auto_Size;Failed_Re
size;Last_Resize;File_Closed/s;Victim_Buffers/s;Unread_PrefPages
/s;XDA_l_reads/s;XDA_p_reads/s;XDA_writes/s;Page_Size;Extent_Siz
e;Prefetch_Size;Minimum_Recovery_Time;Recent_IOs[0]
02:11:30;DEMO1;0;0;0.00%;0.00%;0;0;0;0;0;0;0;0;0.00;0.00;;1;0;0;
0;922;196608;0%;0%;Normal;DMS;Yes;No;Yes;No;-;0;0;0;0;0;0;8K;655
36;65536;-;0
02:11:30;DEMO2;0;0;0.00%;0.00%;0;0;0;0;0;0;0;0;0.00;0.00;;1;0;0;
0;922;196608;0%;0%;Normal;DMS;No;No;Yes;No;-;0;0;0;0;0;0;8K;6553
6;65536;-;0
02:11:30;DEMO3;0;0;0.00%;0.00%;0;0;0;0;0;0;0;0;0.00;0.00;;1;0;0;
0;922;196608;0%;0%;Normal;DMS;No;No;Yes;No;-;0;0;0;0;0;0;8K;6553
6;65536;-;0
Notice here, the output of Dio_On in db2top for
DEMO3 Tablespaces is incorrect.
2) Consider a case of tablespace - DEMO1 - NO FILE SYSTEM
CACHING.
On AIX with Concurrent Journal File System (JFS2),
DB2 should be using CIO for tablespace when NO FILE SYSTEM
CACHING is specified.
In db2top output for column DIO_ON we see it as YES
This shows that the column name is misleading.
Changing the name to FSC_On, which is more clearer to show if
the File System Caching is ON or OFF. |
| Problem Summary: |
The output of Dio_On in db2top Tablespaces monitoring screen is
not correct. And the name is also misleading. Changing the name
to FSC_On, which is more clearer to show if the File System
Caching is ON or OFF.
Example :
1) Create 3 tablespaces.
CREATE LARGE TABLESPACE DEMO1 IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY DATABASE
USING (FILE '/home/skharche/D344' 7376)
EXTENTSIZE 8
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 12.670000
TRANSFERRATE 0.180000
AUTORESIZE YES
MAXSIZE NONE
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
CREATE LARGE TABLESPACE DEMO2 IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY DATABASE
USING (FILE '/home/skharche/D345' 7376)
EXTENTSIZE 8
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 12.670000
TRANSFERRATE 0.180000
AUTORESIZE YES
MAXSIZE NONE
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
CREATE LARGE TABLESPACE DEMO3 IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY DATABASE
USING (FILE '/home/skharche/D346' 7376)
EXTENTSIZE 8
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 12.670000
TRANSFERRATE 0.180000
AUTORESIZE YES
MAXSIZE NONE
DROPPED TABLE RECOVERY ON;
=======================================
i.e.
demo1 - NO FILE SYSTEM CACHING
demo2 - FILE SYSTEM CACHING
demo3 - default - NO FILE SYSTEM CACHING ( on JFS2 filesystem)
=======================================
From "db2pd"
Address Id Type Content PageSz ExtentSz Auto
Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg
Name
0x0700000042DB9F00 4 DMS Large 8192 8 Yes 8
1 1 Off 1 0 7 DEMO1
0x0700000042DBCB00 6 DMS Large 8192 8 Yes 8
1 1 On 1 0 7 DEMO2
0x0700000032DAE400 9 DMS Large 8192 8 Yes 8
1 1 Off 1 0 7 DEMO3
===================================================
From "db2 get snapshot for tablespaces"
DEMO1: File system caching = No
DEMO2: File system caching = Yes
DEMO3: File system caching = No
===================================================
From "db2top -d sample -b t"
demo1 -> DIO_ON -> YES
demo2 -> DIO_ON -> NO
demo3 -> DIO_ON -> NO
i.e.
Time;Tablespace_Name;Delta_l_reads/s;Delta_p_reads/s;Hit_Ratio%;
Async_Reads%;Pages_Aread;Delta_Writes/s;Delta_a_reads/s;Delta_a_
writes/s;Direct_writes/s;Data_writes/s;Index_writes/s;Avg_Reads_
Dir_Reqs;Avg_RdTime;Avg_WrTime;Tbsp_Type;# of_DBP;Avg_PrefSize;S
pace_Used;Total_Size;#
of_Extents;High_WaterMark;%_Full;Data_Skew;Tbspce_Status;Tbspce_
Type;Dio_On;Auto_Stor;Auto_Size;Failed_Re
size;Last_Resize;File_Closed/s;Victim_Buffers/s;Unread_PrefPages
/s;XDA_l_reads/s;XDA_p_reads/s;XDA_writes/s;Page_Size;Extent_Siz
e;Prefetch_Size;Minimum_Recovery_Time;Recent_IOs[0]
02:11:30;DEMO1;0;0;0.00%;0.00%;0;0;0;0;0;0;0;0;0.00;0.00;;1;0;0;
0;922;196608;0%;0%;Normal;DMS;Yes;No;Yes;No;-;0;0;0;0;0;0;8K;655
36;65536;-;0
02:11:30;DEMO2;0;0;0.00%;0.00%;0;0;0;0;0;0;0;0;0.00;0.00;;1;0;0;
0;922;196608;0%;0%;Normal;DMS;No;No;Yes;No;-;0;0;0;0;0;0;8K;6553
6;65536;-;0
02:11:30;DEMO3;0;0;0.00%;0.00%;0;0;0;0;0;0;0;0;0.00;0.00;;1;0;0;
0;922;196608;0%;0%;Normal;DMS;No;No;Yes;No;-;0;0;0;0;0;0;8K;6553
6;65536;-;0
Notice here, the output of Dio_On in db2top for
DEMO3 Tablespaces is incorrect.
2) Consider a case of tablespace - DEMO1 - NO FILE SYSTEM
CACHING.
On AIX with Concurrent Journal File System (JFS2),
DB2 should be using CIO for tablespace when NO FILE SYSTEM
CACHING is specified.
In db2top output for column DIO_ON we see it as YES
This shows that the column name is misleading.
Changing the name to FSC_On, which is more clearer to show if
the File System Caching is ON or OFF. |
| Local Fix: |
1) This can be fixed if we do "ALTER tablespace.... NO FILE
SYSTEM CACHING"
db2 "alter tablespace demo3 NO FILE SYSTEM CACHING"
Then 'db2top' will report it as DIO YES.
From "db2top -d sample -b t"
03:07:44;DEMO1;0;0;0.00%;0.00%;0;0;0;0;0;0;0;0;0.00;0.00;;1;0;0;
0;922;196608;0%;0%;Normal;DMS;Yes;No;Yes;No;-;0;0;0;0;0;0;8K;655
36;65536;-;0
03:07:44;DEMO2;0;0;0.00%;0.00%;0;0;0;0;0;0;0;0;0.00;0.00;;1;0;0;
0;922;196608;0%;0%;Normal;DMS;No;No;Yes;No;-;0;0;0;0;0;0;8K;6553
6;65536;-;0
03:07:44;DEMO3;0;0;0.00%;0.00%;0;0;0;0;0;0;0;0;0.00;0.00;;1;0;0;
0;922;196608;0%;0%;Normal;DMS;Yes;No;Yes;No;-;0;0;0;0;0;0;8K;655
36;65536;-;0
2) n/a |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
First fixed in DB2 Version 9.7 Fix Pack 1. |
| Workaround |
1) This can be fixed if we do "ALTER tablespace.... NO FILE
SYSTEM CACHING"
db2 "alter tablespace demo3 NO FILE SYSTEM CACHING"
Then 'db2top' will report it as DIO YES.
From "db2top -d sample -b t"
03:07:44;DEMO1;0;0;0.00%;0.00%;0;0;0;0;0;0;0;0;0.00;0.00;;1;0;0;
0;922;196608;0%;0%;Normal;DMS;Yes;No;Yes;No;-;0;0;0;0;0;0;8K;655
36;65536;-;0
03:07:44;DEMO2;0;0;0.00%;0.00%;0;0;0;0;0;0;0;0;0.00;0.00;;1;0;0;
0;922;196608;0%;0%;Normal;DMS;No;No;Yes;No;-;0;0;0;0;0;0;8K;6553
6;65536;-;0
03:07:44;DEMO3;0;0;0.00%;0.00%;0;0;0;0;0;0;0;0;0.00;0.00;;1;0;0;
0;922;196608;0%;0%;Normal;DMS;Yes;No;Yes;No;-;0;0;0;0;0;0;8K;655
36;65536;-;0
2) n/a |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 16.07.2009
31.12.2009
31.12.2009 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
