SYBASE and SET SHOWPLAN

[Mark Risman <mrisman@PAINEWEBBER.COM>]

I realize the following question was (obviously) already asked, but I'm wondering if anyone ever encountered a solution to this problem (or a reason why it should not work).  I have also tried "set showplan" inside a begin-sql, and the SQR engine takes the returned query plan as a string of error messages and halts the program.

- Mark


From: "McCusker, Patrick W." <pmccusker@JHANCOCK.COM>
Date: Wed, 5 May 1999 12:02:40 -0400

> Good Afternoon everyone -
>
>       We have an SQR application that takes about four hours to complete.
> WIthout getting into too many details, the app creates two very large temp
> tables and joins them.  Queries against the joined temp tables always
> resulted in a table scan.  We have added an index on one of the temp
> tables
> and want to verify that we are hitting it in the query.  We have tried to
> do
> a set showplan on right before the Begin-Select and are getting the
> following error message :
>
> Wed May  5 10:45:50 EDT 1999
>
>
> SQRT V4.2.1
> Copyright (C) SQRIBE Technologies, 1994-98.  All Worldwide Rights
> Reserved.
>
> after showplan <-------------- DEBUG DISPLAY
> before begin-select <-------------- DEBUG DISPLAY
> (SQR 5528) Sybase CT_RESULTS - Use db error in cursor 0:
>    (6248)
>
>    (6289) QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>    (6248)
>
>    (6248)
>
>    (6201)     STEP 1
>
>    (6203)         The type of query is USE DATABASE.
>
>    (6248)
>
> (SQR 1803) CONNECT failed.  Perhaps username/password incorrect.
>
> SQRT: Program Aborting.
>
>       We have tried the query in isql and can verify that we should hit
> the index, but there is a lot of processing going on and would like to be
> able to confirm that we are getting a good query plan when the SQR app
> runs.
>
>       Any ideas?  We are using Sybase 11.
>
> Thanks,
>
> Patrick
>
>
> Patrick McCusker
> Client Server Application Development
> Product and Distribution Support Technology Services, C-4
> pmccusker@jhancock.com
> (617) 572-0615
>
>