Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

insert showplan text into table

2 posts in General Discussion Last posting was on 2013-03-20 23:54:33.0Z
ahood Posted on 2013-03-19 17:38:17.0Z
Sender: 7b0a.51489f19.1804289383@sybase.com
From: ahood
Newsgroups: sybase.public.ase.general
Subject: insert showplan text into table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5148a289.7b88.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 19 Mar 2013 09:38:17 -0800
X-Trace: forums-1-dub 1363714697 172.20.134.41 (19 Mar 2013 09:38:17 -0800)
X-Original-Trace: 19 Mar 2013 09:38:17 -0800, 172.20.134.41
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31730
Article PK: 1308479

I am working on a stored procedure to trap information on
problem queries. I want to insert the query and showplan
text into tables so we can pull them up later.

I have created a proxy table to the sp_showplan stored
procedure. Problem is that the showplan text is not part of
the selection set, it appears to be a series of print
statements. As a result the showplan text passes through to
the client instead of showing up as part of the proxy table
select results.

Any suggestions on how to get this output as part of a
selection set?

I would like to use the monSysPlanText table, but am
hesitant to configure 'plan text pipe max messages' to
100,000 or more in order to keep the plan in the pipe long
enough to read it. Replication to the system produces a
very large volume of queries and flushes data through the
pipe very quickly.


"Mark A. Parsons" <iron_horse Posted on 2013-03-20 23:54:33.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: insert showplan text into table
References: <5148a289.7b88.1681692777@sybase.com>
In-Reply-To: <5148a289.7b88.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <514a4c39$1@forums-1-dub>
Date: 20 Mar 2013 15:54:33 -0800
X-Trace: forums-1-dub 1363823673 172.20.134.152 (20 Mar 2013 15:54:33 -0800)
X-Original-Trace: 20 Mar 2013 15:54:33 -0800, vip152.sybase.com
Lines: 25
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31731
Article PK: 1308534

monSysPlanText or processing the query plan via the client/front-end are the only solutions I can think of.

Unfortunately there's no monProcessPlanText ...

On 03/19/2013 11:38, ahood wrote:
> I am working on a stored procedure to trap information on
> problem queries. I want to insert the query and showplan
> text into tables so we can pull them up later.
>
> I have created a proxy table to the sp_showplan stored
> procedure. Problem is that the showplan text is not part of
> the selection set, it appears to be a series of print
> statements. As a result the showplan text passes through to
> the client instead of showing up as part of the proxy table
> select results.
>
> Any suggestions on how to get this output as part of a
> selection set?
>
> I would like to use the monSysPlanText table, but am
> hesitant to configure 'plan text pipe max messages' to
> 100,000 or more in order to keep the plan in the pipe long
> enough to read it. Replication to the system produces a
> very large volume of queries and flushes data through the
> pipe very quickly.