Re: Informix tool related question

RobRatner@aol.com wrote: > Good afternoon gentleman. I am a software developer for the govt in > new york city and I wanted to ask a question concerning all of your > informix expertise. > > I am trying to analyze an online web application which runs on > informix, where unfortunately the source code and latest > documentation is not available. Based on transactions that I can > manipulate through the application GUI, I am trying to figure out how > that specific transaction affects the underlying database. In other > words what tables are populated, affected by the specific > transaction. So would any one know if there is a tool that can tell > me throught the informix version that this application runs, what > exactly happened in the database, what has changed in the database, > after the specific transaction has went through. > > Manually this could be quite tedious with just doing the before > snapshot of the table data vs. the after data check. > > I would really appreciate any feedback I could get. There might be tools that can capture the traffic between the client and the engine, and parlay that into the SQL's that are being executed. Otherwise you are mostly fishing in the dark I think. The engine has the capability of writing all the SQL's to a file - along with the "query plan" which says how the query will be executed - eg index lookups, joins, sequential scans, etc This explanation is triggered when the client executes this SQL command: SET EXPLAIN ON and the output goes to your running directory if you are on the same machine, or into your home directory (from memory, but check the manual) if you are running against a remote database. If you are LUCKY, the software developers put a command line option on the programs to switch this on for development. Assuming the machine is UNIX (or you can send the executables to a UNIX or Linux machine) you can do this: strings programexecutable | grep -i explain If anything turns up, you're on the way to snooping out a command line switch or an environment variable that will help enormously. You might need to investigate a bit further using forensic techniques to find the exact way to switch on the explanations. In the absence of this lovely facility, there are two very crude techniques I can think of. 1) if you do an UPDATE STATISTICS for the tables, then record the information in the system tables containing this information (all listed in the manuals) and then you do some work, UPDATE STATISTICS again, then compare the results, you'll get crude indicators of which tables have had rows added or deleted. With a bit more nouse you'll learn a few more facts, but not too many. 2) You can dump the transaction logs from the engine, and piece together what's going on. It's fairly low level but at least it's comprehensive. I haven't done this but I think you'll be able to understand it. Beware that the output of the "onlog" command is copious and it will freeze the engine if it's dumping current logs, so you'll want to be the only person using the experimental database. Beyond this, someone else might be able to nominate some tools. The name "I-Spy" springs to mind...