Re: Unexpected behavior

"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:pan.2003.07.11.11.07.26.757481.15473@bloomberg.net... > 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; strcpy the fixed text for the query which fails into qry_str. Is qry_str long enough to contain the query? > > 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

On Sun, 13 Jul 2003 19:05:43 -0400, David Williams wrote: > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:pan.2003.07.11.11.07.26.757481.15473@bloomberg.net... >> 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; > > strcpy the fixed text for the query which fails into qry_str. That's what's happening here. > Is qry_str long enough to contain the query? Yes. Someone emailed me the definitive. It's a bug in 7.31 engine hangs when accessing itself remotely if there is a 'group' defined in the sqlhosts file that includes the server and INFORMIXSERVER or the remote servername are the groupname. Defect is PTS # B159203. Art S. Kagel > > > >> 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