Have you ever tried to change a database only to realize it’s a lot more complicated than you thought? As the architect for Data Studio Administrator, I’d like to tell you about new capabilities in Data Studio that can help.
Now that Data Studio Administrator has come out, one commonly asked question is, “What’s the difference between the base Data Studio (included in my data server license) and Data Studio Administrator?” The simple answer is that today Data Studio Administrator provides database change management by extending the object management capabilities that exist in the 'base' Data Studio. As Bryan blogged about previously , it will be extended even further in an upcoming release, but for now, this is the simplest way to view the difference.
I'll illustrate with an example. Suppose you want to increase the length of a VARCHAR column. DB2 provides an ALTER statement that will change the database in one command. In simple object management there’s a single command that does exactly what you want. These great DDL statements are sometimes referred as schema evolution.
Those still reading have probably encountered a situation where simple object management was too simple; i.e., there is no single schema evolution command available that would implement the desired change. A change that requires multiple commands is called an extended alter.
For instance, suppose you need to:
- Add a column in the middle of table
- Delete a column
- Decrease the size of a character field
- Change the class of a column data type
These actions require that you drop and recreate the table.
The base Data Studio can analyze dependencies and generate SQL to handle some extended alters. Unfortunately, it doesn’t do everything. For instance, it doesn’t automatically preserve data, or run utilities.
Some extended alters require a great number of commands perhaps across a number of different objects. The commands might include DDL, DB2 commands, DCL, DB2 commands, utilities etc. Further, the changes might require changes be made to multiple objects. Change management tooling not only understands complex relationships, dependencies, and impacts but can aggregate these changes and deploy them in the proper order.
You can get database change management capabilities for DB2 on Linux, Unix, and Windows in Data Studio Administrator.
Suppose you must make an extended alter but also have a dependent trigger, a view, a procedure, and/or a user defined function.
Using Data Studio Administrator, you have the option to unload and reload data using DB2 utilities like IMPORT, EXPORT, LOAD, or LOAD FROM CURSOR. If you also own High Performance Unload you could use that too. You can automatically generate REBIND, RUNSTATS, and REORG utility commands for affected objects.
Suppose those trigger bodies, view definitions, and SQL UDFs need additional changes.
You can use Data Studio Administrator to:
- Show the impacted objects
- Add additional changes to the same change e.g. update a view definition
- Leave objects in a valid state.
- Show inconsistencies at deploy time instead of run time.
- Quickly undo a change.
As you can see, Data Studio provides you the solutions to understand the full change impact, keep it simple, and deploy it safely.
Please write to firstname.lastname@example.org or add a comment using the comment link to below if there are other specific topics you'd like me to blog about.