Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Unanswered: Informix Dynamic Server Scheduler question

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

  2. #2
    Join Date
    Feb 2009
    Posts
    51
    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.

  3. #3
    Join Date
    Apr 2009
    Posts
    5
    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!

  4. #4
    Join Date
    Apr 2009
    Posts
    5
    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.

  5. #5
    Join Date
    Feb 2009
    Posts
    51
    MBatelic, give me insert script which inserting your task in ph_task.

  6. #6
    Join Date
    Feb 2009
    Posts
    51
    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?

  7. #7
    Join Date
    Apr 2009
    Posts
    5
    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!

  8. #8
    Join Date
    Feb 2009
    Posts
    51
    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?

    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.

  9. #9
    Join Date
    Feb 2009
    Posts
    51
    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.

  10. #10
    Join Date
    Apr 2009
    Posts
    5
    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!!!

  11. #11
    Join Date
    Feb 2009
    Posts
    51
    Quote Originally Posted by MBatelic
    Hello, rootdbs!

    I did the same thing you did on your server and the rows DON'T
    Yes, you are right.
    I have created db with non-EN_US locale and haму got this error.

  12. #12
    Join Date
    Feb 2009
    Posts
    51
    IDS 11.5 xC4 is available now.

  13. #13
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Already try use "sysdbclose()" procedure?
    Using SYSDBOPEN and SYSDBCLOSE Procedures
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •