See placeholders in IDS 9.30?

IDS9.30HC5, HP-UX 11i. IBM tech supp have said "no", but I'm wondering if anyone out there knows if there is a (backdoor?) method of looking at the mem dump of a thread to determine what values are being used for the placeholders shown under onstat -g ses for a 4gl connection? I know, I know, 9.30 is unsupported, but until my crowd pay up for the required reversionary testing of all our code, we're stuck with it. I'm pushing. Hard. Really. Note the brick dust on my forehead. But I'm just the DBSA. What do I know? Malc

malc_p@btinternet.com wrote: > IDS9.30HC5, HP-UX 11i. > IBM tech supp have said "no", but I'm wondering if anyone out there > knows if there is a (backdoor?) method of looking at the mem dump of a > thread to determine what values are being used for the placeholders > shown under onstat -g ses for a 4gl connection? > I know, I know, 9.30 is unsupported, but until my crowd pay up for the > required reversionary testing of all our code, we're stuck with it. I'm > pushing. Hard. Really. Note the brick dust on my forehead. But I'm just > the DBSA. What do I know? > Malc No but... You could either - install Informix-SPY in the middle and use it to monitor the session. - if the application is in ESQL/C or 4gl then set environment variable SQLIDEBUG=2:/tmp/myfile run app sqliprint /tmp/myfile > /tmp/myfile.out And you can see C->S (client program to database server) and S->C (database server to client program) traffic. You can the see the program BIND the values to the host variables

I-spy is prohibitively expensive (as far as our acountants go). I've never used SQLIDEBUG - do I set it for the app or the server (i guess it's for the app)? If for the server, do I have to bounce the engine before and after? Got an interesting reply from IBM (see below) but it only worked up to the stage where we should have got the pointers to the host variables. Looks like it may be worth investigating further..... ******** How to find the value of a host variable in IDS versions previous to 9.40 .First you need to identify the session running the prepare statement: $ onstat -g ses Informix Dynamic Server Version 9.30.UC3 session #RSAM id user tty pid hostname threads 1461 informix LON-JSAG 1860 lon-jsag 1 .We need to get the session's RSAM thread control block (rstcb_t), we can get it with onstat -g ses <session_id> $ onstat -g ses 1461 Informix Dynamic Server Version 9.30.UC3 session #RSAM id user tty pid hostname threads 1461 informix LON-JSAG 1860 lon-jsag 1 tid name rstcb flags curstk status 1514 sqlexec 14c91a18 Y--P--- 656 cond wait(netnorm) .... Sess SQL Current Iso Lock Id Stmt type Database Lvl Mode 1461 UPDATE stores_demo CR Not Wait Current SQL statement : update tab1 set s_char=? where s_int=1 Last parsed SQL statement : update tab1 set s_char=? where s_int=1 .Using the address of the rstcb, we can run the following onstat -g dmp to get the address for the session control block (scb) $ onstat -g dmp 0x14c91a18 rstcb_t | grep scb scb = 0x0x15613030 .Then get the address for the SQL session control block (sqscb) $ onstat -g dmp 0x15613030 tag_scb | grep sqscb sqscb = 0x0x156c5018 sqscb_poolp = 0x0x156c6428 .Then get the address for the current control block (currcb) $ onstat -g dmp 0x156c5018 sqscb_t | grep currcb currcb = 0x0x154e48d8 .Then get the address for the host variable list (cb_hostvar) $ onstat -g dmp 0x154e48d8 conblock | grep cb_hostvar cb_hostvar = 0x0x1527dbc0 . from here, we can dump the memory for that address and we should find the pointers to our host variable $ onstat -g dmp 0x1527dbc0 100 Informix Dynamic Server Version 9.30.UC3 address bytes in mem 1527dbc0: 00000000 00000000 00000000 e0454e15 ........ .....EN. 1527dbd0: 08000000 00000000 09000000 00000000 ........ ........ 1527dbe0: 00000000 00000000 00000000 00000000 ........ ........ 1527dbf0 * 1527dc20: 00000000 .... In the 4th column we can see the value "e0454e15", becouse this test was running in a machine with byte swap, the address should be "0x154e45e0" If we dump the first 50 bytes of that memory $ onstat -g dmp 0x154e45e0 50 Informix Dynamic Server Version 9.30.UC3 address bytes in mem 154e45e0: 74657374 5f766172 00000000 00000000 test_var ........ 154e45f0: 88a65115 01010000 89011000 00000000 ..Q..... ........ 154e4600: 00000000 00000000 0200ffff 00000000 ........ ........ 154e4610: 3046 0F "test_var" is the value used to execute the prepare statement. **********

I-spy is prohibitively expensive (as far as our acountants go). I've never used SQLIDEBUG - do I set it for the app or the server (i guess it's for the app)? If for the server, do I have to bounce the engine before and after? Got an interesting reply from IBM (see below) but it only worked up to the stage where we should have got the pointers to the host variables. Looks like it may be worth investigating further..... ******** How to find the value of a host variable in IDS versions previous to 9.40 .First you need to identify the session running the prepare statement: $ onstat -g ses Informix Dynamic Server Version 9.30.UC3 session #RSAM id user tty pid hostname threads 1461 informix LON-JSAG 1860 lon-jsag 1 .We need to get the session's RSAM thread control block (rstcb_t), we can get it with onstat -g ses <session_id> $ onstat -g ses 1461 Informix Dynamic Server Version 9.30.UC3 session #RSAM id user tty pid hostname threads 1461 informix LON-JSAG 1860 lon-jsag 1 tid name rstcb flags curstk status 1514 sqlexec 14c91a18 Y--P--- 656 cond wait(netnorm) .... Sess SQL Current Iso Lock Id Stmt type Database Lvl Mode 1461 UPDATE stores_demo CR Not Wait Current SQL statement : update tab1 set s_char=? where s_int=1 Last parsed SQL statement : update tab1 set s_char=? where s_int=1 .Using the address of the rstcb, we can run the following onstat -g dmp to get the address for the session control block (scb) $ onstat -g dmp 0x14c91a18 rstcb_t | grep scb scb = 0x0x15613030 .Then get the address for the SQL session control block (sqscb) $ onstat -g dmp 0x15613030 tag_scb | grep sqscb sqscb = 0x0x156c5018 sqscb_poolp = 0x0x156c6428 .Then get the address for the current control block (currcb) $ onstat -g dmp 0x156c5018 sqscb_t | grep currcb currcb = 0x0x154e48d8 .Then get the address for the host variable list (cb_hostvar) $ onstat -g dmp 0x154e48d8 conblock | grep cb_hostvar cb_hostvar = 0x0x1527dbc0 . from here, we can dump the memory for that address and we should find the pointers to our host variable $ onstat -g dmp 0x1527dbc0 100 Informix Dynamic Server Version 9.30.UC3 address bytes in mem 1527dbc0: 00000000 00000000 00000000 e0454e15 ........ .....EN. 1527dbd0: 08000000 00000000 09000000 00000000 ........ ........ 1527dbe0: 00000000 00000000 00000000 00000000 ........ ........ 1527dbf0 * 1527dc20: 00000000 .... In the 4th column we can see the value "e0454e15", becouse this test was running in a machine with byte swap, the address should be "0x154e45e0" If we dump the first 50 bytes of that memory $ onstat -g dmp 0x154e45e0 50 Informix Dynamic Server Version 9.30.UC3 address bytes in mem 154e45e0: 74657374 5f766172 00000000 00000000 test_var ........ 154e45f0: 88a65115 01010000 89011000 00000000 ..Q..... ........ 154e4600: 00000000 00000000 0200ffff 00000000 ........ ........ 154e4610: 3046 0F "test_var" is the value used to execute the prepare statement. **********

