Data Studio Update Part 4
Changing Database Objects
Dean Compher
28 May 2013
Data Studio allows you to change and add database objects like tables and indexes with an easy GUI interface. It knows whether an object can just be altered or if it must be dropped and recreated to implement the change you want. If it needs to drop and recreate an object it will generate scripts that save the data and all dependent objects like indexes, authorities (grants), foreign keys, etc to rebuild and reload everything. This can save a lot of time for large changes and improve the quality of changes because it doesn’t forget anything. I’ve seen major changes go into production, just to look bad because we forgot to grant privileges to one object that caused the whole application migration to fail. Data Studio also remembers to do the little things like RUNSTATS and REBINDS where needed. These scripts are just DB2 CLP scripts that can be run from within Data Studio or saved to a file and run from a CLP on the server or your workstation. Not only can the change scripts be created by changes that you do to objects in Data Studio, but it can also compare two databases and generate a script to make one look like the other.
One thing to keep in mind is that all changes are put into a change script and not implemented until you tell Data Studio to do it, even for very simple changes like adding an index. You can always review the scripts before running them and then either run them immediately or schedule them for later. This article focuses on DB2/LUW, but some of these features are available in other DB2 platforms as well as Informix, Oracle and SQL Server. To see what features are available on your favorite database see the features by data server link.
A change plan is a directory and set of files where Data Studio keeps track the changes that you have made. You can create a change plan by just starting to make a change to a database object and one will be created for you, or you can explicitly create a change plan giving it a name and then making changes. The first time that you start making changes to a database, a new project will be created for you in the Data Project Explorer. A subdirectory will be created under the Data Studio Workspace directory as well with the same name. A copy of the plan is also created in the Change Plans folder under the database name in the Administration Explorer. For the most part, you will just be working with change plans from the Change Plans folders under each database in the Administration Explorer. Figure 1 shows that there are two change plans for the GSDBTEST database – the one called “Default Change Plan…” was created by Data Studio when I changed a table, and I created “Project XYZ” explicitly before making changes.
Figure 1. Change Plans
Making a Quick Change
Frequently, in test databases you want to make a quick change to add an object or change something that has no cascading effect. Even in this case Data Studio will not make the change immediately, but will accumulate the change or changes into a change plan that you can execute at any time you like. Such changes include giving authorities, creating objects like tables or indexes or even making minor changes like adding a column to the end of a table. Figure 2 shows how you would go about adding a select authority to the BRANCH table in the GSDBTEST database. As soon as you click the alter button (step 5) a change plan is created that starts with “Default Change Plan…”. If you were to continue making changes to this or other tables or objects in the same session, those changes would continue to be added to your change plan, but your changes would not be implemented in the database. To create a new object you would right click the folder corresponding to that object type in the Administration Explorer View and choose the “Create” option from the pop-up menu. For example, if you wanted to create a new index, you right-click on “Indexes” and then choose “Create Index” and then follow the prompts. For a more detailed description of how to make changes and add objects to your database please see the Basic Change page in the Data Studio Information Center.
Figure 2. Steps to give SELECT authority to user db2admin on table BRANCH
After making your change or changes you will want to deploy them to the database, but first you probably want to review the changes. To do that you will want to open the “Change Plans” folder under the database in the Administration Explorer view as shown in step 1 in Figure 3. You will then click on the plan created for you (step 2) and then click the “Review and Deploy” (step 3) button. This will bring up the “Review and Deploy” view, but will not actually implement the changes. If Data Studio detects that errors would occur in the deployment, it will tell you in a dialog box. As you can see in Figure 3, Data Studio has the DDL needed to grant the authority on the branch table (middle line) and you can also see that I am adding an index and because of that, Data Studio knows that I’ll also need to run RUNSTATS and it puts that in there too automatically. At this point I could click the Run button to effect the changes or I could copy the script and put it into a file and run it in a CLP session. Even if you only want to implement changes with scripts, this is a great way to create the correct syntax without having to look it up.
Figure 3. Implementing Simple Changes.
It is also worth noting that Data Studio also creates a script to back out the changes and put the database back to the way it was in case you decide that you don’t like the effect that the changes had. This is also useful if the change fails part of the way through. To see the undo script, just right click on the change plan (sort of like step 2 above) and choose “Review Undo Script”. Both the change script and the undo script can be pasted in a file and run from a CLP window from a client or on the DB2 database server itself. Please see my article on running DB2 Queries for more information on how you would run this script from a CLP window.
More Involved Changes
There are times that making a seemingly easy change can make for a lot of work. For example, adding a new column to a table that needs to be placed somewhere other than at the end of the table. This type of change means that the table needs to be dropped and created again. When this happens you need to preserve the data and all of the objects that depend on the table such as indexes, authorities, foreign keys and others. You also have to remember to gather all of this before you drop the table or else all of the information will be gone. Then you have to create scripts to put the data back as well as recreate all of the objects that are dropped along with the table. This can be a tedious and error prone task. Fortunately, Data Studio does all of this for you as well as creating a script to back out the changes in case your implementation fails part of the way through and you need to get rid of the changes that you already made.
For your more involved changes you will likely want to give the change script a name, so you should start by creating a change plan before starting any changes. To do this right-click on the “Change Plans” folder in the Administration Explorer and choose “Create Change Plan” menu option and then give your plan a name in the view that pops up. In Figure 4 I am creating a change plan called “Project XYZ”.
Figure 4. Create a named Change Management Script
Once you have created your named change script, you go about adding and changing objects just as you did in the section above. For changing existing objects, you click the object you want to change in the object list on the right side of the window and then make the changes in the properties tab it as illustrated in Figure 2. Further, you add new objects by right-clicking the object type in the Administration Explorer view and then clicking the “Create …” choice from the pop-up menu. For example, if you wanted to create a new buffer pool, you would just right-click on the “Buffer Pools” folder. One other thing that is not obvious is how you add, delete, or change position of items in a list to a new or changed object. This includes things like adding columns to a table or deleting containers from a table space. The buttons you press are circled in red in Figure 5. Click the yellow diamond to add an item to the end of the list, choose an item and click the red X to delete it, and click an item and then the up or down arrow to move it in the list.
Figure 5. Buttons to add, delete or move items in a list for an object.
As I noted earlier, making one small change to a table can mean a very complicated change process. For example I want to add a new column called “END_MFG_DATE” to the PRODUCT table and make it the second column of the table right after the primary key. Had I been OK with leaving it at the end, Data Studio would have realized that the change could have been implemented with a simple ALTER TABLE command and would have done that. However, since I used the up arrow button after adding the new column to put it second in the list, Data Studio knows that DB2 will require that the table be dropped and created again to implement this change. Since this table has data in it and has lots of dependent objects like foreign keys, and indexes, the script to implement the simple change of adding a column in the middle becomes complex. As you can seen in Figure 6, quite a few lines of scripting are needed to implement this change to get the data back as well as to put back all of the objects that depend on the table. I added red boxes around some of the more important ones that I wanted to highlight:
1) The Data is exported using the EXPORT Command. You can edit the output directory and file name if you like.
2) The table is dropped and recreated
3) The SET INTEGRITY command is run before and after the data is loaded back into the table.
Also all of the more mundane things like running RUNSTATS and GRANTs are scripted as well.
Figure 6. Portion of script needed to implement a new column in the middle of a table.
This one simple change caused 69 lines of code to be generated, but all I had to do was to add the column and move it to where I wanted it. Now imagine that you have several of these types of changes along with other miscellaneous changes. The amount of effort that it takes to do this is considerable, and leaves a lot of room to make mistakes that may not be noticed until the night of your big production upgrade. That could be a disaster.
Again you can click run to implement the script or copy it to a file and run it at a later time. Since this is just DB2 CLP scripting, you can change it. You may notice that data is preserved by using the EXPORT and LOAD commands. If you prefer another method, you can change the script to use your favorite way of preserving the data. You should also review the undo script as changing the main script may have adverse impacts on the undo script.
Migrate Changes to Production
After a months long project to enhance an existing set of applications where you and your team have made lots changes and tweaks to the development database, it may be difficult to remember exactly changes were made. However, missing one small database change could blow your entire production migration out of the water. The good news is that Data Studio can compare two databases, highlight the differences and let you easily pick which ones you want migrated. When you are done choosing you click another button and Data Studio will generate a change script for you to implement the changes on the target database that you can then review and deploy. This change script is no different than if you had made the various changes to objects in the target database yourself in Data Studio.
It does not matter to the compare facility whether you used Data Studio to make the changes in the source database or not – it just compares the differences between the two databases. You could have just found a test database and used the compare to see what is different between it and another similar database. It can even compare a DDL script to a database if that works better for you. Comparing the production DDL to the test database is very handy if you as the DBA are not allowed to connect to production. Since Data Studio can detect all differences such as different names for primary keys or foreign keys, it is a good idea to create your test database to be as similar to your production database as possible on the important objects like tables and indexes. This reduces the amount of clutter when Data Studio shows you the differences between the databases. If you get to create your test database from scratch, you can use this compare and migrate process in reverse to copy the structure of the production database into the test database.
I will point out a few highlights of comparing and migrating changes, but you can see detailed instructions for the wizard on the Compare and Migrate page in the Data Studio 3.2 Information Center. While these instructions correctly indicate that naming the change script is optional, I would recommend it. Start by creating the change script in the target (generally production) database. Also one thing that was not clear to me is exactly where to start the migration process. After you create the named change script, right-click on the database name in the Administration Explorer view that will be the target of the changes (generally the production DB) and choose Migrate | Compare and Migrate Objects. In Figure 7 I am starting with the GSDB database because that is the database where I want to implement the changes that I already made in GSDBTEST.
Figure 7. Start Your Migration Test Script
This will launch the “Migrate and Change” wizard view where you will be prompted to select several things including the source of the changes. In this case it is the GSDBTEST database. After working through the wizard views you will eventually come to the view that shows the differences between the source and target databases as shown in Figure 8.
Figure 8. Comparing Objects
In Figure 8 I have scrolled through the changes and expanded
the GOSALES schema and then expanded the PRODUCT table. If you recall, we
added a new column to this table in the GSDBTEST database in “More Involved
Changes” section above. As you can see in the compare window, one of the
differences shown is that the END_MFG_DATE column exists in the GSDBTEST
databases and not in the GSDB database. To have this change put into the
change script, click either the Copy or Merge button: 
. This will cause the
column to also be shown in the target side as well as adding lines to the
change script that would implement the change. As you can see, there are
several other differences shown for this table. The foreign keys have
different names as well as the indexes and the primary key. This may or
may not be something that you want to change when you implement the new column
in production. For the overall description and instructions for the
various aspects of this functionality, please start with the Comparing
and Merging page in the Data Studio Information Center. This page
gives an overview with links for more detailed instructions for various
functions.
Working with Change Plans
In this article I have discussed change plans to some extent, but wanted to take a moment here to discuss how to administer them. Whenever you create one explicitly or implicitly by changing something, the plan that gets created will be the one that captures the changes for your Data Studio session. Once you close Data Studio and want to continue adding changes to an existing change plan you need to open it. Just go to the change plans folder under the database you are working with and double click on the plan you want to be active. You can easily change the name of any plan just by right-clicking on the name and choosing “Rename” from the pop-up menu. Open the Change Plans page in the Data Studio Information Center to read more about this.
Review Impacted Objects Before Change
Before executing your change script, it is a good idea to review what objects will be impacted by the change you are making as shown in Figure 9. To do this you open the Change Plan by double clicking on it (Step 2). This will give you a list of the objects like tables, indexes, etc. that you are changing. You can then right click on each (step 3) one to see a graph of the impacted objects by clicking the Analyze Impact choice on the pop-up menu. This can help avoid any unintended consequences of your change.
Figure 9. Steps to see impacts of each change
Copy/Paste Objects and Data
Another interesting option that Data Studio provides is the ability to copy and paste tables and other objects. You can do this at the schema level, which will copy all tables and other objects, and let you paste it into another database. The copy/paste facility allows to copy tables between schemas and to copy full schemas among databases. You just right click the schema, table or other object from the object list and choose “Copy” from the pop-up menu. You then go to the object list where you want the object to be placed, right-click and click “Paste”. You must have fewer than 10,000 total rows among all objects or the copy/paste will not work. For more information please see the Copy Objects page in the Data Studio Information Center.
Finally, as a reminder, it is a good idea to backup your database before implementing any significant change just in case something goes wrong.
This is the Forth in series of Data Studio Articles that I am writing. Earlier editions that may interest you include:
Data Studio Update Part 1 – Links for installing, summary of my favorite features
Data Studio Update Part 2: Perspectives, Views and Database Connections – Navigating Data Studio, connecting to your databases.
Data Studio Update Part 3: View DB Objects and Attributes – View tables, columns, indexes and model diagram.
Data Studio Update Part 5: Query Building and Editing – Ways to create and edit queries in Data Studio.
Data Studio Update Part 6: Query Tuning for DB2 LUW and z/OS – Tune individual Queries.
Data Studio Update Part 7: Find and Tune Bad Queries from the Package Cache – Find bad queries in the package
***
There are many more things that you can do with the change and compare facilities and I suggest that you take some time to explore them. Please take a view minutes to click around and see what is available. Please add any features that you really like to my Facebook Page or my db2Dean and Friends Community and help the extended community.