Cover image for The Official New Features Guide to Sybase ASE 15

The Official New Features Guide to Sybase ASE 15

Author Carrie King Taylor , Steve Bradley , Naresh Adurty , Brian Taylor
Release Date: 2010/11/01
ISBN: 9781598220049
Topic:
0%
31
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

Sybase Adaptive Server Enterprise 15 is a high-performance, mission-critical database management system designed to meet the increasing demands of large databases and high-transaction volumes, while providing a cost-effective database management system. The Official New Features Guide to Sybase ASE 15 focuses on the many enhancements in Sybase ASE 15 including semantic partitions, computed columns, functional indexes, and scrollable cursors. With this book learn about the enhancements to ASE 15, how the new features can increase your efficiency and productivity, and how these features integrate with existing systems; understand how semantic partitions decrease the complexities of data management; find out how scrollable cursors and computed columns can ease the development process; learn how the optimizer toolset and QP Metrics improve monitoring and diagnostic efforts; provide the ability to ensure customer compliance through the use of Sybase Software Asset Management; test your knowledge with the included Sybase certification practice exam.

Book Description

Sybase Adaptive Server Enterprise 15 is a high-performance, mission-critical database management system designed to meet the increasing demands of large databases and high-transaction volumes, while providing a cost-effective database management system. The Official New Features Guide to Sybase ASE 15 focuses on the many enhancements in Sybase ASE 15 including semantic partitions, computed columns, functional indexes, and scrollable cursors. With this book learn about the enhancements to ASE 15, how the new features can increase your efficiency and productivity, and how these features integrate with existing systems; understand how semantic partitions decrease the complexities of data management; find out how scrollable cursors and computed columns can ease the development process; learn how the optimizer toolset and QP Metrics improve monitoring and diagnostic efforts; provide the ability to ensure customer compliance through the use of Sybase Software Asset Management; test your knowledge with the included Sybase certification practice exam.

