(back)
 
Sybase ASE
11.0 11.5 11.9 12.0 12.5 15.x
Last updated: 06 December 2000
FAQ about "New Generation ASE System Stored Procedures"
 
Other links about these System Stored Procedures(SSPs):
  • the location to download the software;
  • the ISUG Tech Journal article discussing background and architecture of these SSPs;
  • the manual pages for the SSPs.


Q1.1 Why do these stored procedures have a name starting with "sp_rv_" ?
Q1.2 Can I rename the procedures from "sp_rv_..." to something else ?
Q1.3 What makes these "New Generation SSPs" different from the normal Sybase SSPs ?
Q1.4 What's the basic structure of these "New Generation SSPs" ?
Q1.5 Why has the code been "obfuscated" ?
Q1.6 So is it safe to install and use stored procs if you cannot verify what they're doing ... ?
Q1.7 What about the "execute immediate" feature in ASE 12.0 ?
Q1.8 Is it allowed to bundle or sell these SSPs ?

Q2.1 Why is the following function not implemented: ...... ?
Q2.2 Will these SSPs also run on MS SQL Server ?
Q2.3 Is there any support for these SSPs ?

Q3.1 I've accidentally dropped the tables in sybsystemprocs. What do I do now ?
Q3.2 Shouldn't those sybsystemprocs tables be cleaned up ?
Q3.3 Why does "sp_rv_update_statistics 'execute' " not work ?


Q1.1 Why do these stored procedures have a name starting with "sp_rv_" ?
This is to distinguish these procedures from the standard ASE system stored procedures: many DBAs like their non-default SSPs named in some special way to indicate the fact that these are not the standard Sybase SSPs that "sybsystemprocs" normally contains.
So why is it "sp_rv_", and not, say, "sp_xyz_" ? Well, check out my initials ...

Q1.2 Can I rename the procedures from "sp_rv_..." to something else ?
It is best not to change the "sp_rv_" prefix for these stored procedures, as certain functions won't work correctly anymore. The only exception is "sp_rv_dboption" which you can rename as you like.

Q1.3 What makes these "New Generation SSPs" different from the normal Sybase SSPs ?
The main feature of these "New Generation SSPs" is that you can perform actions in different databases in the server in just a single command (like adding a datatype to all databases in the server). The traditional SSPs by Sybase do not have this capability, meaning that you'd have to execute a command repeatedly for each database. Furthermore, in many cases there's no need for the "use" command to switch databases anymore; this also means many of the shell-script tools that DBAs have created over time are not needed anymore.
In short, these SSPs will give DBAs more control in a multi-database environment, allowing them to work more efficiently.

Q1.4 What's the basic structure of these "New Generation SSPs" ?
Basically, all procedures are based on two simple T-SQL features, which are described in detail in the ISUG Tech Journal article . Check out the source code of "sp_rv_dboption" : it's the best example of how these features are used. For the other SSPs, a significant amount of functionality has been engineered on top of these basic principles.

Q1.5 Why has the code been "obfuscated" ?
As of version 2.1 , the SQL is published without attempts to hide anything. At the time, there were reasons for keeping things hidden, but these are not relevant anymore now.

Q1.6 So is it safe to install and use stored procs if you cannot verify what they're doing ... ?
N/A. See previous point.

Q1.7 What about the "execute immediate" feature in ASE 12.0 ?
The next major release of ASE (version 12.0, a.k.a. "Avatar") will contain the long-awaited "execute immediate" feature. This feature makes it possible to execute a T-SQL command string which is dynamically created in a character variable. As many ASE users expect this feature to deliver significant new flexibility, one might wonder whether the existence of the new-generation SSPs described in this article is still justified in version 12.0. It certainly is: the "execute immediate" functionality only overlaps with "feature #2" discussed in the ISUG Tech Journal article (executing a variable stored procedure); "feature #1" (controlling the database context), being the other building block of these SSPs is not related to "execute immediate" at all. Furthermore, "execute immediate" does not support the "use" command, while the new SSPs will also work in ASE versions 10.x to 11.9.x.
Note: version 2.1 uses "execute immediate" in various ways. Check it out!


