Informix Dynamic Server Scheduler question
A student wanted the IDS Scheduler to run a periodic DELETE removing rows whose stored session id is no longer in sysmaster:syssessions, to clean up after application crashes. The task appeared to run (ret_value 0) but deleted nothing. Suggestions included using temp tables or sysdbopen/sysdbclose, plus a sample INSERT into sysadmin:ph_task. Setting tk_dbs to his own database avoided error -23197 (locale mismatch) but rows still weren't deleted; John Miller explained that running tasks in a database with a locale differing from sysadmin's requires 11.50.xC4, which was then just becoming available. No confirmation of a working fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi!
I'm working on a project at my faculty. It's a java desktop application with
an informix database. I have a problem using informix dynamic server scheduler.
I have a particular table in my database. This table contains records that are
inserted by the desktop aplication I'm developing. These records must be
deleted when the user closes the application. I implemented a WindowListener
in my application that does that.
The problem is what to do if the application crashes or the connection to the
database breaks.
I went to my professor and he told me to use informix dynamic server scheduler.
I added a new column to my table and this column contains session id of my
application's connection. So if the application crashes, the session is no
longer active. All I have to do is to periodically (for example every 60
seconds) issue a DELETE statement that deletes all the records from my table
that contain a session id which is not among the active database sessions.
I created the statement and it works fine when I execute it in SQL editor. I
think I'm doing something wrong while inserting the task into the
sysadmin:ph_task table.
The task seem to execute periodically every 60 seconds but it doesn't delete
records.
I looked at the records in ph_run table and it has records regarding my task.
The ret_value is 0.
Can someone help me? I have a deadline and I'm getting very close to it.
Here is the DELETE query I'm trying to execute in my task:
DELETE FROM odrzavanje:kljuc WHERE idsession NOT IN (select sid from
sysmaster:syssessions)
"odrzavanje" is the name of my database.
"kljuc" is the name of the particular table.
"idsession" is the column that contains the session id.
It would be very helpful to me if someone could post an INSERT statement which
inserts the task into the ph_task table so I can try it out.
If you don't have time to do that, a few hints would also be helpful.
In the meanwhile I'll try to solve the problem by myself.
Sorry if it is a stupid question. I'm a newbie regarding all this advanced
Informix features and I really don't have much time to explore.
Thanks!
Can't you just use an Informix TEMP TABLE ?
Informix will automatically delete that when the session drops...
On Thursday 23 April 2009 14:31:31 MARKO BATELIÄ wrote:
> Hi!
>
> I'm working on a project at my faculty. It's a java desktop application
> with an informix database. I have a problem using informix dynamic server
> scheduler.
> I have a particular table in my database. This table contains records that
> are inserted by the desktop aplication I'm developing. These records must
> be deleted when the user closes the application. I implemented a
> WindowListener in my application that does that.
> The problem is what to do if the application crashes or the connection to
> the database breaks.
> I went to my professor and he told me to use informix dynamic server
> scheduler.
> I added a new column to my table and this column contains session id of my
> application's connection. So if the application crashes, the session is no
> longer active. All I have to do is to periodically (for example every 60
> seconds) issue a DELETE statement that deletes all the records from my
> table that contain a session id which is not among the active database
> sessions. I created the statement and it works fine when I execute it in
> SQL editor. I think I'm doing something wrong while inserting the task into
> the
> sysadmin:ph_task table.
> The task seem to execute periodically every 60 seconds but it doesn't
> delete records.
> I looked at the records in ph_run table and it has records regarding my
> task. The ret_value is 0.
> Can someone help me? I have a deadline and I'm getting very close to it.
> Here is the DELETE query I'm trying to execute in my task:
>
> DELETE FROM odrzavanje:kljuc WHERE idsession NOT IN (select sid from
> sysmaster:syssessions)>
> "odrzavanje" is the name of my database.
> "kljuc" is the name of the particular table.
> "idsession" is the column that contains the session id.
>
> It would be very helpful to me if someone could post an INSERT statement
> which inserts the task into the ph_task table so I can try it out.
> If you don't have time to do that, a few hints would also be helpful.
> In the meanwhile I'll try to solve the problem by myself.
>
> Sorry if it is a stupid question. I'm a newbie regarding all this advanced
> Informix features and I really don't have much time to explore.
>
> Thanks!
>
>
> ***************************************************************************
>**** Forum Note: Use "Reply" to post a response in the discussion forum.
--
Mike Aubury
http://www.aubit.com/
Aubit Computing Ltd is registered in England and Wales, Number: 3112827
Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ
Thank you for your answer! The thing is that the table contains records about particular sessions and all other sessions must be able to use them. Application's behavior depends on records in this table inserted by other applications' sessions. After a session is no longer active, only the records inserted by this session must be deleted.
I would make a suggestion that you look at sysdbopen and sysdbclose. T=
hese
are produces execute
every type a user opens or close a database.
Here is what you are looking for:
INSERT INTO ph_task
(
tk_name,
tk_type,
tk_group,
tk_description,
tk_execute,
tk_start_time,,
tk_stop_time,,,
tk_frequency,,,
)
VALUES
(
"mon_config",,,
"TASK",
"MISC",
"Delete sessions information between 8AM and 5PM.","DELETE FROM odrzavanje:kljuc WHERE idsession NOT IN (select sid from
sysmaster:syssessions)",
DATETIME(08:00:00) HOUR TO SECOND,
DATETIME(17:00:00) HOUR TO SECOND,
INTERVAL ( 1 ) MINUTE TO MINUTE
);
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 04/23/2009 06:31:31 AM:
> [image removed]
>
> Informix Dynamic Server Scheduler question [15592]>
>
> MARKO BATELI=C4
>
> to:
>
> ids
>
> 04/23/2009 06:35 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Hi!
>
> I'm working on a project at my faculty. It's a java desktop applicati=
on
with
> an informix database. I have a problem using informix dynamic server
> scheduler.
> I have a particular table in my database. This table contains
> records that are
> inserted by the desktop aplication I'm developing. These records must=
be
> deleted when the user closes the application. I implemented a
WindowListener
> in my application that does that.
> The problem is what to do if the application crashes or the connectio=
n to
the
> database breaks.
> I went to my professor and he told me to use informix dynamic server
> scheduler.
> I added a new column to my table and this column contains session id =
of
my
> application's connection. So if the application crashes, the session =
is
no
> longer active. All I have to do is to periodically (for example every=
60
> seconds) issue a DELETE statement that deletes all the records from m=
y
table
> that contain a session id which is not among the active database
sessions.
> I created the statement and it works fine when I execute it in SQL
editor. I
> think I'm doing something wrong while inserting the task into the
> sysadmin:ph_task table.
> The task seem to execute periodically every 60 seconds but it doesn't=
delete
> records.
> I looked at the records in ph_run table and it has records regardingm=
y
task.
> The ret_value is 0.
> Can someone help me? I have a deadline and I'm getting very close to =
it.
> Here is the DELETE query I'm trying to execute in my task:
>
> DELETE FROM odrzavanje:kljuc WHERE idsession NOT IN (select sid from
> sysmaster:syssessions)>
> "odrzavanje" is the name of my database.
> "kljuc" is the name of the particular table.
> "idsession" is the column that contains the session id.
>
> It would be very helpful to me if someone could post an INSERT
> statement which
> inserts the task into the ph_task table so I can try it out.
> If you don't have time to do that, a few hints would also be helpful.=
> In the meanwhile I'll try to solve the problem by myself.
>
> Sorry if it is a stupid question. I'm a newbie regarding all this
advanced
> Informix features and I really don't have much time to explore.
>
> Thanks!
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.=
>=
Thank you for your reply! I executed your insert statement! The problem is that in the ph_task table the column tk_dbs gets the value 'sysadmin' (the default value), and when the task executes it returns this error: -23197 Database locale information mismatch. I'm not sure why this happens...Probably because DB_LOCALE of my database is different (hr_hr.utf8). So I changed tk_dbs value to 'odrzavanje' which is the name of my database. My understanding is that this field contains the name of the database in which the SQL action should be executed. In this case it shouldn't make any difference in which database the statement is executed because it contains not only table names in the FROM clauses but also the database names (they are separated by ':'). After changing the tk_dbs value to 'odrzavanje' I don't get any alert in the ph_alert table anymore. And the run_retcode in the ph_run table is 0. But the problem still egsists! The records in my table still don't get deleted even if they have fake session ids. I do not understand why this is happening because when I try to execute the DELETE statement in the SQL editor all the records with a fake session id get deleted. Thank you again John for trying to help me!
Marko: You will need to get 11.50.xC4 which has support for running tasks and sensors in databases with locales different than sysadmin locale. You are correct you will = need to place odrzavanje in the tk_dbs column. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) = From: "MARKO BATELI=C4" <marko.batelic@gmail.com> = = = To: ids@iiug.org = = Date: 04/23/2009 04:22 PM = = Subject: Re: Informix Dynamic Server Scheduler question [15615] = = Sent by: ids-bounces@iiug.org = = Thank you for your reply! I executed your insert statement! The problem is that in the ph_task ta= ble the column tk_dbs gets the value 'sysadmin' (the default value), and when t= he task executes it returns this error: -23197 Database locale information mismatch. I'm not sure why this happens...Probably because DB_LOCALE of my databa= se is different (hr_hr.utf8). So I changed tk_dbs value to 'odrzavanje' which is the name of my datab= ase. My understanding is that this field contains the name of the database in w= hich the SQL action should be executed. In this case it shouldn't make any difference in which database the statement is executed because it conta= ins not only table names in the FROM clauses but also the database names (they = are separated by ':'). After changing the tk_dbs value to 'odrzavanje' I don't get any alert i= n the ph_alert table anymore. And the run_retcode in the ph_run table is 0. But the problem still egsists! The records in my table still don't get deleted even if they have fake session ids. I do not understand why this is happening because when I try to execute the DELETE statement in the SQL editor al= l the records with a fake session id get deleted. Thank you again John for trying to help me! ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
Thank you very much John for your response! The current version I'm using is 11.50.xC3 Developers Edition trial for unlimited evaluation. How do I upgrade? Is the xC4 version already available? And is it available for trial?
2009/4/24 MARKO BATELIæ <marko.batelic@gmail.com>: > Thank you very much John for your response! > The current version I'm using is 11.50.xC3 Developers Edition trial for > unlimited evaluation. How do I upgrade? Is the xC4 version already available? > And is it available for trial? IDS 11.50.xC4 is electronically available (there was a brief discussion on the informix-list@iiug.org mailing list). It will be announced on Monday. Whether the trial version is available yet, I'm not sure. Very soon... -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. H. L. Mencken - "It is even harder for the average ape to believe that he has descended from man." - http://www.brainyquote.com/quotes/authors/h/h_l_mencken.html