how to automatically delete a row after the elapsed time?

Hi there, Table: my_data - telephone_no - date Now as per the requirement, i have to write a process which cleans up this table from time to time i.e. delete all the records in this database having insert date of more than 3 days old. I don't want to write a process or stored procedure, instead is there any database feature which allows the automatic deletion of records (once the elapsed time is expired???) thnx in anticipation cheers, M

Jobs_Freshers wrote: > Table: my_data > - telephone_no > - date > > Now as per the requirement, i have to write a process which cleans up > this table from time to time i.e. delete all the records in this > database having insert date of more than 3 days old. > > I don't want to write a process or stored procedure, instead is there > any database feature which allows the automatic deletion of records > (once the elapsed time is expired???) There isn't (yet) a database feature that does that automatically. You will most likely end up writing a stored procedure or other process to execute "DELETE FROM my_data WHERE my_data.date < TODAY - 3" on a regular basis (once a day, possibly via cron). Note that DATE is a type name and a function name (and isn't a particularly good choice of column name). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

> Hi there, > > Table: my_data > - telephone_no > - date > > Now as per the requirement, i have to write a process which > cleans up this table from time to time i.e. delete all the > records in this database having insert date of more than 3 days old. > > I don't want to write a process or stored procedure, instead > is there any database feature which allows the automatic > deletion of records (once the elapsed time is expired???) No you will have to script this or use some SPL and kick it off via cron. If the DATE was DATETIME and with sub- day resolution I would be tempted to use trigger, but for a pure DATE then I'd just cron it Cheers Paul Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com GO FURTHER with DB2 GET THERE FASTER with Informix. Attend the IDUG 2006 European Conference. Vienna, Austria. 2-6 October 2006 Visit http://www.iiug.org/conf for more information.

Jobs_Freshers wrote: > > I don't want to write a process or stored procedure, instead is there > any database feature which allows the automatic deletion of records > (once the elapsed time is expired???) Can you give us any more info why you don't want to do this? It may help us answer your question. cheers SS

Jobs_Freshers wrote: > Hi there, > > Table: my_data > - telephone_no > - date > > Now as per the requirement, i have to write a process which cleans up > this table from time to time i.e. delete all the records in this > database having insert date of more than 3 days old. > > I don't want to write a process or stored procedure, instead is there > any database feature which allows the automatic deletion of records > (once the elapsed time is expired???) Get my package, utils2_ak, and compile the dbdelete utility. Then you can create a cron to run daily: ###### BEGIN ####### #!/usr/bin/ksh . informix.environment.setup dbdelete -d my_database -t my_data -p 10 -s 'today - date > 3' ####### END ######### Dbdelete is at least as fast as using dbaccess and a DELETE script and tends to be faster than an equivalent stored procedure running the simple DELETE. Also since it deletes and commits 8192 rows at a time (configurable) it is abortable and restartable and will not blow your lock table (as long as you have more than 8192 * (1 + # indexes) locks configured, and will not cause a long transaction rollback (unless you have VERY little logical log space configured). Art S. Kagel