Re: Unexpected behavior

hmmm interesting stuff - could we have a look at the stack of thread 469? also from an onstat -u | grep 469, what's it waiting on? onstat -g lmx or onstat -g con, do they show a matching value on the waitcolumn above? Art S. Kagel wrote: > 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 > -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Informix faq http://www.iiug.org/techinfo/faq/informix.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm sending to informix-list