Known Issues for SAP Sybase Event Stream Processor · Known Issues for Installation Guide SAP Adaptive Server Enterprise for HP-UX · Conventions. The Adaptive Server Reference Manual is a three-volume guide to. Sybase® Adaptive Server™ Enterprise and the Transact-SQL® language. This volume. Sybase, Transact-SQL, Adaptive Server Enterprise and Replication Server are Find ASE product manuals echecs16.info (online searchable and PDF ).
|Language:||English, Spanish, Hindi|
|ePub File Size:||16.67 MB|
|PDF File Size:||14.13 MB|
|Distribution:||Free* [*Register to download]|
This publication pertains to Sybase software and to any subsequent release mechanical, manual, optical, or otherwise, without the prior written permission of Sybase, Inc. in PDF format, and may also contain other documents or updated. This manual is for Sybase system administrators and database owners. How to use this . Some documentation may be provided in PDF format, which you can. This publication pertains to Sybase software and to any subsequent release until Chapter 1, “System Procedures,” in Reference Manual: Procedures). To.
Comments Essential for the writing of clear programs are comments, which are explana- Ask your questions about. The aim of this book is to teach you the fundamentals of programming with C by using Visual Studio and the. This article is an effort to provide an accurate information pool for new developers on the basics of software architecture, focusing on Object Oriented Programming OOP. Visual Basic is also widely used to write working programs. Without it all elements will return to their default values. When you use SQL, you must use the correct syntax. This document is not a comprehensive introduction or a reference man-ual.
The development stages are: 1 Comprehension preproduction , 2 Early Production, and 3 Speech Emergence. Visual Studio. Keywords can be used only for their intended purpose. Microsoft Visual Studio makes these powerful features easy to use, and the many new wizards and enhancements included in Visual Studio can greatly improve your productivity as a developer. The only assumption I make about you as a programmer is that you're comfortable with the concepts and processes of software development.
The keywords in Visual Basic. Net programs from simple command-line applications to more complex applications. Net is an object-oriented programming language. Support for implementation inheritance is perhaps the biggest change in Visual Basic. The common elements of a Visual Basic user keywords will be useful when creating your own VBA procedures. NET, on the other hand, is targeted for the. This note gives an outline of the language for those of you familiar with programming.
Events are what happens when an object does something. Unfortunately, the narrative presentation is not as easy to understand and follow. They are associated with the element of water, which becomes a frequent visual theme within this suit. First, we establish the foundation for a theory of narrative structure by describing the basic narrative categories and their relationship to a canonical narrative arc. Companies, names and data used in examples herein are fictitious unless otherwise noted.
Though the meaning of the grid to artists is hard to describe in words, it is more than just a visual armature. The tutorial is divided in 6 parts and each part is divided on its turn into different sections covering a topic each one.
This fact has started discouraging people from learning or attempting computer programming. These functions perform essential services such as input and output. Notice that when a Boolean value is concatenated to a String, Visual Basic concatenates the string "False" or "True" on the basis of the Boolean's value. This handout contains resume examples that will help you get started.
Visual Basic determines which one is intended by differences in the parameters used to call the objects.
Or, you're learning Japanese, and you want to make sure you know some of the most important words in the Japanese language. You may be eligible for Housebound benefits if you are eligible for basic pension benefits Learn about Purdue University's College of Liberal Arts, a college focused on strengthening the Undergraduate Experience, enhancing Graduate Education, and promoting Faculty Excellence. A page selection can be set in the form of comma separated page intervals Ex.
It is paid to a Veteran or surviving spouse who is confined to their home because of a permanent disability. Special C Identifiers The add and remove keywords allow you to execute code whenever a delegate is added or removed from an event.
Although there are ways to use the debugging features through the Visual Basic Editor menu options, you may find these keyboard shortcuts to be a much more efficient way to debug your code. Both VB 6 and VB. Summarizes the way that keywords, placeholders, and other elements of the language are formatted in the Visual Basic documentation. List of computer programming terms, definitions, commands, and glossary.
The list is not exhaustive, but contains mainly those keywords that are used in the procedures shown in this book. These Japanese words will get you through an untold number of When faced with such images we seem to be compelled to determine their meaning, so paying a different kind of attention to them than we would with easily recognizable views of the same thing.
Table 3. Small Basic is a programming language that is designed to make programming extremely easy, approachable and fun for beginners. VisualBasic namespace, with links to their member functions, methods, properties, constants, and enumerations. The YTD team updated this glossary of graphic design terms for design newbies and experienced artists alike.
Before plunging into learning Visual Basic, we will review the fundamentals of computer And this is where this Excel VBA tutorial for beginners comes in to help. This keyword has been part of the C programming language since.
Sloppy Programming The essence of sloppy programming is that the user should be able to enter something simple and natural, like a few keywords, and the computer should try everything within its power to interpret and make sense of this input. We use keywords True and False in the program to specify values of the Boolean data type.
H Visual Basic is not, a replacement for C. To find out the problems in using Audio Visual aids Method and Procedure The study was descriptive research in nature. This wallflower status was not always the case. These keywords are reserved for use by Visual Basic, and you use them to build your programs. ArrayName is the name of the array.
All the Visual Basic Objects can be moved, resized or customized by setting their properties. By the end of this guide, you will understand the basic building blocks of Visual Basic for Applications, which will allow you to learn this keywords will be useful when creating your own VBA procedures. H Visual Basic is not, elegant or fast. A low relief, such as a coin, is called bas relief. The worksheet tabs can have their names changed, but it seems that Visual Basic still thinks of the worksheet names as Sheet1, etc.
We are supposed to create a 1 form application that translates 3 Latin words to English. Dartmouth BASIC revolutionized computer programming for the non-experts, who greatly outnumber the experts!
Then you realize that you don't know any Japanese words. Keywords are words with special meaning in a programming language. Learn how to use Small Basic and to graduate to Visual Basic… in 6 steps! Unfortunately, there's a bug in Small Basic where the Dictionary site updated their. To get help on a keyword The coding structure VB. Following this, we explore complex structures in narrative that extend beyond the basic canonical schema.
Some students are visual learners, while others are auditory or kinaesthetic learners. Using these tools, you can write all kinds of VB. Keywords are the words whose meaning has already been explained to the C compiler and their meanings cannot be changed. Its usage is similar to the get and set keywords with properties: their concepts are pretty challenging to grasp by a beginner.
The "Unknown:"s below indicate that an entry is incomplete. While students use all of their senses to take in information, they seem to have preferences in how they learn best. To analyze the effectiveness of Audio visual aids in teaching learning process at university level. Relief: A projection from a surface.
NET, Files and Directories a button on the Dynamic Help window that allows you to search for help topics using keywords. NET Framework 1. The actions that an object may take are called methods. When writing code, you often define types or declare variables.
If you continue using our site, you consent to the storing of cookies on your computer. Cement your administration skills on topics like data partitioning, preventative maintenance, memory configuration, and more. Every database is different. Your account Help.
Browse the Book Browse the Book. Print edition. Starting with an introduction to the database and an installation overview, this book is packed with information you'll use on any platform. Get the details you need to define database storage areas, create database elements, work with security and user roles, manage backup and restoration, achieve high availability, and more.
About the Book About the E-book pages, hardcover, 1. In particular, semantic partitions can help manage large tables and indexes by dividing them into smaller, more manageable pieces. Additionally, semantic partitions are the building blocks for parallel processing, which can significantly improve performance. In this section, the partition-level utilities and configuration parameters are discussed in the following order: System Maintenance Improvements Partition Configuration Parameters In order to support the new partition-level utilities, two new configuration parameters have been added to the server configuration.
These parameters are: The default is Open partitions on ASE: The 'open partitions' configuration parameter is currently set to By default, all tables are partition type round-robin.
This configuration option is only relevant to ASE when the number of online engines is greater than one. System Maintenance Improvements 35 Utility Benefits from Semantic Partitions Adaptive Server 15 supports horizontal partitioning, wherein a collection of table rows are distributed among multiple disk devices.
In addition, ASE 15 supports semantic partitioning, wherein valuebased partitioning schemes, such as range and hash, partition data according to value. Significant benefits of semantic partitions include: The improvements are a result of partition-specific maintenance capabilities with the enhancements of several utilities to perform system administration and maintenance tasks at the partition level.
Partition-specific Database Consistency Checks dbccs For ASE 15, a portion of the database consistency checks are enhanced with the ability to check consistency at the partition level for semantically partitioned tables.
The partition-enabled dbcc commands are dbcc checktable, dbcc checkindex, dbcc indexalloc, and dbcc tablealloc. System Maintenance Improvements Example: Checking partition 'batch1' partition ID of table 'authors'. The logical page size of this table is bytes. The total number of data pages in partition 'batch1' partition ID is Partition 'batch1' partition ID has data rows.
DBCC execution completed. Table has data rows. Index has leaf rids. The total number of data pages in this table is System Maintenance Improvements 37 dbcc indexalloc Syntax The dbcc indexalloc command can perform analysis on a specific index partition.
The reorg rebuild command can also be run against single index partitions. Benefits of Reorg Partitions Traditionally, reorg operations on the largest of tables is a very timeconsuming operation for ASE servers.
Additionally, reorg operations have only been permitted to operate at the table or index level. Reorg at the partition level is a new feature for ASE Using reorg at the partition level provides the database administrator a mechanism to divide the reorg tasks for very large tables to multiple maintenance windows.
For ASE 15 GA, reorg rebuild works at the table level, even when passed a partition name as an input parameter. The ability to perform reorg rebuild at the partition level may be available in a future IR release of ASE Remember for ASE 15 GA, reorg rebuild on a specific partition performs the reorg operation on the table as a whole. Changes to the bcp Utility The bcp utility is enhanced for ASE 15 to support bcp operations at the semantic partition level.
This is not the only improvement that has occurred for the bcp utility. The capability of using the bcp utility on computed columns has also been included. These enhancements address pre ASE limitations of the bcp utility. Parallel bcp supports partitions in both slow and fast modes. Bulk copy data out from all partitions or a subset of partitions Bulk copy data out to a single file or partition-specific files Bulk copy data in from a single file, multiple files, or partition-specific files Bulk copy data in parallel to specific partitions The new syntax for bcp is in bold: This number ranges from 1 to the total number of partitions in the table.
This parameter can be used for both bcp out and bcp in. System Maintenance Improvements 41 t filename — Specifies a comma-delimited set of one or more datafiles. Can be specified for both bcp in and bcp out. If this is not specified, bcp automatically determines the number of connections.
Copies the Rental table to the rentals. Rental out rentals. Rental in rentals. Rental partition p2, p3, p4 out rentals. Rental in p2. Rental out Because a name is not specified in the command, the file is named the same as the partition with a. For example, if the table consists of the partitions p1, p2, p3, and p4, bcp creates four output datafiles: If the table is not partitioned, the datafile is named after the single default partition that comprises the table.
The Tenants table below is non-partitioned, and a bcp out with the following syntax will result in one bcp file, named in conjunction with the single partition name for this table: Tenants out 42 Chapter 2: System Maintenance Improvements The following command specifies a subset of the partitions for the Rental table.
Since the command does not specify an output file but does specify a subset of the partitions for the Rental table, bcp creates two datafiles: Rental partition p3, p4 out Usage t For bcp out, if you specify both the partition pname clause and filename clause, either: If you specify partitions, you must include a one-to-one mapping between the partition names and the datafiles.
When you bcp data into a table, the input file can contain data for one or more partitions. Clients can send data across parallel connections, and each connection is a dedicated channel for a set of one or more partitions.
If the bcp client has partition-level information to map files to partitions, bcp can initiate dedicated connections for each partition into which data is being copied.
Client-side Parallelism The bcp client determines the number of connections to the server. For maximum parallelism, this includes a separate connection for each partition to the file mapping provided as parameters to the bcp command. The maxconn value indicates the maximum number of connections the bcp client is allowed to set up. Without this parameter, the Chapter 2: System Maintenance Improvements 43 bcp client automatically chooses the number of connections.
One connection per partition is ideal, but if that is not possible, bcp will multiplex batches for more than one partition across a connection. Truncate partitions can be performed on the default round-robin style partition in addition to tables semantically partitioned.
As with truncations at the table level, pages are deallocated with truncate, but only at the partition level. Similar to truncate table, using the partition argument does not log the deletes one row at a time, and is much faster than a delete with a where clause to delete data from a single partition. Truncate table at the partition level can only truncate one partition at a time.
The syntax is: Prior to truncate: System Maintenance Improvements p2 p3 p4 1 Note that after a truncate partition of the hash partitioned table, the database administrator will need to consider partition rebalancing. Looking Forward — Drop Partition Note: Since drop partition is not available in the GA release of ASE 15, use truncate partition where drop partition would otherwise be used.
In the future, the drop partition command may be included in ASE. If so, some potentially interesting scenarios could occur. For example, if a database administrator drops a partition and then later needs to restore the data, re-adding the partition may get interesting, particularly for range partitions.
Think of what happens if you have a semantic partitioned table by range Very Large Storage System ASE 15 is ready for very large data-intensive environments. For ASE 15, it is possible to create up to 2,,, disk devices.
Each of these devices can be up to 4 terabytes in size. Pre-ASE However, the disk init command will still accept the vdevno parameter provided the following conditions are met: So, if the active vdevno numbers utilized are as follows: Large Identifiers There are new limits for the length of object names and identifiers: The new large identifier applies to most user-defined identifiers including table name, column name, index name, and so on.
Due to the expanded limits, some system catalogs and built-in functions have been expanded. System Maintenance Improvements For the temporary table, the large identifier can have a maximum of bytes since the suffix for the temporary table is 17 bytes. Below are lists of identifiers, system catalogs, and built-in functions that are affected by the new limits.
The pre-ASE 15 restriction was 30 bytes. The three datatypes associated with Unicode support are: Additionlly, Unicode data allows for conversion between datatypes, which can be useful for foreign language-based installations. Datatype Range unsigned smallint 0 to 65, unsigned int 0 to 4,,, unsigned bigint 0 to 18,,,,,, For unsigned datatypes, it is valid to also use unsigned tinyint, as this is syntactically supported.
However, unsigned tinyint will function as a standard tinyint. The range for each of the unsigned datatypes doubles on the positive side of zero since it is not possible to declare a negative number with unsigned integer variables.
The storage size for each of the unsigned datatypes remains the same as the signed version of the corresponding datatype. System Maintenance Improvements t 49 hextobigint — Converts large hex values to large integer values Syntax: A return of zero 0 indicates a dump tran is allowed.
A non-zero return indicates a dump tran is not allowed. When a function is deprecated, it is not fully removed from ASE. A deprecated function no longer functions in the way it was designed; instead, the functions will return only a zero.
This deprecation, as opposed to full removal from ASE, keeps third-party applications many database administrators use from crashing due to references to invalid functions.
It is always a good practice to thoroughly test all functions and keywords before migrating a production system and the underlying support software to a new database server release.
This is especially true for systems that employ custom system procedures that are based on Sybase system procedure code excerpts, or with many of the third-party tools used to administer ASE. Parameter Name Description default xml sortorder Defines the sort order used by the XML engine enable metrics capture Instructs ASE to capture QP Metrics at the server level enable semantic partitioning Enables the separately licensed feature of semantic partitions in ASE max repartition degree Specifies the maximum amount of dynamic repartitioning that ASE can perform max resource granularity Specifies a maximum amount of system resources that a single query can use in ASE.
At this time, the effect of this parameter is only felt by the procedure cache. Additionally, the values specified serve as an optimization guide, and not a hard ceiling. The global variable setrowcount is introduced to provide more useful information to the user.
From Chapter 2, one can gain at least a basic understanding of the progress made in system administration with the ASE 15 release. In addition, the examples illustrate the power of many of the new maintenance techniques made possible with the introduction of semantic partitions, which are covered in detail in Chapter 3.
Chapter 3: The last page contention included last page lock contention. This was referred to as table slicing or equi-partitioning, whereby the table was divided into equal-sized parts or slices. Sybase later introduced row-level locking in ASE Until ASE 15, index partitioning was not even addressed. As data volumes grow because of retention requirements, traditional online transaction processing OLTP databases are fast 54 Chapter 3: Large data tables usually need large amounts of data processing resources to resolve scan type queries.
The time required for maintenance activities on large tables increases with the volume of data in tables. Shrinking maintenance windows affect DBA activities and their ability to effectively manage the databases and the database environment. These concepts are detailed later in this chapter. The fewer the levels of index traversal, the faster the query response. Although global indexes may be defined on a table, parallel index scans will now be possible if a local index is defined on the corresponding data partition.
In addition to core partitioning concepts, the strategies employed using partitions can help select, delete, update, and insert commands on the targeted data by selectively processing the data in smaller chunks.
ASE 15 partitioning is a direct answer to these issues. Partitioning allows the DBA to perform maintenance tasks at the partition level instead of always at the full table level. ASE 15 partitioning also has the added benefit of reducing the total cost of ownership.
Partitioning is a data placement strategy whereby large sets of data are broken down into manageable chunks or partitions. Data can be processed in multiple partitions simultaneously, achieving higher levels of performance and scalability. Similarly, DBA activities can be concurrently processed across one or more partitions, thereby increasing data availability and decreasing system downtime.
The goals of partitioning are often seen as twofold. First, it is meant to provide a method of spreading data that increases performance as well as addresses scalability. This was originally accomplished using striping with previous versions of ASE. The second goal is to manage data in such a manner as to allow for data exclusion when querying large portions of data that have similar data with differing demographics.
For example, if you sell products to five different regions you might want to be able to query each region as if the data were located on separate disk devices. By partitioning data with similar meaning or semantics such as region, zip code, date of access , the access paths for the required search 56 Chapter 3: Benefits of Partitioning When Sybase first introduced partitioning, there were two immediate benefits.
If a table did not have a clustered index, all inserted rows were added to the end of the page chain. This hot spot was a major performance bottleneck for applications where large amounts of data were being added to a table. The partitioning addressed the hot spot since ASE would randomly choose a partition for each transaction.
If any rows were inserted by the transaction, ASE would place them on the assigned partition. This type of partitioning is called non-semantic partitioning.
Second, the partitioning provided the optimizer with the ability to determine how many parallel worker processes could be considered for use when performing a table scan on a partitioned table. This type of partitioning is called horizontal partitioning. With ASE 15, partitioning has been extended to include definable partitioning strategies.
This vertical partitioning is based on several industry-accepted partitioning methods. This is a significant benefit for users who have very large tables where table scans are a major performance issue. When partitioning is combined with parallel worker processes and segment placement, high degrees of performance improvement are possible. Prior to Sybase ASE 15, data skew has been an issue.
The provided partition strategies will also address the issue of data skew. The problem with data skew previously was that as the equi-partitions became unbalanced, the effectiveness of parallel queries became much less — and eventually not advisable.
In ASE 15 with semantic partitioning, the optimizer is able to use the semantics of the data values used in the partitioning keys to determine more appropriate parallel query algorithms, effectively eliminating data skew as a concern.
This is important as the very semantics of the data is likely to lead to skew. For example, if partitioning customers by state, the partitions are likely going to be very skewed as the population of more populous states such as California will be larger than small or sparsely populated states such as Idaho.
Although partitions are not required to be specified at table creation, the table will be created with one partition if none are specified. It only becomes required if you decide to use the new semantic partitioning. As data ages, fewer queries are directed at the aged data.
As the data ages to the point where it is no longer useful, it has to be purged. In most cases, purging of aged data can be a resource and performance problem. By partitioning data appropriately, new functionality within ASE 15 can ease the maintenance associated with aged data.
Truncating partitions is one such option for removing aged data easily. Vertical partitioning of data and indexes is a major step forward for Sybase. Vertical partitioning addresses many performance issues associated with very large tables. In data warehousing terminology, fact tables will benefit from partitioning.
The dimensions on which fact tables are built lend themselves to data partitioning. By combining partition segmentation with data and index segmentation, DSS applications should be able to achieve acceptable performance levels. Partition Terminology Sybase has introduced several new terms into our partitioning vocabulary.
Many of the terms are known in the industry. However, in order to understand how partitioning is enhanced, an understanding of the underlying terminology is necessary. Semantic partition — A method of dividing a domain according to the principles of what the data means.
Semantic Partitions and Very Large Database VLDB Support Intra-operator parallelism — A technique used by database management systems to partition the data workload across multiple processing units to allow for parallelization of the query resolution.
Data partition — The basic building block for an ASE 15 table. All tables, whether defined with one or multiple partitions, are composed of at least one data partition.
With ASE 15, if partitioning is not explicitly defined, the first allocation of table is defaulted to be partition 1. Index partition — A portion of an index that contains a subset of the index leaf pages. The root page for the index is located in the first extent allocated for the index. Remember that you can define a particular segment where this will be allocated. This may be different for indexes on APL or DOL tables since there are some low-level differences in index structure and page allocation for these locking schemes.
In any case, the value of the root page is stored in the rootpage column of the syspartitions table. In pre-ASE 15, the page number for the root page was stored in the root column in the sysindexes tables. The index partition will have a unique partition ID in the syspartitions table. A partitioned index cannot exist for a table that only has one partition. In the example below, note that the partition ID does not change given that the segment number is 1 for the table and its indexes.
Each table can have up to 31 columns that define the partition key. Sybase currently recommends that the number of columns be limited to four or less.
Partition keys with more than four columns increase the complexity associated with defining and managing the supporting partitions. When evaluating partition keys to determine which partition to use, ASE uses a vector method to Chapter 3: Semantic Partitions and Very Large Database VLDB Support 59 find the corresponding partition columns with datatype bit, text, or image; Java classes and computed columns are not allowed to participate in defining a partition key. In order for a partition key to be effective, the columns should be found as part of a where clause in queries that have a high frequency of use or a high priority of use.
Partition bound value — The beginning or ending value of a range in a range-partitioned table. The beginning bound value of each subsequent partition has to be greater than the ending bound value of the preceding partition. The following example shows two possible range combinations: However, as already explained, range partitions are defined with only an ending bound value. Hence, it is possible to add a new range partition at the top of the range.
Beginning Bound Ending Bound Partition 1 0 Partition 2 or Beginning Bound Ending Bound Partition 1 a f Partition 2 g m Partition 3 n s Partition 4 t z Lower bound — In a range-partitioned table, this is the lowest value of the partition key that can be assigned to the applicable partition.
In the example of the partition bound value, it is shown as the beginning bound. The lower bound does not need to be defined on the first partition. If it is not specified, the lower bound is the lowest possible value for the partition key datatype s. Upper bound — In a range-partitioned table, this is the highest value of the partition key that can be assigned to the applicable partition.
In the example of the partition bound value, it is shown as the ending bound. The final partition can contain the keyword MAX, which indicates that all rows with a partition key value higher than the upper bound of the next-to-last partition are placed in this partition.
Equi-partition — In reference to multi-processor machines, equipartition is an algorithm that partitions a processor evenly for job 60 Chapter 3: In reference to data or index partitioning, it is a set of rows that exhibit equal distribution across the number of defined partitions.
This characteristic is exhibited in pre-ASE 15 partitioning. Global index — This is an index that references data across all partitions. The index can be clustered or nonclustered. This type of index is not new to ASE The physical location of the index can be on any partition — independent of it being clustered or nonclustered. Global clustered partitioned and non-partitioned indexes can be created on round-robin partitioned tables.
Local index — This index references data on a specific partition. This type of index is new to ASE The index is physically located on the partition for which it is defined. Clustered indexes are always created as local indexes on range, hash, and list partitioned tables. Clustered indexes can be created as local on round-robin partitioned tables.
Prefixed partitioned index — A prefixed partitioned index is one where the first column of the index definition is the same column as the first column of the partition key.
The ID is stored in the syspartitions table as the partitionid column. In addition to reducing the page lock contention, the new partitioning schemes extend the data access paths by defining partitions based on application needs. Also, ASE 15 partitions, for the most part, can be defined so that they are self contained with their local indexes.
Therefore, a search operation can be performed on the indexed portion and thus a former need to split large tables into many small tables is no longer necessary. It is evident that splitting a database with large tables into many databases with small tables exacerbates database management problems. If you split a GB database into 50 databases each of 10 GB, you would be wasting GB additional disk space to perform clustered index maintenance activities because of the aforementioned space requirement.
On the other hand, if 50 partitions of 10 GB each were to be used, you need 62 Chapter 3: ASE 15 semantic partitioning allows you to reap all of the benefits of multiple databases while reducing the requirement for large amounts of reserved free space.
Another issue is that query processing problems arise as table sizes increase. When a large table is split into many small tables and placed across several databases in the same or different dataservers, join query performance becomes a premium.
With ASE 15 partitioning, if the same large table is split into many meaningful partitions, joins may only be local to the table partition. If parallel access to partitions can be achieved, the response times may even exceed client expectations. Semantic partitioning is the answer to the issue of ever exploding data volume. Semantic partitions in ASE 15 will have some meaning attached to each slice.
The industry standard partitioning in ASE 15 is not just intended to manage space, but caters to the customer needs for faster access to the data in an ever increasing data volume environment.
In a very large database with round-the-clock data processing scheduled against large tables, database management activities like dbccs, update statistics, table reorgs, bcps, or table truncation can be time consuming and often result in either application downtime or performance degradation. Sybase has now extended the functionality of partitioning to include maintenance operations that in earlier releases were only possible at the table level. This increases the application availability, makes DBA tasks more efficient, and decreases total cost of ownership TCO.
Semantic partitioning addresses these issues as each of these operations can now be executed at the partition level or at the table level. See Business Case 1 in Appendix B for an example of when to use partitioning. Configuring ASE for Semantic Partitioning As part of the upgrade process or when you want to use semantic partitions, two actions need to be taken. First, you have to acquire a license for Semantic Partitioning. This can be accomplished by contacting Sybase directly or through an account representative.
Second, the following two new configuration options need to be activated: Semantic Partitions and Very Large Database VLDB Support t t 63 number of open partitions — This option should be set high enough to allow opening multiple tables with multiple partitions. Typically this number should be greater than the total number of open objects and number of open indexes. The default setting is adequate for most environments. Partition Support in ASE 15 All tables will now be defined with one partition at the time they are created if no partitioning method is specified.
This behavior is new to ASE The following list highlights the types of partitioning now available with ASE 15 and the operational changes that now take advantage of or support partitioning. The level of concurrency that can be expected in a partitioned object environment is shown in the following diagram. In a partitioned environment, both can coexist simultaneously. Building block for inter-table parallelism — The query processor utilizes partition-level information when determining how to process joins.
Partition Types The pre partitioning is now referred to as round-robin partitioning. This continues to be the only non-semantic and unlicensed partitioning scheme. ASE 15 extends the partitioning with three new licensed partitioning methods. Semantic Partitions and Very Large Database VLDB Support 65 Range Partitioning This is a partitioning strategy that places data and index pages on a partition based on the value of the partition key as it relates to the range of values that are assigned to a particular partition.
The data values of a range partition cannot overlap. By allowing for data exclusion or partition elimination, performance improvements can be gained because unnecessary partitions are not utilized. Range partitions are defined by specifying an upper bound partition key value for each partition defined to the table. The lower bound of the partition range is one unit more than the upper bound of the previous range partition.
In the cases where a table that contains range data is first being created, only creating one partition sets the table up to be altered to add additional partitions as they become necessary. Consider, for example, sales data for a region. If the table is futuristic i.
As the company grows and new sales regions are defined, the database administrator can add another partition by simply altering the table. Example 1: For example, assume you have a greeting card sales table partitioned by month.
The same would be true if you wanted the monthly sales of Easter cards. The majority of your data would be in the April partition, depending, of course, on when Easter falls.
Partitioning in general addresses very large databases and ever increasing data volume. Range partitioning in particular is the best candidate to handle large tables since the goal of range partitioning is to provide for partition elimination based on a range of data.
Range partitioning should be considered with: Please note a MAX partition clause may not be necessary in this example as there are just 52 weeks in a year even if it is a leap year. The value of MAX should be considered where the upper bound value is unknown for the last partition. ASE 15 allows computed columns, columns for which data is generated during run time. In ASE 15 computed columns cannot be used as partition keys.
In this example, week of the year could either be a materialized or nonmaterialized computed column, depending on your application; however, this cannot be used as a semantic partition key. The only way to overcome this deficiency is to add one or more new partitions and bcp the data out from this partition, truncate the partition that needs to be split, and reload the data into the new partitions.
You do not need to split the input files; the data will be loaded according to the partition key. Range partitioning allows for archival and truncation of data that has aged out. Range partitioning also has the benefit of permitting a mixed workload environment to coexist. Application examples include: After the 31st partition has been utilized, the first partition can be used again.
No purge is performed assuming DSS proportions. In all of the above cases, the table is expected to have one or more time-sensitive columns e.
In each of these cases, the leading column of the partition key is this date column. So, potentially you may end up 70 Chapter 3: In a multi-gigabyte database table with millions of transactions per measure of time, a logged operation — like the delete command — would generate multiple problems. For example, to accommodate a delete operation you need to: Data partitioning is a welcome rescue feature for customers who are spending entire nights and weekends deleting old data.
You now can simply truncate the partitions that are no longer necessary.
An operation that would otherwise take several hours to complete may now only need a few minutes. Range partitioning also allows for adding new ranges at the top end of the range without reorganizing the rest of the table.
As additional partitions are required, the table can be altered to add partitions to the end of the partition chain. Hash Partitioning This is a partitioning strategy that places data pages on a particular partition based on a hashing algorithm.
The hashing algorithm is based on the datatype and length and is managed internal to Sybase; therefore, the hashing algorithm cannot be manipulated by the user.
The goal of this partitioning method is to attempt to equally balance the distribution of values across the partition, eliminating data skew.
As this is based on the datatype, the assumption is based on the data being spread across the entire domain of the datatype and in equal proportions. As a result, even hash partitions are likely to have skew. The data has the characteristic of being deterministic, similar to the range partition; however, a hashing function is used to determine the partition where the data or index row will be placed. Although hashing is deterministic, partition elimination cannot occur for table scans on portions of the table — for example, a range scan.
The reason is that it is likely that the Chapter 3: Semantic Partitions and Very Large Database VLDB Support 71 different values within the range will have different hash values and consequently be spread across multiple partitions.
The following SQL code would create a table with four hash partitions. The diagram that follows illustrates how the hash partitions can be split across devices.
When to Use Hash-based Partitioning Hash-based partitioning can benefit queries that are searching for a single row or a small number of rows in a very large table or for tables in which the primary search arguments are high cardinality data elements such as SSN, product SKUs, customer IDs, account numbers, etc.
If most table queries require full table scans, hash partitioning may be the best partitioning strategy. Hash partitioning is also a good choice for tables with many partitions as the user does not need to know or define the dividing criteria for each partition. The main goal of hash-based partitioning is to address the issue of data skew while maintaining the placement of deterministic data. To define a table with hashed partitions in such a way as to avoid data skew, the column s chosen for the partition key should define the key to be as unique as possible.
Keys that are not defined uniquely will tend to bunch the data together, thereby nullifying the benefits of the hashed partitions. The following examples of hashed tables illustrate both good and bad hashed partitions. The data distribution is highly skewed because of the duplicate hash partition key: If you insert a large number of rows where the partition key column has a high duplication ratio, hash partitioning also results in partition skew.
To avoid that skew, just use the full value of the datetime portion. While it is not possible to list all rows of data with specific time stamp values, it is essential to note that by just changing the time value to a more granular value, much of the skew can be avoided.
In the following example, an algorithm is used to increment the initial datetime value by milliseconds. Example 2: Recall the earlier example that stored greeting card sales.
If you use sale date and time as the hash partition key, the data will be distributed across the partitions irrespective of date and time it was sold. In this case, you could use hash partitioning on a representative key of the data within the table. You should keep the following items in mind when choosing hash-based partitioning. For example, a sales territory may be composed of several continents.
In order to access information about a particular sales territory, list partitions allow for partition elimination of all sales territories except for the one of interest. The following example and the corresponding diagram illustrate list partitioning. Seg1 Figure In the example, additional sales territories and states can be added. However, for the values in the list to move from one partition to another, the data will have to be reloaded into the table.
If later a partition called Africa has to be added and data from region4 is to be moved from region4 to region5-Africa, then data from region4 has to be bulk copied out, region4 truncated, and data reloaded.
Then the data will be automatically placed in the correct list partitions. As you can see, list partitions pretty much inherit the characteristics of range partitions except they are more specific in granularity. Because of 76 Chapter 3: When to Use List Partitioning List partitions are unique in that you decide where to place data based on specific values of the partitioning key.
Unlike the other methods where data is grouped by a range of data or by the resulting hashed key value, list partitions are dependent on the key values always existing prior to being placed in the table. If a key value has not been defined in the partition definition, ASE will raise an error. List partitions can be useful for partitioning large tables on partition keys that are based on summary data, such as fiscal quarter, where you need to keep summary data for five years about sales by quarter.
You could define the partitions such that partition and data maintenance could be performed only on the most current data. This will retain the ability to truncate old or unnecessary data using the new truncate partition command.