SAP ASE 16 / Sybase ASE Administration

Book Information

• Publisher: SAP PRESS
• Authors: Jeffrey Garbus
• Year: 2015
• Edition: 1
• Pages: 720
• Languages: English

Description

Skip the marketing jargon, and jump right into the heart of the major tasks for administering SAP ASE 16. Starting with an introduction to the database and an installation overview, this book is packed with information you'll use on any platform. Get the details you need to define database storage areas, create database elements, work with security and user roles, manage backup and restoration, achieve high availability, and more.

• Understand how SAP Adaptive Server Enterprise fits into the SAP landscape
• Learn about all important SAP ASE administration tasks, from installation and configuration to security
• Work with SAP ASE add-ons and tools: Resource Governor, SAP Replication Server, and more

Key Highlights

• Database logging and recovery
• Security
• Auditing
• Named caches
• Remote procedure calls
• SAP ASE connectivity
• Resource Governor
• High availability and disaster recovery
• Remote server management
• Memory configuration

You'll learn about

• SAP ASE 16:
  Get all of the details for the newest release of ASE, including high availability and DBA tools.
• Core Database Tasks:
  Cement your administration skills on topics like data partitioning, preventative maintenance, memory configuration, and more.
• SAP ASE-Specific Tools:
  Every database is different. Learn how SAP ASE provides tools like the Logical Process Manager to prioritize resources, the Query Processor for cost-based optimization, and the Replication Server for syncing.

Table of Contents

• Acknowledgments

• Preface

• Introduction to SAP ASE System Administration

• 1 Introduction to SAP ASE 16
• 1.1 Placement within the SAP Landscape
• 1.2 Architecture Overview
• 1.3 SAP ASE 16: Key Features
• 1.3.1 Increased Speed and Scalability
• 1.3.2 Security and Auditing
• 1.3.3 Simplicity
• 1.4 SAP ASE Key Capabilities
• 1.4.1 SAP ASE Cluster Edition
• 1.4.2 Partitioning
• 1.4.3 Compression
• 1.4.4 Data Federation (via Component Integration Services)
• 1.4.5 Graphical Monitoring and Administration
• 1.4.6 Encryption
• 1.4.7 Replication
• 1.4.8 ASE In-Memory Database
• 1.5 Summary

• 2 Installation and Connectivity
• 2.1 Preparation
• 2.1.1 Logical Page Size
• 2.1.2 Physical Devices
• 2.1.3 Server Names
• 2.1.4 Networking Information
• 2.1.5 Sybase Software Asset Manager
• 2.2 File Transfer
• 2.3 Running Installation
• 2.3.1 Device Installation
• 2.3.2 Database Configuration
• 2.3.3 Server Startup
• 2.4 Installation Files
• 2.4.1 Interfaces File
• 2.4.2 Runserver File
• 2.4.3 Error Log File
• 2.4.4 Server Configuration File: servername.cfg
• 2.4.5 SSL in SAP ASE
• 2.4.6 Environment Variables
• 2.5 Frontend Installation
• 2.6 Starting the Server
• 2.6.1 Normal UNIX Startup
• 2.6.2 Normal Windows Startup
• 2.6.3 Manual UNIX Startup
• 2.6.4 Automatic System Boot
• 2.6.5 Verifying the Server Is Running
• 2.7 Shutting the Server Down
• 2.7.1 Graceful Shutdowns
• 2.7.2 Immediate Shutdowns
• 2.7.3 Maintenance Shutdowns
• 2.8 Summary

• 3 Defining Physical and SAP ASE-Mirrored Devices to the Server
• 3.1 Creating and Dropping Devices
• 3.1.1 Master Device Creation
• 3.1.2 Raw Devices versus File System
• 3.1.3 Create Devices
• 3.1.4 Create Devices: Examples
• 3.1.5 Default Devices
• 3.1.6 Dropping Database Devices
• 3.1.7 Dsync Option
• 3.2 SAP ASE Mirroring
• 3.2.1 Disk Mirror Syntax
• 3.2.2 Deciding What to Mirror
• 3.2.3 Disable Mirroring
• 3.2.4 Software- and Hardware-Level Mirroring
• 3.2.5 RAID
• 3.3 Volume Management
• 3.3.1 Maintenance and Ease of Use
• 3.3.2 Load Balancing
• 3.4 Summary

