Make my day

Sometimes you need things to be done on the beginning or the middle of the month. Fees need to be payed, invoices to be sent. A database could hold the next day when the task should be performed. But how to calculate it in SQL if the result needs to be the 1st or 16th?

So lets start playing with days. This little code snippet results in the first day of the current month:

select
         current_date + 1 day
 - ( day(current_date) )  days
from sysibm.sysdummy1;

Subtract the number of the current day and add one. So in Feb. 2008 it would bring up the date expression '2008-02-01'. Now we need a code which gives us the delta of zero or 15 days, depening on the current day and if it's in the lower or the upper part of the month:

select
  ( (day(current_date) / 16 ) * 15 ) days
from sysibm.sysdummy1;

Due to that day(current_date) is maximum 31, the integer division by 16 will result in zero or one. Multiplied with 15, this is the delta we need. So together these codes snippets will give us the first or 16th of the month:

select
           current_date   + 1 day
 -  (  day(current_date) ) days
 +  ( (day(current_date) / 16 ) * 15 ) days
from sysibm.sysdummy1 ;

It can be varied for values of 16 and above, to provide e.g. the 20th or the 1st day of the current month. Remember to divide by x and then multiply by (x-1) in the last addend. Values of 15 and lower won't work.

Moving target

Strolling around the internet, I came across an article of Roman Melnyk about moving data between DB2 databases. Usually you'd try to do a backup and restore, but sometimes you have to use the tools Roman described, db2move and maybe db2look, too.

We're using those two command line programs since choosing DB2 (v7) as part of the platform for our web applications. They are very useful if you need to copy a whole database but don't have the time to take the source database offline for backup. Or if you just want to copy a part of a database.

When changing the hardware platform for our DB2 servers from Intel to Power hardware we discovered another reason for using db2move and db2look: The x86 architecture is a little-endian one, placing the least significant byte of a multiple byte integer first, while the Power CPU is working big-endian. (See http://en.wikipedia.org/wiki/Endianness for details. I guess you could start a holy war about these two philosophies.) This inhibits the backup/restore strategy totally if you need to copy data between these two platforms.

Although the article was published 2004 the topic won't get out of date and if you need to know how to transfer data between DB2 systems it's worth reading.

P.S.: Of course big-endian is the right way to look at data. ;-)