Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

SYB ASE 15.7 - Best Practices / Lessons Learned

jgleichmann
Active Contributor
‎03-24-2014 2:12 PM

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.

Migration

Prerequisites

Test scenario:



Source System NW731 SPS10 BW (distributed)


MaxDB 7.9


Windows 2012


Physical Size 2TB


Logical Size 1.7TB


40GB RAM


8CPU








Target System NW731 SPS10 BW (distributed)


Sybase ASE SP110


Windows 2012


40GB RAM


8CPU







Export/Import

  1. At first all notes and new kernel/migration tools have to be updated on the source system to run a clean export
  2. Second step is to run the SMIGR_CREATE_DDL to create the needed SQL files for the BW tables
  3. check dictionary and other steps described in the system copy guide
  4. Run the export with migration optimized settings (depends on each source database type)
  5. Create the target DB (setup parameters, configuration of CPU, caches, pools and update to SP110 => Best Practice note 1680803 )
  6. Start the import manually! That is important to control and setup the config and may be seperated index creation with own parameter setup
  7. 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.


Solution


It is mandatory to use at least the following component versions to run a migration:

- ODBC 15.7.0.508

- 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


Solution


Option 1

Collapse the cube to at least 1500 requests (=> 1 REQ = 1 Partition)


Option 2

Create an entry on the source system in table rsadmin (1691300 - SYB: Unpartitioned F fact tables for InfoCubes) with :

ObjectValue
SYBASE_UNPARTITIONED_F_FACT_01ZSDM1CS3

Note:

=> 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.

Solution

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).

Sybase documentation to this option:

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.

Summary

Compression

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.

Performance

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.

MeasurementMaxDBSybase
Startup time of DB12min<30sec
Data compression

no - still 1700GB

770GB
Backup Compression176GB219GB (only SID DB, not master or other)
Partitioningnoyes (up to about 1500 partitions)
GUI Administrationyes (Database Studio / DBACOCKPIT)yes (SCC / DBACOCKPIT)
Shadow DB solutionyesyes
auto. configuration checkyesyes (depends on the SAP release*)
in memory ablenoyes (currently not supported by SAP BS)
db verifyyesyes, with backup or dbcc
shrinking data filesyesyes (note 1881347 since SP100)
huge pagesnoyes ( 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:

7.02 SP17
7.30 SP13
7.31 SP14
7.40 SP09

1581695 - SYB: Configuration Guide for SAP ASE 16.0

1749935 - SYB: Configuration Guide for SAP ASE 15.7

Migration effort

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.

Administration effort

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 )

Best Regards,

Jens Gleichmann

Technology Consultant at Q-Partners (www.qpcm.eu)

######

Edit:

added configuration check

1581695 - SYB: Configuration Guide for SAP ASE 16.0

1749935 - SYB: Configuration Guide for SAP ASE 15.7

######

17 Comments
Labels in this area
Popular Blog Posts