Software Gems' Sybase GEM Documents

Software Gems' Sybase Gem Documents

Content & Veracity
These documents are far and above the Sybase manuals, as follows:

they contain information that is not in the manuals (ie. they overcome the lack of information)
they bring all relevant information about a subject together, in one place
in order to maximise comprehension, they present all aspects of a subject in logical order, in a hierarchy
where the manuals contain contradictory information, the correct version only, is provided (ie. confusion and half-truths are eliminated)
where misleading or false technical terms are used, correct technical terms are used instead
where non-technical terms are used, technical terms are used instead

Sybase Manuals
However, they are not a substitute for, they do not replace, the product anuals, which remain the product reference, eg. for syntax and references. The information therein (updated with each release) is not repeated here.

Technically Correct & Relevant
There is an awful lot of shallow, inaccurate, misleading and false information on the Internet. Unfortunately some of that false or misleading information is published by Sybase, both in the manuals, and on the web. These documents provide full, complete and technically correct information (albeit very condensed) about each subject, such that the reader is no longer vulnerable to false or confusing information.

Not the Full Monty
Customers paying for Performance Enhancement with Consulting obtain a somewhat richer version of these documents, with site-specific configuration directives. The documents published here are provided free of charge, as a service to the Sybase community. As such:

they are one major release behind the current release of Sybase ASE (which is not an issue from an education standpoint, because the product is mature: if you do not understand, or cannot configure the previous major release, you will not be able to understand or configure the current one).
while retaining configuration considerations, they have been stripped of configuration planning information that paying customers receive (including a spreadsheet to perform the arithmetic across related configuration parameters).

Introduction

Memory Tag

Sybase

Sybase ASE

Sybase

Note that they are quite condensed and we are told, have that Gem quality about them.

If you are new to the material, take your time and consume it in small quantities.

This site is for people who appreciate that Truth is objective. If you believe in subjective "truth", or you do not have tertiary qualifications for computing, or you have a short attention span, this site is not for you.

While Memory Tag pages look great, cover the named subject fully, and work well for experienced people, they are not as effective for people who have not had the benefit of the course: there is nothing in the memory to tag. Therefore I get a lot of questions, and I keep upgrading the content (please check for updates). The material has now left Memory Tag territory, it is approaching textbook level.

Two formats are provided.

PDF
These documents were originally formatted and produced as stand-alone PDFs for download. This will not change because the portability, quality and nagivation are unmatched; the complex illustrations show up best in PDF; and they are heavily cross-referenced via links.
Blue glass buttons provide navigation across the major headings. Use the full power of your PDF viewer capabilities, including TableOfContents; Search; etc.

If you can open them: Unfortunately the combination of certain browsers::PDF viewers do not present PDFs correctly. So much for the ability of commercial programs to read Standard-compliant files.

HTML Image Map
Based on your requests, most of the documents have been re-published as HTML, from the same source, for viewing in sub-standard browsers.
This is rendered as one A4 PNG image per webpage, with image quality understandably poorer than PDF. Use your browser capabilities: either use the Next links and follow the material in the sequence presented; or select the objects and jump around to follow your interest. Blue glass buttons provide navigation across the major headings.

Document

Document Map PDF & HTML
Decades ago, a few single pages derived from our education materials were posted, as reference to my discussions on fora. Over time, on the basis of feedback and continued interaction, this has grown into several documents, now integrated and cross-linked. The sections in each document keep growing as well. What was once a number of single pages which were issue-oriented, has evolved into structured, reference-oriented, multi-page documents. A Document Map is now called for; this allows you to review the structure and subject sections and to go directly to them.

Database Server Architecture

Pre-requisite
The technical facts re product or freeware architecture are clear to technically qualified people, they know that Oracle and freeware are not servers by any stretch of the imagination. Technicallly unqualified people however, are easily fooled by the much-repeated marketing; and the occurrence of the word "server" on every page in the manuals, thus they think their product or freeware is somehow a server, and therefore that they already understand what a server, or server architecture, is. This prevents them from learning about actual servers and server architecture, that we have had in the commercial world since 1980, it subverts genuine understanding.
Therefore, a quick illustrated comparison of genuine server architecture vs the mess that is implemented in such program suites is required, to unlearn the marketed "server", to clear the slate, in order to learn about what a genuine server constitutes; what architecture actually means.

