Sybase Tips, Tricks, Tools & Documentation by Rob Verschoor

www.sypron.nl

Sybase Tips & Tricks
by Rob Verschoor

Search this site

Sybase Books by Rob V.

ASE QuickRef Guide (6^thed.)

RepServer QuickRef
Guide (3^rded.)
IQ QuickRef Guide (1^sted.)
Tips, Tricks & Recipes (2^nded.)

Buy these books now!

Quick Intro to ASE

What is Sybase ASE ?
Free copies: ASE, IQ & Linux
Finding ASE docs
Getting help with ASE
Sybase-related WWW links
Recommended books
So... you wanna be a DBA?

Tips & Tricks

Sproc results in a table
User-defined SQL functions
Identity gaps
CIS & XP tricks
Granting SA-commands to non-SA users
Tricks with 'sqsh'
Simulating dynamic SQL(1)
Simulating dynamic SQL(2)

Background Docs

MDA tables
ddlgen
Info on #temp tables
Login triggers
Avoid ASE perf. problems

Sybase IQ

What is Sybase IQ ?
Free copy of IQ
Handy stored procs for IQ
Using IQ functions in ASE
IQ-to-ASE ODBC connections

Replication Server

RepServer tips & tricks

ASE Tools

sybinit4ever
sp_exec_table
sp_recreate
SQL-to-XML
Identity gap repair
dbccdb fault info
ASE/RS errorlog checker
"New Generation" sp_ procs
Blank passwords
Workload capture with Auditing

Miscellaneous

ASE quiz
SQL Poetry
Fun stuff
Sybase certification tips
DBA interview questions
Presentations etc.
Miscellaneous tools
Blog @ sap.com

Old Stuff

sp_license
Query filter
New features in 12.5.0.1
ASE Replicator

ASIQ QuickRef(free)
RepServer QuickRef(free)
Hist. Server QuickRef(free)
ASE QuickRef Supplement(free)

Mailing List

About this site

Who is Rob Verschoor?
What is Sypron B.V.?
2001 ISUG Award
CV/Resume
Customers
Certifications
Consulting
Info for Recruiters
Support this site!

Site Map Shop Quiz Mailing List Feedback About this site Copyright/Legal

Last updated: 18 March 2013

Site Map

Welcome to www.sypron.nl, a web site dedicated to the Sybase products ASE, Replication Server and IQ.
Most content on this web site has been created by Rob Verschoor, a Sybase Certified DBA for Sybase ASE, Sybase IQ and Sybase Replication Server. Some material has kindly been contributed by other authors as indicated.

The information on this web site is copyrighted material, available free of charge. Please be aware of the applicable copyright issues, the legal disclaimer, and some restrictions.

Contents: (bold = most popular)

What's new at www.sypron.nl
Sybase books by Rob Verschoor

Quick introduction to Sybase ASE
     What is ASE? - ASE for free - Documentation - Help - Links - Books - Being a DBA

Tips & Tricks
     Identity gaps - CIS&XP - UDFs - Dynamic SQL: (1), (2) - Grant SA rights - sqsh tricks -
     sproc-in-table

Background Documentation
     MDA tables - ddlgen - #temp tables - Login triggers - ASE Replicator (old) -
     Performance problems - New in 12.5.0.1

Sybase IQ
     What is IQ? - IQ for free - IQ-to-ASE with ODBC - Using IQ features in ASE - Handy IQ procs

Replication Server
     RS tips

ASE Tools
     sybinit4ever - sp_exec_table - dbccdb fault info - sp_license - sp_recreate - SQL-to-XML -
     Identity gap repair - Errorlog tool - sp_* procs - Blank password - Workload capture with auditing -
     Misc.Tools - Query filter (old)

Miscellaneous Topics
     ASE quiz - SQL Poetry - Fun! - Certification - DBA Interview Q's - About Rob V. -
     About Sypron B.V. - Consulting - Customers - Support this site!

Free Quick Reference Guides
     ASIQ (old) - RepServer (old) - HistServer (old) - ASE (old)

What's new at www.sypron.nl (March 2013):

This month's quiz question is about a trick to perform database dumps to the NULL device...

The Sybase newsgroups at forums.sybase.com will be closed down by the end of March 2013. Go here for more information about the new location of these forums at SAP's SCN web site.

The stored procedures to grant SA rights to non-SA users was updated to also run on ASE 15.7 (thanks to Olav Georg Karlsen for highlighting the issues that stopped it from working).

Fixed an integer-overflow bug in sp_mda_diskIO (thanks to Rick Skoroszewski for highlighting the bug).

Changed the 'sp_' prefix for the SQL UDF examples to 'sp_f_'.

Sybase books by Rob Verschoor


"The Complete Sybase ASE Quick Reference Guide" - 6^th edition -- an indispensable resource for any ASE DBA or developer, covering all commands, functions and system stored procs in ASE. The 6^th edition is updated for version 15.7 ESD#3 (SAP Business Suite runs on ASE 15.7) Available now!		"The Complete Sybase IQ Quick Reference Guide" - 1^st edition -- a must-have for every IQ DBA or developer, covering all commands, options, startup switches and background info you may need in practice. Covers IQ 15.0 - 15.4. Available now!		"The Complete Sybase Replication Server Quick Reference Guide" - 3^rd edition, updated for RS 15.7.1 -- an essential companion for every RepServer DBA. Available now!

		"Tips, Tricks & Recipes for Sybase ASE" - 2^nd edition -- a unique book full of handy tips & tricks to make the most of ASE. The 2^nd edition is fully reviewed and updated for ASE 15.0, with 80 pages of new material. Available now!

A quick introduction to Sybase ASE

