|
Problem IC62048
|
Status: Geschlossen |
INCORRECT OUTPUT OF DIO_ON IN DB2TOP TABLESPACES MONITORING |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problembeschreibung: |
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-Zusammenfassung: |
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 |
| verfügbare FixPacks: |
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
|
| Lösung |
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 |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 16.07.2009
31.12.2009
31.12.2009 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.7.FP1
|
| Problem behoben lt. FixList in der Version |
| 9.7.0.1
|
 |
