Jim Wankowski's Blog

Rolling Back Object Changes in Toad for DB2

Jim Wankowski — Mon, 03 Aug 2009 18:49:00 GMT

Have you ever had a situation where you made a change to a DB2 table structure such as renaming a column, or adding a new column only to discover that your production applications are now crashing because they haven’t been updated to support the new structures? What do you do then? You have to scramble to generate an ALTER script to put the objects back to their original form.

One of the neat new features in Toad for DB2 4.5 is the ability to create fallback scripts for object changes. This feature will automatically generate the backup script with the original structure when you generate an ALTER script. If the need arises to rollback the change you simply execute the fallback script and the object gets put back to its original state. The other use for this option is that you can actually keep multiple versions of an object as it goes through change cycles. Every time a change is made to an object the previous structure is backed up in a fallback script.

Setting up for fallback:

Go to TOOLS\OPTIONS\DATABASE\DB2\ADVANCED ADMIN and check the box for generating recovery scripts:

Make the change to your object. In my example I am adding a new column to a table:

The recovery script is generated in the specified directory:

The file contains a complete script with all dependencies that can be easily executed to rollback the change:

So as you can see, this can prove to be a very valuable timesaver in the event of having to rollout out a structural change, or if you need to keep multiple versions of an object.

Toad for DB2 V4.5 has arrived!

Jim Wankowski — Wed, 22 Jul 2009 20:24:00 GMT

I am very happy to announce that we went GA with Toad for DB2 V4.5 on 7/21. This is by far the biggest, best release of Toad for DB2 to date. With this version we have introduced a full featured database administrator’s tool capable of handling the largest DB2 environments.

Some of the highlights of the 4.5 release are:

General

Sync point restart in scripts for better management of large scripts
DDL script wizard
More powerful migration and compare capabilities
Federated object support
Change recovery scripts

LUW

DB2 LUW 9.5 exploitation.
DB2 LUW 9.7 toleration
Graphical performance report for DB2 LUW
Remote script execution
Integration of Quest Central SQL Analysis

z/OS

Begin exploitation of DB2 z/OS V9
DB2 command generation
z/OS utility generation
Batch script execution via JCL

Over the next few weeks I will be covering examples of some the new more exciting features.

DB2 2009

Jim Wankowski — Tue, 06 Jan 2009 15:19:00 GMT

Happy new year everybody. I hope everyone had a great holiday season. It’s tough getting your head back in the game after a long holiday, that’s for sure! But there is a lot to look forward to in 2009 with our DB2 products. Toad for DB2 came a long way in 2008. As you may or may not know, we are in process on transitioning all of the functionality of our Quest Central for DB2 product over to Toad. At the beginning of last year we had just released Toad for DB2 3.1 which was our first cut at an administrator’s edition. After that release we realized that we needed to not only focus on enhancing the functionality, but improving the scalability of the product as well. I spent a lot of time in early 2008 visiting customers around the world and getting their feedback on their experiences with Toad. One of the common comments I heard was that the product was not performing well in large environments. This feedback proved to be very useful in identifying where we needed to go with the product. It resulted in us doing a major overhaul of the internal architecture of the product in order to improve performance. We also added significant DBA functionality for both LUW and z/OS. When we released 4.0 back in September, it was a completely new product from the previous release. I have received a lot of positive feedback from customers. The DBA edition of Toad is now a true full featured tool for the database administrator for both DB2 LUW and z/OS. But our work isn’t finished. We are getting ready to release the 4.5 beta in the next few weeks. This release will be as big, if not bigger than the 4.0 release. Again, we are focusing on the needs of the DBA.

Some of the highlights of the 4.5 release will be:

DB2 LUW 9.5 exploitation
Begin exploitation of DB2 z/OS V9
Batch script execution for both LUW and z/OS
Sync point restart in scripts for better management of large scripts
Integration of Quest Central SQL Analysis
z/OS utility generation
DDL Extraction feature
Rollback of object changes
Graphical performance report for DB2 LUW
More powerful migration and compare capabilities
Federated object support
Much more…..

I am excited about this release. Toad 4.5 will truly be a world class DBA and developers tool. Stay tuned….

Automated Maintenance

Jim Wankowski — Wed, 03 Sep 2008 14:31:00 GMT

