This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Heterogeneous Migration – Kernel Params for ASE 16 Parallel Index Creation

During an SAP BW 7.40 heterogeneous migration (using R3load) from ASE 16.0 to ASE 16.0, it’s possible to use an ASE parallel index creation feature to improve the speed of the index creation process (during import) on the target system.

In this post, I show the required parameter(s) to enable this parallel creation feature, instead of having to manually edit the TPL files to add it.
I am not going to go into the “why use the heterogeneous copy method for migration of an ASE to ASE database“, except to say that certain benefits can be had in certain migration conditions.

The steps that are impacted during the system copy are:

  • the creation of the BW specific table SQL files (SMIGR_CREATE_DDL) in the source system.
  • the import of the BW specific tables and the creation of their indexes in the target system.
  • the “update statistics” job execution selection within SWPM on the target system.

Best Practice

You should note that this post and process was put together with respect to the SAP on ASE Best Practice Guide, which is titled “SAP Applications on SAP Adaptive Server Enterprise – Best Practices for Migration and Runtime” and dated 26-01-2018 which can be found attached to SAP note 1680803.

In the Best Practice document, it specifically says:

  • see SAP Note 1952189: Parallel Index Creation with SMIGR_CREATE_DDL.
  • Changes to the template file DDLSYB_LRG.TPL will not affect the migration of special SAP BW tables

When we follow the mentioned SAP note 1952189, we see that simply selecting the correct option during execution of report SMIGR_CREATE_DDL (in the source system) should allow parallel index creation.
It does not! That is the reason for this post.
I have a theory as to why this selection process does not work; it was never tested by SAP for ASE to ASE. It’s just my theory.

Parallel Index Creation

During the subsequent import into the target ASE 16.0 database, the generated DDL (from SMIGR_CREATE_DDL) is used to create the indexes. In SAP ASE it is possible to use an additional clause on the “CREATE INDEX” statement (“consumers = n“) to use more than 1 worker process when performing the sort operation required for the index build.
This is not so much parallel creation, but parallel sorting during creation. It still speeds up the index creation.

The actual parallelism of the index creation is controlled, as usual, by the number of R3load processes you configure in SWPM (MIGMON).
However, we will stick with the terminology offered by the best practice document.

Hash Statistics

During the migration process, we really want to get through the downtime phase as fast as possible so that post-processing can begin, which can be done by more than 1 person/robot.
Fore this reason, we want to avoid the task “update ASE statistics” step of SWPM. In large BW systems this step can take hours and it prevents the completion of SWPM while it is running.

Instead, as well as a “consumers” clause on the index creation statement, there is also a “statistics” clause to force the creation of index statistics at the point of creation.
This feature allows us to completely skip the update statistics when we also combine this feature with the use of the DDLSYB_LRG.TPL template, which includes this clause also.
It makes complete sense to update statistics after after an index has been created, because the chances are, some of the required index pages are still in memory.

Creating the DDL

BW specific tables may be partitioned and have all sorts of additional features applied, which cannot be easily handled by the usual TABART based DDL generation process (in ASE this uses the DDLSYB.TPL and DDLSYB_LRG.TPL template files).

NOTE: SAP note 1680803 contains an updated DDLSYB_LRG.TPL with “fixes” described in the Best Practice document, also attached to that note.
NOTE: Follow SAP note 1836598 for instructions on how to use DDLSYB_LRG.TPL.

Because of those special BW uses, we are required to execute the SMIGR_CREATE_DDL report in the source system prior to starting the export process.
The report simply creates the necessary SQL DDL to re-create these special BW tables and their indexes.

It is at this point that we can influence the way that these BW tables and their indexes are created.
Specifically, the number of consumers and the creation of hash statistics.

The Parameter

While running the SMIGR_CREATE_DDL report with the correct selection options for the target database “Sybase” with “parallel index creation”, you will notice that the SQL files generated do not contain either the “consumers” or the “statistics” clauses.

After tracing the ABAP code through execution, it was found that the code was checking for 2 specific SAP profile parameters.

To enable the parallel index creation feature, you need to set the parameter “dbs/syb/pll_idx_creation”. The additional parameter related to consumers did not seem to work at Kernel 7.40, but we set it anyway:

dbs/syb/pll_idx_creation = X
dbs/syb/consumers = 4

Once set in RZ10 on the instance profile, the SAP system needs a restart for the parameters to take effect.
Because we found the consumers parameter did not get picked up at all, we just manually edited the SQL files to adjust the consumer number from the default of 3, to our chosen number (see best practice PDF document for comments on how to decide this number).

Just be aware, we are not certain if these parameters influence the standard BW process chain steps for index creation after load.
However, one thing I would say is that the best practice document also mentions that BW process chains should not be dropping indexes when running BW on ASE. This probably goes against the process the BW developers are used to performing with most other databases.
For the above mentioned reason, it is therefore best to only affect those parameters on one app server (where you run SMIGR_CREATE_DDL) and to revert them once the report has finished.

Summary

We have discussed how ASE to ASE migrations using R3load with NW 7.40, do not seem to call the ABAP code to generate the required “consumers” clause in the output SQL code from the running of the SMIGR_CREATE_DDL ABAP report.

I showed how to manually force this using the Kernel parameters, which also included the hashed statistics capability.

I explained how both of the parameters combined, can affect the subsequent R3load import process timings, significantly reducing downtime.

SAP on ASE (Sybase) – 10 Positive Points

Having been part of a major programme of work to move a complete SAP landscape from an Oracle based platform onto an SAP ASE (formerly Sybase) database based platform, I experienced running SAP on ASE for the first time.
My main experience with SAP has been on Oracle, SQL Server, DB2 and HANA.
So it was a new experience seeing it run on ASE.
There were a lot of pain points (a future blog article, I’ll link back here), and yet there are always some positive points.
Below I have documented 10 of what I feel are the positive points of running SAP on the ASE database platform in comparison with other database solutions (I’m not including HANA as it is far more expensive).  This may or may not help you make your own decision.

1 License Cost
There is undoubtedly an incentive provided by SAP for migration from rival database vendors such as Oracle or IBM.
The cost of running SAP ASE is much reduced by aggressive discounting by SAP in return for eradicating these vendors from the SAP landscape.
You should therefore ensure that you seek to reap the best deal if you’re considering migrating to SAP ASE.

2 No Archive Logs
Unlike an Oracle database, the SAP ASE database does not have an archive log function.
This means, in simple terms, that there are only 2 parts to the database to worry about; the database data files and the transaction logs (equivalent to redo logs).
Whilst this may be considered to be additional risk to some, the advances in storage technology only help to negate the need for an additional process for transactional integrity.
This may not be clear cut to some (myself included) but to others, this represents a removal of added complication in the system architecture.

3 No Separate Connection Listener
Out of the box, the SAP ASE database software, when started, initiates only 3 server processes.  The dataserver, the backupserver and the job scheduler.
The dataserver provides the database functionality and also listens on the required ports for incoming database connections.
For an SAP system this is the standard tcp/4901 port.
Unlike an Oracle database platform, there is no need for a standalone network connection listener.
The entire database software stack is started and stopped in one go.

4 Small Codebase
The SAP ASE software is small compared to IBM DB2 or Oracle.  The download is roughly 1.3GB in size and the deployed (on disk) size is typically 2 to 3GB.
This means less demand for disk space for software binaries in an ASE environment.  Typically this diskspace in an SAP landscape is replicated many many times for each SAP system type.
e.g. 3x ERP (dev, qa & production) would all required separate disk storage areas.
In a typical support environment, you would also be looking to retain the last 2 revisions of the database software binaries in your software repository.

5 Integrated Install
The mechanism for installing SAP ASE (recommended by SAP) is to use the SAP software provisioning manager (SWPM).
Part of the SAP Software Toolset, the SWPM provides a client-server based, common graphical user interface, for installing a multitude of SAP software.
The SAP ASE software is installed using the SWPM onto Windows, Unix or Linux and typically can be done in under 3 minutes depending on the initial database datafile size required.

6 Simple Patching
Routine patching of SAP software is well known in the industry for being a complete chore; although crucial.
There is complete underestimation, in my experience, of the fundamental effort required to successfully patch an SAP system and test it.
Often the decision is left until there is no other option.
Patching of SAP ASE is performed in 3 simple steps: 1, Download the patched software. 2, Using SAPControl install the patch. 3, Test.
There are no minor patches or binary only fixes in SAP ASE.  An entire software stack is re-delivered (it’s small).
The database changes (DML and DDL) are applied as part of the patching process, but it’s all initiated from one command line string.

7 SAPControl Integration
Starting and stopping of the SAP ASE software can be performed in at least three different ways.
However, SAP have tried to make this as transparent as possible by integrating the ASE startup into the SAPControl framework.
This allows “startsap” to bring the entire SAP system up, including the database.
It also allows slightly more complex situation whereby the database can be completely started / stopped using the additional tools within the SAP software library, such as Landscape Virtualisation Management (LVM).
Incidentally, the SAPControl mechanism is also the method for initiating the SAP ASE software patching process.

8 DBACockpit Integration
Over the years SAP have standardised and massively improved the database management interface transaction DBACOCKPIT.
From this transaction, you can interactively manage the SAP ASE database through functions such as:
– Parameter configuration.
– Database space management.
– Performance measurement and analysis.
– Database maintenance and housekeeping tasks.

The benefit of the DBACOCKPIT is that for the past few years it is now configured automatically in SAP Solution Manager, so you can see all databases in the entire SAP landscape from within one transaction.
Some of the newer features of DBACOCKPIT (in Netweaver 7.02 SPS15+) are automated parameter configuration according to SAP recommended values.
This reduces the need to extract the recommended parameters and compare with you database’s configuration.

9 Solution Manager Integration
Along with DBACOCKPIT, the SAP ASE software is integrated into Solution Manager end-to-end monitoring.
SAP ASE database extractors pull database performance data into the SAP Solution Manager’s SAP BW module, for analysis by the BASIS team.
Tracking of database storage capacity can be easily measured and alerted on from within the built-in Solution Manager technical monitoring capability.

10 Published SAP Ntes
Unlike other database vendors, the details of issues within SAP ASE are published within the SAP notes system.
This means that accessing information on software bugs or issues, or finding information on performing technical administrative tasks, can be found within the SAP support portal.

SAP on ASE (Sybase) – 10 Lessons Learned

Continuing my SAP on ASE theme (see my other post on 10 positive points of SAP on ASE), I’m going to document the 10 best lessons I learned during the migration and implementation of SAP on SAP ASE (formerly Sybase).
Hopefully these will be useful to any implementation or migration project you may be undertaking.

1 Compatibility with SAP Business Suite
SAP provide SAP ASE for two groups of end-user; standalone use, or use with SAP Business Suite.
The SAP Business Suite is the name given to the suite of software packages such as ERP, BW, CRM, SRM etc.
Due to the 2 distinctions, there are always two delivery channels for the SAP ASE software binaries.

Certain software revisions of SAP ASE are certified (supported) for use with SAP Business Suite products, therefore you should always check if your use case is supported and that you download the relevant software version.

You should also ensure that you validate the information within the SAP notes, to ensure that it is relevant to your use case.
In some situations, there are SAP notes specifically only valid for SAP ASE in use with SAP Business Suite products.

2 Download the Latest
In my experience, the version of SAP ASE will make a big difference when considering the implementation project.
You should consult with your account manager to ensure that you plan to be on the latest release and patch level of SAP ASE possible for your deadlines.

The SAP ASE patches are released very frequently (I’ve seen 3 patches within 6 months); which usually contain some major fix for a possible data corruption scenario.

3 Stay Current
Revisions to the SAP ASE software are performed rapidly.  The software stack is small and no minor fixes (binaries only or single binary fixes) are performed, so plan to patch the database at least once every 3 to 6 months minimum.  If you fail to stay current, then you could face issues staying supported with other compatible software areas (such as SAP Replication Server).

A lot of the SAP notes will be updated to recommend the latest revision (usually for some major issue).
It has been known for a revision to be delayed dramatically (by months) only to be superceded almost immediately by a later revision.

So called “hot fixes” have also been seen, whereby the current revision receives an additional increment in between the previous and the latest.  Usually this is only a stop-gap to fix an issue on a previous revision because the latest revision changes that functionality in a more intrusive manner (with respect to the code lines).

4 Ensure Server Parameters/Config
The configuration parameters of the SAP ASE database is performed against one core SAP note.
The SAP note has a rather messy layout and can be complex to digest manually.
Still, this is a required task and changing the parameters to those recommended by SAP is a must, since the out-of-the-box configuration is never really optimal or potentially even unstable (again refer to the SAP notes).
To ease this task, the method of checking these parameters (providing you’re SAP Netweaver revision is high enough) is through the DBACOCKPIT transaction.
Whilst this allows you to easily configure and apply the settings, it should never negate a manual review of the parameter settings.  Plus, you may just learn something 😉  .

5 Confirm Statements with Your SAP account manager
Sometimes, I have come across erroneous support statement within the SAP notes or the help.sap.com website help pages.
This is potentially due to the two revisions of the SAP ASE software prevalent.  Sometimes the SAP ASE standalone community forget that SAP ASE can run underneath SAP Business Suite.

Therefore, if you see a SAP note stating you’re not supported if you do “X” or have “Y” installed, query it with your account manager.  It may not be relevant to your use case.

6 Set Relevant DB Parameters
Sometimes the parameters listed within the single SAP note (for that specific SAP ASE release), will be specific to SAP BW, or SAP ERP.

