Handy stored procs for Sybase IQ
 
Below are some stored procs I wrote or adapted for Sybase IQ. I hope you'll find them useful -- your feedback is appreciated.

Current contents:

 
Sybase IQ
12.x 15.x
Last updated: 20 February 2011

sp_iqautoformat / sp__iqautoformat
One of the things that keeps surprising me in IQ is how poorly formatted the results of IQ stored procedures often are. Only when using dbisql in GUI mode, stored proc results are getting formatted conveniently. In all other cases (like dbisql -nogui, or ASE's isql), the results are often formatted very wide to the extent that they are hard to read. This is troublesome when you want to run some of those sp_iq... procedures in batch mode (generating a daily report on some aspects of your IQ server, for example).

ASE has the stored proc sp_autoformat, which basically takes a table as input and formats each column as narrow as possible, based on the actual contents of the column.
In an attempt to get the same functionality on IQ, I ported ASE's sp_autoformat to IQ. The proc is named sp_iqautoformat - download it here.

Examples:
sp_iqautoformat 'MyTable'

sp_iqautoformat 'sys.sysdbspace'

sp_iqautoformat 'dbo.sysobjects', @selectlist='name', 
    @whereclause='where type=''U''', @orderby='order by lower(name)'


Some notes (also see the header of the downloaded file):
  • sp_iqautoformat requires IQ 15.0 or later; it also runs fine on SQL Anywhere 11 or later (perhaps also on earlier versions of SQLA, but this was not tested).
  • For online syntax help, run sp_iqautoformat without parameters.
  • sp_iqautoformat can also be called as sp__iqautoformat.
  • When using sp_iqautoformat with the optional select-list, where-clause or order-by parameters, you can easily cause invalid SQL to be generated internally in sp_iqautoformat. Given IQ's different approach to error messages than ASE's, such errors may be tricky to debug.
  • Please note that sp_iqautoformat is a pretty heavy procedure: it takes lots of processing in order to produce at a nicely formatted result set. That means (i) you should expect some additional response time overhead when using sp_iqautoformat; and (ii) use this only for small result sets (i.e. < 1000 rows). When using it on large result sets, something will probably hang or break somewhere.
  • sp_autoformat is perhaps the most complex system stored proc in ASE. Porting it to IQ didn't exactly make things simpler... Should you feel the urge to dig into the SQL code, you may well find some rubbish lying around (like some dead code, not-so-nice written algorithms, etc.). Sorry for that -- and feel free to drop me a note.
    Given the complexity of sp_autoformat, the IQ version still uses T-SQL syntax. I'll take my hat off to anyone who fully rewrites sp_iqautoformat in Watcom SQL -- but for me, it was a bridge too far.



 
Sybase IQ
12.x 15.x
Last updated: 20 February 2011
sp__iqcheckoptions
The proc sp__iqcheckoptions roughly does the same as sp_iqcheckoptions, namely list the IQ options that are set to a non-default value. However, it formats its output much nicer than sp_iqcheckoptions (in fact, it uses sp_iqautoformat for this; see above).
In addition, sp__iqcheckoptions has some handy additional functionality:
  • it lets you filter the current non-default options being reported; this is functionality I'm really missing in the standard IQ procs (writing a select query on top of sp_iqcheckoptions is too much hassle to use quickly, IMHO)
  • it lets you display the default value for one or more options; handy when you want to know what the default is.
  • it lets you display those options whose names were spelled wrongly; these options are still stored in sysoptions and you might want to clean them up, if only you coudl easily find them
See the examples below.

Download sp__iqcheckoptions here.

Examples:
-- shows all non-default options:
sp__iqcheckoptions 


-- show only the non-default DML_CoreNN options
-- by filtering on substring 'dml':
sp__iqcheckoptions dml


-- show the default setting for 'delayed_commit_timeout'
-- (actually, for all options containing 'delay') :
sp__iqcheckoptions delay, showdefault

-- alternatively, use the shorthand 'dft' instead of 'showdefault'
sp__iqcheckoptions delay, showdefault


-- show misspelled options
-- (can also specify 'invalid' instead of 'invalid_options'):
sp__iqcheckoptions invalid_options


-- show syntax help:
sp__iqcheckoptions '?'


Some notes (also see the header of the downloaded file):
  • sp__iqcheckoptions requires on IQ 15.0 or later since it relies on sp_iqautoformat (see above).
  • sp__iqcheckoptions does not run on SQL Anywhere.
  • sp__iqcheckoptions reports option settings slightly different (=better) than sp_iqcheckoptions in the following cases:
    • When running 'set option jsmith.<option-name>=value', sp_iqcheckoptions shows this setting as 'Temporary' if sp_iqcheckoptions is executed by user 'jsmith' himself, but as 'Permanent' when executed by user 'DBA'. Instead, sp__iqcheckoptions will show this permanent setting as 'Permanent', irrespective of the executing user.
      This is more correct than sp_iqcheckoptions, which incorrectly displays this permanent setting as temporary when executed by the user himself.

    • When 'set option jsmith.<option-name>=value' has been set and user 'jsmith' then runs 'set temporary option <option-name>=other-value', then sp__iqcheckoptions will show the permanent setting as well as temporary setting (provided the values for both are different). In this case, sp_iqcheckoptions would only display the temporary option. sp__iqcheckoptions will show both options since this is a better reflection of how the options are actually set.


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