||SQL Capture Techniques - "You don't know what you've got till it's gone"|
||Doug has been an Informix developer for 30 years and is a
Certified System Administrator for IDS 11.50 & 11.70.
After graduating in Management Sciences at Cambridge, he
spent 4 years at Reuters, pioneering the original
Informix product for departmental database systems. A
consulting career in a variety of industries followed,
including sole development of major Informix 4GL
applications such as a bespoke ERP/accounts system for
Lee Lighting and Shepperton Studios, and the Carefone
mobile phone billing and call centre package for service
providers world-wide. He later maintained the PRDB and
Infax systems at the BBC for 4 years.
Doug has been with Oninit Consulting in UK since 2008
providing remote Informix DBA services underpinned by AGS
Server Studio & Sentinel, and co-designed their custom
report pack with Art Kagel. He wrote the Oninit Archiver
which automatically navigates foreign keys to unload and
delete old data from complex hierarchies.
He was previously a council member of the UK Informix
User Group and now presents at IIUG, particularly on his
CGI-SQL invention, which enables self-refreshing Excel
workbooks via Informix Web queries without any ODBC
client being installed.
Doug contributes all SQL code for the open source
SQuirreL SQL Client Informix plug-in.|
|Analysis of SQL statements going through a database
can be the most important task to improve user response
times. Even if you think all is well, you may discover
coding faults or unexpected choices made by the query
optimizer, resulting in longer execution times and higher
system load, that can be easily fixed once you know which
are the worst.
For some applications, it may be a security requirement
prove at any time which users have made schema changes or
updated certain tables, and it is usually more meaningful
know what SQL was run rather than what data changes were
made (for example via "onlog" or "onaudit").
This presentation will compare commercially available
products with what can be achieved via free Informix
scripting approaches. Saving statements completing within
sample time interval - or even externally recording
everything on a continuous basis - is possible with the
SQLTRACE feature from IDS 11 upwards or 3rd party network
snooping tools. Prepared statements can be extracted from
the "sysconblock" table or sampled periodically with AGS
Sentinel, and there are of course several "onstat"
giving a snapshot of current SQL operations.
Some scripting suggestions will be provided and Excel
templates outlined into which results can be loaded and