Jim Wankowski

DB2 LUW Autonomics

Mon, 30 Jun 2008 13:21:00 GMT

Last week I did an IDUG sponsored webcast on the topic of autonomic computing. I wil be excerpting these features over the next few blogs. If you wish to view a recording of the webcast here is the link: http://www.quest.com/events/listdetails.aspx?contentid=7611&technology=&prod=&prodfamily=&loc=

The beginnings of autonomics

In early releases of DB2 LUW the database and instance configurations were more or less static. In order to make a change the instance or database had to be taken offline for any changes to be made. The move towards an automated tuning process began in V8 where you could start to dynamically change parameters without having to shut down the database. There were quite a few limitations at this stage however.

Self Tuning Memory Manager- STMM

With DB2 LUW V9.1, IBM introduced the concept of automated self tuning memory. The feature simplifies the task of managing package cache, sort heap, buffer pools, and locklists. The amount of memory allocated to these areas is constrained by the maximum memory allocated to the database shared memory. This parameter can be left on or once a typical workload has been set the values can be frozen by turning off the automatic parameter. Generally running the auto tune for a few hours under typical workloads will produce optimal tuning parameters.

There are limitations to using self tuning memory.

Good Candidates for STMM
- Box has lots of free memory
- Workload changes throughout the day
- Inexperienced DBAs
- Multiple bufferpools
Poor Candidates for STMM
- Databases with constrained memory
  - Heavily used server
  - Database_memory set low
- Volatile workloads
  - Constantly changing workloads cannot be tuned effectively
- Static workload throughout the day

The level of automation is continuing to evolve. In v9.5 you can now automate at the instance level, database heap, and new application memory. By specifying the database_memory parameter as AUTOMATIC, DB2 will also dynamically adjust the shared memory based on database workload. If you need to view what the current memory allocations are you can use a snapshot or the GET DATABASE CONFIGURATION command. All memory changes are recorded in memory tuning files which can be found in the STMMLOG directory:

STMM Log

Next time, I will be covering the new automatic storage features introduced into DB2 LUW.

DB2 Autonomics

Mon, 02 Jun 2008 15:16:00 GMT

Obtaining optimal performance from your DB2 LUW applications requires that you not only ensure your SQL is written efficiently and is properly tuned, but one must be sure that the DB2 instance itself is properly tuned. Too many people make the mistake of focusing all their efforts on tuning SQL and do not take a look at the big picture. Think of your DB2 installation as an ecosystem, where memory, physical design, and SQL transactions all work in conjunction with each other, and all can have a direct impact on performance. No doubt that proper SQL coding is probably the most critical factor when it comes to performance, but it is very important to understand that the best written SQL statement will not perform properly if there are inadequate memory allocations, or a poor physical database design. Monitoring and tuning a DB2 instance requires time and skill. Many LUW installations may not have the expertise to optimally tune their environments.

One of the unique differentiators between DB2 and other relational databases is IBM’s implementation of the concept of autonomic tuning and self tuning memory and automated maintenance. With these features the database is becoming much more hands off making the database much more reliable and freeing up DBAs time for other tasks.

This automation is broken up into different categories:

Self Tuning Memory (STMM)
- Package Cache
- Buffer Pool
- Sort Heap
- Locklist
Storage Management
- Automatic tablespace
  - Auto-resize
Automate maintenance
- Reorg
- Runstats
- Backup

Over the course of the next few weeks, I will be discussing each of these different categories in more depth so stay tuned!

Custom Data Reporting with Toad for DB2

Tue, 18 Dec 2007 17:57:00 GMT

One of the nicer features within Toad^® for DB2 is the wide array of reporting capabilities. There is a constant requirement for managerial level reports that need to be run on a regular basis. Many of these reports are generating using reporting tools like QMF, Crystal Reporting, etc. Toad’s data reporting facility provides an easy method for generating professional report templates which can be saved and reused. I will take you through an example of building a custom data report.

The first step is to build the desired query. You can do this through the query builder, or SQL editor. In my example I built a simple join of the sample department and employee tables through the query builder option.

Once you are satisfied with your query, right click on the results grid and select:
“SEND TO/Data Report Designer”.

This will open up the report design wizard.

Select the desired columns for the report

Select desired grouping

Select the desired layout

Once all the basic formatting questions have been answered the report designer will then be displayed. The designer allows you to make custom headings, page breaks, column headings, etc.

Once you r formatting is complete, select the preview button to see the finished report:

The report templates can then be saved and put into the project manager for later reference. Toad for DB2 provides a low cost easy to use alternative to other 3^rd party report generators.