• 4 Defining Databases and Logs
• 4.1 Database Structures
• 4.2 System Databases
• 4.2.1 master
• 4.2.2 model
• 4.2.3 tempdb
• 4.2.4 sybsystemprocs
• 4.2.5 Other System Databases
• 4.3 Working with the Database
• 4.3.1 Create Database
• 4.3.2 Database Ownership
• 4.3.3 Creating Database Logs on Separate Devices
• 4.3.4 Sizing a Database
• 4.3.5 Alter Database
• 4.3.6 Find Database Description
• 4.3.7 Dropping the Database
• 4.3.8 Setting Database Options
• 4.4 System Tables
• 4.4.1 sysdevices
• 4.4.2 sysusages
• 4.5 Summary

• 5 Database Logging and Recovery
• 5.1 Transactions
• 5.1.1 Transaction Modes
• 5.1.2 Transaction Control Statements
• 5.2 The Transaction Log
• 5.2.1 Commit Transaction
• 5.2.2 Data to Disk
• 5.2.3 When the Transaction Log Is Full
• 5.2.4 Automatically Truncating the Log
• 5.2.5 Free Space Thresholds
• 5.3 Summary

• 6 Security, User Administration, and Roles
• 6.1 SAP ASE Security Levels
• 6.1.1 Operating System-Level Security
• 6.1.2 Server-Level Security
• 6.1.3 Database-Level Security
• 6.2 Standard Roles
• 6.2.1 The System Administrator Role
• 6.2.2 The sysusers Table
• 6.2.3 The Operator Role
• 6.2.4 The System Security Officer Role
• 6.3 Server Roles
• 6.3.1 System Role Definitions
• 6.3.2 System Role Functions
• 6.3.3 syslogins, sysloginroles, and syssrvroles
• 6.3.4 Turning System Roles Off
• 6.4 User-Defined Roles
• 6.4.1 Creating Roles
• 6.4.2 Activating Roles
• 6.4.3 Role Hierarchy
• 6.4.4 Assigning Roles to Users
• 6.4.5 Dropping Roles
• 6.4.6 Mutually Exclusive Roles
• 6.4.7 Assigning Passwords to Roles
• 6.4.8 Default Roles for Logins
• 6.4.9 Granting Permissions to Roles
• 6.4.10 Displaying Information about Roles
• 6.4.11 Show Active Roles
• 6.4.12 Display Permissions
• 6.4.13 Groups
• 6.5 Login Activities
• 6.5.1 Dropping Logins
• 6.5.2 The syslogins Table
• 6.5.3 Show Login Information
• 6.5.4 Change Existing Login Information
• 6.6 Commands
• 6.6.1 Display Server Connections
• 6.6.2 Granting the Set Proxy Command
• 6.6.3 The Kill Command
• 6.6.4 Command Security
• 6.6.5 System Built-In Functions
• 6.7 Object Permissions
• 6.7.1 With Grant Option
• 6.7.2 Revoking Object Access
• 6.7.3 Granting Vast Permissions
• 6.7.4 Displaying Permissions
• 6.7.5 Ownership Chains
• 6.7.6 Test/Change Permissions
• 6.8 Access Rules
• 6.8.1 Access Rules Using Java Function and Application Contexts
• 6.8.2 Syntax for Access Rules
• 6.8.3 Disable Access Rules
• 6.8.4 Access Rules and bcp
• 6.8.5 Fine-Grained Access Control (FGAC)
• 6.9 Column Encryption
• 6.9.1 Enabling Encryption
• 6.9.2 The sysencryptkeys Table
• 6.9.3 Creating New Tables with Encryption
• 6.9.4 Altering Existing Tables
• 6.9.5 Select Into Syntax with Encryption
• 6.9.6 Cipher Text Randomization
• 6.9.7 Column Encryption Performance Considerations
• 6.9.8 Decryption Permissions
• 6.10 Summary