ahum... $ onstat -g dmp 0x13407a18 rstcb_t |grep scb scb = 0x0x139d6e88 $ onstat -g dmp 0x139d6e88 scb_t |grep sqscb sqscb = 0x0x139e3018 sqscb_poolp = 0x0x139e4410 $ onstat -g dmp 0x139e3018 sqscb_t |grep host nhost = 1 hostnum = 1 host_cb = 0x(nil) $ onstat -g dmp 0x139e3018 sqscb_t |grep hstlist hstlist = 0x0x13b92260 $ onstat -g dmp 0x13b92260 value_t |moreor onstat -g dmp 0x13b92260 value_t |grep v_type v_type = 2 ... 2 means int $ onstat -g dmp 0x13b92260 value_t |grep vint vint = 102 vint8 = { if more hostvars then add 0x40 (Version 7 ) or 0x60 (Version 9) to hstlist (0x13b92260) and dump using value_t...... Superboer. malc_p@btinternet.com schreef: > I-spy is prohibitively expensive (as far as our acountants go). I've > never used SQLIDEBUG - do I set it for the app or the server (i guess > it's for the app)? If for the server, do I have to bounce the engine > before and after? > Got an interesting reply from IBM (see below) but it only worked up to > the stage where we should have got the pointers to the host variables. > Looks like it may be worth investigating further..... > > ******** > How to find the value of a host variable in IDS versions previous to > 9.40 > > .First you need to identify the session running the prepare statement: > $ onstat -g ses> > Informix Dynamic Server Version 9.30.UC3 > > session #RSAM > id user tty pid hostname threads > > 1461 informix LON-JSAG 1860 lon-jsag 1 > > .We need to get the session's RSAM thread control block (rstcb_t), > we can get it with onstat -g ses <session_id> > > $ onstat -g ses 1461> > Informix Dynamic Server Version 9.30.UC3 > > session #RSAM > id user tty pid hostname threads > 1461 informix LON-JSAG 1860 lon-jsag 1 > > tid name rstcb flags curstk status > 1514 sqlexec 14c91a18 Y--P--- 656 cond wait(netnorm) > .... > Sess SQL Current Iso Lock > Id Stmt type Database Lvl Mode > 1461 UPDATE stores_demo CR Not Wait > > Current SQL statement : > update tab1 set s_char=? where s_int=1> > Last parsed SQL statement : > update tab1 set s_char=? where s_int=1> > > .Using the address of the rstcb, we can run the following onstat -g dmp > to get the address for the session control block (scb) > > $ onstat -g dmp 0x14c91a18 rstcb_t | grep scb > scb = 0x0x15613030> > .Then get the address for the SQL session control block (sqscb) > > $ onstat -g dmp 0x15613030 tag_scb | grep sqscb > sqscb = 0x0x156c5018 > sqscb_poolp = 0x0x156c6428> > .Then get the address for the current control block (currcb) > > $ onstat -g dmp 0x156c5018 sqscb_t | grep currcb > currcb = 0x0x154e48d8> > .Then get the address for the host variable list (cb_hostvar) > > $ onstat -g dmp 0x154e48d8 conblock | grep cb_hostvar > cb_hostvar = 0x0x1527dbc0> > . from here, we can dump the memory for that address and > we should find the pointers to our host variable > > $ onstat -g dmp 0x1527dbc0 100> > Informix Dynamic Server Version 9.30.UC3 > > address bytes in mem > 1527dbc0: 00000000 00000000 00000000 e0454e15 ........ .....EN. > 1527dbd0: 08000000 00000000 09000000 00000000 ........ ........ > 1527dbe0: 00000000 00000000 00000000 00000000 ........ ........ > 1527dbf0 * > 1527dc20: 00000000 .... > > In the 4th column we can see the value "e0454e15", becouse this test > was running in a machine with byte swap, the address should be > "0x154e45e0" > If we dump the first 50 bytes of that memory > > $ onstat -g dmp 0x154e45e0 50> > Informix Dynamic Server Version 9.30.UC3 > > address bytes in mem > 154e45e0: 74657374 5f766172 00000000 00000000 test_var ........ > 154e45f0: 88a65115 01010000 89011000 00000000 ..Q..... ........ > 154e4600: 00000000 00000000 0200ffff 00000000 ........ ........ > 154e4610: 3046 0F > > "test_var" is the value used to execute the prepare statement. > **********