Since it’s entirely possible for you to be using the BW component heavily, inside your ERP system, you may wish to apply the BW related parameters (the old OLAP vs OLTP).
Therefore, you should double check the SAP note containing the SAP ASE recommended parameters, and not just blindly apply it.

7 Rigorously Test
It’s fairly simple and requires little effort to patch an SAP ASE database.
For this reason, you should expend the time saved, by testing.
As an example, I experienced an issue whereby I patched the SAP ASE software to a later revision, only to discover during a SAP system copy task, that a minor bug in the later software revision prevented the copy process from working.

In the end, after nearly a week of working on the problem (I immediately raised it with SAP also), I found (not SAP) that there was a workaround to get it working.
I also went on to note that the issue was later fixed in a later SAP ASE revision.

Therefore I decreed that, as part of upgrade testing, some of the standard internal department processes would be tested prior to upgrading the core systems.

8 Always Check the Bug List
As a follow on to the previous statement, you should check the bug listing of the next revision with eagle eyes.
You may not be experiencing a specific issue, but it could save you from potential corruption or getting into an unfixable situation.

Currently the SAP ASE bugs are not listed in individual SAP notes, but generally in the Release Information Note for the next revision (once it is solidified).
In addition to the RIN, there is one core SAP note for each SAP ASE release (e.g. 15.7 or 16.0) in which the up and coming revisions are noted.
Check this regularly.

9 Configure Your Own Housekeeping
One small shortfall in the SAP ASE software, is housekeeping.
The log files for the database, jobserver and backupserver do not rotate until the SAP ASE instance is restarted.
You may wish to keep these files tidy and compressed with your own housekeeping scripts.

There are also a couple of recommendations for retaining certain files such as the last configuration file, the dump history file and an export of the sysdevices table, on a separate file system (a bit like the old SystemV export of the partition table), just in case of a restore scenario.

10 Performance Tune Backups / Restores
Out-of-the-box the performance of backups and restores is adequate.
However on a 1.3TB database I saw backups (within 1 stripe only) taking in excess of 4 hours (to a DataDomain appliance).

By simply spending 2 days to performance tune, by adjusting just one SAP ASE parameter, I was able to reduce this by 1.5 hours (~30% improvement).

This will also be a future blog article as it was such a simple but effective task.  Don’t accept the out-of-the-box settings.

Make sure that you test the restore capability and also ensure that you allocate adequate disk space for emergency backups (dumps) to disk (if you’re planning to backup to a third-party tool).

Make sure you watch out for future ASE articles for additional lessons-learned scenarios.

SAP Unicode Conversion MAXDOP Parallelism on MS SQL Server

When performing a Unicode conversion of an SAP system running on MS SQL Server database, you get pointed to the Unicode Collection Note 1319517.
This note points to a MS SQL Server specific note (Note 1054852 – Recommendations for migrations to MS SQL Server)  which covers a couple of different scenarios, such as moving to SQL Server from another database provider, or just performing an R3load export/import (like me) to the same platform.

Now from experience, I know that the R3load import is actually pretty quick (especially on Oracle with DIRECTPATH).  What takes the time is the index creation afterwards.  Lots of scanning and I/O needs to be done.  This is where you waste time, and where you could save time.
The note 1054852 mentions the use of the MAXDOP (Maximum Degree of Parallelism) SQL Server parameter that could benefit any subsequent index creation/rebuild tasks performed during an R3load import.
The recommendation in note 1054852 is to change the MAXDOP from the SAP recommended setting of 1, to 4 for the entire SQL Server instance.  NOTE: The maximum MAXDOP in 2008R2 is 1024 (see here and SAP note 1654613).
This is the “hammer” approach and should be used with caution.  There is a nice blog here on the use of MAXDOP with SQL Server.  The blog shows how setting this to a value greater than 1 can actually increase fragmentation.  This is completely understandable.  However, the reader must note that the fragmentation is only an issue if the index is specifically set with ALLOW_PAGE_LOCKS to “OFF” (the default in 2008R2/2012 is “ON” (see syntax here)! ).
There is another blog article that shows how this fragmentation problem is overcome by setting the ALLOW_PAGE_LOCKS option.  The article states that by default this is “ON”.  However, according to Microsoft KB 2292737, the default is “OFF” by design. 
So which is it?  In fact, the MS syntax for “ALTER INDEX” specifically states that it is not possible to reorganise an index with ALLOW_PAGE_LOCKS set to “OFF”.

Here’s how to check the value of the ALLOW_PAGE_LOCKS setting for an index (there is no global setting):


use <SAP DB>
go

select name,type,allow_page_locks from sys.indexes
where allow_page_locks != 1
order by 1;