• 7 Auditing
• 7.1 Installation Overview
• 7.2 The sybsecurity Database and Tables
• 7.2.1 sybsecurity Transaction Log
• 7.2.2 sybsecurity Stored Procedures
• 7.2.3 Set Auditing Options
• 7.2.4 Display Enabled Auditing Options
• 7.2.5 Write User-Defined Comment to Audit Trail
• 7.2.6 Add Audit Table to Audit Trail
• 7.3 Audit Storage
• 7.3.1 Queue
• 7.3.2 Audit Database Storage Requirements
• 7.3.3 Archiving Audit Records
• 7.3.4 Creating Audit Tables
• 7.4 Querying the Audit Trail
• 7.5 External Applications and Third-Party Software
• 7.6 Additional Tips on How to Effectively Audit
• 7.7 Summary

• 8 Backing Up and Restoring
• 8.1 Roles and Responsibilities
• 8.2 Backup Types
• 8.2.1 Backup Scenario
• 8.2.2 Restore Scenario
• 8.3 Backup Server
• 8.3.1 Remote Backup Server
• 8.3.2 Server Identification
• 8.3.3 Starting a Backup Server
• 8.3.4 Media Changes during Dump and Load
• 8.4 Dumping the Database
• 8.4.1 Dump Devices
• 8.4.2 Dump Database Command
• 8.4.3 Dump Compression
• 8.4.4 Dump Database Summary
• 8.5 Loading the Database
• 8.5.1 Loading a Corrupted Database
• 8.5.2 Online Database
• 8.5.3 Load Database Summary
• 8.5.4 Creating a Database for a Restore
• 8.6 Monitoring the Transaction Log
• 8.6.1 Last-Chance Threshold
• 8.6.2 Free-Space Thresholds
• 8.6.3 Aborting versus Suspending Transactions
• 8.7 Dumping Transactions
• 8.7.1 Truncate Transaction Log without Dump
• 8.7.2 Truncate Transaction Log without Checkpoint
• 8.7.3 Truncate Transaction Log in the Case of Media Failure
• 8.7.4 Dump Transaction Log from Primary Server
• 8.7.5 Dump Transaction Activities
• 8.8 Loading Transactions
• 8.8.1 Up-to-the-Minute Recovery
• 8.8.2 Point-in-Time Recovery
• 8.9 Restoring the Master Database
• 8.9.1 Steps to Restore
• 8.9.2 The sybdumptran Utility
• 8.10 Cumulative Dumps
• 8.11 Dumping and Loading Across Platforms
• 8.12 Quiesce Database
• 8.13 Database Recovery Scenarios: Q&A
• 8.13.1 Scenario 1
• 8.13.2 Scenario 2
• 8.13.3 Scenario 3
• 8.13.4 Scenario 4
• 8.13.5 Scenario 5
• 8.14 Miscellaneous Topics
• 8.15 Summary

• 9 Resource Governor
• 9.1 Enabling Resource Limits
• 9.2 Time Ranges
• 9.2.1 Add Time Ranges
• 9.2.2 Simplify the Creation of Time Ranges
• 9.2.3 Modify Time Ranges
• 9.2.4 Remove Time Ranges
• 9.3 Creating a Limit
• 9.3.1 Choose a Limit Type
• 9.3.2 Choose the Type of Enforcement
• 9.3.3 Choose an Action
• 9.3.4 Choose a Scope
• 9.4 Limit Hierarchies
• 9.4.1 Examples of Limit Hierarchies
• 9.4.2 View Limits
• 9.4.3 Change Limits
• 9.4.4 Remove Limits
• 9.5 System Tables
• 9.5.1 spt_limit_types
• 9.5.2 sysresourcelimits
• 9.6 Summary

