Unexpected behavior
Has anyone seen this one before?
I have code that has to run in production and test environments and
access datafrom two databases in a single query. On production the two
are resident in different servers on test they reside in the same server.
I did the following (a simplification but accurate) in ESQL/C:
EXEC SQL SELECT DBSERVERNAME, DBINFO('dbhostname')
INTO :servername, :hostname;
if (strcmp( hostname, "prodhost" ) == 0)
strcpy( other_server, "otherprodsvr" );
else
strcpy( other_server, servername );
sprintf( qry_str,
"select a.a, a.b, b.a, b.c, c.a, c.b, d.a, d.c"
"from a_table a, b_table b, c_table@%s c, d_table@%s d",
other_server,
other_server );
EXEC SQL PREPARE qry_stmt FROM qry_str;
So that tables c & d look like either 'c_table@otherprodsvr' or
'c_table@testserver1' depending on which host the $INFORMIXSERVER is
running on.
On production (ie hostname = "prodhost") this works fine. On test if I
change the strcpy to assign the local server's HDR secondary it works
fine and if I get rid of the '@servername' altogether on test it works
fine. But as it is above, with the "c_table@" pointing to the local
$INFORMIXSERVER it hangs forever in the PREPARE. The primary session has
flags 'Y--P---' indicating a condition wait and the "remote" session
shows flags 'T--P---' indicating a transaction wait. Here is some
relevant onstat output:
Onstat -g ses for the primary session shows
Informix Dynamic Server Version 7.31.UD4 -- On-Line (Prim) -- Up 1 days 11:21:33 -- 442496 Kbytes
session #RSAM total used
id user tty pid hostname threads memory memory
291 cli2 288 759 sundev9 1 57344 53824
tid name rstcb flags curstk status
468 sqlexec 302855a0 Y--P--- 4560 cond wait(netnorm)
Memory pools count 1
name class addr totalsize freesize #allocfrag #freefrag
291 V 39f16018 57344 3520 324 2
name free used name free used
overhead 0 120 scb 0 96
opentable 0 1960 filetable 0 536
misc 0 80 log 0 2152
temprec 0 856 keys 0 96
ralloc 0 23056 gentcb 0 8528
ostcb 0 2488 sqscb 0 7960
rdahead 0 832 hashfiletab 0 280
osenv 0 3040 sqtcb 0 1568
fragman 0 176
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
291 SELECT expdb CR Wait 0 0 9.25
Current SQL statement :
SELECT t.rrn, t.employee_id, t.transaction_seq, .....
and onstat -g ses for session for the remote access to the server shows:
Informix Dynamic Server Version 7.31.UD4 -- On-Line (Prim) -- Up 1 days 11:19:49 -- 442496 Kbytes
session #RSAM total used
id user tty pid hostname threads memory memory
292 cli2 - 5945 dgintel1 1 24576 22088
tid name rstcb flags curstk status
469 srvinfx 30285a9c T--P--- 600 sleeping(Forever)
Memory pools count 1
name class addr totalsize freesize #allocfrag #freefrag
292 V 39f8e018 24576 2488 260 1
name free used name free used
overhead 0 120 scb 0 96
opentable 0 280 filetable 0 280
misc 0 80 log 0 2152
gentcb 0 8520 ostcb 0 2488
sqscb 0 4832 hashfiletab 0 280
osenv 0 2096 sqtcb 0 816
fragman 0 48
onstat -g con shows:
Informix Dynamic Server Version 7.31.UD4 -- On-Line (Prim) -- Up 1 days 11:23:37 -- 442496 Kbytes
Conditions with waiters:
cid addr name waiter waittime
200 302b5728 drcb_bqf 458 19
12403 39f947f8 netnorm 403 1
12408 30e227f8 netnorm 404 40801
12413 38c0bb18 netnorm 405 40801
12418 38c0bc80 netnorm 406 682
12423 38c0bde8 netnorm 407 40801
12449 3a62b020 netnorm 412 443
12454 3a62b188 netnorm 413 40740
12459 3a62b2f0 netnorm 414 40740
12464 3a62b458 netnorm 415 40740
12469 3a62b5c0 netnorm 416 40740
12519 3a62ac30 netnorm 426 38235
12524 39dad608 netnorm 427 38234
12529 39f94528 netnorm 428 38234
12534 3a62bd58 netnorm 429 38234
12539 30e22cc0 netnorm 430 38234
12885 30b3fb60 netnorm 465 2800
12908 3a62b890 netnorm 468 1995 <<<< Here I am waiting
All connections are TCP/IP tli connections. Platform is DGUX 4.20MU07
for the server either DGUX or Solaris 8 for the client, CSDK 9.20.UC1 on
DGUX and 9.50.UC1 on Solaris and as you can see IDS 7.31UD4. Obviously,
I can (and did) change to using the single local connection on the test
machine but I'd like to understand why this was hung in the first place.
Anyone have any ideas?
Art S. Kagel