Automated maintenance began with DB2 LUW 8.2. At this time the behavior of automated utilities had to be controlled manually either through the Control Center or the automation wizard. Now with DB2 9.5 automation of RUNSTATS and REORG commands can be controlled by an XML document referred to as an “automatic maintenance policy” These policies control which objects are considered for automated maintenance, and what options are to be used.

Automated Statistics Collection

The feature of automatic statistics collection allows you collect statistics on an as-needed basis. Automated statistics were introduced in DB2 8.2. This release allowed you to check statistics on a 2 hour interval. Statistics collection would then occur if needed based on the analysis. This may or may not be adequate depending on the volatility of the environment. Now with V9.5 we have real-time statistics collection through the DB2 optimizer. When a query runs through the compile/optimize process the statistics for the referenced tables are checked for accuracy, and statistics are collected synchronously as needed. The amount of time allowed for statistics collection is controlled through the optimization profile. The default is 5 seconds, which means that if the RUNSTATS is going to take more than 5 seconds it is automatically kicked off as a background asynchronous process. Automatic statistics collection is turned on by default when a database is created.

Configuration Parameters

AUTO_RUNSTATS=ON
AUTO_STMT_STATS=ON
AUTO_REORG = ON
AUTO_TBL_MAINT = ON
AUTO_MAINT=ON

Automated RUNSTATS activity is tracked in a new log file called DB2_OPTSTATS_LOG. The file can be viewed either manually or with the SYSPROC.PD_GET_DIAG_HIST table function.

Peformance Impact

The real-time asynchronous process executes a “throttled” RUNSTATS, based on current database activity in order to minimize the performance impact.

Statistics Profiles

Statistics profiles control which statistics get collected when a RUNSTATS is executed. Statistic profiles can either be set up manually by running a RUNSTATS with the SET PROFILE option, or by the automatic statistics profiling by turning the AUTO_STATS_PROF, and AUTO_PROF_UPD configuration parameters to ON.

Automated Reorganization

The new automated reorganization feature is essentially an automated version of REORGCHK. If the REORGCHK determines that an index or table reorg is required, it will internally schedule the task.

Configuration Parameters

AUTO_REORG = ON
AUTO_TBL_MAINT = ON
AUTO_MAINT=ON

The new automated maintenance features being introduced into DB2 LUW are not designed to replace the DBA, but to help alleviate these tedious tasks and let them focus on the more critical tasks at hand. See you next time …

Automated Storage management

Jim Wankowski — Tue, 29 Jul 2008 19:30:00 GMT

Continuing on with my discussions on autonomic computing in DB2 is the concept of self managing space. Proper space allocations are another critical factor when it comes to optimizing the performance of your database. Inadequate space allocations can result in application failures and potential downtime which will cost your organization money.

Automated space management was introduced back in version 8.2 (fp9) with the auto-resize feature. This feature would allow for DMS managed tablespaces to automatically increase the size of their containers automatically. The basic syntax was AUTORESIZE YES on your tablespace DDL to activate the feature. The other 2 parameters were INCREASESIZE which specified how much space to add to the container when it needed to be resized (this could either be a fixed value or a percentage) and MAXSIZE which allowed you to specify the maximum size that the tablespace could grow to.

DB2 9.5 introduces the concept of automatic storage. Space management is now moved to the database level. A new storage pool is defined at the database as well as a tablespace storage path. The new syntax is MANAGED BY AUTOMATIC STORAGE on your tablespace definition. This again is only available for DMS type Tablespaces. No containers are specified. Containers will not only be extended but new containers may be created as needed. They will be automatically allocated as needed in the path defined in the database definition. This essentially gives you the simplicity of the SMS allocation method with the performance benefits of DMS.

The new automatic storage feature in 9.5 minimizes the burden of having to monitor space and worry about “unable to allocate new pages” errors in your production databases.

See you next time, when I will be discussing the new automatic maintenance features in 9.5.

DB2 LUW Autonomics

Jim Wankowski — 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

Jim Wankowski — 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

Jim Wankowski — 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

Jim Wankowski — 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

Jim Wankowski — 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

Jim Wankowski — 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

Jim Wankowski — 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!

Jim Wankowski — 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

Jim Wankowski — 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

Jim Wankowski — 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?"