RE: SET EXPLAIN / SQL

David K. Killough wrote: > Using 4GL/SQL, is there a way to > > 1) obtain SET EXPLAIN output without executing the query and > 2) accessing that output in order to conditionally run the query > > We have a very flexible way for users to create queries (sort of like > construct, but more powerful.) The price, of course, is that they could > create a resource intensive query that may not be desired (we have 400 > users.) If we could parse the cost or indexing strategy we could determine > when a heavy query has been requested, and protect our system from running > too many of these at a time. We have Dynamic Server 7.3 (UC3-1) and 4GL > 6.05 (we just got 4GL 7.2 but have not installed it) > > Thanks in advance for any response..... Dave Killough > > David, A couple of thoughts. If you SET EXPLAIN ON and then PREPARE the query, the sqexplain.out file will be written without the query actually being executed. In fact, the sqexplain results are actually available in an undocumented table of sysmaster - syssqexplain. It's not considered a good idea to build systems around these 'unsupported' tables, since they may disappear from a future release, but it does hold some interesting information. Be warned - I crashed an instance at an Informix Sys Admin course once by querying this table when no queries were running that SET EXPLAIN ON. Secondly, you could also look at the Memory Grant Manager that can 'gate' queries if they exceed certain thresholds, to avoid running too much heavy-hitting stuff at once. cheers RET