An example might be when I want to do row counts on every table, or maybe set up a reorg on all the tables in a database.

Here is the command string you can use to do such a thing:

db2 connect to sample

db2 -x “select tabschema, tabname from syscat.tables where tabschema=’DB2INST1′ and type=’T'” | awk ‘BEGIN{print “db2 connect to sample”} {print “db2 -v \” select count(*) from “$1″.”$2″\”"}’ | ksh

The above inline script will select all the tables and return tablenames, then piping that into awk, we first print a connect statement and then print all the count statements.  All of this is then piped into a ksh.  The connect must be put there because a new instance of ksh is generated.

The base query can, of course, be customize to whatever suits your needs.

The pipe in to ksh is optional as you could just direct it to an output file and use it later and or make further modifications.

Being a DBA, I always feel the need to validate what I do especially with ad hoc statements to ensure that I have done what I expected.

Enter the m option on the db2 command line.  This is a relatively new switch that will cause db2 to return to you how many rows were affected. The m option was introduced with verison 9.

To do this from the command line:

db2 -m “delete from test” will return

  Number of rows affected : 18
DB20000I  The SQL command completed sucessfully.

To do this from a script:

db2 -tvmf test.db2

By doing this, you will have the number of rows affected at your finger tips, not more hoping or assuming or doing count before/count after!

Happy DBAing

 -s switch- I find the -s switch the handiest of all.  This will allow you to stop the script execution if there is an error in the script. To do this execute your scripts with “db2 -stvf filename”.  By doing this, you will ensure the successful execution of your script.  If the script encounters an error, it will just stop at that point.  Checking becomes a little easier because you need only go to the bottom of the script for basic checking.

One additional trick – you can turn off the -s off and on in the script stream.  This allows you to disable checking for certain parts of your script you might expect to fail.  Things such as dropping a table that might or might not exist.  To do this your script would look like this:

update table ……;
update command options using s off;
drop table test.table;
update command options using s on;
update table ……;

you would then execute with db2 -stvf filename

I guess of course you could just leave out the -s and use the “update command options” at the top of the script to turn it on, but I usually don’t do that because I want it on more than off.

I finally bought a new system which had Windows 7…. I got myself a HP P6240F with

• 8 Gb of memory — can’t have too much memory can you?
• Intel Q8300 quad core 2.5 GHz
• Windows 7 – 64bit
• 5200 rpm, 750Gb SATA hard drive

I’m no gamer, so the graphics is no biggy for me, pretty much us it for business.  I do us DB2, so the extra memory will come in handy.

This is a replacement system, not an upgrade, so I didn’t have to worry about data much as it still resides on the old system.  Same with my applications, if it didn’t work on W7, then I could always leave it.

First Impressions -Happy, I think.  Got the system home, plugged it in and it went through a little setup process to install Windows 7.  HP seems to do it nicely, although there are some minor annoyances like bloatware type stuff.  It’s fast, but nothing is installed, so no big surprise there.  The ‘areo’ stuff is kinda kool.

Installing my software – Next step is to install all of my varous software.  Here is where the problems start.  My printer is fairly new – HP C6380 and that auto installed fine.

One of the things about Windows 7 is that it does not come with a mail client.  No more outlook express.  At first I could not find my windows office 2003 disk, so I installed my old office 2000 apps.  Outlook did not work from office 2000.  I tried a few others – Thunderbird and Windows live mail.  Didn’t like the very much but they worked fine.  I finally found my windows office 2003 and installed that and outlook was fine.

My biggest problem was with my video/photo editing software.
-Ulead Video Studio would not even install.  That was disappointing as I really like it.
-Photo Exposion Deluxe 3 does not work very well either.  You can install it, but it has problems finding libraries, so in the end does not work.

Those are the two most notable.

My other problem was my accounting software (MYOB 11) does not work as well.  Had to leave that installed on XP.  Pretty small annoyance, but notable if you do not have a backup system.

My other big problem is that my backup software does not work.  Acronis True Image Home works somewhat, but hangs when I am doing backups… it has been relegated to the useless pile now.  The restore function seems to work ok, but there are occasional corruptions.

Sometimes my printer gets ‘forgotten’ — I go to print something and the printer is gone.  Only way to get it back is to reboot.  An annoyance, but not that big a deal – it doesn’t happen that often.

I have not had any problems with bluescreen crashes or freezing issues.  It has run nicely since I got it.  With all my software installed I run at about 1.5 Gb in use minimum.  Goes up from there, so my advice is to get at least 4Gb of memory – stay away from those 2Gb bargan machines.

Last Impressions – In the end, I’m pretty hapy with my new Windows 7 machine.  I like that it is 64Bit as that is the way of the new world.  As more software is upgraded to 64bit, I’ll be better positioned to take advantage of it.  The boot/shutdown times are good.  Boots up much quicker than XP as well as recovery from sleep.  Other than the photo/video software problems, I think I’ll be happy with my investment.

For this example, we’ll assume that we have a table in a data warehouse called DW.TRANSACTION with two columns (TRANSACTION_ID and EFFECTIVE_TS) that is populated and up to date.