And the results…  well, some of the largest SAP tables in my system have their indexes set with ALLOW_PAGE_LOCKS to “OFF“.  Great!

NAME
TYPE
ALLOW_PAGE_LOCKS
ARFCRDATA~0
1
0
ARFCSDATA~0
1
0
COVREF~0
1
0
COVREF~001
2
0
COVREF~002
2
0
D010INC~0
1
0
D010INC~1
2
0
D010TAB~0
1
0
D010TAB~1
2
0
EDI40~0
1
0
EDIDC~0
1
0
EDIDC~1
2
0
EDIDC~2
2
0
EDIDC~3
2
0
EDIDC~4
2
0
EDIDS~0
1
0
EDIDS~1
2
0
EDIDS~2
2
0
REPOLOAD~0
1
0
REPOSRC~0
1
0
REPOSRC~SPM
2
0
TRFCQIN~0
1
0
TRFCQIN~1
2
0
TRFCQIN~2
2
0
TRFCQIN~3
2
0
TRFCQIN~4
2
0
TRFCQIN~5
2
0
TRFCQIN~6
2
0
TRFCQOUT~0
1
0
TRFCQOUT~1
2
0
TRFCQOUT~2
2
0
TRFCQOUT~3
2
0
TRFCQOUT~4
2
0
TRFCQOUT~5
2
0
TRFCQOUT~6
2
0
VBDATA~0
1
0
VBHDR~0
1
0
VBMOD~0
1
0

I can understand the VB* tables might not want index locking and this is also hinted at in the older SAP R/3 Performance Tuning Guide for Microsoft SQL Server 7.0 web page.  However, where did the other tables come from?
I took a look on SAP Notes but I was unable to find anything definitive.  The system I was working on was recently copied and used SWDM (SAP Software Deployment Manager) to perform the post-copy steps, so it’s possible that the indexes were automatically adjusted (ALTER’d) to try and ensure a consistent approach.
What to do next?  Well, some of those tables can be ignored since they are supposed to have ALLOW_PAGE_LOCKS set to “OFF”, some, like REPOLOAD are re-populated only during a system-copy with R3 tools (e.g. Unicode conversion), so you could try adjusting the setting during the R3load import. 
The others, well in theory you would try to minimise data in those tables (like TRFC*) before you perform a Unicode conversion, so the indexes wouldn’t be massive anyway.

At the end of the day, all we are talking about here is a little fragmentation.  So let’s move on.

Let’s go back to the MAXDOP setting recommendation mentioned in SAP note 1054852. 
Now, my system happens to be a BW type system (it’s actually SEM, but this is just BW in disguise), so I found SAP Note 1654613 – SQL Server Parallelism for SAP BW which suggests that for SAP BW systems, you can now manage the MAXDOP parameter settings in table RSADMIN through report SAP_RSADMIN_MAINTAIN by setting parameters MSS_MAXDOP_QUERY, MSS_MAXDOP_<cubename> and MSS_MAXDOP_INDEXING.
The note 1654613 also goes on to say that by applying the note corrections (or the related support package stacks) the default MAXDOP for BW queries is set to 2, and for (process chain) index creations is set to 8.
Aha! 
So the Unicode collection note’s setting of 4, could actually contradict the setting of 8 in BW systems!
The note 1654613 also states that you may increase the MAXDOP for queries to more than 4, but this depends on the number of worker threads configured in your SQL Server instance.
The worker threads setting in my SQL Server 2008R2 instance was set to “0”, which means the number is dynamically calculated (see here).

You can use the sp_configure procedure to check your setting:

sp_configure @configname='max worker threads';

You can query the current number of threads (thanks to https://blogs.msdn.com/b/boduff/archive/2008/05/17/configuring-max-worker-threads-in-sql-2005.aspx):

select count(*) from sys.dm_os_threads;

My idle database was using 50 threads on a VMWare VM with 4 processors.  So I guess I could increase the MAXDOP for queries permanently in my BW (SEM) system.

You should also note the setting MSS_MAXDOP_INDEXING = 0 means that all available threads will be used during (process chain) index creation.

Summary:
We are advised to set MAXDOP to 4 when moving an SQL Server database or performing a system-copy or Unicode conversion. 
However, more detailed analysis has shown that for BW systems specifically, we can potentially adjust the MAXDOP setting even higher than 4 during our R3load import, to ensure that we make the best use of the available CPU. 
This is underlined by the fact that defaults within the BW system are sometimes higher than the recommended setting of 4.
Therefore, I will be trying out a value of 12 (default of 8 for index creation + 50%) in my Unicode conversion:

sp_configure 'max degree of parallelism', 12;
reconfigure with override