If you're new to Sybase ASE, here is some info about ASE to get you started:

The answer to the obvious question: "What is Sybase ASE ?"

Links to where you can download free versions of the ASE software as well as free copies of enterprise-grade Linux.

Some pointers where to find ASE documentation

Suggestions where to go for help if you're stuck with ASE

Tips & Tricks & Documentation

An overview of all issues related to identity gaps. These pages are a must-read for anyone using identity columns.

A trick to get the results from a stored procedure into a table -- based on CIS features.

Some demos of what you can do with CIS and XP in ASE; includes downloadable code examples of how to use sp_remotesql.
Includes a stored proc which shuts down the server and immediately restarts it automatically.

Examples of creating user-defined functions using Java in ASE 12.0 and 12.5 - includes downloadable demo code.

Simulating dynamic SQL (1) - some tricks that work in all ASE versions; includes downloadable demo code.

Simulating dynamic SQL (2) - use sp_remotesql to simulate dynamic SQL; includes downloadable demo code.

Granting SA-commands to non-SA users: it may not be in the books, but it is possible! This shows how to allow ordinary users to execute commands like kill, sp_sysmon and sp_showplan without giving them sa_role ... (includes downloadable code)

Tricks with sqsh (a great isql replacement)

Background Documentation

The article "Reading temporary tables that aren't yours" as published in the Q4 1998 issue of the ISUG Technical Journal - contains some useful background info on #temporary tables.

Everything you wanted to know about login triggers (a new feature in ASE 12.5)

The article "Avoiding Common ASE Performance Problems" as published in the Q2 2000 issue of the ISUG Technical Journal - read this before you start coding...

ASE Replicator: a new feature in ASE 12.5.0.1 which can be used to implement small replication systems.

My ISUG Tech Journal article "Little-known new features in ASE 12.5.0.1". The title says it all...

My ISUG Tech Journal article "Generating DDL Statements with 'ddlgen'".

Information about the "MDA tables" in 12.5.0.3+.

Sybase IQ

A quick introduction to Sybase IQ.

Getting a free version of Sybase IQ.

Some handy procs for IQ - including an IQ version of ASE's sp_autoformat.

Explains how to invoke IQ functionality, like OLAP functions, directly from ASE.

Details of setting up IQ-to-ASE connections with ODBC on Unix/Linux

Replication Server

RepServer Tips & Tricks - survival information for RepServer DBAs.

ASE Tools

sybinit4ever - an ASCII-only Unix tool for ASE 12.5/12.0/11.9/11.5 server creation; runs on all Unix platforms, including Linux.

A page with details about all dbccdb faults; handy for DBAs running ASE pre-12.5.0.3, where sp_dbcc_help_fault is not yet available. .

sp_exec_table, a handy stored procedure that lets you execute SQL based on the contents of a column in a table.

sp_license, a stored procedure showing exactly which licensable options you're running.

sp_recreate, a stored procedure to drop and recreate triggers in a single command, without re-entering the SQL source code.

SQL-to-XML tool for ASE - a simple tool to present data in an ASE table or view as XML; includes a downloadable stored procedure.

Some tips and tools for quickly repairing identity gaps using undocumented commands; a must-have for any DBA dealing with identity columns.

A handy tool to automatically check the errorlog of ASE or Replication Server for error messages , and mail the results to the DBA. It figures out the errorlog pathname itself.

The "New Generation ASE System Stored Procedures" - some very useful stored procs which every DBA should have...
Includes downloadable software, manual pages, a FAQ and the 1999 Q3 ISUG Tech Journal article describing the techniques used.

sp_blank_password : a stored procedure to set ASE login passwords back to blanks.

Some auditing-based tools , which may be handy for ASE migrations or performance troubleshooting.

Miscellaneous ASE/RepServer tools by various authors.

Miscellaneous Topics

A quiz about ASE (well, some tricky questions at least) to test your ASE knowledge - a new question is added every month.

Fun stuff about Sybase -- and some of its competitors .

Downloadable stuff you may find useful/interesting. .

Tips on becoming a Sybase Certified Professional - includes suggestions for practice tests.

Suggestions for interview questions when interviewing a candidate ASE DBA.

Some web links/resources about ASE which I'd recommend.

A list of some books about Sybase ASE (as well as about RepServer).

Query Filter - a Unix tool to find "Queries from Hell" before they hang up your server (for ASE 11.0.3; it hasn't been updated for later versions; it may still be inspiring, though...)

About this site

This web site is maintained by Rob Verschoor, a free-lance Sybase ASE consultant (check out my CV/Resume and my certifications) who also wrote most of the documentation and software on this site. Sypron B.V. (short for "Solving Your Problems Now") is Rob's company -- hence the name www.sypron.nl; check out some customers.

In 2001, Rob received the International Sybase User Group (ISUG) Outstanding Achievement Award -- details & picture are here.

Contact Rob Verschoor at rob@sypron.nl. If you're a recruiter, please read this first.

Support this site!

All information on this web site is freely available to the Sybase community. It is still possible to keep this web site free of ad banners and popups (except a few about this site itself), although the ever-increasing volume of traffic (or rather, the associated costs) may require that to change at some point.
If you find this web site useful, and you'd like it to continue and remain ad-free, you can support this web site in two ways:

Buy Rob's books (or convince someone else to do so ;-)

Link to www.sypron.nl from your web site, and recommend it to others.

Many thanks in advance for your support!

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

Site Map Shop Quiz Mailing List Feedback About this site Copyright/Legal

Copyright © 1998-2013 Rob Verschoor/Sypron B.V. "Solving Your Problems Now !"
All rights reserved. All trademarks are acknowledged.