SAP ASE 16/Sybase ASE Administration PDF

SAP PRESS is a joint initiative of SAP and Rheinwerk Publishing. The know- how offered by SAP specialists combined with the expertise of Rheinwerk Publishing offers the reader expert books in the field. SAP PRESS features first-hand information and expert advice, and provides useful skills for professional decision-making. SAP PRESS offers a variety of books on technical and business-related topics for the SAP user. For further information, please visit our website: http://www.sap-press.com. Dr. Berg, Penny Sylvia SAP HANA: An Introduction (3rd Edition) 2014, 579 pages, hardcover ISBN 978-1-4932-1164-7 Haun, Hickman, Loden, Wells Implementing SAP HANA (2nd Edition) 2015, 860 pages, hardcover ISBN 978-1-4932-1176-0 Richard Bremer, Lars Breddemann SAP HANA Administration 2014, 722 pages, hardcover ISBN 978-1-59229-952-2 Miroslav Antolovic Getting Started with SAPUI5 2014, 462 pages, hardcover ISBN 978-1-59229-969-0 Jeffrey Garbus SAP ASE 16 / Sybase ASE Administration ® ® Dear Reader, If you’re picking up this book as a Sybase administrator, welcome to the world of SAP! And if SAP is your domain, welcome to the world of Sybase! Wherever you’re coming from and wherever you’re going, I think you’ll find that this book on SAP ASE is just what you’re looking for. This book details what you need to know about installing and administering the SAP ASE database. You won’t find too many SAP product-specific mentions here that will distract you from this task. But if you do find yourself starting to wade into the world of SAP systems and products, know that we have an extensive library to keep your skills sharp and up to date. So stop on by—we don’t bite! We at SAP PRESS are always eager to hear your opinion. What did you think about SAP ASE 16/Sybase ASE Administration? Your comments and suggestions are the most useful tools to help us make our books the best they can be. We encourage you to visit our website at www.sap-press.com and share your feedback. Thank you for purchasing a book from SAP PRESS! Laura Korslund Editor, SAP PRESS Rheinwerk Publishing Boston, MA [email protected] http://www.sap-press.com Notes on Usage This e-book is protected by copyright. By purchasing this e-book, you have agreed to accept and adhere to the copyrights. You are entitled to use this e-book for personal purposes. You may print and copy it, too, but also only for personal use. Sharing an electronic or printed copy with others, however, is not permitted, neither as a whole nor in parts. Of course, making them available on the Internet or in a company network is illegal as well. For detailed and legally binding usage conditions, please refer to the section Legal Notes. This e-book copy contains a digital watermark, a signature that indicates which person may use this copy: Copy No. nwem-ducj-2paf-4t7g for personal use of Juan Carlos Flores L [email protected] Imprint This e-book is a publication many contributed to, specifically: Editor Laura Korslund Acquisitions Editor Kelly Grace Weaver Copyeditor Julie McNamee Cover Design Graham Geary Photo Credit Shutterstock.com/205527658/© Wasan Srisawat Production E-Book Graham Geary Typesetting E-Book III-satz, Husby (Germany) We hope that you liked this e-book. Please share your feedback with us and read the Service Pages to find out how to contact us. The Library of Congress has cataloged the printed edition as follows: Garbus, Jeffrey. SAP ASE 16/Sybase ASE administration / Jeffrey Garbus. -- 1st edition. pages cm Includes index. ISBN 978-1-4932-1182-1 (print : alk. paper) -- ISBN 1-4932-1182-X (print : alk. paper) -- ISBN 978-1-4932-1184-5 (print and ebook : alk. paper) -- ISBN (invalid) 978-1-4932-1183-8 (ebook) 1. Client/server computing. 2. Relational databases. 3. SAP Adaptive server enterprise. 4. Sybase. I. Title. QA76.9.C55G385 2014 004’.36--dc23 2014039991 ISBN 978-1-4932-1182-1 (print) ISBN 978-1-4932-1183-8 (e-book) ISBN 978-1-4932-1184-5 (print and e-book) © 2015 by Rheinwerk Publishing Inc., Boston (MA) 1st edition 2015 Contents 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 7 Contents 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 8 Contents 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 9 Contents 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 10 Contents 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 11 Contents 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 12 Contents 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 13 Contents 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 14 Contents 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 15 Contents 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 Contents 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 17 Contents 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 Contents 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 Contents 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.1.1 UNIX Systems Backup...................................................... 633 A.1.2 Solaris: Total Physical Memory......................................... 633 A.2 Database Maintenance.................................................................. 634 A.2.1 Index Maintenance.......................................................... 634 A.2.2 Deadlocking..................................................................... 635 A.2.3 Find Source Procedure Code............................................ 636 A.2.4 Moving tempdb Off the Master Device............................ 637 A.2.5 Loading Production Databases into a Development  Environment.................................................................... 641 A.3 Performance Tuning...................................................................... 641 A.3.1 Tune I (Before You Get There).......................................... 642 A.3.2 Tune IV (Free DB)............................................................. 642 A.3.3 Tune V (After You’re There)............................................. 642 20 Contents A.3.4 Process............................................................................. 643 A.3.5 Monitoring, Scripts, Tools................................................ 644 A.4 Common Problems and Solutions.................................................. 648 A.4.1 Unique Users by Database................................................ 648 A.4.2 Kill User Connections to the Database.............................. 655 A.4.3 Database Device Utilization Report.................................. 658 A.4.4 Active versus Inactive Connections................................... 663 A.4.5 Simple Data Mining......................................................... 669 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 Service Pages............................................................................................. I Legal Notes............................................................................................... III 21 1 Acknowledgments Creating a book, especially a technical book, is a ridiculous amount of work, and without everybody pulling in the same direction, there’s simply no way to finish it. We would like to thank the following people and parties (in no particular order). Carole Achramowicz, SAP ASE marketing, for inviting us to participate in the first place. Ashok Swaminathan, in his role at SAP (not to mention for his author role!), for helping us get the many resources we needed, and answers to questions that nobody had thought to ask yet. Jeff Tallman (SAP), Mike Harrold (ISUG-Tech), and Vinod Chandran for their appropriately critical comments, who made this a better book. SAP PRESS in general and Laura Korslund in particular, for their seemingly end- less support and patience. All the folks who sent in tips for the appendix. Our families, who did without us for a time as we wrote these pages at all hours over a protracted period. You, our reader, for taking the time out of your life to read our words. Please, if you see any of us at a conference, come over and say “Hi” so that we can thank you in person! Personal Copy for Juan Carlos Flores L, [email protected] 23 © 2015 by Rheinwerk Publishing Inc., Boston (MA) 1 Preface It’s been a while since a new SAP Adaptive Server Enterprise (formerly called Syb- ase ASE, and even before that called Sybase SQL Server) book has shown up on the market; in fact, it’s been since our last one in 2006. We now have a new pub- lisher, Rheinwerk Publishing, who is supportive of technology rapidly growing in market share. Since SAP has acquired ASE, they have acquired thousands of new customers, and extended its client base. For those of you who want to learn how to manage your own SAP ASE environ- ments, this is the book for you. We talk about not only how to do the work, but why and when, with some tips from our collective century of experience. For those of you who aren’t looking to manage your own environments, give us a call; in addition to performance and tuning, remote database management is our specialty. Jeff Garbus Soaring Eagle Consulting February 2015 Personal Copy for Juan Carlos Flores L, [email protected] 25 © 2015 by Rheinwerk Publishing Inc., Boston (MA) 2 Introduction to SAP ASE System Administration The system administrator (also referred to as SA, database administrator, or some- times DBA) of an SAP ASE server is responsible for all aspects of creating, maintain- ing, and monitoring the server and database environment, including the following: 왘 Installing SAP ASE 왘 Establishing connectivity between clients and servers 왘 Installing and maintaining user databases 왘 Establishing security for SAP ASE 왘 Maintaining and troubleshooting SAP ASE 왘 Establishing disaster recovery procedures 왘 Monitoring SAP ASE 왘 Running utilities This list isn’t comprehensive; there are many duties implied by this list that may fall to the DBA/SA. Let’s look at an example of an SA’s responsibilities, before we discuss them specifically. Example A stored procedure that previously ran in 30 seconds has recently been running for over an hour. As SA, you’ll need to find out why it’s taking longer now than before. If it’s a configuration problem, then it falls into the troubleshooting category for the SA to resolve. But if the SQL inside the procedure was poorly written, then tuning the SQL falls into a gray zone. In some shops, the programmer is responsible for the performance and tuning (P&T) of the procedures, and in others (our recommendation), the DBA reviews all procedures going in to the production system and approves the procedures based on his knowledge of the system and optimizer. Either way, it usually falls to the DBA/SA to resolve optimizer problems. In the end, the SA is responsible for the server. All aspects of SAP ASE are in the domain of the SA. We’ll now take a closer look at the SA tasks mentioned in the preceding list and mention in which chapters you can find those topics discussed, starting in Chapter 2. Personal Copy for Juan Carlos Flores L, [email protected] 27 Introduction to SAP ASE System Administration Administration Tasks This book provides the details you’ll need to supplement the existing SAP ASE information that’s already available to you. First, however, we’ll provide an over- view of the tasks you’ll need to accomplish, in typical order. Installing SAP ASE Discussed in detail in Chapter 2, the process of installing SAP ASE is the first step toward a productive database management system. Always read the Installation Guide that’s specific to your platform, as there are differing requirements and rec- ommendations for different operating systems (OSs). In particular, walk through the checklist provided and make sure that you meet or exceed all OS requirements. Analyze the user databases you intend to create on this new server, and size your disk space, memory, and other configurable resources to accommodate the cur- rent needs. Be sure to also estimate growth in the foreseeable future, and ensure that the resources you’ll need will be available. There are many calculations in the System Administration Guide to help you calculate resource needs, especially in the chapter on Configuring and Tuning the Server. Establishing Connectivity between Clients and Servers For clients and servers to communicate, they must be able to locate each other by creating an address listing, which is also discussed in Chapter 2. On UNIX machines, this address listing is called the interfaces file, and on Windows NT, it’s called the sql.ini file (the term “interfaces” is used to generically refer to any of these files, regardless of OS). Within these files, you’ll list all servers (SAP ASE servers, backup servers, monitor servers, XP servers) with their network addresses. Clients will use these files to find the server they want to query, and servers will use them to listen for client requests. Within these interfaces files, there may be multiple server entries, each of which may contain multiple services. A service is a specific address used for a specific purpose. The master service (featuring the keyword master) is used for servers to track incoming requests; the query service (with the keyword query) is used by cli- ents to locate a server when sending data queries or commands. Each service will contain a specific network location and information about supported network protocols. These entries look different depending on the OS. 28 © 2015 by Rheinwerk Publishing Inc., Boston (MA) Introduction to SAP ASE System Administration Example SYBASE_PROD query tcp ether my_machine 4500 master tcp ether my_machine 4500 The easiest way to maintain these files is with the dsedit utility. SAP ASE 12.5 introduced the Lightweight Directory Access Protocol (LDAP), which will replace the interfaces file if it’s used. LDAP is an industry standard for accessing directory services. Directory services allow components to look up information by a distinguished name (DN) from an LDAP server that stores and manages server, user, and software information that is used throughout the enter- prise or over a network. The LDAP server can be located on a different platform/machine from the one on which SAP ASE or the clients are running. LDAP defines the communication pro- tocol and the content of messages exchanged between clients and servers. Mes- sages are operators, such as client requests for read, write, and query, and server responses, including data format information. The LDAP server stores and retrieves information about the following: 왘 SAP ASE, such as IP address, port number, and network protocol 왘 Security mechanisms and filters 왘 High availability companion server name The LDAP server can be configured with these access restrictions: 왘 Anonymous authentication: All data is visible to any user. 왘 User name and password authentication: SAP ASE uses the default user name and password. Installing and Maintaining User Databases Before a user database can be created, the DBA needs to set up mirrored devices (Chapter 3) and make storage available to the server. This is performed using the disk init command, illustrated in Listing 1. disk init name = "device_name" , physname = "physical_name" , Personal Copy for Juan Carlos Flores L, [email protected] 29 Introduction to SAP ASE System Administration size = {number_of_blocks | {size K|M|G} } [, vstart = virtual_address , cntrltype = controller_number ] [, dsync = {true|false}] Listing 1 disk init Syntax Detailed syntax and usage for disk init and other device-related commands are found in Chapter 4. After the devices have been initialized, the create database command can be issued. Note Prior to SAP ASE 12.5, you needed to provide a virtual device number (vdevno), but with release 12.5, that is automatically assigned. Listing 2 provides a quick example. create database database_name [on {default | database_device} [= size] [, {default | database_device} [= size]]...] [log on {default | database_device} [= size] [, {default | database_device} [= size]]...] [with {override | default_location = "pathname"}] [for {load | proxy_update}] Listing 2 create database Syntax After the database is created, you can add tables, indexes, users, and permissions. Creation of databases is covered in more detail in Chapter 4, and recovery of the database is discussed in Chapter 5. Establishing Security for SAP ASE Security is critical for the protection of SAP ASE and the data contained within and is the subject of Chapter 6. Sensitive data must be protected from improper access, whether by reading or modification. There are several levels of security within SAP ASE: 왘 At the server level, a login allows a connection to be established. 왘 At the database level, a user has access to the storage structures of data. 왘 At the object level, permissions allow the use of data creation and manipulation syntax. 30 © 2015 by Rheinwerk Publishing Inc., Boston (MA) Introduction to SAP ASE System Administration It takes a combination of these security measures to successfully create a secure SAP ASE environment. After you’ve put your chosen security scheme in place, the SAP ASE auditing fea- ture may be enabled at the SA’s discretion, discussed in Chapter 7. Auditing allows you to monitor users, commands, and administrative tasks (but adds over- head to the server). Auditing must be installed and requires its own database (syb- security) in which it retains auditing information. Reports can be generated from the auditing tables to track trends and help identify attempted security breaches. Kerberos/Distributed Computing Environment (DCE) In a distributed client-server computing environment, intruders can view or tam- per with confidential data. SAP ASE works with third-party providers to give you security services that do the following: 왘 Authenticate users, clients, and servers Make sure they are who they say they are 왘 Provide data confidentiality with encryption Ensure that data can’t be read by an intruder 왘 Provide data integrity Prevent data tampering and detect when it has occurred SSL SAP ASE security services now support Secure Sockets Layer (SSL) session-based security. SSL is the standard for securing the transmission of sensitive informa- tion, such as credit card numbers, stock trades, and banking transactions, over the Internet. The implementation of SAP ASE SSL features assumes that there is a knowledgeable system security officer who is familiar with the security policies and needs of your site, and who has a general understanding of SSL and public-key cryptography. Security is covered completely in Chapter 6 and Chapter 15. Controlling Data Flow The DBA also is responsible for how data flows throughout a system, whether it’s standalone or part of a larger network. There are several tools and applications that will help you to accomplish various tasks: Personal Copy for Juan Carlos Flores L, [email protected] 31 Introduction to SAP ASE System Administration 왘 Set user limits, discussed in Chapter 9 왘 Prioritize how system resources are used, discussed in Chapter 10 왘 Partitioning data, discussed in Chapter 13 왘 Work with a remote server, discussed in Chapter 14 왘 Decide how queries are best received, discussed in Chapter 18 System Performance What good is getting your system up and running if it doesn’t work properly? The greatest part of the SA’s role may just be making sure that the system is running optimally. This book provides some guidelines on how to configure your mem- ory optimally in Chapter 11, and how to cache your data in Chapter 12. Maintaining and Troubleshooting SAP ASE When the application databases are in place and users are allowed into the SAP ASE database, you still need to keep the server running. Logs will fill, data will outgrow its allotted space, indexes will become inefficient, and data can become corrupt. Many of these things can be detected early or avoided by general main- tenance being performed on a scheduled basis. You should consider implement- ing the following maintenance processes: 왘 Run the database consistency checker (DBCC), which is a series of commands that detects structural problems within your system. Some of these are highly resource intensive and should only be run during slow times. 왘 Periodically rebuild indexes to improve performance. This is more important on highly volatile data (lots of inserts, updates, or deletes) than on static data. 왘 Maintain statistics to improve query optimization. 왘 Add thresholds to warn when free space approaches a minimum you’ve defined. These topics are discussed in Chapter 14 and Appendix B. Establishing Disaster Recovery Procedures Disasters happen. Whatever the nature of your disaster—environmental or other- wise—you must be prepared to move the complete business (or part of the 32 © 2015 by Rheinwerk Publishing Inc., Boston (MA) Introduction to SAP ASE System Administration business) operation to a new location and be up and ready in a short amount of time. A well-thought-out and thoroughly practiced plan must be available to deal with any contingency. Learn how to devise a good preventative maintenance plan in Chapter 15. Some things to consider include the following: 왘 Spare machines with sufficient resources to run the business will be required. 왘 Networking needs must be addressed. 왘 Connectivity issues must be addressed and tested. 왘 Good backups of the databases will need to be accessible from off-site. 왘 Installation disks for all components (clients, servers, applications, etc.) must be accessible from off-site. 왘 Timetables for acceptable recovery time must be established. 왘 A step-by-step disaster recovery plan must be created. 왘 Personnel must be designated for disaster recovery. 왘 A failover site with warm or hot backups should be evaluated (determine whether it’s an option or mandatory for business survival). 왘 The plan must be tested. No matter how impressive it is on paper, it still may fall apart. Test it repeatedly. There are books on disaster recovery and how to prepare for it. Each site will have different requirements and possibly different approaches to this subject matter. Just remember that it can happen to you, so be prepared. System and database backups and restoration is discussed in Chapter 8, and disaster recovery and high availability is discussed in Chapter 16. Monitoring SAP ASE There are many ways to monitor SAP ASE, including the following: 왘 System stored procedures (Chapter 7 and Chapter 16) These procedures present information about the state of the server. 왘 Monitor and historical servers (Appendix A) These servers obtain and track performance statistics. 왘 Third–party applications (Chapter 7, Section 7.5) These applications can be run with SAP ASE. Personal Copy for Juan Carlos Flores L, [email protected] 33 Introduction to SAP ASE System Administration Whatever the tool or technique, monitoring is essential to the health of SAP ASE; knowing which resources are overused (or available in excess) allows you to max- imize SAP ASE performance. Most mature shops have their own set of monitor- ing tools, which have been developed over time. Use of Utilities SAP ASE provides a set of utilities specifically for you, the administrator, to per- form tasks for which you have sole responsibility and sole permission/rights. There are many tools discussed in this book; you can find a Sybase utilities are used at the OS level to perform special services against SAP ASE (discussed in Chapter 20): 왘 srvbuild A UNIX-based program to create the SAP ASE after it’s unloaded from CD. 왘 srvconfig A Windows NT-based program to create SAP ASE after it’s unloaded from CD. 왘 dsedit Edits the server entries in the interfaces file and LDAP servers. 왘 bcp The bulk copy program allows mass loading (or unloading) of data files into (or out of) tables. 왘 defncopy Creates script files containing the data definition language for specific database objects. 왘 optdiag Displays (and, in some cases, modifies) statistics pertaining to tables and the optimizer. Databases Within a server, a database provides the context for data. There are four types of databases stored in SAP ASE. The following system databases are essential to SAP ASE operation: 왘 master database Contains information needed to start the server, as well as all the system tables needed to govern SAP ASE. Anything that has to do with SAP ASE as a whole 34 © 2015 by Rheinwerk Publishing Inc., Boston (MA) Introduction to SAP ASE System Administration is stored in master (including permitted logins, existing databases, space allo- cation, and other broad-scope items). master is the first database to be brought online when you power up SAP ASE; therefore, it should be kept small and without user tables for faster access and startup of your SAP ASE. 왘 model database Used as a template when new databases are created, the contents of model are copied to each newly created database. Therefore, all changes that are made to model are reflected in new databases. 왘 tempdb is the temporary working area. Temporary tables are automatically created to hold intermediate results in some queries (especially when sorting is involved), and users may create temporary tables for their own purposes. Remember that the contents of tempdb aren’t permanent. tempdb is cleared and recreated each time SAP ASE is started, and some temporary tables will delete themselves even without a server restart. tempdb is heavily used by SAP ASE. If you experience performance bottlenecks, you can create more temporary databases. 왘 sybsystemprocs contains the stored procedures that are installed by the server (known as system procedures), which are used to query and change the contents of system tables. 왘 sybsystemdb is used for the data change processes known as distributed transactions (most transactions take place in a single database, but distrib- uted transactions span multiple databases and sometimes multiple servers). Special functionality: 왘 dbccdb is used to store output from the dbcc checkstorage command. 왘 sybsecurity is the auditing database, which must be installed prior to turn- ing on the auditing options. 왘 sybdiag database is installed by Sybase Technical Support to help debug problems. 왘 Sample databases pubs2 and pubs3 are optional databases. They are the source of syntax examples in the documentation set and are used in many Sybase classes for labs. The installation scripts are provided with SAP ASE. 왘 User databases These are databases created specifically for applications. The rest of this book is detail surrounding these tasks. Good luck! Personal Copy for Juan Carlos Flores L, [email protected] 35 © 2015 by Rheinwerk Publishing Inc., Boston (MA) Whether you’re new to SAP ASE or are an old hat, this chapter explain what SAP ASE is, how it fits into the landscape, and explains both new and existing features. 1 Introduction to SAP ASE 16 SAP Adaptive Server Enterprise (SAP ASE), previously known as Sybase ASE, is a high performance OLTP database that uses client-server technology. SAP ASE controls all necessary memory usage, disk access, and network functions. E. F. Codd created the concept of relational databases, and described the character- istics of the relational database management system (RDBMS). The term “relational” indicates how the data is represented to the user, and the term “client-server” indicates what kind of technology is used to access the data. In such systems, there is a division of effort between the client application (which makes a request for data) and the server (which processes and determines whether the client has access to the data and returns any results to the client). The client can exist on the same machine as the server or more typically on a different machine to spread system load. SAP ASE is widely used in capital markets and other industries that require extreme transaction processing. The most current version, SAP ASE 16, was released in early 2014. SAP ASE 16 is available on a variety of different platforms, as an on-premise product, appliance, and also as a cloud offering. Different editions of SAP ASE are available that target developers and production use. These editions vary in their capabilities and usage rights. Because editions will change and evolve with market conditions, users should refer to the SAP website (http://www.sap.com/pc/tech/database/software/adaptive-server-enterprise/ index.html) to learn about the most current offerings. This chapter provides an overview of how SAP ASE 16 is placed within the SAP landscape, and then touches on its architecture, features, and capabilities. Personal Copy for Juan Carlos Flores L, [email protected] 37 1 Introduction to SAP ASE 16 1.1 Placement within the SAP Landscape SAP’s data management portfolio consists of a comprehensive set of integrated products, which include the following: 왘 SAP HANA: The platform for next-generation applications and analytics. SAP HANA converges database and application platform capabilities in-memory to transform transactions, analytics, text analysis, and predictive and spatial pro- cessing so businesses can operate in real time. For more details, go to http:// hana.sap.com/abouthana.html. 왘 SAP ASE: Provides high performance transaction processing 왘 SAP IQ: A highly optimized RDBMS built for extreme-scale big data analytics and warehousing 왘 SAP Event Stream Processor (SAP ESP): Provides event processing capabilities, and enables the loading of stream-oriented data into SAP databases for analysis 왘 SAP SQL Anywhere: Provides mobile and embedded database capabilities These data management products share information via data federation, enabling the access of data in a remote server, as if it is a local table in another server. SAP ASE customers can use SAP HANA for their analytical applications, by accessing data in SAP ASE from SAP HANA via SAP HANA smart data access (which pro- vides access to SAP ASE data, as if the tables are local to SAP HANA). Customers can also choose to replicate the data from SAP ASE to SAP HANA using SAP Rep- lication Server, and make it available in SAP HANA. SAP HANA data can be made available in SAP ASE, as proxy tables in SAP ASE, which appear as local tables. SAP ASE users can write queries that span SAP ASE tables and SAP ASE proxy tables, and SAP ASE query processor will push as much of the query processing as possible to SAP HANA, and the results are returned to SAP ASE and the query is completed in SAP ASE (see Section 1.3.3 for more details). For more details on the SAP data management solutions, refer to http://www. sap.com/pc/tech/data-management/software/overview.html. 1.2 Architecture Overview SAP ASE is based on a multithreaded architecture that manages thousands of con- current clients. In a system with multiple cores, SAP ASE can be configured to use 38 © 2015 by Rheinwerk Publishing Inc., Boston (MA) Architecture Overview 1.2 multiple SAP ASE engines; typically each engine is assigned to a core. In threaded kernel mode (the default), each engine is an OS thread, which will execute on a core. SAP ASE also supports the process mode, in which each engine is a separate OS process. All engines communicate through shared memory and internal structures such as buffer caches and locks, which are available to each engine. SAP ASE engines ser- vice client requests. They perform all database functions, including searching data caches, issuing disk I/O read and write requests, requesting and releasing locks, updating, and logging. SAP ASE manages the way in which CPU resources are shared between the engines that process client requests. It also manages system services (such as database locking, disk I/O, and network I/O) that impact process- ing resources. As shown in Figure 1.1, the SAP ASE architecture can be conceptually thought of as a server layer and kernel layer interacting with the OS. SAP ASE Server Layer Databases Locks Transactions Users Logging Queries RDBMS Engine SAP ASE Kernel Layer Tasks Spinlocks Engines Alarms Scheduler OS Services Platform-Independent “OS” Operating System CPU Network Memory Signals Disks Disks Physical Resources Figure 1.1 SAP ASE Architecture Conceptual Overview Personal Copy for Juan Carlos Flores L, [email protected] 39 1 Introduction to SAP ASE 16 The server layer provides support for user queries, user databases, write-ahead logging to ensure recoverability, locks to ensure the desired transaction seman- tics, and concurrent database transactions. The kernel layer schedules the tasks and assigns these tasks to different engines. The data structures shared between the tasks running on different engines are protected through the use of spinlocks. The task scheduler and alarms are part of the kernel layer. How SAP ASE Processes Requests SAP ASE provides the ability to handle multiple concurrent client requests. For every new connection, SAP ASE executes the following actions: 왘 The client program establishes a network socket connection to SAP ASE. 왘 SAP ASE assigns a task from the pool of tasks, which are allocated at start-up time. The task is identified by the SAP ASE process identifier. 왘 SAP ASE transfers the context of the client request, including information such as permissions and the current database, to the task, which executes on an engine. 왘 SAP ASE parses, optimizes, and compiles the request, on the assigned en- gine. 왘 If parallel query execution is enabled, SAP ASE decomposes the query into sub- tasks to help perform the parallel query execution. The subtasks are called work units, which are executed in parallel in different engines. The results from the subtasks are combined in the engine which created the subtasks. 왘 SAP ASE executes the task in the assigned engine. If the query was executed in parallel, the task merges the results of the subtasks. 왘 The task returns the results to the client, using Tabular Data Stream (TDS) pack- ets. TDS is an application level protocol used to send requests and responses between SAP ASE clients and servers. 왘 The response from the server may return one or many result sets. For each new user connection, SAP ASE allocates a private memory, a dedicated stack, and other internal data structures. SAP ASE uses the stack to keep track of each client task’s state during processing, and uses synchronization mechanisms such as queues, locks, semaphores, and spinlocks to provide shared access to any common, modifiable data structures. SAP ASE is optimized to ensure that concurrent threads can execute with minimal contention. 40 © 2015 by Rheinwerk Publishing Inc., Boston (MA) SAP ASE 16: Key Features 1.3 The next sections will discuss the newest features and capabilities of SAP ASE 16. 1.3 SAP ASE 16: Key Features SAP ASE 16 provides significant new features that can be categorized into three main areas: 왘 Increased speed and scalability This focus area deals with providing high performance for concurrent work- loads, on machines with high core counts. These optimizations and new fea- tures enable SAP ASE 16 to linearly scale to at least 80 cores. Additionally, capa- bilities to deal with increasing data size are also provided. 왘 Security and auditing This is a continued area of emphasis due to the regulatory environments and the need for accountability. Features added in this area include full database encryption and auditing. 왘 Simplicity With the increased complexity of database environments, it’s important to enable DBAs to manage SAP ASE environments more easily. Additionally, inte- gration with SAP HANA via proxy table access has been provided, making it easier to exchange data between SAP ASE and SAP HANA. The following sections explore these features further. 1.3.1

SAP ASE 16/Sybase ASE Administration PDF

Document Details

Tags

Related

Summary

Full Transcript

Upgrade to continue