• 10 Logical Process Manager
• 10.1 Purpose of the Logical Process Manager
• 10.2 Logical Process Manager Execution Classes
• 10.2.1 Base Priority
• 10.2.2 Engine Affinity
• 10.2.3 Timeslice (Quantum)
• 10.3 Logical Process Manager Procedures
• 10.3.1 Add Execution Class
• 10.3.2 Remove Execution Class
• 10.3.3 Bind Objects to Execution Class
• 10.3.4 Remove Object Bindings from an Execution Class
• 10.3.5 Set Dynamic Execution Attributes
• 10.3.6 Reset Dynamic Execution Classes
• 10.4 Thread Pools and Engine Groups
• 10.4.1 Create Engine Group
• 10.4.2 Drop Engine from Group
• 10.4.3 Detailed Examples
• 10.5 Logical Process Manager Conflicts and Precedence
• 10.6 Summary

• 11 Memory Configuration and Tuning
• 11.1 SAP ASE Releases
• 11.1.1 Pre-SAP ASE 12.5 Memory
• 11.1.2 SAP ASE 12.5 and Later Memory Use
• 11.2 SAP ASE Configuration Basics
• 11.2.1 Current Memory Configuration
• 11.2.2 Dynamic versus Static Options
• 11.2.3 Configuration System Tables
• 11.2.4 Configuration File Parameter Format
• 11.3 Summary Table of Memory-Related Variables
• 11.4 Recommended Configuration Settings
• 11.4.1 Cache Configuration
• 11.4.2 Memory Guidelines for a Cache
• 11.4.3 Calculating Procedure Cache
• 11.4.4 Sample Server Configuration
• 11.4.5 Statement Cache
• 11.5 Summary

• 12 Data Cache
• 12.1 Named Caches
• 12.1.1 Transaction Performance and Named Caches
• 12.1.2 Creating a Named Cache
• 12.1.3 Guidelines for Configuring a Named Cache
• 12.2 Buffer Pools
• 12.2.1 Creating a Buffer Pool
• 12.2.2 Removing a Buffer Pool
• 12.2.3 Using Buffer Pools
• 12.2.4 Wash Area
• 12.3 Binding
• 12.3.1 Binding an Object to a Named Cache
• 12.3.2 Dropping Cache Bindings
• 12.3.3 Information on Bindings
• 12.3.4 fred cache after Partitioning
• 12.4 How to Tune Caches
• 12.4.1 Tuning Ideas
• 12.4.2 Spinlocks
• 12.4.3 Creating Cache for In-Memory or Relaxed Durability Databases
• 12.4.4 MRU Cache Replacement Strategy
• 12.5 Summary

• 13 Semantic Data Partitioning
• 13.1 Why Use Data Partitioning?
• 13.1.1 Reducing the Cost of Managing and Maintaining Databases Using Data Partitioning
• 13.1.2 Data Availability
• 13.1.3 Index Partitioning
• 13.2 Types of Partitioning
• 13.2.1 Range Partitioning
• 13.2.2 List Partitioning
• 13.2.3 Hash Partitioning
• 13.3 How and When to Use Data Partitioning
• 13.3.1 Range Partitioning
• 13.3.2 List Partitioning
• 13.3.3 Hash Partitioning
• 13.4 Local versus Global Indexes
• 13.5 Working with Partitions
• 13.5.1 Configuring Partitions
• 13.5.2 Getting Partition Information
• 13.6 Some Uses for Semantic Partitioning
• 13.6.1 Data Loads
• 13.6.2 Data Truncation
• 13.6.3 Updating Partition Statistics
• 13.7 Summary

• 14 Remote Server Management
• 14.1 Remote Procedure Call
• 14.1.1 Server Naming
• 14.1.2 Remote Access
• 14.1.3 Login Mapping
• 14.1.4 Example: Remote Access Setup
• 14.2 Component Integration Services
• 14.2.1 Adding a Remote Server for CIS Use
• 14.2.2 Local Storage
• 14.2.3 Proxy Databases
• 14.2.4 Creating Tables from System Files
• 14.2.5 Enhanced Mapping of External Logins
• 14.2.6 File Access
• 14.2.7 SAP ASE Variable Page Size Issues
• 14.3 Summary

