Sybase ASE
11.0 11.5 11.9 12.0 12.5 15.x
Last updated: 24 December 2008
Tricks with CIS and XP Server
 
In Sybase ASE 11.5 and later, two powerful features are available: CIS (Component Integration Services, previously known as OmniConnect) and XP Server.
Please note: we're referring to XP Server here as a feature of Sybase ASE; this has NOTHING to do with Windows XP.

To demonstrate the technical possibilities of CIS and XP, this page contains some demo applications which exploit some of the features CIS and XP have available:
  • Adaptive Server talks to you: executing a stored procedure inside the server pops up a window prompting the user for input, and then writes this input into a database table.
  • ASE restarts itself: the stored procedure sp_restart_server (or its alias, sp_reboot) shuts down the server and immediately restarts it automatically.
  • Simulating dynamic SQL through CIS: a stored procedure sp_exec_dynsql which lets you execute a string containing dynamically created SQL statements. This works in ASE 11.5 and 11.9 and gives you roughly the same functionality as the new execute immediate feature in ASE 12.0.
  • DISK INIT made easy: a stored procedure sp_disk_init checks for sufficient free diskspace before creating the device. In fact, it contains some other features that make it worthwhile even when you're running ASE version 11.0 ...



Warning!
Please note that these tricks with CIS and XP may not be the best algorithms to build into your high-volume, multi-user, critical production systems, as CIS and XP really weren't designed to do this sort of thing. Some potential risks to bear in mind:
  • XP server doesn't scale very well (i.e. isn't very good at executing many ESPs concurrently). You probably shouldn't try to concurrently execute ESPs at the same high rates as you execute normal stored procedures in OLTP systems.
  • When executing OS commands through xp_cmdshell, checking things like the OS command return status may be difficult. Also, you cannot easily handle situations where OS commands prompt for input. Lastly, I've sometimes seen XP server hang while excuting an OS command through xp_cmdshell. Killing XP server on OS level may then be your only way out.
  • Be aware of potential security problems when using XP server: the setting of the configuration parameter xp_cmdshell context; has significant security implications (see the ASE System Administration Guide for details).
  • CIS functionality was not designed to log into your own server via a server name alias. Still, it will normally work, as CIS connectivity is built on CT-lib. Note that there's a risk of exhausting the number of available user connections when running this sort of application concurrently. Also, I've seen cases where something went wrong on some low level inside CIS, and the only way to clear it up was to reboot the ASE server (this was in ASE version 11.5, and CIS has presumably become more robust since then).
  • CIS was primarily designed as a connectivity product, and application performance is the price often to be paid for this functionality: performance is usually significantly worse than in applications where only "normal" T-SQL features are used (i.e. where CIS is not involved).
  • When you log in through the CIS back door, you'll end up in the default database for the login you're using. This may be a different database than where your sp_remotesql call originated from, so you should ensure all SQL statements explicitly specify the database name when referring to objects to avoid ambiguities.
  • Also consider issues like these: when a CIS-through-the-back-door session gets into an error condition such as "out of locks" or gets killed as a deadlock victim, how will you application respond to this ?

Because of these issues, the CIS- and XP-related tricks described on these pages are probably best suited for use in DBA-type tools, which are usually executed by only a single user at a time. Some useful examples could be the stored procedures mentioned above; also, the stored procedure sp_rv_update_statistics (which can be downloaded from here ) would be a good example of how to apply CIS functionality.

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