Multi-Location Script Executions

Thu, 22 Nov 2007 03:24:00 GMT

In many cases there is a need to create an object or to execute an SQL statement in multiple locations. The group execute option in TOAD for DB2 allows you to execute a script against multiple environments with one easy step. Let’s step through a few use cases.

Example 1: A new table needs to be added to multiple databases. The first step is to generate the necessary DDL and send it to the editor.

The next step is to go to the toolbar and select VIEW/GROUP EXECUTE:

The group execute window will open on the bottom of the editor, displaying all your cataloged connections. Select the locations you wish to execute the DDL from and click the execute button.

Status messages will be displayed next to selected targets and script executions can be view on the right.

Example 2: Execute a query against multiple databases and validate that the same results set is being brought back.

Open the Query in the editor and select the VIEW/GROUP EXECTUE option.

Select the desired targets and execute the query. Then right click on the results sets and send them to the Data Diff Viewer.

The Diff Viewer will then validate the results are the same.

As you can see the “Group Execute” option can be a very useful tool. It is very easy to replicate script executions across multiple environments and even multiple platforms of DB2 concurrently.

TheThree Letter Word

Mon, 29 Oct 2007 21:27:00 GMT

I just got back from the IBM IOD conference in Las Vegas; it was a great show. Every year there is usually some buzz about what is “hot” with DB2. This year’s buzz was heard loud and clear – XML was being discussed just about everywhere. This is definitely the driving focus for the latest release of DB2 running on both LUW and z/OS. Toad^® for DB2 3.0 has many XML features already enabled. You can create tables with XML data types, create indexes on XML columns, import XML data into tables, and generate data reports including XML data. The following is a quick example showing how TOAD will allow you to create a table featuring XML data.

Select the CREATE option from the tool bar and select TABLE:

From the general tab assign a name for the table. Then select the column option to begin adding your columns.

The column data types are release sensitive. When you create a table on V9 LUW the object types will include XML. Create a column with the XML datatype.

The SQL script option will allow you to review the DDL then execute the DDL to generate the table.

To populate the table you can then use the SQL Editor to code inserts or use the IMPORT wizard to import data in a file. You can then also run your XQUERY statements through the SQL EDITOR to query your table as well.

Toad for DB2 is well enabled today to help you build your XML applications.

DB2 Day to Day

Fri, 12 Oct 2007 18:13:00 GMT

Hey DB2’ers,

I had a busy week last week speaking at the Central Canadian DB2 Users Group in Toronto and visiting with customers. The best part of this job is that I have the opportunity to speak to many people in the DB2 community and get to hear what they are doing with DB2 and what their needs are from a tool perspective.

It’s interesting to hear that although companies are using DB2 for a variety of applications the work of the DBA stays pretty consistent. There is always a need to be able to quickly and accurately make changes to objects and put all dependencies back as they were. The other common thread is they need a solid SQL tuning tool.

Developers like to throw SQL together and not really test. This was the subject of my blog a few weeks ago. I spent all day Friday onsite with one of our largest customers discussing what they do on a day-to-day basis and what they need their tools to do. They confirmed the need for a solid object management and SQL tuning product. We have some exciting new features planed for the upcoming release of Toad^® over the next few months.

What I would like is to hear from you on what your day to day job entails, and what type of functionality would be most useful to you. I am looking forward to your feedback.

Roll with the Changes

Fri, 28 Sep 2007 22:16:00 GMT

One of the questions I get quite a bit from customers is; do we have the ability to compare data and/or have the ability to compare DDL files to what’s currently in the DB2 catalog? As of V3.0 of TOAD for DB2 the answer is YES to both of these questions. A new feature called “Difference Viewer” allows you to compare table data, results sets, and various script files either file to file, or file to catalog. This feature is available for both LUW and z/OS environments. This is a very useful feature when you wish to verify the data in multiple copies of a table, or in many cases you may have DDL files saved and you want to be sure they match what’s actually in the catalog. Here are a couple of examples:

Example 1 – I have 2 copies of my employee table. I wish to verify the employee count is the same.

Select the table and send the data to the Diff viewer. Repeat this step for the second employee table. The diff viewer report will display the data side by with a summary at the bottom left of the window. In this example we can see that an additional employee was added to my second employee table.

This feature will also be useful when tuning queries. A queries result set can be sent to the Diff Viewer and when the query is modified and re-executed the new results set can be compared back to the original query to verify that the result set has not changed.

Example 2 – I have a DDL file that I wish to compare to what’s actually in the catalog to validate that they are in sync. This time we select “Diff Viewer as Script”:

