| Sybase ASE |
| 11.0 |
11.5 |
11.9 |
12.0 |
12.5 |
15.0 |
| 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.
|
|
