Results 1 to 14 of 14

Thread: SQL query

  1. #1
    Join Date
    Jun 2003
    Posts
    38

    Unanswered: SQL query

    Hi,

    I am using IDS 9.3. I would like to write a SQL Query (that needs to be executed using dbaccess) which should basically hang.

    Any thougts?

    Thanks!!
    Anu_R

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    eum...

    an SQL that hangs the server? Why?

    If you really want to crash the server, you can use onmode -I.
    or block th eserver using onmode -c block.

    Another thing you can do is set your configuration to make sure that when a dbspace is down, the server waits. Bring a dbspace down. Then do a select from some data from that dbspace.
    rws

  3. #3
    Join Date
    Jun 2003
    Posts
    38
    Hi,

    Thanks for the reply.

    In your response you mentioned the following statements:

    Another thing you can do is set your configuration to make sure that when a dbspace is down, the server waits. Bring a dbspace down. Then do a select from some data from that dbspace

    I am pretty new to informix.

    Please let me know:
    (1) what configuration variables to set
    (2) how to bring a dbspace down


    Thanks,
    Anu_R

  4. #4
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Hi,

    check th eother thread:

    <read-Only database>
    rws

  5. #5
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Coul dyou inform me why you want this sql statement to hang?
    There might be an easier solution if I understand the reason why.
    rws

  6. #6
    Join Date
    Jun 2003
    Posts
    38
    hi,

    I want a SELECT query to hang. We have a product which calculates the timeout when we execute a "hanging" SELECT query.

    I tried bringing the chunk down and running a select or insert. Both works .. amazingly... i dont know what to do...

    Please suggest something.

    Regards,
    Anu_R

  7. #7
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Ok,

    sess1:

    begin work;
    create table bla (col1 serial);
    lock table bla in exclusive mode;

    ---NO COMMIT!!!!!


    sess2:

    begin work;
    set lock mode to wait;
    select * from bla;


    Session 2 will hang until you release the lock in session 1 f.ex. or commit.
    or with the statement UNLOCK. Check sql sysntax guide...
    rws

  8. #8
    Join Date
    Jun 2003
    Posts
    38
    Hi,

    It worked!!

    Only change to your query statements was that in Session 2, I should not be issuing a begin work; statement.

    One more small question, is it possible that i use only "select * from bla" in the second session and it still will hang!! that means, I do not want to use the statement "set lock mode to wait" in my second session!!

    Any thoughts!!

    Anu_R

  9. #9
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    You need the 'SET LOCK MODE' clause. Here's the explanation:

    The default behavior for a database server is to immediately return an error to a process when an SQL request is blocked by an existing lock. If you prefer that the database server wait for the lock to be released, you can use the SET LOCK MODE statement to specify how long the database server should wait for the lock to be released. If the lock is not released within the period you specify, the operation fails and an error is returned to the requesting process.
    Use care when you set the lock mode to WAIT without specifying a maximum wait interval. If you do not specify a wait interval for SET LOCK MODE, your process could, theoretically, wait forever. For example: SET LOCK MODE TO WAIT;

    Even when you would simulate a deadlock, this would be detected and aborted. It is abnormal database behaviour to just 'hang' ...
    What you coul ddo is simulate a hang. If you define a cursor in your client application, with an isolation level set to 'repeatable read' and you don't do a next fetch. This is actually just an illustration of bad programming, but it could be used in your test scenario. You will need a client application i.e. 4GL or esql/c to test this. dbaccess is not enough I'm afraid.
    rws

  10. #10
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    This one hangs too:

    sess 1:
    onmode -c block

    sess2 (in dbaccess):
    create table bla1(col1 serial);


    the sess2 will hang until you issue an onmode -c unblock in your first session. This only works with updates since the checkpoint is blocking the server. Be aware that when your server is blocked no updates/inserts/deletes/DDL will be executed. It is not a 'select' as you requested, but it works too.
    rws

  11. #11
    Join Date
    Jun 2003
    Posts
    38
    I understand the explanations you provide .. but unfortunately my product needs me only to issue a SELECT that hangs and a single SELECT statement. I cant think of anything else though.

    Can I define a CREATE/INSERT/SELECT (as you suggested in the next post with onmode -c block) using a EXECUTE command...? I dont know how ...

    I am thinking of various possibilities of making a single SELECT hang... i need only a SELECT unfortunately ...

    Thanks!!
    If you think of something... please post a reply ...

    Anu_R

  12. #12
    Join Date
    Jun 2003
    Posts
    38
    OR

    is there a way in which i can corrupt my database such that the SELECT statement itself fails??

    Anu_R

  13. #13
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    You can crash the server with 'onmode -I'.
    This command will trap an error and create an AF file.
    There is a way to make the server 'hang' while he is creating the AF file.
    Let me see if I can find it...
    rws

  14. #14
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Before you startup the engine, set the environment variable AFDEBUG=1
    export AFDEBUG=1
    Startup the engine and open dbaccess, connect to the database.
    In another session, check with 'onstat -g sql', to find out th esessid of the dbaccess session
    You can trap an error with onmode -I <err> <sessid>
    It doesn't matter what error you want to trap. The one I always use is 206 (table not found).
    set f.ex. onmode -I 206 76
    This command will generate an af file in /tmp and send various log records to the message log. (onstat -m)
    Now because you set the AFDEBUG, th eengine will freeze to gather additional diagnostics.
    If you can't set AFDEBUG, you can use 'onmode -A 1' to turn it on and 'onmode -A 0' to turn it back off.
    rws

Posting Permissions

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