SQL Statement

[d***@neocol.com]

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.

Many thanks.

[Blair Kenneth Adamache]

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.

Post by d***@neocol.com
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.
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.
Many thanks.

[d***@neocol.com]

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;

but I don't know whether how to say that the OBJ_ACTIONDATE is today? As for the rest of the statement, going from the select statement, I can't see why it wouldn't work.

[Richard Lyko]

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