Q1.8 Is it allowed to bundle or sell these SSPs ?
No. It is explicitly not allowed to use these SSPs for any commercial purpose. You may only use these for your own professional, non-commercial purposes. Please check out the copyright issues .
If you see great commercial opportunities for these SSPs, we would need to talk first...


Q2.1 Why is the following function not implemented: ...... ?
Obviously, it wasn't possible to implement everything I could think of. So if you feel a certain feature would be really useful, please E-mail me your idea and I'll do my best to stick it in...

Q2.2 Will these SSPs also run on MS SQL Server ?
No, they won't. These SSPs are written specifically for ASE, using ASE-specific features. Porting these SSPs to MS SQL Server could be an interesting thing to do, but nothing is planned.

Q2.3 Is there any support for these SSPs ?
There's no guarantee of getting support, but having said that, I'll try to help when possible. In case you encounter any problems, please post a message to the newsgroup "comp.databases.sybase", and I'll try to help out if my own schedule allows. You could of course also send me your problems directly through E-mail, but I'll handle newsgroup questions with higher priority.


Q3.1 I've accidentally dropped the tables in sybsystemprocs. What do I do now ?
There are two tables in the "sybsystemprocs" database ("tab_sp_rv_incl_db" and "tab_sp_rv_excl_db"), which contain the settings made through "sp_rv_exec_include_db" and "sp_rv_exec_exclude_db". If these tables have disappeared, "sp_rv_exec" and the other SSPs won't run anymore, but generate lots of error messages.
If this happens, you can simply repair this by running "sp_rv_exec_crtab", which will recreate the tables.

There is also a table named "tab_sp_rv_msgs", which only contains informational messages (the stuff that's printed when you use '?' as a parameter). To restore this table, you must re-run the installation SQL script (new_ssp.sql).

Q3.2 Shouldn't those sybsystemprocs tables be cleaned up ?
The tables "tab_sp_rv_incl_db" and "tab_sp_rv_excl_db" contain the settings made through "sp_rv_exec_include_db" and "sp_rv_exec_exclude_db". These settings are session-specific, and because they're not automatically cleaned up when the session exits, there may be some accumulation of "old" rows. Every now and then, "sp_rv_exec" will try to clean up rows which are obviously obsolete, so the tables shouldn't get too large.
If you wish, you can also manually delete the contents of these tables, but be aware that you may affect settings for currently active sessions.

Q3.3 Why does "sp_rv_update_statistics 'execute' " not work ?
In order to run "sp_rv_update_statistics" with the "execute" option, you need to be able to use CIS features, which are available only in ASE 11.5 and later (except on AIX, where you'll need 11.9.2 or later). If you run an earlier version of ASE, you cannot use the "execute" option, but only the "generate" option.
When running "sp_rv_update_statistics", the server configuration is examined, and if necessary you'll be prompted to run "sp_rv_cis_setup1", restart the server, and run "sp_rv_cis_setup2". This will change some server configuration parameters to allow CIS to run properly. In case you still get errors, you probably have a CIS problem. Verify your CIS setup as follows (assuming your servername is YOURSERVER) :
1> connect to YOURSERVER_MYSELF
2> go
Entered passthru mode to server 'YOURSERVER_MYSELF'.
1> disconnect
2> go
Exited passthru mode from server 'YOURSERVER_MYSELF'.
If you don't get the message "Entered passthru mode to server 'YOURSERVER_MYSELF'", this means there is a CIS problem. Check the server errorlog for messages indicating CIS didn't initalise properly.
If the above worked properly, try the following command :
exec sp_remotesql YOURSERVER_MYSELF, "select getdate()"
This should return the current date and time. Basically, when these example statements don't work, you'll have to get this working first.
In case you've customised the remote access authorisations, check if you have object owner permissions (or better: "dbo" permissions) after connecting to YOURSERVER_MYSELF: you need the proper permissions to run "UPDATE STATISTICS".



 This document is located at www.sypron.nl/new_ssp_faq.html

 Copyright © 1998-2014 Rob Verschoor/Sypron B.V. 
 All rights reserved. All trademarks are acknowledged.