Table of Contents

  1. Dedications
  2. Contents
  3. Foreword
  4. Acknowledgments
  5. About the Authors
  6. About the Contributing Authors
  7. Introduction
    1. Audience
    2. Scope
    3. Conventions
      1. Disclaimer
  8. Part I New Features Overview
    1. Chapter 1 Exploring the Sybase Galaxy
      1. Part I — New Features Overview
        1. System Maintenance Improvements
        2. Partition Management — Semantic Partitions
        3. Scrollable Cursors
        4. Overview of Changes to Query Processing
        5. Detection and Resolution of Performance Issues in Queries
        6. Computed Columns
        7. Functional Indexes
        8. Capturing Query Processing Metrics
        9. Plan Viewer
        10. Sybase Software Asset Management (SySAM) 2.0
        11. Installation of ASE 15
      2. Part II — Pre-15 Improvements
        1. Multiple tempdb Databases
        2. MDA Tables
        3. Java and XML
      3. The Appendices
        1. Sample Certification Exam
        2. Use Cases
      4. 3, 2, 1, Contact!
    2. Chapter 2 System Maintenance Improvements
      1. Recent Pre-ASE 15 Improvements
        1. Multiple tempdb
        2. Native Data Encryption/Security Enhancements
        3. Automatic Database Expansion
          1. The Basics
        4. Job Scheduler
          1. Basic Components
          2. Installation of Job Scheduler
      2. ASE 15 Improvements
        1. Row Locked System Catalogs
        2. Update Statistics
          1. Updates to Partition Statistics
        3. Automatic Update Statistics
        4. Datachange
          1. Why Use datachange?
        5. Datachange, Semantic Partitions, and Maintenance Schedules
          1. Alternate Maintenance Schedule with Semantic Partitions
        6. Local Indexes
          1. Benefits
        7. sp_helpindex
        8. Partition-level Utilities
          1. Partition Configuration Parameters
          2. Utility Benefits from Semantic Partitions
          3. Partition-specific Database Consistency Checks (dbccs)
            1. dbcc checktable Syntax
            2. dbcc checkindex Syntax
            3. dbcc indexalloc Syntax
            4. dbcc tablealloc Syntax
          4. Reorg Partitions
            1. Benefits of Reorg Partitions
          5. Changes to the bcp Utility
            1. Usage
            2. bcp: Client-side Parallelism
          6. Truncate Partitions
            1. Looking Forward — Drop Partition
      3. Very Large Storage System
        1. Disk Init
        2. Large Identifiers
          1. Long Identifiers
          2. Short Identifiers
      4. Unicode Text Support
      5. New Datatypes
      6. New Functions
      7. Deprecated Functions
      8. New Configuration Parameters
      9. Eliminated Configuration Parameters
      10. New Global Variables
      11. Summary
    3. Chapter 3 Semantic Partitions and Very Large Database (VLDB) Support
      1. Introduction
        1. Why Partition Data?
        2. Benefits of Partitioning
        3. Partition Terminology
      2. Semantic Partitions
        1. Configuring ASE for Semantic Partitioning
        2. Partition Support in ASE 15
        3. Partition Types
          1. Range Partitioning
            1. When to Use Range Partitioning
          2. Hash Partitioning
            1. When to Use Hash-based Partitioning
          3. List Partitioning
            1. When to Use List Partitioning
          4. Round-robin Partitioning
            1. When to Use Round-robin Partitioning
      3. Partitioning Strategies
      4. Inserting, Updating, and Deleting Data in Partitions
        1. Inserting Data into Semantic Partitions
        2. Inserting Data into Range Partitions
        3. Inserting Data into Hash Partitions
        4. Inserting Data into List Partitions
        5. Deleting Data from All Semantic Partitions
        6. Updating Data in All Semantic Partitions
      5. Built-in Functions
      6. Data Partition Implementation and Upgrade Strategies
      7. Index Partitioning
        1. Local Index
          1. Clustered Prefixed Index on Range Partitioned Table
          2. Clustered Non-Prefixed Index on Range Partitioned Table
          3. Clustered Prefixed Index on List Partitioned Table
          4. Clustered Non-Prefixed Index on List Partitioned Table
          5. Clustered Prefixed Index on Round-robin Partitioned Table
          6. Clustered Non-Prefixed Index on Round-robin Partitioned Table
          7. Clustered Non-Prefixed Index on Hash Partitioned Table
          8. Clustered Prefixed Index on Hash Partitioned Table
        2. Global Index
          1. Global Nonclustered Prefixed Index on Range Partitioned Table
          2. Global Nonclustered Prefixed Index on List Partitioned Table
          3. Global Nonclustered Prefixed Index on Round-robin Partitioned Table
          4. Global Nonclustered Prefixed Index on Hash Partitioned Table
      8. Query Processor and Partition Support
      9. ASE 15 Optimizer
      10. Partition Maintenance
        1. Altering Data Partitions
          1. Unpartition a Table
          2. Change the Number of Partitions
          3. Add a Partition to a Table
          4. Drop Partitions
          5. Modifications to the Partition Key
      11. Partition Information
      12. Influence of Partitioning on DBA Activities
      13. Influence of Partitioning on Long-time Archival
      14. Summary
    4. Chapter 4 Scrollable Cursors
      1. Introduction
      2. Scrollable Cursor Background
      3. Cursor Scrollability
      4. Cursor-related Global Variables
        1. @@rowcount
        2. @@fetch_status
        3. @@cursor_rows
      5. Changes to the sp_cursorinfo System Procedure
      6. Be Aware of Scrollable Cursor Rules!
      7. Cursor Sensitivity
        1. Demonstration 1: Update to a Row Already Fetched
        2. Demonstration 2: Update to a Row Not Yet Fetched
        3. Cursor Sensitivity — An Exception
        4. Locking Considerations with Cursors
        5. Impact on tempdb Usage
        6. Worktable Materialization with Scrollable Sensitive Cursors
        7. Conclusion of Sensitive vs. Insensitive Cursors
      8. Sybase Engineer’s Insight
      9. Summary
        1. Future Direction
    5. Chapter 5 Overview of Changes to the Query Processing Engine
      1. Introduction
      2. Optimization Goals
        1. allrows_oltp
        2. allrows_mix
        3. allrows_dss
        4. Determining the Current Optimization Goal
      3. Optimization Criteria
        1. merge_join
        2. merge_union_all
        3. merge_union_distinct
        4. multi_table_store_ind
        5. opportunistic_distinct_view
        6. parallel_query
        7. hash_join
      4. Optimization Timeout Limit
      5. Query Processor Improvements
        1. Datatype Mismatch
        2. Partition Elimination and Directed Joins
        3. Tables with Highly Skewed Histogram Values
        4. Group By and Order By
        5. or Queries
        6. Star Queries
      6. Summary
    6. Chapter 6 Detection and Resolution of Query Performance Issues
      1. Introduction
      2. An Approach to Poor Query Performance Diagnosis
      3. Common Query Performance Factors
        1. Eliminating Causes for Sub-Optimal Plan Selection
          1. Find Missing or Invalid Statistics
          2. Consider Range Cell Density on Non-Unique Indexes
          3. Identify Index Needs
          4. Identify Poor Index Strategy
          5. Fragmentation of Data
          6. Resolve Partition Imbalance
          7. Reset Server- or Session-level Options
          8. Overengineered Forceplan
          9. Invalid Use of Index Force
          10. Inefficient Query Plan Forced by Abstract Plan
      4. Query Processor “set options” — The Basics
        1. Query Optimizer Cost Algorithm
        2. ASE 15 vs. 12.5.x Cost Algorithm
      5. Query Processor “set options” — Explored
        1. show_missing_stats
        2. show_elimination
        3. show_abstract_plan
        4. Why Use Abstract Plans for ASE 15?
      6. Application of Optimization Tools
        1. Optimization Goal Performance Analysis
        2. Optimization Criteria Performance Analysis
        3. Optimization Timeout Analysis
          1. Suggested Approach to Fix Optimization Timeout Problems
      7. Detection, Resolution, and Prevention of Partition-related Performance Issues
        1. Data Skew Due to Incorrect Partition Type or Poor Partition Key Selection
        2. Effect of Invalid Statistics on Table Semantically Partitioned
      8. Summary
    7. Chapter 7 Computed Columns
      1. Introduction
      2. Key Concepts
        1. Materialization
        2. Deterministic Property
        3. Relationship between Deterministic Property and Materialization
          1. Deterministic and Materialized Computed Columns
          2. Deterministic and Nonmaterialized Computed Columns
          3. Nondeterministic and Materialized Computed Columns
          4. Nondeterministic and Nonmaterialized Computed Columns
      3. Benefits of Using Computed Columns
        1. Provide Shorthand and Indexing for an Expression
        2. Composing and Decomposing Datatypes
        3. User-defined Sort Order
      4. Rules and Properties of Computed Columns
      5. Sybase Enhancements to Support Computed Columns
        1. Create Table Syntax Change
        2. Alter Table Syntax Change
        3. System Table Changes
        4. Stored Procedure Changes
      6. Summary
    8. Chapter 8 Functional Indexes
      1. Computed Column Index
        1. Purpose
        2. Rules and Properties of a Computed Column Index
        3. Feature Benefits
        4. Feature Limitations
        5. Impacts to tempdb
        6. Impact to Existing Application Code
        7. Determining When to Use a Computed Column Index
        8. Optimizer Statistics
      2. Function-based Index
        1. Purpose
        2. Rules and Properties of a Function-based Index
        3. Feature Benefits
        4. Feature Limitations
        5. Impacts to tempdb
        6. Impact to Existing Application Code
        7. Determining the Use of a Function-based Index
        8. Optimizer Statistics
      3. Behind the Scenes
      4. Getting Index Information
      5. Summary
    9. Chapter 9 Capturing Query Processing Metrics
      1. Alternatives to Query Processing Metrics
      2. Introduction to Query Processing Metrics
        1. Contents of sysquerymetrics
        2. Contents of the sysquerymetrics View
      3. How to Enable QP Metrics Capture
      4. Captured Information Explored
        1. Stored Procedures
        2. Triggers and Views
        3. Execute Immediate
      5. Accessing Captured Plans
      6. How Is the QP Metrics Information Useful?
      7. Identification of Performance Regression
      8. Comparing Metrics for a Specific Query between Running Groups
      9. Comparing Metrics for All Queries between Running Groups
        1. Why Separate the QP Metrics Data by gid?
      10. Syntax Style Matters; Spacing Does Not
      11. Clearing and Saving the Metrics
      12. Relationship between Stats I/O and QP Metrics I/O Counts
      13. Information for Resource Governor
      14. Space Utilization Considerations
      15. Limitations
      16. Summary
    10. Chapter 10 Graphical Plan Viewer
      1. Graphical Plan Viewer from Interactive SQL
      2. Graphical Query Tree Using Set Options
      3. Summary
    11. Chapter 11 Sybase Software Asset Management (SySAM) 2.0
      1. Introduction
      2. Prior to ASE 15
      3. With ASE 15
      4. Components of Asset Management
        1. SySAM Server
        2. SySAM Utility Program — lmutil
        3. SySAM Reporting Tool
        4. System Environment Variables
        5. License File
        6. Options File
        7. Properties File
        8. The SySAM Environment
          1. Standalone License Server
          2. Networked License Server
          3. Redundant License Server
      5. Acquiring Product Licenses
      6. Product Licenses
        1. Try and Buy
        2. License Activation
      7. SySAM Administration
        1. sp_lmconfig
      8. ASE 15 SySAM Upgrade Process
      9. SySAM Reporting
        1. Summary Reports
        2. Server Usage Reports
        3. Raw Data Reports
      10. Summary
    12. Chapter 12 Installation of ASE Servers
      1. Prior to Installation for All Methods
      2. Installation with Resource Files
        1. Notes for Resource File Installation of ASE
        2. Installation of ASE Components with a Resource File
      3. GUI Installation Method with srvbuild Executable
      4. Installation with the Dataserver Executable
      5. Summary
  9. Part II Pre-15 Improvements
    1. Chapter 13 Multiple Temporary Databases
      1. Introduction
      2. Purposes for Multiple Temporary Databases
        1. Prior to ASE 15
        2. With ASE 15
          1. System Catalog Changes
          2. directio Support
          3. update statistics
          4. Insensitive Scrollable Cursors
          5. Semi-sensitive Scrollable Cursors
          6. Sensitive Scrollable Cursors
      3. How to Decide When to Add a Temporary Database
        1. Strategies
        2. What Are Your Needs?
      4. Implementation Steps
      5. Determining Available Temporary Databases
      6. Sample Setup for Temporary Database for “sa” Use Only
      7. Other Issues
        1. Dropping Temporary Databases
        2. Altering a Temporary Database
        3. @@tempdb
      8. Summary
    2. Chapter 14 The MDA Tables
      1. What Are the MDA Tables?
      2. Past Solutions
      3. MDA Table Installation
      4. MDA Table Server Configuration Options
        1. Group: Monitoring
        2. The Parent Switch
      5. The MDA Tables
      6. Changes from ASE 12.5.3
      7. What Is Meant by “stateful” Tables?
      8. Stateful MDA Table Data Management
      9. SQL Use
      10. Useful MDA Table Queries
      11. MDA Alternatives
      12. Summary
    3. Chapter 15 Java, XML, and Web Services in ASE
      1. Introduction
      2. Java in the Database
        1. Installing Java Classes
          1. Creating Java Classes and JARs
          2. Using the installjava Utility
          3. Configuring Memory for Java in the Database
        2. Java Classes as Datatypes
          1. An Example of Table Definition Using a Java Class
          2. Performance Considerations
          3. An Example of Using a Java Class within a Select
        3. Executing Java Methods
          1. Class Static Variables
        4. Recommendations and Considerations
      3. XML in the Database
        1. XML Stored in the Database
          1. Option 1: Store the XML Document into a Text Datatype
          2. Option 2: Store the XML Document into an Image Datatype Using xmlparse
          3. Option 3: Store the XML Document into an Image Datatype Using Compression
          4. Option 4: Store the XML Document Outside the Database
          5. HTML Stored in the Database
          6. Recommendations and Considerations
          7. Performance and Sizing
            1. Description of the Tests
            2. Conclusions
        2. SQL Result Sets Converted to Return an XML Document
      4. Web Services
        1. Web Services Producer
        2. Web Services Consumer
          1. Recommendations and Considerations
          2. Appendix A Sybase ASE 15 Certification Sample Questions and Answers
            1. Answers for Sybase ASE 15 Certification Sample Questions
          3. Appendix B Use Cases
            1. Business Case 1
              1. Option 1: One VLDB with large tables self contained
                1. Architecture Overview
                2. Advantages
                3. Limitations
              2. Option 2: Primary database and an archive database for the largest tables
                1. Architecture Overview
                2. Advantages
                3. Limitations
              3. Option 3: Several small databases, each holding a portion (none overlapping) of the data from the very large tables
                1. Architecture Overview
                2. Advantages
                3. Limitations
              4. Option 4: One VLDB with a large table, partitioned semantically
                1. Architecture Overview
                2. Advantages
                3. Limitations
            2. Business Case 2
              1. Option 1
                1. Advantages
                2. Limitations
              2. Option 2
                1. Advantages
                2. Limitations
              3. Option 3
                1. Advantages
                2. Limitations
              4. Option 4
                1. Advantages
                2. Limitations
            3. Business Case 3
              1. Background on Current Architecture
              2. Proposed Solutions
              3. Option 1
                1. Advantages
                2. Limitations
              4. Option 2
                1. Cursor CSR1
                2. Advantages
                3. Limitations
              5. Option 3
                1. Advantages
                2. Limitations
          4. Index
3.140.186.241