To retrieve the previous month’s records, we would create a query of the form:

SELECT
       TRANSACTION_ID
      ,EFFECTIVE_TS
FROM
       DW.TRANSACTION
WHERE
       EFFECTIVE_TS >= TIMESTAMP_ISO(DATE(CURRENT DATE) - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH)
AND    EFFECTIVE_TS <= TIMESTAMP_ISO(DATE(CURRENT DATE) - (DAY(CURRENT DATE)-1) DAYS) - 1 MICROSECOND
;

If you would like to retrieve data from two months ago, the WHERE clause would be changed to:

       EFFECTIVE_TS >= TIMESTAMP_ISO(DATE(CURRENT DATE) - (DAY(CURRENT DATE)-1) DAYS - 2 MONTHS)
AND    EFFECTIVE_TS <= TIMESTAMP_ISO(DATE(CURRENT DATE) - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH) - 1 MICROSECOND

At this point, you can keep increasing the values for the MONTHS to keep going into the past.

This entry is about how to use HADR.  Once again, just the basics.

The command to switch the primary/standby pair is:

from the standby database:
db2 takeover hadr on database sample

simple as that, the standby is now the primary and the old primary is now the standby.

There is also a feature – ACR (automatic client re-route) which can automatically automatically re-route your client to the new primary database when it can not access the current primary.

1. You need to have either a second machine with DB2 installed, or alternately you can use a second instance.  I’m sure I don’t have to tell you that using a second instance of DB2 on the same machine defeats the whole purpose of this, but it is fine for testing
2. Make sure you have Log Retain set on either explicitly or implicitly
3. Make sure you have db cfg LOGINDEXBUILD set to On
   you need to set the following db config parameters –
   HADR_LOCAL_HOST set to the IP address or host name of the local host
   HADR_LOCAL_SVC set to the port you are going to assign
   HADR_REMOTE_HOST set to the IP address of standby instance – same as LOCAL if using an alternate instance
   HADR_REMOTE_SVC set to the standby port should be at least LOCAL +2
   HADR_REMOTE_INST set to standby instance name
4. Make a backup of your database – can be either online or offline
5. Send the backup to the standby machine.  If you are using two instances, then you can skip this
6. restore from the backup on the standby instance  — I’m assuming you are using the IBM SAMPLE database for this excerise.
7. On the standby database, you need to set the same db config values except you need to flip them around so they are correct – LOCAL values become REMOTE values.
8. On the standby machine/instance “db2 start hadr on database sample as standby”
9. On the primary machine/instance “db2 start hadr on database sample as primary”
10. Check if HADR is working – Do “db2 get snapshot for database on sample” — in the output look for:
    HADR Status
      Role                   = Primary
      State                  = Peer
      Synchronization mode   = Nearsync
      Connection status      = Connected , 11/26/2009 17:47:40.447523
      Heartbeats missed      = 0
      Local host             = 192.168.0.105
      Local service          = 10000
      Remote host            = 192.168.0.101
      Remote service         = 11000
      Remote instance        = DB2
      timeout(seconds)       = 120
      Primary log position(file, page, LSN) = S0000036.LOG, 0, 000000000B3B0000
      Standby log position(file, page, LSN) = S0000036.LOG, 0, 000000000B3B0000
      Log gap running average(bytes) = 0

    if it says anything other than ‘peer’ then there is a problem. One common problem is a firewall… if you are doing this on a windows box with a firewall, then you will have to open up the HADR ports both ways.

You now have a HADR pair up and running — anything you do on the primary will be done on the standby as well.  Be sure to review things that don’t work. 

Standby for Part 3 where we will go over how to switch back and forth from primary to standby.

One thing I do notice with HADR is that if you do an operation that is not allowable (such as load … not entirely true which will be explained later), it does not really tell you.  That means you could flip over to the standby and find it unusable.  Care is needed.

In this post (I’ll do it over multiple posts), I am going to run through an example of setting up HADR on a windows environment.  You can use one or two machines to try it out.  If you only have one machine, you need to define an additional instance to be the HADR.  A HADR relationship must at least be on different instances.  In real life, there is not much point in having an HADR pair on the same physical hardware.

Here are the basic steps to setting up a HADR pair

1. Create/select your primary database
2. Set DB config parameters on primary db
3. Backup database
4. Restore database at standby site/instance
5. Set DB config parameters on standby db
6. Start HADR on standby then on primary site/instance
7. Validate HADR is connected and working
8. Test takeover function to switch primary/standby

I’ll go over each step with examples in subsequent posts

One problem has always been dates… they never come out right

One solution to that is to use the TEXT format command to format it to DB2 formatted date…

use =TEXT(cell ref,”yyyy-mm-dd”) in excel to format as a date that can be used in a formula when you generate sql

sql-build-sample

• This will search all files in the current directory for the text you are looking for: findstr /c:”I want to find this text” *.*
• If you want to also search sub-directories use the /s option: findstr /s /c:”I want to find this text” *.*