Recently I was forced outside my comfort zone and asked to vet various open source BI tools. I was a report developer in a past life, a database administrator supporting datamarts at various employers, and even supported Cognos backend databases. I thought my past experience gave me an edge when it came to evaluating BI tools.
A number of new clients I’ve worked with lately have had minor or major runstats or reorg issues. I have tons of advice on runstats and reorgs across many blog entries, but I don’t have one place where all that advice is brought together. Here is that article. The truth is that reorgs and runstats are complicated, and most DBAs get them wrong or disagree on them in some way. There is no one right way, but there are a vast variety of ways to get it wrong.
It is no secret that I’ve been doing more work with DB2 on Windows lately. I know a number of clients who are looking for DBAs with experience with DB2 on Windows and are frustrated that they cannot find more candidates. Truthfully, it is not a steep learning curve to work with DB2 on Windows when you’re used to DB2 on Linux or UNIX, but one of the more painful areas is scripting. Unless you’re lucky enough to have always scripted in an OS-agnostic Perl and are able to get Perl installed on all your Windows servers, scripting is a difficult area of conversion. Those of us who are familiar with ksh or bash often use small scripts just from the command line, and can whip up other scripts quickly as needed. Batch is still a bit of a mystery to me. It never works quite how I think it should. I am a PowerShell fan, and have written a number of scripts in it at this point. I cannot say I have fully embraced the object-orientation that PowerShell enables, but at least I have a language that makes sense to me. I thought I’d share a script that is particularly useful.
The database directory is something many of us use every day. It includes informaiton for connecting to databases, both local and remote, as well as additional information like alternate servers and filesystems where database directories reside. Getting just the local database names out of it may take a bit of practice.
What is ADMIN_MOVE_TABLE
ADMIN_MOVE_TABLE is an administrative stored procedure introduced in DB2 9.7. The intent is to provide a tool that can be used to perform an online move of a table, while transactions are still occurring against the table. Moves can be used to change what tablespace a table is in, convert a table to MDC, change the table name, perform several changes that would normally require reorgs, reduce the size of a column, and perform other changes. In the early fixpacks, it was not much more than any DB2 DBA could have written themselves. It uses triggers against the source table to track changes in a staging table while moving the data to a new table. Once the new table is populated, then the data tracked by the triggers is replayed against the target table and a table-level exclusive lock is obtained on the source table for a short period to make the switch (rename).
When I started working with DB2 (on versions 5 and 7), the only option for seeing what was going on in the database was snapshots. I spent a fair amount of time parsing the data in snapshots with Perl scripts to filter out only the data I wanted to see. Slowly, methods of using SQL to access snapshot data were introduced. First table functions for accessing snapshot data, then SYSIBMADM views, and finally we have the MON_GET family of table functions. I’m going to focus on the use of these MON_GET interfaces in this post.
Having just solved this problem for the second time in two weeks, I thought I’d blog about it to make it easier to find the next time.
Not long ago, I posted some of the details of using PowerShell as your command line for DB2 on Windows. I am definitely addicted to PowerShell as my command line when I have to work on Windows servers.
One of the awesome things about running scripts locally on a DB2 server is that if they’re run as a privileged user, you do not have to specify the password. This makes for easier scripting without storing or encrypting passwords. When I first connected to a database with PowerShell, it took me a bit to figure out how to do the password-less local connection, so I thought I would share.
I’ve played with the clpplus at least once before, but have generally thought of it as a tool created to satisfy those coming from Oracle and looking for Oracle-like features. One of the features I actually liked about Oracle in the class and certification tests that I took for it was the ability to specify values for an SQL statement stored in a file on execution. When I lamented the fact that DB2 doesn’t have this feature on twitter, @idbjorh was quick to remind me that such functionality is indeed available using CLPPLUS.