sqlexplain on procedure

hoping someone can help here using IBM Informix Dynamic Server Version 11.50.FC6 When I put set explain on session id on database It does not show the sql in the priocedure - not that i can tell is that correct I used onmode -Y 1513072 1 onstat -g sql shows thisLast parsed SQL statement : execute procedure get_serial(?)Is there a way I can find out what sql is run at the time proc is run on database

You can't get SET EXPLAIN output from an SPL stored procedure. You have two choices. You can either add TRACE statements to the particular procedure you want to debug or you can use the SPL debugger included in AGS's Server Studio. If you haven't used SS yet, you get a free full function license 30 day trial of Server Studio along with your Informix installation. That includes the SPL debugger. If you want to continue to use the debugger after the trial, you will have to purchase the Server Studio Developer Edition or Enterprise Edition license, but for the trial period it's free. Informix 11.70, formerly code named Panther, includes the ability to debug SPL procedures, but that is presented only as a standard debugging API, so you either have to program your own debugger, use the next release of Server Studio (which will use the API), or use IBM's Data Director or some other debugger that can subscribe to the API. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Mon, Nov 1, 2010 at 6:29 PM, KARL OLIVER <karl.oliver@maf.govt.nz> wrote: > hoping someone can help here > using IBM Informix Dynamic Server Version 11.50.FC6 > When I put set explain on session id on database > It does not show the sql in the priocedure - not that i can tell is that > correct > I used onmode -Y 1513072 1 > > onstat -g sql shows this> Last parsed SQL statement : > execute procedure get_serial(?)> Is there a way I can find out what sql is run at the time proc is run on > database > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00163631095564e43704940597c8

thanks for that information

You can set explain on and update statistics for procedure, then look at the sqexplain.out file also for some clues as to SPL execution paths. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of KARL OLIVER Sent: Tuesday, 2 November 2010 9:54 AM To: ids@iiug.org Subject: Re: sqlexplain on procedure [21848] thanks for that information ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. *************************************************************** This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily the views of the Land and Property Management Authority. This email message has been swept by MIMEsweeper for the presence of computer viruses. *************************************************************** Please consider the environment before printing this email.

There is one other option which will help you identify performance bottleneck in SPL. This is the SQLTRACE facility in version 11. While you will NOT get the statement text, you will get the stored procedure name, any calling procedures, SQL statement type and many statistics about the statement. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 11/01/2010 03:47:26 PM: > [image removed] > > Re: sqlexplain on procedure [21847] > > Art Kagel > > to: > > ids > > 11/01/2010 03:47 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > You can't get SET EXPLAIN output from an SPL stored procedure. You have two > choices. You can either add TRACE statements to the particular procedure > you want to debug or you can use the SPL debugger included in AGS's Server > Studio. If you haven't used SS yet, you get a free full function license 30 > day trial of Server Studio along with your Informix installation. That > includes the SPL debugger. If you want to continue to use the debugger > after the trial, you will have to purchase the Server Studio Developer > Edition or Enterprise Edition license, but for the trial period it's free. > > Informix 11.70, formerly code named Panther, includes the ability to debug > SPL procedures, but that is presented only as a standard debugging API, so > you either have to program your own debugger, use the next release of Server > Studio (which will use the API), or use IBM's Data Director or some other > debugger that can subscribe to the API. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > 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 Mon, Nov 1, 2010 at 6:29 PM, KARL OLIVER <karl.oliver@maf.govt.nz> wrote: > > > hoping someone can help here > > using IBM Informix Dynamic Server Version 11.50.FC6 > > When I put set explain on session id on database > > It does not show the sql in the priocedure - not that i can tell is that > > correct > > I used onmode -Y 1513072 1 > > > > onstat -g sql shows this> > Last parsed SQL statement : > > execute procedure get_serial(?)> > Is there a way I can find out what sql is run at the time proc is run on > > database > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --00163631095564e43704940597c8 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >