Table of Contents

Open all
Close all
Acknowledgments
23
Preface
25
Introduction to SAP ASE System Administration
27
1 Introduction to SAP ASE 16
37
1.1 Placement within the SAP Landscape
38
1.2 Architecture Overview
38
1.3 SAP ASE 16: Key Features
41
1.3.1 Increased Speed and Scalability
41
1.3.2 Security and Auditing
45
1.3.3 Simplicity
46
1.4 SAP ASE Key Capabilities
50
1.4.1 SAP ASE Cluster Edition
50
1.4.2 Partitioning
51
1.4.3 Compression
52
1.4.4 Data Federation (via Component Integration Services)
52
1.4.5 Graphical Monitoring and Administration
53
1.4.6 Encryption
53
1.4.7 Replication
54
1.4.8 ASE In-Memory Database
55
1.5 Summary
55
2 Installation and Connectivity
57
2.1 Preparation
58
2.1.1 Logical Page Size
59
2.1.2 Physical Devices
60
2.1.3 Server Names
61
2.1.4 Networking Information
61
2.1.5 Sybase Software Asset Manager
61
2.2 File Transfer
62
2.3 Running Installation
62
2.3.1 Device Installation
63
2.3.2 Database Configuration
70
2.3.3 Server Startup
70
2.4 Installation Files
73
2.4.1 Interfaces File
73
2.4.2 Runserver File
76
2.4.3 Error Log File
78
2.4.4 Server Configuration File: servername.cfg
78
2.4.5 SSL in SAP ASE
79
2.4.6 Environment Variables
80
2.5 Frontend Installation
82
2.6 Starting the Server
83
2.6.1 Normal UNIX Startup
84
2.6.2 Normal Windows Startup
84
2.6.3 Manual UNIX Startup
85
2.6.4 Automatic System Boot
86
2.6.5 Verifying the Server Is Running
86
2.7 Shutting the Server Down
88
2.7.1 Graceful Shutdowns
89
2.7.2 Immediate Shutdowns
89
2.7.3 Maintenance Shutdowns
89
2.8 Summary
90
3 Defining Physical and SAP ASE-Mirrored Devices to the Server
91
3.1 Creating and Dropping Devices
92
3.1.1 Master Device Creation
93
3.1.2 Raw Devices versus File System
94
3.1.3 Create Devices
96
3.1.4 Create Devices: Examples
100
3.1.5 Default Devices
101
3.1.6 Dropping Database Devices
102
3.1.7 Dsync Option
102
3.2 SAP ASE Mirroring
103
3.2.1 Disk Mirror Syntax
104
3.2.2 Deciding What to Mirror
105
3.2.3 Disable Mirroring
107
3.2.4 Software- and Hardware-Level Mirroring
109
3.2.5 RAID
110
3.3 Volume Management
111
3.3.1 Maintenance and Ease of Use
111
3.3.2 Load Balancing
111
3.4 Summary
121
4 Defining Databases and Logs
123
4.1 Database Structures
123
4.2 System Databases
125
4.2.1 master
125
4.2.2 model
126
4.2.3 tempdb
126
4.2.4 sybsystemprocs
127
4.2.5 Other System Databases
127
4.3 Working with the Database
128
4.3.1 Create Database
128
4.3.2 Database Ownership
132
4.3.3 Creating Database Logs on Separate Devices
132
4.3.4 Sizing a Database
134
4.3.5 Alter Database
135
4.3.6 Find Database Description
137
4.3.7 Dropping the Database
140
4.3.8 Setting Database Options
141
4.4 System Tables
143
4.4.1 sysdevices
144
4.4.2 sysusages
144
4.5 Summary
147
5 Database Logging and Recovery
149
5.1 Transactions
149
5.1.1 Transaction Modes
150
5.1.2 Transaction Control Statements
152
5.2 The Transaction Log
156
5.2.1 Commit Transaction
156
5.2.2 Data to Disk
158
5.2.3 When the Transaction Log Is Full
162
5.2.4 Automatically Truncating the Log
162
5.2.5 Free Space Thresholds
167
5.3 Summary
169
6 Security, User Administration, and Roles
171
6.1 SAP ASE Security Levels
172
6.1.1 Operating System-Level Security
174
6.1.2 Server-Level Security
175
6.1.3 Database-Level Security
176
6.2 Standard Roles
181
6.2.1 The System Administrator Role
182
6.2.2 The sysusers Table
183
6.2.3 The Operator Role
185
6.2.4 The System Security Officer Role
186
6.3 Server Roles
188
6.3.1 System Role Definitions
189
6.3.2 System Role Functions
189
6.3.3 syslogins, sysloginroles, and syssrvroles
190
6.3.4 Turning System Roles Off
190
6.4 User-Defined Roles
191
6.4.1 Creating Roles
191
6.4.2 Activating Roles
192
6.4.3 Role Hierarchy
192
6.4.4 Assigning Roles to Users
192
6.4.5 Dropping Roles
193
6.4.6 Mutually Exclusive Roles
193
6.4.7 Assigning Passwords to Roles
193
6.4.8 Default Roles for Logins
194
6.4.9 Granting Permissions to Roles
195
6.4.10 Displaying Information about Roles
195
6.4.11 Show Active Roles
196
6.4.12 Display Permissions
197
6.4.13 Groups
197
6.5 Login Activities
199
6.5.1 Dropping Logins
199
6.5.2 The syslogins Table
200
6.5.3 Show Login Information
200
6.5.4 Change Existing Login Information
201
6.6 Commands
202
6.6.1 Display Server Connections
202
6.6.2 Granting the Set Proxy Command
203
6.6.3 The Kill Command
204
6.6.4 Command Security
205
6.6.5 System Built-In Functions
205
6.7 Object Permissions
207
6.7.1 With Grant Option
208
6.7.2 Revoking Object Access
209
6.7.3 Granting Vast Permissions
209
6.7.4 Displaying Permissions
210
6.7.5 Ownership Chains
210
6.7.6 Test/Change Permissions
213
6.8 Access Rules
213
6.8.1 Access Rules Using Java Function and Application Contexts
213
6.8.2 Syntax for Access Rules
214
6.8.3 Disable Access Rules
218
6.8.4 Access Rules and bcp
218
6.8.5 Fine-Grained Access Control (FGAC)
219
6.9 Column Encryption
219
6.9.1 Enabling Encryption
221
6.9.2 The sysencryptkeys Table
221
6.9.3 Creating New Tables with Encryption
223
6.9.4 Altering Existing Tables
224
6.9.5 Select Into Syntax with Encryption
224
6.9.6 Cipher Text Randomization
225
6.9.7 Column Encryption Performance Considerations
225
6.9.8 Decryption Permissions
226
6.10 Summary
227
7 Auditing
229
7.1 Installation Overview
230
7.2 The sybsecurity Database and Tables
232
7.2.1 sybsecurity Transaction Log
235
7.2.2 sybsecurity Stored Procedures
236
7.2.3 Set Auditing Options
236
7.2.4 Display Enabled Auditing Options
238
7.2.5 Write User-Defined Comment to Audit Trail
239
7.2.6 Add Audit Table to Audit Trail
240
7.3 Audit Storage
241
7.3.1 Queue
241
7.3.2 Audit Database Storage Requirements
242
7.3.3 Archiving Audit Records
244
7.3.4 Creating Audit Tables
245
7.4 Querying the Audit Trail
246
7.5 External Applications and Third-Party Software
249
7.6 Additional Tips on How to Effectively Audit
250
7.7 Summary
250
8 Backing Up and Restoring
251
8.1 Roles and Responsibilities
252
8.2 Backup Types
254
8.2.1 Backup Scenario
255
8.2.2 Restore Scenario
255
8.3 Backup Server
256
8.3.1 Remote Backup Server
257
8.3.2 Server Identification
257
8.3.3 Starting a Backup Server
258
8.3.4 Media Changes during Dump and Load
258
8.4 Dumping the Database
259
8.4.1 Dump Devices
259
8.4.2 Dump Database Command
261
8.4.3 Dump Compression
264
8.4.4 Dump Database Summary
266
8.5 Loading the Database
266
8.5.1 Loading a Corrupted Database
267
8.5.2 Online Database
268
8.5.3 Load Database Summary
268
8.5.4 Creating a Database for a Restore
269
8.6 Monitoring the Transaction Log
271
8.6.1 Last-Chance Threshold
272
8.6.2 Free-Space Thresholds
273
8.6.3 Aborting versus Suspending Transactions
274
8.7 Dumping Transactions
274
8.7.1 Truncate Transaction Log without Dump
275
8.7.2 Truncate Transaction Log without Checkpoint
275
8.7.3 Truncate Transaction Log in the Case of Media Failure
275
8.7.4 Dump Transaction Log from Primary Server
276
8.7.5 Dump Transaction Activities
276
8.8 Loading Transactions
277
8.8.1 Up-to-the-Minute Recovery
278
8.8.2 Point-in-Time Recovery
278
8.9 Restoring the Master Database
279
8.9.1 Steps to Restore
279
8.9.2 The sybdumptran Utility
281
8.10 Cumulative Dumps
282
8.11 Dumping and Loading Across Platforms
283
8.12 Quiesce Database
283
8.13 Database Recovery Scenarios: Q&A
286
8.13.1 Scenario 1
286
8.13.2 Scenario 2
287
8.13.3 Scenario 3
287
8.13.4 Scenario 4
288
8.13.5 Scenario 5
288
8.14 Miscellaneous Topics
289
8.15 Summary
290
9 Resource Governor
291
9.1 Enabling Resource Limits
292
9.2 Time Ranges
293
9.2.1 Add Time Ranges
294
9.2.2 Simplify the Creation of Time Ranges
294
9.2.3 Modify Time Ranges
295
9.2.4 Remove Time Ranges
296
9.3 Creating a Limit
296
9.3.1 Choose a Limit Type
297
9.3.2 Choose the Type of Enforcement
298
9.3.3 Choose an Action
299
9.3.4 Choose a Scope
299
9.4 Limit Hierarchies
300
9.4.1 Examples of Limit Hierarchies
300
9.4.2 View Limits
301
9.4.3 Change Limits
301
9.4.4 Remove Limits
302
9.5 System Tables
302
9.5.1 spt_limit_types
302
9.5.2 sysresourcelimits
303
9.6 Summary
303
10 Logical Process Manager
305
10.1 Purpose of the Logical Process Manager
305
10.2 Logical Process Manager Execution Classes
307
10.2.1 Base Priority
307
10.2.2 Engine Affinity
307
10.2.3 Timeslice (Quantum)
307
10.3 Logical Process Manager Procedures
308
10.3.1 Add Execution Class
309
10.3.2 Remove Execution Class
310
10.3.3 Bind Objects to Execution Class
310
10.3.4 Remove Object Bindings from an Execution Class
311
10.3.5 Set Dynamic Execution Attributes
312
10.3.6 Reset Dynamic Execution Classes
312
10.4 Thread Pools and Engine Groups
313
10.4.1 Create Engine Group
313
10.4.2 Drop Engine from Group
314
10.4.3 Detailed Examples
314
10.5 Logical Process Manager Conflicts and Precedence
317
10.6 Summary
318
11 Memory Configuration and Tuning
321
11.1 SAP ASE Releases
321
11.1.1 Pre-SAP ASE 12.5 Memory
321
11.1.2 SAP ASE 12.5 and Later Memory Use
322
11.2 SAP ASE Configuration Basics
322
11.2.1 Current Memory Configuration
323
11.2.2 Dynamic versus Static Options
326
11.2.3 Configuration System Tables
327
11.2.4 Configuration File Parameter Format
354
11.3 Summary Table of Memory-Related Variables
359
11.4 Recommended Configuration Settings
360
11.4.1 Cache Configuration
361
11.4.2 Memory Guidelines for a Cache
362
11.4.3 Calculating Procedure Cache
362
11.4.4 Sample Server Configuration
363
11.4.5 Statement Cache
364
11.5 Summary
364
12 Data Cache
365
12.1 Named Caches
365
12.1.1 Transaction Performance and Named Caches
367
12.1.2 Creating a Named Cache
367
12.1.3 Guidelines for Configuring a Named Cache
369
12.2 Buffer Pools
371
12.2.1 Creating a Buffer Pool
372
12.2.2 Removing a Buffer Pool
373
12.2.3 Using Buffer Pools
373
12.2.4 Wash Area
374
12.3 Binding
376
12.3.1 Binding an Object to a Named Cache
377
12.3.2 Dropping Cache Bindings
377
12.3.3 Information on Bindings
377
12.3.4 fred cache after Partitioning
378
12.4 How to Tune Caches
379
12.4.1 Tuning Ideas
384
12.4.2 Spinlocks
385
12.4.3 Creating Cache for In-Memory or Relaxed Durability Databases
386
12.4.4 MRU Cache Replacement Strategy
387
12.5 Summary
389
13 Semantic Data Partitioning
391
13.1 Why Use Data Partitioning?
392
13.1.1 Reducing the Cost of Managing and Maintaining Databases Using Data Partitioning
392
13.1.2 Data Availability
394
13.1.3 Index Partitioning
395
13.2 Types of Partitioning
396
13.2.1 Range Partitioning
396
13.2.2 List Partitioning
397
13.2.3 Hash Partitioning
397
13.3 How and When to Use Data Partitioning
397
13.3.1 Range Partitioning
398
13.3.2 List Partitioning
399
13.3.3 Hash Partitioning
400
13.4 Local versus Global Indexes
400
13.5 Working with Partitions
403
13.5.1 Configuring Partitions
403
13.5.2 Getting Partition Information
403
13.6 Some Uses for Semantic Partitioning
404
13.6.1 Data Loads
404
13.6.2 Data Truncation
405
13.6.3 Updating Partition Statistics
405
13.7 Summary
406
14 Remote Server Management
407
14.1 Remote Procedure Call
408
14.1.1 Server Naming
409
14.1.2 Remote Access
410
14.1.3 Login Mapping
410
14.1.4 Example: Remote Access Setup
411
14.2 Component Integration Services
413
14.2.1 Adding a Remote Server for CIS Use
414
14.2.2 Local Storage
415
14.2.3 Proxy Databases
417
14.2.4 Creating Tables from System Files
418
14.2.5 Enhanced Mapping of External Logins
419
14.2.6 File Access
421
14.2.7 SAP ASE Variable Page Size Issues
421
14.3 Summary
423
15 Preventative Maintenance Regimen
425
15.1 Server-Level Maintenance
425
15.1.1 System Use Information
426
15.1.2 MDA Tables
429
15.1.3 Locking Contention Monitoring
430
15.1.4 System Parameter Monitoring
435
15.1.5 Monitoring the System Error Log
439
15.1.6 Resource Verification
454
15.1.7 Software Maintenance
455
15.1.8 Recording Runtime Data
456
15.2 Database-Level Maintenance
456
15.2.1 Scheduling Database Maintenance
457
15.2.2 Run dbcc Commands
459
15.2.3 General-Purpose Checks
460
15.2.4 Understanding the Output from dbcc Commands
471
15.2.5 Errors Generated by dbcc
472
15.2.6 Planning Resources
473
15.2.7 Maintaining dbccdb
482
15.2.8 Generating Reports from dbccdb
485
15.2.9 Database Dumps
492
15.2.10 Disaster Recovery
493
15.2.11 Log Management
493
15.2.12 Space Management
494
15.2.13 Script Maintenance
494
15.3 Table-Level Maintenance
494
15.3.1 Update Statistics
495
15.3.2 Indexes
495
15.4 Summary
495
16 High Availability and Disaster Recovery
497
16.1 Definitions and Causes
497
16.1.1 Uptime
498
16.1.2 Data Loss Prevention
498
16.1.3 Recovery
499
16.1.4 Unavailability Causes
499
16.2 Broad Approaches to High Availability and Disaster Recovery Planning
501
16.2.1 Hardening: Reduce the Chance of Failure
501
16.2.2 Redundancy: Reduce the Impact of Failure
502
16.2.3 Recovery Planning: Reduce the Cost of Recovery Post-Failure
503
16.2.4 Hot, Warm, and Cold Standby
503
16.3 Architecting the System for Availability and Recoverability
504
16.3.1 Hardware
504
16.3.2 Operating System
506
16.3.3 Storage/Disk
507
16.4 DBA and User Activity
513
16.4.1 SAP ASE Patches
513
16.4.2 Multiple SAP ASE Listener Ports
514
16.4.3 Multiple tempdbs
516
16.4.4 SAP ASE Boot Time
516
16.4.5 Costs of Inadequate DBA Housekeeping
517
16.4.6 Offload DBA Housekeeping from Production
518
16.4.7 Key Person Dependencies
520
16.5 Backup Strategies
520
16.5.1 Incremental/Transaction Database Backups
521
16.5.2 Native Database Backups versus Other Backup Solutions
522
16.5.3 Disk Replication
523
16.5.4 Dump to Tape versus Dump to Disk
524
16.5.5 Test Dumps by Loading Elsewhere
525
16.5.6 Test Dumps by Validating
525
16.6 Cold Standby: Dump Shipping
525
16.6.1 Dump and Load Database
526
16.6.2 dump and load tran
526
16.6.3 Dump with standby_access, load, online database for standby_access
527
16.7 Warm Standby: SAP Replication Server
528
16.7.1 Replicate Transactions, Not Data
528
16.7.2 Warm Standby versus Multi-Site Availability
529
16.7.3 Reduce Downtime for Upgrades and Migrations
531
16.8 Hot Standby: Clustering
532
16.8.1 Vanilla SAP ASE with OS Clustering
532
16.8.2 SAP ASE HA with OS Clustering
533
16.8.3 SAP ASE Cluster Edition
535
16.9 Summary
537
17 SAP Replication Server
539
17.1 Advantages of SAP Replication Server
539
17.2 New Features in SAP Replication Server (Support Packages Series)
540
17.3 Replication Methods
544
17.4 Components and Features
545
17.4.1 System Tables
546
17.4.2 Partitions and Stable Queues
546
17.4.3 Data Servers
547
17.4.4 Replication Agent
547
17.5 Installation of SAP Replication Server
547
17.5.1 Obtaining a License at SPDC or SMP
548
17.5.2 Installation of SAP Replication Server Software
549
17.5.3 Preparation for Installing and Configuring SAP Replication Server
550
17.5.4 Perform Post-Installation Tasks
554
17.6 Setting Up Warm Standby
556
17.6.1 Consistency and Latency
557
17.6.2 Warm Standby Considerations
558
17.6.3 Setting Up Warm Standby Replication
559
17.6.4 Setting Up the Warm Standby at the Database
561
17.6.5 Adding the Standby Database to the Replication System
562
17.6.6 Switching between the Active and Standby Databases
563
17.7 Troubleshooting Overview
565
17.8 Summary
566
18 Introduction to Query Optimization
569
18.1 Changes in SAP ASE 16
570
18.1.1 Showplan Utility
571
18.1.2 Relaxed Query Limits
572
18.1.3 Improvements in the Hash Joins
572
18.1.4 Improvements in the Query Plan and Execution Statistics in HTML
572
18.2 Query Optimization Process
573
18.3 Layers of SAP ASE 16
576
18.3.1 Application Layer
576
18.3.2 Database Layer
576
18.3.3 Network Layer Loads
577
18.3.4 Hardware Device Layer
577
18.3.5 Operating System Layer
577
18.4 Query Processor and the I/O Size
578
18.4.1 Determine Total Actual I/O Cost Value
578
18.4.2 Search Engine
579
18.4.3 Display Access Method Costs
580
18.5 Query Optimization and the Performance Impacts on Tuning
581
18.5.1 Statistics in Query Optimization
582
18.5.2 Storing Statistics in Tables
583
18.5.3 Parallel Query Processing
585
18.5.4 Controlling the Query Optimization
586
18.6 Automatically Updating Statistics
587
18.6.1 The datachange Function
588
18.6.2 Viewing the Processor Statistics Using the optdiag Utility
589
18.6.3 Deciding How Often to Run update statistics
594
18.6.4 Recommendations for Adding Statistics for Unindexed Columns
595
18.6.5 User Input
595
18.7 Changing Rules for LRU and MRU in Query Optimization
595
18.7.1 Default Strategy (LRU)
596
18.7.2 Fetch-and-Discard (MRU) Strategy
596
18.8 Summary
598
19 SAP ASE In-Memory Database
599
19.1 Use Cases for SAP ASE IMDBs
600
19.2 Difference between SAP ASE IMDB and Traditional SAP ASE
602
19.2.1 Performance
602
19.2.2 Recovery/Durability
603
19.2.3 Database Cache
604
19.3 Operations Supported By Different Database Configurations
605
19.3.1 In-Memory Temporary Databases
606
19.3.2 Steps to Create an In-Memory Database
606
19.3.3 Administering In-Memory Databases
609
19.3.4 Using Minimally Logged DML
610
19.4 Creating and Managing Relaxed Durability Databases
611
19.5 Summary
612
20 Tools
613
20.1 Bulk Copy Program
614
20.1.1 bcp Modes
616
20.1.2 bcp Command Examples
617
20.2 Copy Definitions (defncopy)
619
20.2.1 defncopy Command Examples
620
20.2.2 defncopy Command Tips
620
20.3 Extract Creation Script (ddlgen)
620
20.3.1 ddlgen Command Examples
622
20.3.2 ddlgen Command Tips
623
20.4 Command Parser (isql)
623
20.4.1 isql History
624
20.4.2 isql Tips
625
20.5 Display System Table Information (optdiag)
625
20.6 Interactive SQL
626
20.7 Summary
630
Appendices
631
A Tips and Tricks
633
A.1 System-Specific Recommendations
633
A.2 Database Maintenance
634
A.3 Performance Tuning
641
A.4 Common Problems and Solutions
648
A.5 Security
685
A.6 DBA Commands: Reference Sheet
687
A.7 Acknowledgements
693
B Troubleshooting
695
B.1 The Server Won’t Come Up
695
B.2 Some Users Claim the Server Stopped
696
B.3 Server Is Up, Some Users Can’t Gain Access
696
B.4 Processing Slows Down or Stops
697
B.5 Some Database(s) Can’t Be Accessed
697
B.6 Users Can’t Access Objects
698
C Other Resources
699
D The Authors
701
Index
705