When the Diff Viewer window opens the existing tables structure will appear on the left. On the right I then select the browse button and select the DDL file from my directory. The compare then verifies that the DDL I have saved does not match what is currently in the catalog.

As you can see there are many uses for this new feature. It can even be used to compare various TXT , HTML, XML, JAVA, .NET, PERL and many more types of documents. Have fun with this one!

See you next week!

The Attack of the Clones!

Mon, 17 Sep 2007 18:28:00 GMT

This week I am going to switch gears over to the big iron and talk about DB2 z/OS. IBM has been making great strides in the area of schema management. As any mainframe DBA knows, the process of having to make a seemingly simple schema change can result in a very complex combination of DDL, Utilities and batch processes. These changes can result in applications having to be unavailable for significant amounts of time. Starting with V8 of DB2, IBM introduced the concept of “Online Schema Evolution.” This is the process of being able to make changes to your DB2 objects with very little or no down time by eliminating the need to unload data, drop/recreate objects with dependencies, reload the data and rebind any plans or packages associated with the objects. This is a significant feature when it comes to high availability applications. With the upcoming release of DB2 for z/OS V9 this area continues to evolve.

The first one I want to talk about this week is table cloning. Table cloning is a unique feature that will benefit applications that require high availability such as web based applications. With table cloning you can make an exact copy of an existing table in the same tablespace and then switch back and forth between the tables with almost complete transparency. The cloned table can then be loaded with data just like the original table. This in effect provides a very simple data mirroring facility plus having the ability to have different versions of data.

The other significant schema management features are:

Column renaming – This feature gives you to have the ability to rename table columns without having to drop/recreate the table. This will be a great time saver and potentially eliminate the need to add new columns to a table by allowing for the reuse of existing columns.

Renaming Indexes -This feature can be helpful when maintaining ERP type applications. Many of these applications have the version coded into the name of the indexes. Not having to drop and recreate indexes when a version upgrade is applied will be a huge timesaver.

DB2 has made great strides in improving availability by minimizing the downtime necessary to perform changes to the database structures. This is essential in today’s 24x7 web enabled world. These types of enhancements are ensuring that DB2 z/OS will remain the core data repository for large IT operations for a long time to come.

See you next week!

The importance of SQL scalability testing

Thu, 30 Aug 2007 20:26:00 GMT

As we all know, there are many factors that influence the performance of your application. Proper memory allocations, physical design, how the SQL is written and workload all affect the way your queries perform. One of most overlooked parts of testing an application is testing queries under production workload conditions. A seemingly simple query runs fine in your test environment, but how will it perform when 10, 25 or 50 end users are running the query concurrently?

One of the neater features in Toad^® for DB2 is the integration with SQL Optimizer and Benchmark Factory^® to do automated index analysis and scalability testing. Once you have a query built the optimizer can recommend indexes and simulate real world user loads.

In this example below a query containing a 3 table join is being run in a DB2 V8 Windows environment. The SLA for this application is to maintain a sub-second response time during peak user time. The query is initially test run with production data volume with an average elapsed time around 230 milliseconds; well below my SLA requirement. I then put the query through a scalability test escalating users from 10 to 25, to 50. As you can see from the graph, the average response time jumped to over 4 seconds, which is completely unacceptable for this application.

After running the query through Toad’s EXPLAIN, you can see that all 3 tables are doing tablespace scans.

The query was then launched into SQL optimizer and the Index Expert option was run. It determined the indexes to be created to optimize this query.

The Indexes were created and the scalability test was run again. This time the query maintained its sub second response time even with 50 concurrent users.

This simple demonstration highlights the importance of doing realistic testing of SQL prior to putting it into production.

Welcome to the Toad for DB2 blog

Wed, 15 Aug 2007 17:41:00 GMT

Welcome to my inaugural blog for Toad^® for DB2. Many of you may not be aware that Toad is now a multiplatform solution. I plan to discuss subjects across both DB2 running on the LUW platforms as well as z/OS. I will address day-to-day issues and show you how Toad can make your job easier whether you are developing new applications or administering legacy databases. Toad for DB2 has been available for about two years now. Quest just released version 3.0 which added some advanced database administration features such as schema migration, compare/synchronization, and advanced alters.

If you are not familiar with Toad for DB2 you may want to take a look at the web pages for more info:

DB2 for LUW

DB2 for z/OS

I look forward to showing you a lot of the neat features in Toad for DB2 and how they can help make you better manage your DB2 environments.

Be on the look out for my next blog where I will answer the quest "Does your SQL Scale?"