Cognos-8 blowing-up Informix IDS 11.50.FC6

Good afternoon friend, I am diagnosing some issues with some pretty bad SQL coming from Cognos-8 against my IDS 11.50.FC6 Linux system. One of the things I can't seem to be able to figure out is how to insert a Set Explain On statement into a Cognos 8 report. Cognos 8 won't allow me direct access to the SQL command that it's creating for me. Has anyone had experience enabling Set Explain with in Cognos 8 or Cognos 10? I would love to be able to insert: SET EXPLAIN ON FILE TO '/tmp/query_plan.out'; --- or --- SET EXPLAIN ON AVOIDEXECUTE FILE TO '/tmp/query_plan.out'; inside the Cognos report somehow. Any ideas, experience, or expertise you could lend me? Thanks in advance for your advice or suggestions. Very respectfully, Jonathan B. Smaby Pomona College phone: (909) 621-8506 email: jonathan.smaby@pomona.edu Web: http://profiles.google.com/jonathan.smaby -- Semper Paratus! "Always Ready!" "if you expect the unexpected, doesn't that make the unexpected...expected?" - Anonymous ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.

On Fri, Sep 30, 2011 at 16:09, Jonathan Smaby <Jonathan.Smaby@pomona.edu>wrote: > I am diagnosing some issues with some pretty bad SQL coming from Cognos-8 > against my IDS 11.50.FC6 Linux system. One of the things I can't seem to be > able to figure out is how to insert a Set Explain On statement into a > Cognos 8 > report. Cognos 8 won't allow me direct access to the SQL command that it's > creating for me. Has anyone had experience enabling Set Explain with in > Cognos > 8 or Cognos 10? > > I would love to be able to insert: > > SET EXPLAIN ON FILE TO '/tmp/query_plan.out'; --- or --- SET EXPLAIN ON> AVOID > EXECUTE FILE TO '/tmp/query_plan.out'; inside the Cognos report somehow. > > Any ideas, experience, or expertise you could lend me? > Can you set explain on at the server dynamically with ON-Mode: onmode -Y sessionid {0|1|2} The feasibility of this depends on whether your session lasts long enough for you to determine what its session ID is. Maybe you can create a stored procedure which is invoked in a very early SQL statement that sleeps for 30 seconds and then give yourself enough time to find the session ID and run the dynamic explain. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --000e0cd6b326828ab504ae347d1f

If you use a specific user the easiest way would be to create a sysdbopen() procedure for that user. Alternatively you can use the SQLTRACE funcionality together with OAT. On you engine version you should be able to trace for user session and database... This is not specific for Cognos of course... Regards. On Sat, Oct 1, 2011 at 4:39 AM, Jonathan Leffler <jonathan.leffler@gmail.com > wrote: > On Fri, Sep 30, 2011 at 16:09, Jonathan Smaby > <Jonathan.Smaby@pomona.edu>wrote: > > > I am diagnosing some issues with some pretty bad SQL coming from Cognos-8 > > against my IDS 11.50.FC6 Linux system. One of the things I can't seem to > be > > able to figure out is how to insert a Set Explain On statement into a > > Cognos 8 > > report. Cognos 8 won't allow me direct access to the SQL command that > it's > > creating for me. Has anyone had experience enabling Set Explain with in > > Cognos > > 8 or Cognos 10? > > > > I would love to be able to insert: > > > > SET EXPLAIN ON FILE TO '/tmp/query_plan.out'; --- or --- SET EXPLAIN ON> > AVOID > > EXECUTE FILE TO '/tmp/query_plan.out'; inside the Cognos report somehow. > > > > Any ideas, experience, or expertise you could lend me? > > > > Can you set explain on at the server dynamically with ON-Mode: > > onmode -Y sessionid {0|1|2}> > The feasibility of this depends on whether your session lasts long enough > for you to determine what its session ID is. > > Maybe you can create a stored procedure which is invoked in a very early > SQL > statement that sleeps for 30 seconds and then give yourself enough time to > find the session ID and run the dynamic explain. > > -- > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org > "Blessed are we who can laugh at ourselves, for we shall never cease to be > amused." > > --000e0cd6b326828ab504ae347d1f > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e65ae9226512c604ae3bc47d

You can use onmode -Y to turn on set explain for the entire session once Cognos is connected to the instance. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Sep 30, 2011 at 7:09 PM, Jonathan Smaby <Jonathan.Smaby@pomona.edu>wrote: > Good afternoon friend, > > I am diagnosing some issues with some pretty bad SQL coming from Cognos-8 > against my IDS 11.50.FC6 Linux system. One of the things I can't seem to be > able to figure out is how to insert a Set Explain On statement into a > Cognos 8 > report. Cognos 8 won't allow me direct access to the SQL command that it's > creating for me. Has anyone had experience enabling Set Explain with in > Cognos > 8 or Cognos 10? > > I would love to be able to insert: > > SET EXPLAIN ON FILE TO '/tmp/query_plan.out'; --- or --- SET EXPLAIN ON> AVOID > EXECUTE FILE TO '/tmp/query_plan.out'; inside the Cognos report somehow. > > Any ideas, experience, or expertise you could lend me? > > Thanks in advance for your advice or suggestions. > > Very respectfully, > > Jonathan B. Smaby > Pomona College > phone: (909) 621-8506 > email: jonathan.smaby@pomona.edu > Web: http://profiles.google.com/jonathan.smaby > -- > Semper Paratus! "Always Ready!" > > "if you expect the unexpected, doesn't that make the > unexpected...expected?" - > Anonymous > > ------------------------------------------------------------- > This message has been scanned by Postini anti-virus software. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba1eff2c7414f904ae46e6d2