Adaptive Server Enterprise 12.5
Release Bulletin ASE 12.5 on Digital UNIX
Accessing current release bulletin information
Component summary
Installation kit
Server components
PC-client components
Operating system updates
Special installation instructions
Known installation issues
Studio Installer fails to load on Pentium 4 machines
Input cursor not always visible in Studio Installer
Using Adaptive Server with transaction services
Special upgrade instructions
Migrating data between Adaptive Servers with different logical page sizes
Known upgrade issues
Upgrading Monitor Server
OAM counts mismatch on sysobjects after upgrade
Using diagserver after upgrade
Changed functionality in this version
Changes affecting applications after upgrade
Increased optimization time for queries with many join keys
ANSI outer join syntax recommended
Known problems
Rebuilding system databases on upgraded servers
Understanding sizes
Highlighted known LDAP-related problems
Enabling lightweight directory services
Sybase Central does not support directory services
langinstall and charset utilities do not support LDAP
Highlighted known RPC-related problems
RPC compatibility with older versions of Adaptive Server
Channel setup failure message when issuing RPCs
Highlighted known SSL problems
Component Integration Services connection fails using SSL
Network memory requirements for using SSL for incoming connections
Lack of memory causes ssl_handshake failures
SSL listener port hangs
Highlighted known Sybase Central problems
Error when viewing table properties in Sybase Central
Highlighted known alter table problems
Sporadic error message from alter table on DOL tables
alter table and sysstatistics flaw
alter table and triggers with if update() clauses
Highlighted known select problems
select statements that include order by clauses
Truncation error using select with power() function and numeric argument
create schema causes lock contention
Emulated instructions warning on DEC
unichar does not work across platforms
sortkey() truncates result sets from wide columns
Restrictions on login trigger
Dynamic and host variables not allowed in DDL statements
Product compatibilities
Documentation updates and clarifications
Full Text Search Specialty Data Store User's Guide
Hardware and software requirements
Upgrading from Adaptive Server version 12.0
New functionality in this version
Known problems
Product compatibility
Performance and Tuning Guide
Performance issues with using larger logical pages
Bulk copy utility
Union and view operators
Prefetch size and replacement strategy
Quick Reference Guide
Range of values for smallint and int
Reference Manual Volume 1: Building Blocks
Range of values for smallint
Size of expressions and global variables
data_pgs command includes dbid parameter
Varchars, cursors, and trailing spaces
Reference Manual Volume 2: Commands
Minimum size that alter database extends a database
prefetch_size values
Using alter table in if...else command
Cursor scope description in declare cursor command incomplete
Use of readpast may cause duplicates
revoke command with cascade option
Comments in select and select into regarding nullability are inaccurate
truncate table command
Reference Manual Volume 3: Procedures
sp_audit and SSL
sp_audit incorrectly states that cmdtext works for roles
sp_estspace
Additional parameter for sp_unbindrule
sp_modifystats
Obtaining status information for commands invoked by xp_cmdshell
Reference Manual Volume 4: Tables
Datatype information for columns
Tracking create dates for columns
System Administration Guide
Configuration parameters for user-defined login security
dbcc checkstorage
Data structures used for storing text and image data
Heap memory
Unicode sort orders
sp_help and sp_depend display access rule information
Setting limits for tempdb space usage
Unicode sort orders
Transact-SQL User’s Guide
System test can fail if too many aggregates in compute clause
DDL commands allowed in transactions
Utility Guide
bcp out and partitioned tables
Using bcp to transfer data to and from Adaptive Server
bcp and row-level access rules
What’s New in Sybase Adaptive Server Enterprise?
Changed Transact-SQL commands
Changed system procedures
Changed status in existing columns
Technical support
Other sources of information
Sybase certifications on the Web
Release Bulletin ASE 12.5 on HP-UX
Accessing current release bulletin information
Component summary
Installation kit
Server components
PC-client components
Operating system updates
Special installation instructions
Known installation issues
Studio Installer fails to load on Pentium 4 machines
Running EJB Server on HP 32-bit machines
Input cursor not always visible in Studio Installer
Using Adaptive Server with transaction services
Special upgrade instructions
Migrating data between Adaptive Servers with different logical page sizes
Known upgrade issues
Upgrading Monitor Server
OAM counts mismatch on sysobjects after upgrade
Changed functionality in this version
Changes affecting applications after upgrade
Increased optimization time for queries with many join keys
ANSI outer join syntax recommended
Known problems
Rebuilding system databases on upgraded servers
Understanding sizes
Highlighted known LDAP-related problems
Enabling lightweight directory services
Sybase Central does not support directory services
langinstall and charset utilities do not support LDAP
Highlighted known RPC-related problems
RPC compatibility with older versions of Adaptive Server
Channel setup failure message when issuing RPCs
Highlighted known SSL problems
Network memory requirements for using SSL for incoming connections
Lack of memory causes ssl_handshake failures
SSL listener port hangs
Highlighted known Sybase Central problems
Error when viewing table properties in Sybase Central
Highlighted known alter table problems
Sporadic error message from alter table on DOL tables
alter table and sysstatistics flaw
alter table and triggers with if update() clauses
Highlighted known select problems
select statements that include order by clauses
Truncation error using select with power() function and numeric argument
create schema causes lock contention
Error message after running truncate table command
unichar does not work across platforms
sortkey() truncates result sets from wide columns
Restrictions on login trigger
Dynamic and host variables not allowed in DDL statements
Product compatibilities
Documentation updates and clarifications
Full Text Search Specialty Data Store User's Guide
Hardware and software requirements
Upgrading from Adaptive Server version 12.0
New functionality in this version
Known problems
Product compatibility
Performance and Tuning Guide
Performance issues with using larger logical pages
Bulk copy utility
Union and view operators
Prefetch size and replacement strategy
Quick Reference Guide
Range of values for smallint and int
Reference Manual Volume 1: Building Blocks
Range of values for smallint
Size of expressions and global variables
data_pgs command includes dbid parameter
Varchars, cursors, and trailing spaces
Reference Manual Volume 2: Commands
Minimum size that alter database extends a database
prefetch_size values
Using alter table in if...else command
Cursor scope description in declare cursor command incomplete
Use of readpast may cause duplicates
revoke command with cascade option
Comments in select and select into regarding nullability are inaccurate
truncate table command
Reference Manual Volume 3: Procedures
sp_audit and SSL
sp_audit incorrectly states that cmdtext works for roles
sp_estspace
Additional parameter for sp_unbindrule
sp_modifystats
Obtaining status information for commands invoked by xp_cmdshell
Reference Manual Volume 4: Tables
Datatype information for columns
Tracking create dates for columns
System Administration Guide
Configuration parameters for user-defined login security
dbcc checkstorage
Data structures used for storing text and image data
Heap memory
Unicode sort orders
sp_help and sp_depend display access rule information
Setting limits for tempdb space usage
Unicode sort orders
Transact-SQL User’s Guide
System test can fail if too many aggregates in compute clause
DDL commands allowed in transactions
Utility Guide
bcp out and partitioned tables
Using bcp to transfer data to and from Adaptive Server
bcp and row-level access rules
What’s New in Sybase Adaptive Server Enterprise?
Changed Transact-SQL commands
Changed system procedures
Changed status in existing columns
Technical support
Other sources of information
Sybase certifications on the Web
Release Bulletin ASE 12.5 on IBM AIX
Accessing current release bulletin information
Component summary
Installation kit
Server components
PC-client components
Operating system updates
Special installation instructions
Known installation issues
Studio Installer fails to load on Pentium 4 machines
Input cursor not always visible in Studio Installer
Using Adaptive Server with transaction services
Special upgrade instructions
Migrating data between Adaptive Servers with different logical page sizes
Known upgrade issues
Upgrading Monitor Server
OAM counts mismatch on sysobjects after upgrade
Changed functionality in this version
Changes affecting applications after upgrade
Increased optimization time for queries with many join keys
ANSI outer join syntax recommended
Known problems
Rebuilding system databases on upgraded servers
Understanding sizes
Highlighted known LDAP-related problems
Enabling lightweight directory services
Sybase Central does not support directory services
langinstall and charset utilities do not support LDAP
Highlighted known RPC-related problems
RPC compatibility with older versions of Adaptive Server
Channel setup failure message when issuing RPCs
Highlighted known SSL problems
Re-entrant libraries required for SSL
Network memory requirements for using SSL for incoming connections
Lack of memory causes ssl_handshake failures
SSL listener port hangs
Highlighted known Sybase Central problems
Error when viewing table properties in Sybase Central
Highlighted known alter table problems
Sporadic error message from alter table on DOL tables
alter table and sysstatistics flaw
alter table and triggers with if update() clauses
Highlighted known select problems
select statements that include order by clauses
Truncation error using select with power() function and numeric argument
create schema causes lock contention
unichar does not work across platforms
sortkey() truncates result sets from wide columns
Restrictions on login trigger
Monitor Server counters increment improperly
Dynamic and host variables not allowed in DDL statements
Product compatibilities
Documentation updates and clarifications
Full Text Search Specialty Data Store User's Guide
Hardware and software requirements
Upgrading from Adaptive Server version 12.0
New functionality in this version
Known problems
Product compatibility
Performance and Tuning Guide
Performance issues with using larger logical pages
Bulk copy utility
Union and view operators
Prefetch size and replacement strategy
Quick Reference Guide
Range of values for smallint and int
Reference Manual Volume 1: Building Blocks
Range of values for smallint
Size of expressions and global variables
data_pgs command includes dbid parameter
Varchars, cursors, and trailing spaces
Reference Manual Volume 2: Commands
Minimum size that alter database extends a database
prefetch_size values
Using alter table in if...else command
Cursor scope description in declare cursor command incomplete
Use of readpast may cause duplicates
revoke command with cascade option
Comments in select and select into regarding nullability are inaccurate
truncate table command
Reference Manual Volume 3: Procedures
sp_audit and SSL
sp_audit incorrectly states that cmdtext works for roles
sp_estspace
Additional parameter for sp_unbindrule
sp_modifystats
Obtaining status information for commands invoked by xp_cmdshell
Reference Manual Volume 4: Tables
Datatype information for columns
Tracking create dates for columns
System Administration Guide
Configuration parameters for user-defined login security
dbcc checkstorage
Data structures used for storing text and image data
Heap memory
Unicode sort orders
sp_help and sp_depend display access rule information
Setting limits for tempdb space usage
Unicode sort orders
Transact-SQL User’s Guide
System test can fail if too many aggregates in compute clause
DDL commands allowed in transactions
Utility Guide
bcp out and partitioned tables
Using bcp to transfer data to and from Adaptive Server
bcp and row-level access rules
What’s New in Sybase Adaptive Server Enterprise?
Changed Transact-SQL commands
Changed system procedures
Changed status in existing columns
Technical support
Other sources of information
Sybase certifications on the Web
Release Bulletin ASE 12.5 on Linux
Accessing current release bulletin information
Component summary
Installation kit
Server components
PC-client components
Operating system updates
Special installation instructions
Known installation issues
Studio Installer fails to load on Pentium 4 machines
Input cursor not always visible in Studio Installer
/etc/rc.d/init.d/sybase incorrectly defines $SYBASE variables
Using Adaptive Server with transaction services
Special upgrade instructions
Migrating data between Adaptive Servers with different logical page sizes
Known upgrade issues
Upgrading Monitor Server
OAM counts mismatch on sysobjects after upgrade
Using diagserver after upgrade
Changed functionality in this version
Changes affecting applications after upgrade
Increased optimization time for queries with many join keys
ANSI outer join syntax recommended
Known problems
Rebuilding system databases on upgraded servers
Understanding sizes
Highlighted known RPC-related problems
RPC compatibility with older versions of Adaptive Server
Channel setup failure message when issuing RPCs
Highlighted known SSL problems
Network memory requirements for using SSL for incoming connections
Lack of memory causes ssl_handshake failures
Harmless error messages when starting Linux with SSL enabled
SSL listener port hangs
Highlighted known Sybase Central problems
Unnecessary characters appear when executing Sybase Central
Error when viewing table properties in Sybase Central
Highlighted known alter table problems
Sporadic error message from alter table on DOL tables
alter table and sysstatistics flaw
alter table and triggers with if update() clauses
Highlighted known select problems
select statements that include order by clauses
Truncation error using select with power() function and numeric argument
Turning off the O_SYNC flag
unichar does not work across platforms
sortkey() truncates result sets from wide columns
Restrictions on login trigger
Engine not exiting after ueshutdown()
Dynamic and host variables not allowed in DDL statements
Product compatibilities
Documentation updates and clarifications
Full Text Search Specialty Data Store User's Guide
Hardware and software requirements
Upgrading from Adaptive Server version 11.9.2
New functionality in this version
Known problems
Product compatibility
Performance and Tuning Guide
Performance issues with using larger logical pages
Bulk copy utility
Union and view operators
Prefetch size and replacement strategy
Quick Reference Guide
Range of values for smallint and int
Reference Manual Volume 1: Building Blocks
Range of values for smallint
Size of expressions and global variables
data_pgs command includes dbid parameter
Varchars, cursors, and trailing spaces
Reference Manual Volume 2: Commands
Minimum size that alter database extends a database
prefetch_size values
Using alter table in if...else command
Cursor scope description in declare cursor command incomplete
Use of readpast may cause duplicates
revoke command with cascade option
Comments in select and select into regarding nullability are inaccurate
truncate table command
Reference Manual Volume 3: Procedures
sp_audit and SSL
sp_audit incorrectly states that cmdtext works for roles
sp_estspace
Additional parameter for sp_unbindrule
sp_modifystats
Obtaining status information for commands invoked by xp_cmdshell
Reference Manual Volume 4: Tables
Datatype information for columns
Tracking create dates for columns
System Administration Guide
Configuration parameters for user-defined login security
dbcc checkstorage
Data structures used for storing text and image data
Heap memory
Unicode sort orders
sp_help and sp_depend display access rule information
Setting limits for tempdb space usage
Unicode sort orders
Transact-SQL User’s Guide
System test can fail if too many aggregates in compute clause
DDL commands allowed in transactions
Utility Guide
bcp out and partitioned tables
Using bcp to transfer data to and from Adaptive Server
bcp and row-level access rules
What’s New in Sybase Adaptive Server Enterprise?
Changed Transact-SQL commands
Changed system procedures
Changed status in existing columns
Technical support
Other sources of information
Sybase certifications on the Web
Release Bulletin ASE 12.5 on Silicon Graphics IRIX
Accessing current release bulletin information
Component summary
Installation kit
Server components
PC-client components
Operating system updates
Special installation instructions
Known installation issues
Input cursor not always visible in Studio Installer
Using Adaptive Server with transaction services
Compatibility with other products
Special upgrade instructions
Migrating data between Adaptive Servers with different logical page sizes
Known upgrade issues
Upgrading Monitor Server
OAM counts mismatch on sysobjects after upgrade
Changed functionality in this version
Changes affecting applications after upgrade
Increased optimization time for queries with many join keys
ANSI outer join syntax recommended
Known problems
Rebuilding system databases on upgraded servers
Understanding sizes
Highlighted known LDAP-related problems
Enabling lightweight directory services
Sybase Central does not support directory services
langinstall and charset utilities do not support LDAP
dsedit does not support LDAP
Highlighted known RPC-related problems
RPC compatibility with older versions of Adaptive Server
Channel setup failure message when issuing RPCs
Backup Server hangs when issued an RCP to dump a database
Highlighted known Sybase Central problems
Error when viewing table properties in Sybase Central
Highlighted known alter table problems
Sporadic error message from alter table on DOL tables
alter table and sysstatistics flaw
alter table and triggers with if update() clauses
Highlighted known select problems
select statements that include order by clauses
Truncation error using select with power() function and numeric argument
create schema causes lock contention
unichar does not work across platforms
sortkey() truncates result sets from wide columns
Restrictions on login trigger
Dynamic and host variables not allowed in DDL statements
shutdown no wait causes a stacktrace
uninstall does not work with Studio Installer
Product compatibilities
Documentation updates and clarifications
Performance and Tuning Guide
Performance issues with using larger logical pages
Bulk copy utility
Union and view operators
Prefetch size and replacement strategy
Reference Manual Volume 1: Building Blocks
Size of expressions and global variables
data_pgs command includes dbid parameter
Varchars, cursors, and trailing spaces
Reference Manual Volume 2: Commands
Minimum size that alter database extends a database
prefetch_size values
Using alter table in if...else command
Cursor scope description in declare cursor command incomplete
Use of readpast may cause duplicates
revoke command with cascade option
Comments in select and select into regarding nullability are inaccurate
truncate table command
Reference Manual Volume 3: Procedures
sp_audit incorrectly states that cmdtext works for roles
sp_estspace
Additional parameter for sp_unbindrule
sp_modifystats
Obtaining status information for commands invoked by xp_cmdshell
Reference Manual Volume 4: Tables
Datatype information for columns
Tracking create dates for columns
System Administration Guide
Configuration parameters for user-defined login security
dbcc checkstorage
Data structures used for storing text and image data
Heap memory
Unicode sort orders
sp_help and sp_depend display access rule information
Setting limits for tempdb space usage
Unicode sort orders
Transact-SQL User’s Guide
System test can fail if too many aggregates in compute clause
DDL commands allowed in transactions
Utility Guide
bcp out and partitioned tables
Using bcp to transfer data to and from Adaptive Server
bcp and row-level access rules
What’s New in Sybase Adaptive Server Enterprise?
Changed Transact-SQL commands
Changed system procedures
Changed status in existing columns
Technical support
Other sources of information
Sybase certifications on the Web
Release Bulletin ASE 12.5 on Sun Solaris
Accessing current release bulletin information
Component summary
Installation kit
Server components
PC-client components
Operating system updates
Special installation instructions
Known installation issues
Studio Installer fails to load on Pentium 4 machines
Input cursor not always visible in Studio Installer
Using Adaptive Server with transaction services
Special upgrade instructions
Migrating data between Adaptive Servers with different logical page sizes
Known upgrade issues
Upgrading Monitor Server
OAM counts mismatch on sysobjects after upgrade
Changed functionality in this version
Changes affecting applications after upgrade
Increased optimization time for queries with many join keys
ANSI outer join syntax recommended
Known problems
Rebuilding system databases on upgraded servers
Understanding sizes
Highlighted known LDAP-related problems
Enabling lightweight directory services
Sybase Central does not support directory services
langinstall and charset utilities do not support LDAP
Highlighted known RPC-related problems
RPC compatibility with older versions of Adaptive Server
Channel setup failure message when issuing RPCs
Highlighted known SSL problems
Component Integration Services connection fails using SSL
Network memory requirements for using SSL for incoming connections
Lack of memory causes ssl_handshake failures
SSL listener port hangs
Highlighted known Sybase Central problems
Error when viewing table properties in Sybase Central
Highlighted known alter table problems
Sporadic error message from alter table on DOL tables
alter table and sysstatistics flaw
alter table and triggers with if update() clauses
Highlighted known select problems
select statements that include order by clauses
Truncation error using select with power() function and numeric argument
create schema causes lock contention
unichar does not work across platforms
sortkey() truncates result sets from wide columns
Restrictions on login trigger
Asynchronous large I/O on UNIX file systems
Dynamic and host variables not allowed in DDL statements
Product compatibilities
Documentation updates and clarifications
Full Text Search Specialty Data Store User's Guide
Hardware and software requirements
Upgrading from Adaptive Server version 12.0
New functionality in this version
Known problems
Product compatibility
Performance and Tuning Guide
Performance issues with using larger logical pages
Bulk copy utility
Union and view operators
Prefetch size and replacement strategy
Quick Reference Guide
Range of values for smallint and int
Reference Manual Volume 1: Building Blocks
Range of values for smallint
Size of expressions and global variables
data_pgs command includes dbid parameter
Varchars, cursors, and trailing spaces
Reference Manual Volume 2: Commands
Minimum size that alter database extends a database
prefetch_size values
Using alter table in if...else command
Cursor scope description in declare cursor command incomplete
Use of readpast may cause duplicates
revoke command with cascade option
Comments in select and select into regarding nullability are inaccurate
truncate table command
Reference Manual Volume 3: Procedures
sp_audit and SSL
sp_audit incorrectly states that cmdtext works for roles
sp_estspace
Additional parameter for sp_unbindrule
sp_modifystats
Obtaining status information for commands invoked by xp_cmdshell
Reference Manual Volume 4: Tables
Datatype information for columns
Tracking create dates for columns
System Administration Guide
Configuration parameters for user-defined login security
dbcc checkstorage
Data structures used for storing text and image data
Heap memory
Unicode sort orders
sp_help and sp_depend display access rule information
Setting limits for tempdb space usage
Unicode sort orders
Transact-SQL User’s Guide
System test can fail if too many aggregates in compute clause
DDL commands allowed in transactions
Utility Guide
bcp out and partitioned tables
Using bcp to transfer data to and from Adaptive Server
bcp and row-level access rules
What’s New in Sybase Adaptive Server Enterprise?
Changed Transact-SQL commands
Changed system procedures
Changed status in existing columns
Technical support
Other sources of information
Sybase certifications on the Web
Release Bulletin ASE 12.5 on Windows
Accessing current release bulletin information
Component summary
Installation kit
Server components
PC-client components
Operating system updates
Special installation instructions
Known installation issues
Studio Installer fails to load on Pentium 4 machines
Input cursor not always visible in Studio Installer
Cannot use Services to start and stop server on mapped network drive
Using Adaptive Server with MTS and DTC
Special upgrade instructions
Migrating data between Adaptive Servers with different logical page sizes
Known upgrade issues
Upgrade can cause system path to exceed maximum limit
OAM counts mismatch on sysobjects after upgrade
Using diagserver after upgrade
Changed functionality in this version
Changes affecting applications after upgrade
Increased optimization time for queries with many join keys
ANSI outer join syntax recommended
Known problems
Rebuilding system databases on upgraded servers
Understanding sizes
Highlighted known LDAP-related problems
Enabling lightweight directory services
Sybase Central does not support directory services
langinstall and charset utilities do not support LDAP
Highlighted known RPC-related problems
RPC compatibility with older versions of Adaptive Server
Channel setup failure message when issuing RPCs
Highlighted known SSL problems
Component Integration Services connection fails using SSL
Network memory requirements for using SSL for incoming connections
Lack of memory causes ssl_handshake failures
SSL listener port hangs
Highlighted known Sybase Central problems
Error when viewing table properties in Sybase Central
Highlighted known alter table problems
Sporadic error message from alter table on DOL tables
alter table and sysstatistics flaw
alter table and triggers with if update() clauses
Highlighted known select problems
select statements that include order by clauses
Truncation error using select with power() function and numeric argument
create schema causes lock contention
Problems using named pipes in the sql.ini file
unichar does not work across platforms
sortkey() truncates result sets from wide columns
Restrictions on login trigger
Dynamic configuration in Adaptive Server and PerfMon
Dynamic and host variables not allowed in DDL statements
Product compatibilities
Documentation updates and clarifications
Full Text Search Specialty Data Store User's Guide
Hardware and software requirements
Upgrading from Adaptive Server version 12.0
New functionality in this version
Known problems
Product compatibility
Performance and Tuning Guide
Performance issues with using larger logical pages
Bulk copy utility
Union and view operators
Prefetch size and replacement strategy
Quick Reference Guide
Range of values for smallint and int
Reference Manual Volume 1: Building Blocks
Range of values for smallint
Size of expressions and global variables
data_pgs command includes dbid parameter
Varchars, cursors, and trailing spaces
Reference Manual Volume 2: Commands
Minimum size that alter database extends a database
prefetch_size values
Using alter table in if...else command
Cursor scope description in declare cursor command incomplete
Use of readpast may cause duplicates
revoke command with cascade option
Comments in select and select into regarding nullability are inaccurate
truncate table command
Reference Manual Volume 3: Procedures
sp_audit and SSL
sp_audit incorrectly states that cmdtext works for roles
sp_estspace
Additional parameter for sp_unbindrule
sp_modifystats
Obtaining status information for commands invoked by xp_cmdshell
Reference Manual Volume 4: Tables
Datatype information for columns
Tracking create dates for columns
System Administration Guide
Configuration parameters for user-defined login security
dbcc checkstorage
Data structures used for storing text and image data
Heap memory
Unicode sort orders
sp_help and sp_depend display access rule information
Setting limits for tempdb space usage
Unicode sort orders
Transact-SQL User’s Guide
System test can fail if too many aggregates in compute clause
DDL commands allowed in transactions
Utility Guide
bcp out and partitioned tables
Using bcp to transfer data to and from Adaptive Server
bcp and row-level access rules
What’s New in Sybase Adaptive Server Enterprise?
Changed Transact-SQL commands
Changed system procedures
Changed status in existing columns
Technical support
Other sources of information
Sybase certifications on the Web
Installation Guide for ASE 12.5 on Digital UNIX
About this book
Overview
User roles
Product descriptions
Installation and setup utilities
PC-client product descriptions
Sybase installation directory
Installation Requirements
System requirements
Product disk space requirements
Language module sizes
Adaptive Server specifications
Adaptive Server devices and system databases
master device
sybsystemdb device and database
sysprocsdev device
Optional devices and databases
sybsecurity device and database
Sample databases
sybsyntax database
dbccdb database
dsync option on by default for database device files
Determining the location, type, and size of a database device
Sybase Software Asset Management (SySAM)
SySAM overview
Adaptive Server features licensed through SySAM
How SySAM works
Adaptive Server check-out procedure
Starting Adaptive Server with optional features
Types of SySAM systems
SySAM in the network environment
Redundant servers
SySAM administration
Verify the software is running
Starting the software manually
Adding feature licenses
Installing Sybase Servers
Overview
Installation definitions
Pre-installation tasks
Installing server components
Installation methods
Installing components with Studio Installer
Configuring the servers
Configuring EJB Server
Viewing the installation status output screen
Post-Installation Tasks
Environment variables
Setting environment variables
Verifying that servers are running
Verifying that you can connect to servers
Connecting to Adaptive Server via Sybase Central
Setting the System Administrator password
Installing sample databases
Default devices for sample databases
interpubs database
jpubs database
Maintaining the sample databases
jConnect 4.5, 5.5, and Java utilities
Using open database connectivity
Installing stored procedures
Installing character sets
Installing online help for Transact-SQL syntax
Online syntax help: sp_syntax
Default device for the sybsyntax database
Installing sybsyntax
Configuring XP Server after initial installation
Installing Sybase PC-Client Products
Overview
Installing PC-client products
Installing the Java Runtime Environment
Installing the Java Development Kit
Installing InfoMaker
Installing PowerDynamo
Configuring network connections for client products
Adding a server entry to the libtcl.cfg file
Adding a server to the sql.ini file
Enabling TCP connections
Testing the Sybase Central installation
Starting and Stopping Servers
Overview
Starting servers
Using the Runserver file
Using the startserver command
Using the monserver command
Stopping servers
Stopping Adaptive Server
Stopping Backup Server
Stopping Monitor Server
Using the kill command
Shutdown and shared memory files
Setting server start-up parameters
Starting servers when the operating system restarts
Upgrading Sybase Servers
Overview of the upgrade process
System catalog changes during upgrade
Catalog changes that might affect existing applications
Pre-upgrade tasks
Check system and upgrade requirements
Check RUN_server file location
Test current applications and stored procedures
Procedure text is required for upgrade
Reserved words
Running a reserved word check
Addressing reserved words conflicts
Using quoted identifiers
Verify that users are logged off
Check database integrity
Back up databases
Dump transaction logs
Prepare the database and devices for the upgrade
Create a sybsystemdb database
sybsystemprocs
Turn off database options
Disable auditing
Disable disk mirroring
Preparing to upgrade servers with replicated databases
Suspending transaction processing and replication activities
Draining the transaction logs for primary databases
Draining the RSSD Transaction Log
Disabling the secondary truncation point
Upgrading to Adaptive Server 12.5
Using sqlupgrade
Using sqlupgraderes
Resource file attributes for upgrading Adaptive Server
Upgrading using sqlupgraderes
Post-upgrade tasks
Verify that servers are running
Restore functionality in Adaptive Server
Reenable Replication Server
Remove old log records
Reenable replication
Restoring replication after upgrade
Reenable auditing
Upgrading Backup Server, Monitor Server, and XP Server
Upgrading compiled objects with dbcc upgrade_object
Finding compiled object errors before production
Reserved word errors
Missing, truncated, or corrupted source text
Quoted identifier errors
Temporary table references
select * potential problem areas
Using dbcc upgrade_object
Increasing the log segment size
Error reporting
Using database dumps in upgrades
Upgrading using dump and load
Upgrading compiled objects in database dumps
Determining whether a compiled object has been upgraded
Migrating from 32-bit to 64-bit versions
Recovering from a failed upgrade
Removing Sybase Servers
Uninstalling Sybase Servers
Removing an existing Adaptive Server
Troubleshooting
Overview
Error log locations
For installation utilities
For Sybase servers
Solutions to common installation problems
Unable to use X-Windows
Fatal Error: Cannot Map libct.so
Cannot eject the CD from the drive
DISPLAY environment variable not set correctly
Client not authorized to connect to server
No such device; operation not supported
Address already in use
Adaptive Server failed to start
Cannot start XP Server
Stopping Adaptive Server after a failure
Recovering from a failed installation
If installation fails after files are created
Troubleshooting resource file installations
Recovering from a failed upgrade
Restoring from backup
Rerunning the upgrade
Recording the upgrade manually
If the cause of the failure is known
If the cause of the failure is unknown
Troubleshooting SySAM
Authorization code input error
Alternative Installation Methods
Resource file installation
Installing from a resource file
Editing a sample resource file
Editing a resource file created by srvbuild
Resource file attributes for Adaptive Server
Resource file attributes for Backup Server
Using srvbuildres
Installing files in NOGUI mode
Completing the installation process
Installing components in cmdfile
Installation Guide for HP-UX
About this book
Overview
User roles
Product descriptions
Installation and setup utilities
PC-client product descriptions
Sybase installation directory
Installation Requirements
System requirements
Product disk space requirements
Language module sizes
Adaptive Server specifications
Adaptive Server devices and system databases
master device
sybsystemdb device and database
sysprocsdev device
Optional devices and databases
sybsecurity device and database
Sample databases
sybsyntax database
dbccdb database
dsync option on by default for database device files
Determining the location, type, and size of a database device
Sybase Software Asset Management (SySAM)
SySAM overview
Adaptive Server features licensed through SySAM
How SySAM works
Adaptive Server check-out procedure
Starting Adaptive Server with optional features
Types of SySAM systems
SySAM in the network environment
Redundant servers
SySAM administration
Verify the software is running
Starting the software manually
Adding feature licenses
Installing Sybase Servers
Overview
Installation definitions
Pre-installation tasks
Installing server components
Installation methods
Installing components with Studio Installer
Configuring the servers
Viewing the installation status output screen
Post-Installation Tasks
Environment variables
Setting environment variables
Setting EJB server environment
Verifying that servers are running
Verifying that you can connect to servers
Connecting to Adaptive Server via Sybase Central
Setting the System Administrator password
Installing sample databases
Default devices for sample databases
interpubs database
jpubs database
Maintaining the sample databases
jConnect 4.5, 5.5, and Java utilities
Using open database connectivity
Installing stored procedures
Installing character sets
Installing online help for Transact-SQL syntax
Online syntax help: sp_syntax
Default device for the sybsyntax database
Installing sybsyntax
Configuring XP Server after initial installation
Installing Sybase PC-Client Products
Overview
Installing PC-client products
Installing the Java Runtime Environment
Installing the Java Development Kit
Installing InfoMaker
Installing PowerDynamo
Configuring network connections for client products
Adding a server entry to the libtcl.cfg file
Adding a server to the sql.ini file
Enabling TCP connections
Testing the Sybase Central installation
Starting and Stopping Servers
Overview
Starting servers
Using the Runserver file
Using the startserver command
Using the monserver command
Stopping servers
Stopping Adaptive Server
Stopping Backup Server
Stopping Monitor Server
Using the kill command
Shutdown and shared memory files
Setting server start-up parameters
Starting servers when the operating system restarts
Upgrading Sybase Servers
Overview of the upgrade process
System catalog changes during upgrade
Catalog changes that might affect existing applications
Pre-upgrade tasks
Check system and upgrade requirements
Check RUN_server file location
Test current applications and stored procedures
Procedure text is required for upgrade
Reserved words
Running a reserved word check
Addressing reserved words conflicts
Using quoted identifiers
Verify that users are logged off
Check database integrity
Back up databases
Dump transaction logs
Prepare the database and devices for the upgrade
Create a sybsystemdb database
sybsystemprocs
Turn off database options
Disable auditing
Disable disk mirroring
Preparing to upgrade servers with replicated databases
Suspending transaction processing and replication activities
Draining the transaction logs for primary databases
Draining the RSSD Transaction Log
Disabling the secondary truncation point
Upgrading to Adaptive Server 12.5
Using sqlupgrade
Using sqlupgraderes
Resource file attributes for upgrading Adaptive Server
Upgrading using sqlupgraderes
Post-upgrade tasks
Verify that servers are running
Restore functionality in Adaptive Server
Reenable Replication Server
Remove old log records
Reenable replication
Restoring replication after upgrade
Reenable auditing
Upgrading Backup Server, Monitor Server, and XP Server
Upgrading compiled objects with dbcc upgrade_object
Finding compiled object errors before production
Reserved word errors
Missing, truncated, or corrupted source text
Quoted identifier errors
Temporary table references
select * potential problem areas
Using dbcc upgrade_object
Increasing the log segment size
Error reporting
Using database dumps in upgrades
Upgrading using dump and load
Upgrading compiled objects in database dumps
Determining whether a compiled object has been upgraded
Migrating from 32-bit to 64-bit versions
Recovering from a failed upgrade
Removing Sybase Servers
Uninstalling Sybase Servers
Removing an existing Adaptive Server
Troubleshooting
Overview
Error log locations
For installation utilities
For Sybase servers
Solutions to common installation problems
Unable to use X-Windows
Fatal Error: Cannot Map libct.so
Cannot eject the CD from the drive
DISPLAY environment variable not set correctly
Client not authorized to connect to server
Address already in use
Adaptive Server failed to start
Cannot start XP Server
Stopping Adaptive Server after a failure
Recovering from a failed installation
If installation fails after files are created
Troubleshooting resource file installations
Recovering from a failed upgrade
Restoring from backup
Rerunning the upgrade
Recording the upgrade manually
If the cause of the failure is known
If the cause of the failure is unknown
Troubleshooting SySAM
Authorization code input error
Alternative Installation Methods
Resource file installation
Installing from a resource file
Editing a sample resource file
Editing a resource file created by srvbuild
Resource file attributes for Adaptive Server
Resource file attributes for Backup Server
Using srvbuildres
Installing files in NOGUI mode
Completing the installation process
Installing components in cmdfile
Installation Guide for IBM AIX
About this book
Overview
User roles
Product descriptions
Installation and setup utilities
PC-client product descriptions
Sybase installation directory
Installation Requirements
System requirements
Product disk space requirements
Language module sizes
Adaptive Server specifications
Adaptive Server devices and system databases
master device
sybsystemdb device and database
sysprocsdev device
Optional devices and databases
sybsecurity device and database
Sample databases
sybsyntax database
dbccdb database
dsync option on by default for database device files
Determining the location, type, and size of a database device
Sybase Software Asset Management (SySAM)
SySAM overview
Adaptive Server features licensed through SySAM
How SySAM works
Adaptive Server check-out procedure
Starting Adaptive Server with optional features
Types of SySAM systems
SySAM in the network environment
Redundant servers
SySAM administration
Verify the software is running
Starting the software manually
Adding feature licenses
Installing Sybase Servers
Overview
Installation definitions
Pre-installation tasks
Installing server components
Installation methods
Installing components with Studio Installer
Configuring the servers
Configuring EJB Server
Viewing the installation status output screen
Post-Installation Tasks
Environment variables
Setting environment variables
Verifying that servers are running
Verifying that you can connect to servers
Connecting to Adaptive Server via Sybase Central
Setting the System Administrator password
Installing sample databases
Default devices for sample databases
interpubs database
jpubs database
Maintaining the sample databases
jConnect 4.5, 5.5, and Java utilities
Using open database connectivity
Installing stored procedures
Installing character sets
Installing online help for Transact-SQL syntax
Online syntax help: sp_syntax
Default device for the sybsyntax database
Installing sybsyntax
Configuring XP Server after initial installation
Installing Sybase PC-Client Products
Overview
Installing PC-client products
Installing the Java Runtime Environment
Installing the Java Development Kit
Installing InfoMaker
Installing PowerDynamo
Configuring network connections for client products
Adding a server entry to the libtcl.cfg file
Adding a server to the sql.ini file
Enabling TCP connections
Testing the Sybase Central installation
Starting and Stopping Servers
Overview
Starting servers
Using the Runserver file
Using the startserver command
Using the monserver command
Stopping servers
Stopping Adaptive Server
Stopping Backup Server
Stopping Monitor Server
Using the kill command
Shutdown and shared memory files
Setting server start-up parameters
Starting servers when the operating system restarts
Upgrading Sybase Servers
Overview of the upgrade process
System catalog changes during upgrade
Catalog changes that might affect existing applications
Pre-upgrade tasks
Check system and upgrade requirements
Check RUN_server file location
Test current applications and stored procedures
Procedure text is required for upgrade
Reserved words
Running a reserved word check
Addressing reserved words conflicts
Using quoted identifiers
Verify that users are logged off
Check database integrity
Back up databases
Dump transaction logs
Prepare the database and devices for the upgrade
Create a sybsystemdb database
sybsystemprocs
Turn off database options
Disable auditing
Disable disk mirroring
Preparing to upgrade servers with replicated databases
Suspending transaction processing and replication activities
Draining the transaction logs for primary databases
Draining the RSSD Transaction Log
Disabling the secondary truncation point
Upgrading to Adaptive Server 12.5
Using sqlupgrade
Using sqlupgraderes
Resource file attributes for upgrading Adaptive Server
Upgrading using sqlupgraderes
Post-upgrade tasks
Verify that servers are running
Restore functionality in Adaptive Server
Reenable Replication Server
Remove old log records
Reenable replication
Restoring replication after upgrade
Reenable auditing
Upgrading Backup Server, Monitor Server, and XP Server
Upgrading compiled objects with dbcc upgrade_object
Finding compiled object errors before production
Reserved word errors
Missing, truncated, or corrupted source text
Quoted identifier errors
Temporary table references
select * potential problem areas
Using dbcc upgrade_object
Increasing the log segment size
Error reporting
Using database dumps in upgrades
Upgrading using dump and load
Upgrading compiled objects in database dumps
Determining whether a compiled object has been upgraded
Migrating from 32-bit to 64-bit versions
Recovering from a failed upgrade
Removing Sybase Servers
Uninstalling Sybase Servers
Removing an existing Adaptive Server
Troubleshooting
Overview
Error log locations
For installation utilities
For Sybase servers
Solutions to common installation problems
Unable to use X-Windows
Fatal Error: Cannot Map libct.so
Cannot eject the CD from the drive
DISPLAY environment variable not set correctly
Client not authorized to connect to server
Address already in use
Adaptive Server failed to start
Cannot start XP Server
Configuration failed
Stopping Adaptive Server after a failure
Recovering from a failed installation
If installation fails after files are created
Troubleshooting resource file installations
Recovering from a failed upgrade
Restoring from backup
Rerunning the upgrade
Recording the upgrade manually
If the cause of the failure is known
If the cause of the failure is unknown
Troubleshooting SySAM
Authorization code input error
Alternative Installation Methods
Resource file installation
Installing from a resource file
Editing a sample resource file
Editing a resource file created by srvbuild
Resource file attributes for Adaptive Server
Resource file attributes for Backup Server
Using srvbuildres
Installing files in NOGUI mode
Completing the installation process
Installing components in cmdfile
Installation Guide for Linux
About this book
Overview
User roles
Product descriptions
Installation and setup utilities
PC-Client product descriptions
Sybase installation directory
Installation Requirements
System requirements
System requirements
Product disk space requirements
Language module sizes
Adaptive Server specifications
Adaptive Server devices and system databases
master device
sybsystemdb device and database
sysprocsdev device
Optional devices and databases
sybsecurity device and database
Sample databases
sybsyntax database
bccdb database
Determining the location, type, and size of a database device
Sybase Software Asset Management (SySAM)
SySAM overview
Adaptive Server features licensed through SySAM
How SySAM works
Adaptive Server check-out procedure
Starting Adaptive Server with optional features
Types of SySAM systems
SySAM in the network environment
Redundant servers
SySAM administration
Verify the software is running
Starting the software manually
Adding feature licenses
Installing Sybase Servers
Overview
Installation Definitions
Pre-installation tasks
Installing the Linux dummy-network interface
Installing server components
Installing using Red Hat Package Manager (RPM)
Registering Adaptive Server features
Configuring the servers
Installing the documentation
Online documentation
Installing the documentation
Removing Sybase servers
Post-Installation Tasks
Setting environment variables
Setting environment variables
Verifying that servers are running
Verifying that you can connect to servers
Connecting to Adaptive Server via Sybase Central
Setting the System Administrator password
Installing sample databases
Default devices for sample databases
interpubs database
jpubs database
Maintaining the sample databases
jConnect 4.5, 5.5, and Java utilities
Using open database connectivity
Installing stored procedures
Installing character sets
Installing online help for Transact-SQL syntax
Online syntax help: sp_syntax
Default device for the sybsyntax database
Installing sybsyntax
Configuring XP Server after initial installation
Installing Sybase PC-Client Products
Overview
Installing PC-Client products
Installing a Java Runtime Environment
Installing the Java Development Kit
Installing InfoMaker
Installing PowerDynamo
Configuring network connections for client products
Adding a server entry to the libtcl.cfg file
Adding a server to the sql.ini file
Enabling TCP connections
Testing the Sybase Central installation
Starting and Stopping Servers
Overview
Starting servers
Using the Runserver file
Using the startserver command
Using the monserver command
Starting servers when the operating system restarts
Stopping servers
Stopping Adaptive Server
Stopping Backup Server
Stopping Monitor Server
Using the kill command
Shutdown and shared memory files
Upgrading Sybase Servers
Overview
System catalog changes during upgrade
Catalog changes that might affect existing applications
Pre-upgrade tasks
Check system and upgrade requirements
Check RUN_server file location
Test current applications and stored procedures
Procedure text is required for upgrade
Reserved words
Running a reserved word check
Addressing reserved words conflicts
Using quoted identifiers
Verify that users are logged off
Check database integrity
Back up databases
Dump transaction logs
Preparing database and devices
Create a sybsystemdb database
sybsystemprocs
Turn off database options
Disable auditing
Disable disk mirroring
Preparing to upgrade servers with replicated databases
Suspending transaction processing and replication activities
Draining the transaction logs for primary databases
Draining the RSSD Transaction Log
Disabling the secondary truncation point
Restoring replication after upgrade
Upgrading to Adaptive Server 12.5
Using sqlupgrade
Using sqlupgraderes
Resource file attributes for upgrading Adaptive Server
Upgrading using sqlupgraderes
Post-upgrade tasks
Verifying that servers are running
Restore functionality in Adaptive Server
Reenabling replication server
Removing old log records
Reenabling replication
Reenabling auditing
Upgrading Backup Server, Monitor Server, and XP Server
Upgrading compiled objects with dbcc upgrade_object
Finding compiled object errors before production
Reserved word errors
Missing, truncated, or corrupted source text
Quoted identifier errors
Temporary table references
select * potential problem areas
Using dbcc upgrade_object
Increasing the log segment size
Error reporting
Upgrading compiled objects in database dumps
Determining whether a compiled object has been upgraded
Recovering from a failed upgrade
Troubleshooting
Overview
Error log locations
For installation utilities
For Sybase servers
Solutions to common installation problems
Unable to use X-Windows
Cannot eject the CD from the drive
DISPLAY environment variable not set correctly
Client not authorized to connect to server
Address already in use
Adaptive Server failed to boot
Cannot start XP Server
Stopping Adaptive Server after a failure
Recovering from a failed installation
If installation fails after the installer creates files
Troubleshooting resource file installations
Recovering from a failed upgrade
Restoring from backup
Rerunning the upgrade
Recording the upgrade manually
If the cause of the failure is known
If the cause of the failure is unknown
Troubleshooting SySAM
Authorization code input error
Alternative Installation Methods
Resource file installation
Editing a resource file
Editing a sample resource file
Editing a resource file created by srvbuild
Resource file attributes for Adaptive Server
Resource file attributes for Backup Server
Using srvbuildres
Troubleshooting resource file installations
Creating Raw Partitions
Overview
Creating the partitions
Binding the partitions for raw disk I/O
Accessing raw devices from Adaptive Sever
Installation Guide for Silicon Graphics IRIX
About this book
Overview
User roles
Product descriptions
Installation and setup utilities
PC-client product descriptions
Sybase installation directory
Installation Requirements
System requirements
Product disk space requirements
Language module sizes
Adaptive Server specifications
Adaptive Server devices and system databases
master device
sybsystemdb device and database
sysprocsdev device
Optional devices and databases
sybsecurity device and database
Sample databases
sybsyntax database
dbccdb database
dsync option on by default for database device files
Determining the location, type, and size of a database device
Sybase Software Asset Management (SySAM)
SySAM overview
Adaptive Server features licensed through SySAM
How SySAM works
Adaptive Server check-out procedure
Compatibility with other licensed products
Starting Adaptive Server with optional features
Types of SySAM systems
SySAM in the network environment
Redundant servers
SySAM administration
Verify the software is running
Starting the software manually
Adding feature licenses
Installing Sybase Servers
Overview
Installation definitions
Pre-installation tasks
Installing server components
Installation methods
Installing components with Studio Installer
Configuring the servers
Configuring EJB Server
Viewing the installation status output screen
Post-Installation Tasks
Environment variables
Setting environment variables
Verifying that servers are running
Verifying that you can connect to servers
Connecting to Adaptive Server via Sybase Central
Setting the System Administrator password
Installing sample databases
Default devices for sample databases
interpubs database
jpubs database
Maintaining the sample databases
jConnect 4.5, 5.5, and Java utilities
Using open database connectivity
Installing stored procedures
Installing character sets
Installing online help for Transact-SQL syntax
Online syntax help: sp_syntax
Default device for the sybsyntax database
Installing sybsyntax
Configuring XP Server after initial installation
Installing Sybase PC-Client Products
Overview
Installing PC-client products
Installing the Java Runtime Environment
Installing the Java Development Kit
Installing InfoMaker
Installing PowerDynamo
Configuring network connections for client products
Adding a server entry to the libtcl.cfg file
Adding a server to the sql.ini file
Enabling TCP connections
Testing the Sybase Central installation
Starting and Stopping Servers
Overview
Starting servers
Using the Runserver file
Using the startserver command
Using the monserver command
Stopping servers
Stopping Adaptive Server
Stopping Backup Server
Stopping Monitor Server
Using the kill command
Shutdown and shared memory files
Setting server start-up parameters
Starting servers when the operating system restarts
Upgrading Sybase Servers
Overview of the upgrade process
System catalog changes during upgrade
Catalog changes that might affect existing applications
Pre-upgrade tasks
Check system and upgrade requirements
Check RUN_server file location
Test current applications and stored procedures
Procedure text is required for upgrade
Reserved words
Running a reserved word check
Addressing reserved words conflicts
Using quoted identifiers
Verify that users are logged off
Check database integrity
Back up databases
Dump transaction logs
Prepare the database and devices for the upgrade
Create a sybsystemdb database
sybsystemprocs
Turn off database options
Disable auditing
Disable disk mirroring
Preparing to upgrade servers with replicated databases
Suspending transaction processing and replication activities
Draining the transaction logs for primary databases
Draining the RSSD Transaction Log
Disabling the secondary truncation point
Upgrading to Adaptive Server 12.5
Using sqlupgrade
Using sqlupgraderes
Resource file attributes for upgrading Adaptive Server
Upgrading using sqlupgraderes
Post-upgrade tasks
Verify that servers are running
Restore functionality in Adaptive Server
Reenable Replication Server
Remove old log records
Reenable replication
Restoring replication after upgrade
Reenable auditing
Upgrading Backup Server, Monitor Server, and XP Server
Upgrading compiled objects with dbcc upgrade_object
Finding compiled object errors before production
Reserved word errors
Missing, truncated, or corrupted source text
Quoted identifier errors
Temporary table references
select * potential problem areas
Using dbcc upgrade_object
Increasing the log segment size
Error reporting
Using database dumps in upgrades
Upgrading using dump and load
Upgrading compiled objects in database dumps
Determining whether a compiled object has been upgraded
Migrating from 32-bit to 64-bit versions
Recovering from a failed upgrade
Removing Sybase Servers
Uninstalling Sybase Servers
Removing an existing Adaptive Server
Troubleshooting
Overview
Error log locations
For installation utilities
For Sybase servers
Solutions to common installation problems
Unable to use X-Windows
Fatal Error: Cannot Map libct.so
Cannot eject the CD from the drive
DISPLAY environment variable not set correctly
Client not authorized to connect to server
Address already in use
Adaptive Server failed to start
Cannot start XP Server
Stopping Adaptive Server after a failure
Recovering from a failed installation
If installation fails after files are created
Troubleshooting resource file installations
Recovering from a failed upgrade
Restoring from backup
Rerunning the upgrade
Recording the upgrade manually
If the cause of the failure is known
If the cause of the failure is unknown
Troubleshooting SySAM
Authorization code input error
Alternative Installation Methods
Resource file installation
Installing from a resource file
Editing a sample resource file
Editing a resource file created by srvbuild
Resource file attributes for Adaptive Server
Resource file attributes for Backup Server
Using srvbuildres
Installing files in NOGUI mode
Completing the installation process
Installing components in cmdfile
Installation Guide for Sun Solaris
About this book
Overview
User roles
Product descriptions
Installation and setup utilities
PC-client product descriptions
Sybase installation directory
Installation Requirements
System requirements
Product disk space requirements
Language module sizes
Adaptive Server specifications
Adaptive Server devices and system databases
master device
sybsystemdb device and database
sysprocsdev device
Optional devices and databases
sybsecurity device and database
Sample databases
sybsyntax database
dbccdb database
dsync option on by default for database device files
Determining the location, type, and size of a database device
Sybase Software Asset Management (SySAM)
SySAM overview
Adaptive Server features licensed through SySAM
How SySAM works
Adaptive Server check-out procedure
Starting Adaptive Server with optional features
Types of SySAM systems
SySAM in the network environment
Redundant servers
SySAM administration
Verify the software is running
Starting the software manually
Starting SySAM as an automatic service
Adding feature licenses
Installing Sybase Servers
Overview
Installation definitions
Pre-installation tasks
Installing server components
Installation methods
Installing components with Studio Installer
Configuring the servers
Configuring EJB Server
Viewing the installation status output screen
Post-Installation Tasks
Environment variables
Setting environment variables
Setting EJB server environment
Verifying that servers are running
Verifying that you can connect to servers
Connecting to Adaptive Server via Sybase Central
Setting the System Administrator password
Installing sample databases
Default devices for sample databases
interpubs database
jpubs database
Maintaining the sample databases
jConnect 4.5, 5.5, and Java utilities
Using open database connectivity
Installing stored procedures
Installing character sets
Installing online help for Transact-SQL syntax
Online syntax help: sp_syntax
Default device for the sybsyntax database
Installing sybsyntax
Configuring XP Server after initial installation
Installing Sybase PC-Client Products
Overview
Installing PC-client products
Installing the Java Runtime Environment
Installing the Java Development Kit
Installing InfoMaker
Installing PowerDynamo
Configuring network connections for client products
Adding a server entry to the libtcl.cfg file
Adding a server to the sql.ini file
Enabling TCP connections
Testing the Sybase Central installation
Starting and Stopping Servers
Overview
Starting servers
Using the Runserver file
Using the startserver command
Using the monserver command
Stopping servers
Stopping Adaptive Server
Stopping Backup Server
Stopping Monitor Server
Using the kill command
Shutdown and shared memory files
Setting server start-up parameters
Starting servers when the operating system restarts
Upgrading Sybase Servers
Overview of the upgrade process
System catalog changes during upgrade
Catalog changes that might affect existing applications
Pre-upgrade tasks
Check system and upgrade requirements
Check RUN_server file location
Test current applications and stored procedures
Procedure text is required for upgrade
Reserved words
Running a reserved word check
Addressing reserved words conflicts
Using quoted identifiers
Verify that users are logged off
Check database integrity
Back up databases
Dump transaction logs
Prepare the database and devices for the upgrade
Create a sybsystemdb database
sybsystemprocs
Turn off database options
Disable auditing
Disable disk mirroring
Preparing to upgrade servers with replicated databases
Suspending transaction processing and replication activities
Draining the transaction logs for primary databases
Draining the RSSD Transaction Log
Disabling the secondary truncation point
Upgrading to Adaptive Server 12.5
Using sqlupgrade
Using sqlupgraderes
Resource file attributes for upgrading Adaptive Server
Upgrading using sqlupgraderes
Post-upgrade tasks
Verify that servers are running
Restore functionality in Adaptive Server
Reenable Replication Server
Remove old log records
Reenable replication
Restoring replication after upgrade
Reenable auditing
Upgrading Backup Server, Monitor Server, and XP Server
Upgrading compiled objects with dbcc upgrade_object
Finding compiled object errors before production
Reserved word errors
Missing, truncated, or corrupted source text
Quoted identifier errors
Temporary table references
select * potential problem areas
Using dbcc upgrade_object
Increasing the log segment size
Error reporting
Using database dumps in upgrades
Upgrading using dump and load
Upgrading compiled objects in database dumps
Determining whether a compiled object has been upgraded
Migrating from 32-bit to 64-bit versions
Recovering from a failed upgrade
Removing Sybase Servers
Uninstalling Sybase Servers
Removing an existing Adaptive Server
Troubleshooting
Overview
Error log locations
For installation utilities
For Sybase servers
Solutions to common installation problems
Unable to use X-Windows
Fatal Error: Cannot Map libct.so
Cannot eject the CD from the drive
DISPLAY environment variable not set correctly
Client not authorized to connect to server
Address already in use
Adaptive Server failed to start
Cannot start XP Server
Configuration failed
Stopping Adaptive Server after a failure
Recovering from a failed installation
If installation fails after files are created
Troubleshooting resource file installations
Recovering from a failed upgrade
Restoring from backup
Rerunning the upgrade
Recording the upgrade manually
If the cause of the failure is known
If the cause of the failure is unknown
Troubleshooting SySAM
Authorization code input error
Alternative Installation Methods
Resource file installation
Installing from a resource file
Editing a sample resource file
Editing a resource file created by srvbuild
Resource file attributes for Adaptive Server
Resource file attributes for Backup Server
Using srvbuildres
Installing files in NOGUI mode
Completing the installation process
Installing components in cmdfile
Installation Guide for Windows
About this book
Overview
User roles
Product descriptions
Installation and setup utilities
PC-client product descriptions
Sybase installation directory
Installation Requirements
System requirements
Product disk space requirements
Language module sizes
Adaptive Server specifications
Adaptive Server devices and system databases
master device
sybsystemdb device and database
sysprocsdev device
Optional devices and databases
sybsecurity device and database
Sample databases
sybsyntax database
dbccdb database
Determining the location, type, and size of a database device
Sybase Software Asset Management (SySAM)
SySAM overview
Adaptive Server features licensed through SySAM
How SySAM works
Adaptive Server check-out procedure
Starting Adaptive Server with optional features
Types of SySAM systems
SySAM in the network environment
Redundant servers
SySAM administration
Verify the software is running
Starting the software manually
Starting SySAM as an automatic service
Adding feature licenses
Installing Sybase Servers
Overview
Installation definitions
Pre-installation tasks
Installing server components
Installation methods
Installing components with Studio Installer
Configuring the servers
Post-Installation Tasks
Environment variables
Setting environment variables
Reset variables temporarily
Reset variables permanently
Setting EJB server environment
Verifying that servers are running
Verifying that you can connect to servers
Connecting to Adaptive Server via Sybase Central
Setting the System Administrator password
Installing sample databases
Default devices for sample databases
interpubs database
jpubs database
Maintaining the sample databases
jConnect 4.5, 5.5, and Java utilities
Using open database connectivity
Installing stored procedures
Installing character sets
Installing online help for Transact-SQL syntax
Online syntax help: sp_syntax
Default device for the sybsyntax database
Installing sybsyntax
Installing Sybase PC-Client Products
Overview
Installing PC-client products
Installing the Java Runtime Environment
Installing the Java Development Kit
Installing InfoMaker
Installing PowerDynamo
Configuring network connections for client products
Adding a server entry to the libtcl.cfg file
Adding a server to the sql.ini file
Enabling TCP connections
Testing the Sybase Central installation
Starting and Stopping Servers
Overview
Requirements for starting servers
Checking the server status
Starting servers
Starting and stopping servers using the Control Panel
Starting, stopping, and pausing servers manually
Stopping servers
Stopping Adaptive Server
Stopping Backup Server
Stopping Monitor Server
Setting server start-up parameters
Server start-up parameters
Changing start-up parameters
Starting servers as an automatic service
Upgrading Sybase Servers
Overview of the upgrade process
System catalog changes during upgrade
Catalog changes that might affect existing applications
Pre-upgrade tasks
Check system and upgrade requirements
Note server and device names and locations
Test current applications and stored procedures
Procedure text is required for upgrade
Reserved words
Running a reserved word check
Addressing reserved words conflicts
Using quoted identifiers
Verify that users are logged off
Check database integrity
Back up databases
Dump transaction logs
Prepare the database and devices for the upgrade
Create a sybsystemdb database
sybsystemprocs
Turn off database options
Disable auditing
Disable disk mirroring
Preparing to upgrade servers with replicated databases
Suspending transaction processing and replication activities
Draining the transaction logs for primary databases
Draining the RSSD Transaction Log
Disabling the secondary truncation point
Upgrading to Adaptive Server 12.5
Upgrading the server
Testing the Adaptive Server upgrade
Post-upgrade tasks
Verify that servers are running
Restore functionality in Adaptive Server
Reenable Replication Server
Remove old log records
Reenable replication
Restoring replication after upgrade
Reenable auditing
Upgrading Backup Server, Monitor Server, and XP Server
Upgrading compiled objects with dbcc upgrade_object
Finding compiled object errors before production
Reserved word errors
Missing, truncated, or corrupted source text
Quoted identifier errors
Temporary table references
select * potential problem areas
Using dbcc upgrade_object
Increasing the log segment size
Error reporting
Using database dumps in upgrades
Upgrading using dump and load
Upgrading compiled objects in database dumps
Determining whether a compiled object has been upgraded
Recovering from a failed upgrade
Removing Sybase Servers
Uninstalling Sybase products
Uninstalling Adaptive Server 12.5
To remove an old server
Troubleshooting
Overview
Error log locations
For installation utilities
For Sybase servers
Solutions to common installation problems
Adaptive Server failed to start
Stopping Adaptive Server after a failure
Recovering from a failed installation
If installation fails after files are created
If Adaptive Server fails the pre-upgrade eligibility test
Recovering from a failed upgrade
Restoring from backup
Rerunning the upgrade
Recording the upgrade manually
If the cause of the failure is known
If the cause of the failure is unknown
Troubleshooting SySAM
Authorization code input error
Alternative Installation Methods
Resource file installation
Installing from a resource file
Editing a sample resource file
Resource file variables
Running the sybatch utility
Installing files in NOGUI mode
Completing the installation process
Installing components in cmdfile
Configuration Guide for UNIX
About this book
Introduction
About Adaptive Server
System-specific issues
Definition of terms
User roles
Environment variables
Adaptive Server devices and system databases
The master device
The sybsystemdb device
The sysprocsdev device
Optional devices and databases
Client/server communication
Lightweight Directory Access Protocol
Changing Adaptive Server configuration
Support for high-availability products
Languages other than U.S. English
Starting and Stopping Servers
Overview
Requirements for starting servers
Starting servers
Server start-up parameters
Changing start-up parameters
Using a runserver file
Using the startserver command
Using the monserver command
Starting servers when the operating system restarts
For Compaq Tru64
For HP-UX
For IBM RS/6000
For SGI
For Sun Solaris
For Macintosh OS X
The boot sequence
BootROM
BootX
Kernel initialization
System initialization
The rc.boot and rc scripts
Start-up items
Sybase-provided StartupItems
Stopping servers
Stopping Adaptive Server
Stopping Backup Server
Stopping Monitor Server
Using the kill command
Shutdown and shared memory files
Configuring the Operating System
Setting environment variables
DSLISTEN
DSQUERY
PATH
Using the stty setting
Restoring correct permissions
File descriptors and user connections
For Compaq Tru64
For HP-UX
For Sun Solaris and SGI
For Macintosh OS X
Displaying current soft and hard limits
Increasing the soft limit
Increasing the hard limit
Sample program
Adjusting the client connection timeout period
For Compaq Tru64
For HP-UX
For IBM RS/6000
For Sun Solaris
For Macintosh OS X
Checking for hardware errors
For Compaq Tru64
For HP-UX
For IBM RS/6000
For SGI
For Sun Solaris
For Macintosh OS X
Monitoring the use of operating system resources
For Compaq Tru64
For HP-UX
For IBM RS/6000
For Sun Solaris
For SGI
For Macintosh OS X
A sample C shell maintenance script
Adaptive Server Default Configuration
Default settings
Setting Up Communications Across the Network
How clients connect to Adaptive Server
Creating an interfaces file
Contents of an interfaces file
How Adaptive Server listens for client connections
How a client uses an interfaces file
Heterogeneous and homogeneous environments
Understanding the format of the interfaces file
Components of an interfaces file entry
Format by platform
Creating interfaces files for SPX
Utilities for editing the interfaces file
Creating a master interfaces file
Using dsedit or dscp to create a master interfaces file
Using a text editor to create a master interfaces file
Configuring interfaces files for multiple networks
Configuring the server for multiple network handlers
Sample interfaces files for multiple network handlers
Configuring the client connections
Using one network-independent DSQUERY name
Using different DSQUERY names
Configuring for query port backup
Troubleshooting
Server fails to start
Error when executing an ESP
Lightweight Directory Access Protocol in Adaptive Server
Overview
LDAP directory services versus the Sybase interfaces file
The libtcl.cfg and libtcl64.cfg files
Enabling LDAP directory services
Adding a server to the directory services
Multiple directory services
Encrypting the password
Performance
Migrating from the interfaces file to LDAP
Customizing Localization for Adaptive Server
Overview of localization support
Language modules
Default character sets for servers
Changing the default language and character set
Changing the default character set for servers
Supported character sets
Character set conversion
Conversions between server and client
Sort orders
Available sort orders
Language modules
Installing a new language module
Message languages
Localization
Localization directories
About the directory
About the charsets directory
About the locales.dat file
Format of locales.dat file entries
How client applications use locales.dat
Editing the locales.dat file
Changing the localization configuration
For Adaptive Server
For Backup Server
Configuring Adaptive Server for other character sets
Sort orders
Character sets
charset utility
Logging Error Messages and Events
Adaptive Server error logging
Enabling and disabling error logging
Types of information logged
Setting error log paths
Setting the Adaptive Server error log path
Managing messages
Logging user-defined messages
New messages
Existing messages
Logging auditing events
Managing Adaptive Server Databases
Managing database devices
Device requirements
Creating .dat files for database devices
Backing up and restoring data
Using a tape drive
Setting the maximum capacity for a tape drive
Dumping across a network
Examples of backing up and restoring databases
User databases
System databases
Using disk drives
Monitoring disk usage
Adding Optional Functionality to Adaptive Server
Adding auditing
Audit system devices and databases
Overview of audit installation
Pre-installation tasks for auditing devices
Installing auditing
Installing online help for Transact-SQL syntax
Online syntax help: sp_syntax
Default device for the sybsyntax database
Installing sybsyntax
Configuration Guide for Windows NT
About this book
Introduction
About Adaptive Server
System-specific issues
Definition of terms
User roles
Environment variables
Adaptive Server devices and system databases
The master device
The sybsystemdb device
The sysprocsdev device
Optional devices and databases
Client/server communication (the interfaces file)
Lightweight Directory Access Protocol
Changing Adaptive Server configuration
Support for high-availability products
Languages other than U.S. English
Starting and Stopping Servers
Overview
Requirements for starting servers
Starting servers
Server start-up parameters
Changing start-up parameters
Starting and stopping servers using Sybase Central
Starting and stopping servers using the Control Panel
Starting servers as an automatic service
Starting, stopping, and pausing servers manually
Stopping servers
Stopping Adaptive Server
Stopping Backup Server
Stopping Monitor Server
Monitoring servers
Sybase Central
The Control Panel
Default Adaptive Server Configuration
Starting Server Config for Adaptive Server
Configuring Adaptive Server
Setting Adaptive Server parameters
Changing the default Backup Server
Changing the default XP Server
Configuring Backup Server
Configuring Monitor Server
Supporting access to large memory
Network Communications Using sql.ini
How clients connect to Adaptive Server
How Adaptive Server listens for client connections
How a client accesses Adaptive Server
Enabling client access to a server
Changing the server entries in sql.ini
Components in the sql.ini file
Server name
Network driver
Service type
Server address
Address format
IP address
Named Pipes format
Windows Sockets format
NWLink IPX/SPX format
Sharing network configuration information
Creating a master sql.ini file
Using NT Registry as a directory service
Verifying server connections
Configuring ODBC connections
Configuring the ODBC driver
Lightweight Directory Access Protocol in Adaptive Server
Overview
LDAP directory services versus the Sybase interfaces file
The libtcl.cfg and libtcl64.cfg files
Enabling LDAP directory services
Adding a server to the directory services
Multiple directory services
Encrypting the password
Performance
Migrating from the sql.ini file to LDAP
Customizing Localization for Adaptive Server
Overview of localization support
Language modules
Default character sets for servers
Changing the default character set for servers
Default character set after installation
Changing the default character set for servers
Supported character sets
Character set conversion
Conversions between server and client
Sort orders
Available sort orders
Language modules
Installing a new language module
Message languages
Localization
Localization directories
About the directory
About the charsets directory
About the locales.dat file
Format of locales.dat file entries
How client applications use locales.dat
Editing the locales.dat file
Changing the localization configuration
For Adaptive Server
For Backup Server
Configuring Adaptive Server for other character sets
Sort orders
Character sets
charset utility
Logging Error Messages and Events
Logging errors and events
Adaptive Server error logging
Enabling and disabling error logging
Types of information logged
NT event-logging
Setting up NT event-logging for use by Adaptive Server
Types of information logged
Managing the logs
Setting error log paths
Setting the Adaptive Server error log path
Setting the Backup Server error log path
Setting the Monitor Server error log path
Enabling and disabling NT event logging
Using Server Config
Using sp_configure
Managing messages
Logging user-defined messages
New messages
Existing messages
Logging auditing events
Logging user-defined events
Using a remote log
Using a central logging site
Logging messages from multiple Adaptive Servers
Setting up a local central logging site
To create and define a Registry key
Viewing the messages
In the NT event log
In the Adaptive Server error log
Using Security Services with NT LAN Manager
Security services with NT LAN Manager
How login authentication works
Administering security services using LAN Manager
Modifying configuration files for a unified login
Setting up drivers for network-based security
Entries for network drivers
Entries for Directory Services
Entries for security drivers
Checking the LAN Manager’s local name
Specifying security information for Adaptive Server
Identifying users and servers to LAN Manager
Configuring Adaptive Server for LAN Manager security
Enabling and disabling external security services
Managing unified login
Requiring unified login
Establishing a secure default login
Mapping LAN Manager login names to server names
Requiring data integrity check
Ensuring adequate memory for security services
Initiating the new security services
Adding logins to support unified login
General procedure for adding logins
Defining the connection to a server for security services
Specifying the principal name
Specifying network-based user authentication
Specifying the name assigned to LAN Manager
Determining the status of security services
Configuration parameters used in security services
Checking data integrity
Checking message sequence
Detecting interception or replay
Specifying a login
Controlling user authentication
Managing login security on an NT computer
Overview of security features
Adaptive Server security
Combined Adaptive Server and NT login security
Standard mode
Integrated mode
Mixed mode
Managing the login security features
Permitting trusted connections
NT Registry parameters
Administering login security using system procedures
Assigning trusted connection permissions
Displaying the current Registry values
Displaying permissions and user names
Revoking permissions granted with sp_grantlogin
Configuring login security
Create NT users and groups
Configure mapping and Default Domain values
Set login security mode
Add network login names to syslogins
Assign roles
Changing login security options
Using E-mail with Adaptive Server
Sybmail messages
Sending messages
Receiving messages
Preparing NT Mail for Sybmail
Connecting to a post office
Creating a mailbox for Adaptive Server
Creating a mail profile for Adaptive Server
Creating an Adaptive Server login for Sybmail
Sybmail and extended stored procedures
Managing a mail session
Starting a session
Starting Sybmail without parameters
Stopping a mail session
Stored and extended procedures for handling messages
Sending messages
Text messages
Query result messages
Receiving messages
Finding the next message
Reading a specific message
Deleting a message
Processing incoming mail
Using Sybmail security
Setting execution privileges
Setting the execution context
Naming both the user and the database
Naming the user but not the database
Naming the database but not the user
Naming neither the user nor the database
Managing Adaptive Server Databases
Managing database devices
Device requirements
Creating .dat files for database devices
Backing up and restoring data
Using a tape drive
NT tape drive names
Setting the maximum capacity for a tape drive
Using a hard disk
Dumping across a network
Examples of backing up and restoring databases
User databases
System databases
Optimizing Adaptive Server performance and tuning
Using dedicated Adaptive Server operation
Using disk drives
Monitoring disk usage
Monitoring Adaptive Server statistics with NT Performance Monitor
Adding Optional Functionality to Adaptive Server
Installing auditing
Audit system devices and databases
The sybsecurity device and database
Tables and devices for the audit trail
Device for syslogs systems table
Pre-installation tasks for auditing devices
Installing Auditing
Installing online help for Transact-SQL syntax
Online syntax help: sp_syntax
Default device for the sybsyntax database
Installing sybsyntax
Troubleshooting Network Connections
The dsedit Server Ping Utility
Running Server Ping
Troubleshooting connection failures
When a test fails
Using returned messages to diagnose a failure
Failure to connect to Adaptive Server
Failure to load Net-Library DLLs
Failure of other applications
Before calling Sybase Technical Support
Adaptive Server Registry Keys
ASE Replicator User's Guide
About this book
Introduction to ASE Replicator
Understanding replication
Table replication
Stored procedure replication
Transaction replication
Guaranteed delivery
Understanding ASE Replicator
ASE Replicator components
ASE Replicator process
Distribution Database
Publisher component
Distributor component
ASE Replicator processing
ASE Replicator objects
Database connections
Publications and subscriptions
Articles
Fields
ASE Replicator limitations
Setting Up and Starting ASE Replicator
Setting up ASE Replicator
Configuring the primary Adaptive Server
Enabling and configuring CIS
Setting up the ASE Replicator system user
Defining a local name and remote alias for the primary Adaptive Server
Defining a remote server name for the ASE Replicator process
Configuring the tempdb database
Creating the Distribution Database
Configuring the replicate servers and databases
Identifying the remote server
Setting up a separate a Maintenance User login
Granting permissions in the replicate database
Initializing the ASE Replicator process
Running the aserep script the first time
Setting up the sp_helpddb system procedure
Starting ASE Replicator
Using the aserep script
Using the RUN script
Setting up a replication system
Administering ASE Replicator
Administering the ASE Replicator process
Configuring ASE Replicator
Shutting down the ASE Replicator process
Suspending replication
Resuming replication
Monitoring ASE Replicator
Managing database connections
Creating a database connection
Creating a primary database connection
Creating a replicate database connection
Deleting a database connection
Deleting a primary database connection
Deleting a replicate database connection
Configuring a database connection
Configuring a primary database connection
Configuring a replicate database connection
Suspending and resuming database connections
Suspending database connections
Resuming database connections
Getting information about database connections
Getting connection metadata and status information
Getting statistics information for connections
Getting connection configuration information
Getting primary connection configuration information
Getting replicate connection configuration information
Managing publications and subscriptions
Creating publications and subscriptions
Deleting publications and subscriptions
Suspending and resuming subscriptions
Getting information about publications and subscriptions
Getting metadata and status information for publications and subscriptions
Getting statistics information for subscriptions
Managing primary and replicate articles
Creating primary articles
Creating replicate articles
Deleting primary articles
Deleting replicate articles
Materializing and validating replicate articles
Getting information about primary articles
Getting primary article metadata information
Getting information about published fields in primary articles
Getting information about primary articles in publications
Getting information about unpublished objects
Getting information about primary articles with no subscribers
Getting information about replicate articles
Getting replicate article metadata information
Getting information about fields in replicate articles
Getting information about replicate articles in subscriptions
ASE Replicator configuration parameters
General configuration parameters
admin_port
ase_charset
ase_host
ase_port
batch_size
batch_timeout
ddb_name
log_directory
log_trace_verbose
log_wrap
monitor_delay
queue_size
scan_sleep_increment
scan_sleep_max
stat_trunc_interval
stat_write_timeout
status_monitoring
truncate_numops
Connection configuration parameters
gen_id
lti_version
mode
numrecs
queue_size
scan_sleep_increment
scan_sleep_max
timeout
ASE Replicator Procedures
sp_addprimaryart
sp_addprimaryconn
sp_addpub
sp_addreplicateart
sp_addreplicateconn
sp_addsub
sp_configprimaryconn
sp_configrep
sp_configreplicateconn
sp_dropprimaryart
sp_dropprimaryconn
sp_droppub
sp_dropreplicateart
sp_dropreplicateconn
sp_dropsub
sp_helpconn
sp_helplastcommit
sp_helplocator
sp_helpprimaryart
sp_helpprimaryconn
sp_helppub
sp_helprep
sp_helpreplicateart
sp_helpreplicateconn
sp_helpsub
sp_materializesub
sp_resumeprimaryconn
sp_resumerep
sp_resumereplicateconn
sp_resumesub
sp_shutdownrep
sp_suspendprimaryconn
sp_suspendrep
sp_suspendreplicateconn
sp_suspendsub
sp_tracerep
sp_validatesub
Troubleshooting ASE Replicator
Problems that prevent replication from starting
Primary server configuration problems
Remote server configuration problems
ASE Replicator configuration problems
User login or permission problems
Problems that cause ongoing replication to fail
Schema changes in primary or replicate tables
Altering a published primary object
Altering a replicate object
Datatypes, constraints, and column properties
Replicating approximate numeric datatypes
Replicating the value of the timestamp datatype
Replicating IDENTITY columns
Incompatible datatypes
Incompatible primary key constraints
Different null types
Distribution Database Schema
Distribution Database base objects
Distribution Database datatypes
Base tables in the Distribution Database
Distribution Database system table
Connections table
Connection properties table
Publications table
Primary articles table
Primary article fields table
Primary articles/publications relation table
Subscriptions table
Replicate articles table
Replicate article fields table
Transaction log table
Configuration table
Trace table
Statistics table
Base procedures in the Distribution Database
Distribution Database shadow tables
Shadow tables for primary tables without large objects
Shadow tables for primary tables with large objects
Shadow tables for primary procedures
Distribution Database replicate objects
Distribution procedures
Replicate article proxy tables
Glossary
Component Integration Services User's Guide
About this book
Audience
How to use this book
Adaptive Server Enterprise documents
Other sources of information
Conventions
If you need help
Introduction
New features in Adaptive Server Enterprise 12.5
Who can use Component Integration Services
Steps needed to use Component Integration Services
Understanding Component Integration Services
Basic concepts
Access methods
Server classes
Object types
Interface to remote servers
Defining the storage location of individual objects
Creating proxy tables
Proxy tables
Using the create table command
Using the create existing table command
Datatype Conversions
Example of Remote Table Definition
Using the create proxy_table command
Remote Procedures as proxy tables
New server limits
Remote server capabilities
create new proxy table
create existing proxy table
create proxy_table
alter proxy table
select, insert, delete, update
RPC handling
Cascading proxy tables
Proxy databases
User proxy databases
User Proxy Database Schema Synchronization
System proxy databases
System proxy database creation
Schema synchronization when current database has a system proxy database
Stored procedure execution within a system proxy database
Additional behavior of the system proxy database
DDL commands behavior affected by proxy databases
File system access
Directory access
Recursion through subordinate directories
File access
Security considerations
ANSI joins
When the remote server supports only ANSI joins
When the remote server supports both ANSI joins and T-SQL joins
When the remote server supports only DB2-ANSI
When an ANSI query is received for a server that does not support ANSI syntax
50-Table join limit
Union in views
Referential integrity
Remote servers
Defining remote servers
Server class ASEnterprise
Server class ASAnywhere
Server class ASIQ
Server class sql_server
Server class db2
Server class direct_connect
Server class sds
Connection management
LDAP directory services
Secure communication with SSL
Trusted root files
Security issues
Remote server logins
Trusted mode
Mapping of external logins
Remote server connection failover
Remote server capabilities
Query processing
Processing steps
Query parsing
Query normalization
Query preprocessing
Decision point
Component Integration Services plan generation
Adaptive Server optimization and plan generation
Component Integration Services remote location optimizer
Query execution
Distributed query optimization
Component Integration Services access methods
Query plan execution
create table command
create existing table command
alter table command
create index command
drop table command
drop index command
truncate table command
Passthrough mode
The connect to command
sp_autoconnect
sp_passthru
sp_remotesql
Quoted identifier support
auto identity option
Triggers
RPC handling and Component Integration Services
Site handler and outbound RPCs
Component Integration Services and outbound RPCs
Text parameters for RPCs
Text parameter support for XJS/390
Transaction management
Two-phase commit
Server classes and ASTC
Strict DTM enforcement
Enable xact coordination
Enable CIS
CIS set commands
Attach and detach
Pre-12.x servers
Transactional RPCs
Restrictions on transaction management
Using update statistics
Finding index names
Java in the database
@@textsize
@@stringsize
Constraints on Java class columns
Error messages
SQLJ in Adaptive Server Enterprise
Changes to CIS
Java Abstract Datatypes (ADTs)
Datatypes
Unicode support
create table
create existing table
create proxy_table
alter table
select, insert, update and delete statements
Datatype conversions
text and image datatypes
Restrictions on text and image columns
Limits of @@textsize
Odd bytes padded
Converting text and image datatypes
Pattern matching with text data
Entering text and image values
readtext using bytes
text and image with bulk copy
Error logging
text and image data with server class sql_server
text and image data with server class direct_connect (access_server)
db2 server issues
Fine-grained access control
The select into command
select into syntax
Execute immediate
Configuration and tuning
Using sp_configure
sysconfigures table
Changing the configuration parameters
Component Integration Services configuration parameters
Dynamic reconfiguration
Global variables for status
SQL reference
dbcc commands
dbcc options
Trace flags
Transact-SQL commands
alter database
alter table
begin transaction
case
close
commit transaction
connect to...disconnect
create database
create existing table
create index
create proxy_table
create table
create trigger
deallocate cursor
declare cursor
delete
drop database
drop index
drop table
execute
fetch
Functions
insert
open
prepare transaction
readtext
rollback transaction
select
set
setuser
truncate table
update
update statistics
writetext
Tutorial
Getting Started with Component Integration Services
Adding a Remote Server
Overview of the Procedure
Step 1: Add the Remote Server to the Interfaces File
Step 2: Create Server Entries in System Tables
Step 3: Add an Alternate Login and Password
Verifying Connectivity
Mapping Remote Objects to Local Proxy Tables
Overview of the Procedure
Step 1: Define the Storage Location of a Remote Object
Step 2: Map Remote Table Schema to Adaptive Server
Join Between Two Remote Tables
Overview of the Procedure
Step 1: Add the Remote Servers to the Interfaces File
Step 2: Define the Remote Servers
Step 4: Map the Remote Tables to Adaptive Server
Step 5: Perform the Join
Troubleshooting
Problems Accessing Component Integration Services
Problems Using Component Integration Services
Unable to Access Remote Server
Requested Server Name Not Found
Driver Call to Connect Two Endpoints Failed
Login Failed
Unable to Access Remote Object
Problem Retrieving Data From Remote Objects
Object Is Altered Outside Component Integration Services
Index Is Added or Dropped Outside CIS
If You Need Help
Full-Text Search Specialty Data Store User's Guide
About this book
Audience
How to Use This Book
Adaptive Server Enterprise Documents
Other Sources of Information
Conventions
Directory Paths
Formatting SQL Statements
SQL Syntax Conventions
Case
Obligatory Options {You Must Choose At Least One}
Optional Options [You Don’t Have to Choose Any]
Ellipsis: Do It Again (and Again)...
If You Need Help
Introduction
What Is the Full-Text Search Specialty Data Store?
Capabilities of the Full-Text Search Engine
High Availablility
Understanding the Full-Text Search Engine
Components of the Full-Text Search Engine
The Source Table
The Verity Collections
Filters
The text_db Database
The vesaux Table
The vesauxcol Table
The Index Table
The text_events Table
Relationships Between the Components
How a Full-Text Search Works
Configuring Adaptive Server for Full-Text Searches
Configuring Adaptive Server for a Full-Text Search Engine
Enabling Configuration Parameters
Running the installtextserver Script
Editing the installtextserver Script
Running the installtextserver Script
Running the installmessages Script
Running the installevent Script
Editing the installevent Script
Running the installevent Script
Name the local server
Creating and Maintaining the Text Indexes
Setting Up Source Tables for Indexing
Adding an IDENTITY Column to a Source Table
Adding a Unique Index to an IDENTITY Column
Creating the Text Index and Index Table
Specifying Multiple Columns When Creating a Text Index
Bringing the Database Online for Full-Text Searches
Propagating Changes to the Text Index
Replicating Text Indexes
Example: Enabling a New Database for Text Searches
Step 1. Verify that the text_events Table Exists
Step 2. Check for an IDENTITY Column
Step 3. Create a Unique Index on the IDENTITY Column
Step 4. Create the Text Index and Index Table
Step 5. Bring the Database Online for a Full-Text Search
Setting Up Verity Functions
Enabling Query-By-Example, Summarization, and Clustering
Editing the Master style.prm File
Editing Individual style.prm Files
Setting Up a Column to Use As a Sort Specification
Using Filters on Text That Contains Tags
Creating a Custom Thesaurus (Enhanced Version Only)
Examining the Default Thesaurus (Optional)
Creating the Control File
Control File Syntax
Creating the Thesaurus
Replacing the Default Thesaurus with the Custom Thesaurus
Creating Topics (Enhanced Version Only)
Creating an Outline File
Creating a Topic Set Directory
Creating a Knowledge Base Map
Defining the Location of the Knowledge Base Map
Executing Queries Against Defined Topics
Troubleshooting Topics
Writing Full-Text Search Queries
Components of a Full-Text Search Query
Default Behaviour
Pseudo Columns in the Index Table
Using the score Column to Relevance-Rank Search Results
Using the sort_by Column to Specify a Sort Order
Using the summary Column to Summarize Documents
Using Pseudo Columns to Request Clustered Result Sets
Preparing to Use Clustering
Writing Queries Requesting a Clustered Result Set
Full-Text Search Operators
Considerations When Using Verity Operators
Using the Verity Operators
accrue
and, or
complement
in
like
near, near/n
or
phrase
paragraph
product
sentence
stem
sum
thesaurus
topic (Enhanced Version Only)
wildcard
word
yesno
Operator Modifiers
System Administration
Starting the Full-Text Search Engine on UNIX
Creating the Runserver File
Starting the Full-Text Search Engine on Windows NT
Starting the Full-Text Search Engine As a Service
Shutting Down the Full-Text Search Engine
Modifying the Configuration Parameters
Modifying Values in the Enhanced Version
Available Configuration Parameters
Setting the Default Language
.Setting the Default Character Set
Setting the Default Sort Order
Setting Trace Flags
Setting Open Server Trace Flags
Setting Case Sensitivity
Backup and Recovery for the Enhanced Full-Text Search Engine
Customizable Backup and Restore
Backing Up Verity Collections
Restoring Collections and Text Indexes from Backup
Performance and Tuning
Updating Existing Indexes
Increasing Query Performance
Limiting the Number of Rows
Ensuring the Correct Join Order for Queries
Reconfiguring Adaptive Server
cis cursor rows
cis packet size
Reconfiguring the Full-Text Search Engine
batch_size
min_sessions and max_sessions
Using sp_text_notify
Configuring Multiple Full-Text Search Engines
Creating Multiple Full-Text Search Engines at Start-Up
Adding Full-Text Search Engines
Configuring Additional Full-Text Search Engines
Multiple Users
Verity Topics
What are Topics?
Topic Organization
Weight Assignments
Using a Topic Outline File
Making Topics Available
Setup Process
Knowledge Bases of Topics
Combining Topics into a Knowledge Base
Structure of Topics
Top-Level Topics
Subtopics
Evidence Topics
Topic and Subtopic Relationships
Maximum Number of Topics
Topic Naming Issues
Topic Name Length
Case Sensitivity
Verity Query Language
Query Language Summary
Evidence Operators
Proximity Operators
Relational Operators
Concept Operators
Boolean Operators
Modifiers
Operator Precedence Rules
Sample Topic Outlines
Operator Reference
ACCRUE Operator
ALL Operator
AND Operator
ANY Operator
CONTAINS Operator
ENDS Operator
= (EQUALS) Operator
FILTER Operator
> (GREATER THAN) Operator
>= (GREATER THAN OR EQUAL TO) Operator
< (LESS THAN) Operator
<= (LESS THAN OR EQUAL TO) Operator
IN Operator
MATCHES Operator
NEAR Operator
NEAR/N Operator
OR Operator
PARAGRAPH Operator
PHRASE Operator
SENTENCE Operator
SOUNDEX Operator
STARTS Operator
STEM Operator
SUBSTRING Operator
THESAURUS Operator
WILDCARD Operator
Using Wildcard Special Characters
Searching for Non-alphanumeric Characters
Searching for Wildcard Characters as Literals
Searching for Special Characters as Literals
WORD Operator
Modifier Reference
CASE Modifier
MANY Modifier
NOT Modifier
ORDER Modifier
Weights and Document Importance
Topic Weights
Which Operators Accept Weights
How Weights Affect Importance
Assigning Weights
Automatic Weight Assignments
Tips for Assigning Weights
Changing Weights
Topic Scoring and Document Importance
Designing Topics
Preparing Your Topic Design
Understanding Your Information Needs
Understanding Your Documents
Using Scanned Data
Categorizing Document Samples
Topic Design Strategies
Top-Down Design
Bottom-Up Design
Designing the Initial Topic
Outlining a Topic
Top-Down Topic Outline Example
Step One: Establishing an Information Hierarchy
Step Two: Establishing Individual Search Categories
Step Three: Establishing the Topics to be Built
Bottom-Up Topic Outline Example
Step One: Identifying Low-level Topics
Step Two: Categorizing Related Subtopics
Step Three: Establishing Top-Level Topics
System Procedures
sp_check_text_index
sp_clean_text_events
sp_clean_text_indexes
sp_create_text_index
sp_drop_text_index
sp_help_text_index
sp_optimize_text_index
sp_redo_text_events
sp_refresh_text_index
sp_show_text_online
sp_text_cluster
sp_text_configure
sp_text_dump_database
sp_text_kill
sp_text_load_index
sp_text_notify
sp_text_online
Sample Files
Default textsvr.cfg Configuration File
The sample_text_main.sql Script
Sample Files Illustrating Full-Text Search Engine Features
Custom Thesaurus
Topics
Clustering, Summarization, and Query-by-Example
getsend Sample Program
Unicode Support
Glossary
Glossary
Java in Adaptive Server Enterprise
About this book
An Introduction to Java in the Database
Advantages of Java in the database
Capabilities of Java in the database
Invoking Java methods in the database
Invoking Java methods directly in SQL
Invoking Java methods as SQLJ stored procedures and functions
Storing Java classes as datatypes
Storing and querying XML in the database
Standards
Java in the database: questions and answers
What are the key features?
How can I store Java instructions in the database?
How is Java executed in the database?
Client- and server-side JDBC
How can I use Java and SQL together?
What is the Java API?
How can I access the Java API from SQL?
Which Java classes are supported in the Java API?
Can I install my own Java classes?
Can I access data using Java?
Can I use the same classes on client and server?
How do I use Java classes in SQL?
Where can I find information about Java in the database?
What you cannot do with Java in the database
Sample Java classes
Preparing for and Maintaining Java in the Database
The Java runtime environment
Java classes in the database
Sybase runtime Java classes
User-defined Java classes
JDBC drivers
The Java VM
Configuring memory for Java in the database
Enabling the server for Java
Disabling the server for Java
Creating Java classes and JARs
Writing the Java code
Compiling Java code
Saving classes in a JAR file
Installing uncompressed JARs
Installing compressed JARs
Installing Java classes in the database
Using installjava
Retaining the JAR file
Updating installed classes
Referencing other Java-SQL classes
Viewing information about installed classes and JARs
Downloading installed classes and JARs
Removing classes and JARs
Retaining classes
Using Java Classes in SQL
General concepts
Java considerations
Java-SQL names
Using Java classes as datatypes
Creating and altering tables with Java-SQL columns
Altering partitioned tables
Selecting, inserting, updating, and deleting Java objects
Invoking Java methods in SQL
Sample methods
Exceptions in Java-SQL methods
Representing Java instances
Assignment properties of Java-SQL data items
Datatype mapping between Java and SQL fields
Character sets for data and identifiers
Subtypes in Java-SQL data
Widening conversions
Narrowing conversions
Runtime versus compile-time datatypes
The treatment of nulls in Java-SQL data
References to fields and methods of null instances
Null values as arguments to Java-SQL methods
Null values when using the SQL convert function
Java-SQL string data
Zero-length strings
Type and void methods
Java void instance methods
Java void static methods
Equality and ordering operations
Evaluation order and Java method calls
Columns
Variables and parameters
Static variables in Java-SQL classes
Java classes in multiple databases
Scope
Cross-database references
Inter-class transfers
Passing inter-class arguments
Temporary and work databases
Java classes
Data Access Using JDBC
Overview
JDBC concepts and terminology
Differences between client- and server-side JDBC
Permissions
Using JDBC to access data
Overview of the JDBCExamples class
The main( ) and serverMain( ) methods
Using main( )
Using serverMain( )
Obtaining a JDBC connection: the Connecter( ) method
Routing the action to other methods: the doAction( ) method
Executing imperative SQL operations: the doSQL( ) method
Executing an update statement: the UpdateAction( ) method
Executing a select statement: the selectAction( ) method
Calling a SQL stored procedure: the callAction( ) method
Error handling in the native JDBC driver
The JDBCExamples class
The main( ) method
The internalMain( ) method
The connecter( ) method
The doAction( ) method
The doSQL( ) method
The updateAction( ) method
The selectAction( ) method
The callAction( ) method
SQLJ Functions and Stored Procedures
Overview
Compliance with SQLJ Part 1 specifications
General issues
Security and permissions
SQLJExamples
Invoking Java methods in Adaptive Server
Using Sybase Central to manage SQLJ functions and procedures
SQLJ user-defined functions
Handling null argument values
Handling nulls when creating the function
Handling nulls in the function call
Deleting a SQLJ function name
SQLJ stored procedures
Modifying SQL data
Using input and output parameters
Returning result sets
Deleting a SQLJ stored procedure name
Viewing information about SQLJ functions and procedures
Advanced topics
Mapping Java and SQL datatypes
Using the command main method
SQLJ and Sybase implementation: a comparison
SQLJExamples class
Introduction to XML in the Database
Introduction
Source code and javadoc
References
An overview of XML
A sample XML document
HTML display of Order data
XML document types
XSL: formatting XML information
Character sets and XML data
XML parsers
Selecting Data with XQL
Accessing the XML parser
Setting the CLASSPATH environment variable
Installing XQL in Adaptive Server
Converting a raw XML document to a parsed version
Inserting XML documents
Updating XML documents
Deleting XML documents
Memory requirements for running the query engine inside Adaptive Server
Using XQL
Query structures that affect performance
Examples
Other usages of the XQL package
com.sybase.xml.xql.XqlDriver syntax
Sample queries
Validating your document
Using XQL to develop standalone applications
Example standalone application
Example JDBC client
Example EJB example
XQL methods
Methods in com.sybase.xml.xql.Xql
parse(String xmlDoc)
parse(InputStream xml_document, boolean validate)
query(String query, String xmlDoc)
query(String query, InputStream xmlDoc)
query(String query, SybXmlStream xmlDoc)
query(String query, JXml jxml)
sybase.aseutils.SybXmlStream
com.sybase.xml.xql.store.SybMemXmlStream
com.sybase.xml.xql.store.SybFileXmlStream
setParser(String parserName)
reSetParser
Specialized XML Processing
The OrderXml class for order documents
OrderXml(String) constructor
OrderXml(date, customerid, server)
void order2Sql(String ordersTableName, String server)
static void createOrderTable (String ordersTableName, String server)
void setOrderElement (String elementName, String newValue)
String getItemElement (int itemNumber, String elementName)
void setItemElement (int itemNumber, String elementName, String newValue
String getItemAttribute (int itemNumber, elementName, attributeName)
void setItemAttribute (int itemNumber, elementName, attributeName, newValue)
void appendItem (newItemid, newItemName, newQuantity, newUnit)
void deleteItem(int itemNumber)
Storing XML documents
Mapping and storage
Advantages and disadvantages of storage options
Client or server considerations
Creating and populating SQL tables for order data
Tables for element storage
Tables for document and hybrid storage
Using the element storage technique
Composing order documents from SQL data
Generating an order on the client
Generating an order on the server
Translating data from an XML order into SQL
Translating the XML document on the client
Translating the XML document on the server
Using the document storage technique
Storing XML order documents in SQL columns
Inserting an order document from a client file
Inserting a generated order document on the server
Accessing the elements of stored XML order documents
Client access to order elements
Server access to order elements
Appending and deleting items in the XML document
Using the hybrid storage technique
XML for SQL Result Sets
The ResultSetXML class
ResultSetXml(String)
Constructor: ResultSetXml (query, cdataColumns, colNames, server)
ResultXml example
String toSqlScript (resultTableName, columnPrefix, goOption)
String getColumn(int rowNumber, int columnNumber)
String getColumn(int rowNumber, String columnName)
void setColumn (int rowNumber, int columnNumber, newValue)
void setColumn (int rowNumber, String columnName, newValue)
Boolean allString (int ColumnNumber, String compOp, String comparand)
Boolean someString (int columnNumber, String compOp, String comparand)
A customizable example for different result sets
The ResultSet document type
The XML DTD for the ResultSetXml document type
Using the element storage technique
Composing a ResultSet XML document from the SQL data
Generating a ResultSet in the client
Generating a result set in Adaptive Server
Translating the XML ResultSet document in the client
Translating the XML ResultSet Document in Adaptive Server
Using the document storage technique
Storing an XML ResultSet document in a SQL column
Accessing the columns of stored ResultSet documents
A client-side call
A server-side script
Quantified comparisons in stored ResultSet documents
Debugging Java in the Database
Introduction to debugging Java
How the debugger works
Requirements for using the Java debugger
What you can do with the debugger
Using the debugger
Starting the debugger and connecting to the database
Compiling classes for debugging
Attaching to a Java VM
The Source window
The debugger windows
Options
Setting breakpoints
Breaking on a line number
Breaking on a static method
Using counts with breakpoints
Using conditions with breakpoints
Breaking when execution is not interrupted
Disconnecting from the database
A debugging tutorial
Before you begin
Start the Java debugger and connect to the database
Attach to a Java VM
Load source code into the debugger
Step through source code
Examples
Inspecting and modifying variables
Inspecting local variables
Modifying local variables
Inspecting static variables
Network Access Using java.net
Overview
java.net classes
Setting up java.net
Example usage
Using socket classes
Saving text out of Adaptive Server
Using the URL class
Using the MailTo class URL to mail a document
Obtaining an HTTP document
Accessing an external document with XQL
User notes
Where to go for help
Reference Topics
Assignments
Assignment rules at compile-time
Assignment rules at runtime
Allowed conversions
Transferring Java-SQL objects to clients
Supported Java API packages, classes, and methods
Supported Java packages and classes
Unsupported Java packages and classes
Unsupported java.sql methods and interfaces
Invoking SQL from Java
Special considerations
Transact-SQL commands from Java methods
Datatype mapping between Java and SQL
Java-SQL identifiers
Java-SQL class and package names
Java-SQL column declarations
Java-SQL variable declarations
Java-SQL column references
Java-SQL member references
Java-SQL method calls
Glossary
Migrating to Adaptive Server Enterprise 12.5
Introduction and Guide to Resources
In This Chapter
Chapters in the Migration Guide
Relating Documentation to Migration Phase
Other Sources of Information
Sybase Certifications on the Web
Migration Web Page
Classes
Consulting Services
If You Need Help
Analyze: Documenting Business Requirements
In This Chapter
Diagramming the System
Operational Business Requirements
Availability Requirements
Database Change Metrics
Database Dump Details
Maintenance Procedures
Service Level Requirements
Transaction Profile
Current Performance Metrics
Additional Business Requirements
Analyze: Documenting Your Environment
In This Chapter
Hardware Configuration
General Server Hardware
CPU Resources per Machine
Disk Configuration
Network Configuration
Tape Configuration
Physical Memory Utilization
Software Configuration
Operating System
Applications
Sybase Configuration
General Information
Database Devices
Databases and Segments
Dump Devices
Adaptive Server Objects
Gather Scripts to Create Objects
If You Don’t Have Scripts
Query Sybase System Tables
Use System Stored Procedures
Use Sybase Tools
Plan: Writing a Plan and Getting Ready to Migrate
In this Chapter
Review Documentation
Review the Upgrade Process
Special Cases: Migrating to 64-bit Operating System or Larger Page Size
Determine Migration Approach
Parallel With Replication
Cutover Without Replication
Phased Cutover
Write a Migration Plan
Build the Adaptive Server Environment
Update Hardware Resources
Verify Operating System Version and EBF Level
Review Adaptive Server Interoperability with Other Sybase Products
Update Applications and System Administration Procedures
Create Migration Scripts
Implement: Making Required Application Changes
In This Chapter
If Your Version is 11.5
Changes to Locking
Data-only Locking
Changes to Table-Level Locking
Workarounds
Changes to Error Checking
Permissions on Objects in Procedures Checked at Run-time
Stored Procedure Existence Checks in Command Batches
Ordering of Integrity and Permissions Checks
Changes in Error Numbers
11.9.2 Query Processing Changes
New and Changed Statistics
Aggregate Optimization
Costing Large I/O
The optdiag Utility
Trace Flag 302
If Your Version is 11.5 or 11.9.x
ANSI Joins
dsync Function
12.0 Query Processing Changes
Predicate Transformation and Factoring
50 Tables in a Query
Abstract Query Plans
Increased Optimization Time
Like Optimization Enhancements
If Your Version is 11.5, 11.9.x, or 12.0
Changes to Transact-SQL
The enable xact coordination Configuration Parameter
New Reserved Words
Wide Columns and Data Truncation
Change in Truncation Behavior
Using New Wide Data Sizes with Open Client
Wide Columns and Optimizer Statistics
Wide Columns with col_length() and datalength()
Implement: Making Database Administration Changes
In This Chapter
If Your Version is 11.5
New Rollback Records
Optimizer Changes
Updating Statistics After Upgrade
Trace Flag 326
If Your Version is 11.5 or 11.9.x
Documentation Changes
Information Moved to Different Manual
Discontinued Documents
Backup Server
Stripes
Network Connections
File Descriptors
Non-Rewinding Tapes
Starting Backup Server
Dump Format
Suspending Updates to Databases for Copying
New Unix File System Device Support
Changes to dbids
CIS Proxy Databases
ASE Plug-in for Sybase Central
The sybsyntax Utility
If Your Version is 11.5, 11.9.x, or 12.0
Changes to Documentation
System Database Sizes
The sybsystemprocs Database
sybsystemdb
Changes to System Tables
BCP with syslogins
New Database and Table Limits
Maximum Number of Users and Logins
New Table Limits
New Reserved Words
Configuration Parameters
stack size
RPC Problem with “enable xact coordination”
Increased Memory
New Storage for text and image Data
buildmaster Command Obsolete
Test: Ensuring Stability and Performance
In This Chapter
The Goal of Testing
Setting Up the Test Environment
Make Backups
Use Scripts to Create the Test System
Create Your Databases by Loading Backups
If the Test Environment Is Not an Exact Duplicate
Prioritizing Applications to be Tested
Establishing Performance Criteria
Developing Fallback Procedures
Summary of Testing Techniques
Writing Performance Scripts
Write Benchmark Scripts
Drivers
General Error Handling
Deadlock Handling
Result Handling
Time Measurement
Runtime Data Generation
Test Cycle: Summary of Tests
Test Cycle: Testing for Performance
Pre-Upgrade Single-User Tests
Optimizer
I/O
Pre-Upgrade Multi-User Tests
Untimed Benchmarks
Timed Benchmarks
Test System Upgrade
Post-Upgrade Single-User Tests
Post-Upgrade Multi-User Tests
Untimed Benchmarks
Timed Benchmarks
Worksheets for Your Current Environment
Adaptive Server Operational Worksheets
Operational Business Requirements
Backup and Restore Procedures
Database Dump Details
Maintenance Procedure Details
Data Architecture Worksheet
Client Application Components
Production Performance Metrics
Transaction Profile
Adaptive Server Infrastructure Worksheets
Host Configuration
Hardware
Physical Memory Usage
Disk I/O Configuration
Network Configuration
Tape Configuration
Operating System Configuration
Adaptive Server Configuration
Database Devices
Databases and Segments
Dump Devices
Sample Migration Task Lists
Sample Task List Template
General Migration Task List Example
Migration Analysis
Document Current Configuration
Gather Business Requirements
Conduct Compatibility Analysis
Develop Migration Strategy
Migration Preparation
Write Test Plans and Test Scripts
Prepare Applications For Migration
Design and Develop Server Migration Scripts
Design and Develop Database Migration Scripts
Design and Develop Data Migration Scripts
Perform Other Pre-migration Tasks
Implement Migration (Using Install/Load Technique)
Create Target Environment
Perform Server Migration
Perform Database Migration
Perform Data Migration
Complete Server and Data Migration
Perform Application Migration
Implement Migration (Using Upgrade Technique)
Upgrade Adaptive Server
Complete Migration
Perform Application Migration
Migration Quality Assurance
Perform System Tests
Perform Integration Tests
Perform Stress Tests
Perform User Acceptance Tests
Perform Production Data Refresh
Parallel Migration Task List Example
Define Test/Acceptance Criteria—Regression Test Suites
Back-end regression test suite—production loads
Front-end simulation regression test suite
Front-end Phase 1 and 2 regression test suite
Set Up Target Production Environment
Set Up Replication Server
Run Regression Test Suites
Back-end regression test suite—production loads
Front-end simulation regression test suite
Front-end Phase 1 and 2 regression test suite
Upgrade Server B (Shadow)
Run Post-upgrade Regression Test Suites on ASE 12.5 (Server B)
Back-end regression test suite—production loads
Front-end simulation regression test suite
Front-End Phase 1 and 2 Regression Test Suite
Other Testing
Run User Acceptance Tests on ASE 12.5 (Server B)
Shift Production Users to Adaptive Server 12.5 (Server B)
Perform Final Steps
Cutover Migration Task List Example
Set Up ASE 12.5 Environment on Development System
Define Test/Acceptance Criteria—Regression Test Suites
Front-end simulation regression test suite
Front-end regression test suite
Define Fallback Procedures on Test System
“Baseline” Older Environment on Test System
Run Regression Test Suites on Older Release Test System
Front-end simulation regression test suite
Front-end Regression Test Suite
Upgrade Test System to Release 12.5
Run Regression Test Suites on Release 12.5 Test System
Back-End Regression Test Suite—Production Loads
Front-End Simulation Regression Test Suite
Front-End Regression Test Suite
Other Testing
Run User/Acceptance Tests on Release 12.5 Test System
Execute Fallback Procedures on Test System
Upgrade Production Server to ASE 12.5
Perform Final Steps
Staged Cutover Task Overview
Tasks
Migration Issue Checklists
Logical Data Architecture
Logical Application Architecture
Logical Technology Architecture
Logical Support Architecture
Migration Strategy Design
Pre-Upgrade Checklist
Pre-Upgrade Checklist
Monitor Client Library Programmer's Guide
About this book
Getting started with Monitor Client Library
Overview
What is Adaptive Server Enterprise Monitor
Adaptive Server Enterprise Monitor components
Adaptive Server Enterprise Monitor architecture
Writing a Basic Monitor Client Library program
Application logic flow
Step 1: define error handling
Step 2: connect to a server
Allocating a connection structure
Setting connection structure properties
Required connection properties
Connecting to a server
Step 3: create a view
Data items
Statistical types
Creating views for a connection
Step 4: create filters
Step 5: set alarms
Step 6: request performance data and process results
Step 7: close and deallocate connections
Closing and deallocating connections
Reopening connections
Playing back recorded data
A sample Monitor Client Library program
Example program
Data Items and Statistical Types
Overview
Result and key data items
Data items and views
Rows with no data versus no rows in views
Server-level status
Combining data items
Result and key combinations
Connection summaries
Current statement and application name data items
Data item definitions
Deciphering the names of data items
SMC_NAME_ACT_STP_DB_ID
SMC_NAME_ACT_STP_DB_NAME
SMC_NAME_ACT_STP_ID
SMC_NAME_ACT_STP_NAME
SMC_NAME_ACT_STP_OWNER_NAME
SMC_NAME_APPLICATION_NAME
SMC_NAME_APP_EXECUTION_CLASS
SMC_NAME_BLOCKING_SPID
SMC_NAME_CONNECT_TIME
SMC_NAME_CPU_BUSY_PCT
SMC_NAME_CPU_PCT
SMC_NAME_CPU_TIME
SMC_NAME_CPU_YIELD
SMC_NAME_CUR_APP_NAME
SMC_NAME_CUR_ENGINE
SMC_NAME_CUR_EXECUTION_CLASS
SMC_NAME_CUR_PROC_STATE
SMC_NAME_CUR_STMT_ACT_STP_DB_ID
SMC_NAME_CUR_STMT_ACT_STP_DB_NAME
SMC_NAME_CUR_STMT_ACT_STP_ID
SMC_NAME_CUR_STMT_ACT_STP_NAME
SMC_NAME_CUR_STMT_ACT_STP_OWNER_NAME
SMC_NAME_CUR_STMT_ACT_STP_TEXT
SMC_NAME_CUR_STMT_BATCH_ID
SMC_NAME_CUR_STMT_BATCH_TEXT
SMC_NAME_CUR_STMT_BATCH_TEXT_ENABLED
SMC_NAME_CUR_STMT_CONTEXT_ID
SMC_NAME_CUR_STMT_CPU_TIME
SMC_NAME_CUR_STMT_ELAPSED_TIME
SMC_NAME_CUR_STMT_LINE_NUM
SMC_NAME_CUR_STMT_LOCKS_GRANTED_IMMED
SMC_NAME_CUR_STMT_LOCKS_GRANTED_WAITED
SMC_NAME_CUR_STMT_LOCKS_NOT_GRANTED
SMC_NAME_CUR_STMT_NUM
SMC_NAME_CUR_STMT_PAGE_IO
SMC_NAME_CUR_STMT_PAGE_LOGICAL_READ
SMC_NAME_CUR_STMT_PAGE_PHYSICAL_READ
SMC_NAME_CUR_STMT_PAGE_WRITE
SMC_NAME_CUR_STMT_QUERY_PLAN_TEXT
SMC_NAME_CUR_STMT_START_TIME
SMC_NAME_CUR_STMT_TEXT_BYTE_OFFSET
SMC_NAME_DATA_CACHE_CONTENTION
SMC_NAME_DATA_CACHE_EFFICIENCY
SMC_NAME_DATA_CACHE_HIT
SMC_NAME_DATA_CACHE_HIT_PCT
SMC_NAME_DATA_CACHE_ID
SMC_NAME_DATA_CACHE_LARGE_IO_DENIED
SMC_NAME_DATA_CACHE_LARGE_IO_PERFORMED
SMC_NAME_DATA_CACHE_LARGE_IO_REQUESTED
SMC_NAME_DATA_CACHE_MISS
SMC_NAME_DATA_CACHE_NAME
SMC_NAME_DATA_CACHE_PREFETCH_EFFICIENCY
SMC_NAME_DATA_CACHE_REUSE
SMC_NAME_DATA_CACHE_REUSE_DIRTY
SMC_NAME_DATA_CACHE_REF_AND_REUSE
SMC_NAME_DATA_CACHE_SIZE
SMC_NAME_DB_ID
SMC_NAME_DB_NAME
SMC_NAME_DEADLOCK_CNT
SMC_NAME_DEMAND_LOCK
SMC_NAME_DEV_HIT
SMC_NAME_DEV_HIT_PCT
SMC_NAME_DEV_IO
SMC_NAME_DEV_MISS
SMC_NAME_DEV_NAME
SMC_NAME_DEV_READ
SMC_NAME_DEV_WRITE
SMC_NAME_ELAPSED_TIME
SMC_NAME_ENGINE_NUM
SMC_NAME_HOST_NAME
SMC_NAME_KPID
SMC_NAME_LOCK_CNT
SMC_NAME_LOCK_HIT_PCT
SMC_NAME_LOCK_RESULT
SMC_NAME_LOCK_RESULT_SUMMARY
SMC_NAME_LOCK_STATUS
SMC_NAME_LOCK_STATUS_CNT
SMC_NAME_LOCK_TYPE
SMC_NAME_LOCKS_BEING_BLOCKED_CNT
SMC_NAME_LOCKS_GRANTED_IMMED
SMC_NAME_LOCKS_GRANTED_WAITED
SMC_NAME_LOCKS_NOT_GRANTED
SMC_NAME_LOG_CONTENTION_PCT
SMC_NAME_LOGIN_NAME
SMC_NAME_MEM_CODE_SIZE
SMC_NAME_MEM_KERNEL_STRUCT_SIZE
SMC_NAME_MEM_PAGE_CACHE_SIZE
SMC_NAME_MEM_PROC_BUFFER
SMC_NAME_MEM_PROC_HEADER
SMC_NAME_MEM_SERVER_STRUCT_SIZE
SMC_NAME_MOST_ACT_DEV_IO
SMC_NAME_MOST_ACT_DEV_NAME
SMC_NAME_NET_BYTE_IO
SMC_NAME_NET_BYTES_RCVD
SMC_NAME_NET_BYTES_SENT
SMC_NAME_NET_DEFAULT_PKT_SIZE
SMC_NAME_NET_MAX_PKT_SIZE
SMC_NAME_NET_PKT_SIZE_RCVD
SMC_NAME_NET_PKT_SIZE_SENT
SMC_NAME_NET_PKTS_RCVD
SMC_NAME_NET_PKTS_SENT
SMC_NAME_NUM_ENGINES
SMC_NAME_NUM_PROCESSES
SMC_NAME_OBJ_ID
SMC_NAME_OBJ_NAME
SMC_NAME_OBJ_TYPE
SMC_NAME_OWNER_NAME
SMC_NAME_PAGE_HIT_PCT
SMC_NAME_PAGE_INDEX_LOGICAL_READ
SMC_NAME_PAGE_INDEX_PHYSICAL_READ
SMC_NAME_PAGE_IO
SMC_NAME_PAGE_LOGICAL_READ
SMC_NAME_PAGE_NUM
SMC_NAME_PAGE_PHYSICAL_READ
SMC_NAME_PAGE_WRITE
SMC_NAME_PROC_STATE
SMC_NAME_PROC_STATE_CNT
SMC_NAME_SPID
SMC_NAME_SQL_SERVER_NAME
SMC_NAME_SQL_SERVER_VERSION
SMC_NAME_STP_CPU_TIME
SMC_NAME_STP_ELAPSED_TIME
SMC_NAME_STP_EXECUTION_CLASS
SMC_NAME_STP_HIT_PCT
SMC_NAME_STP_LINE_NUM
SMC_NAME_STP_LINE_TEXT
SMC_NAME_STP_LOGICAL_READ
SMC_NAME_STP_NUM_TIMES_EXECUTED
SMC_NAME_STP_PHYSICAL_READ
SMC_NAME_STP_STMT_NUM
SMC_NAME_THREAD_EXCEEDED_MAX
SMC_NAME_THREAD_EXCEEDED_MAX_PCT
SMC_NAME_THREAD_MAX_USED
SMC_NAME_TIME_WAITED_ON_LOCK
SMC_NAME_TIMESTAMP
SMC_NAME_TIMESTAMP_DATIM
SMC_NAME_XACT
SMC_NAME_XACT_DELETE
SMC_NAME_XACT_DELETE_DEFERRED
SMC_NAME_XACT_DELETE_DIRECT
SMC_NAME_XACT_INSERT
SMC_NAME_XACT_INSERT_CLUSTERED
SMC_NAME_XACT_INSERT_HEAP
SMC_NAME_XACT_SELECT
SMC_NAME_XACT_UPDATE
SMC_NAME_XACT_UPDATE_DEFERRED
SMC_NAME_XACT_UPDATE_DIRECT
SMC_NAME_XACT_UPDATE_EXPENSIVE
SMC_NAME_XACT_UPDATE_IN_PLACE
SMC_NAME_XACT_UPDATE_NOT_IN_PLACE
Monitor Client Library Functions
Threads
Error handling
Error handler
Callback function
smc_close
smc_connect_alloc
smc_connect_drop
smc_connect_ex
smc_connect_props
smc_create_alarm_ex
smc_create_filter
smc_create_playback_session
smc_create_recording_session
smc_create_view
smc_drop_alarm
smc_drop_filter
smc_drop_view
smc_get_command_info
smc_get_dataitem_type
smc_get_dataitem_value
smc_get_row_count
smc_get_version_string
smc_initiate_playback
smc_initiate_recording
smc_refresh_ex
smc_terminate_playback
smc_terminate_recording
Building a Monitor Client Library Application
Building on UNIX platforms
Compiling the application
Linking the application
Running the application
Building the sample applications
Building on Windows platforms
Compiling the application
Linking the application
Running the application
Building the sample applications
Monitor Client Library Configuration Instructions
Loading Monitor Client Library
Using Studio Installer
Results of the load
Confirming your login account and permissions
Modifying the interfaces file
Setting up the user environment
Setting the SYBASE environment variable
Overriding the default location of the interfaces file
Using Monitor Client Library
Examples of Views
Cache performance summary
Current statement summary
Database object lock status
Database object page I/O
Data cache activity for individual caches
Data cache statistics for session
Data cache statistics for sample interval
Device I/O for session
Device I/O for sample interval
Device I/O performance summary
Engine activity
Lock performance summary
Network activity for session
Network activity for sample interval
Network performance summary
Procedure cache statistics for session
Procedure cache statistics for sample interval
Procedure page I/O
Process activity
Process database object page I/O
Process detail for locks
Process detail page I/O
Process locks
Process page I/O
Process state summary
Process stored procedure page I/O
Server performance summary
Stored procedure activity
Transaction activity
Datatypes and Structures
Summary of datatypes
Enum: SMC_ALARM_ACTION_TYPE
Enum: SMC_CLOSE_TYPE
Enum: SMC_DATAITEM_NAME
Enum: SMC_DATAITEM_STATTYPE
Structure: SMC_DATAITEM_STRUCT
Enum: SMC_DATAITEM_TYPE
Enum: SMC_ERR_SEVERITY
Enum: SMC_FILTER_TYPE
Enum: SMC_HS_ESTIM_OPT
Enum: SMC_HS_MISSDATA_OPT
Enum: SMC_HS_PLAYBACK_OPT
Enum: SMC_HS_SESS_DELETE_OPT
Enum: SMC_HS_SESS_ERR_OPT
Enum: SMC_HS_SESS_PROT_LEVEL
Enum: SMC_HS_SESS_SCRIPT_OPT
Enum: SMC_HS_TARGET_OPT
Enum: SMC_HS_TARGET_OPT
Enum: SMC_INFO_TYPE
Enum: SMC_LOCK_RESULT
Enum: SMC_LOCK_RESULT_SUMMARY
Enum: SMC_LOCK_STATUS
Enum: SMC_LOCK_TYPE
Enum: SMC_OBJ_TYPE
Enum: SMC_PROC_STATE
Enum: SMC_PROP_ACTION
Enum: SMC_PROP_TYPE
Enum: SMC_RETURN_CODE
Enum: SMC_SERVER_MODE
Enum: SMC_SOURCE
Union: SMC_VALUE_UNION
Backward Compatibility
Obsolete and replacement functions
New functions
Rules for functions and callbacks compatibility
Troubleshooting Information and Error Messages
Troubleshooting
Error messages
Communication failure: check if server is running.
Configuration failure: possibly missing interfaces file or bad login parameters.
Don’t know how to build example.h
error L2029: ‘SMC_CONNECT’ : unresolved external
error L2029: ‘SMC_CREATE_VIEW’ : unresolved external
fatal error C1083: Cannot open include file: ‘cstypes.h’: No such file or directory
fatal error C1083: Cannot open include file: ‘mcpublic.h’: No such file or directory
LINK: fatal error L4051: smcapi32.lib : cannot find library
Monitor Historical Server User's Guide
About this book
Introduction
Description of Adaptive Server Enterprise Monitor
Adaptive Server Monitor components
Adaptive Server Monitor architecture
Adaptive Server Enterprise Monitor architecture
Historical Server concepts
Recording sessions
Playback sessions
Views
Data items and statistic types
Configuring Historical Server
Historical Server configuration concepts
The Historical Server control file and home directory
Accessing control file information
Do not edit the control file
The operating system start-up account
The Historical Server superuser account
Sybase Open Client/Server connections
Initial configuration on UNIX platforms
Assumptions on UNIX platforms
Results of installation on UNIX platforms
Configuration procedures on UNIX platforms
Initial configuration on Windows NT
Assumptions on Windows NT
Results of installation on Windows NT
Configuration procedures on Windows NT
Changing start-up parameter
Setting Historical Server start-up parameters
Function
Syntax
Parameters
Configuring multiple instances of Historical Server
When to create multiple instances of Historical Server
Configuring an additional Historical Server on UNIX platforms
Configuring an additional Historical Server on Windows NT
Task 1: add start-up parameters to the NT Registry
Task 2: update the NT Registry services list
Task 3: add connectivity information for Historical Server
Task 4: configure Historical Server client machines
Task 5: create a .bat file (optional)
Starting and Stopping Historical Server
Starting and stopping Historical Server on UNIX platforms
Starting Historical Server on UNIX
Stopping Historical Server on UNIX
Who can shut down Historical Server
Determining current activity on Historical Server
Deferred versus immediate shutdown
Detailed shutdown procedures
Starting and stopping Historical Server on NT
Starting Historical Server on NT
Inferring start-up parameters from the NT Registry
Verifying that Historical Server is running
Stopping Historical Server on Windows NT
Who can shut down Historical Server on Windows NT
Determining current activity on Windows NT
Deferred versus immediate shutdown on Windows NT
Detailed shutdown procedures on Windows NT
Shutdown methods to avoid on Windows NT
Command Reference
Command summary
Command syntax
Command status and errors
Script files as input to Historical Server
Connecting to Historical Server
Assumptions before connection
How to connect
Required permissions for Historical Server activities
Mutually exclusive sessions
Starting and ending a recording session definition
Starting and ending a playback session
Historical Server commands
hs_create_alarm
hs_create_filter
hs_create_playback_session
hs_create_playback_view
hs_create_recording_session
hs_create_view
hs_delete_data
hs_initiate_playback
hs_initiate_recording
hs_list
hs_playback_sample
hs_shutdown
hs_status
hs_terminate_playback
hs_terminate_recording
Data Files
Overview of Historical Server data files
Description of Historical Server files
Permissions on files
General file format
Control file
Header record
Session control record
View control record
Data item control record
Alarm control record
Filter control record
Data file
Error message file
Script file
Script file table names
Script file table column names
Passing script file commands
Script use example
Bulk copy example
Explanation
Example
Cut utility example
Data Items
Table of data items and definitions
Specifications for Defining Recording Session Views
Definition of key and result
Designing recording session views
Using the Process ID
Using the application name
Empty rows versus no rows in views
Table of valid key and result data item combinations
Examples of valid combinations
Examples of invalid combinations
Table of valid statistic types for data items
Specifications for Defining Playback Views
Summarization level details
Raw playback
Actual playback
Entire playback
Playback with user-defined intervals
Summary of summarization intervals
Designing playback views
Rules for specifying input sessions
Relationship of input views to playback views
Rules for defining views
Rules for defining raw playback views
Rules for defining non-raw playback views
Table of data item requirements for playback views
Additional information about some data items
Using “Timestamp”, “Timestamp Datim”, and “Elapsed Time”
Using Process ID
Using Procedure Elapsed Time and Procedure CPU Time
Examples of Recording Session Views
Cache performance summary
Database object lock status
Database object page I/O
Data cache activity for individual caches
Data cache statistics for recording session
Data cache statistics for sample interval
Device I/O for recording session
Device I/O for sample interval
Device I/O performance summary
Engine activity
Lock performance summary
Network activity for recording session
Network activity for sample interval
Network performance summary
Page I/O
Procedure cache statistics for recording session
Procedure cache statistics for sample interval
Procedure page I/O
Process activity
Process database object page I/O
Process detail for locks
Process detail page I/O
Process locks
Process page I/O
Process state summary
Process stored procedure page I/O
Server performance summary
Stored procedure activity
Transaction activity
Monitor Server User's Guide
About this book
Introduction
Description of Adaptive Server Enterprise Monitor
Monitor components
Monitor architecture
Interaction with sp_sysmon
Configuring Monitor Server
Monitoring SQL Server version 11.0.x
Initial configuration on UNIX platforms
Assumptions on UNIX platforms
Results of installation on UNIX platforms
Configuration procedures on UNIX platforms
Initial configuration on Microsoft Windows NT
Assumptions on Windows NT
Results of installation on Windows NT
Configuration procedures on Windows NT
Setting up the automatic start-up service
Setting up the server start-up order
Changing the default configuration
Changing start-up information in the NT Registry
Changing the .bat file
Configuring another Monitor Server
Monitor Server start-up parameters
Function
Syntax
Parameters
Adjusting Monitor Server runtime configuration
Monitor Server configuration file
Initial scan interval
Heartbeat interval
Configuring Monitor Server heap space usage
Summary buffer size
Number of summary buffers per event summary request
Maximum number of event summaries per connection
Adaptive Server configuration issues that affect Monitor Server
Restrictions on length of Adaptive Server name
Configuring event buffers
Understanding event buffers and event loss
Determining a value for event buffers per engine parameter
Changing the event buffers per engine parameter
Configuring SQL text buffers
Understanding the SQL text feature
Determining a value for the max SQL text monitored parameter
Changing the max SQL text monitored Parameter
Starting, Verifying, and Stopping Monitor Server
Starting, verifying, and stopping Monitor Server on UNIX platforms
Starting Monitor Server on UNIX
Verifying that Monitor Server is running on UNIX
Stopping Monitor Server on UNIX
Starting, verifying, and stopping Monitor Server on Windows NT
Starting Monitor Server on Windows NT
How start-up parameters are inferred on Windows NT
Verifying that Monitor Server is running on Windows NT
Stopping Monitor Server on Windows NT
Orderly shutdowns and restarts
Orderly shutdowns
Automatic shutdowns
Orderly restarts
Connecting to clients
Monitor Server isql commands
Using the commands
sms_shutdown
sms_status
Troubleshooting Monitor Server
Error messages
Common problems
Monitor Server fails to start
Access violations
Check the NT Registry services entry (NT only)
Client errors
Maximum connection errors
ct_netlib errors (Windows NT)
Messages in Adaptive Server error log
Performance and Tuning Guide
About this book
Overview
Good performance
Response time
Throughput
Designing for performance
Tuning performance
Tuning levels
Application layer
Database layer
Adaptive Server layer
Devices layer
Network layer
Hardware layer
Operating – system layer
Identifying system limits
Setting tuning goals
Analyzing performance
Normal Forms
Locking
Special Considerations
Networks and Performance
Introduction
Potential performance problems
Basic questions on network performance
Techniques summary
Using sp_sysmon while changing network configuration
How Adaptive Server uses the network
Changing network packet sizes
Large versus default packet sizes for user connections
Number of packets is important
Evaluation tools with Adaptive Server
Evaluation tools outside of Adaptive Server
Server-based techniques for reducing network traffic
Impact of other server activities
Single user versus multiple users
Improving network performance
Isolate heavy network users
Set tcp no delay on TCP networks
Configure multiple network listeners
Using Engines and CPUs
Background concepts
How Adaptive Server processes client requests
Client task implementation
Single-CPU process model
Scheduling engines to the CPU
Scheduling tasks to the engine
Execution task scheduling
Scheduling client task processing time
Maintaining CPU availability during idle time
Adaptive Server SMP process model
Scheduling engines to CPUs
Scheduling Adaptive Server tasks to engines
Multiple network engines
Task priorities and run queues
Processing scenario
Housekeeper task improves CPU utilization
Side effects of the housekeeper task
Configuring the housekeeper task
Changing the percentage by which writes can be increased
Disabling the housekeeper task
Allowing the housekeeper task to work continuously
Measuring CPU usage
Single-CPU machines
Using sp_monitor to measure CPU usage
Using sp_sysmon to measure CPU usage
Operating - system commands and CPU usage
Determining when to configure additional engines
Taking engines offline
Enabling engine-to-CPU affinity
Multiprocessor application design guidelines
Distributing Engine Resources
Algorithm for successfully distributing engine resources
Algorithm guidelines
Environment analysis and planning
Analyzing
Example: phase 1 – execution object behavior
Example: phase 2 – the environment as a whole
Performing benchmark tests
Setting goals
Results analysis and tuning
Monitoring the environment over time
Manage preferred access to resources
Types of execution classes
Predefined execution classes
User-Defined execution classes
Execution class attributes
Base priority
Time slice
Task-to-engine affinity
Setting execution class attributes
Assigning execution classes
Engine groups and establishing task-to-engine affinity
How execution class bindings affect scheduling
Execution class bindings
Engine affinity can affect scheduling
Setting attributes for a session only
Getting information
Rules for determining precedence and scope
Multiple execution objects and ECs
Precedence rule
Scope rule
Resolving a precedence conflict
Examples: determining precedence
Example scenario using precedence rules
Planning
Configuration
Execution characteristics
Considerations for Engine Resource Distribution
Client applications: OLTP and DSS
Unintrusive client applications
I/O-bound client applications
Highly critical applications
Adaptive Server logins: high-priority users
Stored procedures: “hot spots”
Controlling Physical Data Placement
Object placement can improve performance
Symptoms of poor object placement
Underlying problems
Using sp_sysmon while changing data placement
Terminology and concepts
Guidelines for improving I/O performance
Spreading data across disks to avoid I/O contention
Avoiding physical contention in parallel join queries
Isolating server-wide I/O from database I/O
Where to place tempdb
Where to place sybsecurity
Keeping transaction logs on a separate disk
Mirroring a device on a separate disk
Device mirroring performance issues
Using serial mode
Creating objects on segments
Using segments
Separating tables and indexes
Splitting large tables across devices
Moving text storage to a separate device
Partitioning tables for performance
User transparency
Partitioned tables and parallel query processing
Distributing data across partitions
Improving insert performance with partitions
How partitions address page contention
Selecting heap tables to partition
Restrictions on partitioned tables
Partition-related configuration parameters
How Adaptive Server distributes partitions on devices
RAID devices and partitioned tables
Space planning for partitioned tables
Read-only tables
Read-mostly tables
Tables with random data modification
Commands for partitioning tables
alter table...partition syntax
alter table...unpartition Syntax
Changing the number of partitions
Distributing data evenly across partitions
Commands to create and drop clustered indexes
Using reorg rebuild on data-only-locked tables
Using drop index and create clustered index
Using constraints and alter table
Special concerns for partitioned tables and clustered indexes
Using parallel bcp to copy data into partitions
Parallel copy and locks
Getting information about partitions
Using bcp to correct partition balance
Checking data distribution on devices with sp_helpsegment
Effects of imbalance of data on segments and partitions
Determining the number of pages in a partition
Updating partition statistics
Syntax for update partition statistics
Steps for partitioning tables
Backing up the database after partitioning tables
Table does not exist
Table exists elsewhere in the database
Table exists on the segment
Redistributing data
Adding devices to a segment
Special procedures for difficult situations
Clustered indexes on large tables
Alternative for clustered indexes
Problems when devices for partitioned tables are full
Adding disks when devices are full
Adding disks when devices are nearly full
Maintenance issues and partitioned tables
Regular maintenance checks for partitioned tables
Database Design
Basic design
Physical database design for Adaptive Server
Logical Page Sizes
Normalization
Levels of normalization
Benefits of normalization
First Normal Form
Second Normal Form
Third Normal Form
Denormalizing for performance
Risks
Disadvantages
Performance advantages
Denormalization input
Techniques
Adding redundant columns
Adding derived columns
Collapsing tables
Duplicating tables
Splitting tables
Horizontal splitting
Vertical splitting
Managing denormalized data
Using triggers
Using application logic
Batch reconciliation
Data Storage
Performance gains through query optimization
Query processing and page reads
Adaptive Server pages
Page headers and page sizes
Varying logical page sizes
Data and index pages
Large Object (LOB) Pages
Extents
Pages that manage space allocation
Global allocation map pages
Allocation pages
Object allocation map pages
How OAM pages and allocation pages manage object storage
Page allocation keeps an object’s pages together
sysindexes table and data access
Space overheads
Number of columns and size
Variable-length columns in APL tables
Variable length columns in DOL tables
Number of rows per data page
Maximum numbers
Arguments for stored procedures
Retrieving data with enhanced limits
Heaps of data: tables without clustered indexes
Lock schemes and differences between heaps
Select operations on heaps
Allpages-locked heap tables
Data-only locked heap tables
Inserting data into an allpages-locked heap table
Conflicts during heap inserts
Inserting data into a data-only-locked heap table
If conflicts occur during heap inserts
Deleting data from a heap table
Deleting from an allpages-locked heap table
Deleting from a data-only locked heap table
Deleting the last row on a page
Updating data on a heap table
Allpages-locked heap tables
Data-only-locked heap tables
How Adaptive Server performs I/O for heap operations
Sequential prefetch, or large I/O
Caches and object bindings
Heaps, I/O, and cache strategies
Overview of cache strategies
LRU replacement strategy
When LRU strategy is used
MRU replacement strategy
Select operations and caching
Data modification and caching
Caching and inserts on heaps
Caching, update and delete operations on heaps
Asynchronous prefetch and I/O on heap tables
Heaps: pros and cons
Maintaining heaps
Methods
Using reorg rebuild to reclaim space
Reclaiming space by creating a clustered index
Reclaiming space using bcp
Transaction log: a special heap table
Indexing for Performance
How indexes affect performance
Detecting indexing problems
Symptoms of poor indexing
Lack of indexes is causing table scans
Index is not selective enough
Index does not support range queries
Too many indexes slow data modification
Index entries are too large
Exception for wide data rows and wide index rows
Fixing corrupted indexes
Repairing the system table index
Repairing a nonclustered index
Index limits and requirements
Choosing indexes
Index keys and logical keys
Guidelines for clustered indexes
Choosing clustered indexes
Candidates for nonclustered indexes
Other indexing guidelines
Choosing nonclustered indexes
Performance price for data modification
Choosing composite indexes
Key order and performance in composite indexes
Advantages and disadvantages of composite indexes
Techniques for choosing indexes
Choosing an index for a range query
Adding a point query with different indexing requirements
Index and statistics maintenance
Dropping indexes that hurt performance
Choosing space management properties for indexes
Additional indexing tips
Creating artificial columns
Keeping index entries short and avoiding overhead
Dropping and rebuilding indexes
How Indexes Work
Types of indexes
Index pages
Root level
Leaf level
Intermediate level
Index Size
Clustered indexes on allpages-locked tables
Clustered indexes and select operations
Clustered indexes and insert operations
Page splitting on full data pages
Exceptions to page splitting
Page splitting on index pages
Performance impacts of page splitting
Overflow pages
Clustered indexes and delete operations
Deleting the last row on a page
Index page merges
Nonclustered indexes
Leaf pages revisited
Nonclustered index structure
Nonclustered indexes and select operations
Nonclustered index performance
Nonclustered indexes and insert operations
Nonclustered indexes and delete operations
Clustered indexes on data-only-locked tables
Index covering
Covering matching index scans
Covering nonmatching index scans
Indexes and caching
Using separate caches for data and index pages
Index trips through the cache
Locking in Adaptive Server
How locking affects performance
Overview of locking
Granularity of locks and locking schemes
Allpages locking
Datapages locking
Datarows locking
Types of locks in Adaptive Server
Page and row locks
Table locks
Demand locks
Demand locking with serial execution
Demand locking with parallel execution
Range locking for serializable reads
Latches
Lock compatibility and lock sufficiency
How isolation levels affect locking
Isolation Level 0, read uncommitted
Isolation Level 1, read committed
Isolation Level 2, repeatable read
Isolation Level 3, serializable reads
Adaptive Server default isolation level
Lock types and duration during query processing
Lock types during create index commands
Locking for select queries at isolation Level 1
Table scans and isolation Levels 2 and 3
Table scans and table locks at isolation Level 3
Isolation Level 2 and Allpages-Locked tables
When update locks are not required
Locking during or processing
Processing or queries for Allpages-Locked tables
Processing or queries for Data-Only-Locked tables
Skipping uncommitted inserts during selects
Pseudo column-level locking
Select queries that do not reference the updated column
Using alternative predicates to skip nonqualifying rows
Qualifying old and new values for uncommitted updates
Suggestions to reduce contention
Using Locking Commands
Specifying the locking scheme for a table
Specifying a server-wide locking scheme
Specifying a locking scheme with create table
Changing a locking scheme with alter table
Before and after changing locking schemes
After alter table completes
Expense of switching to or from allpages locking
Sort performance during alter table
Specifying a locking scheme with select into
Controlling isolation levels
Setting isolation levels for a session
Syntax for query-level and table-level locking options
Using holdlock, noholdlock, or shared
Using the at isolation clause
Making locks more restrictive
Using read committed
Making locks less restrictive
Using read uncommitted
Using shared
Readpast locking
Cursors and locking
Using the shared keyword
Additional locking commands
lock table Command
Lock timeouts
Reporting on Locks
Locking tools
Getting information about blocked processes
Viewing locks
Viewing locks
Intrafamily blocking during network buffer merges
Deadlocks and concurrency
Server-side versus application-side deadlocks
Application deadlock example
Server task deadlocks
Deadlocks and parallel queries
Printing deadlock information to the error log
Avoiding deadlocks
Acquire locks on objects in the same order
Delaying deadlock checking
Identifying tables where concurrency is a problem
Lock management reporting
Locking Configuration and Tuning
Locking and performance
Using sp_sysmon and sp_object_stats
Reducing lock contention
Adding indexes to reduce contention
Keeping transactions short
Avoiding hot spots
Additional locking guidelines
Configuring locks and lock promotion thresholds
Configuring Adaptive Server’s lock limit
Estimating number of locks for data-only-locked tables
Configuring the lock hashtable (Lock Manager)
Setting lock promotion thresholds
Lock promotion and scan sessions
Lock promotion high water mark
Lock promotion low water mark
Lock promotion percent
Setting server-wide lock promotion thresholds
Setting the lock promotion threshold for a table or database
Precedence of settings
Dropping database and table settings
Using sp_sysmon while tuning lock promotion thresholds
Choosing the locking scheme for a table
Analyzing existing applications
Choosing a locking scheme based on contention statistics
Monitoring and managing tables after conversion
Applications not likely to benefit from data-only locking
Tables where clustered index performance must remain high
Tables with maximum-length rows
Setting Space Management Properties
Reducing index maintenance
Advantages of using fillfactor
Disadvantages of using fillfactor
Setting fillfactor values
fillfactor examples
No stored fillfactor values
Table-level or clustered index fillfactor value stored
Use of the sorted_data and fillfactor options
Reducing row forwarding
Default, minimum, and maximum values for exp_row_size
Default value
Specifying an expected row size with create table
Adding or changing an expected row size
Setting a default expected row size server-wide
Displaying the expected row size for a table
Choosing an expected row size for a table
Using optdiag to check for forwarded rows
Querying systabstats to check for forwarded rows
Conversion of max_rows_per_page to exp_row_size
Monitoring and managing tables that use expected row size
Leaving space for forwarded rows and inserts
Extent allocation operations and reservepagegap
Specifying a reserve page gap with create table
Specifying a reserve page gap with create index
Changing reservepagegap
reservepagegap examples
reservepagegap specified only for the table
reservepagegap specified for a clustered index
Choosing a value for reservepagegap
Monitoring reservepagegap settings
reservepagegap and sorted_data options to create index
Background on the sorted_data option
Matching options and goals
Using max_rows_per_page on allpages-locked tables
Reducing lock contention
Indexes and max_rows_per_page
select into and max_rows_per_page
Applying max_rows_per_page to existing data
Memory Use and Performance
How memory affects performance
How much memory to configure
Caches in Adaptive Server
Procedure cache
Getting information about the procedure cache size
proc buffers
proc headers
Monitoring procedure cache performance
Procedure cache errors
Procedure cache sizing
Estimating stored procedure size
Data cache
Default cache at installation time
Page aging in data cache
Effect of data cache on retrievals
Effect of data modifications on the cache
Data cache performance
Testing data cache performance
Cache hit ratio for a single query
Cache hit ratio information from sp_sysmon
Configuring the data cache to improve performance
Commands to configure named data caches
Tuning named caches
Cache configuration goals
Gather data, plan, and then implement
Evaluating cache needs
Large I/O and performance
The optimizer and cache choices
Choosing the right mix of I/O sizes for a cache
Reducing spinlock contention with cache partitions
Cache replacement strategies and policies
Strategies
Policies
Named data cache recommendations
Sizing caches for special objects, tempdb, and transaction logs
Determining cache sizes for special tables or indexes
Examining cache needs for tempdb
Examining cache needs for transaction logs
Choosing the I/O size for the transaction log
Configuring for large log I/O size
Additional tuning tips for log caches
Basing data pool sizes on query plans and I/O
Checking I/O size for queries
Configuring buffer wash size
Overhead of pool configuration and binding objects
Pool configuration overhead
Cache binding overhead
Maintaining data cache performance for large I/O
Diagnosing excessive I/O Counts
Using sp_sysmon to check large I/O performance
Speed of recovery
Tuning the recovery interval
Effects of the housekeeper task on recovery time
Auditing and performance
Sizing the audit queue
Auditing performance guidelines
Determining Sizes of Tables and Indexes
Why object sizes are important to query tuning
Tools for determining the sizes of tables and indexes
Effects of data modifications on object sizes
Using optdiag to display object sizes
Advantages of optdiag
Disadvantages of optdiag
Using sp_spaceused to display object size
Advantages of sp_spaceused
Disadvantages of sp_spaceused
Using sp_estspace to estimate object size
Advantages of sp_estspace
Disadvantages of sp_estspace
Using formulas to estimate object size
Factors that can affect storage size
Storage sizes for datatypes
Tables and indexes used in the formulas
Calculating table and clustered index sizes for allpages-locked tables
Step 1: Calculate the data row size
Step 2: Compute the number of data pages
Step 3: Compute the size of clustered index rows
Step 4: Compute the number of clustered index pages
Step 5: Compute the total number of index pages
Step 6: Calculate allocation overhead and total pages
Step 7: Calculate the size of the leaf index row
Step 8: Calculate the number of leaf pages in the index
Step 9: Calculate the size of the non-leaf rows
Step 10: Calculate the number of non-leaf pages
Step 11: Calculate the total number of non-leaf index pages
Step 12: Calculate allocation overhead and total pages
Calculating the sizes of data-only-locked tables
Step 1: Calculate the data row size
Step 2: Compute the number of data pages
Step 3: Calculate allocation overhead and total pages
Step 4: Calculate the size of the index row
Step 5: Calculate the number of leaf pages in the index
Step 6: Calculate the number of non-leaf pages in the index
Step 7: Calculate the total number of non-leaf index pages
Step 8: Calculate allocation overhead and total pages
Other factors affecting object size
Effects of space management properties
Using average sizes for variable fields
Very small rows
LOB pages
Advantages of using formulas to estimate object size
Disadvantages of using formulas to estimate object size
Maintenance Activities and Performance
Running reorg on tables and indexes
Creating and maintaining indexes
Configuring Adaptive Server to speed sorting
Dumping the database after creating an index
Creating an index on sorted data
Maintaining index and column statistics
Rebuilding indexes
Speeding index creation with sorted_data
Creating or altering a database
Backup and recovery
Local backups
Remote backups
Online backups
Using thresholds to prevent running out of log space
Minimizing recovery time
Recovery order
Bulk copy
Parallel bulk copy
Batches and bulk copy
Slow bulk copy
Improving bulk copy performance
Replacing the data in a large table
Adding large amounts of data to a table
Using partitions and multiple bulk copy processes
Impacts on other users
Database consistency checker
Using dbcc tune (cleanup)
Using dbcc tune on spinlocks
When not to use this command
Determining the space available for maintenance activities
Overview of space requirements
Tools for checking space usage and space available
Checking space used for tables and indexes
Checking space on segments
Checking space requirements for space management properties
Space management properties applied to the table
Space management properties applied to the index
Estimating the effects of space management properties
If there is not enough space
tempdb Performance Issues
How management of tempdb affects performance
Main solution areas for tempdb performance
Types and uses of temporary tables
Truly temporary tables
Regular user tables
Worktables
Initial allocation of tempdb
Sizing the tempdb
Placing tempdb
Dropping the master device from tempdb segments
Using multiple disks for parallel query performance
Binding tempdb to its own cache
Commands for cache binding
Temporary tables and locking
Minimizing logging in tempdb
With select into
By using shorter rows
Optimizing temporary tables
Creating indexes on temporary tables
Creating nested procedures with temporary tables
Breaking tempdb uses into multiple procedures
Adaptive Server Optimizer
Definition
Steps in query processing
Working with the optimizer
Object sizes are important to query tuning
Query optimization
Factors examined during optimization
Preprocessing can add clauses for optimizing
Converting clauses to search argument equivalents
Converting expressions into search arguments
Search argument transitive closure
Join transitive closure
Enabling join transitive closure
Predicate transformation and factoring
Example
Guidelines for creating search arguments
Search arguments and useful indexes
Search argument syntax
Nonequality operators
Examples of SARGs
How statistics are used for SARGS
Histogram cells
Density values
Range cell density and total density
How the optimizer uses densities and histograms
Using statistics on multiple search arguments
Default values for search arguments
SARGs using variables and parameters
Join syntax and join processing
How joins are processed
When statistics are not available for joins
Density values and joins
Multiple column joins
Search arguments and joins on a table
Datatype mismatches and query optimization
Overview of the datatype hierarchy and index issues
Comparison of numeric and decimal datatypes
Comparing numeric types to other datatypes
Datatypes for parameters and variables used as SARGs
Troubleshooting datatype mismatch problems fo SARGs
Compatible datatypes for join columns
Troubleshooting datatype mismatch problems for joins
Suggestions on datatypes and comparisons
Forcing a conversion to the other side of a join
Splitting stored procedures to improve costing
Basic units of costing
Advanced Optimizing Tools
Special optimizing techniques
Specifying optimizer choices
Specifying table order in joins
Risks of using forceplan
Things to try before using forceplan
Specifying the number of tables considered by the optimizer
Specifying an index for a query
Risks
Things to try before specifying an index
Specifying I/O size in a query
Index type and large I/O
When prefetch specification is not followed
set prefetch on
Specifying the cache strategy
In select, delete, and update statements
Controlling large I/O and cache strategies
Getting information on cache strategies
Enabling and disabling merge joins
Enabling and disabling join transitive closure
Suggesting a degree of parallelism for a query
Query level parallel clause examples
Concurrency optimization for small tables
Changing locking scheme
Query Tuning Tools
Overview
How tools may interact
Using showplan and noexec together
noexec and statistics io
How tools relate to query processing
Access Methods and Query Costing for Single Tables
Table scan cost
Cost of a scan on allpages-locked table
Cost of a scan on a data-only-locked tables
From rows to pages
How cluster ratios affect large I/O estimates
Data page cluster ratio
Index page cluster ratio
Evaluating the cost of index access
Query that returns a single row
Query that returns many rows
Range queries using clustered indexes (allpages locking)
Range queries with covering indexes
Range queries with noncovering indexes
Result-set size and index use
Costing for noncovering index scans
Costing for forwarded rows
Costing for queries using order by
Prefix subset and sorts
Key ordering and sorts
Specifying ascending or descending order for index keys
How the optimizer costs sort operations
Allpages-locked tables with clustered indexes
Sorts when index covers the query
Sorts and noncovering indexes
Backward scans and joins
Deadlocks and descending scans
Access Methods and Costing for or and in Clauses
or syntax
in (values_list) converts to or processing
Methods for processing or clauses
When table scans are used for or queries
Dynamic index (OR strategy)
Multiple matching index scans (special OR strategy)
How aggregates are optimized
Combining max and min aggregates
Queries that use both min and max
How update operations are performed
Direct updates
In-place updates
Cheap direct updates
Expensive direct updates
Deferred updates
When deferred updates are required
Deferred index inserts
Restrictions on update modes through joins
Joins and subqueries in update and delete statements
Deletes and updates in triggers versus referential integrity
Optimizing updates
Designing for direct updates
Effects of update types and indexes on update modes
Using sp_sysmon while tuning updates
Accessing Methods and Costing for Joins and Subqueries
Costing and optimizing joins
Processing
Index density and joins
Multicolumn densities
Datatype mismatches and joins
Join permutations
Outer joins and join permutations
Nested-loop joins
Cost formula
How inner and outer tables are determined
Access methods and costing for sort-merge joins
How a full-merge is performed
How a right-merge or left-merge is performed
How a sort-merge is performed
Mixed example
showplan messages for sort-merge joins
Costing for merge joins
Costing for a full-merge with unique values
Example: allpages-locked tables with clustered indexes
Costing for a full-merge with duplicate values
Costing sorts
Worktable size for sort-merge joins
When merge joins cannot be used
Use of worker processes
Recommendations for improved merge performance
Enabling and disabling merge joins
At the server level
At the session level
Reformatting strategy
Subquery optimization
Flattening in, any, and exists subqueries
When flattening can be done
Exceptions to flattening
Flattening methods
Join order and flattening methods
Flattened subqueries executed as regular joins
Flattened subqueries executed as existence joins
Flattened subqueries executed using unique reformatting
Flattened subqueries using duplicate elimination
Flattening expression subqueries
Materializing subquery results
Noncorrelated expression subqueries
Quantified predicate subqueries containing aggregates
Subquery introduced with an and clause
Subquery introduced with an or clause
Subquery results caching
Displaying subquery cache information
Optimizing subqueries
or clauses versus unions in joins
Parallel Query Processing
Types of queries that can benefit from parallel processing
Adaptive Server’s worker process model
Parallel query execution
Returning results from parallel queries
Types of parallel data access
Hash-based table scans
Partition-based scans
Hash-based index scans
Parallel processing for two tables in a join
showplan messages
Controlling the degree of parallelism
Configuration parameters for controlling parallelism
How limits apply to query plans
How the limits work in combination
Examples of setting parallel configuration parameters
Using set options to control parallelism for a session
set command examples
Controlling parallelism for a query
Query level parallel clause examples
Worker process availability and query execution
Other configuration parameters for parallel processing
Commands for working with partitioned tables
Balancing resources and performance
CPU resources
Disk resources and I/O
Tuning example: CPU and I/O saturation
Guidelines for parallel query configuration
Hardware guidelines
Working with your performance goals and hardware guidelines
Examples of parallel query tuning
Improving the performance of a table scan
Improving the performance of a nonclustered index scan
Guidelines for partitioning and parallel degree
Experimenting with data subsets
System level impacts
Locking issues
Device issues
Procedure cache effects
When parallel query results can differ
Queries that use set rowcount
Queries that set local variables
Achieving consistent results
Parallel Query Optimization
What is parallel query optimization?
Optimizing for response time versus total work
When is optimization performed?
Overhead costs
Factors that are not considered
Parallel access methods
Parallel partition scan
Requirements for consideration
Cost model
Parallel clustered index partition scan (allpages-locked tables)
Requirements for consideration
Cost model
Parallel hash-based table scan
Hash-based table scans on allpages-locked tables
Hash-based table scans on data-only-locked tables
Requirements for consideration
Cost model
Parallel hash-based index scan
Cost model and requirements
Parallel range-based scans
Requirements for consideration
Additional parallel strategies
Partitioned worktables
Parallel sorting
Summary of parallel access methods
Selecting parallel access methods
Degree of parallelism for parallel queries
Upper limit
Optimized degree
Worker processes for partition-based scans
Worker processes for hash-based scans
Worker processes for range-based scans
Nested-loop joins
Alternative plans
Computing the degree of parallelism for nested-loop joins
Parallel queries and existence joins
Examples
Partitioned heap table
Nonpartitioned heap table
Table with clustered index
Runtime adjustments to worker processes
Parallel query examples
Single-table scans
Table partition scan
Multitable joins
Parallel join optimization and join orders
Subqueries
Queries that require worktables
union queries
Queries with aggregates
select into statements
Runtime adjustment of worker processes
How Adaptive Server adjusts a query plan
Evaluating the effect of runtime adjustments
Recognizing and managing runtime adjustments
Using set process_limit_action
Using showplan
Reducing the likelihood of runtime adjustments
Checking runtime adjustments with sp_sysmon
Diagnosing parallel performance problems
Query does not run in parallel
Parallel performance is not as good as expected
Calling technical support for diagnosis
Resource limits for parallel queries
Parallel Sorting
Commands that benefits from parallel sorting
Requirements and resources overview
Overview of the parallel sorting strategy
Creating a distribution map
Dynamic range partitioning
Range sorting
Merging results
Configuring resources for parallel sorting
Worker process requirements for parallel sorts
Worker process requirements for creating indexes
Using with consumers while creating indexes
Worker process requirements for select query sorts
Worker processes for merge-join sorts
Other worktable sorts
Caches, sort buffers, and parallel sorts
Cache bindings
Number of sort buffers can affect sort performance
Sort buffer configuration guidelines
Using less than the configured number of sort buffers
Configuring the number of sort buffers parameter
Procedure for estimating merge levels and I/O
Configuring caches for large I/O during parallel sorting
Balancing sort buffers and large I/O configuration
Disk requirements
Space requirements for creating indexes
Space requirements for worktable sorts
Number of devices in the target segment
Recovery considerations
Tools for observing and tuning sort behavior
Using set sort_resources on
Examples
Using sp_sysmon to tune index creation
Tuning Asynchronous Prefetch
How asynchronous prefetch improves performance
Improving query performance by prefetching pages
Prefetching control mechanisms in a multiuser environment
Look-ahead set during recovery
Prefetching log pages
Prefetching data and index pages
Look-ahead set during sequential scans
Look-ahead set during nonclustered index access
Look-ahead set during dbcc checks
Allocation checking
checkdb and checktable
Look-ahead set minimum and maximum sizes
When prefetch is automatically disabled
Flooding pools
I/O system overloads
Unnecessary reads
Page chain fragmentation
Tuning Goals for asynchronous prefetch
Commands for configuration
Other Adaptive Server performance features
Large I/O
Sizing and limits for the 16k pool
Limits for the 2K pool
Fetch-and-discard (MRU) scans
Parallel scans and large I/Os
Hash-based table scans
Partition-based scans
Special settings for asynchronous prefetch limits
Setting limits for recovery
Setting limits for dbcc
Maintenance activities for high prefetch performance
Eliminating kinks in heap tables
Eliminating kinks in clustered index tables
Eliminating kinks in nonclustered indexes
Performance monitoring and asynchronous prefetch
Cursors and Performance
Definition
Set-oriented versus row-oriented programming
Example
Resources required at each stage
Memory use and execute cursors
Cursor modes
Index use and requirements for cursors
Allpages-locked tables
Data-only-locked tables
Table scans to avoid the Halloween problem
Comparing performance with and without cursors
Sample stored procedure without a cursor
Sample stored procedure with a cursor
Cursor versus noncursor performance comparison
Locking with read-only cursors
Isolation levels and cursors
Partitioned heap tables and cursors
Optimizing tips for cursors
Optimizing for cursor selects using a cursor
Using union instead of or clauses or in lists
Declaring the cursor’s intent
Specifying column names in the for update clause
Using set cursor rows
Keeping cursors open across commits and rollbacks
Opening multiple cursors on a single connection
Introduction to Abstract Plans
Definition
Managing abstract plans
Relationship between query text and query plans
Limits of options for influencing query plans
Full versus partial plans
Creating a partial plan
Abstract plan groups
How abstract plans are associated with queries
Abstract Query Plan Guide
Introduction
Abstract plan language
Queries, access methods, and abstract plans
Identifying tables
Identifying indexes
Specifying join order
Shorthand notation for joins
Join order examples
Match between execution methods and abstract plans
Specifying join order for queries using views
Specifying the join type
Specifying partial plans and hints
Grouping multiple hints
Inconsistent and illegal plans using hints
Creating abstract plans for subqueries
Materialized subqueries
Flattened subqueries
Example: changing the join order in a flattened subquery
Nested subqueries
Subquery identification and attachment
More subquery examples: reading ordering and attachment
Modifying subquery nesting
Abstract plans for materialized views
Abstract plans for queries containing aggregates
Specifying the reformatting strategy
OR strategy limitation
When the store operator is not specified
Tips on writing abstract plans
Comparing plans “before” and “after”
Effects of enabling server-wide capture mode
Time and space to copy plans
Abstract plans for stored procedures
Procedures and plan ownership
Procedures with variable execution paths and optimization
Ad Hoc queries and abstract plans
Creating and Using Abstract Plans
Using set commands to capture and associate plans
Enabling plan capture mode with set plan dump
Associating queries with stored plans
Using replace mode during plan capture
When to use replace mode
Using dump, load, and replace modes simultaneously
Using dump and load to the same group
Using dump and load to different groups
set plan exists check option
Using other set options with abstract plans
Using showplan
Using noexec
Using forceplan
Server-wide abstract plan capture and association Modes
Creating plans using SQL
Using create plan
Using the plan Clause
Managing Abstract Plans with System Procedures
System procedures for managing abstract plans
Managing an abstract plan group
Creating a group
Dropping a group
Getting information about a group
Renaming a group
Finding abstract plans
Managing individual abstract plans
Viewing a plan
Copying a plan to another group
Dropping an individual abstract plan
Comparing two abstract plans
Changing an existing plan
Managing all plans in a group
Copying all plans in a group
Comparing all plans in a group
Dropping all abstract plans in a group
Importing and exporting groups of plans
Exporting plans to a user table
Importing plans from a user table
Abstract Plan Language Reference
Keywords
Operands
Derived tables
Schema for examples
g_join
hints
i_scan
in
lru
m_g_join
mru
nested
nl_g_join
parallel
plan
prefetch
prop
scan
store
subq
t_scan
table
union
view
work_t
Using Statistics to Improve Performance
Importance of statistics
Updating
Adding statistics for unindexed columns
update statistics commands
Column statistics and statistics maintenance
Creating and updating column statistics
When additional statistics may be useful
Adding statistics for a column with update statistics
Adding statistics for minor columns with update index statistics
Adding statistics for all columns with update all statistics
Choosing step numbers for histograms
Disadvantages of too many steps
Choosing a step number
Scan types, sort requirements, and locking
Sorts for unindexed or non leading columns
Locking, scans, and sorts during update index statistics
Locking, scans and sorts during update all statistics
Using the with consumers clause
Reducing update statistics impact on concurrent processes
Using the delete statistics command
When row counts may be inaccurate
Using the set statistics Commands
Command syntax
Using simulated statistics
Checking subquery cache performance
Checking compile and execute time
Converting ticks to milliseconds
Reporting physical and logical I/O statistics
Total actual I/O cost value
Statistics for writes
Statistics for reads
Sample output with and without an index
statistics io output for cursors
Scan count
Queries reporting a scan count of 1
Queries reporting a scan count of more than 1
Queries reporting scan count of 0
Relationship between physical and logical reads
Logical reads, physical reads, and 2K I/O
Physical reads and large I/O
Reads and writes on worktables
Effects of caching on reads
statistics io and merge joins
Using set showplan
Using
Basic showplan messages
Query plan delimiter message
Step message
Query type message
FROM TABLE message
FROM TABLE and referential integrity
TO TABLE message
Update mode messages
Direct update mode
Deferred mode
Deferred index and deferred varcol messages
Optimized using messages
Simulated statistics message
Abstract plan messages
showplan messages for query clauses
GROUP BY message
Selecting into a worktable
Grouped aggregate message
Grouped aggregates and group by
compute by message
Ungrouped aggregate message
Ungrouped aggregates
compute messages
messages for order by and distinct
Worktable message for distinct
Worktable message for order by
Sorting messages
Step involves sorting message
GETSORTED message
Serial or parallel sort message
Messages describing access methods, caching, and I/O cost
Auxiliary scan descriptors message
Nested iteration message
Merge join messages
Worktable message
Table scan message
Clustered index message
Index name message
Scan direction messages
Positioning messages
Scanning messages
Index covering message
Keys message
Matching index scans message
Dynamic index message (OR strategy)
Reformatting Message
Trigger Log Scan Message
I/O Size Messages
Cache strategy messages
Total estimated I/O cost message
showplan messages for parallel queries
Executed in parallel messages
Coordinating process message
Worker processes message
Scan type message
Merge messages
Data merge messages
Runtime adjustment message
showplan messages for subqueries
Output for flattened or materialized subqueries
Flattened queries
Materialized queries
Structure of subquery showplan output
Subquery execution message
Nesting level delimiter message
Subquery plan start delimiter
Subquery plan end delimiter
Type of subquery
Subquery predicates
Internal subquery aggregates
Quantified predicate subqueries and the ANY aggregate
Expression subqueries and the ONCE aggregate
Subqueries with distinct and the ONCE-UNIQUE aggregate
Existence join message
Subqueries that perform existence tests
Statistics Tables and Displaying Statistics with optdiag
System tables that store statistics
systabstats table
sysstatistics table
Viewing statistics with the optdiag utility
optdiag syntax
optdiag header information
Table statistics
Sample output for table statistics
Data page CR count
Table-level derived statistics
Data page cluster ratio
Space utilization
Large I/O efficiency
Index statistics
Sample output for index statistics
Index-level derived statistics
Data page cluster ratio
Index page cluster ratio
Data row cluster ratio
Space utilization for an index
Large I/O efficiency for an index
Column statistics
Sample output for column statistics
Range cell and total density values
Range and in-between selectivity values
Histogram displays
Sample output for histograms
Understanding histogram output
Histograms for columns with highly duplicated values
Choosing the number of steps for highly duplicated values
Changing statistics with optdiag
Using the optdiag binary mode
When you must use binary mode
Updating selectivities with optdiag input mode
Editing histograms
Adding frequency count cells to a histogram
Skipping the load-time verification for step numbering
Rules checked during histogram loading
Re-creating indexes without losing statistics updates
Using simulated statistics
optdiag syntax for simulated statistics
Simulated statistics output
Requirements for loading and using simulated statistics
Using simulated statistics in the original database
Using simulated statistics in another database
Dropping simulated statistics
Running queries with simulated statistics
showplan messages for simulated statistics
Character data containing quotation marks
Effects of SQL commands on statistics
How query processing affects systabstats
Tuning with dbcc traceon
Tuning with dbcc traceon(302)
dbcc traceon(310)
Invoking the dbcc trace facility
General tips for tuning with dbcc traceon(302)
Checking for join columns and search arguments
Determining how the optimizer estimates I/O costs
Structure of dbcc traceon(302) output
Additional blocks and messages
Table information block
Identifying the table
Basic table data
Cluster ratio
Partition information
Base cost block
Concurrency optimization message
Clause block
Search clause identification
When search clauses are not optimizable
Values unknown at optimize time
Join clause identification
Sort avert messages
Column block
Selectivities when statistics exist and values are known
When the optimizer uses default values
Unknown values
If no statistics are available
Out-of-range messages
“Disjoint qualifications” message
Forcing messages
Unique index messages
Other messages in the column block
Index selection block
Scan and filter selectivity values
How scan and filter selectivity can differ
Other information in the index selection block
Best access block
dbcc traceon(310) and final query plan costs
Flattened subquery join order message
Worker process information
Final plan information
Sort-merge costs
Monitoring Performance with sp_sysmon
Using
When to run
Invoking
Fixed time intervals
Using begin_sample and end_sample
Specifying report sections for output
Specifying the application detail parameter
Redirecting output to a file
How to use the reports
Reading output
Rows
Columns
Interpreting the data
Per second and per transaction data
Percent of total and count data
Per engine data
Total or summary data
Sample interval and time reporting
Kernel utilization
Sample output
Engine busy utilization
CPU yields by engine
Network checks
Non–blocking
Blocking
Total network I/O checks
Average network I/Os per check
Disk I/O checks
Total disk I/O checks
Checks returning I/O
Average disk I/Os returned
Worker process management
Sample output
Worker process requests
Worker process usage
Memory requests for worker processes
Avg mem ever used by a WP
Parallel query management
Sample output
Parallel query usage
Merge lock requests
Sort buffer waits
Task management
Sample output
Connections opened
Task context switches by engine
Task context switches due to
Voluntary yields
Cache search misses
System disk writes
I/O pacing
Logical lock contention
Address lock contention
Latch contention
Log semaphore contention
PLC lock contention
Group commit sleeps
Last log page writes
Modify conflicts
I/O device contention
Network packet received
Network packet sent
Other causes
Application management
Requesting detailed application information
Sample output
Application statistics summary (all applications)
Priority changes
Allotted slices exhausted
Skipped tasks by engine
Engine scope changes
Per application or per application and login
Application activity
Application priority changes
Application I/Os completed
Resource limits violated
ESP management
Sample output
ESP requests
Avg. time to execute an ESP
Housekeeper task activity
Sample output
Buffer cache washes
Garbage collections
Statistics updates
Monitor access to executing SQL
Sample output
Waits on execution plans
Number of SQL text overflows
Maximum SQL text requested
Transaction profile
Sample output
Transaction summary
How to count multi database transactions
Transaction detail
Inserts
APL heap tables
APL clustered table
Data only lock table
Total rows inserted
Updates and update detail sections
Updates
Data-only-locked updates
Deletes
Total rows deleted
Transaction management
Sample output
ULC flushes to transaction log
By full ULC
By end transaction
By change of database
By system log record and by other
Total ULC flushes
ULC log records
Maximum ULC size
ULC semaphore requests
Log semaphore requests
Log semaphore contention and user log caches
Transaction log writes
Transaction log allocations
Avg # writes per log page
Index management
Sample output
Nonclustered maintenance
Inserts and updates requiring maintenance to indexes
Deletes requiring maintenance
Row ID updates from clustered split
Data-Only-Locked updates and deletes requiring maintenance
Page splits
Reducing page splits for ascending key inserts
Default data page splitting
Effects of ascending inserts
Setting ascending inserts mode for a table
Retries and deadlocks
Add index level
Page shrinks
Index scans
Metadata cache management
Sample output
Open object, index, and database usage
Object and index spinlock contention
Hash spinlock contention
Using sp_monitorconfig to find metadata cache usage statistics
Lock management
Sample output
Lock summary
Lock detail
Address locks
Last page locks on heaps
Table lock hashtable
Deadlocks by lock type
Deadlock detection
Deadlock searches
Searches skipped
Average deadlocks per search
Lock promotions
Lock time-out information
Data cache management
Sample output
Cache statistics summary (all caches)
Cache search summary
Cache turnover
Cache strategy summary
Large I/O usage
Large I/O effectiveness
Asynchronous prefetch activity report
Other asynchronous prefetch statistics
Dirty read behavior
Cache management by cache
Cache spinlock contention
Utilization
Cache search, hit, and miss information
Pool turnover
Buffer wash behavior
Cache strategy
Large I/O usage
Large I/O detail
Dirty read behavior
Procedure cache management
Sample output
Procedure requests
Procedure reads from disk
Procedure writes to disk
Procedure removals
Memory management
Sample output
Pages allocated
Pages released
Recovery management
Sample output
Checkpoints
Number of normal checkpoints
Number of free checkpoints
Total checkpoints
Average time per normal checkpoint
Average time per free checkpoint
Increasing the housekeeper batch limit
Disk I/O management
Sample output
Maximum outstanding I/Os
I/Os delayed by
Disk I/O structures
Server configuration limit
Engine configuration limit
Operating system limit
Requested and completed disk I/Os
Total requested disk I/Os
Completed disk I/Os
Device activity detail
Reads and writes
Total I/Os
Device semaphore granted and waited
Network I/O management
Sample output
Total network I/Os requests
Network I/Os delayed
Total TDS packets received
Total bytes received
Average bytes received per packet
Total TDS packets sent
Total bytes sent
Average bytes sent per packet
Reducing packet overhead
Quick Reference Guide
Reference Manual
About this book
System and User-Defined Datatypes
Datatype categories
Range and storage size
Declaring the datatype of a column, variable, or parameter
Declaring the datatype for a column in a table
Declaring the datatype for a local variable in a batch or procedure
Declaring the datatype for a parameter in a stored procedure
Determining the datatype of a literal
Datatype of mixed-mode expressions
Determining the datatype hierarchy
Determining precision and scale
Converting one datatype to another
Automatic conversion of fixed-length NULL columns
Handling overflow and truncation errors
Standards and compliance
Exact numeric datatypes
Function
Integer types
Decimal datatypes
Standards and compliance
Approximate numeric datatypes
Function
Understanding approximate numeric datatypes
Range, precision, and storage size
Entering approximate numeric data
Values that may be entered by Open Client clients
Standards
Money datatypes
Function
Accuracy
Range and storage size
Entering monetary values
Standards
Timestamp datatype
Function
Creating a timestamp column
Date and time datatypes
Function
Range and storage requirements
Entering datetime and smalldatetime data
Standards and compliance
Character datatypes
Function
Length and storage size
Entering character data
Treatment of blanks
Manipulating character data
Standards
Binary datatypes
Function
Valid binary and varbinary entries
Entries of more than the max column size
Treatment of trailing zeroes
Platform dependence
Standards
bit datatype
Function
Entering data into bit columns
Storage size
Restrictions
Standards
sysname datatype
Function
Using the sysname datatype
Standards
text and image datatypes
Function
Data structures used for storing text and image data
Format of text data pages
Text nodes
Initializing text and image columns
Saving space by allowing NULL
Getting information from sysindexes
Using readtext and writetext
Determining how much space a column uses
Restrictions on text and image columns
Selecting text and image data
Converting text and image datatypes
Pattern matching in text data
Duplicate rows
Standards
User-defined datatypes
Function
Creating frequently used datatypes in the model database
Creating a user-defined datatypes
Renaming a user-defined datatype
Dropping a user-defined datatype
Getting help on datatypes
Standards and compliance
Transact-SQL Functions
Types of functions
Aggregate functions
Aggregates used with group by
Aggregate functions and NULL values
Vector and scalar aggregates
Aggregate functions as row aggregates
Datatype conversion functions
Converting character data to a non-character type
Converting from one character type to another
Converting numbers to a character type
Rounding during conversion to and from money types
Converting date/time information
Converting between numeric types
Arithmetic overflow and divide-by-zero errors
Scale errors
Domain errors
Conversions between binary and integer types
Converting between binary and numeric or decimal types
Converting image columns to binary types
Converting other types to bit
Converting NULL value
Date functions
Date parts
Mathematical functions
Security functions
String functions
Limits on string functions
System functions
Text and image functions
abs
acos
ascii
asin
atan
atn2
avg
ceiling
char
charindex
char_length
col_length
col_name
compare
convert
cos
cot
count
curunreservedpgs
data_pgs
datalength
dateadd
datediff
datename
datepart
db_id
db_name
degrees
difference
exp
floor
get_appcontext
getdate
hextoint
host_id
host_name
index_col
index_colorder
inttohex
isnull
is_sec_service_on
lct_admin
license_enabled
list_appcontext
lockscheme
log
log10
lower
ltrim
max
min
mut_excl_roles
object_id
object_name
pagesize
patindex
pi
power
proc_role
ptn_data_pgs
radians
rand
replicate
reserved_pgs
reverse
right
rm_appcontext
role_contain
role_id
role_name
round
rowcnt
rtrim
set_appcontext
show_role
show_sec_services
sign
sin
sortkey
soundex
space
sqrt
str
stuff
substring
sum
suser_id
suser_name
syb_quit()
syb_sendmsg
tan
textptr
textvalid
to_unichar
tsequal
uhighsurr
ulowsurr
upper
uscalar
used_pgs
user
user_id
user_name
valid_name
valid_user
Global Variables
Adaptive Server’s global variables
Expressions, Identifiers, and Wildcard Characters
Expressions
Size of expressions
Arithmetic and character expressions
Relational and logical expressions
Operator precedence
Arithmetic operators
Bitwise operators
String concatenation operator
Comparison operators
Nonstandard operators
Using any, all and in
Negating and testing
Ranges
Using nulls in expressions
Comparisons that return TRUE
Difference between FALSE and UNKNOWN
Using “NULL” as a character string
NULL compared to the empty string
Connecting expressions
Using parentheses in expressions
Comparing character expressions
Using the empty string
Including quotation marks in character expressions
Using the continuation character
Identifiers
Tables beginning with # (temporary tables)
Case sensitivity and identifiers
Uniqueness of object names
Using delimited identifiers
Identifying tables or columns by their qualified object name
Using delimited identifiers within an object name
Omitting the owner name
Referencing your own objects in the current database
Referencing objects owned by the database owner
Using qualified identifiers consistently
Determining whether an identifier is valid
Renaming database objects
Using multibyte character sets
Pattern matching with wildcard characters
Using not like
Case and accent insensitivity
Using wildcard characters
The percent sign (%) wildcard character
The underscore (_) wildcard character
Bracketed ([ ]) characters
The caret (^) wildcard character
Using multibyte wildcard characters
Using wildcard characters as literal characters
Using square brackets ( [ ] ) as escape characters
Using the escape clause
Using wildcard characters with datetime data
Reserved Words
Transact-SQL reserved words
SQL92 reserved words
Potential SQL92 reserved words
SQLSTATE Codes and Messages
Warnings
Exceptions
Cardinality violations
Data exceptions
Integrity constraint violations
Invalid cursor states
Syntax errors and access rule violations
Transaction rollbacks
with check option violation
Commands
Overview
alter database
alter role
alter table
begin...end
begin transaction
break
case
checkpoint
close
coalesce
commit
compute clause
connect to...disconnect
continue
create database
create default
create existing table
create function (SQLJ)
create index
create plan
create procedure
create procedure (SQLJ)
create proxy_table
create role
create rule
create schema
create table
create trigger
create view
dbcc
deallocate cursor
declare
declare cursor
delete
delete statistics
disk init
disk mirror
disk refit
disk reinit
disk remirror
disk unmirror
drop database
drop default
drop function (SQLJ)
drop index
drop procedure
drop role
drop rule
drop table
drop trigger
drop view
dump database
dump transaction
execute
fetch
goto label
grant
group by and having clauses
if...else
insert
kill
load database
load transaction
lock table
nullif
online database
open
order by clause
prepare transaction
print
quiesce database
raiserror
readtext
reconfigure
remove java
reorg
return
revoke
rollback
rollback trigger
save transaction
select
set
setuser
shutdown
truncate table
union operator
update
update all statistics
update partition statistics
update statistics
use
waitfor
where clause
while
writetext
System Procedures
Introduction to system procedures
Permissions on system procedures
Executing system procedures
Entering parameter values
Messages
System procedure tables
List of system procedures
sp_activeroles
sp_addalias
sp_addauditrecord
sp_addaudittable
sp_addengine
sp_addexeclass
sp_addextendedproc
sp_addexternlogin
sp_addgroup
sp_addlanguage
sp_addlogin
sp_addmessage
sp_addobjectdef
sp_add_qpgroup
sp_addremotelogin
sp_add_resource_limit
sp_addsegment
sp_addserver
sp_addthreshold
sp_add_time_range
sp_addtype
sp_addumpdevice
sp_adduser
sp_altermessage
sp_audit
sp_autoconnect
sp_bindcache
sp_bindefault
sp_bindexeclass
sp_bindmsg
sp_bindrule
sp_cacheconfig
sp_cachestrategy
sp_changedbowner
sp_changegroup
sp_checknames
sp_checkreswords
sp_checksource
sp_chgattribute
sp_clearpsexe
sp_clearstats
sp_client_addr
sp_cmp_all_qplans
sp_cmp_qplans
sp_commonkey
sp_companion
sp_configure
sp_copy_all_qplans
sp_copy_qplan
sp_countmetadata
sp_cursorinfo
sp_dboption
sp_dbrecovery_order
sp_dbremap
sp_defaultloc
sp_depends
sp_deviceattr
sp_diskdefault
sp_displayaudit
sp_displaylevel
sp_displaylogin
sp_displayroles
sp_dropalias
sp_drop_all_qplans
sp_dropdevice
sp_dropengine
sp_dropexeclass
sp_dropextendedproc
sp_dropexternlogin
sp_dropglockpromote
sp_dropgroup
sp_dropkey
sp_droplanguage
sp_droplogin
sp_dropmessage
sp_dropobjectdef
sp_drop_qpgroup
sp_drop_qplan
sp_dropremotelogin
sp_drop_resource_limit
sp_droprowlockpromote
sp_dropsegment
sp_dropserver
sp_dropthreshold
sp_drop_time_range
sp_droptype
sp_dropuser
sp_dumpoptimize
sp_engine
sp_estspace
sp_export_qpgroup
sp_extendsegment
sp_extengine
sp_familylock
sp_find_qplan
sp_fixindex
sp_flushstats
sp_forceonline_db
sp_forceonline_object
sp_forceonline_page
sp_foreignkey
sp_freedll
sp_getmessage
sp_grantlogin
sp_ha_admin
sp_help
sp_helpartition
sp_helpcache
sp_helpconfig
sp_helpconstraint
sp_helpdb
sp_helpdevice
sp_helpextendedproc
sp_helpexternlogin
sp_helpgroup
sp_helpindex
sp_helpjava
sp_helpjoins
sp_helpkey
sp_helplanguage
sp_helplog
sp_helpobjectdef
sp_help_qpgroup
sp_help_qplan
sp_helpremotelogin
sp_help_resource_limit
sp_helprotect
sp_helpsegment
sp_helpserver
sp_helpsort
sp_helptext
sp_helpthreshold
sp_helpuser
sp_hidetext
sp_import_qpgroup
sp_indsuspect
sp_listsuspect_db
sp_listsuspect_object
sp_listsuspect_page
sp_lock
sp_locklogin
sp_logdevice
sp_loginconfig
sp_logininfo
sp_logiosize
sp_modifylogin
sp_modify_resource_limit
sp_modify_time_range
sp_modifystats
sp_modifythreshold
sp_monitor
sp_monitorconfig
sp_object_stats
sp_passthru
sp_password
sp_placeobject
sp_plan_dbccdb
sp_poolconfig
sp_primarykey
sp_processmail
sp_procqmode
sp_procxmode
sp_recompile
sp_remap
sp_remoteoption
sp_remotesql
sp_rename
sp_renamedb
sp_rename_qpgroup
sp_reportstats
sp_revokelogin
sp_role
sp_sendmsg
sp_serveroption
sp_setlangalias
sp_setpglockpromote
sp_setpsexe
sp_set_qplan
sp_setrowlockpromote
sp_setsuspect_granularity
sp_setsuspect_threshold
sp_showcontrolinfo
sp_showexeclass
sp_showplan
sp_showpsexe
sp_spaceused
sp_ssladmin
sp_syntax
sp_sysmon
sp_thresholdaction
sp_transactions
sp_unbindcache
sp_unbindcache_all
sp_unbindefault
sp_unbindexeclass
sp_unbindmsg
sp_unbindrule
sp_volchanged
sp_who
Catalog Stored Procedures
Overview
Specifying optional parameters
Pattern matching
System procedure tables
ODBC datatypes
sp_column_privileges
sp_columns
sp_databases
sp_datatype_info
sp_fkeys
sp_pkeys
sp_server_info
sp_special_columns
sp_sproc_columns
sp_statistics
sp_stored_procedures
sp_table_privileges
sp_tables
System Extended Stored Procedures
Overview
Permissions on system ESPs
DLLs associated with system ESPs
Using system ESPs
xp_cmdshell
xp_deletemail
xp_enumgroups
xp_findnextmsg
xp_logevent
xp_readmail
xp_sendmail
xp_startmail
xp_stopmail
dbcc Stored Procedures
Overview
Specifying the object name and date
Specifying the object name
Specifying the date
sp_dbcc_alterws
sp_dbcc_configreport
sp_dbcc_createws
sp_dbcc_deletedb
sp_dbcc_deletehistory
sp_dbcc_differentialreport
sp_dbcc_evaluatedb
sp_dbcc_faultreport
sp_dbcc_fullreport
sp_dbcc_runcheck
sp_dbcc_statisticsreport
sp_dbcc_summaryreport
sp_dbcc_updateconfig
System Tables
Locations of system tables
System tables in master
System tables in sybsecurity
System table in sybsystemdb
System tables in all databases
About the sybdiagdb database
About the syblicenseslog table
Rules for using system tables
Permissions on system tables
Locking schemes used for system tables
Reserved columns
Updating system tables
Triggers on system tables
Aggregate functions and virtual tables
sysalternates
sysattributes
sysauditoptions
sysaudits_01 – sysaudits_08
syscharsets
syscolumns
syscomments
sysconfigures
sysconstraints
syscoordinations
syscurconfigs
sysdatabases
sysdepends
sysdevices
sysengines
sysgams
sysindexes
sysjars
syskeys
syslanguages
syslisteners
syslocks
sysloginroles
syslogins
syslogs
syslogshold
sysmessages
sysmonitors
sysobjects
syspartitions
sysprocedures
sysprocesses
sysprotects
sysqueryplans
sysreferences
sysremotelogins
sysresourcelimits
sysroles
syssecmechs
syssegments
sysservers
syssessions
syssrvroles
sysstatistics
systabstats
systhresholds
systimeranges
systransactions
systypes
sysusages
sysusermessages
sysusers
sysxtypes
syblicenseslog
dbccdb Tables
dbccdb workspaces
dbccdb log
dbcc_config
dbcc_counters
dbcc_fault_params
dbcc_faults
dbcc_operation_log
dbcc_operation_results
dbcc_types
System Administration Guide
About this book
Audience
How to use this book
Related Documents
Other sources of information
Sybase certifications on the Web
Sybase EBFs and software updates
Conventions used in this manual
Formatting SQL statements
SQL syntax conventions
Case
Obligatory options {you must choose at least one}
Optional options
Ellipsis
Expressions
If you need help
Overview of System Administration
Adaptive Server administration tasks
Roles required for system administration tasks
Database Owner
Database object owner
Using isql to perform system administration tasks
Starting isql
Entering statements
Saving and reusing statements
Using Sybase Central for system administration tasks
System tables
Querying the system tables
Keys in system tables
Updating system tables
System procedures
Using system procedures
System procedure tables
Creating system procedures
System extended stored procedures
Creating system ESPs
Logging error messages
Connecting to Adaptive Server
The interfaces file
Directory services
LDAP as a directory service
Multiple directory services
LDAP directory services versus the Sybase interfaces file
Security features available in Adaptive Server
System and Optional Databases
Overview of system databases
master database
Controlling object creation in master
Backing up master and keeping copies of system tables
model database
sybsystemprocs database
tempdb database
Creating temporary tables
sybsecurity database
sybsystemdb database
pubs2 and pubs3 sample databases
Maintaining the sample databases
pubs2 image data
dbccdb database
sybdiag database
System Administration for Beginners
Logical page sizes
Using “test” servers
Understanding new procedures and features
Planning resources
Achieving performance goals
Installing Sybase products
Check product compatibility
Install or upgrade Adaptive Server
Install additional third-party software
Configure and test client connections
Allocating physical resources
Dedicated vs. shared servers
Decision support and OLTP applications
Advance resource planning
Operating system configuration
Backup and recovery
Keep up-to-date backups of master
Keep offline copies of system tables
Automate backup procedures
Verify data consistency before backing up a database
Monitor the log size
Ongoing maintenance and troubleshooting
Starting and stopping Adaptive Server
Viewing and pruning the error log
Keeping records
Contact information
Configuration information
Maintenance schedules
System information
Disaster recovery plan
Getting more help
Diagnosing System Problems
How Adaptive Server uses error messages to respond to system problems
Error messages and message numbers
Variables in error message text
Adaptive Server error logging
Error log format