• 15 Preventative Maintenance Regimen
• 15.1 Server-Level Maintenance
• 15.1.1 System Use Information
• 15.1.2 MDA Tables
• 15.1.3 Locking Contention Monitoring
• 15.1.4 System Parameter Monitoring
• 15.1.5 Monitoring the System Error Log
• 15.1.6 Resource Verification
• 15.1.7 Software Maintenance
• 15.1.8 Recording Runtime Data
• 15.2 Database-Level Maintenance
• 15.2.1 Scheduling Database Maintenance
• 15.2.2 Run dbcc Commands
• 15.2.3 General-Purpose Checks
• 15.2.4 Understanding the Output from dbcc Commands
• 15.2.5 Errors Generated by dbcc
• 15.2.6 Planning Resources
• 15.2.7 Maintaining dbccdb
• 15.2.8 Generating Reports from dbccdb
• 15.2.9 Database Dumps
• 15.2.10 Disaster Recovery
• 15.2.11 Log Management
• 15.2.12 Space Management
• 15.2.13 Script Maintenance
• 15.3 Table-Level Maintenance
• 15.3.1 Update Statistics
• 15.3.2 Indexes
• 15.4 Summary

• 16 High Availability and Disaster Recovery
• 16.1 Definitions and Causes
• 16.1.1 Uptime
• 16.1.2 Data Loss Prevention
• 16.1.3 Recovery
• 16.1.4 Unavailability Causes
• 16.2 Broad Approaches to High Availability and Disaster Recovery Planning
• 16.2.1 Hardening: Reduce the Chance of Failure
• 16.2.2 Redundancy: Reduce the Impact of Failure
• 16.2.3 Recovery Planning: Reduce the Cost of Recovery Post-Failure
• 16.2.4 Hot, Warm, and Cold Standby
• 16.3 Architecting the System for Availability and Recoverability
• 16.3.1 Hardware
• 16.3.2 Operating System
• 16.3.3 Storage/Disk
• 16.4 DBA and User Activity
• 16.4.1 SAP ASE Patches
• 16.4.2 Multiple SAP ASE Listener Ports
• 16.4.3 Multiple tempdbs
• 16.4.4 SAP ASE Boot Time
• 16.4.5 Costs of Inadequate DBA Housekeeping
• 16.4.6 Offload DBA Housekeeping from Production
• 16.4.7 Key Person Dependencies
• 16.5 Backup Strategies
• 16.5.1 Incremental/Transaction Database Backups
• 16.5.2 Native Database Backups versus Other Backup Solutions
• 16.5.3 Disk Replication
• 16.5.4 Dump to Tape versus Dump to Disk
• 16.5.5 Test Dumps by Loading Elsewhere
• 16.5.6 Test Dumps by Validating
• 16.6 Cold Standby: Dump Shipping
• 16.6.1 Dump and Load Database
• 16.6.2 dump and load tran
• 16.6.3 Dump with standby_access, load, online database for standby_access
• 16.7 Warm Standby: SAP Replication Server
• 16.7.1 Replicate Transactions, Not Data
• 16.7.2 Warm Standby versus Multi-Site Availability
• 16.7.3 Reduce Downtime for Upgrades and Migrations
• 16.8 Hot Standby: Clustering
• 16.8.1 Vanilla SAP ASE with OS Clustering
• 16.8.2 SAP ASE HA with OS Clustering
• 16.8.3 SAP ASE Cluster Edition
• 16.9 Summary

• 17 SAP Replication Server
• 17.1 Advantages of SAP Replication Server
• 17.2 New Features in SAP Replication Server (Support Packages Series)
• 17.3 Replication Methods
• 17.4 Components and Features
• 17.4.1 System Tables
• 17.4.2 Partitions and Stable Queues
• 17.4.3 Data Servers
• 17.4.4 Replication Agent
• 17.5 Installation of SAP Replication Server
• 17.5.1 Obtaining a License at SPDC or SMP
• 17.5.2 Installation of SAP Replication Server Software
• 17.5.3 Preparation for Installing and Configuring SAP Replication Server
• 17.5.4 Perform Post-Installation Tasks
• 17.6 Setting Up Warm Standby
• 17.6.1 Consistency and Latency
• 17.6.2 Warm Standby Considerations
• 17.6.3 Setting Up Warm Standby Replication
• 17.6.4 Setting Up the Warm Standby at the Database
• 17.6.5 Adding the Standby Database to the Replication System
• 17.6.6 Switching between the Active and Standby Databases
• 17.7 Troubleshooting Overview
• 17.8 Summary