Oracle & Freeware "SQL"
The freeware/shareware/vapourware/nowhere program suites purport to be SQL Compliant. They are not. Instead they provide extra "features" which SQL does not need, locking you even more into their special dialect of "sql". Further, this "language" changes substantially which each major release, as they slowly learn about SQL; ACID Transactions; etc, causing you to re-write your "sql" code. Over and over again.

Oracle & Freeware "Server" or "Platform"
Note that the 'orrible creature has the same non-architecture as freeware "sql" "platforms", thus;

The "architecture" of oracle places it squarely in the freeware "sql" "platform" category. With all the wonderful high maintenence issues that go with it.

The only difference is, oracle is commercial, the rest of the rabble are not. You pay peanuts, you get monkeys; you pay nothing, you get ...

Oracle & Freeware vs Sybase ASE V2.0 PDF (No HTML)
For Oracle types, and others without technical qualificiations, especially those who know only freeware "sql", this is a pre-requisite to understanding Sybase ASE Architecture. Although oracle components are explicitly named, the document applies to all such "platforms". In other words, if you would like to understand the "architecture" of any such product, why your freeware flatporn performs like a tortoise in heat, examine this document.
The document incidentally proves why the obese orable product or freeware need ten to twenty times the resources as Sybase, for the same workload, and why it is nevertheless at least one hundred times slower, but that is not the main intent.

Sybase ASE Architecture V2.0 PDF (No HTML)
This is offered as an alternative to the pathetic diagrams purporting to be "architecture" diagrams, that squeeze a thousand objects onto a single page, they are merely a bad inventory of parts, devoid of meaning.

Essential information for administering or tuning a Sybase server a complete and structured narrative of the Adaptive Server Enterprise. The subject is introduced progressively, in ten pages of 'edible' illustrations. The Process Kernel must be thoroughly understood first, before the Threaded Kernel can be contemplated, therefore they are presented chronologically, and full detail is provided for each.

Explains the architecture progressively, in four increments
Explains Kernel operation, limits, and common misunderstandings
Explains all major features of ASE, such as Parallelism, in context
Enumerates all componentry and resources, enabling sysmon reports to be interpreted effectively
Enables configuration parameters to be understood: what they are; their purpose; their relation to other parameters
And finally, the Threaded kernel is explained.
When contemplating a problem in the server, this enables accurate determination (location) of the relevant component and resources
Answers a thousand questions, such as, I have doubled the number of locks, why am I still lock-bound ?
Neither ASE, nor ASE Architecture, nor a diagram of ASE Architecture, is for the faint-hearted.

Data Storage & Fragmentation V2.5 PDF
The three documents below, provided in a single PDF, with all links and references resolved. 32 condensed pages.

Data Storage V2.5 HTML
Detailed and illustrated. Defines Units of storage and their relations; the five DataStructures and Partitions; all Index Structures and their relations; and introduces Devices and Segments in context. Understanding Data Storage and DataStructures is essential, in order to administer them effectively, and to understand performance of the DataStructures, as well as the performance of a server that uses them. Those hunting the snark or the DOL "clustered" index will find it here. Those seeking a deeper understanding or intending to write utilities may appreciate the Sybase Catalogue pertaining to DataStructures; and the exposition of all DataStructures and their relations in terms of a full Data Model.

Fragmentation V2.5 HTML
Detailed and illustrated. Version 2.5 is substantially expanded, based on your feedback (thank you!).
- Defines all the Types of Fragmentation, within three distinct Levels, as it applies to each Data Storage Unit, and the impact at each Level/Unit
- New chapter on Determination of each Type of Fragmentation, detailing not only the methods, but the considerations for each metric
- Each type of Fragmentation illustrated and discussed
- For DOL-only Fragmentation, the effect of each command is illustrated and discussed
- Explains why common de-fragmentation operations do not work
- Provides a solid foundation for prevention of Level I Fragmentation; returning great speed to the Data Structures; and for reducing the frequency of de-fragmentation operations
- Provides methods of correcting Level I & II Fragmentation
- Where the difference between APL and DPL/DRL lockschemes is relevant, these are compared side-by-side.

Index Type V2.5 HTML
An older document, it describes and compares APL vs DPL/DRL Index Types in summary form, on a single page.

Lock Manager V2.10 PDF & HTML
An illustrated overview of the operation of the Lock Manager, in three condensed pages. It shows all commonly encountered types of locks within ASE; how they are used; how they are escalated and promoted. It tabulates ISOLATION LEVELS vis-a-vis locks, and clarifies the transaction/session/query/table context. Configuration considerations and the different types of contention resolution metods (locks, latches, LW Protection, and Spinlocks) are identified. Suitable for developers who have obtained education in the OLTP Standard.

Cache Manager V2.4 PDF & HTML
A full explanation of all aspects of the behaviour of ASE caches and pools, using illustrations: how they operate; considerations re the Wash Area; the two cache strategies; overload scenarios; large I/O pools, in terms of both reading and writing; partitioning the cache; and Asynch Pre-Fetch. The information is essential for understanding ASE caches, and for configuring and tuning them correctly.

The only element not detailed is site-specific cache sizing and configuration guidelines (our

High Performance Configuration

, which includes the configuration of a full set of site-specific resources, is available for purchase or as an implementation within an assignment

Statistics Demystified V2.5 PDF & HTML

The subject of Statistics is neither complicated, nor simple. The purpose; requirement; and considerations, as well as the mechanics, simply need to be understood. However, that is made hugely complicated by the denizens of the web, particularly the self-styled "gurus". This document explains Statistics in a straight-forward manner, such that the unnecessary complexity is eliminated.

Sybase ASE Statistics explained, logically and without confusion. Presented with many illustrations, in three parts, plus examples of meaningful Statistics reports from our utilities (for those who seek to write their own reports). Twenty -six pages:

Concepts: the essentials (not found anywhere else, unfortunately !), without which administration of Statistics is an unfulfilling and unenlightening exercise, a shotgun in the dark. In order to identify which Stats are relevant, to maintain them properly and efficiently, the Concepts need to be understood. The Histogram is explained completely, such that expectations are understood, and intentions can be set. The interpretation of optdiag reports is explained.
Catalogue: useful for a deeper understanding of the issues. A requirement for writing your own Statistics reports.
UPDATE STATS: the effect of each flavour of the command is explained with an illustration, using a moderately complex example.
Statistic Reports:
- Help Statistic: displays Statistics and their relations to all relevant items (Indices; other Stats; columns) on one page, at both the Table and Partition levels.
- Help Histogram: displays the Histogram in a human-readable format, and has advantages over optdiag. Handling Skew and Repeated Values is explained.

MDA Collector V1.4 PDF
MDA Source PDF

In our Technical Audits, Performance Tuning & Verification, we use Sysmon exclusively, due to its richness and zero overhead; and provide a full-featured Sysmon Processor. For those customers who insist on MDA, we support the Monitoring aspect of MDA tables (of which the overheads are bad enough) as a second preference to Sysmon, but not the Diagnostic aspect (of which the overhead and processing requirements are horrendous; there are much cheaper and less onerous methods of diagnosis), we provide a full-featured MDA Collector.

These documents posted here because we are told they assist people in understanding MDA, and in writing their own MDA Collector. The first document is a single page overview of the MDA Collector database, showing the ERD and the command syntax for this utility. The second document identifies the MDA source for each table; the cofiguration options required; and the MDA overhead for each; it may be laid over the first document.

RAID5 vs RAID1+0
PDF A substantiated review, with many links, for those who need to overcome the false information provided by SAN salesmen and lazy StorageTeams, that one great big RAID5 volume is acceptable for databases. Page 5 provides an illustration.

SAN Configuration Guide for Sybase ASE
The several levels of configuration of the Logical Volumes in the SAN is very important, as it seriously affects the performance and isolation of your ASE server and your databases. We provide a complete Guide, that addresses the issues and consequences in detail, currently over 50 pages, available for purchase. The document above identifies the first line of issues that require address in response to the pathetically slow RAID5, which is commonly pressed as the answer to every data storage requirement; it is provided free of charge.

Software Gems' Sybase Gem Documents

Introduction

Document