SyBooks Online Infocenter Help
Navigating the Content
Searching for Content
Searching all Content Collections
Narrowing a Search
Search Keyboard Shortcuts
Navigating the Content
Searching for Content
Searching all Content Collections
Narrowing a Search
Search Keyboard Shortcuts
Adaptive Server Enterprise 15.5
Release Bulletin for HP-UX
Product Summary
Adaptive Server Interoperability
Product Compatibility
Replication Server Support for In-Memory Databases
Changes that Affect Existing Applications
Installation and Upgrade
Special Configuration Instructions
Known Issues
Documentation Updates and Clarifications
Obtaining Help and Additional Information
Technical Support
Sybase EBFs and Software Maintenance
Sybase Product and Component Certifications
Web Links to Certification Information
Creating a MySybase Profile
Accessibility Features
Release Bulletin for IBM AIX
Product Summary
Adaptive Server Interoperability
Product Compatibility
Replication Server Support for In-Memory Databases
Changes that Affect Existing Applications
Installation and Upgrade
Special Configuration Instructions
Known Issues
Documentation Updates and Clarifications
Obtaining Help and Additional Information
Technical Support
Sybase EBFs and Software Maintenance
Sybase Product and Component Certifications
Web Links to Certification Information
Creating a MySybase Profile
Accessibility Features
Release Bulletin for Linux
Product Summary
Adaptive Server Interoperability
Product Compatibility
Replication Server Support for In-Memory Databases
Changes that Affect Existing Applications
Installation and Upgrade
Special Configuration Instructions
Known Issues
Documentation Updates and Clarifications
Obtaining Help and Additional Information
Technical Support
Sybase EBFs and Software Maintenance
Sybase Product and Component Certifications
Web Links to Certification Information
Creating a MySybase Profile
Accessibility Features
Release Bulletin for Sun Solaris
Product Summary
Adaptive Server Interoperability
Product Compatibility
Replication Server Support for In-Memory Databases
Changes that Affect Existing Applications
Installation and Upgrade
Special Configuration Instructions
Known Issues
Documentation Updates and Clarifications
Obtaining Help and Additional Information
Technical Support
Sybase EBFs and Software Maintenance
Sybase Product and Component Certifications
Web Links to Certification Information
Creating a MySybase Profile
Accessibility Features
Release Bulletin for Windows
Release Bulletin Adaptive Server® Enterprise 15.5 for Windows
Product Summary
Adaptive Server Interoperability
Product Compatibility
Replication Server Support for In-Memory Databases
Changes that Affect Existing Applications
Installation and Upgrade
Special Configuration Instructions
Known Issues
Documentation Updates and Clarifications
Obtaining Help and Additional Information
Technical Support
Sybase EBFs and Software Maintenance
Sybase Product and Component Certifications
Web Links to Certification Information
Creating a MySybase Profile
Accessibility Features
Installation Guide for HP-UX
Adaptive Server Enterprise Architecture
Adaptive Server Enterprise Editions
Determining the Current Edition
Options for Adaptive Server Editions
Installation Workflows
Style Conventions
Server Component Descriptions and Directory Layout
PC-Client Product Descriptions and Directory Layout
Separately Installable PC-Client Products
Planning the Installation
Obtaining a License
Accessing SPDC
Generating a License at SPDC
Generating a Served License
Generating an Unserved License
Installing a New License Server
System Requirements for Servers
Sybase Support for OLE DB and ODBC
Memory Requirements
System Requirements for Clients
Preinstallation Tasks for the Server Installation
Obtaining Your Host ID
Preinstallation for an Overlay Installation
Installing Server Components
Installing in GUI mode
Installing From the Command Line
Creating a Response File
Interactive Installation Using a Response File
Installing Server in Unattended (silent) Mode
Installing the Adaptive Server Version 15.5 Binary Overlay
Determining Adaptive Server Version
Backing up Adaptive Server
Upgrading an Adaptive Server Configured with High Availability
Upgrading a High Availability-enabled Adaptive Server in an Active-active Configuration:
Upgrading High Availability-enabled Adaptive Servers in an Active-passive Configuration
Monitoring and Diagnostic (MDA) Tables
New Monitoring and Diagnostic Tables in Adaptive Server 15.5
Monitoring Remote Servers Using MDA Tables
Removing Adaptive Server
Uninstalling Adaptive Server 15.5
Removing an Existing Adaptive Server
Installer Changes from Adaptive Server 12.5.4 to Adaptive Server 15.5
Response File Change
Postinstallation Tasks
Verifying that the Servers Are Running
Verifying Your Connection to the Servers
Connecting to Adaptive Server Via Sybase Central
Setting the System Administrator Password
Installing Sample Databases
Default Devices for the Sample Databases
Running the Database Scripts
Installing the Interpubs Database
Installing the Jpubs Database
Maintaining Sample Databases
PC-Client Installation
System Requirements for PC-Client
Installing the Client
Creating a Response File
Interactive Installation Using a Response File
Installing the Client in Unattended (Silent) Mode
Getting Started After Installing
Configuring libtcl.cfg for LDAP
Using a Directory Service
Upgrade
Preparing to Upgrade
Preupgrade Tasks
Reserved Words
Running a Reserved Word Check
Addressing Reserved Words Conflicts
Using Quoted Identifiers
Preparing the Database and Devices for the Upgrade
Increasing Default Database Sizes
Upgrading to Adaptive Server 15.5
Using Sqlupgrade
Using Sqlupgraderes
Resource File Attributes for Upgrading Adaptive Server
Upgrading Using Sqlupgraderes
Enabling PCI in Adaptive Server
Postupgrade Tasks
Running the Instmsgs.ebf Script
Restoring Functionality in Adaptive Server
Reenabling Replication Server After the Dataserver Upgrade
Restore Replication on Destination Databases
Restoring Replication on Primary Databases
Reenabling Auditing
Special Considerations for Threshold Procedures for Audit Segments
Upgrading Backup and Monitor Servers
Upgrading Java in the Database
Enabling the Java in The Database Feature in a High Availability System
Upgrading Job Scheduler
Upgrading Job Scheduler Templates
Upgrading High Availability and Cluster Support
Migrating from 32-bit to 64-bit Versions on Different Computers
Migrating Data Using a Dump and Load Method
Using BCP to Migrate Data
Migrating Data by Replacing the Binary
Upgrading Using Allrows_dss In Parallel
Recovering From a Failed Upgrade
Downgrading from Adaptive Server 15.5
Post-downgrade Tasks
Additional Steps Required if You Used New Features
Downgrading Adaptive Server with Replicated Databases
Downgrading Job Scheduler
If You Downgraded Back to the Original Installation After Upgrading to 15.5
Troubleshoot SySAM
Where to Look For SySAM Related Errors
Troubleshooting SySAM
Enabling and Changing E-mail Notifications
Calling Sybase Technical Support
Troubleshoot the Server
Logs For Installation Utilities
Logs For Sybase Servers
Solutions to Common Installation Problems
Stopping Adaptive Server After a Failure
Recovering from a Failed Installation
If the Installation Quits While You Are Configuring Adaptive Server
If Adaptive Server Fails the Preupgrade Eligibility Test
Recovering from a Failed Upgrade
Restoring from Backup
If the Cause of the Failure Is Unknown
Re-running the Upgrade
If the Cause of the Failure Is Known
Upgrading Compiled Objects with Dbcc Upgrade_Object
Finding Compiled Object Errors Before Production
Quoted Identifier Errors
Select * Potential Problem Areas
Determining Whether Select * Should Be Changed in Views
Syntax for Using Dbcc Upgrade_object
Syntax Examples for Upgrading Compiled Objects
Increasing the Log Segment Size
Upgrading Using Dump and Load
Upgrading Compiled Objects in Database Dumps
Upgrading Servers With Replicated Databases
Determining If Existing Servers Contain 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
Using sybsystemprocs
Increasing the Size of the sybsystemprocs Database
Increasing Device and Database Capacity for System Procedures
Obtaining Help and Additional Information
Installation Guide for IBM AIX
Adaptive Server Enterprise Architecture
Adaptive Server Enterprise Editions
Determining the Current Edition
Options for Adaptive Server Editions
Installation Workflows
Style Conventions
Server Component Descriptions and Directory Layout
PC-Client Product Descriptions and Directory Layout
Separately Installable PC-Client Products
Planning the Installation
Obtaining a License
Accessing SPDC
Generating a License at SPDC
Generating a Served License
Generating an Unserved License
Installing a New License Server
System Requirements for Servers
Sybase Support for OLE DB and ODBC
Memory Requirements
System Requirements for Clients
Preinstallation Tasks for the Server Installation
Obtaining Your Host ID
Preinstallation for an Overlay Installation
Installing Server Components
Installing in GUI mode
Installing From the Command Line
Creating a Response File
Interactive Installation Using a Response File
Installing Server in Unattended (silent) Mode
Installing the Adaptive Server Version 15.5 Binary Overlay
Determining Adaptive Server Version
Backing up Adaptive Server
Upgrading an Adaptive Server Configured with High Availability
Upgrading a High Availability-enabled Adaptive Server in an Active-active Configuration:
Upgrading High Availability-enabled Adaptive Servers in an Active-passive Configuration
Monitoring and Diagnostic (MDA) Tables
New Monitoring and Diagnostic Tables in Adaptive Server 15.5
Monitoring Remote Servers Using MDA Tables
Removing Adaptive Server
Uninstalling Adaptive Server 15.5
Removing an Existing Adaptive Server
Installer Changes from Adaptive Server 12.5.4 to Adaptive Server 15.5
Response File Change
Postinstallation Tasks
Verifying that the Servers Are Running
Verifying Your Connection to the Servers
Connecting to Adaptive Server Via Sybase Central
Setting the System Administrator Password
Installing Sample Databases
Default Devices for the Sample Databases
Running the Database Scripts
Installing the Interpubs Database
Installing the Jpubs Database
Maintaining Sample Databases
PC-Client Installation
System Requirements for PC-Client
Installing the Client
Creating a Response File
Interactive Installation Using a Response File
Installing the Client in Unattended (Silent) Mode
Getting Started After Installing
Configuring libtcl.cfg for LDAP
Using a Directory Service
Upgrade
Preparing to Upgrade
Preupgrade Tasks
Reserved Words
Running a Reserved Word Check
Addressing Reserved Words Conflicts
Using Quoted Identifiers
Preparing the Database and Devices for the Upgrade
Increasing Default Database Sizes
Upgrading to Adaptive Server 15.5
Using Sqlupgrade
Using Sqlupgraderes
Resource File Attributes for Upgrading Adaptive Server
Upgrading Using Sqlupgraderes
Enabling PCI in Adaptive Server
Postupgrade Tasks
Running the Instmsgs.ebf Script
Restoring Functionality in Adaptive Server
Reenabling Replication Server After the Dataserver Upgrade
Restore Replication on Destination Databases
Restoring Replication on Primary Databases
Reenabling Auditing
Special Considerations for Threshold Procedures for Audit Segments
Upgrading Backup and Monitor Servers
Upgrading Java in the Database
Enabling the Java in The Database Feature in a High Availability System
Upgrading Job Scheduler
Upgrading Job Scheduler Templates
Upgrading High Availability and Cluster Support
Migrating from 32-bit to 64-bit Versions on Different Computers
Migrating Data Using a Dump and Load Method
Using BCP to Migrate Data
Migrating Data by Replacing the Binary
Upgrading Using Allrows_dss In Parallel
Recovering From a Failed Upgrade
Downgrading from Adaptive Server 15.5
Post-downgrade Tasks
Additional Steps Required if You Used New Features
Downgrading Adaptive Server with Replicated Databases
Downgrading Job Scheduler
If You Downgraded Back to the Original Installation After Upgrading to 15.5
Troubleshoot SySAM
Where to Look For SySAM Related Errors
Troubleshooting SySAM
Enabling and Changing E-mail Notifications
Calling Sybase Technical Support
Troubleshoot the Server
Logs For Installation Utilities
Logs For Sybase Servers
Solutions to Common Installation Problems
Stopping Adaptive Server After a Failure
Recovering from a Failed Installation
If the Installation Quits While You Are Configuring Adaptive Server
If Adaptive Server Fails the Preupgrade Eligibility Test
Recovering from a Failed Upgrade
Restoring from Backup
If the Cause of the Failure Is Unknown
Re-running the Upgrade
If the Cause of the Failure Is Known
Upgrading Compiled Objects with Dbcc Upgrade_Object
Finding Compiled Object Errors Before Production
Quoted Identifier Errors
Select * Potential Problem Areas
Determining Whether Select * Should Be Changed in Views
Syntax for Using Dbcc Upgrade_object
Syntax Examples for Upgrading Compiled Objects
Increasing the Log Segment Size
Upgrading Using Dump and Load
Upgrading Compiled Objects in Database Dumps
Upgrading Servers With Replicated Databases
Determining If Existing Servers Contain 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
Using sybsystemprocs
Increasing the Size of the sybsystemprocs Database
Increasing Device and Database Capacity for System Procedures
Obtaining Help and Additional Information
Installation Guide for Linux
Adaptive Server Enterprise Architecture
Adaptive Server Enterprise Editions
Determining the Current Edition
Options for Adaptive Server Editions
Installation Workflows
Style Conventions
Server Component Descriptions and Directory Layout
PC-Client Product Descriptions and Directory Layout
Separately Installable PC-Client Products
Planning the Installation
Obtaining a License
Accessing SPDC
Generating a License at SPDC
Generating a Served License
Generating an Unserved License
Installing a New License Server
System Requirements for Servers
Sybase Support for OLE DB and ODBC
Memory Requirements
System Requirements for Clients
Preinstallation Tasks for the Server Installation
Obtaining Your Host ID
Preinstallation for an Overlay Installation
Installing Server Components
Installing in GUI mode
Installing From the Command Line
Creating a Response File
Interactive Installation Using a Response File
Installing Server in Unattended (silent) Mode
Installing the Adaptive Server Version 15.5 Binary Overlay
Determining Adaptive Server Version
Backing up Adaptive Server
Upgrading an Adaptive Server Configured with High Availability
Upgrading a High Availability-enabled Adaptive Server in an Active-active Configuration:
Upgrading High Availability-enabled Adaptive Servers in an Active-passive Configuration
Monitoring and Diagnostic (MDA) Tables
New Monitoring and Diagnostic Tables in Adaptive Server 15.5
Monitoring Remote Servers Using MDA Tables
Removing Adaptive Server
Uninstalling Adaptive Server 15.5
Removing an Existing Adaptive Server
Installer Changes from Adaptive Server 12.5.4 to Adaptive Server 15.5
Response File Change
Postinstallation Tasks
Verifying that the Servers Are Running
Verifying Your Connection to the Servers
Connecting to Adaptive Server Via Sybase Central
Setting the System Administrator Password
Installing Sample Databases
Default Devices for the Sample Databases
Running the Database Scripts
Installing the Interpubs Database
Installing the Jpubs Database
Maintaining Sample Databases
PC-Client Installation
System Requirements for PC-Client
Installing the Client
Creating a Response File
Interactive Installation Using a Response File
Installing the Client in Unattended (Silent) Mode
Getting Started After Installing
Configuring libtcl.cfg for LDAP
Using a Directory Service
Upgrade
Preparing to Upgrade
Preupgrade Tasks
Reserved Words
Running a Reserved Word Check
Addressing Reserved Words Conflicts
Using Quoted Identifiers
Preparing the Database and Devices for the Upgrade
Increasing Default Database Sizes
Upgrading to Adaptive Server 15.5
Using Sqlupgrade
Using Sqlupgraderes
Resource File Attributes for Upgrading Adaptive Server
Upgrading Using Sqlupgraderes
Enabling PCI in Adaptive Server
Postupgrade Tasks
Running the Instmsgs.ebf Script
Restoring Functionality in Adaptive Server
Reenabling Replication Server After the Dataserver Upgrade
Restore Replication on Destination Databases
Restoring Replication on Primary Databases
Reenabling Auditing
Special Considerations for Threshold Procedures for Audit Segments
Upgrading Backup and Monitor Servers
Upgrading Java in the Database
Enabling the Java in The Database Feature in a High Availability System
Upgrading Job Scheduler
Upgrading Job Scheduler Templates
Upgrading High Availability and Cluster Support
Migrating from 32-bit to 64-bit Versions on Different Computers
Migrating Data Using a Dump and Load Method
Using BCP to Migrate Data
Migrating Data by Replacing the Binary
Upgrading Using Allrows_dss In Parallel
Recovering From a Failed Upgrade
Downgrading from Adaptive Server 15.5
Post-downgrade Tasks
Additional Steps Required if You Used New Features
Downgrading Adaptive Server with Replicated Databases
Downgrading Job Scheduler
If You Downgraded Back to the Original Installation After Upgrading to 15.5
Troubleshoot SySAM
Where to Look For SySAM Related Errors
Troubleshooting SySAM
Enabling and Changing E-mail Notifications
Calling Sybase Technical Support
Troubleshoot the Server
Logs For Installation Utilities
Logs For Sybase Servers
Solutions to Common Installation Problems
Stopping Adaptive Server After a Failure
Recovering from a Failed Installation
If the Installation Quits While You Are Configuring Adaptive Server
If Adaptive Server Fails the Preupgrade Eligibility Test
Recovering from a Failed Upgrade
Restoring from Backup
If the Cause of the Failure Is Unknown
Re-running the Upgrade
If the Cause of the Failure Is Known
Upgrading Compiled Objects with Dbcc Upgrade_Object
Finding Compiled Object Errors Before Production
Quoted Identifier Errors
Select * Potential Problem Areas
Determining Whether Select * Should Be Changed in Views
Syntax for Using Dbcc Upgrade_object
Syntax Examples for Upgrading Compiled Objects
Increasing the Log Segment Size
Upgrading Using Dump and Load
Upgrading Compiled Objects in Database Dumps
Raw Partitions on Linux
Choosing a Raw Partition
Creating Raw Partitions
Red Hat Raw Device Administration
SuSE Raw Device Administration
Accessing Raw Devices From the Server
Upgrading Servers With Replicated Databases
Determining If Existing Servers Contain 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
Using sybsystemprocs
Increasing the Size of the sybsystemprocs Database
Increasing Device and Database Capacity for System Procedures
Obtaining Help and Additional Information
Installation Guide for Sun Solaris
Adaptive Server Enterprise Architecture
Adaptive Server Enterprise Editions
Determining the Current Edition
Options for Adaptive Server Editions
Installation Workflows
Style Conventions
Server Component Descriptions and Directory Layout
PC-Client Product Descriptions and Directory Layout
Separately Installable PC-Client Products
Planning the Installation
Obtaining a License
Accessing SPDC
Generating a License at SPDC
Generating a Served License
Generating an Unserved License
Installing a New License Server
System Requirements for Servers
Sybase Support for OLE DB and ODBC
Memory Requirements
System Requirements for Clients
Preinstallation Tasks for the Server Installation
Obtaining Your Host ID
Preinstallation for an Overlay Installation
Installing Server Components
Installing in GUI mode
Installing From the Command Line
Creating a Response File
Interactive Installation Using a Response File
Installing Server in Unattended (silent) Mode
Installing the Adaptive Server Version 15.5 Binary Overlay
Determining Adaptive Server Version
Backing up Adaptive Server
Upgrading an Adaptive Server Configured with High Availability
Upgrading a High Availability-enabled Adaptive Server in an Active-active Configuration:
Upgrading High Availability-enabled Adaptive Servers in an Active-passive Configuration
Monitoring and Diagnostic (MDA) Tables
New Monitoring and Diagnostic Tables in Adaptive Server 15.5
Monitoring Remote Servers Using MDA Tables
Removing Adaptive Server
Uninstalling Adaptive Server 15.5
Removing an Existing Adaptive Server
Installer Changes from Adaptive Server 12.5.4 to Adaptive Server 15.5
Response File Change
Postinstallation Tasks
Verifying that the Servers Are Running
Verifying Your Connection to the Servers
Connecting to Adaptive Server Via Sybase Central
Setting the System Administrator Password
Installing Sample Databases
Default Devices for the Sample Databases
Running the Database Scripts
Installing the Interpubs Database
Installing the Jpubs Database
Maintaining Sample Databases
PC-Client Installation
System Requirements for PC-Client
Installing the Client
Creating a Response File
Interactive Installation Using a Response File
Installing the Client in Unattended (Silent) Mode
Getting Started After Installing
Configuring libtcl.cfg for LDAP
Using a Directory Service
Upgrade
Preparing to Upgrade
Preupgrade Tasks
Reserved Words
Running a Reserved Word Check
Addressing Reserved Words Conflicts
Using Quoted Identifiers
Preparing the Database and Devices for the Upgrade
Increasing Default Database Sizes
Upgrading to Adaptive Server 15.5
Using Sqlupgrade
Using Sqlupgraderes
Resource File Attributes for Upgrading Adaptive Server
Upgrading Using Sqlupgraderes
Enabling PCI in Adaptive Server
Postupgrade Tasks
Running the Instmsgs.ebf Script
Restoring Functionality in Adaptive Server
Reenabling Replication Server After the Dataserver Upgrade
Restore Replication on Destination Databases
Restoring Replication on Primary Databases
Reenabling Auditing
Special Considerations for Threshold Procedures for Audit Segments
Upgrading Backup and Monitor Servers
Upgrading Java in the Database
Enabling the Java in The Database Feature in a High Availability System
Upgrading Job Scheduler
Upgrading Job Scheduler Templates
Upgrading High Availability and Cluster Support
Migrating from 32-bit to 64-bit Versions on Different Computers
Migrating Data Using a Dump and Load Method
Using BCP to Migrate Data
Migrating Data by Replacing the Binary
Upgrading Using Allrows_dss In Parallel
Recovering From a Failed Upgrade
Downgrading from Adaptive Server 15.5
Post-downgrade Tasks
Additional Steps Required if You Used New Features
Downgrading Adaptive Server with Replicated Databases
Downgrading Job Scheduler
If You Downgraded Back to the Original Installation After Upgrading to 15.5
Troubleshoot SySAM
Where to Look For SySAM Related Errors
Troubleshooting SySAM
Enabling and Changing E-mail Notifications
Calling Sybase Technical Support
Troubleshoot the Server
Logs For Installation Utilities
Logs For Sybase Servers
Solutions to Common Installation Problems
Stopping Adaptive Server After a Failure
Recovering from a Failed Installation
If the Installation Quits While You Are Configuring Adaptive Server
If Adaptive Server Fails the Preupgrade Eligibility Test
Recovering from a Failed Upgrade
Restoring from Backup
If the Cause of the Failure Is Unknown
Re-running the Upgrade
If the Cause of the Failure Is Known
Upgrading Compiled Objects with Dbcc Upgrade_Object
Finding Compiled Object Errors Before Production
Quoted Identifier Errors
Select * Potential Problem Areas
Determining Whether Select * Should Be Changed in Views
Syntax for Using Dbcc Upgrade_object
Syntax Examples for Upgrading Compiled Objects
Increasing the Log Segment Size
Upgrading Using Dump and Load
Upgrading Compiled Objects in Database Dumps
Upgrading Servers With Replicated Databases
Determining If Existing Servers Contain 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
Using sybsystemprocs
Increasing the Size of the sybsystemprocs Database
Increasing Device and Database Capacity for System Procedures
Obtaining Help and Additional Information
Installation Guide for Windows
Adaptive Server Enterprise Architecture
Adaptive Server Enterprise Editions
Determining the Current Edition
Options for Adaptive Server Editions
Installation Workflows
Style Conventions
Server Component Descriptions and Directory Layout
PC-Client Product Descriptions and Directory Layout
Separately Installable PC-Client Products
Planning the Installation
Obtaining a License
Accessing SPDC
Generating a License at SPDC
Generating a Served License
Generating an Unserved License
Installing a New License Server
System Requirements for Servers
Sybase Support for OLE DB and ODBC
Memory Requirements
Java in the Database Feature
Upgrading Windows to 3GB
System Requirements for Clients
Preinstallation Tasks for the Server Installation
Obtaining Your Host ID
Preinstallation for an Overlay Installation
Installing Server Components
Installing in GUI mode
Installing From the Command Line
Creating a Response File
Interactive Installation Using a Response File
Installing Server in Unattended (silent) Mode
Installing the Adaptive Server Version 15.5 Binary Overlay
Determining Adaptive Server Version
Backing up Adaptive Server
Upgrading an Adaptive Server Configured with High Availability
Upgrading a High Availability-enabled Adaptive Server in an Active-active Configuration:
Upgrading High Availability-enabled Adaptive Servers in an Active-passive Configuration
Monitoring and Diagnostic (MDA) Tables
New Monitoring and Diagnostic Tables in Adaptive Server 15.5
Monitoring Remote Servers Using MDA Tables
Removing Adaptive Server
Uninstalling Adaptive Server 15.5
Removing an Existing Adaptive Server
Installer Changes from Adaptive Server 12.5.4 to Adaptive Server 15.5
Response File Change
Postinstallation Tasks
Post Installation Instructions
Verifying that the Servers Are Running
Verifying Your Connection to the Servers
Connecting to Adaptive Server Via Sybase Central
Setting the System Administrator Password
Installing Sample Databases
Default Devices for the Sample Databases
Running the Database Scripts
Installing the Interpubs Database
Installing the Jpubs Database
Maintaining Sample Databases
PC-Client Installation
System Requirements for PC-Client
Installing the Client
Creating a Response File
Interactive Installation Using a Response File
Installing the Client in Unattended (Silent) Mode
Getting Started After Installing
Configuring libtcl.cfg for LDAP
Using a Directory Service
Adding a Server to the Interfaces File
Upgrade
Preparing to Upgrade
Preupgrade Tasks
Reserved Words
Running a Reserved Word Check
Addressing Reserved Words Conflicts
Using Quoted Identifiers
Preparing the Database and Devices for the Upgrade
Increasing Default Database Sizes
Upgrading to Adaptive Server 15.5
Upgrading the Server
Using Sqlupgrade
Using Sqlupgraderes
Resource File Attributes for Upgrading Adaptive Server
Upgrading Using Sqlupgraderes
Enabling PCI in Adaptive Server
Testing the Adaptive Server Upgrade
Postupgrade Tasks
Running the Instmsgs.ebf Script
Restoring Functionality in Adaptive Server
Reenabling Replication Server After the Dataserver Upgrade
Restore Replication on Destination Databases
Restoring Replication on Primary Databases
Reenabling Auditing
Special Considerations for Threshold Procedures for Audit Segments
Upgrading Backup and Monitor Servers
Upgrading Java in the Database
Enabling the Java in The Database Feature in a High Availability System
Upgrading Job Scheduler
Upgrading Job Scheduler Templates
Upgrading High Availability and Cluster Support
Migrating from 32-bit to 64-bit Versions on Different Computers
Migrating Data Using a Dump and Load Method
Using BCP to Migrate Data
Migrating Data by Replacing the Binary
Upgrading Using Allrows_dss In Parallel
Recovering From a Failed Upgrade
Downgrading from Adaptive Server 15.5
Post-downgrade Tasks
Additional Steps Required if You Used New Features
Downgrading Adaptive Server with Replicated Databases
Downgrading Job Scheduler
If You Downgraded Back to the Original Installation After Upgrading to 15.5
Troubleshoot SySAM
Where to Look For SySAM Related Errors
Troubleshooting SySAM
Enabling and Changing E-mail Notifications
Calling Sybase Technical Support
Troubleshoot the Server
Logs For Installation Utilities
Logs For Sybase Servers
Solutions to Common Installation Problems
Stopping Adaptive Server After a Failure
Recovering from a Failed Installation
If the Installation Quits While You Are Configuring Adaptive Server
If Adaptive Server Fails the Preupgrade Eligibility Test
Recovering from a Failed Upgrade
Restoring from Backup
If the Cause of the Failure Is Unknown
Re-running the Upgrade
If the Cause of the Failure Is Known
Upgrading Compiled Objects with Dbcc Upgrade_Object
Finding Compiled Object Errors Before Production
Quoted Identifier Errors
Select * Potential Problem Areas
Determining Whether Select * Should Be Changed in Views
Syntax for Using Dbcc Upgrade_object
Syntax Examples for Upgrading Compiled Objects
Increasing the Log Segment Size
Upgrading Using Dump and Load
Upgrading Compiled Objects in Database Dumps
Upgrading Servers With Replicated Databases
Determining If Existing Servers Contain 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
Using sybsystemprocs
Increasing the Size of the sybsystemprocs Database
Increasing Device and Database Capacity for System Procedures
Obtaining Help and Additional Information
Configuration Guide UNIX Adaptive Server Enterprise 15.5
About this book
Introduction
About Adaptive Server
System-specific issues
System user roles
Environment variables
Adaptive Server devices and system databases
The master device
The sybsystemdb device
The sysprocsdev device
Optional devices and databases
Using UNIX operating system files as database devices
Using the directio parameter
Using the dsync option
Determining the location, type, and size of a database device
For Solaris, HP-UX, IBM AIX, and Linux
For All Platforms
Client/server communication
Changing Adaptive Server configuration
Languages other than U.S. English
Adaptive Server specifications
Starting and Stopping Servers
Overview
Requirements for starting servers
Starting servers
Server start-up parameters
Changing start-up parameters
Using a RUN_server_name file
Using the startserver command
Using the monserver and backupserver commands
Starting servers when the operating system restarts
For HP-UX
For IBM RS/6000
For Sun Solaris and Linux
Starting XP Server after initial installation
Stopping servers
Stopping Adaptive Server
Stopping Backup Server
Stopping Monitor Server
Using the kill command
Shutdown and shared memory files
Huge pages on Linux p-series
Configuring the Operating System
Verifying environment variables
Configuring new servers with srvbuild or srvbuildres
Using the stty setting
Restoring correct permissions
File descriptors and user connections
For HP-UX
For AIX
For Linux
For Sun Solaris
Displaying current soft and hard limits
Increasing the soft limit
Increasing the hard limit
Sample program
Enabling asynchronous disk I/O
Adjusting the client connection timeout period
For HP-UX
For IBM RS/6000
For Sun Solaris
For Linux
Checking for hardware errors
For HP-UX
For IBM RS/6000
For Sun Solaris
For Linux
Monitoring the use of operating system resources
For HP-UX
For IBM RS/6000
For Sun Solaris and Linux
A sample C shell maintenance script
Adaptive Server Default Configuration
Default settings
Setting Up Communications Across the Network
How Adaptive Server determines which directory service entry to use
How a client uses directory services
Creating a directory services entry
Supported directory drivers
Contents of an interfaces file
Heterogeneous and homogeneous environments
Understanding the format of the interfaces file
Components of an interfaces file entry
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
IPv6 support
IPv6 infrastructure
Troubleshooting
Server fails to start
Error when executing an ESP
Using the Lightweight Directory Access Protocol as a Directory Service
Overview
LDAP directory services versus the Sybase interfaces file
The libtcl*.cfg file
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
Adaptive Server localization
Backup Server localization
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
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 files for database devices
Adding Optional Functionality to Adaptive Server
Adding auditing
Audit system devices and databases
Overview of audit installation
Preinstallation 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 Windows Adaptive Server Enterprise 15.5
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)
Changing Adaptive Server configuration
Support for high-availability products
Languages other than U.S. English
Adaptive Server specifications
Starting and Stopping Servers
Overview
Requirements for starting servers
Starting servers
Server start-up parameters
Changing start-up parameters
Starting and stopping servers using Unified Agent
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
Unified Agent
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
Configuring Job Scheduler and Self Management
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 Windows Registry as a directory service
Verifying server connections
Configuring ODBC connections
Configuring the ODBC driver
IPv6 support
Understanding IPv6
IPv6 infrastructure
Lightweight Directory Access Protocol in Adaptive Server
Overview
LDAP directory services versus the Sybase interfaces file
The libtcl.cfg file
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
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
Windows event-logging
Setting up Windows 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 Windows 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 Windows event log
In the Adaptive Server error log
Using Security Services with Windows LAN Manager
Security services with Windows 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 Windows computer
Overview of security features
Adaptive Server security
Combined Adaptive Server and Windows login security
Standard mode
Integrated mode
Mixed mode
Managing the login security features
Permitting trusted connections
Windows 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 Windows 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 Windows 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
Windows 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 Windows 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
New Features Summary
New Features in Adaptive Server® Version 15.5
Adaptive Server 15.5 Feature and Platform Matrix
In-Memory and Relaxed-Durability Databases
Faster Compression for Backups
Backup Server Support for the IBM® Tivoli® Storage Manager
Deferred Name Resolution for User-Defined Stored Procedures
FIPS 140-2 Login Password Encryption
Incremental Data Transfer
bigdatetime and bigtime Datatypes
Creating and Managing tempdb Groups
System Changes in Adaptive Server 15.5
Datatypes
Functions
System Stored Procedures
Commands
Configuration Parameters
Monitoring Tables
System Tables
Utilities
Auditing
New Features in Adaptive Server 15.0.3
SQL Statement Replication
Security Enhancements
LDAPS User Authentication Enhancement
Automatic LDAP User Authentication and Failback
Login Mapping of External Authentication
Using SSL to Specify a Common Name
Concurrent Kerberos Authentication
Virtually Hashed Tables
Huge Pages
Upgrading During a High Availability Configuration
Reinstalling System Stored Procedures
Distributed Transaction Management (DTM)
Adaptive Server Plug-in Updates
The Java Interface
System Changes in Adaptive Server 15.0.3
Functions
System Stored Procedures
Commands
Configuration Parameters
Monitoring Tables
System Tables
New Features in Adaptive Server 15.0.2
Encrypted Columns
Archive Database Access
Finding Slow-Running Queries
Deferred Compilation
Case-Insensitive Sort Orders for Chinese and Japanese Character Sets
Statistical Aggregate Functions
Standard Deviation and Variance
Eager and Lazy Aggregation
Vector and Scalar Aggregation
Improved Performance for Data Insertion
Using Asynchronous Writes During a Page Split
Improving Throughput of tempdb Transactions
Post-commit Optimization
Changes to the Query Processor
Deferred Compilation
Non-binary Character Set Histogram Interpolation
Expression Histogramming Selectivity Estimates
Viewing Current Optimizer Settings
New Security Features
PAM Support in 64-bit Adaptive Server on AIX
Global Login Triggers Set Automatically
SSL Support
Improved Password Security
Auditing Enhancements
Hiding System Stored Procedure and Command Password Parameters
Monitoring Failed Login Attempts
High Availability Considerations
Installing and Editing Monitoring Tables
Monitoring Tables for the Statement Cache
Row-Level Locking for System Tables
The xmltable() Function
Relocated Joins
User-Defined SQL Functions
instead of Triggers
System Changes in Adaptive Server 15.0.2
Trace Flags
Commands
Changes to the set Command
Utilities
System Stored Procedures
System Tables
Configuration Parameters
Functions
Global Variables
New Features in Adaptive Server 15.0.1
Changes to Abstract Plans
New Query-Level Settings
Operator Name Alignment for the Abstract Plan and the Optimizer Criteria
Extending the Optimizer Criteria Set Syntax
Literal Parameterization
System Changes in Adaptive Server 15.0.1
Functions
Configuration Parameters
Commands
Monitoring Tables
New Features in Adaptive Server 15.0
Partition Support
Row-Locked System Catalogs
Query Processor
Large Identifiers
Computed Columns
Differences Between Computed Columns and Function-Based Indexes
Differences Between Materialized and Not Materialized Computed Columns
Scrollable Cursors
unitext Datatype Support
big int Datatype Support
Unsigned Integer Datatype Support
Integer Identity
Enhancements to XML Services
Adaptive Server Plug-in Enhancements
Interactive SQL
User-Defined Web Services
Very Large Storage Support
Automatic Running of update statistics
SySAM License Management
Query Processing Metrics (qp Metrics)
Updates to Abstract Plans
showplan Changes
Secure Socket Layer Uses FIPS 140-2
System Changes in Adaptive Server 15.0
Utilities
Reserved Words
Global Variables
Configuration Parameters
Functions
Commands
System Stored Procedures
System Tables
Monitoring Tables
New Features in Adaptive Server 12.5.4
Kerberos Enhancements
LDAP User Authentication Enhancements
Password Complexity Enhancements
Archive Database Access Support
Shared Directory Changes
Sybase Driver Support
Dynamically Loaded TIBCO Libraries
JRE Support
Adaptive Server Plug-in Changes
Updating System Catalogs
Monitoring Tables Changes
syscomments Changes
Shared-Memory Support in Windows Terminal Server Environments
Global Login Trigger
Exporting set Options from a Login Trigger
Modulo Arithmetic for Numeric Datatypes
System Changes in Adaptive Server 12.5.4
Commands
Configuration parameters
Functions
Transact-SQL Commands
Obtaining Help and Additional Information
Active Messaging Users Guide Adaptive Server Enterprise 15.5
About this book
Introduction
Active Messaging concepts
Automatic decisions in real time
Messaging models
JMS
WebSphere MQ messaging models
Message format
JMS message properties
MQ message topics
Message selectors
Understanding Active Messaging
Sending and receiving messages from a queue
Publishing and consuming messages from a JMS topic
Working with message properties
Previewing the messaging interface
MQ overview
Securing channels with SSL
MQ publish/subscribe
Syntax for topics
Publisher and subscriber identities
MQ publish/subscribe examples
Working with MQ cluster queue objects
Working with remote queue objects�
Working with text messaging
Text messages and JMS
Text messages and MQ
Adaptive Server Cluster Edition support
Login redirection
Extended high availability
Internationalization support
Transactional message behavior
Transactional messaging set option
MQ security
Connecting to the MQ queue manager
Installing MQ client on Adaptive Server host machines
MQ authorizations
Querying MQ information
SQL Reference
Message-related global variables
<msgheader> and <msgproperties> documents
Adaptive Server-specific message properties
Keywords
Stored procedures
Built-in functions
Syntax segments
sp_configure 'enable real time messaging'
sp_engine
sp_msgadmin
msgconsume
msgpropcount
msgproplist
msgpropname
msgproptype
msgpropvalue
msgpublish
msgrecv
msgsend
msgsubscribe
msgunsubscribe
endpoint
option_string
sizespec
timespec
Samples
Sybase directories
Using code samples with SQL
Using code samples with Java/JDBC
Glossary
Component Integration Services Users Guide Adaptive Server Enterprise 15.5
About this book
Introduction
Understanding Component Integration Services
Basic concepts
Access methods
Server classes
Object types
Interface to remote servers
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
Server limits
create new proxy table
create existing proxy table
create proxy_table
alter table
select, insert, delete, update
RPC handling
sp_tables
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
File system access
Security considerations
Directory access
Recursion through subordinate directories
File access
Remote servers
Server class ASEnterprise
Server class ASAnywhere
Server class ASIQ
Server class direct_connect
Server class sds
Server class RPCServer
Connection management
Connecting to remote servers without the interfaces file
LDAP directory services
Secure communication with SSL
Trusted roots files
Security issues
Using encrypted columns in CIS
Remote server logins
Trusted mode
Connecting to Backup Server and XP Server
Mapping 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 plan generation
Component Integration Services remote location optimizer
Query plan execution
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
Distributed Transaction Management
Server classes and ASTC
DTM-enabled servers
Pre-DTM servers
strict DTM enforcement
enable xact coordination
Enable Component Integration Services
Transactional RPCs
Restrictions on transaction management
Adaptive Server to Adaptive Server update statistics
Limitations
Updating statistics on non-Adaptive Server backends
Java in the database
@@textsize
@@stringsize
Constraints on Java class columns
Error messages
Java abstract datatypes (ADTs)
Java class definitions
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, image, and unitext columns
Limits of @@textsize
Odd bytes padded
Converting text and image datatypes
Pattern matching with text and unitext data
Entering text and image values
readtext using bytes
text, image, and unitext with bulk copy
Error logging
text, unitext, and image data with server class ASEnterprise
text, image, and unitext data with server class direct_connect
Configuration and tuning
Using sp_configure
sysconfigures table
Changing the configuration parameters
Component Integration Services configuration parameters
Global variables for status
SQL Reference
dbcc commands
dbcc options
Trace flags
Functions
Support for functions within Component Integration Services
Aggregate functions
Datatype conversion functions
Date functions
Mathematical functions
Security functions
String functions
System functions
Text and image functions
Transact-SQL commands
alter table
case
connect to...disconnect
create existing table
create index
create table
delete
drop index
fetch
insert
readtext
select
truncate table
update
update statistics
writetext
Passthrough mode
connect to
sp_autoconnect
sp_passthru
sp_remotesql
Quoted identifier support
Delimited identifier support
auto identity option
Triggers
Tutorial
Getting started with Component Integration Services
Adding a remote server
Overview
Adding the remote server to the interfaces file
Creating server entries in system tables
Adding an alternate login and password
Verifying connectivity
Join between two remote tables
Adding the remote servers to the interfaces file
Defining the remote servers
Mapping the remote tables to Adaptive Server
Performing 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 Component Integration Services
If you need help
Glossary Adaptive Server Enterprise 15.5
Glossary
Historical Server Users Guide Adaptive Server Enterprise 15.5
About this book
Introduction
Description of Adaptive Server Enterprise Monitor
Adaptive Server Monitor components
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
Configuration procedures on UNIX platforms
Initial configuration on Windows platforms
Assumptions on Windows
Results of installation on Windows
Configuration procedures on Windows
Changing start-up parameters
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
Adding start-up parameters to the Windows Registry
Updating the Windows Registry services list
Adding connectivity information for Historical Server
Configuring Historical Server client machines
Creating 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 Windows
Starting Historical Server on Windows
Inferring start-up parameters from the Registry
Verifying that Historical Server is running
Stopping Historical Server on Windows
Who can shut down Historical Server on Windows
Determining current activity on Windows
Deferred versus immediate shutdown on Windows
Detailed shutdown procedures on Windows
Shutdown methods to avoid on Windows
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 and Output Options
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
Example
Cut utility example
Enabling Historical Server to output monitoring data to a database
Setting up the receiving Adaptive Server
Starting Historical Server
Viewing the data
Data storage
Deleting Historical Server sessions
Reporting errors
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
Java in Adaptive Server Enterprise Adaptive Server Enterprise 15.5
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
Storing Java classes as datatypes
Storing and querying XML in the database
Java components
Functional changes in Adaptive Server 15.0.3 and later
Changes in class distribution
The PCA/JVM runs in headless mode
Changes in memory management
Changes in ClassLoader behavior
Standards
Java in the database: questions and answers
What are the key features?
How are Java instructions stored in the database?
How is Java executed in the database?
Which Java Virtual Machines (JVMs) are supported?
What is headless mode?
What about JDBC?
How can Java and SQL be used together?
What is the Java API?
Which Java classes are supported in the Java API?
Can user-defined classes be installed in the database?
Can data be accessed using Java?
Can the same classes be used on the client and the server?
How to use Java classes in SQL
Where can information about Java in the database be found?
What you cannot do with Java in the database
Managing the Java Environment
Components of the Java environment
The JVM pluggable component
Pluggable component adapter JVM (PCA/JVM)
Pluggable component interface (PCI) and the PCI Bridge
The PCI memory pool
The sybpcidb database
How configuration values are organized in sybpcidb
When to change configuration values
Server-level options
Configuration options for the PCI Bridge
Configuration options for the PCA/JVM
Changing configuration values in a running server
Changing configuration values by restarting Adaptive Server
Changing configuration values before the JVM is initialized
Changing configuration values after the JVM is initialized
Restoring default configuration values to sybpcidb
Using monitor tables to display information about the PCI Bridge
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 JVM
Enabling Java
Installing Java classes in the database
Using installjava
Installing uncompressed JARs
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
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
Deterministic Java functions in expressions
Static variables in Java-SQL classes
Changes for static variables for Adaptive Server 15.0.3 and later
Changes for static variables for the Cluster Edition
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 updater( ) method
Executing a select statement: the selecter( ) method
Calling a SQL stored procedure: the caller( ) method
Error handling in the native JDBC driver
The JDBCExamples class
The main( ) method
The serverMain( ) method
The connecter( ) method
The doAction( ) method
The doSQL( ) method
The updater( ) method
The selecter( ) method
The caller( ) method
SQLJ Functions and Stored Procedures
Overview
Compliance with SQLJ Part 1 specifications
General issues
Security and permissions
SQLJ Examples
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
Debugging Java in the Database
Supported Java debuggers
Setting up Java debugging
Configuring the server to support debugging
Attaching the remote debugger to the JVM debug agent
File and Network Access Using Java
File access using java.io
User identity and permissions
Specifying directories for file I/O: UNIX platforms
Mask syntax
Examples
Specifying directories for file I/O: Windows platforms
Mask syntax
Examples
File I/O changes
Rules for opening existing files
UNIX platforms
Windows platforms
Rules for creating files with a file open operation
Final file check
File access using java.net
Examples
Using socket classes
Using the URL classes
Additional Topics
JDK requirement for Java classes in the server
Assignments
Assignment rules at compile-time
Assignment rules at runtime
Allowed conversions
Transferring Java-SQL objects to clients
Suggestions for improving performance
Minimize the number of calls from SQL to the JVM
Use the java.lang.Thread class with care
Determine if you are running within the PCA/JVM
Avoid SQL loops in a multi-engine environment
Controlling access to native methods in the PCA/JVM
Unsupported Java API packages, classes, and methods
Restricted Java packages, classes, and methods
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
Job Scheduler Users Guide Adaptive Server Enterprise 15.5
About this book
Overview
Introduction
Terminology and concepts
Job Scheduler components and functionality
Job Scheduler architecture
Security
js_user_role
js_admin_role
js_sa_role
Shared objects
Configuring and Running Job Scheduler
Installing Job Scheduler manually
Setting up Job Scheduler users
Setting up access to target servers
Using Templates to Create Jobs
Introduction
Installing stored procedures on a target server
Installing the templates into Job Scheduler
Using Job Scheduler templates
Backup
Statistics management
Reorganization
Reconfiguration
Template updates
Using Job Scheduler at the Command Line
Creating a job
Job state codes
Creating a schedule
Creating a scheduled job
Deleting a scheduled job
Modifying a scheduled job
Invoking stored procedures on a target server
Managing jobs
Managing job history and logs
Command Reference
Command line stored procedures
Command syntax
sp_sjobcreate
sp_sjobcmd
sp_sjobmodify
sp_sjobdrop
sp_sjobhelp
sp_sjobcontrol
sp_sjobhistory
Managing Job Scheduler in the Sybase Central ASE Plug-in
Adding a scheduled job
Adding a job
Adding a schedule
Scheduling an existing job
Viewing job history
Purging job history
Administering Job Scheduler
Showing all users
Managing a scheduled job
Editing properties
Deleting a Job Scheduler object
Troubleshooting
Logging error messages
A job fails to run at the time you scheduled it
A scheduled job created from a template fails
A job with multiple calls to sp_sjobcmd fails
A stored procedure fails
Migration Technology Guide Adaptive Server Enterprise 15.5
About this book
Migration Strategy
Preupgrade considerations
Understanding optimization goals
Optimization criteria
Resource recommendations for Adaptive Server 15.0
Incorporating statistics in Adaptive Server 15.0
Recommended testing before upgrade
Migrating to Adaptive Server 15.0 features
Upgrading, and using new features immediately
Upgrading, and using new features later
Upgrading, but not using new features
Troubleshooting
Query processing tips
Information to capture before contacting Technical Support
701 errors
Performance problems with a limited number of queries
System-wide performance issues
Uploading diagnostics to Technical Support
QPTune
Setting up your system
Using QPTune to fix missing statistics
Starting QPTune to fix missing statistics
Collecting statistics
Fixing statistics
Using undo_fix_stats
Using QPTune to tune queries or applications
Starting QPTune to tune queries or applications
Simple start
Custom start
Collecting metrics
Comparing metrics
Applying the best results
Configuration file
Examples
Upgrade issues
Localization
QPTune GUI
Environment and system requirements
Starting the QPTune GUI
Fixing missing statistics
Tuning Task
QPTune reference information
Running the Query Processor in Compatibility Mode
Enabling compatibility mode
Feature support in compatibility mode
Additional trace flag for diagnostics
New stored procedure sp_compatmode
Changes to @@qpmode global variable
Diagnostic tool
Monitor Client Library Programmers Guide Adaptive Server Enterprise 15.5
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: Defining error handling
Step 2: Connecting to a server
Allocating a connection structure
Setting connection structure properties
Required connection properties
Connecting to a server
Step 3: creating a view
Data items
Statistical types
Creating views for a connection
Step 4: Creating filters
Step 5: Setting alarms
Step 6: Requesting performance data and process results
Step 7: closing and deallocating 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
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 InstallShield
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, as Adaptive Server version 11.5
Rules for functions and callbacks compatibility
Troubleshooting Information and Error Messages
Troubleshooting
Confusing messages from Adaptive Server
View refreshes fail
Negative numbers as object IDs
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 Server Users Guide Adaptive Server Enterprise 15.5
About this book
Introduction
Description of Adaptive Server Enterprise Monitor
Monitor components
Monitor architecture
Configuring Monitor Server
Initial configuration on UNIX platforms
Assumptions on UNIX platforms
Results of installation on UNIX platforms
Configuration procedures on UNIX platforms
Initial configuration on Windows platforms
Assumptions on Windows platofrms
Results of installation on Windows
Configuration procedures on Windows platforms
Setting up the automatic start-up service
Setting up the server start-up order
Changing the default configuration
Changing start-up information in the 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
Starting Monitor Server on Windows
How start-up parameters are inferred on Windows
Verifying that Monitor Server is running on Windows
Stopping Monitor Server on Windows
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 Registry services entry (Windows only)
Client errors
Maximum connection errors
ct_netlib errors (Windows)
Messages in Adaptive Server error log
Monitoring Tables Diagram (poster)
Quick Reference Guide Adaptive Server Enterprise 15.5
Quick Reference Guide
Datatypes
Datatypes and encrypted columns
Standards and compliance
Adaptive Server global variables
Reserved words
Transact-SQL reserved words
ANSI SQL reserved words
Potential ANSI SQL reserved words
Functions
Commands
Interactive dbsql commands
System procedures
Catalog stored procedures
Extended stored procedures
dbcc stored procedures
System tables
DBCC tables
Monitoring tables
Utilities
Reference Manual: Building Blocks Adaptive Server Enterprise 15.5
About this book
System and User-Defined Datatypes
Datatype categories
Range and storage size
Datatypes of columns, variables, or parameters
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
Numeric literals
Character literals
Datatypes of mixed-mode expressions
Determining the datatype hierarchy
Determining precision and scale
Datatype conversions
Automatic conversion of fixed-length NULL columns
Handling overflow and truncation errors
Standards and compliance
Exact numeric datatypes
Integer types
Decimal datatypes
Standards and compliance
Approximate numeric datatypes
Understanding approximate numeric datatypes
Range, precision, and storage size
Entering approximate numeric data
NaN and Inf values
Standards and compliance
Money datatypes
Accuracy
Range and storage size
Entering monetary values
Standards and compliance
Timestamp datatype
Creating a timestamp column
Date and time datatypes
Range and storage requirements
Entering date and time data
Standards and compliance
Character datatypes
unichar, univarchar
Length and storage size
Entering character data
Entering Unicode characters
Treatment of blanks
Manipulating character data
Standards and compliance
Binary datatypes
Valid binary and varbinary entries
Entries of more than the maximum column size
Treatment of trailing zeros
Platform dependence
Standards and compliance
bit datatype
Standards and compliance
sysname and longsysname datatypes
Standards and compliance
text, image, and unitext datatypes
Data structures used for storing text, unitext, and image data
Initializing text, unitext, and image columns
Defining unitext columns
Saving space by allowing NULL
Getting information from sysindexes
Using readtext and writetext
Determining how much space a column uses
Restrictions on text, image, and unitext columns
Selecting text, unitext, and image data
Converting text and image datatypes
Converting to or from unitext
Pattern matching in text data
Duplicate rows
Standards and compliance
Datatypes and encrypted columns
User-defined 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
Statistical aggregate functions
Standard deviation and variance
Statistical aggregates
Datatype conversion functions
Converting character data to a noncharacter type
Converting from one character type to another
Converting numbers to a character type
Rounding during conversion to and from money types
Converting date and 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, unitext, and image columns
Text and image functions
User-defined SQL functions
abs
acos
ascii
asehostname
asin
atan
atn2
avg
audit_event_name
authmech
biginttohex
bintostr
cache_usage
case
cast
ceiling
char
char_length
charindex
coalesce
col_length
col_name
compare
convert
cos
cot
count
count_big
current_bigdatetime
current_bigtime
current_date
current_time
curunreservedpgs
data_pages
datachange
datalength
dateadd
datediff
datename
datepart
day
db_attr
db_id
db_instanceid
db_name
db_recovery_status
degrees
derived_stat
difference
exp
floor
get_appcontext
getdate
getutcdate
has_role
hash
hashbytes
hextobigint
hextoint
host_id
host_name
instance_id
identity_burn_max
index_col
index_colorder
index_name
inttohex
isdate
isnumeric
is_quiesced
is_sec_service_on
isnull
isnumeric
instance_name
lc_id
lc_name
lct_admin
left
len
license_enabled
list_appcontext
lockscheme
log
log10
lower
ltrim
max
min
month
mut_excl_roles
newid
next_identity
nullif
object_attr
object_id
object_name
object_owner_id
pagesize
partition_id
partition_name
partition_object_id
patindex
pi
power
proc_role
pssinfo
radians
rand
rand2
replicate
reserve_identity
reserved_pages
reverse
right
rm_appcontext
role_contain
role_id
role_name
round
row_count
rtrim
sdc_intempdbconfig
set_appcontext
show_role
show_sec_services
sign
sin
sortkey
soundex
space
spid_instance_id
square
sqrt
stddev
stdev
stdevp
stddev_pop
stddev_samp
str
str_replace
strtobin
stuff
substring
sum
suser_id
suser_name
syb_quit
syb_sendmsg
sys_tempdbid
tan
tempdb_id
textptr
textvalid
to_unichar
tran_dumpable_status
tsequal
uhighsurr
ulowsurr
upper
uscalar
used_pages
user
user_id
user_name
valid_name
valid_user
var
var_pop
var_samp
variance
varp
workload_metric
xa_bqual
xa_gtrid
xmltable
year
Global Variables
Adaptive Server global variables
Using global variables in a clustered environment
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
Short 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
ANSI SQL reserved words
Potential ANSI SQL 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
Reference Manual: Commands Adaptive Server Enterprise 15.5
About this book
Commands
Overview
alter database
alter encryption key
alter role
alter table
begin...end
begin transaction
break
checkpoint
close
commit
compute clause
connect to...disconnect
continue
create archive database
create database
create default
create encryption key
create existing table
create function
create function (SQLJ)
create index
create plan
create procedure
create procedure (SQLJ)
create proxy_table
create role
create rule
create schema
create service
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 resize
disk unmirror
drop database
drop default
drop encryption key
drop function
drop function (SQLJ)
drop index
drop procedure
drop role
drop rule
drop service
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
mount
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
transfer table
truncate table
union operator
unmount
update
update all statistics
update index statistics
update statistics
update table statistics
use
waitfor
where clause
while
writetext
Interactive SQL Commands
clear
configure
connect
disconnect
exit
input
output
parameters
read
set connection
set option
start logging
stop logging
system
Reference Manual: Procedures Adaptive Server Enterprise 15.5
About this book
System Procedures
Introduction to system procedures
Return values for system procedures
Permissions on system procedures
Auditing system procedures
Executing system procedures
Entering parameter values
Messages
System procedure tables
List of system procedures
sp_activeroles
sp_add_qpgroup
sp_add_resource_limit
sp_add_time_range
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_addremotelogin
sp_addsegment
sp_addserver
sp_addthreshold
sp_addtype
sp_addumpdevice
sp_adduser
sp_altermessage
sp_audit
sp_autoconnect
sp_autoformat
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_cleanpwdchecks
sp_clearpsexe
sp_clearstats
sp_client_addr
sp_clusterlockusage
sp_cluster
sp_cmp_all_qplans
sp_cmp_qplans
sp_commonkey
sp_companion
sp_compatmode
sp_configure
sp_copy_all_qplans
sp_copy_qplan
sp_countmetadata
sp_cursorinfo
sp_dbextend
sp_dboption
sp_dbrecovery_order
sp_dbremap
sp_defaultloc
sp_deletesmobj
sp_depends
sp_deviceattr
sp_diskdefault
sp_displayaudit
sp_displaylevel
sp_displaylogin
sp_displayroles
sp_downgrade
sp_dropalias
sp_drop_all_qplans
sp_drop_qpgroup
sp_drop_qplan
sp_drop_resource_limit
sp_drop_time_range
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_dropremotelogin
sp_droprowlockpromote
sp_dropsegment
sp_dropserver
sp_dropthreshold
sp_droptype
sp_dropuser
sp_dumpoptimize
sp_encryption
sp_engine
sp_estspace
sp_export_qpgroup
sp_extendsegment
sp_extengine
sp_extrapwdchecks
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_help_resource_limit
sp_help_qpgroup
sp_help_qplan
sp_helpapptrace
sp_helpartition
sp_helpcache
sp_helpcomputedcolumn
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_helpremotelogin
sp_helprotect
sp_helpsegment
sp_helpserver
sp_helpsort
sp_helptext
sp_helpthreshold
sp_helpuser
sp_hidetext
sp_import_qpgroup
sp_indsuspect
sp_jreconfig
sp_ldapadmin
sp_listener
sp_listsuspect_db
sp_listsuspect_object
sp_listsuspect_page
sp_lmconfig
sp_lock
sp_locklogin
sp_logdevice
sp_loginconfig
sp_logininfo
sp_logiosize
sp_logintrigger
sp_maplogin
sp_metrics
sp_modify_resource_limit
sp_modify_time_range
sp_modifylogin
sp_modifystats
sp_modifythreshold
sp_monitor
sp_monitorconfig
sp_object_stats
sp_options
sp_passthru
sp_password
sp_passwordpolicy
sp_pciconfig
sp_placeobject
sp_plan_dbccdb
sp_poolconfig
sp_post_xpload
sp_primarykey
sp_processmail
sp_procxmode
sp_querysmobj
sp_recompile
sp_refit_admin
sp_remap
sp_remoteoption
sp_remotesql
sp_rename
sp_rename_qpgroup
sp_renamedb
sp_reportstats
sp_revokelogin
sp_role
sp_sendmsg
sp_serveroption
sp_set_qplan
sp_setlangalias
sp_setpglockpromote
sp_setpsexe
sp_setrowlockpromote
sp_setsuspect_granularity
sp_setsuspect_threshold
sp_setup_table_transfer
sp_show_options
sp_showcontrolinfo
sp_showexeclass
sp_showplan
sp_showpsexe
sp_spaceusage
sp_spaceused
sp_ssladmin
sp_syntax
sp_sysmon
sp_tab_suspectptn
sp_tempdb
sp_tempdb_markdrop
sp_thresholdaction
sp_tran_dumpable_status
sp_transactions
sp_unbindcache
sp_unbindcache_all
sp_unbindefault
sp_unbindexeclass
sp_unbindmsg
sp_unbindrule
sp_version
sp_volchanged
sp_webservices
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_exclusions
sp_dbcc_faultreport
sp_dbcc_fullreport
sp_dbcc_help_fault
sp_dbcc_patch_finishtime
sp_dbcc_recommendations
sp_dbcc_runcheck
sp_dbcc_statisticsreport
sp_dbcc_summaryreport
sp_dbcc_updateconfig
Reference Manual: Tables Adaptive Server Enterprise 15.5
About this book
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
Using system tables in the Cluster Edition
timestamp columns
Changed identity values
Controlling fake-table materialization
Rules for using system tables
Permissions on system tables
Locking schemes used for system tables
Reserved columns
Updating system tables
Triggers on system tables
syblicenseslog
sysalternates
sysaltusages
sysattributes
sysauditoptions
sysaudits_01 – sysaudits_08
syscharsets
syscolumns
syscomments
sysconfigures
sysconstraints
syscoordinations
syscurconfigs
sysdatabases
sysdepends
sysdevices
sysencryptkeys
sysengines
sysgams
sysindexes
sysinstances
sysjars
syskeys
syslanguages
syslisteners
syslocks
sysloginroles
syslogins
syslogs
syslogshold
sysmessages
sysmonitors
sysobjects
sysoptions
syspartitionkeys
syspartitions
sysprocedures
sysprocesses
sysprotects
sysquerymetrics
sysqueryplans
sysreferences
sysremotelogins
sysresourcelimits
sysroles
syssecmechs
syssegments
sysservers
syssessions
sysslices
syssrvroles
sysstatistics
systabstats
systhresholds
systimeranges
systransactions
systypes
sysusages
sysusermessages
sysusers
sysxtypes
dbccdb Tables
dbccdb workspaces
dbccdb log
dbcc_config
dbcc_counters
dbcc_exclusions
dbcc_fault_params
dbcc_faults
dbcc_operation_log
dbcc_operation_results
dbcc_types
Monitoring Tables
monCachedObject
monCachePool
monCachedProcedures
monCachedStatement
monCIPC
monCIPCEndpoints
monCIPCLinks
monCIPCMesh
monCLMObjectActivity
monClusterCacheManager
monCMSFailover
monDataCache
monDBRecovery
monDBRecoveryLRTypes
monDeadLock
monDeviceIO
monEngine
monErrorLog
monFailoverRecovery
monIOQueue
monLicense
monLocks
monLogicalCluster
monLogicalClusterAction
monLogicalClusterInstance
monLogicalClusterRoute
monNetworkIO
monOpenDatabases
monOpenObjectActivity
monOpenPartitionActivity
monPCIBridge
monPCIEngine
monPCISlots
monPCM
monProcedureCache
monProcedureCacheMemoryUsage
monProcedureCacheModuleUsage
monProcess
monProcessActivity
monProcessLookup
monProcessMigration
monProcessNetIO
monProcessObject
monProcessProcedures
monProcessSQLText
monProcessStatement
monProcessWaits
monProcessWorkerThread
monState
monStatementCache
monSysLoad
monSysPlanText
monSysSQLText
monSysStatement
monSysWaits
monSysWorkerThread
monTableColumns
monTableParameters
monTables
monTableTransfer
monTempdbActivity
monWaitClassInfo
monWaitEventInfo
monWorkload
monWorkloadPreview
monWorkloadProfile
monWorkloadRaw
sybpcidb Tables
pca_jre_arguments
pca_jre_directives
pci_arguments
pci_directives
pci_slotinfo
pci_slot_syscalls
System Administration Guide: Volume 1 Adaptive Server Enterprise 15.5
About this book
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
sybmgmtdb database
pubs2 and pubs3 sample databases
Maintaining the sample databases
pubs2 image data
dbccdb database
sybdiag database
Determining the version of the installation scripts
System Administration for Beginners
Logical page sizes
Using “test” servers
Planning resources
Achieving performance goals
Considerations when 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 versus 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
Additional resources
Introduction to the Adaptive Server Plug-in for Sybase Central
Overview for Adaptive Server Sybase Central Plug-in
Adaptive Server plug-In and command line updates
Using the Adaptive Server Plug-in
Starting and stopping Sybase Central
Registering Adaptive Server Plug-in
Performing common tasks
Using Interactive SQL
Starting Interactive SQL
Setting Configuration Parameters
Overview
The Adaptive Server configuration file
Modifying configuration parameters
Required roles for modifying configuration parameters
Unit specification using sp_configure
Getting help information on configuration parameters
Using sp_configure
Syntax elements
Parameter parsing
Using sp_configure with a configuration file
Naming tips for the configuration file
Using sp_configure to read or write the configuration file
Editing the configuration file
Starting Adaptive Server with a configuration file
The parameter hierarchy
User-defined subsets of the parameter hierarchy: display levels
The effect of the display level on sp_configure output
Performance tuning with sp_configure and sp_sysmon
Using configuration parameters in a clustered environment
sp_configure output
Named cache configuration parameters
sysconfigures and syscurconfigs tables
Querying syscurconfigs and sysconfigures: an example
Configuration parameters
Alphabetical listing of configuration parameters
abstract plan cache
abstract plan dump
abstract plan load
abstract plan replace
additional network memory
allocate max shared memory
allow backward scans
allow nested triggers
allow procedure grouping
allow remote access
allow resource limits
allow sendmsg
allow sql server async i/o
allow updates to system tables
average cap size
audit queue size
auditing
automatic cluster takeover
builtin date strings
caps per ccb
check password for digit
CIPC large message pool size
CIPC regular message pool size
cis bulk insert array size
cis bulk insert batch size
cis connect timeout
cis cursor rows
cis idle connection timeout
cis packet size
cis rpc handling
cluster heartbeat interval
cluster heartbeat retries
cluster vote timeout
compression memory size
configuration file
cost of a logical io
cost of a physical io
cost of a cpu unit
cpu accounting flush interval
cpu grace time
current audit table
deadlock checking period
deadlock pipe active
deadlock pipe max messages
deadlock retries
default character set id
default database size
default exp_row_size percent
default fill factor percent
default language id
default network packet size
default sortorder id
default unicode sortorder
default XML sortorder
deferred name resolution
disable character set conversions
disable disk mirroring
disk i/o structures
DMA object pool size
dtm detach timeout period
dtm lock timeout period
dump on conditions
dynamic allocation on demand
enable backupserver HA
enable cis
enable DTM
enable encrypted columns
enable enterprise java beans
enable file access
enable full-text search
enable HA
enable housekeeper GC
enable i/o fencing
enable java
enable job scheduler
enable ldap user auth
enable literal autoparam
enable logins during recovery
enable merge join
enable metrics capture
enable monitoring
enable pam user auth
enable pci
enable query tuning mem limit
enable query tuning time limit
enable real time messaging
enable rep agent threads
enable row level access control
enable semantic partitioning
enable sort-merge join and JTC
enable sql debugger
enable ssl
enable stmt cache monitoring
enable surrogate processing
enable unicode conversion
enable unicode normalization
enable webservices
enable xact coordination
enable xml
engine memory log size
errorlog pipe active
errorlog pipe max messages
esp execution priority
esp execution stacksize
esp unload dll
event buffers per engine
event log computer name (Windows only)
event logging (Windows only)
executable codesize + overhead
extended cache size
FIPS login password encryption
global async prefetch limit
global cache partition number
heap memory per user
histogram tuning factor
housekeeper free write percent
i/o accounting flush interval
i/o batch size
i/o polling process count
identity burning set factor
identity grab size
identity reservation size
idle migration timeout
job scheduler interval
job scheduler tasks
license information
lock address spinlock ratio
lock hashtable size
lock scheme
lock shared memory
lock spinlock ratio
lock table spinlock ratio
lock wait period
log audit logon failure
log audit logon success
max async i/os per engine
max async i/os per server
max cis remote connections
max concurrently recovered db
max memory
max native threads per engine
max nesting level
max network packet size
max number network listeners
max online engines
max online Q engines
max parallel degree
max pci slots
max query parallel degree
max repartition degree
max resource granularity
max scan parallel degree
max SQL text monitored
max transfer history
maximum dump conditions
max buffers per lava operator
maximum failed logins
maximum job output
memory alignment boundary
memory per worker process
messaging memory
metrics elap max
metrics exec max
metrics lio max
metrics pio max
min pages for parallel scan
minimum password length
mnc_full_index_filter
msg confidentiality reqd
msg integrity reqd
net password encryption required
number of alarms
number of aux scan descriptors
number of backup connections
number of ccbs
number of checkpoint tasks
number of devices
number of dtx participants
number of dump threads
number of engines at startup
number of histogram steps
number of index trips
number of java sockets
number of large i/o buffers
number of locks
number of mailboxes
number of messages
number of oam trips
number of open databases
number of open indexes
number of open objects
number of open partitions
number of pre-allocated extents
number of Q engines at startup
number of remote connections
number of remote logins
number of remote sites
number of sort buffers
number of user connections
number of worker processes
o/s file descriptors
object lockwait timing
open index hash spinlock ratio
open index spinlock ratio
open object spinlock ratio
optimization goal
optimization timeout limit
page lock promotion HWM
page lock promotion LWM
page lock promotion PCT
page utilization percent
partition groups
partition spinlock ratio
pci memory size
per object statistics active
percent database for history
percent database for output
percent history free
percent output free
performance monitoring option
permission cache entries
plan text pipe active
plan text pipe max messages
print deadlock information
print recovery information
procedure cache size
procedure deferred compilation
process wait events
prod-consumer overlap factor
quorum heartbeat interval
quorum heartbeat retries
read committed with lock
recovery interval in minutes
remote server pre-read packets
restricted decrypt permission
row lock promotion HWM
row lock promotion LWM
row lock promotion PCT
rtm thread idle wait period
runnable process search count
sampling percent
secure default login
select on syscomments.text
send doneinproc tokens
session migration timeout
session tempdb log cache size
shared memory starting address
size of auto identity column
size of global fixed heap
size of process object heap
size of shared class heap
size of unilib cache
sproc optimize timeout limit
SQL batch capture
SQL Perfmon Integration (Windows only)
sql server clock tick length
sql text pipe active
sql text pipe max messages
stack guard size
stack size
start mail session (Windows only)
start xp server during boot
startup delay
statement cache size
statement pipe active
statement pipe max messages
statement statistics active
strict dtm enforcement
suspend audit when device full
syb_sendmsg port number
sysstatistics flush interval
systemwide password expiration
tape retention in days
tcp no delay
text prefetch size
time slice
total data cache size
total logical memory
total physical memory
transfer utility memory size
txn to pss ratio
unified login required
upgrade version
use security services
user log cache size
user log cache spinlock ratio
wait event timing
workload manager cache size
xact coordination interval
xp_cmdshell context
Overview of Disk Resource Issues
Device allocation and object placement
Commands for managing disk resources
Considerations in storage management decisions
Recovery
Keeping logs on a separate device
Mirroring
Performance
Status and defaults at installation time
System tables that manage storage
The sysdevices table
The sysusages table
The syssegments table
The sysindexes table
The syspartitions table
Initializing Database Devices
Database devices
Using the disk init command
disk init syntax
Specifying a logical device name
Specifying a physical device name
Choosing a device number
Specifying the device size
Specifying the dsync setting (optional)
Performance implications of dsync
Limitations and restrictions of dsync
Using directio to bypass operating system buffer
Other optional parameters for disk init
Getting information about devices
Dropping devices
Designating default devices
Choosing default and nondefault devices
Increasing the size of devices with disk resize
Insufficient disk space
Setting Database Options
Using the sp_dboption procedure
Database option descriptions
Viewing the options on a database
Configuring Character Sets, Sort Orders, and Languages
Understanding internationalization and localization
Advantages of internationalized systems
A sample internationalized system
Elements of an internationalized system
Selecting the character set for your server
Unicode
Character set installation
Configuration parameters
Functions
Using unichar columns
Using unitext
Open Client interoperability
Java interoperability
Limitations
Selecting the server default character set
Selecting the sort order
Using sort orders
Different types of sort orders
Selecting the default sort order
Chinese Pinyin sort order
Selecting case-insensitive sort orders for Chinese and Japanese character sets
Selecting the default Unicode sort order
Selecting a language for system messages
Setting up your server: examples
A Spanish-version server
A U.S.-based company in Japan
A Japan-based company with multinational clients
Changing the character set, sort order, or message language
Changing the default character set
Changing the sort order with a resources file
Changing the default sort order
Reconfiguring the character set, sort order, or message language
Unicode examples
Schema
Converting to UTF-8
Migrating selected columns to unichar
Migrating to or from unitext
Preliminary steps
Setting the user’s default language
Recovery after reconfiguration
Using sp_indsuspect to find corrupt indexes
Rebuilding indexes after changing the sort order
Upgrading text data after changing character sets
Retrieving text values after changing character sets
Handling suspect partitions
Fixing tables with suspect partitions
Handling suspect partitions in cross-platform dump and load operations
Installing date strings for unsupported languages
Server versus client date interpretation
Internationalization and localization files
Types of internationalization files
Character sets directory structure
Types of localization files
Software messages directory structure
Message languages and global variables
Configuring Client/Server Character Set Conversions
Character set conversion
Supported character set conversions
Conversion for native character sets
Conversion in a Unicode system
Adaptive Server direct conversions
Unicode conversions
Choosing a conversion type
Non-Unicode client/server systems
Unicode client/server systems
Configuring the server
Enabling and disabling character set conversion
Characters that cannot be converted
Error handling in character set conversion
Conversions and changes to data lengths
Configuring your system and application
Specifying the character set for utility programs
Display and file character set command line options
Setting the display character set
Setting the file character set
Diagnosing System Problems
How Adaptive Server uses error messages
Error messages and message numbers
Variables in error message text
Adaptive Server error logging
Error log format
Severity levels
Severity levels 10 – 18
Level 10: Status information
Level 11: Specified database object not found
Level 12: Wrong datatype encountered
Level 13: User transaction syntax error
Level 14: Insufficient permission to execute command
Level 15: Syntax error in SQL statement
Level 16: Miscellaneous user error
Level 17: Insufficient resources
Level 18: Nonfatal internal error detected
Severity levels 19 – 26
Level 19: Adaptive Server fatal error in resource
Level 20: Adaptive Server fatal error in current process
Level 21: Adaptive Server fatal error in database processes
Level 22: Adaptive Server fatal error: Table integrity suspect
Level 23: Fatal error: Database integrity suspect
Level 24: Hardware error or system table corruption
Level 25: Adaptive Server internal error
Level 26: Rule error
Reporting errors
Backup Server error logging
Killing processes
Using kill with statusonly
Using sp_lock to examine blocking processes
Housekeeper functionality
Housekeeper wash
Housekeeper chores
Housekeeper garbage collection
Running at user priority
Configuring enable housekeeper GC
Using the reorg command
Configuring Adaptive Server to save SQL batch text
Allocating memory for batch text
Configuring the amount of SQL text retained in memory
Enabling Adaptive Server to start saving SQL text
SQL commands not represented by text
Viewing the query plan of a SQL statement
Viewing previous statements
Viewing a nested procedure
Shutting down servers
Shutting down Adaptive Server
Shutting down a Backup Server
Checking for active dumps and loads
Using nowait on a Backup Server
Learning about known problems
Introduction to Security
Introduction to security
What is “information security?”
Information security standards
Common Criteria configuration evaluation
FIPS 140-2 validated cryptographic module
Getting Started With Security Administration in Adaptive Server
General process of security administration
Recommendations for setting up security
An example of setting up security
Security features in Adaptive Server
Identification and authentication
External authentication
Managing remote servers
Discretionary access control
Row-level access control
Division of roles
Role hierarchy
Mutual exclusivity
Auditing for accountability
Confidentiality of data
Password-protected database backup
Managing Adaptive Server Logins, Database Users, and Client Connections
Choosing and creating a password
Adding logins to Adaptive Server
Login failure
Creating groups
Adding users to databases
Adding a “guest” user to a database
“guest” user permissions
“guest” user in user databases
“guest” user in installed system databases
“guest” user in pubs2 and pubs3
Adding a guest user to the server
Adding remote users
Number of user and login IDs
Limits and ranges of ID numbers
Login connection limitations
Creating and assigning roles to users
System-defined roles
System administrator privileges
System security officer privileges
Operator privileges
Sybase Technical Support
Replication role
Distributed Transaction Manager role
High availability role
Monitoring and diagnosis
Job Scheduler roles
Real-time messaging role
Web Services role
Key custodian role
User-defined roles
Planning user-defined roles
Creating a user-defined role
Adding and removing passwords from a role
Role hierarchies and mutual exclusivity
Role hierarchies and mutual exclusivity
Defining and changing mutual exclusivity of roles
Defining and changing a role hierarchy
Setting up default activation at login
Activating and deactivating roles
Setting up groups and adding users
Dropping users, groups, and user-defined roles
Dropping users
Dropping groups
Dropping user-defined roles
Locking or dropping Adaptive Server login accounts
Locking and unlocking login accounts
Dropping login accounts
Locking logins that own thresholds
Changing user information
Changing passwords
Requiring new passwords
Null passwords
Logging in after lost password
Changing user defaults
Changing a user’s group membership
Changing user process information
Using aliases in databases
Adding aliases
Dropping aliases
Getting information about aliases
Getting information about users
Reporting on users and processes
Getting information about login accounts
Getting information about database users
Finding user names and IDs
Displaying information about roles
Finding role IDs and names
Viewing active system roles
Displaying a role hierarchy
Viewing user roles in a hierarchy
Determining mutual exclusivity
Determining role activation
Checking for roles in stored procedures
Establishing a password and login policy
Setting and changing the maximum login attempts
Logging in after losing a password
Locking and unlocking logins and roles
Displaying password information
Checking passwords for at least one digit
Setting and changing minimum password length
Password complexity checks
Disallowing simple passwords
Custom password-complexity checks
Specifying characters in a password
Password complexity option cross-checks
Setting password complexity checks
Enabling custom password checks
Setting the login and role expiration interval for a password
Password expiration turned off for pre-12.x passwords
Circumventing password protection
Creating a password expiration interval for a new login
Creating a password expiration interval for a new role
Creation date added for passwords
Changing or removing password expiration interval for login or role
Securing login passwords on the network
Securing login passwords stored on disk and in memory
Using only the SHA-256 algorithm
Character set considerations for passwords
Upgrade and and downgrade behavior
Behavior changes on upgraded master database
Behavior changes in a new master database
Retaining password encryption after upgrading then downgrading
Expiring passwords when allow password downgrade is set to 0
Showing the current value of allow password downgrade
Last login and locking inactive accounts
Using syslogins to track if an account is locked
Using passwords in a high-availability environment
High-availability configuration
Passwords updated after upgrade
Monitoring license use
How licenses are counted
Configuring the License Use Monitor
Monitoring license use with the housekeeper task
Logging the number of user licenses
Getting information about usage: chargeback accounting
Reporting current usage statistics
Displaying current accounting totals
Initiating a new accounting interval
Specifying the interval for adding accounting statistics
Managing Remote Servers
Overview
Managing remote servers
Adding a remote server
Examples of adding remote servers
Managing remote server names
Setting server connection options
Using the timeouts option
Using the net password encryption option
Using the rpc security model options
Getting information about servers
Dropping remote servers
Adding remote logins
Mapping users’ server IDs
Mapping remote logins to particular local names
Mapping all remote logins to one local name
Keeping remote login names for local servers
Example of remote user login mapping
Password checking for remote users
Effects of using the untrusted mode
Getting information about remote logins
Configuration parameters for remote logins
External Authentication
Configuring Adaptive Server for network-based security
Security services and Adaptive Server
Administering network-based security
Setting up configuration files for security
Specifying security information for the server
Preparing libtcl.cfg to use network-based security
The objectid.dat file
Identifying users and servers to the security mechanism
Configuring Adaptive Server for security
Enabling network-based security
Requiring unified login
Establishing a secure default login
Mapping security mechanism login names to server names
Requiring message confidentiality with encryption
Requiring data integrity
Memory requirements for network-based security
Adding logins to support unified login
General procedure for adding logins
Establishing security for remote procedures
Security model A
Security model B
Unified login and the remote procedure models
Establishing the security model for RPCs
Rules for setting up security model B for RPCs
Preparing to use security model B for RPCs
Example of setting up security model B for RPCs
Getting information about remote servers
Connecting to the server and using the security services
Example using security services
Using security mechanisms for the client
Getting information about available security services
Determining supported security services and mechanisms
Determining active security services
Determining whether a security service Is enabled
Using Kerberos
Kerberos compatibility
Starting Adaptive Server under Kerberos
Configuring Kerberos
Using principal names
Specifying the Adaptive Server principal name
Using sybmapname to handle user principal names
Concurrent Kerberos authentication
Configuring Adaptive Server for LDAP user authentication
Composed DN algorithm
Searched DN algorithm
Configuring LDAP
LDAP user authentication administration
LDAP user authentication password information changes
Failover support
Adaptive Server logins and LDAP user accounts
Secondary lookup server support
LDAP server state transitions
LDAP user authentication tuning
Adding tighter controls on login mapping
Troubleshooting LDAP user authentication errors
Configuring an LDAP server
LDAPS user authentication enhancements
Automatic LDAP user authentication and failback
Setting the LDAP failback time interval
Examples
Login mapping of external authentication
Configuring Adaptive Server for authentication using PAM
Enabling PAM in Adaptive Server
Configuring operating system s
Running a 32- and 64-bit server on the same machine
Configuring Adaptive Server for PAM user authentication
Adaptive Server logins and PAM user accounts
Enhanced login controls
Forcing authentication
Mapping logins using sp_maplogin
Displaying mapping information
Determining the authentication mechanism
Managing User Permissions
Overview
Permissions for creating databases
Changing database ownership
Database owner privileges
Database object owner privileges
Other database user privileges
Permissions on system procedures
Granting and revoking permissions
Object access permissions
Concrete identification
Special requirements for SQL92 standard compliance
Examples of granting object access permissions
Examples of revoking object access permissions
Granting and revoking permissions for update statistics, delete statistics, and truncate table
Granting permissions on functions
Granting and revoking permissions to execute commands
Granting permissions to execute commands
Granting command permission examples
Granting proxy authorization
Granting permissions on dbcc commands
Server-wide and database-specific dbcc commands
dbcc grantees and users in databases
Permissions on system tables
Granting default permissions to system tables and stored procedures
Combining grant and revoke statements
Understanding permission order and hierarchy
Grant dbcc and set proxy issue warning for fipsflagger
Granting and revoking roles
Granting roles
Understanding grant and roles
Revoking roles
Acquiring the permissions of another user
Using setuser
Using proxy authorization
Using set proxy to restrict roles
Executing proxy authorization
Proxy authorization for applications
Reporting on permissions
Querying the sysprotects table for proxy authorization
Displaying information about users and processes
Reporting permissions on database objects or users
Reporting permissions on specific tables
Reporting permissions on specific columns
Using views and stored procedures as security mechanisms
Using views as security mechanisms
Using stored procedures as security mechanisms
Roles and stored procedures
Understanding ownership chains
Example of views and ownership chains
Example of procedures and ownership chains
Permissions on triggers
Using row-level access control
Access rules
Syntax for access rules
Using access and extended access rules
Access rule examples
Access rules and alter table command
Access rules and bcp
Access rules as user-defined Java functions
Using the Application Context Facility
Setting permissions for using application context functions
Creating and using application contexts
set_appcontext
get_appcontext
list_appcontext
rm_appcontext
SYS_SESSION system application context
Solving a problem using an access rule and ACF
Using login triggers
Creating login triggers
Configuring login triggers
Executing a login trigger
Understanding login trigger output
Using login triggers for other applications
Login trigger restrictions
Issues and information
Disabling execute privilege on login triggers
Exporting set options from a login trigger
Setting global login triggers
Auditing
Introduction to auditing in Adaptive Server
Correlating Adaptive Server and operating system audit records
The audit system
The sybsecurity database
The audit queue
Auditing configuration parameters
System procedures for auditing
Installing and setting up auditing
Installing the audit system
Tables and devices for the audit trail
Device for the syslogs transaction log table
Installing auditing with installsecurity
Moving the auditing database to multiple devices
Setting up audit trail management
Setting up threshold procedures
Setting auditing configuration parameters
Setting up transaction log management
Truncating the transaction log
Managing the transaction log with no truncation
Enabling and disabling auditing
Single-table auditing
Establishing and managing single-table auditing
Threshold procedure for single-table auditing
What happens when the current audit table is full?
Recovering when the current audit table is full
Restarting auditing
Setting global auditing options
Auditing options: types and requirements
Examples of setting auditing options
Hiding system stored procedure and command password parameters
Determining current auditing settings
Adding user-specified records to the audit trail
Examples of adding user-defined audit records
Querying the audit trail
Understanding the audit tables
Reading the extrainfo column
Monitoring failed login attempts
Auditing login failures
Confidentiality of Data
Secure Sockets Layer (SSL) in Adaptive Server
Internet communications overview
Public-key cryptography
SSL overview
SSL in Adaptive Server
SSL filter
Authentication via the certificate
Connection types
Enabling SSL
Obtaining a certificate
Creating server directory entries
Administering certificates
Performance
Cipher Suites
@@ssl_ciphersuite
Setting SSL cipher suite preferences
Examples sp_ssladmin
Other considerations
Using SSL to specify a common name
Specifying a common name with sp_listener
Stored procedure sp_addserver changed
Kerberos confidentiality
Dumping and loading databases with password protection
Passwords and earlier versions of Adaptive Server
Passwords and character sets
System Administration Guide: Volume 2 Adaptive Server Enterprise 15.5
About this book
Limiting Access to Server Resources
Resource limits
Planning resource limits
Enabling resource limits
Defining time ranges
Determining the time ranges you need
Creating named time ranges
A time range example
Modifying a named time range
Dropping a named time range
When do time range changes take effect?
Identifying users and limits
Identifying heavy-usage users
Identifying heavy-usage applications
Choosing a limit type
Determining time of enforcement
Determining the scope of resource limits
Understanding limit types
Limiting I/O cost
Identifying I/O costs
Calculating the I/O cost of a cursor
The scope of the io_cost limit type
Limiting elapsed time
The scope of the elapsed_time limit type
Limiting the size of the result set
The scope of the row_count limit type
Setting limits for tempdb space usage
Limiting idle time
Creating a resource limit
Resource limit examples
Examples
Getting information on existing limits
Listing all existing resource limits
Modifying resource limits
Dropping resource limits
Resource limit precedence
Time ranges
Resource limits
Mirroring Database Devices
Disk mirroring
Deciding what to mirror
Mirroring using minimal physical disk space
Mirroring for nonstop recovery
Conditions that do not disable mirroring
Disk mirroring commands
Initializing mirrors
Unmirroring a device
Effects on system tables
Restarting mirrors
waitfor mirrorexit
Mirroring the master device
Getting information about devices and mirrors
Disk mirroring tutorial
Disk resizing and mirroring
Configuring Memory
Determining memory availability for Adaptive Server
How Adaptive Server allocates memory
Disk space allocation
Larger logical page sizes and buffers
Heap memory
Calculating heap memory
How Adaptive Server uses memory
Determining the amount of memory Adaptive Server needs
Determining Adaptive Server memory configuration
If you are upgrading
Determining the amount of memory Adaptive Server can use
Configuration parameters that affect memory allocation
Dynamically allocating memory
If Adaptive Server cannot start
Dynamically decreasing memory configuration parameters
System procedures for configuring memory
Using sp_configure to set configuration parameters
Memory available for dynamic growth
Using sp_helpconfig
Using sp_monitorconfig
Configuration parameters that control Adaptive Server memory
Adaptive Server executable code size
Data and procedure caches
Determining the procedure cache size
Determining the default data cache size
Monitoring cache space
User connections
Open databases, open indexes, and open objects
Number of locks
Database devices and disk I/O structures
Other parameters that use memory
Parallel processing
Worker processes
Remote servers
Number of remote sites
Other configuration parameters for RPCs
Referential integrity
Other parameters that affect memory
The statement cache
Setting the statement cache
Ad hoc query processing
Monitoring the statement cache
Purging the statement cache
Printing statement summaries
Displaying the SQL plan for cached statements
Configuring memory for caches
Configuring Data Caches
The Adaptive Server data cache
Cache configuration commands and system procedures
Information on data caches
Configuring data caches
Creating a new cache
Insufficient space for new cache
Adding memory to an existing named cache
Decreasing the size of a cache
Deleting a cache
Explicitly configuring the default cache
Changing the cache type
Configuring cache replacement policy
Dividing a data cache into memory pools
Matching log I/O size for log caches
Binding objects to caches
Cache binding restrictions
Getting information about cache bindings
Checking cache overhead
How overhead affects total cache space
Dropping cache bindings
Changing the wash area for a memory pool
When the wash area is too small
When the wash area is too large
Setting the housekeeper to avoid washes for cache
Changing the asynchronous prefetch limit for a pool
Changing the size of memory pools
Moving space from the memory pool
Moving space from other memory pools
Adding cache partitions
Setting the number of cache partitions
Setting the number of local cache partitions
Precedence
Dropping a memory pool
When pools cannot be dropped due to page use
Cache binding effects on memory and query plans
Flushing pages from cache
Locking to perform bindings
Cache binding effects on stored procedures and triggers
Configuring data caches using the configuration file
Cache and pool entries in the configuration file
Cache configuration guidelines
Configuration file errors
Managing Multiprocessor Servers
Target architecture
Configuring an SMP environment
Managing engines
Resetting the number of engines
Choosing the right number of engines
Starting and stopping engines
Monitoring engine status
Starting and stopping engines with sp_engine
Managing user connections (UNIX only)
Configuration parameters that affect SMP systems
Configuring spinlock ratio parameters
Creating and Managing User Databases
Commands for creating and managing user databases
Permissions for managing user databases
Using the create database command
Assigning space and devices to databases
Default database size and devices
Estimating the required space
Placing the transaction log on a separate device
Estimating the transaction log size
Default log size and device
Moving the transaction log to another device
Using the for load option for database recovery
Using the with override option with create database
Changing database ownership
Altering databases
alter database syntax
Using the drop database command
System tables that manage space allocation
The sysusages table
The segmap column
The lstart, size, and vstart columns
Getting information about database storage
Database device names and options
Checking the amount of space used
Checking space used in a database
Checking summary information for a table
Checking information for a table and its indexes
Querying system table for space usage information
Database Mount and Unmount
Overview
Manifest file
Copying and moving databases
Performance considerations
Device verification
Mounting and unmounting databases
Unmounting a database
Mounting a database
Creating a mountable copy of a database
Moving databases from one Adaptive Server to another
System restrictions
quiesce database extension
Creating and Using Segments
Adaptive Server segments
System-defined segments
How Adaptive Server uses segments
Controlling space usage
Improving performance
Separating tables, indexes, and logs
Splitting tables
Moving a table to another device
Creating segments
Changing the scope of segments
Extending the scope of segments
Automatically extending the scope of a segment
Reducing the scope of a segment
Assigning database objects to segments
Creating new objects on segments
Placing existing objects on segments
Placing text pages on a separate device
Creating clustered indexes on segments
Dropping segments
Getting information about segments
sp_helpsegment
sp_helpdb
sp_help and sp_helpindex
Segments and system tables
A segment tutorial
Using the reorg Command
reorg command and its parameters
Using the optdiag utility to assess the need for a reorg
Moving forwarded rows to home pages
Using reorg compact to remove row forwarding
Reclaiming unused space from deletions and updates
Reclaming space without the reorg command
Reclaiming unused space and undoing row forwarding
Rebuilding a table
Prerequisites for running reorg rebuild
Changing space management settings before using reorg rebuild
Using the reorg rebuild command on indexes
Rebuilding indexes with reorg rebuild index_name partition_name
Space requirements for rebuilding an index
Status messages
resume and time options for reorganizing large tables
Specifying no_of_minutes in the time option
Checking Database Consistency
What is the database consistency checker?
Page and object allocation
Understanding the object allocation map (OAM)
Understanding page linkage
What checks can be performed with dbcc?
Understanding the output from dbcc commands
Checking consistency of databases and tables
dbcc checkstorage
Advantages of using dbcc checkstorage
Comparison of dbcc checkstorage and other dbcc commands
Understanding the dbcc checkstorage operation
Performance and scalability
dbcc checktable
dbcc checkdb
Checking page allocation
dbcc checkalloc
dbcc indexalloc
dbcc tablealloc
dbcc textalloc
Correcting allocation errors using the fix | nofix option
Generating reports with dbcc tablealloc and dbcc indexalloc
Checking consistency of system tables
Strategies for using consistency checking commands
Using large I/O and asynchronous prefetch
Scheduling database maintenance at your site
Database use
Backup schedule
Size of tables and importance of data
Errors generated by database consistency problems
Reporting on aborted checkstorage and checkverify operations
Aborting with error 100032
Comparison of soft and hard faults
Soft faults
Hard faults
Verifying faults with dbcc checkverify
How dbcc checkverify works
When to use dbcc checkverify
How to use dbcc checkverify
Preparing to use dbcc checkstorage
Planning resources
Examples of sp_plan_dbccdb output
Planning workspace size
Configuring worker processes
Setting up a named cache for dbcc
Configuring an 8-page I/O buffer pool
Allocating disk space for dbccdb
Segments for workspaces
Creating the dbccdb database
Updating the dbcc_config table
Adding default configuration values with sp_dbcc_updateconfig
Deleting configuration values with sp_dbcc_updateconfig
Viewing the current configuration values
Maintaining dbccdb
Reevaluating and updating dbccdb configuration
Cleaning up dbccdb
Removing workspaces
Performing consistency checks on dbccdb
Generating reports from dbccdb
Reporting a summary of dbcc checkstorage operations
Reporting configuration, statistics and fault information
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 database dumps in upgrades
Upgrading using dump and load
Upgrading compiled objects in database dumps
Determining whether a compiled object has been upgraded
Developing a Backup and Recovery Plan
Keeping track of database changes
Getting information about the transaction log
Using delayed_commit to determine when log records are committed
Designating responsibility for backups
Synchronizing a database and its log: checkpoints
Setting the recovery interval
Automatic checkpoint procedure
Checkpoint after user database upgrade
Truncating the log after automatic checkpoints
Free checkpoints
Manually requesting a checkpoint
Automatic recovery after a system failure or shutdown
Fast recovery
Adaptive Server start-up sequence
Bringing engines online early
Parallel recovery
Database recovery
Recovery order
Changing or deleting the recovery position of a database
Listing the user-assigned recovery order of databases
Parallel checkpoints
Recovery state
Tuning for fast recovery
Database layout
Runtime configuration suggestions
Setting space accounting
Fault isolation during recovery
Persistence of offline pages
Configuring recovery fault isolation
Isolating suspect pages
Raising the number of suspect pages allowed
Getting information about offline databases and pages
Bringing offline pages online
Index-level fault isolation for data-only-locked tables
Side effects of offline pages
Recovery strategies using recovery fault isolation
Reload strategy
Repair strategy
Assessing the extent of corruption
Using the dump and load commands
Making routine database dumps: dump database
Making routine transaction log dumps: dump transaction
Copying the log after device failure: dump tran with no_truncate
Restoring the entire database: load database
Applying changes to the database: load transaction
Making the database available to users: online database
Dumping and loading databases across platforms
Dumping a database
Loading a database
Restrictions for dumping and loading databases and transactions
Performance notes
Moving a database to another Adaptive Server
Upgrading a user database
Using the special dump transaction options
Using the special load options to identify dump files
Restoring a database from backups
Suspending and resuming updates to databases
Guidelines for using quiesce database
Maintaining server roles in a primary and secondary relationship
Starting the secondary server with the -q option
“in quiesce” database log record value updated
Updating the dump sequence number
Backing up primary devices with quiesce database
Recovery of databases for warm standby method
Making archived copies during the quiescent state
Using mount and unmount commands
Using Backup Server for backup and recovery
Communicating with Backup Server
Mounting a new volume
Starting and stopping Backup Server
Configuring your server for remote access
Choosing backup media
Protecting backup tapes from being overwritten
Dumping to files or disks
Creating logical device names for local dump devices
Listing the current device names
Adding a backup device
Scheduling backups of user databases
Scheduling routine backups
Other times to back up a database
Dumping a user database after upgrading
Dumping a database after creating an index
Dumping a database after unlogged operations
Dumping a database when the log has been truncated
Scheduling backups of master
Dumping master after each change
Saving scripts and system tables
Truncating the master database transaction log
Avoiding volume changes and recovery
Scheduling backups of the model database
Truncating the model database’s transaction log
Scheduling backups of the sybsystemprocs database
Configuring Adaptive Server for simultaneous loads
Gathering backup statistics
Backing Up and Restoring User Databases
Specifying the database and dump device
Rules for specifying database names
Rules for specifying dump devices
Tape device determination by Backup Server
Tape sevice configuration file
Compressing a dump
Backup Server dump files and compressed dumps
Loading compressed dumps
Specifying a remote Backup Server
Specifying tape density, block size, and capacity
Overriding the default density
Overriding the default block size
Specifying a larger block size value
Specifying tape capacity for dump commands
Non-rewinding tape functionality for Backup Server
Tape operations
Dump version compatibility
Specifying the volume name
Loading from a multifile volume
Identifying a dump
Improving dump or load performance
Compatibility with prior versions
Labels stored in integer format
Configuring system resources
Setting shared memory usage
Setting maximum number of stripes
Setting the maximum number of network connections
Setting the maximum number of service threads
Specifying additional dump devices: the stripe on clause
Dumping to multiple devices
Loading from multiple devices
Using fewer devices to load than to dump
Specifying the characteristics of individual devices
Tape handling options
Specifying whether to dismount the tape
Rewinding the tape
Protecting dump files from being overwritten
Reinitializing a volume before a dump
Dumping and loading databases with password protection
Overriding the default message destination
Bringing databases online with standby_access
Determining when to use with standby_access
Bring databases online with standby_access
Getting information about dump files
Requesting dump header information
Determining the database, device, file name, and date
Copying the log after a device failure
Truncating the log
Truncating a log that is not on a separate segment
Truncating the log in early development environments
Truncating a log that has no free space
Dangers of using with truncate_only and with no_log
Providing enough log space
Responding to volume change requests
Volume change prompts for dumps
Volume change prompts for loads
Recovering a database: step-by-step instructions
Getting a current dump of the transaction log
Examining the space usage
Dropping the databases
Re-creating the databases
Loading the database
Loading the transaction logs
Loading a transaction log to a point in time
Bringing the databases online
Replicated databases
Loading database dumps from older versions
Upgrading a dump to the current version of Adaptive Server
The database offline status bit
Version identifiers
Cache bindings and loading databases
Databases and cache bindings
Database objects and cache bindings
Checking on cache bindings
Cross-database constraints and loading databases
Restoring the System Databases
Recovering a system database
Recovering the master database
About the recovery process
Summary of recovery procedure
Finding copies of system tables
Building a new master device
Starting Adaptive Server in master-recover mode
Re-creating device allocations for master
Checking your Backup Server sysservers information
Verifying that your Backup Server is running
Loading a backup of master
Updating the number of devices configuration parameter
Restarting Adaptive Server in master-recover mode
Checking system tables to verify current backup of master
Restarting Adaptive Server
Restoring server user IDs
Restoring the model database
Checking Adaptive Server
Backing up master
Recovering the model database
Recovering the sybsystemprocs database
Restoring sybsystemprocs with installmaster
Restoring sybsystemprocs with load database
Restoring system tables with disk reinit and disk refit
Restoring sysdevices with disk reinit
Restoring sysusages and sysdatabase with disk refit
Archive Database Access
Overview
Components of an archive database
The database dump
The modified pages section
The sysaltusages table and the scratch database
Working with an archive database
DDLGen support for archive database access
Configuring an archive database
Sizing the modified pages section
Increasing the amount of space allocated to the modified pages section
Materializing an archive database
Using load database with norecovery
Using logical devices with an archive database
load database limitations with an archive database
Bringing an archive database online
Loading a transaction log into an archive database
Dropping an archive database
Using an archive database
Using SQL commands with an archive database
Using dbcc commands with an archive database
Typical archive database command sequence
Compressed dumps for an archive database
Creating a compression memory pool
Upgrading and downgrading an archive database
Upgrading an Adaptive Server with an archive database
Downgrading an Adaptive Server with an archive database
Compatibility issues for a compressed dump
Archive database limitations
Expanding Databases Automatically
Understanding disks, devices, databases, and segments
Threshold action procedures
Installing automatic database expansion procedures
Running sp_dbextend
Command options in the sp_dbextend interface
Validating current thresholds
Setting up a database for automatic expansion
Restrictions and limitations
Managing Free Space with Thresholds
Monitoring free space with the last-chance threshold
Crossing the threshold
Controlling how often sp_thresholdaction executes
Rollback records and the last-chance threshold
Calculating the space for rollback records
Using lct_admin to determine the free log space
Determining the current space for rollback records
Effect of rollback records on the last-chance threshold
User-defined thresholds
Last-chance threshold and user log caches for shared log and data segments
Using lct_admin abort to abort suspended transactions
Adding space to the master database’s transaction log
Automatically aborting or suspending processes
Using abort tran on log full to abort transactions
Waking suspended processes
Adding, changing, and deleting thresholds
Displaying information about existing thresholds
Thresholds and system tables
Adding a free-space threshold
Changing or specifying a new free-space threshold
Dropping a threshold
Creating a free-space threshold for the log segment
Testing and adjusting the new threshold
Creating additional thresholds on other segments
Determining threshold placement
Creating threshold procedures
Declaring procedure parameters
Generating error log messages
Dumping the transaction log
A simple threshold procedure
A more complex procedure
Deciding where to put a threshold procedure
Disabling free-space accounting for data segments
System Tables Diagram (poster)
Transact-SQL Users Guide Adaptive Server Enterprise 15.5
About this book
SQL Building Blocks
SQL in Adaptive Server
Queries, data modification, and commands
Tables, columns, and rows
Relational operations
Compiled objects
Saving or restoring source text
Verifying and encrypting source text
Naming conventions
SQL data characters
SQL language characters
Identifiers
Multibyte character sets
Delimited identifiers
Uniqueness and qualification conventions
Remote servers
Expressions in Adaptive Server
Arithmetic and character expressions
Operator precedence
Arithmetic operators
Bitwise operators
The String concatenation operator
The comparison operators
Nonstandard operators
Character expression comparisons
Empty strings
Quotation marks
Relational and logical expressions
any, all, and in
and and or
Transact-SQL extensions
compute clause
Control-of-flow language
Stored procedures
Extended stored procedures
Triggers
Defaults and rules
Error handling and set options
Additional Adaptive Server extensions to SQL
Compliance to ANSI standards
Federal Information Processing Standards (FIPS) flagger
Chained transactions and isolation levels
Identifiers
SQL standard-style comments
Right truncation of character strings
Permissions required for update and delete statements
Arithmetic errors
Synonymous keywords
Treatment of nulls
Adaptive Server login accounts
Group membership
Role membership
Information about your Adaptive Server account
Password changes
Remote logins
Password changes on a remote server
isql utility
Default databases
Network-based security services with isql
isql logout
Displaying SQL text
pubs2 and pubs3 sample databases
Sample database content
Queries: Selecting Data from a Table
What are queries?
select syntax
Checking for identifiers in a select statement
Choosing columns using the select clause
Choosing all columns using select *
Choosing specific columns
Rearranging the column order
Renaming columns in query results
Using expressions
Quoted strings in column headings
Character strings in query results
Computed values in the select list
Arithmetic operator precedence
Selecting text, unitext, image, and values
Using readtext
Select list summary
Eliminating duplicate query results with distinct
Specifying tables with the from clause
Selecting rows using the where clause
Comparison operators
Ranges (between and not between)
Lists (in and not in)
Matching patterns
Matching character strings: like
Using not like
Getting different results using not like and ^
Using wildcard characters as literal characters
Interaction of wildcard characters and square brackets
Using trailing blanks and %
Using wildcard characters in columns
Character strings and quotation marks
“Unknown” values: NULL
Testing a column for null values
Difference between FALSE and UNKNOWN
Substituting a value for NULLs
Expressions that evaluate to NULL
Concatenating strings and NULL
System-generated NULLs
Connecting conditions with logical operators
Logical operator precedence
Using Aggregates, Grouping, and Sorting
Using aggregate functions
Aggregate functions and datatypes
count vs. count (*)
Aggregate functions with distinct
Null values and the aggregate functions
Using statistical aggregates
Standard deviation and variance
Statistical aggregates
Organizing query results into groups: the group by clause
group by and SQL standards
Nesting groups with group by
Referencing other columns in queries using group by
Using outer joins and and aggregate extended columns
Expressions and group by
Using group by in nested aggregates
Null values and group by
where clause and group by
group by and all
Aggregates without group by
Selecting groups of data: the having clause
How the having, group by, and where clauses interact
Using having without group by
Sorting query results: the order by clause
order by and group by
order by and group by used with select distinct
Summarizing groups of data: the compute clause
Row aggregates and compute
Rules for compute clauses
Specifying more than one column after compute
Using more than one compute clause
Applying an aggregate to more than one column
Using different aggregates in the same compute clause
Generating totals: compute without by
Combining queries: the union operator
Guidelines for union queries
Using union with other Transact-SQL commands
Joins: Retrieving Data from Several Tables
How joins work
Join syntax
Joins and the relational model
How joins are structured
The from clause
The where clause
Join operators
Datatypes in join columns
Joins and text and image columns
How joins are processed
Equijoins and natural joins
Joins with additional conditions
Joins not based on equality
Self-joins and correlation names
The not-equal join
Not-equal joins and subqueries
Joining more than two tables
Outer joins
Inner and outer tables
Outer join restrictions
Views used with outer joins
ANSI inner and outer joins
Correlation name and column referencing rules for ANSI joins
ANSI inner joins
ANSI outer joins
Should the predicate be in the on or where clause?
Nested ANSI outer joins
Converting outer joins with join-order dependency
Transact-SQL outer joins
Relocated joins
Using relocated joins
Configuring relocated joins
How null values affect joins
Determining which table columns to join
Subqueries: Using Queries Within Other Queries
How subqueries work
Subquery restrictions
Example of using a subquery
Qualifying column names
Subqueries with correlation names
Multiple levels of nesting
Subqueries in update, delete, and insert statements
Subqueries in conditional statements
Subqueries instead of expressions
Types of subqueries
Expression subqueries
Using scalar aggregate functions to guarantee a single value
Using group by and having in expression subqueries
Using distinct with expression subqueries
Quantified predicate subqueries
Subqueries with any and all
Subqueries used with in
Subqueries used with not in
Subqueries using not in with NULL
Subqueries used with exists
Subqueries used with not exists
Finding intersection and difference with exists
Subqueries using SQL derived tables
Using correlated subqueries
Correlated subqueries containing Transact-SQL outer joins
Correlated subqueries with correlation names
Correlated subqueries with comparison operators
Correlated subqueries in a having clause
Using and Creating Datatypes
How Transact-SQL datatypes work
Using system-supplied datatypes
Exact numeric types: integers
Exact numeric types: decimal numbers
Approximate numeric datatypes
Money datatypes
Date and time datatypes
Character datatypes
unichar datatype
text datatype
unitext datatype
Binary datatypes
image datatype
The bit datatype
The timestamp datatype
The sysname and longsysname datatype
Converting between datatypes
Mixed-mode arithmetic and datatype hierarchy
Working with money datatypes
Determining precision and scale
Creating user-defined datatypes
Creating a user-defined datatype with the identity property
Specifying length, precision, and scale
Specifying null type
Associating rules and defaults with user-defined datatypes
Creating user-defined datatype with IDENTITY property
Creating IDENTITY columns from user-defined datatypes
Dropping a user-defined datatype
Getting information about datatypes
Creating Databases and Tables
What are databases and tables?
Enforcing data integrity in databases
Permissions within databases
Using and creating databases
Choosing a database: use
Creating a user database: create database
The on clause
The log on clause
The for load option
quiesce database command
Altering the sizes of databases
Dropping databases
Creating tables
Maximum number of columns per table
Example of creating a table
Choosing table names
Creating tables in different databases
create table syntax
Using IDENTITY columns
Creating IDENTITY columns with user-defined datatypes
Referencing IDENTITY columns
Referring to IDENTITY columns with syb_identity
Creating “hidden” IDENTITY columns automatically
Allowing null values in a column
Constraints and rules used with null values
Defaults and null values
Nulls require variable-length datatypes
text, unitext, and image columns
Using temporary tables
Ensuring that the temporary table name is unique
Manipulating temporary tables in stored procedures
General rules on temporary tables
Managing identity gaps in tables
Parameters for controlling identity gaps
Comparison of identity burning set factor and identity_gap
Example of using identity burning set factor
Example of using identity_gap
Setting the table-specific identity gap
Setting identity gap with create table
Setting identity gap with select into
Changing the table-specific identity gap
Displaying table-specific identity gap information
Gaps from other causes
When table inserts reach IDENTITY column maximum value
Defining integrity constraints for tables
Specifying table-level or column-level constraints
Creating error messages for constraints
After creating a check constraint
Specifying default column values
Specifying unique and primary key constraints
Specifying referential integrity constraints
Table-level or column-level referential integrity constraints
Maximum number of references allowed for a table
Using create schema for cross-referencing constraints
General rules for creating referential integrity constraints
Specifying check constraints
Designing applications that use referential integrity
How to design and create a table
Make a design sketch
Create the user-defined datatypes
Choose the columns that accept null values
Define the table
Creating new tables from query results: select into
Checking for errors
Using select into with IDENTITY columns
Selecting an IDENTITY column into a new table
Selecting the IDENTITY column more than once
Adding a new IDENTITY column with select into
Defining a column whose value must be computed
IDENTITY columns selected into tables with unions or joins
Altering existing tables
Objects using select * do not list changes to table
Using alter table on remote tables
Adding columns
Adding columns appends column IDs
Adding NOT NULL columns
Adding constraints
Dropping columns
Dropping columns renumbers the column ID
Dropping constraints
Modifying columns
Which datatypes can I convert?
Modifying tables may prevent successful bulk copy of previous dump
Decreasing column length may truncate data
Modifying datetime columns
Modifying the NULL default value of a column
Modifying columns that have precision or scale
Modifying text, unitext, and image columns
Adding, dropping, and modifying IDENTITY columns
Adding IDENTITY columns
Dropping IDENTITY columns
Modifying IDENTITY columns
Data copying
Changing exp_row_size
Modifying locking schemes and table schema
Altering columns with user-defined datatypes
Adding a column with user-defined datatypes
Dropping a column with user-defined datatypes
Modifying a column with user-defined datatypes
Errors and warnings from alter table
Errors and warnings generated by alter table modify
Scripts generated by if exists()...alter table
Renaming tables and other objects
Renaming dependent objects
Dropping tables
Computed columns
Using computed columns
Computed columns example
Indexes on computed columns
Deterministic property
What is the deterministic property?
What affects the deterministic property?
How does the deterministic property affect computed columns?
Examples
How does the deterministic property affect function-based indexes?
Assigning permissions to users
Getting information about databases and tables
Getting help on databases
Getting help on database objects
Using sp_help on database objects
Using sp_helpconstraint to find a table’s constraint information
Finding out how much space a table uses
Listing tables, columns, and datatypes
Finding an object name and ID
Adding, Changing, Transferring, and Deleting Data
Introduction
Permissions
Referential integrity
Transactions
Using the sample databases
Datatype entry rules
char, nchar, unichar, univarchar, varchar, nvarchar, unitext, and text
date and time
Entering times
Entering dates
Searching for dates and times
binary, varbinary, and image
money and smallmoney
float, real, and double precision
decimal and numeric
Integer types and their unsigned counterparts
timestamp
Adding new data
Adding new rows with values
Inserting data into specific columns
Restricting column data: rules
Using the NULL character string
Inserting NULLs into columns that do not allow them
Adding rows without values in all columns
Changing a column’s value to NULL
Adaptive-Server-generated values for IDENTITY columns
Explicitly inserting data into an IDENTITY column
Retrieving IDENTITY column values with @@identity
Reserving a block of IDENTITY column values
Reaching the IDENTITY column’s maximum value
Adding new rows with select
Using computed columns
Inserting data into some columns
Inserting data from the same table
Changing existing data
Using the set clause with update
Assigning variables in the set clause
Using the where clause with update
Using the from clause with update
Performing updates with joins
Updating IDENTITY columns
Changing text, unitext, and image data
Transfering data incrementally
Marking tables for incremental transfer
Transferring tables from a destination file
Converting Adaptive Server datatypes to IQ
Storing transfer information
Exceptions and errors
Sample session for incremental data transfer
Replacing data with new rows
Deleting data
Using the from clause with delete
Deleting from IDENTITY columns
Deleting all rows from a table
truncate table syntax
SQL Derived Tables
Differences from abstract plan derived tables
How SQL derived tables work
Advantages of SQL derived tables
SQL derived tables and optimization
SQL derived table syntax
Derived column lists
Correlated SQL derived tables
Using SQL derived tables
Nesting
Subqueries using SQL derived tables
Unions
Unions in subqueries
Renaming columns with SQL derived tables
Constant expressions
Aggregate functions
Joins with SQL derived tables
Creating a table from a SQL derived table
Using views with SQL derived tables
Correlated attributes
Partitioning Tables and Indexes
Overview
Upgrading from Adaptive Server 12.5.x and earlier
Data partitions
Index partitions
Partition IDs
Locks and partitions
Partitioning types
Range partitioning
Hash partitioning
List partitioning
Round-robin partitioning
Composite partitioning keys
Partition pruning
Indexes and partitions
Global indexes
Global nonclustered index on unpartitioned table
Global clustered index on unpartitioned table
Global clustered index on round-robin partitioned table
Global nonclustered index on partitioned table
Local indexes
Local clustered indexes
Local nonclustered indexes
Guaranteeing a unique index
Creating and managing partitions
Enabling partitioning
Partitioning tasks
Creating data partitions
Creating a range-partitioned table
Creating a hash-partitioned table
Creating a list-partitioned table
Creating a round-robin–partitioned table
Creating partitioned indexes
Creating global indexes
Creating local indexes
Creating clustered indexes on partitioned tables
Creating a partitioned table from an existing table
Altering data partitions
Changing an unpartitioned table to a partitioned table
Adding partitions to a partitioned table
Changing the partitioning type
Changing the partitioning key
Unpartitioning round-robin–partitioned tables
Using the partition parameter
Altering partition key columns
Configuring partitions
Updating, deleting, and inserting in partitioned tables
Updating values in partition-key columns
Displaying information about partitions
Using functions
Truncating a partition
Using partitions to load table data
Updating partition statistics
Virtually-hashed tables
Structure of a virtually-hashed table
Creating a virtually-hashed table
Limitations for virtually-hashed tables
Changes to commands
Changes to the query processor
Changes to monitor counters
Changes to system procedures
Views: Limiting Access to Data
How views work
Advantages of views
Security
Logical data independence
View examples
Creating views
create view syntax
Using the select statement with create view
View definition with projection
View definition with a computed column
View definition with an aggregate or built-in function
View definition with a join
Views used with outer joins
Views derived from other views
distinct views
Views that include IDENTITY columns
After creating a view
Validating a view’s selection criteria
Views derived from other views
Retrieving data through views
View resolution
Redefining views
Renaming views
Altering or dropping underlying objects
Modifying data through views
Restrictions on updating views
Computed columns in a view definition
group by or compute in a view definition
NULL values in underlying objects
Views created using with check option
Multi-table views
Views with IDENTITY columns
Dropping views
Using views as security mechanisms
Getting information about views
Using sp_help and sp_helptext to display view information
Using sp_depends to list dependent objects
Listing all views in a database
Finding an object name and ID
Creating Indexes on Tables
How indexes work
Comparing the two ways to create indexes
Guidelines for using indexes
When to index
When not to index
Creating indexes
create index syntax
Indexing more than one column: composite indexes
Indexing with function-based indexes
Syntax changes
Using the unique option
Including IDENTITY columns in nonunique indexes
Ascending and descending index-column values
Using fillfactor, max_rows_per_page, and reservepagegap
Indexes on computed columns
Function-based indexes
Using clustered or nonclustered indexes
Creating clustered indexes on segments
Specifying index options
Using the ignore_dup_key option
Using the ignore_dup_row and allow_dup_row options
Using the sorted_data option
Using the on segment_name option
Dropping indexes
Determining what indexes exist on a table
Updating statistics about indexes
Defining Defaults and Rules for Data
How defaults and rules work
Creating defaults
create default syntax
Binding defaults
Unbinding defaults
How defaults affect NULL values
After creating a default
Dropping defaults
Creating rules
create rule syntax
Binding rules
Rules bound to columns
Rules bound to user-defined datatypes
Precedence of rules
Rules and NULL values
After defining a rule
Unbinding rules
Dropping rules
Getting information about defaults and rules
Using Batches and Control-of-Flow Language
Introduction
Rules associated with batches
Examples of using batches
Batches submitted as files
Using control-of-flow language
if...else
case expression
Using case expression for alternative representation
case and division by zero
Using rand() functions in case expressions
case expression results
case expression requires at least one non-null result
case
case and value comparisons
coalesce
nullif
begin...end
while and break...continue
declare and local variables
goto
return
print
raiserror
Creating messages for print and raiserror
waitfor
Comments
Slash-asterisk style comments
Double-hyphen style comments
Local variables
Declaring local variables
Local variables and select statements
Local variables and update statements
Local variables and subqueries
Local variables and while loops and if…else blocks
Variables and null values
Global variables
Transactions and global variables
Checking for errors with @@error
Checking IDENTITY values with @@identity
Checking the transaction nesting level with @@trancount
Checking the transaction state with @@transtate
Checking the nesting level with @@nestlevel
Checking the status from the last fetch
Global variables affected by set options
Language and character set information in global variables
Global variables for monitoring system activity
Optimizer and partition information stored in global variables
Server information stored in global variables
Global variables and text, unitext, and image data
Using the Built-In Functions in Queries
System functions that return database information
Examples of using system functions
col_length
datalength
isnull
user_name
String functions used for character strings or expressions
Examples of using string functions
charindex, patindex
str
stuff
soundex, difference
substring
Examples of other string functions
Concatenation
Concatenation and the empty string
Nested string functions
Text functions used for text, unitext, and image data
readtext
Using readtext on unitext columns
Examples of using text functions
Aggregate functions
Mathematical functions
Examples of using mathematical functions
Date functions
Get current date: getdate
Find date parts as numbers or names
Calculate intervals or increment dates
Add date interval: dateadd
Datatype conversion functions
Using the general purpose conversion function: convert
Conversion rules
Converting character data to a noncharacter type
Converting from one character type to another
Converting numbers to a character type
Rounding during conversion to or from money types
Converting date and time information
Converting to or from unitext
Converting between numeric types
Converting binary-like data
Converting hexadecimal data
Converting image data to binary or varbinary
Converting between binary and numeric or decimal types
Conversion errors
Arithmetic overflow and divide-by-zero errors
Scale errors
Domain errors
Security functions
User-defined SQL functions
Using Stored Procedures
How stored procedures work
Examples of creating and using stored procedures
Stored procedures and permissions
Stored procedures and performance
Creating and executing stored procedures
Using deferred_name_resolution
Parameters
Default parameters
Using default parameters in stored procedures
NULL as the default parameter
Wildcard characters in the default parameter
Using more than one parameter
Procedure groups
Using with recompile in create procedure
Using with recompile in execute
Nesting procedures within procedures
Using temporary tables in stored procedures
Setting options in stored procedures
Query optimization settings
Arguments for stored procedures
Length of expressions, variables, and arguments
After creating a stored procedure
Executing stored procedures
Executing procedures after a time delay
Executing procedures remotely
Using with recompile
Deferred compilation in stored procedures
Returning information from stored procedures
Return status
Reserved return status values
User-generated return values
Checking roles in procedures
Return parameters
Passing values in parameters
The output keyword
Restrictions associated with stored procedures
Qualifying names inside procedures
Renaming stored procedures
Renaming objects referenced by procedures
Using stored procedures as security mechanisms
Dropping stored procedures
System procedures
Executing system procedures
Permissions on system procedures
Types of system procedures
System procedures for auditing
System procedures used for security administration
System procedures used for remote servers
System procedures for managing databases
System procedures used for data definition and database objects
System procedures used for user-defined messages
System procedures for languages
System procedures used for device management
System procedures used for backup and recovery
System procedures used for configuration and tuning
System procedures used for system administration
System procedures for upgrade
Other Sybase-supplied procedures
Catalog stored procedures
System extended stored procedures
dbcc procedures
Getting information about stored procedures
Getting a report with sp_help
Viewing the source text of a procedure with sp_helptext
Identifying dependent objects with sp_depends
Using sp_depends with deferred_name_resolution
Identifying permissions with sp_helprotect
Using Extended Stored Procedures
Overview
XP Server
Dynamic link library support
Open Server API
Example of creating and using ESPs
ESPs and permissions
ESPs and performance
Setting priority
Freeing memory
Creating functions for ESPs
Files for ESP development
Open Server data structures
SRV_PROC
CS_SERVERMSG
CS_DATAFMT
Open Server return codes
Outline of a simple ESP function
Multithreading
ESP function example
Building the DLL
Search order for DLLs
Sample makefile (UNIX)
Sample definitions file
Registering ESPs
Using create procedure
Using sp_addextendedproc
Removing ESPs
Renaming ESPs
Executing ESPs
System ESPs
Getting information about ESPs
ESP exceptions and messages
Starting XP Server manually
Cursors: Accessing Data
Selecting rows with cursors
Sensitivity and scrollability
Types of cursors
Cursor scope
Cursor scans and the cursor result set
Making cursors updatable
Determining which columns can be updated
How processes cursors
declare cursor syntax
declare cursor examples
Opening cursors
Fetching data rows using cursors
fetch syntax
Checking cursor status
Getting multiple rows with each fetch
Checking the number of rows fetched
Updating and deleting rows using cursors
Updating cursor result set rows
Deleting cursor result set rows
Closing and deallocating cursors
Scrollable and forward-only cursor examples
Forward-only (default) cursors
Example table for scrollable cursors
Insensitive scrollable cursors
Semi-sensitive scrollable cursors
Using cursors in stored procedures
Cursors and locking
Cursor-locking options
Information about cursors
Using browse mode instead of cursors
Browsing a table
Browse-mode restrictions
Timestamping a new table for browsing
Timestamping an existing table
Comparing timestamp values
Join cursor processing and data modifications
Updates and deletes that may affect the cursor position
Cursor positioning after a delete or update command without joins
Effects of updates and deletes on join cursors
Effects of join column buffering on join cursors
Recommendations
Triggers: Enforcing Referential Integrity
How triggers work
Using triggers vs. integrity constraints
Creating triggers
create trigger syntax
SQL statements that are not allowed in triggers
Using triggers to maintain referential integrity
Testing data modifications against the trigger test tables
Insert trigger example
Delete trigger examples
Cascading delete example
Restricted delete examples
Update trigger examples
Restricted update triggers
Updating a foreign key
Multirow considerations
Insert trigger example using multiple rows
Delete trigger example using multiple rows
Update trigger example using multiple rows
Conditional insert trigger example using multiple rows
Rolling back triggers
Global login triggers
Nesting triggers
Trigger self-recursion
Rules associated with triggers
Triggers and permissions
Trigger restrictions
Implicit and explicit null values
Triggers and performance
set commands in triggers
Renaming and triggers
Trigger tips
Disabling triggers
Dropping triggers
Getting information about triggers
sp_help
sp_helptext
sp_depends
Using instead of Triggers
Overview
Inserted and deleted logical tables
Triggers and transactions
Nesting and recursion
instead of insert triggers
Example
instead of update trigger
instead of delete trigger
searched and positioned update and delete
Getting information about triggers
Transactions: Maintaining Data Consistency and Recovery
How transactions work
Transactions and consistency
Transactions and recovery
Using transactions
Allowing data definition commands in transactions
System procedures that are not allowed in transactions
Beginning and committing transactions
Rolling back and saving transactions
Checking the state of transactions
Nested transactions
Example of a transaction
Selecting the transaction mode and isolation level
Choosing a transaction mode
Transaction modes and nested transactions
Finding the status of the current transaction mode
Choosing an isolation level
Default isolation levels for Adaptive Server and ANSI SQL
Dirty reads
Repeatable reads
Finding the status of the current isolation level
Changing the isolation level for a query
Isolation level precedences
Cursors and isolation levels
Stored procedures and isolation levels
Triggers and isolation levels
Compliance with SQL standards
Using the lock table command to improve performance
Syntax of the lock table command
Using transactions in stored procedures and triggers
Errors and transaction rollbacks
Transaction modes and stored procedures
Setting transaction modes for stored procedures
Using cursors in transactions
Issues to consider when using transactions
Backup and recovery of transactions
Locking Commands and Options
Setting a time limit on waiting for locks
wait/nowait option of the lock table command
Setting a session-level lock-wait limit
Setting a server-wide lock-wait limit
Information on the number of lock-wait timeouts
Readpast locking for queue processing
readpast syntax
Incompatible locks during readpast queries
Allpages-locked tables and readpast queries
Effects of isolation levels select queries with readpast
Session-level transaction isolation levels and readpast
Query-level isolation levels and readpast
Data modification commands with readpast and isolation levels
text, unitext, and image columns and readpast
Readpast-locking examples
The pubs2 Database
Tables in the pubs2 database
publishers table
authors table
titles table
titleauthor table
salesdetail table
sales table
stores table
roysched table
discounts table
blurbs table
au_pix table
Diagram of the pubs2 database
The pubs3 Database
Tables in the pubs3 database
publishers table
authors table
titles table
titleauthor table
salesdetail table
sales table
stores table
store_employees table
roysched table
discounts table
blurbs table
Diagram of the pubs3 database
Encrypted Columns Users Guide Adaptive Server Enterprise 15.5
About this book
Overview of Encryption
Creating and Managing Encryption Keys
Creating encryption keys
Key protection
Granting access to keys
Key protection using the system-encryption password
Changing the key
Separating keys from data
Dropping encryption keys
Encrypting Data
Specifying encryption on new tables
Specifying encryption on select into
Encrypting data in existing tables
Creating indexes and constraints on encrypted columns
Creating domain and access rules on encrypted columns
Decrypt permission
Revoking decryption permission
Restricting decrypt permission
Assigning privileges for restricted decrypt permissions
Returning default values instead of decrypted data
Defining a decrypt default
Permissions and decrypt default
Columns with decrypt default values
Decrypt default columns and query qualifications
decrypt default and implicit grants
decrypt default and insert, update, and delete statements
Removing decrypt defaults
Length of encrypted columns
Accessing Encrypted Data
Processing encrypted columns
Permissions for decryption
Dropping encryption
Protecting Data Privacy from the Administrator
Role of the key custodian
Users, roles, and data access
Key protection using user-specified passwords
Changing a key’s password
Creating key copies
Changing passwords on key copies
Accessing encrypted data with user password
Application transparency using login passwords on key copies
Login password change and key copies
Dropping a key copy
Recovering Keys from Lost Passwords
Loss of password on key copy
Loss of login password
Loss of password on base key
Key recovery commands
Changing ownership of encryption keys
Auditing Encrypted Columns
Auditing options
Audit values
Event names and numbers
Masking passwords in command text auditing
Auditing actions of the key custodian
Performance Considerations
Indexes on encrypted columns
Sort orders and encrypted columns
Joins on encrypted columns
Search arguments and encrypted columns
Movement of encrypted data as cipher text
In-Memory Database Users Guide Adaptive Server Enterprise 15.5
About this book
In-Memory Databases
Cache and buffer support
Durability levels
Temporary databases and in-memory temporary databases
Multidatabase transactions and database types
Template databases
Altering the database to use a new template
Minimally logged commands
Limits for in-memory and relaxed-durability databases
Changed system procedures
Managing In-Memory and Relaxed-Durability Databases
Specifying named caches for in-memory databases
Verifying changes to the configuration file
Changing static configuration parameters for in-memory databases
Creating in-memory devices
Creating in-memory databases
Creating disk-resident databases with relaxed durability
Administering in-memory databases
Resizing in-memory storage caches
Deleting in-memory storage caches
Increasing the size of in-memory databases
Dumping and loading in-memory databases
Configuring number of backup connections
Dropping in-memory databases
Dropping in-memory devices
Minimally Logged DML
Types of DML logging settings
Database-level logging
Table-level logging
Session-level logging
Additional minimal logging rules
Transactional semantics
Logging concurrent transactions
Minimal logging with ddl in tran set to true
Effect of referential integrity constraints
Multistatement transactions in minimally logged mode
Stored procedures and minimally logged DML
Including set dml_logging in a trigger
Using deferred updates
Obtaining diagnostic information
Performance and Tuning for In-Memory Databases
Configuring in-memory storage cache
Cache layout
sp_sysmon output for in-memory databases
Monitoring the default data cache performance
Organizing physical data for in-memory devices
Performance optimization for low-durability databases
Tuning checkpoint intervals
Minimally logged DML
Dumping and loading in-memory databases
Tuning for spinlock contention and network connections
Improving contention for lock manager hashtable spinlock ratios
Determining the number of network connections
Using Adaptive Server Distributed Transaction Management Features Adaptive Server Enterprise 15.5
About this book
Overview
Distributed Transaction Management features
Affected transaction types
Distributed transactions coordinated by external transaction managers
Behavior for transaction manager-coordinated transactions
Enhanced transaction manager for Adaptive Server version 15.0.3 or later
RPC and CIS transactions
New behavior for RPC and CIS transactions
SYB2PC transactions
Enabling DTM Features
Installing a license key
Enabling DTM features
enable dtm parameter
enable xact coordination parameter
Configuring transaction resources
Calculating required transaction descriptors
Setting the number of transaction descriptors
Using Adaptive Server Transaction Coordination Services
Overview of transaction coordination services
Hierarchical transaction coordination
X/Open XA-compliant behavior in DTP environments
Requirements and behavior
Configuring participant server resources
number of dtx participants parameter
Optimizing number of dtx participants for your system
Using transaction coordination services in heterogeneous environments
strict dtm enforcement parameter
Monitoring coordinated transactions and participants
DTM Administration and Troubleshooting
Transactions and threads of control
Implications for system administrators
dtm detach timeout period parameter
Lock manager changes to support detached transactions
Getting information about distributed transactions
Transaction identification in systransactions
Transaction keys
Viewing active transactions with sp_transactions
Identifying local, remote, and external transactions
Identifying the transaction coordinator
Viewing the transaction thread of control
Understanding transaction state information
Transaction failover information
Determining the commit node and gtrid with sp_transactions
Commit and parent nodes
Global transaction ID
Steps to execute external transactions
Crash recovery procedures for distributed transactions
Transactions coordinated with MSDTC
Transactions coordinated by Adaptive Server or X/Open XA
Transactions coordinated with SYB2PC
Heuristically completing transactions
Completing prepared transactions
Forgetting heuristically completed transactions
Manually clearing the commit status
Completing transactions that are not prepared
Determining the commit status for Adaptive Server transactions
Programming versus configuration considerations
Behavior of DDLs within distributed transactions
Adaptive Server implicit rollback in external transactions
Using Backup Server with IBM Tivoli Storage Manager Adaptive Server Enterprise 15.5
About this book
Creating Backups Using the IBM Tivoli Storage Manager
Installing and setting up the backup system
Installing the backup system
Configuring TSM to allow different source and target machines
Configuring TSM data compression
TSM concepts and Backup Server
Logical structures on TSM
Object naming and data organization
Backing up databases and transactions
Using dump and load with the same and different databases
Using dump and load when the source and target Adaptive Servers are different
Using dump and load with multiple stripes
Listing a server’s backup objects
Deleting backup objects from TSM
Using Sybase Failover in a High Availability System Adaptive Server Enterprise 15.5
About this book
What is High Availability?
Differences between active-active and active-passive
Requirements for failover
Resource requirements
How does Sybase Failover work with high availability?
Single-system presentation
Special considerations for Sybase Failover
Installing the monitoring table scripts
Using disk mirroring
Running the installhasvss script
Creating a SYB_HACMP server entry
Defining user-defined datatypes
Adaptive Server and two-phase commit transactions
Failover and Failback
What is failover?
Client connections during fail over
User logins in failover
What is failback?
Performing failback
Cluster locks in a high availability node
Asymmetric and Symmetric Configurations
Asymmetric and symmetric configuration
Configuring the asymmetric companion
Performance of Adaptive Server in an asymmetric configuration
Configuring the symmetric companion
Performance of Adaptive Server in a symmetric configuration
Auditing in a high availability system
Setting auditing options
sybsecurity and Sybase Failover
Audit trails and Sybase Failover
Modes of Failover
What are modes?
Different modes of a companion server
Determining the companion’s mode
Domains
Proxy Databases, User Databases, and Proxy System Tables
Proxy databases
Creating proxy databases
When are proxy databases created?
Size of the proxy databases
Commands and system procedures in proxy databases
Changes to commands in proxy databases
Changes to system procedures in proxy databases
Issuing user-defined stored procedures in proxy databases
Manually updating the proxy databases
Proxy system tables in master
Running do_advisory
What is the do_advisory option?
Running the do_advisory option
Quorum attributes
Configuring Adaptive Server for Failover on HP
Hardware and operating system requirements
Preparing Adaptive Server for high availability
Installing Adaptive Servers
Adding entries for both Adaptive Servers to the interfaces file
Adding entries to interfaces file for client connections
Setting the value of $SYBASE
Configuring sybha executable
Creating a new default device other than master
Adding the local server to sysservers
Adding the secondary companion to sysservers
Running installhasvss
Assigning ha_role to system administrator
Verifying configuration parameters
Configuring HP for failover
Creating the package configuration
Editing the ASE_HA.sh script
Creating the package control script
Verifying and distributing the configuration
Starting the primary and secondary companions
Configuring companion servers for failover
Running sp_companion with do_advisory option
Creating an asymmetric companion configuration
Creating the symmetric configuration
Administering Sybase Failover
Failing back to the primary companion and resuming normal companion mode
Suspending companion mode
Resuming normal companion mode from suspended mode
Dropping companion mode
Troubleshooting Sybase Failover on HP
Error message 18750
Recovering from a failed prepare_failback
Location of error logs
Configuring Adaptive Server for Failover on IBM AIX HACMP
Hardware and operating system requirements
Requirements for running Failover on IBM AIX HACMP
Special considerations for running Adaptive Server on HACMP for AIX
Preparing Adaptive Server to work with high availability
Installing Adaptive Servers
Adding entries for both Adaptive Servers to the interfaces file
Adding entries to the interfaces file for client connections
Setting the value of $SYBASE
sybha executable
Verifying configuration parameters
Adding thresholds to the master log
Creating a new default device other than master
Adding a local server to sysservers
Adding a secondary companion to sysservers
Running installhasvss script
Assigning ha_role to System Administrator
Configuring the IBM AIX subsystem for Sybase Failover
Modifying the ASE_HA.sh script
Configuring resource groups in HACMP
Configuring companion servers for failover
Running sp_companion with do_advisory option
Creating an asymmetric companion configuration
Creating the symmetric configuration
Starting the primary companion as a monitored resource
Administering Sybase Failover
Failing back to the primary node
Failing back manually
Suspending companion mode
Restarting companion during suspended mode
Resuming normal companion mode
Resuming normal companion mode from suspended mode
Resuming normal companion mode from failover mode
Dropping companion mode
Troubleshooting fail over for HACMP for AIX
Error message 18750
Recovering from a failed prepare_failback
Location of error logs
Active-Active Configuration for Sun Cluster 3.0 and 3.1
Hardware and operating system requirements
Active-active setup in Sun Cluster
Preparing Adaptive Server for active-active setup
Installing Adaptive Servers
Adding entries for both Adaptive Servers to the interfaces file
The value of $SYBASE is the same for both companions
Executing sybha
Creating new default devices
Adding the local server to sysservers
Adding secondary companion to sysservers
Assigning the ha_role to System Administrator
Running installhasvss script
Verifying configuration parameters
Adding thresholds to the master log
Adding user and login for fault monitor
Configuring the Sun Cluster subsystem
Using the syscadm script
Sample sysc_input_file
Adaptive Server resource extension properties
Configuring Adaptive Server resource groups
Using SUNW.HAStoragePlus
Configuring companion servers for failover
High availability services library within Adaptive Server
Running sp_companion with do_advisory
Before initiating sp_companion
Creating an asymmetric companion configuration
Setting up a symmetric configuration
Administering Sybase Failover
Failing back to the primary companion
Suspending normal companion mode
Resuming normal companion mode
Dropping companion mode
Verifying high availability on Sun Cluster
Configuring the resource groups manually
Primary companion resource group
Secondary companion resource group
Troubleshooting
Recovering from a failed prepare_failback
Recovering from a secondary failover on the secondary companion
Preventing the failover of secondary companion
Changing resource and resource group state
Location of the error logs
Active-Passive Configuration for Sun Cluster 3.0 and 3.1
Hardware and operating system requirements
Active-passive setup in Sun Cluster
Failing back in an active-passive configuration
Clients in an active-passive configuration
Preparing Adaptive Server for active-passive setup
Installing Adaptive Server
Passing environment to Adaptive Server
Running the SySam license manager in the cluster
Adding an entry for Adaptive Server to the interfaces file
Configuring the interfaces file on the server side
Configuring the interfaces file on the client side
Verifying configuration parameters
Adding thresholds to the master log
Adding user and login for fault monitor
Configuring the Sun Cluster subsystem
Using the syscadm script
Sample sysc_input_file
Configuring the Adaptive Server resource group
Using SUNW.HAStoragePlus
Verifying the active-passive configuration
Working with a multi-node cluster
Multi-node setup
Configuring the resource group manually
Location of the error logs
Configuring Adaptive Server for Failover on Veritas 4.0
Hardware and operating system requirements
Preparing Adaptive Server to work with high availability
Installing Adaptive Servers
Adding entries for both Adaptive Servers to the interfaces file
Adding entries to the interfaces file for client connections during fail over
sybha executable
Creating a new default device
Adding the local server to sysservers
Adding secondary companion to sysservers
Assigning ha_role
Installing high availability stored procedures
Verifying configuration parameters
Adding thresholds to the master log
Configuring the Veritas subsystem for Sybase Failover
Installing the HAase agent
Creating an Adaptive Server login file
Importing the HAase resource type
Starting the HAase agent
Adding the HAase resource
Configuring an instance of the HAase resource for each service group
Configuring companion servers for failover
Adding user and login for high availability monitor
Running sp_companion with do_advisory option
Verifying the high availability agent
Creating an asymmetric companion configuration
Configuring for symmetric configuration
Administering Sybase Failover
During failover
Failing back to the primary companion
Suspending normal companion mode
Resuming normal companion mode
Dropping companion mode
Troubleshooting failover for Veritas Cluster
Recovering from a failed prepare_failback
Location of the logs
Upgrading from an agent of resource type Sybase
Upgrading companions with a binary replacement
Configuring Adaptive Server for Failover on Windows
Hardware and operating system requirements
Installing Adaptive Servers
Changing the domain administration account
Adding entries for both Adaptive Servers to sql.ini
Adding entries to sql.ini for client connections
Creating a new default device other than master
Adding the primary companion as a local server
Adding secondary companion to sysservers
Running insthasv to install high availability stored procedures
Assigning ha_role to the System Administrator
Verifying configuration parameters
Running sp_companion with do_advisory option
Configuring Windows
Asymmetric setup from the command line
Symmetric configuration from the command line
Configuring Windows using Cluster Administrator
Configuring and securing Microsoft Cluster Server
Checking the MSCS configuration
Securing the MSCS cluster
Troubleshooting
Error message 18750
Recovering from a failed prepare_failback
Open Client Functionality in a Failover Configuration
CTLIB application changes
Troubleshooting Secondary Points of Failure
Troubleshooting with dbcc ha_admin
Reinstalling installmaster
Rerunning installhasvss
Using dbcc ha_admin for rolling back failover commands
Using @@hacmpservername
Error messages
Changes to Commands, System Procedures, and Databases
Changes to commands
Changes to system procedures
System procedures hold table lock
System procedures that synchronize changes
Other changes to system procedures
dbcc options for high availability systems
dbcc dbrepair option
Glossary
Utility Guide Windows and UNIX Adaptive Server Enterprise 15.5
About this book
Building Servers Using dataserver
Introduction
Building a new master device
Environments when using dataserver
build mode
start mode
Upgrading to a server with larger page sizes
Viewing the current server limits
Using Interactive isql from the Command Line
Before you begin
Starting and stopping isql
How to use Transact-SQL in isql
Formatting isql output
Correcting input
set options that affect output
Changing the command terminator
Performance statistics interaction with command terminator values
Setting the network packet size
Input and output files
UNIX command-line redirection
Using Interactive SQL in Graphics Mode
Starting Interactive SQL
Main window description
Plan dialog tab
Using the Interactive SQL toolbar
Opening multiple windows
Keyboard shortcuts
Using Interactive SQL to display data
Editing table values in Interactive SQL
Copying rows from the Interactive SQL result set
Editing table values from the Interactive SQL result set
Inserting rows into the database from the Interactive SQL result set
Deleting rows from the database using Interactive SQL
Working with SQL statements in Interactive SQL
Canceling an Interactive SQL command
Combining multiple statements
Looking up tables, columns, and procedures
Recalling commands
Logging commands
Configuring Interactive SQL
General dialog box
Result dialog box
Import/Export dialog box
Messages dialog tab
Editor
Query Editor
Running command files
Writing output to a file
Executing command files
Saving, loading, and running command files
Using SQL escape syntax in Interactive SQL
Interactive SQL commands
Using bcp to Transfer Data to and from Adaptive Server
Methods for moving data
Importing and exporting data with bcp
bcp requirements
bcp modes
Copying data to a file
Examples
Bulk copying encrypted data
bcp performance
Using fast or slow bcp
Copying in data with fast bcp
Bulk copying data into partitioned tables
Monitoring bcp sessions with dbcc checktable and sp_helpsegment
Reducing logging by increasing page allocations
Using parallel bulk copy to copy data into a specific partition
bcp in and locks
Parallel bulk copy methods
Parallel bulk copy syntax
Using parallel bulk copy on round-robin partitioned tables
Parallel bulk copy and IDENTITY columns
Using the bcp options
Using the default formats
Native format
Character format
Changing terminators from the command line
Changing the defaults: interactive bcp
Responding to bcp prompts
File storage type
Prefix length
Field length
Field and row terminators
Choosing Terminators
Using format files
Elements of the bcp format file
Examples: copying out data interactively
Copying out data with field lengths
Copying out data with delimiters
Comma-delimited, newline-delimited with format file
Tab-delimited with format file
Examples: copying in data interactively
Copying in data with field lengths
Copying in data with delimiters
Copying in data with a format file
Using bcp with alternate languages
bcp and row-level access rules
Copy in and batch files
Improving recoverability
Batches and partitioned tables
Copy out and text and image data
Specifying a network packet size
Copy in and error files
Copy out and error files
Data integrity: defaults, rules, and triggers
Defaults and datatypes
Rules and triggers
How bcp differs from other utilities
Using dscp
Getting started with dscp
Using a dscp session
Working with server entries
Adding and modifying server entries
Copying server entries
Listing and viewing contents of server entries
Deleting server entries
Exiting dscp
Quick reference for dscp utility commands
Using dsedit
Getting started with dsedit
Starting dsedit
Opening an editing session
Adding, viewing, and editing server entries
Modifying server entries in Windows
Modifying server entries in UNIX platforms
Adding or editing network transport addresses
SPX/IPX addresses
Copying server entries
Troubleshooting dsedit
The dsedit utility does not start
Error message: “Unable to open X display”
Cannot add, modify, or delete server entries
Utility Commands Reference
Getting started
*_r utilities
Utilities quick reference
Installation or configuration utilities
Utilities for languages, character sets, and sort orders
Utilities to start servers