Sybase - Acceleration & Protection
Optimization of ETL & Reorgs, PII Masking, Data Replication
Sybase Challenges
As an SAP Sybase IQ or ASE DBA, you may face one or more of these performance or protection issues:
- Unloading and Loading Large Sybase Tables
- Slow helper operations (e.g., reorganizations) or queries
- Unprotected Personal Identifiable Information (PII)
- Annoying Database migration or -replication
- Generation of or access to test datasets
Also, specific performance diagnostics and tuning also take time and can affect other users. Specialized tools for data masking and test data management are expensive and too difficult to use. Finally, stored SQL procedures can also be programmed inefficiently, need to be optimized, and still take too long.
Solutions
Use the program SortCL in the product IRI CoSort or in the IRI Voracity platform, to filter, sort, join, and aggregate the extracts in parallel. The Sybase SQL Server Reference Manual recommends an external pre-sorting to speed up index creation during database loading. Multiple tables created quickly in the query index order should help speed up queries.
How to speed up Sybase loads: „CoSorting“ data in the file system prior to Sybase loads can:
- Speed load capacity 2 and 10X
- Remove the sorting aspect of the charge
- Reducing database and disk overhead
- Increase in processing rate (lines per hour)
The recommended technique states that flat files should be pre-sorted on the clustered index key. CoSort was designed to sort large files in parallel, scaling performance linearly with volume. You should create a clustered index. But first, you should eliminate indexes by creating tables without indexes or triggers, or removing them from the table you are loading. Then you can:
- Sort data by primary key index
- Business Capability Planning loads the sorted table with a fast bcp path (since there are no indexes on the table).
- Use the `CREATE INDEX` SQL command with the `SORTED_DATA` option to create the clustered index.
CoSort's SortCL program can also transform and reformat data for database migrations and custom (detail, summary, and delta) reports, and protect sensitive data at a field level using a variety of encryption, masking, and other functions.