Its good to see that people are reading my BLOG
It’s good to see that people are reading my BLOG. I got a ping on AIM last night, and got some tips on shortening up my script. When I hit the divide by zero error on Windows and it went into the system dump, I looked for a quick solution to not have this any more. I thought of using the CASE statement, and got it working, but there is definitely more elegant ways to do this. Here is an update to the script using NULLIF to get rid of the case statement.select substr(bp_name,1,20) as BP_NAME, int (( 1 - (decimal(pool_data_p_reads) / nullif(pool_data_l_reads,0) )) * 100) as data_hit_ratio,int (( 1 - (decimal(pool_index_p_reads) / nullif(pool_index_l_reads,0) )) * 100) as index_hit_ratio,int (( 1 - (decimal(pool_data_p_reads + pool_index_p_reads) / nullif( (pool_data_l_reads + pool_index_l_reads),0) )) * 100) as BP_hit_ratio,int (( 1 - (decimal(pool_async_data_reads + pool_async_index_reads) / nullif( (pool_async_data_reads + pool_async_index_reads + direct_reads),0) ))* 100) as Async_read_pct,int (( 1 - (decimal(direct_writes) / nullif(direct_reads,0) )) * 100) as Direct_RW_Ratiofrom table (snapshot_bp ('sample', -1) ) as snapshot_bp..Dwaine
Snapshot statements - Good idea to add error checking
I have been reading and looking at some other people’s scripts for capturing snapshots, and noticed one thing that has caused me problems in the past when developing these scripts for my own use. None of the statements are checking to ensure they will not get a “divide by zero” error. I ran into this a number of times while writing my scripts for the Advanced DBA Cert Guide and the Understand DB2 books, and if you have ever had one of these happen on Windows, you know it is not fun.
I therefore added case statements to all of my scripts to ensure that I do not have to ensure another of these errors. Here is an example of the buffer pool snapshot calculating the hit ratios.
select substr(bp_name,1,20) as BP_NAME, -- Need to use a CASE stmt to avoid the divide by 0 (zero) error... (case -- Since dividing by pool_data_l_reads (logical reads) check if it is greater than 0.-- If it is > 0, calculate data hit ratio as 1 - (Physical reads / logical reads) * 100% when pool_data_l_reads > 0 then (int ((1 - (decimal(pool_data_p_reads) / decimal(pool_data_l_reads)) ) * 100) )-- If logical reads = 0, set hit ratio to 0, zero else 0end) as data_hit_ratio,(case -- If index logical reads is > 0, calculate index hit ratio as 1 - (Physical reads / logical reads) * 100% when pool_index_l_reads > 0 then (int ((1 - (decimal(pool_index_p_reads) / decimal(pool_index_l_reads)) ) * 100) ) else 0end) as index_hit_ratio,(case -- If index total logical reads is > 0, i.e. index plus data, calculate index hit ratio as 1 - (total Physical reads / total logical reads) * 100% when pool_index_l_reads + pool_data_l_reads > 0 then (int ((1 - (decimal(pool_index_p_reads + pool_data_p_reads) / decimal(pool_index_l_reads + pool_data_l_reads)) ) * 100) ) else 0end) as BP_hit_ratio,(case -- Asynch read ratio is a measure of the "effectiveness" of the I/O Servers or pre-fetchers-- calculate asynch read reatio by totalling asynch index plus data reads and dividing by total direct index plus data reads. when pool_async_data_reads + pool_async_index_reads > 0 then (int ((1 - (decimal(pool_async_data_reads + pool_async_index_reads) / decimal(pool_async_data_reads + pool_async_index_reads + direct_reads)) ) * 100) ) else 0end) as Async_read_pct,(case -- Examining the read/write ratio, normally the reads should be FAR greater than the writes. when direct_reads > 0 then (int ((1 - (decimal(direct_writes) / decimal(direct_reads)) ) * 100) ) else 0end) as Direct_RW_Ratiofrom table (snapshot_bp ('sample', -1) ) as snapshot_bp ;I will post some more examples later this week.
Give these a try they definitely save a lot of work deciphering the snapshot output by hand.
..Dwaine
An Update from the DB2 Technical Conference
I am at the DB2 Technical Conference in Orlando this week, catching up with some old friends and meeting a lot of new people. There have been a number of very interesting sessions so far, and our DB2 developers and planners are here talking about features they are working on for Viper.
The biggest feature in Viper is likely to be the native XML data type. This will allow you to store XML documents with your regular data in a DB2 table. The XML type is a regular, first class data type, i.e. not a CLOB, and will be able to be queried, updated and indexed the same as a regular data type.
Another feature that will be of interest to a large number of you is data partitioning (AKA range based partitioning). This will allow you to logically configure your tables to group rows by a specific range of dates, etc and when queries are executed, only the relevant groups of data need be accessed. This can greatly speed up the access to these rows since the database server will be accessing a much smaller number of rows. A typical usage of this will be to divide up the table based on the month and year, since in many cases a large percentage of data access will be done on the most recent month or so of the data.
I am presenting two topics his year, “Where has my memory gone” and “Efficiently storing and analyzing your time series data in DB2 UDB”. My first session is this afternoon, so I have to head down to the conference center now.
I’ll post more thoughts on the conference and DB2 Viper later in the week.
..Dwaine
DB2's Automatic Storage - Reason # 11 to Love DB2
If Mr. Whitney was writing his article now, I am sure he would have called it the
Eleven Reasons to Love DB2. In Version 8.2.2 (aka Version 8.2 fixpack 2, or Version 8.1 fixpack 9) DB2 you can now tell DB2 to automatically manage its storage.
To use the full capability of automatic storage, the database must be created on V8.2.2. However, for databases using DMS table spaces with file containers, you can alter the table space to allow it to grow automatically as it fills.
Chris Eaton has done a great job of describing how this works in his blog.
Ten Reasons to Love DB2 UDB V8.2 for Windows
DB2 V8.2 has been available for some time now, and it features a number of new features that increase DB2’s integration with the Windows environment and make the DBA’s job a lot easier. In his article
Ten Reasons to Love DB2 UDB V8.2 for Windows Justin Whitney describes his favorite new features of DB2 V8.2.
The DB2 UDB Cookbook
Do you ever get stuck trying to think of the exact syntax of an SQL statement or command. A great reference that I have used over the years is
The DB2 Cookbook written by Graeme Birchall. This book has the syntax for just about every statement or DB2 command I have ever looked for, and examples which are very helpful with the complex and OLAP SQL. I keep a copy of this on my laptop since it has been so helpful in the past.
