SQL Statement

Hello I am looking to set an automated task to run against the Resource Manager
DB in order to migrate data from Content Managers LBOSdata directory over to
TSM. As it stands I am changing the action date to the current date so that CM
will then action the old job and send it to TSM.

The command I am using is:

update RMADMIN.RMOBJECTS set OBJ_ACTIONDATE='2007-11-02' where OBJ_MGTCLASSID=1
and OBJ_STGCLASSID=1 and OBJ_CREATEDATE<='2005-10-31 23:59:59.000000';

What I would like to be able to do is set up a script so that DB2 looks at the
current date and says it will move objects over 18 months old to TSM via a
similar command to the above, thus requiring no daily intervention from myself.

As you can guess, my SQL skills aren't up to the task so I am asking for your
assistance.

I'd rather not write the update statement, but does the days function 
compared with 180 help?  i.e. with these rows:

D:\>db2 select * from rmobjects

OBJ_CREATEDATE
--------------------------
2005-10-31-23.59.59.000000
2006-10-31-23.59.59.000000
2007-10-31-23.59.59.000000

   3 record(s) selected.

I can select only rows where the date is 180 days behind the current date:

D:\>db2 select * from rmobjects where days(current 
date)-days(obj_createdate) ">" 180

OBJ_CREATEDATE
--------------------------
2005-10-31-23.59.59.000000
2006-10-31-23.59.59.000000

   2 record(s) selected.

doug.woodward@neocol.com wrote:
> Hello I am looking to set an automated task to run against the Resource
Manager DB in order to migrate data from Content Managers LBOSdata directory
over to TSM. As it stands I am changing the action date to the current date so
that CM will then action the old job and send it to TSM.
> 
> The command I am using is:
> 
> update RMADMIN.RMOBJECTS set OBJ_ACTIONDATE='2007-11-02' where
OBJ_MGTCLASSID=1 and OBJ_STGCLASSID=1 and OBJ_CREATEDATE<='2005-10-31
23:59:59.000000';
> 
> What I would like to be able to do is set up a script so that DB2 looks at
the current date and says it will move objects over 18 months old to TSM via a
similar command to the above, thus requiring no daily intervention from myself.
> 
> As you can guess, my SQL skills aren't up to the task so I am asking for
your assistance.
> 

That certainly is helpful and works as you stated, but I don't know how to
adjust the action date to be the current day.

The command I think would work is:

update RMADMIN.RMOBJECTS set OBJ_ACTIONDATE='2007-11-02' where OBJ_MGTCLASSID=1
and OBJ_STGCLASSID=1 and days(current date)-days(OBJ_CREATEDATE)>547;

CURRENT DATE is an available special register in DB2, so you could use it 
with a little date arithmetic, like this:

update RMADMIN.RMOBJECTS set OBJ_ACTIONDATE= CURRENT DATE where 
OBJ_MGTCLASSID=1 and OBJ_STGCLASSID=1 and OBJ_CREATEDATE<= CURRENT DATE - 18

MONTHS