SYB ASE 15.7 – Best Practices / Lessons Learned
Hello database experts,
more and more people are interested in Sybase ASE as new database solution for their SAP systems in cause of unknown future of MaxDB and the high prices of databases and features of RDBMS like Oracle and DB2.
But how performant is Sybase ASE?
How big is the migration effort?
How good is the compression?
How big is the administration effort?
I will clarify some aspects and bring a little bit light into the darkness or more or less bad documented areas.
- Sybase section on note 888210 – NW 7.**: System copy (supplementary note)
- Implement note collection for SYB 7.x (1612437 – SYB: System copy with SMIGR and table partitioning)
- BW Report to determine the number of partitions in BW system (Report RS_BW_PRE_MIGRATION; 1690674 – RS_BW_PRE_MIGRATION )
- SMIGR_CREATE_DDL notes/fixes (search for them, most once should be included in the collection note; parallel index creation option)
- replace migration tools (R3load min. pl 402, R3ldctrl, R3ta, R3szchck, DBSL min pl431, ODBC min. 22.214.171.1248)
- Migration Best Practice (1680803 – SYB: Migration to SAP Sybase ASE – Best Practice)
- Update to SYB SP110 or later ( 1590719 – SYB: Updates for SAP Adaptive Server Enterprise (SAP ASE) )
- Unicode System or Unicode Migration (SYB ASE runs only with UC systems!)
- Configuration of the Sybase database (1749935 – SYB: Configuration Guide for SAP ASE 15.7 )
Source System NW731 SPS10 BW (distributed)
Physical Size 2TB
Logical Size 1.7TB
Target System NW731 SPS10 BW (distributed)
Sybase ASE SP110
- At first all notes and new kernel/migration tools have to be updated on the source system to run a clean export
- Second step is to run the SMIGR_CREATE_DDL to create the needed SQL files for the BW tables
- check dictionary and other steps described in the system copy guide
- Run the export with migration optimized settings (depends on each source database type)
- Create the target DB (setup parameters, configuration of CPU, caches, pools and update to SP110 => Best Practice note 1680803 )
- Start the import manually! That is important to control and setup the config and may be seperated index creation with own parameter setup
- standard postprocessing described in the system copy guide
Issues during Sybase migration
1. R3load issue trunc log issue
[ASE Error SQL1105]Can't allocate space for object 'syslogs' in database '<SID>' because 'logsegment' segment is full/has no free extents.
It is mandatory to use at least the following component versions to run a migration:
– ODBC 126.96.36.1998
– DBSL 720_EXT UC 64, pl 431
– R3load 720_EXT UC 64, pl 402
Note: log segment must be big enough to handle the I/O that the checkpoint can be triggered in this time
=> increase size of the log segment or decrease parallel degree (=import jobs)
2. Partitioning issue
number of partitions > 8000 partitions, no official statement how many partitions are supported! My tests results show a working number of 1500 partitions.
Report RS_BW_PRE_MIGRATION: A buggy display of size => that are only 6GB not 6TB!
So we have 2 options because if I import this table with 8172 partitions, because I get an error while import the data:
(SYB_IMP) ERROR: DbSlPrepare/BegModify
failed rc = 99, table "/BIC/FZSDM1CS3"
SQL error 30046)
error message returned by DbSl:
[ASE Error SQL21][SAP][ASE ODBC Driver][Adaptive Server Enterprise]WARNING - Fatal Error 702 occurred
Collapse the cube to at least 1500 requests (=> 1 REQ = 1 Partition)
Create an entry on the source system in table rsadmin (1691300 – SYB: Unpartitioned F fact tables for InfoCubes) with :
=> Table must be exported again with new run of SMIGR_CREATE_DDL, because the new table creation statement without partitioning must be written to the sql files.
=> so please analyze this before you start the export! Collapse or export with rsadmin option!
3. Index creation issue (sorted creation)
Index creation fails with:
SQL error 1530: Create index with sorted_data was aborted because of a row out of order.
If index creation fails due to data being out of order, the DDL template file mapping is wrong for the respective package.
Map the failing package to DDLSYB_LRG.TPL to omit the sorted_data option for the creation of the SAP primary keys. If the R3load task files have already been generated, modify the corresponding command file (<package name>__DT.cmd).
The sorted_data option can reduce the time needed to create an index by skipping the sort step and by eliminating the need to copy the data rows to new pages in certain cases. The speed increase becomes significant on large tables and increases to several times faster in tables larger than 1GB.
If sorted_data is specified, but data is not in sorted order, Adaptive Server displays an error message, and the command fails.
Creating a nonunique, nonclustered index succeeds, unless there are rows with duplicate keys. If there are rows with duplicate keys, Adaptive Server displays an error message, and the command fails.
Tips for migration
Increase migration performance with seperated data import and index creation, with different DB settings. All settings therefore are described in the Best Practice migration guide.
Compression ratio like MSSQL (no wonder because MSSQL based on Sybase source code) but not as good as e.g. Oracle 11g compression for OLTP, but this is only my impression, because I have no 1:1 example on Oracle.
In Oracle we have normally a block size of 8k and there the block compression takes place. An index compression and a secure file (LOB files) compression is also included.
With Sybase we have a row compression to compress away empty spaces/zeroes in fixed length columns.
Both page dictionary and page index compression strategies are used at the page/block level and last but not least the LOB compression for large objects.
This all happens with a SAP standard block/page size of 16k.
Compared to MaxDB this is a quantum jump not only the fact that you save disk space, you also increase the efficiency of your data cache.
Here are a bit more tests required to take a significant statement. SAP tests results in a performance boost of 35-40% compared to MaxDB.
|Startup time of DB||12min||<30sec|
no – still 1700GB
|Backup Compression||176GB||219GB (only SID DB, not master or other)|
|Partitioning||no||yes (up to about 1500 partitions)|
|GUI Administration||yes (Database Studio / DBACOCKPIT)||yes (SCC / DBACOCKPIT)|
|Shadow DB solution||yes||yes|
|auto. configuration check||yes||yes (depends on the SAP release*)|
|in memory able||no||yes (currently not supported by SAP BS)|
|db verify||yes||yes, with backup or dbcc|
|shrinking data files||yes||yes (note 1881347 since SP100)|
|huge pages||no||yes ( note 1805750 and this blog)|
*The configuration requirements and recommendations specified in this SAP Note can be compared with your configuration of an SAP ASE database with the DBA Cockpit. This feature is available starting with the SAP_BASIS support package stacks:
Not more effort compared to another DB. You just have to read the migration Best Practice of SAP and notice the mentioned known errors.
It is definitively in cause of the compression and partitioning features a lot of faster than MaxDB and a good alternative for all other more expensive DBs. It fits into the concept of SAP for the next years.
Not all functions and features are as good documented/integrated as wished for the customers but SAP keeps going on to improve this things.
The integration of the tools in dbacockpit are pretty good, but such a nice colorful and gladly clickable interface like the database studio is not integrated in Sybase ASE. The people who already familiar with the commandline based administration like Oracle or DB2 are learning fast the new commands/stored procedures. The procedures are nearly the same like MSSQL – no surprise or?
A automated configuration check or script for the whole configuration would be really helpful. I have written my own scripts because I don’t want waste time to copy paste over 100 parameters for each DB which I’m going to install.
It is also easy to update the Sybase ASE with a GUI wizzard, but to set all the additionally parameter for performance which are not described in the configuration note, you will need some time to size the perfect values.
I hope I could show you some new interesting facts of Sybase ASE. It is a definitively a good alternative to MaxDB and must now accept the challenge against Oracle, DB2, MSSQL etc. SAP has still some work to do to fully integrate all functions.
If you have any further questions, don’t hestate to comment the blog or contact me or one of my colleagues at Q-Partners ( info_at_qpcm_dot_de )
Technology Consultant at Q-Partners (www.qpcm.eu)
added configuration check