sysadmin:ph_alert cleanup
Frank's IDS 11.50.FC8 sysadmin database had ph_alert growing to ~1.7M rows dating back 18 months despite the built-in "Alert Cleanup" task being enabled. Replies pointed him to ph_task and the AlertCleanup() SPL in $INFORMIXDIR/etc/sysadmin/sch_tasks.sql, which uses ph_threshold's ALERT HISTORY RETENTION value. John Miller identified it as upgrade defect IC85171: the SPL's inner join to ph_run skipped alerts with no matching run row. Fix: recreate AlertCleanup() in sysadmin using an OUTER join to ph_run (editing sch_tasks.sql only helps new instances).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
HI, IDS 11.50 FC8 Looks IDS does not cleanup ph_alert table automatically (correct? ) and it grows quite bit and takes up disk spaces... Any suggestions on its cleanup job? Thanks, Frank --e89a8f6464eb9aac7804ccad1b31
You should have an "alert cleanup" task... is it enabled? Or simply not there? Regards On Mon, Oct 22, 2012 at 11:09 PM, FRANK <yunyaoqu@gmail.com> wrote: > HI, > > IDS 11.50 FC8 > > Looks IDS does not cleanup ph_alert table automatically (correct? ) and it > grows quite bit and takes up disk spaces... > > Any suggestions on its cleanup job? > > Thanks, > Frank > > --e89a8f6464eb9aac7804ccad1b31 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf30334679d7c32304ccae610a
Fernando, We do not think we ever touched that task since installation..... Where is it ? and how to enable it? Thanks, Frank On Mon, Oct 22, 2012 at 7:40 PM, Fernando Nunes <domusonline@gmail.com>wrote: > You should have an "alert cleanup" task... is it enabled? Or simply not > there? > Regards > > On Mon, Oct 22, 2012 at 11:09 PM, FRANK <yunyaoqu@gmail.com> wrote: > > > HI, > > > > IDS 11.50 FC8 > > > > Looks IDS does not cleanup ph_alert table automatically (correct? ) and > it > > grows quite bit and takes up disk spaces... > > > > Any suggestions on its cleanup job? > > > > Thanks, > > Frank > > > > --e89a8f6464eb9aac7804ccad1b31 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --20cf30334679d7c32304ccae610a > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d0402ac45058cda04ccbaf5b4
Original post: Fernando, We do not think we ever touched that task since installation..... Where is it ? and how to enable it? Thanks, Frank Response: In the sysadmin database you can look at the ph_task table to see all the defined tasks... you can just do a select * from ph_task. If the tk_enable field is set to 't' it's enabled, if it is set to 'f' it isn't. I believe you are looking for the task where tk_name = "Alert Cleanup"...which calls a spl AlertCleanup() which you can see the code for in $INFORMIXDIR/etc/sysadmin/sch_tasks.sql. Jacques Renaut IBM Informix Advanced Support APD Team
Hi, Jacques,
It was/is enabled,
tk_id 14
tk_name Alert Cleanup
tk_description Remove all old alert entries from the system.
tk_type TASK
tk_sequence 546
tk_result_table
tk_create
tk_dbs sysadmin
tk_execute AlertCleanup
tk_delete 0 01:00:00
tk_start_time 02:00:00
tk_stop_time
tk_frequency 1 00:00:00
tk_next_execution 2012-10-23 17:56:33
tk_total_executio+ 546
tk_total_time 106.2778002257
tk_monday t
tk_tuesday t
tk_wednesday t
tk_thursday t
tk_friday t
tk_saturday t
tk_sunday t
tk_attributes 404
tk_group SERVER
tk_enable t
tk_priority 0
The following are the content info of the table, you can see its
accumulation.
select count(*) from ph_alert;(count(*)) 1739652
select alert_type, count(*) from ph_alert group by 1;alert_type (count(*))
WARNING 1665166
INFO 74486
select min (alert_time), max(alert_time) from ph_alert;
(min) (max)
2011-04-26 17:56:34 2012-10-23 15:45:17
Somewhere to adjust its cleanup criteria?
Thanks,
Frank
On Tue, Oct 23, 2012 at 11:13 AM, JACQUES RENAUT <jrenaut@us.ibm.com> wrote:
> Original post:
>
> Fernando,
>
> We do not think we ever touched that task since installation..... Where is
> it ? and how to enable it?
>
> Thanks,
> Frank
>
> Response:
>
> In the sysadmin database you can look at the ph_task table to see all the
> defined tasks... you can just do a select * from ph_task. If the tk_enable
> field is set to 't' it's enabled, if it is set to 'f' it isn't. I believe
> you
> are looking for the task where tk_name = "Alert Cleanup"...which calls a
> spl
> AlertCleanup() which you can see the code for in
> $INFORMIXDIR/etc/sysadmin/sch_tasks.sql.
>
> Jacques Renaut
> IBM Informix Advanced Support
> APD Team
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d04428c6ef5a58804ccbbfa6c
Original post: Hi, Jacques, It was/is enabled, <stuff cut> Somewhere to adjust its cleanup criteria? Thanks, Frank Response: That's why in my previous reply I said it looks like it executes the SPL AlertCleanup and listed what file that function is defined in to see what the SPL is doing. It looks like in that SPL it doing some select from ph_threshold to determine some retention time period. I'm guessing you could update that table or at the very least run the queries this spl is doing by hand and see what it's doing and maybe why it isn't removing things yet. Jacques Renaut IBM Informix Advanced Support APD Team
I believe you might have encountered an upgrade defect IC85171
You can fix it very easily by upgrading the procedure in the sysadmin
database
called AlertCleanup() show below. The change in the procedure is to do
an OUTER join between ph_alert and ph_run tables.
CREATE FUNCTION AlertCleanup(task_id INTEGER, ID INTEGER)
RETURNING INTEGER
DEFINE cur_run_id LIKE ph_run.run_id;
DEFINE cur_id LIKE ph_alert.id;
DEFINE count INTEGER;
LET count =0;
FOREACH SELECT id, run_id
INTO cur_id, cur_run_id
FROM ph_alert, OUTER ph_run
WHERE ph_alert.alert_task_id = ph_run.run_task_id
AND ph_alert.alert_task_seq = ph_run.run_task_seq
AND alert_time < (
SELECT current - value::INTERVAL DAY to SECOND
FROM ph_threshold
WHERE name = 'ALERT HISTORY RETENTION' )
DELETE FROM ph_run where run_id = cur_run_id;
DELETE FROM ph_alert where id = cur_id;
LET count = count + 1;
END FOREACH
RETURN count;
END FUNCTION;
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 10/23/2012 08:54:13 AM:
> From: "FRANK" <yunyaoqu@gmail.com>
> To: ids@iiug.org,
> Date: 10/23/2012 08:57 AM
> Subject: Re: sysadmin:ph_alert cleanup [28614]
> Sent by: ids-bounces@iiug.org
>
> Hi, Jacques,
>
> It was/is enabled,
>
> tk_id 14
> tk_name Alert Cleanup
> tk_description Remove all old alert entries from the system.
> tk_type TASK
> tk_sequence 546
> tk_result_table
> tk_create
> tk_dbs sysadmin
> tk_execute AlertCleanup
> tk_delete 0 01:00:00
> tk_start_time 02:00:00
> tk_stop_time
> tk_frequency 1 00:00:00
> tk_next_execution 2012-10-23 17:56:33
> tk_total_executio+ 546
> tk_total_time 106.2778002257
> tk_monday t
> tk_tuesday t
> tk_wednesday t
> tk_thursday t
> tk_friday t
> tk_saturday t
> tk_sunday t
> tk_attributes 404
> tk_group SERVER
> tk_enable t
> tk_priority 0
>
> The following are the content info of the table, you can see its
> accumulation.
>
> select count(*) from ph_alert;> (count(*)) 1739652
>
> select alert_type, count(*) from ph_alert group by 1;> alert_type (count(*))
> WARNING 1665166
> INFO 74486
>
> select min (alert_time), max(alert_time) from ph_alert;
> (min) (max)
> 2011-04-26 17:56:34 2012-10-23 15:45:17
>
> Somewhere to adjust its cleanup criteria?
>
> Thanks,
> Frank
>
> On Tue, Oct 23, 2012 at 11:13 AM, JACQUES RENAUT <jrenaut@us.ibm.com>
wrote:
>
> > Original post:
> >
> > Fernando,
> >
> > We do not think we ever touched that task since installation..... Where
is
> > it ? and how to enable it?
> >
> > Thanks,
> > Frank
> >
> > Response:
> >
> > In the sysadmin database you can look at the ph_task table to see all
the
> > defined tasks... you can just do a select * from ph_task. If the
tk_enable
> > field is set to 't' it's enabled, if it is set to 'f' it isn't. I
believe
> > you
> > are looking for the task where tk_name = "Alert Cleanup"...which calls
a
> > spl
> > AlertCleanup() which you can see the code for in
> > $INFORMIXDIR/etc/sysadmin/sch_tasks.sql.
> >
> > Jacques Renaut
> > IBM Informix Advanced Support
> > APD Team
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --f46d04428c6ef5a58804ccbbfa6c
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
John,
This is exactly I just found,
You can see,
select count(alert_task_seq)
from ph_alert
where alert_task_seq not in (
select run_task_seq
from ph_run);
(count) 1240088
So, the original Equal Join in AlertCleanup(task_id INTEGER, ID INTEGER)
will miss the above rows.
So, I just need manually add a " OUTER" in the FROM clause of the
AlertCleanup() function , correct?
Thanks,
Frank
On Tue, Oct 23, 2012 at 1:15 PM, John Miller iii <miller3@us.ibm.com> wrote:
> I believe you might have encountered an upgrade defect IC85171
>
> You can fix it very easily by upgrading the procedure in the sysadmin
> database
> called AlertCleanup() show below. The change in the procedure is to do
> an OUTER join between ph_alert and ph_run tables.
>
> CREATE FUNCTION AlertCleanup(task_id INTEGER, ID INTEGER)>
> RETURNING INTEGER
>
> DEFINE cur_run_id LIKE ph_run.run_id;
> DEFINE cur_id LIKE ph_alert.id;
> DEFINE count INTEGER;
>
> LET count =0;
>
> FOREACH SELECT id, run_id
>
> INTO cur_id, cur_run_id
>
> FROM ph_alert, OUTER ph_run
>
> WHERE ph_alert.alert_task_id = ph_run.run_task_id
>
> AND ph_alert.alert_task_seq = ph_run.run_task_seq
>
> AND alert_time < (
>
> SELECT current - value::INTERVAL DAY to SECOND
>
> FROM ph_threshold
>
> WHERE name = 'ALERT HISTORY RETENTION' )
>
> DELETE FROM ph_run where run_id = cur_run_id;>
> DELETE FROM ph_alert where id = cur_id;>
> LET count = count + 1;
>
> END FOREACH
>
> RETURN count;
>
> END FUNCTION;
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 10/23/2012 08:54:13 AM:
>
> > From: "FRANK" <yunyaoqu@gmail.com>
> > To: ids@iiug.org,
> > Date: 10/23/2012 08:57 AM
> > Subject: Re: sysadmin:ph_alert cleanup [28614]
> > Sent by: ids-bounces@iiug.org
> >
> > Hi, Jacques,
> >
> > It was/is enabled,
> >
> > tk_id 14
> > tk_name Alert Cleanup
> > tk_description Remove all old alert entries from the system.
> > tk_type TASK
> > tk_sequence 546
> > tk_result_table
> > tk_create
> > tk_dbs sysadmin
> > tk_execute AlertCleanup
> > tk_delete 0 01:00:00
> > tk_start_time 02:00:00
> > tk_stop_time
> > tk_frequency 1 00:00:00
> > tk_next_execution 2012-10-23 17:56:33
> > tk_total_executio+ 546
> > tk_total_time 106.2778002257
> > tk_monday t
> > tk_tuesday t
> > tk_wednesday t
> > tk_thursday t
> > tk_friday t
> > tk_saturday t
> > tk_sunday t
> > tk_attributes 404
> > tk_group SERVER
> > tk_enable t
> > tk_priority 0
> >
> > The following are the content info of the table, you can see its
> > accumulation.
> >
> > select count(*) from ph_alert;> > (count(*)) 1739652
> >
> > select alert_type, count(*) from ph_alert group by 1;> > alert_type (count(*))
> > WARNING 1665166
> > INFO 74486
> >
> > select min (alert_time), max(alert_time) from ph_alert;
> > (min) (max)
> > 2011-04-26 17:56:34 2012-10-23 15:45:17
> >
> > Somewhere to adjust its cleanup criteria?
> >
> > Thanks,
> > Frank
> >
> > On Tue, Oct 23, 2012 at 11:13 AM, JACQUES RENAUT <jrenaut@us.ibm.com>
> wrote:
> >
> > > Original post:
> > >
> > > Fernando,
> > >
> > > We do not think we ever touched that task since installation..... Where
> is
> > > it ? and how to enable it?
> > >
> > > Thanks,
> > > Frank
> > >
> > > Response:
> > >
> > > In the sysadmin database you can look at the ph_task table to see all
> the
> > > defined tasks... you can just do a select * from ph_task. If the
> tk_enable
> > > field is set to 't' it's enabled, if it is set to 'f' it isn't. I
> believe
> > > you
> > > are looking for the task where tk_name = "Alert Cleanup"...which calls
> a
> > > spl
> > > AlertCleanup() which you can see the code for in
> > > $INFORMIXDIR/etc/sysadmin/sch_tasks.sql.
> > >
> > > Jacques Renaut
> > > IBM Informix Advanced Support
> > > APD Team
> > >
> > >
> > >
> > >
> >
>
>
>
*******************************************************************************
>
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --f46d04428c6ef5a58804ccbbfa6c
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e8f6cdf1d9604ccbd7b17
John,
Which do recommend?
(1) drop and recreate the function AlertCleanup in sysadmin database,
or
(2) manually change the $INFORMIXDIR /etc/sysadmin /sch_tasks.sql
Thanks,
Frank
On Tue, Oct 23, 2012 at 1:41 PM, FRANK <yunyaoqu@gmail.com> wrote:
> John,
>
> This is exactly I just found,
>
> You can see,
>
> select count(alert_task_seq)
> from ph_alert
> where alert_task_seq not in (
> select run_task_seq
> from ph_run);>
> (count) 1240088
>
> So, the original Equal Join in AlertCleanup(task_id INTEGER, ID INTEGER)
> will miss the above rows.
>
> So, I just need manually add a " OUTER" in the FROM clause of the
> AlertCleanup() function , correct?
>
> Thanks,
> Frank
>
> On Tue, Oct 23, 2012 at 1:15 PM, John Miller iii <miller3@us.ibm.com>
> wrote:
>
> > I believe you might have encountered an upgrade defect IC85171
> >
> > You can fix it very easily by upgrading the procedure in the sysadmin
> > database
> > called AlertCleanup() show below. The change in the procedure is to do
> > an OUTER join between ph_alert and ph_run tables.
> >
> > CREATE FUNCTION AlertCleanup(task_id INTEGER, ID INTEGER)> >
> > RETURNING INTEGER
> >
> > DEFINE cur_run_id LIKE ph_run.run_id;
> > DEFINE cur_id LIKE ph_alert.id;
> > DEFINE count INTEGER;
> >
> > LET count =0;
> >
> > FOREACH SELECT id, run_id
> >
> > INTO cur_id, cur_run_id
> >
> > FROM ph_alert, OUTER ph_run
> >
> > WHERE ph_alert.alert_task_id = ph_run.run_task_id
> >
> > AND ph_alert.alert_task_seq = ph_run.run_task_seq
> >
> > AND alert_time < (
> >
> > SELECT current - value::INTERVAL DAY to SECOND
> >
> > FROM ph_threshold
> >
> > WHERE name = 'ALERT HISTORY RETENTION' )
> >
> > DELETE FROM ph_run where run_id = cur_run_id;> >
> > DELETE FROM ph_alert where id = cur_id;> >
> > LET count = count + 1;
> >
> > END FOREACH
> >
> > RETURN count;
> >
> > END FUNCTION;
> >
> > John F. Miller III
> > STSM, Embedability Architect
> > miller3@us.ibm.com
> > 503-578-5645
> > IBM Informix Dynamic Server (IDS)
> >
> > ids-bounces@iiug.org wrote on 10/23/2012 08:54:13 AM:
> >
> > > From: "FRANK" <yunyaoqu@gmail.com>
> > > To: ids@iiug.org,
> > > Date: 10/23/2012 08:57 AM
> > > Subject: Re: sysadmin:ph_alert cleanup [28614]
> > > Sent by: ids-bounces@iiug.org
> > >
> > > Hi, Jacques,
> > >
> > > It was/is enabled,
> > >
> > > tk_id 14
> > > tk_name Alert Cleanup
> > > tk_description Remove all old alert entries from the system.
> > > tk_type TASK
> > > tk_sequence 546
> > > tk_result_table
> > > tk_create
> > > tk_dbs sysadmin
> > > tk_execute AlertCleanup
> > > tk_delete 0 01:00:00
> > > tk_start_time 02:00:00
> > > tk_stop_time
> > > tk_frequency 1 00:00:00
> > > tk_next_execution 2012-10-23 17:56:33
> > > tk_total_executio+ 546
> > > tk_total_time 106.2778002257
> > > tk_monday t
> > > tk_tuesday t
> > > tk_wednesday t
> > > tk_thursday t
> > > tk_friday t
> > > tk_saturday t
> > > tk_sunday t
> > > tk_attributes 404
> > > tk_group SERVER
> > > tk_enable t
> > > tk_priority 0
> > >
> > > The following are the content info of the table, you can see its
> > > accumulation.
> > >
> > > select count(*) from ph_alert;> > > (count(*)) 1739652
> > >
> > > select alert_type, count(*) from ph_alert group by 1;> > > alert_type (count(*))
> > > WARNING 1665166
> > > INFO 74486
> > >
> > > select min (alert_time), max(alert_time) from ph_alert;
> > > (min) (max)
> > > 2011-04-26 17:56:34 2012-10-23 15:45:17
> > >
> > > Somewhere to adjust its cleanup criteria?
> > >
> > > Thanks,
> > > Frank
> > >
> > > On Tue, Oct 23, 2012 at 11:13 AM, JACQUES RENAUT <jrenaut@us.ibm.com>
> > wrote:
> > >
> > > > Original post:
> > > >
> > > > Fernando,
> > > >
> > > > We do not think we ever touched that task since installation.....
> Where
> > is
> > > > it ? and how to enable it?
> > > >
> > > > Thanks,
> > > > Frank
> > > >
> > > > Response:
> > > >
> > > > In the sysadmin database you can look at the ph_task table to see all
> > the
> > > > defined tasks... you can just do a select * from ph_task. If the
> > tk_enable
> > > > field is set to 't' it's enabled, if it is set to 'f' it isn't. I
> > believe
> > > > you
> > > > are looking for the task where tk_name = "Alert Cleanup"...which
> calls
> > a
> > > > spl
> > > > AlertCleanup() which you can see the code for in
> > > > $INFORMIXDIR/etc/sysadmin/sch_tasks.sql.
> > > >
> > > > Jacques Renaut
> > > > IBM Informix Advanced Support
> > > > APD Team
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
> >
>
>
*******************************************************************************
> >
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --f46d04428c6ef5a58804ccbbfa6c
> > >
> > >
> > >
> >
> >
> >
>
>
*******************************************************************************
> >
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --90e6ba6e8f6cdf1d9604ccbd7b17
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f2344e9cc52db04ccbdab74
Frank:
Option #1 will take effect immediately after recreating the function,
while option #2 will be useful if you setup a new instance. If
you are not planning re-initializing your system I would just re-create
the function.
ids-bounces@iiug.org wrote on 10/23/2012 10:55:16 AM:
> From: "FRANK" <yunyaoqu@gmail.com>
> To: ids@iiug.org,
> Date: 10/23/2012 05:42 PM
> Subject: Re: sysadmin:ph_alert cleanup [28619]
> Sent by: ids-bounces@iiug.org
>
> John,
>
> Which do recommend?
>
> (1) drop and recreate the function AlertCleanup in sysadmin database,
>
> or
>
> (2) manually change the $INFORMIXDIR /etc/sysadmin /sch_tasks.sql
>
> Thanks,
> Frank
>
> On Tue, Oct 23, 2012 at 1:41 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > John,
> >
> > This is exactly I just found,
> >
> > You can see,
> >
> > select count(alert_task_seq)
> > from ph_alert
> > where alert_task_seq not in (
> > select run_task_seq
> > from ph_run);> >
> > (count) 1240088
> >
> > So, the original Equal Join in AlertCleanup(task_id INTEGER, ID
INTEGER)
> > will miss the above rows.
> >
> > So, I just need manually add a " OUTER" in the FROM clause of the
> > AlertCleanup() function , correct?
> >
> > Thanks,
> > Frank
> >
> > On Tue, Oct 23, 2012 at 1:15 PM, John Miller iii <miller3@us.ibm.com>
> > wrote:
> >
> > > I believe you might have encountered an upgrade defect IC85171
> > >
> > > You can fix it very easily by upgrading the procedure in the sysadmin
> > > database
> > > called AlertCleanup() show below. The change in the procedure is to
do
> > > an OUTER join between ph_alert and ph_run tables.
> > >
> > > CREATE FUNCTION AlertCleanup(task_id INTEGER, ID INTEGER)> > >
> > > RETURNING INTEGER
> > >
> > > DEFINE cur_run_id LIKE ph_run.run_id;
> > > DEFINE cur_id LIKE ph_alert.id;
> > > DEFINE count INTEGER;
> > >
> > > LET count =0;
> > >
> > > FOREACH SELECT id, run_id
> > >
> > > INTO cur_id, cur_run_id
> > >
> > > FROM ph_alert, OUTER ph_run
> > >
> > > WHERE ph_alert.alert_task_id = ph_run.run_task_id
> > >
> > > AND ph_alert.alert_task_seq = ph_run.run_task_seq
> > >
> > > AND alert_time < (
> > >
> > > SELECT current - value::INTERVAL DAY to SECOND
> > >
> > > FROM ph_threshold
> > >
> > > WHERE name = 'ALERT HISTORY RETENTION' )
> > >
> > > DELETE FROM ph_run where run_id = cur_run_id;> > >
> > > DELETE FROM ph_alert where id = cur_id;> > >
> > > LET count = count + 1;
> > >
> > > END FOREACH
> > >
> > > RETURN count;
> > >
> > > END FUNCTION;
> > >
> > > John F. Miller III
> > > STSM, Embedability Architect
> > > miller3@us.ibm.com
> > > 503-578-5645
> > > IBM Informix Dynamic Server (IDS)
> > >
> > > ids-bounces@iiug.org wrote on 10/23/2012 08:54:13 AM:
> > >
> > > > From: "FRANK" <yunyaoqu@gmail.com>
> > > > To: ids@iiug.org,
> > > > Date: 10/23/2012 08:57 AM
> > > > Subject: Re: sysadmin:ph_alert cleanup [28614]
> > > > Sent by: ids-bounces@iiug.org
> > > >
> > > > Hi, Jacques,
> > > >
> > > > It was/is enabled,
> > > >
> > > > tk_id 14
> > > > tk_name Alert Cleanup
> > > > tk_description Remove all old alert entries from the system.
> > > > tk_type TASK
> > > > tk_sequence 546
> > > > tk_result_table
> > > > tk_create
> > > > tk_dbs sysadmin
> > > > tk_execute AlertCleanup
> > > > tk_delete 0 01:00:00
> > > > tk_start_time 02:00:00
> > > > tk_stop_time
> > > > tk_frequency 1 00:00:00
> > > > tk_next_execution 2012-10-23 17:56:33
> > > > tk_total_executio+ 546
> > > > tk_total_time 106.2778002257
> > > > tk_monday t
> > > > tk_tuesday t
> > > > tk_wednesday t
> > > > tk_thursday t
> > > > tk_friday t
> > > > tk_saturday t
> > > > tk_sunday t
> > > > tk_attributes 404
> > > > tk_group SERVER
> > > > tk_enable t
> > > > tk_priority 0
> > > >
> > > > The following are the content info of the table, you can see its
> > > > accumulation.
> > > >
> > > > select count(*) from ph_alert;> > > > (count(*)) 1739652
> > > >
> > > > select alert_type, count(*) from ph_alert group by 1;> > > > alert_type (count(*))
> > > > WARNING 1665166
> > > > INFO 74486
> > > >
> > > > select min (alert_time), max(alert_time) from ph_alert;
> > > > (min) (max)
> > > > 2011-04-26 17:56:34 2012-10-23 15:45:17
> > > >
> > > > Somewhere to adjust its cleanup criteria?
> > > >
> > > > Thanks,
> > > > Frank
> > > >
> > > > On Tue, Oct 23, 2012 at 11:13 AM, JACQUES RENAUT
<jrenaut@us.ibm.com>
> > > wrote:
> > > >
> > > > > Original post:
> > > > >
> > > > > Fernando,
> > > > >
> > > > > We do not think we ever touched that task since installation.....
> > Where
> > > is
> > > > > it ? and how to enable it?
> > > > >
> > > > > Thanks,
> > > > > Frank
> > > > >
> > > > > Response:
> > > > >
> > > > > In the sysadmin database you can look at the ph_task table to see
all
> > > the
> > > > > defined tasks... you can just do a select * from ph_task. If the
> > > tk_enable
> > > > > field is set to 't' it's enabled, if it is set to 'f' it isn't. I
> > > believe
> > > > > you
> > > > > are looking for the task where tk_name = "Alert Cleanup"...which
> > calls
> > > a
> > > > > spl
> > > > > AlertCleanup() which you can see the code for in
> > > > > $INFORMIXDIR/etc/sysadmin/sch_tasks.sql.
> > > > >
> > > > > Jacques Renaut
> > > > > IBM Informix Advanced Support
> > > > > APD Team
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > >
> > > > > Forum Note: Use "Reply" to post a response in the discussion
forum.
> > > > >
> > > > >
> > > >
> > > > --f46d04428c6ef5a58804ccbbfa6c
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > >
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > >
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --90e6ba6e8f6cdf1d9604ccbd7b17
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --e89a8f2344e9cc52db04ccbdab74
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Thanks a lot , John!
I would like to do a TRUNCATE ph_alert first before recreating the
function, what do you think? ( because I want to release its occupied
spaces, a great deal of)
Normally I am very reluctant/cautious to do any thing on a table which is
not maintained by me.
Thanks again!
Frank
On Tue, Oct 23, 2012 at 9:54 PM, John Miller iii <miller3@us.ibm.com> wrote:
> Frank:
>
> Option #1 will take effect immediately after recreating the function,
> while option #2 will be useful if you setup a new instance. If
> you are not planning re-initializing your system I would just re-create
> the function.
>
> ids-bounces@iiug.org wrote on 10/23/2012 10:55:16 AM:
>
> > From: "FRANK" <yunyaoqu@gmail.com>
> > To: ids@iiug.org,
> > Date: 10/23/2012 05:42 PM
> > Subject: Re: sysadmin:ph_alert cleanup [28619]
> > Sent by: ids-bounces@iiug.org
> >
> > John,
> >
> > Which do recommend?
> >
> > (1) drop and recreate the function AlertCleanup in sysadmin database,
> >
> > or
> >
> > (2) manually change the $INFORMIXDIR /etc/sysadmin /sch_tasks.sql
> >
> > Thanks,
> > Frank
> >
> > On Tue, Oct 23, 2012 at 1:41 PM, FRANK <yunyaoqu@gmail.com> wrote:
> >
> > > John,
> > >
> > > This is exactly I just found,
> > >
> > > You can see,
> > >
> > > select count(alert_task_seq)
> > > from ph_alert
> > > where alert_task_seq not in (
> > > select run_task_seq
> > > from ph_run);> > >
> > > (count) 1240088
> > >
> > > So, the original Equal Join in AlertCleanup(task_id INTEGER, ID
> INTEGER)
> > > will miss the above rows.
> > >
> > > So, I just need manually add a " OUTER" in the FROM clause of the
> > > AlertCleanup() function , correct?
> > >
> > > Thanks,
> > > Frank
> > >
> > > On Tue, Oct 23, 2012 at 1:15 PM, John Miller iii <miller3@us.ibm.com>
> > > wrote:
> > >
> > > > I believe you might have encountered an upgrade defect IC85171
> > > >
> > > > You can fix it very easily by upgrading the procedure in the sysadmin
>
> > > > database
> > > > called AlertCleanup() show below. The change in the procedure is to
> do
> > > > an OUTER join between ph_alert and ph_run tables.
> > > >
> > > > CREATE FUNCTION AlertCleanup(task_id INTEGER, ID INTEGER)> > > >
> > > > RETURNING INTEGER
> > > >
> > > > DEFINE cur_run_id LIKE ph_run.run_id;
> > > > DEFINE cur_id LIKE ph_alert.id;
> > > > DEFINE count INTEGER;
> > > >
> > > > LET count =0;
> > > >
> > > > FOREACH SELECT id, run_id
> > > >
> > > > INTO cur_id, cur_run_id
> > > >
> > > > FROM ph_alert, OUTER ph_run
> > > >
> > > > WHERE ph_alert.alert_task_id = ph_run.run_task_id
> > > >
> > > > AND ph_alert.alert_task_seq = ph_run.run_task_seq
> > > >
> > > > AND alert_time < (
> > > >
> > > > SELECT current - value::INTERVAL DAY to SECOND
> > > >
> > > > FROM ph_threshold
> > > >
> > > > WHERE name = 'ALERT HISTORY RETENTION' )
> > > >
> > > > DELETE FROM ph_run where run_id = cur_run_id;> > > >
> > > > DELETE FROM ph_alert where id = cur_id;> > > >
> > > > LET count = count + 1;
> > > >
> > > > END FOREACH
> > > >
> > > > RETURN count;
> > > >
> > > > END FUNCTION;
> > > >
> > > > John F. Miller III
> > > > STSM, Embedability Architect
> > > > miller3@us.ibm.com
> > > > 503-578-5645
> > > > IBM Informix Dynamic Server (IDS)
> > > >
> > > > ids-bounces@iiug.org wrote on 10/23/2012 08:54:13 AM:
> > > >
> > > > > From: "FRANK" <yunyaoqu@gmail.com>
> > > > > To: ids@iiug.org,
> > > > > Date: 10/23/2012 08:57 AM
> > > > > Subject: Re: sysadmin:ph_alert cleanup [28614]
> > > > > Sent by: ids-bounces@iiug.org
> > > > >
> > > > > Hi, Jacques,
> > > > >
> > > > > It was/is enabled,
> > > > >
> > > > > tk_id 14
> > > > > tk_name Alert Cleanup
> > > > > tk_description Remove all old alert entries from the system.
> > > > > tk_type TASK
> > > > > tk_sequence 546
> > > > > tk_result_table
> > > > > tk_create
> > > > > tk_dbs sysadmin
> > > > > tk_execute AlertCleanup
> > > > > tk_delete 0 01:00:00
> > > > > tk_start_time 02:00:00
> > > > > tk_stop_time
> > > > > tk_frequency 1 00:00:00
> > > > > tk_next_execution 2012-10-23 17:56:33
> > > > > tk_total_executio+ 546
> > > > > tk_total_time 106.2778002257
> > > > > tk_monday t
> > > > > tk_tuesday t
> > > > > tk_wednesday t
> > > > > tk_thursday t
> > > > > tk_friday t
> > > > > tk_saturday t
> > > > > tk_sunday t
> > > > > tk_attributes 404
> > > > > tk_group SERVER
> > > > > tk_enable t
> > > > > tk_priority 0
> > > > >
> > > > > The following are the content info of the table, you can see its
> > > > > accumulation.
> > > > >
> > > > > select count(*) from ph_alert;> > > > > (count(*)) 1739652
> > > > >
> > > > > select alert_type, count(*) from ph_alert group by 1;> > > > > alert_type (count(*))
> > > > > WARNING 1665166
> > > > > INFO 74486
> > > > >
> > > > > select min (alert_time), max(alert_time) from ph_alert;
> > > > > (min) (max)
> > > > > 2011-04-26 17:56:34 2012-10-23 15:45:17
> > > > >
> > > > > Somewhere to adjust its cleanup criteria?
> > > > >
> > > > > Thanks,
> > > > > Frank
> > > > >
> > > > > On Tue, Oct 23, 2012 at 11:13 AM, JACQUES RENAUT
> <jrenaut@us.ibm.com>
> > > > wrote:
> > > > >
> > > > > > Original post:
> > > > > >
> > > > > > Fernando,
> > > > > >
> > > > > > We do not think we ever touched that task since installation.....
>
> > > Where
> > > > is
> > > > > > it ? and how to enable it?
> > > > > >
> > > > > > Thanks,
> > > > > > Frank
> > > > > >
> > > > > > Response:
> > > > > >
> > > > > > In the sysadmin database you can look at the ph_task table to see
> all
> > > > the
> > > > > > defined tasks... you can just do a select * from ph_task. If the
> > > > tk_enable
> > > > > > field is set to 't' it's enabled, if it is set to 'f' it isn't. I
>
> > > > believe
> > > > > > you
> > > > > > are looking for the task where tk_name = "Alert Cleanup"...which
> > > calls
> > > > a
> > > > > > spl
> > > > > > AlertCleanup() which you can see the code for in
> > > > > > $INFORMIXDIR/etc/sysadmin/sch_tasks.sql.
> > > > > >
> > > > > > Jacques Renaut
> > > > > > IBM Informix Advanced Support
> > > > > > APD Team
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
>
>
>
*******************************************************************************
>
> > > >
> > > > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > > >
> > > > > >
> > > > >
> > > > > --f46d04428c6ef5a58804ccbbfa6c
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
>
>
>
*******************************************************************************
>
> > > >
>
You should TRUNCATE, then use the SQL Admin API to REPACK and SHRINK that
table to return all of the unused space allocated to it to the freelist for
reuse by other tables in case the amount of data it has been collecting is
much larger than what the cleanup task would normally permit it to keep.
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, Oct 24, 2012 at 10:39 AM, FRANK <yunyaoqu@gmail.com> wrote:
> Thanks a lot , John!
>
> I would like to do a TRUNCATE ph_alert first before recreating the
> function, what do you think? ( because I want to release its occupied
> spaces, a great deal of)
>
> Normally I am very reluctant/cautious to do any thing on a table which is
> not maintained by me.
>
> Thanks again!
> Frank
>
> On Tue, Oct 23, 2012 at 9:54 PM, John Miller iii <miller3@us.ibm.com>
> wrote:
>
> > Frank:
> >
> > Option #1 will take effect immediately after recreating the function,
> > while option #2 will be useful if you setup a new instance. If
> > you are not planning re-initializing your system I would just re-create
> > the function.
> >
> > ids-bounces@iiug.org wrote on 10/23/2012 10:55:16 AM:
> >
> > > From: "FRANK" <yunyaoqu@gmail.com>
> > > To: ids@iiug.org,
> > > Date: 10/23/2012 05:42 PM
> > > Subject: Re: sysadmin:ph_alert cleanup [28619]
> > > Sent by: ids-bounces@iiug.org
> > >
> > > John,
> > >
> > > Which do recommend?
> > >
> > > (1) drop and recreate the function AlertCleanup in sysadmin database,
> > >
> > > or
> > >
> > > (2) manually change the $INFORMIXDIR /etc/sysadmin /sch_tasks.sql
> > >
> > > Thanks,
> > > Frank
> > >
> > > On Tue, Oct 23, 2012 at 1:41 PM, FRANK <yunyaoqu@gmail.com> wrote:
> > >
> > > > John,
> > > >
> > > > This is exactly I just found,
> > > >
> > > > You can see,
> > > >
> > > > select count(alert_task_seq)
> > > > from ph_alert
> > > > where alert_task_seq not in (
> > > > select run_task_seq
> > > > from ph_run);> > > >
> > > > (count) 1240088
> > > >
> > > > So, the original Equal Join in AlertCleanup(task_id INTEGER, ID
> > INTEGER)
> > > > will miss the above rows.
> > > >
> > > > So, I just need manually add a " OUTER" in the FROM clause of the
> > > > AlertCleanup() function , correct?
> > > >
> > > > Thanks,
> > > > Frank
> > > >
> > > > On Tue, Oct 23, 2012 at 1:15 PM, John Miller iii <miller3@us.ibm.com
> >
> > > > wrote:
> > > >
> > > > > I believe you might have encountered an upgrade defect IC85171
> > > > >
> > > > > You can fix it very easily by upgrading the procedure in the
> sysadmin
> >
> > > > > database
> > > > > called AlertCleanup() show below. The change in the procedure is to
> > do
> > > > > an OUTER join between ph_alert and ph_run tables.
> > > > >
> > > > > CREATE FUNCTION AlertCleanup(task_id INTEGER, ID INTEGER)> > > > >
> > > > > RETURNING INTEGER
> > > > >
> > > > > DEFINE cur_run_id LIKE ph_run.run_id;
> > > > > DEFINE cur_id LIKE ph_alert.id;
> > > > > DEFINE count INTEGER;
> > > > >
> > > > > LET count =0;
> > > > >
> > > > > FOREACH SELECT id, run_id
> > > > >
> > > > > INTO cur_id, cur_run_id
> > > > >
> > > > > FROM ph_alert, OUTER ph_run
> > > > >
> > > > > WHERE ph_alert.alert_task_id = ph_run.run_task_id
> > > > >
> > > > > AND ph_alert.alert_task_seq = ph_run.run_task_seq
> > > > >
> > > > > AND alert_time < (
> > > > >
> > > > > SELECT current - value::INTERVAL DAY to SECOND
> > > > >
> > > > > FROM ph_threshold
> > > > >
> > > > > WHERE name = 'ALERT HISTORY RETENTION' )
> > > > >
> > > > > DELETE FROM ph_run where run_id = cur_run_id;> > > > >
> > > > > DELETE FROM ph_alert where id = cur_id;> > > > >
> > > > > LET count = count + 1;
> > > > >
> > > > > END FOREACH
> > > > >
> > > > > RETURN count;
> > > > >
> > > > > END FUNCTION;
> > > > >
> > > > > John F. Miller III
> > > > > STSM, Embedability Architect
> > > > > miller3@us.ibm.com
> > > > > 503-578-5645
> > > > > IBM Informix Dynamic Server (IDS)
> > > > >
> > > > > ids-bounces@iiug.org wrote on 10/23/2012 08:54:13 AM:
> > > > >
> > > > > > From: "FRANK" <yunyaoqu@gmail.com>
> > > > > > To: ids@iiug.org,
> > > > > > Date: 10/23/2012 08:57 AM
> > > > > > Subject: Re: sysadmin:ph_alert cleanup [28614]
> > > > > > Sent by: ids-bounces@iiug.org
> > > > > >
> > > > > > Hi, Jacques,
> > > > > >
> > > > > > It was/is enabled,
> > > > > >
> > > > > > tk_id 14
> > > > > > tk_name Alert Cleanup
> > > > > > tk_description Remove all old alert entries from the system.
> > > > > > tk_type TASK
> > > > > > tk_sequence 546
> > > > > > tk_result_table
> > > > > > tk_create
> > > > > > tk_dbs sysadmin
> > > > > > tk_execute AlertCleanup
> > > > > > tk_delete 0 01:00:00
> > > > > > tk_start_time 02:00:00
> > > > > > tk_stop_time
> > > > > > tk_frequency 1 00:00:00
> > > > > > tk_next_execution 2012-10-23 17:56:33
> > > > > > tk_total_executio+ 546
> > > > > > tk_total_time 106.2778002257
> > > > > > tk_monday t
> > > > > > tk_tuesday t
> > > > > > tk_wednesday t
> > > > > > tk_thursday t
> > > > > > tk_friday t
> > > > > > tk_saturday t
> > > > > > tk_sunday t
> > > > > > tk_attributes 404
> > > > > > tk_group SERVER
> > > > > > tk_enable t
> > > > > > tk_priority 0
> > > > > >
> > > > > > The following are the content info of the table, you can see its
> > > > > > accumulation.
> > > > > >
> > > > > > select count(*) from ph_alert;> > > > > > (count(*)) 1739652
> > > > > >
> > > > > > select alert_type, count(*) from ph_alert group by 1;> > > > > > alert_type (count(*))
> > > > > > WARNING 1665166
> > > > > > INFO 74486
> > > > > >
> > > > > > select min (alert_time), max(alert_time) from ph_alert;
> > > > > > (min) (max)
> > > > > > 2011-04-26 17:56:34 2012-10-23 15:45:17
> > > > > >
> > > > > > Somewhere to adjust its cleanup criteria?
> > > > > >
> > > > > > Thanks,
> > > > > > Frank
> > > > > >
> > > > > > On Tue, Oct 23, 2012 at 11:13 AM, JACQUES RENAUT
> > <jrenaut@us.ibm.com>
> > > > > wrote:
> > > > > >
> > > > > > > Original post:
> > > > > > >
> > > > > > > Fernando,
> > > > > > >
> > > > > > > We do not think we ever touched that task since
> installation.....
> >
> > > > Where
> > > > > is
> > > > > > > it ? and how to enable it?
> > > > > > >
> > > > > > > Thanks,
> > > > > > > Frank
> > > > > > >
> > > > > > > Response:
> > > > > > >
> > > > > > > In the sysadmin database you can look at