ph_task scheduled tasks
Prasad scheduled a DELETE task by inserting a row into sysadmin's ph_task, but nothing appeared in ph_run and exectask() returned -1. After checks of tk_enable, tk_dbs (John Miller noted the task runs in sysadmin, so set tk_dbs to the target database rather than using db:table syntax) and onstat -g dbc, the output showed "Database Scheduler is Disabled" — the real cause. Removing $INFORMIXDIR/etc/sysadmin/stop and restarting the server (or running task("scheduler start")) fixed it. A follow-up on date arithmetic was answered with: datein < CURRENT YEAR TO SECOND - 15 UNITS DAY, which also avoids month-subtraction errors.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I am using the ph_task table to schedule a task to delete data from a table. I have a question around this. 1. In the event of no data present in the table how to verify if this scheduled task actually got triggered? Regards, Prasad
Every time a task is execute a entry is automatically inserted into the ph_run table. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) (Embedded image moved to file: pic21942.gif) ids-bounces@iiug.org wrote on 12/14/2011 01:18:11 AM: > From: "PRASAD KATTI" <prkatti@cisco.com> > To: ids@iiug.org > Date: 12/14/2011 01:18 AM > Subject: ph_task scheduled tasks [25604] > Sent by: ids-bounces@iiug.org > > Hi, > > I am using the ph_task table to schedule a task to delete data from > a table. I > have a question around this. > > 1. In the event of no data present in the table how to verify if this > scheduled task actually got triggered? > > Regards, > Prasad > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Apparently I am not finding the entry in ph_run table. -Prasad
BTW, this is what I inserted into the ph_task table.
==
insert into ph_task
(
tk_name,
tk_type,
tk_group,
tk_description,
tk_execute,
tk_start_time,tk_frequency
)
values
(
"Data Retention ",
"TASK",
"TABLES",
"Data Retention for Table",
"DELETE FROM abc:xyz where dateinactive < ((CURRENT YEAR TO SECOND) - INTERVAL
(1) MONTH to MONTH )",
"16:00:00",
"1 0:00:00"
)
==
Please let me know if some thing is wrong about this.
Regards,
Prasad
There will be a record in the ph_run table when it completes. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 14, 2011 at 4:18 AM, PRASAD KATTI <prkatti@cisco.com> wrote: > Hi, > > I am using the ph_task table to schedule a task to delete data from a > table. I > have a question around this. > > 1. In the event of no data present in the table how to verify if this > scheduled task actually got triggered? > > Regards, > Prasad > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340a51c67c6a04b40b9efa
Questions: - Are there any recent records in ph_run" - Does your ph_task record have the tk_enabled flag set to 't'? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 14, 2011 at 4:58 AM, PRASAD KATTI <prkatti@cisco.com> wrote: > Apparently I am not finding the entry in ph_run table. > > -Prasad > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3b9d4d268ad704b40ba541
You have to set tk_enable to 't' and IB that you have to set a tk_stop time
and you may have to set the days you want it to run by setting the
tk_monday etc flags (not sure about this last one).
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Dec 14, 2011 at 5:38 AM, PRASAD KATTI <prkatti@cisco.com> wrote:
> BTW, this is what I inserted into the ph_task table.
> ==
> insert into ph_task
> (
> tk_name,
> tk_type,
> tk_group,
> tk_description,
> tk_execute,
> tk_start_time,> tk_frequency
> )
> values
> (
> "Data Retention ",
> "TASK",
> "TABLES",
> "Data Retention for Table",
> "DELETE FROM abc:xyz where dateinactive < ((CURRENT YEAR TO SECOND) -
> INTERVAL
> (1) MONTH to MONTH )",
> "16:00:00",
> "1 0:00:00"
> )
> ==
>
> Please let me know if some thing is wrong about this.
>
> Regards,
> Prasad
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f3b9d4d3f2bc004b40bb10c
Thanks for all your responses. All the values that you mentioned have been set appropriately. Regards, Prasad Katti
I am guessing the problem is the database name. It is attempting to
run this query with the current database being sysadmin. If the
logging mode and locale does not match database "abc" then the
distributed sql will fail. You should check onstat -g dbc
and or the online.log for errors after this task runs.
To correct this set the current database to "abc" by changing
the tk_dbs = "abc"
For testing you can manually force this task to run by
using the function exectask().
execute function exectask("Data Retention ");
insert into ph_task
(
tk_name,
tk_type,
tk_group,
tk_description,
tk_dbs,
tk_execute,
tk_start_time,tk_frequency
)
values
(
"Data Retention ",
"TASK",
"TABLES",
"Data Retention for Table",
"abc",
"DELETE FROM xyz where dateinactive < ((CURRENT YEAR TO SECOND) - INTERVAL
(1) MONTH to MONTH )",
"16:00:00",
"1 0:00:00"
)
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
(Embedded image moved to file: pic52580.gif)
ids-bounces@iiug.org wrote on 12/14/2011 02:38:48 AM:
> From: "PRASAD KATTI" <prkatti@cisco.com>
> To: ids@iiug.org
> Date: 12/14/2011 02:39 AM
> Subject: Re: ph_task scheduled tasks [25607]
> Sent by: ids-bounces@iiug.org
>
> BTW, this is what I inserted into the ph_task table.
> ==
> insert into ph_task
> (
> tk_name,
> tk_type,
> tk_group,
> tk_description,
> tk_execute,
> tk_start_time,> tk_frequency
> )
> values
> (
> "Data Retention ",
> "TASK",
> "TABLES",
> "Data Retention for Table",
> "DELETE FROM abc:xyz where dateinactive < ((CURRENT YEAR TO SECOND)
> - INTERVAL
> (1) MONTH to MONTH )",
> "16:00:00",
> "1 0:00:00"
> )
> ==
>
> Please let me know if some thing is wrong about this.
>
> Regards,
> Prasad
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
John,
I tried the following.
==
insert into ph_task
(
tk_name,
tk_type,
tk_group,
tk_description,
tk_execute,
tk_dbs,
tk_start_time,tk_frequency
)
values
(
"DRDL1",
"TASK",
"TABLES",
"Data Retention",
"DELETE FROM test where datein < ((CURRENT YEAR TO SECOND) - INTERVAL (1)
MONTH to MONTH )",
"db_cra",
"12:00:00",
"1 0:00:00"
)
==
1. After the time went past 12:00 pm, I did not see any entry in ph_run.
2. When execute 'execute function exectask("DRDL1")', I get the following
output:
(expression)
-1
Regards,
Prasad
Just a few things:
1. I am assuming you are using version 11.70. If you are using another
version please let me know.
2. After inserting the task it would be nice to see what the system set
for tk_next_execution, in fact
the entire ph_task row would be nice.
3. Did you look at onstat -g dbc to see if any errors were reported.
You testcase seems to work for me, Here is what I did.
drop database if exists db_cra;
create database db_cra with log;
create table test (c1 serial, datein datetime year to second);
insert into test select 0, CURRENT from systables;
database sysadmin;
delete from ph_task where tk_name = "DRDL1";
insert into ph_task
(
tk_name,
tk_type,
tk_group,
tk_description,
tk_execute,
tk_dbs,
tk_start_time,tk_frequency
)
values
(
"DRDL1",
"TASK",
"TABLES",
"Data Retention",
"DELETE FROM test where datein < ((CURRENT YEAR TO SECOND) - INTERVAL (1)
MONTH to MONTH )",
"db_cra",
"12:00:00",
"1 0:00:00"
) ;
execute function exectask("DRDL1");
select *
from ph_task , ph_run
where tk_name = "DRDL1"
and tk_id = run_task_id
and run_task_seq = tk_sequence;
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
(Embedded image moved to file: pic03559.gif)
ids-bounces@iiug.org wrote on 12/14/2011 10:48:17 PM:
> From: "PRASAD KATTI" <prkatti@cisco.com>
> To: ids@iiug.org
> Date: 12/14/2011 10:49 PM
> Subject: Re: ph_task scheduled tasks [25617]
> Sent by: ids-bounces@iiug.org
>
> John,
>
> I tried the following.
> ==
>
> insert into ph_task
> (
> tk_name,
> tk_type,
> tk_group,
> tk_description,
> tk_execute,
> tk_dbs,
> tk_start_time,> tk_frequency
> )
> values
> (
> "DRDL1",
> "TASK",
> "TABLES",
> "Data Retention",
> "DELETE FROM test where datein < ((CURRENT YEAR TO SECOND) - INTERVAL (1)
> MONTH to MONTH )",
> "db_cra",
> "12:00:00",
> "1 0:00:00"
> )
> ==
>
> 1. After the time went past 12:00 pm, I did not see any entry in ph_run.
> 2. When execute 'execute function exectask("DRDL1")', I get the following
> output:
>
> (expression)
>
> -1
>
> Regards,
> Prasad
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
onstat -g dbc output is "Database Scheduler is Disabled.". How can this beenabled?
-Prasad
onstat -g dbc output is "Database Scheduler is Disabled.". Does it need to beenabled for ph_task tasks to work? How can this be enabled?
-Prasad
1. Using 11.5 IDS
2. ph_task row is like this:
tk_id 18
tk_name DRDL1
tk_description Data Retention for List Table
tk_type TASK
tk_sequence 0
tk_result_table
tk_create
tk_dbs abc
tk_execute DELETE FROM DList where active='f' and datein
< ((CURRENT YEAR TO SECOND) - INTERVAL (1) MONTH to MONTH
)
tk_delete 0 01:00:00
tk_start_time 12:00:00
tk_stop_time 19:00:00
tk_frequency 1 00:00:00
tk_next_execution 2011-12-15 11:54:47
tk_total_executio+ 0
tk_total_time 0.00
tk_monday t
tk_tuesday t
tk_wednesday t
tk_thursday t
tk_friday t
tk_saturday t
tk_sunday t
tk_attributes 0
tk_group TABLES
tk_enable t
tk_priority 0
3. onstat -g dbc says "Database Scheduler is disabled".
-Prasad
Without the database scheduler on, no task from the ph_task table will
execute. To enable the scheduler do the following:
1. If the following file exists $INFORMIXDIR/etc/sysadmin/stop you will
need
to remove it.
2. You can then shutdown the database server and restart it and the
database scheduler
should be running
OR
in sysadmin database execute the following command
execute function task("scheduler start");
This will dynamically start the database scheduler threads without
requiring
a restart of the database server.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
(Embedded image moved to file: pic43248.gif)
ids-bounces@iiug.org wrote on 12/15/2011 01:15:24 AM:
> From: "PRASAD KATTI" <prkatti@cisco.com>
> To: ids@iiug.org
> Date: 12/15/2011 01:16 AM
> Subject: Re: ph_task scheduled tasks [25621]
> Sent by: ids-bounces@iiug.org
>
> 1. Using 11.5 IDS
> 2. ph_task row is like this:
>
> tk_id 18
> tk_name DRDL1
> tk_description Data Retention for List Table
> tk_type TASK
> tk_sequence 0
> tk_result_table
> tk_create
> tk_dbs abc
> tk_execute DELETE FROM DList where active='f' and datein
>
> < ((CURRENT YEAR TO SECOND) - INTERVAL (1) MONTH to MONTH
>
> )
> tk_delete 0 01:00:00
> tk_start_time 12:00:00
> tk_stop_time 19:00:00
> tk_frequency 1 00:00:00
> tk_next_execution 2011-12-15 11:54:47
> tk_total_executio+ 0
> tk_total_time 0.00
> tk_monday t
> tk_tuesday t
> tk_wednesday t
> tk_thursday t
> tk_friday t
> tk_saturday t
> tk_sunday t
> tk_attributes 0
> tk_group TABLES
> tk_enable t
> tk_priority 0
>
> 3. onstat -g dbc says "Database Scheduler is disabled".
>
> -Prasad
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Thanks John. I took the first approach and managed to get this working. appreciate your inputs. Regards Prasad Katti
The query that I am using
DELETE FROM DL where datein < ((CURRENT YEAR TO SECOND) - INTERVAL (1) MONTHto MONTH ) retains data for the last 1 month. I am trying to get it to work
for the last 15 days data. but in vain.
Can somebody throw in some ideas?
Regards,
Prasad
would this query be the right one?
DELETE FROM DL where datein < ((CURRENT YEAR TO SECOND) - INTERVAL (15) DAY to
DAY )
Regards,
Prasad
where datein < current year to second - 15 units day
j.
On Dec 23, 2011, at 3:48 AM, PRASAD KATTI wrote:
> would this query be the right one?=20
>=20
> DELETE FROM DL where datein < ((CURRENT YEAR TO SECOND) - INTERVAL =
(15) DAY to=20> DAY )=20
>=20
> Regards,=20
> Prasad=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
On Thu, Dec 22, 2011 at 23:48, PRASAD KATTI <prkatti@cisco.com> wrote:
> The query that I am using
>
> DELETE FROM DL where datein < ((CURRENT YEAR TO SECOND) - INTERVAL (1)> MONTH
> to MONTH ) retains data for the last 1 month. I am trying to get it to work
> for the last 15 days data. but in vain.
>
> Can somebody throw in some ideas?
>
This expression is prone to failure from the 29th of the month onwards. If
you subtract 1 month from 31st March, you get an error; likewise 31st May,
and 30th March, and (usually - but not this coming year) on 29th March.
DELETE FROM dl
WHERE datein < (CURRENT YEAR TO SECOND - 15 UNITS DAY);
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--14dae934097d9c375004b4c4b122
On Fri, Dec 23, 2011 at 00:48, PRASAD KATTI <prkatti@cisco.com> wrote:
> would this query be the right one?
>
> DELETE FROM DL where datein < ((CURRENT YEAR TO SECOND) - INTERVAL (15)> DAY to
> DAY )
>
No-one could tell what your question is without looking at some other
information - you need to make your postings self-contained.
Since I just sent a response to your previous question, I can say "Yes,
this would work; it would delete records where the datein value is more
than 15 days old". But wouldn't it be as simple to create a simple test
table - possibly even a temp table - and insert some rows, and test the
statement on that?
And you can also test a DELETE by changing 'DELETE FROM' into 'SELECT *
FROM'.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--90e6ba6e836c67b86204b4c4bc1a