SQL Statement - 11-02-2007 , 07:02 AM
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.
Re: SQL Statement - 11-02-2007 , 09:20 AM
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
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
2 record(s) selected.
doug.woodward (AT) neocol (DOT) com wrote:
Re: SQL Statement - 11-02-2007 , 11:11 AM
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.
Re: SQL Statement - 11-03-2007 , 06:22 AM
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