Re: How to estimate query cost before execution

Hello LonelyWolf, SGBoyUK is right. If your SQL is already running, try "onmode -y <sid> 1 output.filename" where "sid" is the session ID of the SQL. And, yes, there is some information in the sysmaster "sysrstcb" and "syssqexplain" tables (join them with "sysrstcb.sid = syssqexplain.sqx_sessionid"). -L.S.

On 9 syys, 18:24, LIGHT SCANS <light_sc...@yahoo.com> wrote: > Hello LonelyWolf, > > SGBoyUK is right.  If your SQL is already running, try "onmode -y > <sid> 1 output.filename" where "sid" is the session ID of the SQL. > > And, yes, there is some information in the sysmaster "sysrstcb" and > "syssqexplain" tables (join them with "sysrstcb.sid = > syssqexplain.sqx_sessionid"). > > -L.S. Thank you for your answers! Yes I'm familiar with SET EXPLAIN ON AVOID_EXECUTE but it is not possible to build "cost-based" control automatic around it. So far as I understand it's more research tool and is not suitable controlling multiuser environment and making decisions if some query is accepted or not. Theoritically it's possible to get the output file back to calling process with some filetoclob- or load-command but overhead doing it is too big. Is there any better options for this kind of "gate-keeper" problem ?

LonelyWolf wrote: > On 9 syys, 18:24, LIGHT SCANS <light_sc...@yahoo.com> wrote: >> Hello LonelyWolf, >> >> SGBoyUK is right. If your SQL is already running, try "onmode -y >> <sid> 1 output.filename" where "sid" is the session ID of the SQL. >> >> And, yes, there is some information in the sysmaster "sysrstcb" and >> "syssqexplain" tables (join them with "sysrstcb.sid = >> syssqexplain.sqx_sessionid"). >> >> -L.S. > > Thank you for your answers! > Yes I'm familiar with SET EXPLAIN ON AVOID_EXECUTE but it is not > possible to build "cost-based" control automatic around it. So far as > I understand it's more research tool and is not suitable controlling > multiuser environment and making decisions if some query is accepted > or not. Theoritically it's possible to get the output file back to > calling process with some filetoclob- or load-command but overhead > doing it is too big. Is there any better options for this kind of > "gate-keeper" problem ? I'm not sure if I understand you completely. Version 11.50 has a functionality which could help. It has what IBM calls Visual Explain. To be clear, the Visual Explain is a functionality that some tools may provide (data studio does it, and I believe tools like AGS do it, or will do it in a near future). This functionality is based around a new procedure called EXPLAIN_SQL. This routine receives some obscure parameters and returns an xml with the query plan and estimated costs. If you were using version 11.50 (and I mention this because you may have plans to do it), you could create some kind of service that receive the query and uses this to decide if it should be run or not... With version 11.10, it can be harder... Some ideas: - Use the set explain file feature and point to a remote filesystem mounted on the database server. Also use the AVOID_EXECUTE You need to parse the file... - I haven't tested if the syssqexplain contains the query if you use avoid_execute... Regards, -

Turn PDQ on You can then limit the resources available to PDQ queries and even I think limit the number of PDQ queries that can run. This will mean that other queries and still run and your slow, complex queries will take even longer. I say bring back SCAFS!