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

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.

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

Hi,

check th eother thread:

<read-Only database>

Coul dyou inform me why you want this sql statement to hang?
There might be an easier solution if I understand the reason why.

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

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...

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

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.

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.

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

OR

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

Anu_R

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...

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.