As I talked about last week, good DB2 SQL performance is vital for efficient processing. I also talked previously about DB2 SQL performance here, here, and here describing the many attributes that can impact and improve DB2 SQL performance.
Fortunately all of these blogs combined offer 30 different DB2 SQL tips that will help you get the best DB2 SQL performance possible. Here are 5 more that can help you with improving processing elapsed time and minimize DB2 CPU time.
- Use DB2 Virtual Indexes to model your potential Indexes. Using Virtual Indexes you can model all types of new index definitions such as INCLUDE Column, Index on Expression, Uniqueness and other new possibilities.
Since DB2 is the big data operational engine these days, creating prototype indexes on multi-billion row tables is impractical. To quickly and easily prototype indexes on these large tables create Virtual Indexes that leverage the DB2 V10 INCLUDE Column feature, Index on Expression and any different index column combinations.
Virtual Indexes can be created numerous ways using the Optim Workload Tuner Data Studio Tooling or manually which is not endorsed by IBM but I’ve done it anyway. To understand all the steps, search for “Virtually testing recommended indexes and indexes that you propose for query workloads that run on DB2 for z/OS” within the IBM Data Studio V4.1 Knowledge Center or through this link.
There is also a write-up in Appendix B of the DB2 V11 Managing Performance Manual describing all the columns within the DSN_VIRTUAL_INDEXES table. If you go the manual route, make sure you have authorities for activities against the DSN_VIRTUAL_INDEXES and all the EXPLAIN tables. Also make sure to have the correct CREATOR id on these tables and populate the statistics through the FIRSTKEYCARDF, FULLKEYCARDF and other DSN_VIRTUAL_INDEXES columns.
Most important is to clean up all your virtual index definitions after your prototyping is done. You don’t want a dynamic access path to reference a virtual index definition and cause DB2 SQL performance issues. - Leverage EXCLUDE NULL when creating indexes to reduce index size. Verify that the NULL-ability is not within the application WHERE clauses.
One of the index definitions you might want to virtually prototype is EXCLUDE NULL option. If your index columns definitions have NULLs, excluding them may dramatically reduce the index entries, the number of index levels, and the overall index size. Reducing the index size will definitely speed up DB2 SQL performance and overall access. - Use Optimizer Filter Factor Hints and Profiles to give the optimizer the full information. JOIN Skew/Correlation, accurate statistics, complex predicates, and host variable and special register information are only some of the filter factors that the optimizer uses to determine an access path.
Within the Data Studio and Optim products the DB2 Filter Factor Hints can be generated by editing the Join Sequence Diagram. Once generated, validated, and deployed, the DB2 Optimizer will use the new Hint or Profile information to create a, hopefully, better access path and improve your DB2 SQL performance. Make sure to verify your Hints and Profiles against the entire impacted DB2 packages or create a special user id that only uses Hints and modified Profile Bind attributes to isolate the DB2 SQL performance improvements. Also using a special user id with your Hints and Profiles can make it easier to monitor and document your improved DB2 SQL performance. - Analyze the SYSSTATFEEDBACK table contents to understand your SQL Access Path results better.
The new DB2 command –DIS MODE(STATS) gathers and populates the SYSSTATFEEDBACK table with all the DB2 internal control block information. This command, along with the DSNZPARMS controls in the STATFDBK_SCOPE and STATSINT settings of real time statistics, provides additional information to the DB2 dynamic bind processes for access path selection. These same facilities can be used to understand which table and index statistics are missing. This information can be used to generate better RUNSTAT utility input parameters. With the appropriate APARS applied this feature is available in DB2 Version 10 for z/OS to improve your DB2 SQL performance. - Override predicate selectivity with the DSN_PREDICAT_% tables. Populate these tables with the details of the selectivity of the tables’ uniqueness to improve optimizer knowledge for better access paths and improved DB2 SQL performance.
This feature, like the SYSSTATFEEDBACK feature above, gives you the DB2 DSN_PREDICAT EXPLAIN tables with more information about the SQL Query blocks and the WHERE clause predicates. By querying these DSN_PREDICAT table entries, more detailed data cardinality, comparison information, and other filter factor information is exposed EXPLAINING why DB2 chose the access path it did. Looking at this information, you can identify DB2 SQL performance problems, populate missing statistics, understand query rewrites, and understand what tables might need additional indexes.
Each of these DB2 SQL performance tips can help you dramatically improve your processing elapsed time and reduce your CPU time. Also there are probably more DB2 SQL performance tips besides the 30 that I have mentioned in the various blog entries, so please feel free to suggest more DB2 SQL performance tips ideas in the comments. Try one of these DB2 SQL performance tips today that are appropriate for your database design and you will be amazed how small DB2 SQL improvements can make a big runtime difference.
Here’s a list with links to other posts that may help you improve your DB2 performance, security, and ease-of-use:
5 More DB2 SQL Performance Tips
Another 5 More DB2 SQL Performance Tips
5 Big Data SQL Performance Tips – Fixing Generated SQL
DB2 11 SQL Performance Improvements
5 More SQL Performance Tips for your Big Data
Hadoop SQL: 4 Reasons Why BigInsights Is the Best
Vital Java DB2 SQL Performance Considerations
More DB2 Family Security Best Practices Part 7: Preventing SQL Injection
DB2 SQL Security Audits
Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions. He is an IBM Gold Consultant, Information Champion, and President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs.
Leave a Reply