The Complete Sybase ASE Quick Reference Guide - …

1 The Complete Sybase ASE Quick Reference Guide ASE versions , & 6th edition These pages are taken from "The Complete Sybase ASE Quick Reference Guide ", as a sample of the contents. Note that the origi-nal page size is 11 by 22 cm ( by inch). The Complete book can be ordered from . Rob Verschoor Sypron Publications 2 The Complete Sybase ASE Quick Reference Guide The Complete Sybase ASE Quick Reference Guide ASE versions , & 6th edition by Rob Verschoor ISBN 978-90-806117-1-9 Published by: Sypron Het Wolfseind 24 3823 VS Amersfoort The Netherlands Internet Email Printed in The Netherlands First edition : January 2001 (ASE & ) Second edition : March 2002 (ASE , & ) Third edition : July 2004 (ASE , , & ) Fourth edition : August 2006 (ASE , & ) Fifth edition : April 2009 (ASE , ) Sixth edition : November 2012 (ASE , & ESD#3) Sypron is a registered trademark of Sypron Sybase , Transact-SQL, Adaptive Server Enterprise and Replication Server are regis-tered trademarks of Sybase , Inc.

2 Other product or brand names may be (registered) trademarks of their respective owners. Copyright 2001-2012 Sypron All rights reserved. No part of this publication may be reproduced in any form, or by any means, without the prior written permission of the publisher. While this book has been prepared with care, neither the author, nor the publisher, nor SAP AG or Sybase Inc. (nor their subsidiaries), assume any responsibility for errors or omissions, nor do they accept any liabilities for damages resulting from the use of the information herein. ASE versions , & 3 Contents Introduction .. 5 About this Quick Reference Guide .. 5 ASE versions covered in this 5 How to use this Quick Reference Guide .. 6 Electronic version of code 7 Undocumented commands: warning & disclaimer.

3 7 Starting Points For Common DBA Tasks .. 8 Developer 10 1 Datatype conversion 11 2 Identifiers ..11 3 Quotes & Comments ..12 4 Operators ..12 5 Built-in Date & Time functions .. 13 Date/time formatting .. 14 String functions .. 14 System functions .. 17 Mathematical functions .. 22 Trigonometric functions .. 23 Login/user/role 23 Miscellaneous 24 6 Text & Image (LOB) data ..24 LOB Locators ( ) .. 25 7 Aggregate functions ..26 8 DML statements ..27 Select statement .. 27 29 Joins, ANSI Joins & Outer Joins .. 29 Statements to modify data .. 30 9 Table Partitioning .. 33 Identity columns .. 35 Encrypted Columns ( ) .. 36 10 11 Referential Integrity Constraints ..41 12 Object storage properties ..42 13 14 Materialized sets( ESD#2)..44 15 Rules & Defaults ..45 16 17 Stored 18 SQL Functions ( ) ..48 19 Programming & flow control.

4 48 20 Cursors ..50 Example of cursor 52 21 Transactions ..52 Example of transaction programming .. 53 22 Settings affecting ASE query Settings affecting query 54 Settings affecting query 55 Settings affecting query 55 Advanced settings affecting query plans .. 56 Settings for displaying query plan information .. 57 Miscellaneous 58 23 Statement cache ..59 24 Commonly used system stored 25 Java in ASE (SQLJ) ..61 26 XML (XPath/XQuery/SQLX) in ASE ..63 DBA 66 27 Database devices & dump devices ..66 Device 67 28 Creating & maintaining Temporary databases .. 70 70 Thresholds .. 71 29 Dumping & loading databases (backup & restore)..71 Quiesce, Mount, Unmount .. 74 4 The Complete Sybase ASE Quick Reference Guide 30 Data Compression ( ) ..75 31 Logins & Logins (server-level 'users') ..76 Login profiles ( ).

5 80 Database-level users ..80 Roles ..81 32 Permissions (grant/revoke) ..83 Classic permissions ..83 Granular permissions ( ESD#2)+ ..85 Row-level access control (access rules) ..85 33 Managing configuration parameters ..86 34 Memory Named caches & buffer pools ..87 35 CPUs/Cores, Engines & Threads ( )..88 Logical Process Manager ('Execution Classes') ..90 36 Update 37 38 39 Languages & 40 Character set & sort Unicode ..96 41 Miscellaneous DBA commands & config parameters ..96 42 DBA 43 MDA tables / monitoring 44 Setting up sybsystemprocs, pubs3 & 45 Setting up 'dbccdb' for 'dbcc checkstorage'..99 'dbccdb' stored procedures ..100 Advanced DBA 102 46 Advanced configuration 47 Configuring parallel query Parallel index 48 ASE Cluster Edition (CE) ..104 49 Supported DBCC 50 Unsupported DBCC commands ..108 51 ASE Traceflags ..113 52 Accessing data located outside Configuring remote access with RPCs.

6 115 Configuring remote access with CIS ..116 Component Integration Services (CIS) features ..116 53 Transaction isolation levels ..121 Lock 54 Resource limits ('Resource Governor')..122 55 XP Server ..123 56 57 Database 58 Abstract 59 Query metrics 60 Replication 61 Job 62 Shared memory Miscellaneous Topics .. 131 63 The interfaces 64 Server 65 Client 66 ASE environment 67 Running servers as Windows 68 Global Session-specific global variables ..139 Server-wide & non-static global variables ..141 Server-wide & static global variables ..142 69 Logical keys ..143 70 Catalog stored 71 Issues with 72 Minimally logged 73 Monitor Server & Historical Server ( )..144 74 ASE limits ..145 75 ASE license manager & license Index .. 148 8 The Complete Sybase ASE Quick Reference Guide Starting Points For Common DBA Tasks Below is a brief list of some common ASE DBA tasks.

7 See the index ( ) or Table of Contents ( ) if a topic is not listed here. On Windows, read instead of dataserver. Task Command or procedure / see page Find ASE product manuals (online searchable and PDF) Install ASE See the ASE installation Guide for your platform Download the ASE software, patches and upgrades Download ASE from ; download patches from ASE license options Generate license keys & put in license file ( ) Get a free version of ASE Developer/Express Ed., see Create a new ASE server srvbuild[res]; syconfig; sybatch; dataserver -z -b Start an ASE server startserver( ); start as Windows service( )Start ASE in standalone (single-user) mode Use the -m option when starting dataserver ( ) Stop an ASE server shutdown( ); or stop Windows service ( ) Find running ASE servers showserver ( ); or use the Windows service manager ( ) Connect to an ASE server isql utility ( ) Change database context use ( ), with the database name Using a GUI DBA admin tool Sybase Control Center or Sybase Central ( ) Find the ASE version @@version ( ) or dataserver -v ( ) Find the ASE version for SAP Business Suite @@sbssav ( ) or dataserver --sbssav ( )Find the ASE edition sp_lmconfig ( ) Find version of system procs sp_version( ) (=version of installmaster etc.)

8 Find the ASE server's IP address and port number In a running server, use sp_listener 'status' ( ); or look in the interfaces file ( ) Add a user to ASE create login or sp_addlogin ( ) to allow access to the ASE server; sp_adduser or sp_addalias( ) to allow access to an ASE database Change a login password sp_password or alter passwd ( ) List all databases in an ASE server sp_helpdb ( ), without parameters List all tables/objects in an ASE database sp_help ( ); or a direct query against the catalog: select name, type, crdate from sysobjects Display free/used space in an ASE database sp_spaceused ( ) without database name sp_helpdb ( ) with database name Find size of a table sp_spaceused ( ), with the table name Find schema and indexes for a table sp_help ( ), with the table name ddlgen utility ( ) Find SQL code for stored procs, views, triggers, etc.

9 Sp_showtext, sp_helptext ( ) defncopy utility ( ); ddlgen utility ( ) Copy data to/from table/file bcp utility ( ) Backup an ASE database dump database & dump transaction ( ) Restore an ASE database from backups load database & load transaction ( ), followed by online database Information about database devices sp_helpdevice ( ) Create an ASE database disk init ( ) and create database ( ) Enlarge a database device disk resize ( ) Enlarge an ASE database alter database ( ) Shrink an ASE database alter off ( ; transaction log only) Display ASE configuration parameters sp_configure for all parameters ( ) sp_configure 'nondefault' for non-default parameters Check database corruptions dbcc checkstorage ( ) Display all current users sp_who ( ) Kill a user connection kill ( ) Monitor ASE activity and resource usage sp_monitorconfig ( ); MDA tables ( ); sp_sysmon ( ) Look at ASE query plans set showplan ( ); set statistics plancost ( )sp_showplan ( ); show_plan() ( ); & others Gathering information for Sybase TechSupport sybdiag utility ( ) Find ASE limits 12 The Complete Sybase ASE Quick Reference Guide requiring set quoted_identifier on.

10 The quotes/brackets reduce the max. length by 2. Examples: set quoted_identifier on create table "This is my table!" ("My Column!" int, YourColumn int) select [name] from [sysobjects] where [type] = 'U' valid_name ( character_expression [, length ] ) Returns 0 if character_expression is not a valid identifier, or a number other than 0 if it is a valid identifier. With length ( , default=30), character_expression will be deemed an invalid identifier ( returns 0) if its length exceeds length. In , returns 128 when the config setting 'quoted identifier enhancements' = 1, set quoted identifier = on, and character_expression is a quoted identifier that is valid under these settings, but would be invalid otherwise. ( ) sp_configure 'quoted identifier enhancements', { 0 | 1 } (static) When 1 (default=0), certain aspects of quoted identifiers behave differently; see the ASE manuals for details.