SAP ASE 16 / Sybase ASE Administration

1. Auflage 2015
ISBN: 978-1-4932-1182-1
Verlag: Rheinwerk Publishing, Inc.

Buch, Englisch, 720 Seiten, Format (B × H): 181 mm x 236 mm, Gewicht: 1470 g

Reihe: SAP PRESS Englisch

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 you'll use on any platform. Get the information 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.

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

Autoren/Hrsg.

Garbus, Jeffrey

Fachgebiete

• Mathematik | Informatik EDV | Informatik Business Application Unternehmenssoftware SAP
• Mathematik | Informatik EDV | Informatik Daten / Datenbanken Relationale Datenbanken

Inhaltsverzeichnis

... 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.4 ... SAP ASE Key Capabilities ... 50

1.5 ... Summary ... 55



2 ... Installation and Connectivity ... 57



2.1 ... Preparation ... 58

2.2 ... File Transfer ... 62

2.3 ... Running Installation ... 62

2.4 ... Installation Files ... 73

2.5 ... Frontend Installation ... 82

2.6 ... Starting the Server ... 83

2.7 ... Shutting the Server Down ... 88

2.8 ... Summary ... 90



3 ... Defining Physical and SAP ASE-Mirrored Devices to the Server ... 91



3.1 ... Creating and Dropping Devices ... 92

3.2 ... SAP ASE Mirroring ... 103

3.3 ... Volume Management ... 111

3.4 ... Summary ... 121



4 ... Defining Databases and Logs ... 123



4.1 ... Database Structures ... 123

4.2 ... System Databases ... 125

4.3 ... Working with the Database ... 128

4.4 ... System Tables ... 143

4.5 ... Summary ... 147



5 ... Database Logging and Recovery ... 149



5.1 ... Transactions ... 149

5.2 ... The Transaction Log ... 156

5.3 ... Summary ... 169



6 ... Security, User Administration, and Roles ... 171



6.1 ... SAP ASE Security Levels ... 172

6.2 ... Standard Roles ... 181

6.3 ... Server Roles ... 188

6.4 ... User-Defined Roles ... 191

6.5 ... Login Activities ... 199

6.6 ... Commands ... 202

6.7 ... Object Permissions ... 207

6.8 ... Access Rules ... 213

6.9 ... Column Encryption ... 219

6.10 ... Summary ... 227



7 ... Auditing ... 229



7.1 ... Installation Overview ... 230

7.2 ... The sybsecurity Database and Tables ... 232

7.3 ... Audit Storage ... 241

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.3 ... Backup Server ... 256

8.4 ... Dumping the Database ... 259

8.5 ... Loading the Database ... 266

8.6 ... Monitoring the Transaction Log ... 271

8.7 ... Dumping Transactions ... 274

8.8 ... Loading Transactions ... 277

8.9 ... Restoring the Master Database ... 279

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.14 ... Miscellaneous Topics ... 289

8.15 ... Summary ... 290



9 ... Resource Governor ... 291



9.1 ... Enabling Resource Limits ... 292

9.2 ... Time Ranges ... 293

9.3 ... Creating a Limit ... 296

9.4 ... Limit Hierarchies ... 300

9.5 ... System Tables ... 302

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.3 ... Logical Process Manager Procedures ... 308

10.4 ... Thread Pools and Engine Groups ... 313

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.2 ... SAP ASE Configuration Basics ... 322

11.3 ... Summary Table of Memory-Related Variables ... 359

11.4 ... Recommended Configuration Settings ... 360

11.5 ... Summary ... 364



12 ... Data Cache ... 365



12.1 ... Named Caches ... 365

12.2 ... Buffer Pools ... 371

12.3 ... Binding ... 376

12.4 ... How to Tune Caches ... 379

12.5 ... Summary ... 389



13 ... Semantic Data Partitioning ... 391

13.1 ... Why Use Data Partitioning? ... 392

13.2 ... Types of Partitioning ... 396

13.3 ... How and When to Use Data Partitioning ... 397

13.4 ... Local versus Global Indexes ... 400

13.5 ... Working with Partitions ... 403

13.6 ... Some Uses for Semantic Partitioning ... 404

13.7 ... Summary ... 406



14 ... Remote Server Management ... 407



14.1 ... Remote Procedure Call ... 408

14.2 ... Component Integration Services ... 413

14.3 ... Summary ... 423



15 ... Preventative Maintenance Regimen ... 425



15.1 ... Server-Level Maintenance ... 425

15.2 ... Database-Level Maintenance ... 456

15.3 ... Table-Level Maintenance ... 494

15.4 ... Summary ... 495



16 ... High Availability and Disaster Recovery ... 497



16.1 ... Definitions and Causes ... 497

16.2 ... Broad Approaches to High Availability and Disaster Recovery Planning ... 501

16.3 ... Architecting the System for Availability and Recoverability ... 504

16.4 ... DBA and User Activity ... 513

16.5 ... Backup Strategies ... 520

16.6 ... Cold Standby: Dump Shipping ... 525

16.7 ... Warm Standby: SAP Replication Server ... 528

16.8 ... Hot Standby: Clustering ... 532

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.5 ... Installation of SAP Replication Server ... 547

17.6 ... Setting Up Warm Standby ... 556

17.7 ... Troubleshooting Overview ... 565

17.8 ... Summary ... 566



18 ... Introduction to Query Optimization ... 569



18.1 ... Changes in SAP ASE 16 ... 570

18.2 ... Query Optimization Process ... 573

18.3 ... Layers of SAP ASE 16 ... 576

18.4 ... Query Processor and the I/O Size ... 578

18.5 ... Query Optimization and the Performance Impacts on Tuning ... 581

18.6 ... Automatically Updating Statistics ... 587

18.7 ... Changing Rules for LRU and MRU in Query Optimization ... 595

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.3 ... Operations Supported By Different Database Configurations ... 605

19.4 ... Creating and Managing Relaxed Durability Databases ... 611

19.5 ... Summary ... 612



20 ... Tools ... 613



20.1 ... Bulk Copy Program ... 614

20.2.2 ... defncopy Command Tips ... 620

20.3 ... Extract Creation Script (ddlgen) ... 620

20.4 ... Command Parser (isql) ... 623

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

Über Autor(innen)

Garbus, Jeffrey
Jeffrey Garbus has a B.S. from Rensselaer Polytechnic Institute, and has worked with everything from PCs to mainframes and back again. He has decades of client/server, Sybase, and Microsoft SQL Server experience, with a special emphasis on assisting clients in migrating from existing systems to pilot and large-scale projects with very large databases. Jeff has spoken at user conferences and user groups since the early 1990s, has written articles and columns for many magazines nationally and internationally, and has about 20 books to his name. Recently, his focus has been on database performance, tuning, monitoring, and reengineering. Jeff has been in consulting, training, and the software businesses for more than two decades. He has a demonstrated talent for staying at the leading edge of technology, as well as transferring his knowledge to others. You can contact Jeff at [e-mail].