As everyone gets ready for the DB2 migration to Version 10 there are many things that need to get done to start implementing the system and application enhancements. Below are another three more items to do ahead of the schedule so your company, systems and applications can leverage the features as soon as you start your DB2 migration to DB2 10.
First: Analyze the reporting that is done within your systems and applications. There are always accounting, financial or inventory or other reports that have all types of calculations. Most of these report types have formulas that use averages, sums and other type of calculations. With the DB2 migration to Version 10 consider leveraging all the data warehousing and business intelligence functions built into DB2 already such as RANK, ROLLUP, CUBE, OVER PARTITION and others as well as the new DB2 10 moving average and cumulative sum functions within SQL to make a huge difference in performance.
By driving these calculations, especially the new sum and average calculations, directly into the DB2 engine the processing can sometimes be reduced five to ten times. Remember no application or external software is faster than the DB2 engine and with the DB2 migration to DB2 10 you can leverage these DW and BI functions to give your applications a quick DB2 10 performance improvement story.
Next: Remove duplicate or extra indexes by combining them with the new INCLUDE Index option. With the DB2 migration to DB2 10 this new feature allows non-unique columns to be included in the definition of a unique index definition. Before this enhancement, multiple indexes were required for indexing, one for the unique constraint and another index for the non-unique columns.
Start the analysis early before the DB2 migration by running the following queries to help you identify tables with more than 4 indexes and then identify the columns defined within those indexes that might be candidates to be INCLUDEd once the DB2 migration to Version 10 is complete. By eliminating the extra indexes you can save potentially millions of I/Os and the related CPU..
Find the tables with more than 4 indexes
SELECT COUNT(*) AS CNT, CREATOR, TBNAME
FROM SYSIBM.SYSINDEXES
GROUP BY CREATOR, TBNAME
HAVING COUNT(*) > 4
ORDER BY COUNT(*) DESC
WITH UR
Find the indexes and their columns on a table
SELECT A.CREATOR,
B.INDNAME,
A.UNIQUERULE,
B.COLSEQ,
B.COLNAME,
C.LENGTH,
B.COLORDER
FROM SYSIBM.SYSINDEXES A,
SYSIBM.SYSINDEXCOLUSE B,
SYSIBM.SYSCOLUMNS C
WHERE A.NAME = B.INDNAME
AND A.TBNAME = C.TBNAME
AND A.TBCREATOR = C.TBCREATOR
AND A.CREATOR = B.INDSCHEMA
AND B.COLSEQ = C.COLNO
AND B.COLNAME = C.NAME
AND A.TBCREATOR LIKE ‘creator’
AND A.TBNAME LIKE ‘table-name’
ORDER BY A.NAME,
COLSEQ
WITH UR
Three: Even before your DB2 migration, start checking out, developing and exploiting the SQL/PL capabilities and the DB2 10 enhancements such as the SQL Inline and Non-inline Scalar Functions. DB2 Version 9 provided the baseline functionality, the DB2 migration to Version 10 provides further functionality and compatible with other DBMS vendors. You can dramatically improve your application performance by pushing your common routines, processing directly into the DB2 engine with SQL/PL.
In addition the non-linear scalar functions can provide all types of programming logic and SQL control statements for maximum application flexibility and SQL functionality. Also the DB2 migration to Version 10 provides SQL/PL features that can now be put into a versioned DB2 package allowing easy application change control management for all this type of new functionality.
So check out these situations before your DB2 migration to DB2 Version 10 and you will quickly gain the performance improvements advertised for this new release.
Also don’t forget to sign up for my DB2 Night webcast next week October 17th. I will be talking about Data Warehousing and the new DB2 10 Bi-Temporal data warehousing features. Click here (https://www3.gotomeeting.com/island/webinar/registration) to register for webcast.
I will be speaking at the IBM Information on Demand Conference in Las Vegas. I will be presenting my “DB2 10 Temporal Database Designs for Performance” on Tuesday, October 25th, 10:00 AM -11:00 AM in the Mandalay North Convention Center Islander D. If you are registered for the conference, make sure to add this session to your schedule. For more details on my presentation click here. I am also arranging a get together for colleagues either Tuesday or Wednesday night before the IBM festivities start. Please let me know if you are attending and maybe we can talk about your application and performance issues.
Remember the early bird cutoff is coming quick so sign up today for IDUG Prague! I will also be speaking at the International DB2 User Group (IDUG) European Conference in Prague. I will be presenting my “DB2 10 Temporal Database Designs for Performance” on Mon, November 14, 1:30 PM – 2:30 PM. For more details on my presentation click here. I will also be involved in the Data Warehousing Special Interest Group (SIG) and the final discussion panel, so please join all the presentations, networking and discussions on DB2. Sign up now at www.idug.org.
Finally, I am going to be presenting at the Midwest DB2 Users groups in the 4th quarter. I will be doing Minneapolis on December 6; Milwaukee, Wisconsin (http://www.wdug.com/) on December 7; and Chicago, Illinois (http://www.mwdug.org/) on December 8. I look forward to seeing everyone at the meetings to discuss all their plans for the 2012 year.
Come see me in any of these venues and ask me your DB2 performance or data warehouse performance questions.
Leave a Reply