sybase 15 vs 12.5 builtin sp resultset issue

by: Joe_WoodhousePosted on 2009-08-29 at 01:26:14ID: 25213340

Hmm! I've done a few ASE 15 upgrades recently and haven't run into this. I'm not sure I quite understand the issue. Can you give us an example of how the output format differs between 12.5 and 15?

Do you mean the format of specific built-in procedures? If so, you could always rewrite the built-ins, or call them through a wrapper, or take the 12.5 versions of the procs you need from a 12.5 instmstr script and (in some cases) tweak for 15...

by: gagaliyaPosted on 2009-08-31 at 07:18:13ID: 25222974

if i run "exec sp_helpindex test"

on 12.5 it returns back 1 result:

index_name index_description index_keys index_max_rows_per_page index_fillfactor index_reservepagegap index_created
...........

on 15 it reutnrs back 3 results:

1)
Object has the following indexes
2)
index_name index_keys index_description index_max_rows_per_page index_fillfactor index_reservepagegap index_created index_local
........
3)
index_ptn_name index_ptn_seg
........

i need 15 to return back 1 resultset similar to 12.5, thanks.

by: gagaliyaPosted on 2009-08-31 at 07:22:14ID: 25223006

the problem with tweaking the builtin sp is we have about 15 databases, and to avoid touching the legacy code it means we need to deploy 15 copies of the custom sp_helpindex to all those database, we like to avoid that and find a solution hopefully some setting change that will give back the same result.

I dont understand why sybase would go and do those things, all their literature assured us it will be full backward compatible.

thank you for your help.

thank you

by: Joe_WoodhousePosted on 2009-08-31 at 08:03:21ID: 25223335

Hmm. That's nothing to do with changes in the ASE codeline - that's just a change in the accompanying stored procs.

Many had to be changed to deal with the changes in ASE data structures - every table is now considered partitioned, for example, and that's relevant to indexes, so of course sp_helpindex has to understand and report on those changes.

I'm not aware of Sybase saying anywhere that ASE 15.0.x is 100% back compatible - their whitepapers talk at length about the GROUP BY/ORDER BY change, and the possibility of changes in cursor result sets unless defined as insensitive.

I don't think it's reasonable to expect Sybase to keep all the built-in stored procs the same when they have to do different things in different ways...

Basically what you're dealing with is version control of the built-in procs. This isn't actually an ASE 15 issue per se because it is still possible to write SQL in a proc that returns the old behaviour.

That said... I think your best answer is to change the legacy application - how will it cope with a table that has global vs local indexes, say? Sure, we can easily write SQL that gives you the old display, but... there's a reason Sybase changed the proc... because the old display just isn't good enough in ASE 15.0.

I can think of ways around your code deployment issue, if you have to change the procs rather than the app: does your app connect as a specific login? If so, you could have a wrapper called sp_helpindex in sybsystemprocs that tests for whether it is the app login, and if so calls the old sp_helpindex code, but for anyone else calls the new.

I don't see any way around this though, you have either an app change or a stored procedure change ahead of you...

by: gagaliyaPosted on 2009-08-31 at 08:15:16ID: 25223422

yeah looks that way :( one last question, do you know how to extra the sql from the built-in stored proc. I want to look at the actual sql for helpindex on 15 vs 12.5 and make a determination.

Thanks again

by: gagaliyaPosted on 2009-08-31 at 08:15:57ID: 25223429

i mean to extract the sql from the built-in stored proc, i searched but couldnt find a solution.

by: Joe_WoodhousePosted on 2009-08-31 at 08:22:16ID: 25223500

It'd be nice if there was a switch to make every run as close to ASE 12.5.x as possible... :/ I think though here it's fair enough - indexes are more complicated, there has to be a change to sp_helpindex to deal with that.

For getting the actual SQL, you have many methods:

- in sybsystemprocs, run sp_helptext sp_helpindex
- search installmaster in $SYBASE/$SYBASE_ASE/scripts
- use ddlgen (in the ASEP* directory I think)
- use any of the various GUI DBA tools against a live 15.0 and 12.5 server to get the SQL

Sorry I don't have a nice answer for you on this one...

by: gagaliyaPosted on 2009-08-31 at 08:46:33ID: 25223724

ah i forgot to set the db to sybsystemprocs, was wondering what happened. Thank you Joe, you have been very helpful!

by: gagaliyaPosted on 2009-08-31 at 08:47:19ID: 31621910

thank you!

by: Joe_WoodhousePosted on 2009-08-31 at 08:50:27ID: 25223763

Yeah, sp_helptext is looking for local database only. The sp_procs in sybsystemprocs kindas obscure that because you can execute them from anywhere, but as arguments to other procs you have to have sybsystemprocs as your current database. I've been bit by that before which is why I remembered. :)

Question

sybase 15 vs 12.5 builtin sp resultset issue

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Sybase Database

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press