Informix Dynamic Server Scheduler question

MBatelic · #1 (**permalink**) 04-23-09, 11:36

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

h_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!

rootdbs · #2 (**permalink**) 04-23-09, 13:08

Very simple workaround.
Use cron (for *nix) or Windows scheduler.
You should put your DELETE statement in file, for exaple, dlt.sql.
Create also next file which contains:
INFORMIXDIR=your_dir
INFORMIXSERVER=your_server
PATH=$INFORMIXDIR/bin:$ISMDIR/bin:$PATH
DB_LOCALE=your_locale
CLIENT_LOCALE=your_locale
ONCONFIG=your_onconfig
#and next line
dbaccess your_database /path_to_dlt_file/dlt.sql

I think it will help you.
I can give you a good russian Informix forum.

MBatelic · #3 (**permalink**) 04-23-09, 14:14

Thank you for your reply!
I already thought about the solution you suggested. In fact, last week I went to my mentor with that suggestion and he told me to use the scheduler. So I would like to try a little more on getting it to work if it is possible before I go to my mentor to tell him I can't get it to work this way.
You can send me the link to the forum but I'm not sure if I will understand anything because I'm from Croatia. Especially if people post in Cyrillic alphabet

.
Once again, thank you for your reply! It will help me if I don't get any success with the scheduler!

MBatelic · #4 (**permalink**) 04-23-09, 14:33

One more thing! If I could get the scheduler to work, the solution would surely be OS independent. It also wouldn't contain any file and folder paths. It would be a simple INSERT statement among other SQL statements that are executed during the database creation and the creation of it's content.

rootdbs · #5 (**permalink**) 04-24-09, 03:38

MBatelic, give me insert script which inserting your task in ph_task.

rootdbs · #6 (**permalink**) 04-24-09, 05:16

Can you execute statement

Code:

DELETE FROM odrzavanje:kljuc WHERE idsession NOT IN (select sid from sysmaster:syssessions)

from SQL editor when you connect to sysadmin database?

MBatelic · #7 (**permalink**) 04-24-09, 05:32

Hello, rootdbs!

I'm using Server Studio. So when I open the SQL editor, no matter which database I choose to execute the DELETE statement in, it works fine.

Here is the insert statement:

Code:

INSERT INTO ph_task
(
tk_name,
tk_type,
tk_group,
tk_description,
tk_execute,
tk_start_time,
tk_stop_time,
tk_frequency,
tk_dbs
)
VALUES
(
'mon_sess',
'TASK',
'MISC',
'Delete sessions information between 8AM and 5PM.',
'DELETE FROM odrzavanje:kljuc WHERE idsession NOT IN (select sid from sysmaster:syssessions)',
DATETIME(00:00:00) HOUR TO SECOND,
DATETIME(23:59:00) HOUR TO SECOND,
INTERVAL ( 1 ) MINUTE TO MINUTE,
'odrzavanje'
);

On it's executing, the task returns no errors but nothing gets deleted from kljuc table, although when I execute the DELETE statement in the SQL editor it works fine.

Is it maybe possible that the problem is in the version of my IDS - 11.50.xC3 ?
If I don't insert the database in which the delete statement should be executed, it gets executed in sysadmin database which is the default value of tk_dbs column. Then when the statement gets executed I get the error: -23197 Database locale information mismatch.
In my 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 ':').

Ok, then I found out that the xC4 version has support for running tasks and sensors in databases
with locales different than the sysadmin locale.

But I don't understand why the task doesn't do it's job when I define the database name in which it should be executed (tk_dbs) as 'odrzavanje'.

Thanks, rootdbs!

rootdbs · #8 (**permalink**) 04-24-09, 06:24

Sorry, but I didn't understand you about error: -23197.
You get error: -23197 when you execute statement
DELETE FROM odrzavanje:kljuc WHERE idsession NOT IN (select sid from sysmaster:syssessions)
from SQL editor when you connect to sysadmin database.
Is it right?

Quote:

I found out that the xC4 version has support for running tasks and sensors in databases with locales different than the sysadmin locale.

Could you give me a link to this source.

rootdbs · #9 (**permalink**) 04-24-09, 07:26

MBatelic, you should create a procedure in database "odrzavanje"

create procedure del_row ()
DELETE FROM odrzavanje:kljuc WHERE idsession NOT IN (select sid from sysmaster:syssessions);
end procedure;

And you should create task execuring procedure del_row in ph_task table.
You should instead of "'DELETE FROM odrzavanje:kljuc WHERE idsession NOT IN (select sid from sysmaster:syssessions)'" insert "'execute procedure odrzavanje:del_row();'"

IT MUST WORK.
I have checked it in my server.

MBatelic · #10 (**permalink**) 04-24-09, 12:56

Hello, rootdbs!

I did the same thing you did on your server and the rows DON'T get deleted

. The task gets executed without reporting any error. So the things remain pretty much the same.

What is the version of your IDS? My version is 11.50.xC3.
Your database is maybe created with the same locale as sysadmin and here is what someone else replied to me on another forum:
International Informix Users Group

The problem could also be related to this problem:
IBM - IC57571: SCHAPI: ERROR -23197 DATABASE LOCALE INFORMATION MISMATCH ERROR IN ONLINE.LOG
(see the problem description, my database was created with different locale from sysadmin: hr_hr.utf-8)

You don't have to bother anymore about my problem. You wasted enough of your time

! Maybe I'll try to upgrade to 11.50.xC4 but I'm not sure if it is already available.
You helped me a lot! By knowing that it works fine on your server at least I'm sure that I'm inserting the task correctly.

Thank you very very much!!!