• 18 Introduction to Query Optimization
• 18.1 Changes in SAP ASE 16
• 18.1.1 Showplan Utility
• 18.1.2 Relaxed Query Limits
• 18.1.3 Improvements in the Hash Joins
• 18.1.4 Improvements in the Query Plan and Execution Statistics in HTML
• 18.2 Query Optimization Process
• 18.3 Layers of SAP ASE 16
• 18.3.1 Application Layer
• 18.3.2 Database Layer
• 18.3.3 Network Layer Loads
• 18.3.4 Hardware Device Layer
• 18.3.5 Operating System Layer
• 18.4 Query Processor and the I/O Size
• 18.4.1 Determine Total Actual I/O Cost Value
• 18.4.2 Search Engine
• 18.4.3 Display Access Method Costs
• 18.5 Query Optimization and the Performance Impacts on Tuning
• 18.5.1 Statistics in Query Optimization
• 18.5.2 Storing Statistics in Tables
• 18.5.3 Parallel Query Processing
• 18.5.4 Controlling the Query Optimization
• 18.6 Automatically Updating Statistics
• 18.6.1 The datachange Function
• 18.6.2 Viewing the Processor Statistics Using the optdiag Utility
• 18.6.3 Deciding How Often to Run update statistics
• 18.6.4 Recommendations for Adding Statistics for Unindexed Columns
• 18.6.5 User Input
• 18.7 Changing Rules for LRU and MRU in Query Optimization
• 18.7.1 Default Strategy (LRU)
• 18.7.2 Fetch-and-Discard (MRU) Strategy
• 18.8 Summary

• 19 SAP ASE In-Memory Database
• 19.1 Use Cases for SAP ASE IMDBs
• 19.2 Difference between SAP ASE IMDB and Traditional SAP ASE
• 19.2.1 Performance
• 19.2.2 Recovery/Durability
• 19.2.3 Database Cache
• 19.3 Operations Supported By Different Database Configurations
• 19.3.1 In-Memory Temporary Databases
• 19.3.2 Steps to Create an In-Memory Database
• 19.3.3 Administering In-Memory Databases
• 19.3.4 Using Minimally Logged DML
• 19.4 Creating and Managing Relaxed Durability Databases
• 19.5 Summary

• 20 Tools
• 20.1 Bulk Copy Program
• 20.1.1 bcp Modes
• 20.1.2 bcp Command Examples
• 20.2 Copy Definitions (defncopy)
• 20.2.1 defncopy Command Examples
• 20.2.2 defncopy Command Tips
• 20.3 Extract Creation Script (ddlgen)
• 20.3.1 ddlgen Command Examples
• 20.3.2 ddlgen Command Tips
• 20.4 Command Parser (isql)
• 20.4.1 isql History
• 20.4.2 isql Tips
• 20.5 Display System Table Information (optdiag)
• 20.6 Interactive SQL
• 20.7 Summary

• Appendices
• A Tips and Tricks
• A.1 System-Specific Recommendations
• A.2 Database Maintenance
• A.3 Performance Tuning
• A.4 Common Problems and Solutions
• A.5 Security
• A.6 DBA Commands: Reference Sheet
• A.7 Acknowledgements
• B Troubleshooting
• B.1 The Server Won't Come Up
• B.2 Some Users Claim the Server Stopped
• B.3 Server Is Up, Some Users Can't Gain Access
• B.4 Processing Slows Down or Stops
• B.5 Some Database(s) Can't Be Accessed
• B.6 Users Can't Access Objects
• C Other Resources
• D The Authors

• Index

Disclaimer

SAP, other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE (or an SAP affiliate company) in Germany and other countries. Our Company is not affiliated to SAP SE or any of its affiliated companies including but not limited to: Sybase, Business Objects, Hybris, Ariba and SuccessFactors. All other names, brands, logos, etc. are registered trade or service marks of their respective owners.