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

Tag: SAP

Hardening SAP Hostagent SSL Connections

You may have recently had a penetration test and in the report you find that the SSL port for the SAP Hosagent (saphostexec) are listed as allowing weak encryption cipher strength and older SSL protocols.
You want to know how you can remedy this.

In this post I will show how we can appease the Cyber Security penetration testing team, by hardening the SSL ciphers and protocols used for connections to the Hostagent.

What Are Weak Ciphers?

Ciphers, like Triple-DES and Blowfish use 64-bit block sizes (the cipher text is split up into blocks of 64-bit in length) which makes a block cipher more vulnerable to compromise, compared to a cipher that uses a larger 128-bit block size.

The cipher is agreed upon during the setup of the SSL connection between the client and the server (SAP Hostagent in our scenario).
If a server advertises that it supports weaker ciphers, and a client elected to use one of the supported weaker ciphers during the SSL connection negotiation, then the connection could be vulnerable to decryption.

What Are Older SSL Protocols?

Through time the SSL protocol has been improved and strengthened.
The SSL protocol versions go from SSL v1.0 to SSL v3.0, then re-named to TLS and the versions again incremented from TLS 1.0, TLS 1.1, TLS 1.2 and the most recent TLS 1.3 (in 2018).

The old SSL versions of the protocol are deprecated and should not be used. The slightly newer TLS versions 1.0 and 1.1 are also now widely deprecated (do not confuse “deprecated” with “unused”).

It is therefore recommended, generally, to use TLS 1.2 and above.

Why Harden the Hostagent SSL Service?

Now we have an appreciation of our older ciphers and protocols, let’s look at the Hostagent.
Usually the PEN test report will highlight the SSL TCP port 1129, and the report will state two things:

  • The SSL ciphers accepted by the Hostagent include weaker ciphers (such as RC4).
  • The SSL protocols accepted by the Hostagent include TLS 1.0 and 1.1.

The above issues present opportunities for hackers that may allow them to more easily compromise a SAP Hostagent on a SAP server.
Whilst this may not sound too bad, it is just the Hostagent, when we realise that the Hostagent runs as the Linux root (or Windows SYSTEM user) and there are known vulnerabilities that allow remote exploitation, we can see that the Hostagent could be a window into the SAP system as the highest privileged user on the server!
It is therefore quite important to try and protect the Hostagent as much as possible.

How Can We Harden the Hostagent SSL Service?

To ensure that weak ciphers are not used, the server needs to be configured to not use them. In the context of SAP Hostagents, they are the SSL servers and they need to be configured to only use stronger ciphers.

The SAP Hostagent is really the same as the SAP Instance Agent in disguise.
Because of this, it is possible to find documented parameters that allow us to harden the SSL service of the Hostagent in the same way.

By following SAP note 510007, we can see two SAP recommended parameters and settings that can be used to harden the SSL ciphers used:

  • ssl/ciphersuites = 135:PFS:HIGH::EC_P256:EC_HIGH
  • ssl/client_ciphersuites = 150:PFS:HIGH::EC_P256:EC_HIGH

The SAP note 510007 includes an extremely good description of the SAP cryptographic library’s capabilities, the role of SSL and even some commentary on the probability of an older protocol being abused.
I feel that the note has been written by someone with a lot of experience.

The above two parameters apply a numeric calculation that selects an appropriate strength of cryptographic ciphers to be used for server and client connectivity.
With the Hostagent, we are more concerned with the server side, but the Hostagent can also do client calls, so we apply both parameters in unison.

The values assigned to the two parameters are described by the SAP note as being good, but also allow flexibility for backwards compatibility with the older SAP and non-SAP software. Again the SAP note stresses the importance of compatibility (and having stuff continue to work) versus security.

What is the Impact of the Parameters?

To be able to see the impact to the Hostagent, we first need to see what the Hostagent supports out-of-the-box.

Thanks to a great post here: www.ise.io/using-openssl-determine-ciphers-enabled-server
we can use a super simple shell script (on Unix/Linux) to call the OpenSSL executable, make a connection to the target server (the Hostagent) and check the list of ciphers and protocols that are advertised.
The code from the above site is here:

for v in ssl2 ssl3 tls1 tls1_1 tls1_2; do 
   for c in $(openssl ciphers 'ALL:eNULL' | tr ':' ' '); do 
      openssl s_client -connect localhost:1129 -cipher $c -$v < /dev/null > /dev/null 2>&1 && echo -e "$v:\t$c" 
   done 
done

You can see that I have placed “localhost” and “1129” in the code.
This is because I am running the script on a Linux host with a SAP Hostagent installed, and the SSL port is 1129 (default).

The output is something like this (depending on your version of the Hostagent):

tls1: ECDHE-RSA-AES256-SHA 
tls1: AES256-SHA 
tls1: ECDHE-RSA-AES128-SHA 
tls1: AES128-SHA 
tls1: RC4-SHA 
tls1: RC4-MD5 
tls1: DES-CBC3-SHA 
tls1_1: ECDHE-RSA-AES256-SHA 
tls1_1: AES256-SHA 
tls1_1: ECDHE-RSA-AES128-SHA 
tls1_1: AES128-SHA 
tls1_1: RC4-SHA 
tls1_1: RC4-MD5 
tls1_1: DES-CBC3-SHA 
tls1_2: ECDHE-RSA-AES256-GCM-SHA384 
tls1_2: ECDHE-RSA-AES256-SHA384 
tls1_2: ECDHE-RSA-AES256-SHA 
tls1_2: AES256-GCM-SHA384 
tls1_2: AES256-SHA 
tls1_2: ECDHE-RSA-AES128-GCM-SHA256 
tls1_2: ECDHE-RSA-AES128-SHA 
tls1_2: AES128-GCM-SHA256 
tls1_2: AES128-SHA 
tls1_2: RC4-SHA 
tls1_2: RC4-MD5 
tls1_2: DES-CBC3-SHA

You can see that we have some RC4 and some DES ciphers listed in the TLS 1.0, TLS 1.1 and TLS 1.2 sections.
We now use SAP note 510007 to decide that we want to use the more secure settings that remove these weaker ciphers.

In the case of SAP Host Agents, we adjust the profile file /usr/sap/hostctrl/exe/host_profile (as root), and add our two SAP recommended parameters (mentioned previously):
ssl/ciphersuites = 135:PFS:HIGH::EC_P256:EC_HIGH
ssl/client_ciphersuites = 150:PFS:HIGH::EC_P256:EC_HIGH

NOTE: You should be running the latest SAP Hostagent, this is very important for security of your system. There are known vulnerabilities in older versions that allow remote compromise.

Once set, we need to restart the agent:

/usr/sap/hostctrl/exe/saphostexec -restart

We can re-execute our check script to see that we have a more secure configuration:

tls1: ECDHE-RSA-AES256-SHA 
tls1: AES256-SHA 
tls1: ECDHE-RSA-AES128-SHA 
tls1: AES128-SHA 
tls1_1: ECDHE-RSA-AES256-SHA 
tls1_1: AES256-SHA 
tls1_1: ECDHE-RSA-AES128-SHA 
tls1_1: AES128-SHA 
tls1_2: ECDHE-RSA-AES256-GCM-SHA384 
tls1_2: ECDHE-RSA-AES256-SHA384 
tls1_2: ECDHE-RSA-AES256-SHA 
tls1_2: AES256-GCM-SHA384 
tls1_2: AES256-SHA 
tls1_2: ECDHE-RSA-AES128-GCM-SHA256 
tls1_2: ECDHE-RSA-AES128-SHA 
tls1_2: AES128-GCM-SHA256 
tls1_2: AES128-SHA

The more insecure ciphers are removed, but we still see those older protocols (TLS 1.0 and TLS 1.1) in the list.
We decide that we would like to further harden the setup by removing those protocols.

If we look at SAP note 2384290, we can see that an alternate set of parameter values are provided:

  • ssl/ciphersuites = 545:PFS:HIGH::EC_P256:EC_HIGH
  • ssl/client_ciphersuites = 560:PFS:HIGH::EC_P256:EC_HIGH

Let’s apply these and re-run the test for a final time.
We can see that we get a super refined list of protocols and ciphers:

tls1_2: ECDHE-RSA-AES256-GCM-SHA384 
tls1_2: ECDHE-RSA-AES256-SHA384 
tls1_2: ECDHE-RSA-AES256-SHA 
tls1_2: AES256-GCM-SHA384 
tls1_2: AES256-SHA 
tls1_2: ECDHE-RSA-AES128-GCM-SHA256 
tls1_2: ECDHE-RSA-AES128-SHA 
tls1_2: AES128-GCM-SHA256 
tls1_2: AES128-SHA

Our Hostagent SSL service is now as secure as it can be at this point in time, within reason. If we try and adjust the ciphers any further, we may end up breaking compatibility with other SAP systems in your landscape.

Summary

We’ve seen how applying two SAP standard parameters to the SAP Hostagent and restarting it, can significantly strengthen the posture of the Hostagent’s SSL service.

However, we need to be cautious of compatibility with other SAP and non-SAP software in the landscape, which may talk to the Hostagent only with older protocols.

As a final note, you may be wondering if we can remove the HTTP service from the Hostagent? At this point in time I have not found a SAP note that would indicate this is possible or recommended. However, since the HTTP protocol is known to be insecure, just don’t use it. This is in comparison with SSL which should be secure, but might not be as secure as it could be.

You may also be interested in:

Best Disk Topology for SAP ASE Databases on Azure

Maybe you are considering migration of on-premise SAP ASE databases to Microsoft Azure, or you may be considering migrating from your existing database vendor to SAP ASE on Azure.
Either way, you will benefit from understanding a good, practical disk topology for SAP ASE on Azure.

In this post, I show how you can optimise use of the SAP ASE, Linux and Azure technical layers to provide a balanced approach to disk use, considering both performance and disk (ASE device) management.

The Different Layers

In an ASE on Linux on Azure (IaaS) setup, you have the following layers:

  • Azure Storage Services
  • Azure Data Disk Cache Settings
  • Linux Physical Disks
  • Linux Logical Volumes
  • Linux File Systems
  • ASE Database Data Devices
  • ASE Instance

Each layer has different options around tuning and setup, which I will highlight below.

Azure Storage Services

Starting at the bottom of the diagram we need to consider the Azure Disk Storage that we wish to use.
There are 2 design considerations here:

  • size of disk space required.
  • performance of disk device.

For performance, you are more than likely tied by the SAP requirements for running SAP on Azure.
Currently these require a minimum of Premium SSD storage, since it provides a guaranteed SLA. However, as of June 2020, Standard SSD was also given an SLA by Microsoft, potentially paving the way for cheaper disk (when certified by SAP) provided that it meets your SLA expectations.

Generally, the size of disk determines the performance (IOPS and MBps), but this can also be influenced by the quantity of data disk devices.
For example, by using 2 data disks striped together you can double the available IOPS. The IOPS are an important factor for databases, especially on high throughput database systems.

When considering multiple data disks, you also need to remember that each VM has limitations. There is a VM level IOPS limit, a VM level throughput limit (megabytes per second) plus a limit to the number of data disks that can be attached. These limit values are different for different Azure VM types.

Also remember that in Linux, each disk device has its own set of queues and buffers. Making use of multiple Linux disk devices (which translates directly to the number of Azure data disks) usually means better performance.

Essentials:

  • Choose minimum of Premium SSD (until Standard SSD is supported by SAP).
  • Spread database space requirements over multiple data disks.
  • Be aware of the VM level limits.

Azure Data Disk Cache Settings

Correct configuration of the Azure data disk cache settings on the Azure VM can help with performance and is an easy step to complete.
I have already documented the best practice Azure Disk Cache settings for ASE on Azure in a previous post.

Essentials:

  • Correctly set Azure VM disk cache settings on Azure data disks at the point of creation.

Use LVM For Managing Disks

Always use a logical volume manager, instead of formatting the Linux physical disk devices directly.
This allows the most flexibility for growing, shrinking and striping the disks for size and performance.

You should stripe the data logical volumes with a minimum of 2 physical disks and a maximum stripe size of 128KB (test it!). This fits within the window of testing that Microsoft have performed in order to achieve the designated IOPS for the underlying disk. It’s also the maximum size that ASE will read at. Depending on your DB read/write profile, you may choose a smaller stripe size such as 64KB, but it depends on the amount of Large I/O and pre-fetch. When reading the Microsoft documents, consider ASE to be the same as MS SQL Server (they are are from the same code lineage).

Stripe the transaction log logical volume(s) with a smaller stripe size, maybe start at 32KB and go lower but test it (remember HANA is 2KB stripe size for log volumes, but HANA uses Azure WriteAccelerator).

Essentials:

  • Use LVM to create volume groups and logical volumes.
  • Stipe the data logical volumes with (max) 128KB stripe size & test it.

Use XFS File System

You can essentially choose to use your preferred file system format; there are no restrictions – see note 405827.
However, if you already run or are planning to run HANA databases in your landscape, then choosing XFS for ASE will make your landscape architecture simpler, because HANA is recommended to run on an XFS file system (when on local disk) on Linux; again see SAP note 405827.

Where possible you will need to explicitly disable any Linux file system write barrier caching, because Azure will be handling the caching for you.
In SUSE Linux this is the “nobarrier” setting on the mount options of the XFS partition and for EXT4 partitions it is option “barrier=0”.

Essentials:

  • Choose disk file system wisely.
  • Disable write barriers.

Correctly Partition ASE

For SAP ASE, you should segregate the disk partitions of the database to avoid certain system specific databases or logging areas, from filling other disk locations and causing a general database system crash.

If you are using database replication (maybe SAP Replication Server a.k.a HADR for ASE), then you will have additional replication queue disk requirements, which should also be segregated.

A simple but flexible example layout is:

Volume
Group		Logical
Volume		Mount PointDescription
vg_aselv_ase<SID>/sybase/<SID>For ASE binaries
vg_sapdatalv_sapdata<SID>_1./sapdata_1One for each ASE device for SAP SID database.
vg_saploglv_saplog<SID>_1./saplog_1One for each log device for SAP SID database.
vg_asedatalv_asesec<SID>./sybsecurityASE security database.
lv_asesyst<SID>./sybsystemASE system databases (master, sybmgmtdb).
lv_saptemp<SID>./saptempThe SAP SID temp database.
lv_asetemp<SID>./sybtempThe ASE temp database.
lv_asediag<SID>./sapdiagThe ASE saptools database.
vg_asehadrlv_repdata<SID>./repdataThe HADR queue location.
vg_backupslv_backups<SID>./backupsDisk backup location.

The above will allow each disk partition usage type to be separately expanded, but more importantly, it allows specific Azure data disk cache settings to be applied to the right locations.
For instance, you can use read-write caching on the vg_ase volume group disks, because that location is only for storing binaries, text logs and config files for the ASE instance. The vg_asedata contains all the small ASE system databases, which will not use too much space, but could still benefit from read caching on the data disks.

TIP: Depending on the size of your database, you may decide to also separate the saptemp database into its own volume group. If you use HADR you may benefit from doing this.

You may not need the backups disk area if you are using a backup utility, but you may benefit from a scratch area of disk for system copies or emergency dumps.

You should choose a good naming standard for volume groups and logical volumes, because this will help you during the check phase, where you can script the checking of disk partitioning and cache settings.

Essentials:

  • Segregate disk partitions correctly.
  • Use a good naming standard for volume groups and LVs.
  • Remember the underlying cache settings on those affected disks.

Add Whole New ASE Devices

Follow the usual SAP ASE database practices of adding additional ASE data devices on additional file system partitions sapdata_2, sapdata_3 etc.
Do not be tempted to constantly (or automatically) expand the ASE device on sapdata_1 by adding new disks, you will find this difficult to maintain because striped logical volumes need at least 2 disks in the stripe set.
It will get complicated and is not easy to shrink back from this.

When you add new disks to an existing volume group and then expand an existing lv_sapdata<SID>_n logical volume, it is not as clean as adding a whole new logical volume (e.g. lv_sapdata<SID>_n+1) and then adding a whole new ASE data device.
The old problem of shrinking data devices is more easily solved by being able to drop a whole ASE device, instead of trying to shrink one.

NOTE: The Microsoft notes suggest enabling automatic DB expansion, but on Azure I don’t think it makes sense from a DB administration perspective.
Yes, by adding a new ASE device, as data ages you may end up with “hot” devices, but you can always move specific devices around and add more underlying disks and re-stripe etc. Keep the layout flexible.

Essentials:

  • Add new disks to new logical volumes (sapdata_n+1).
  • Add big whole new ASE devices to the new LVs.

Summary:

We’ve been through each of the layers in detail and now we can summarise as follows:

  • Choose a minimum of Premium SSD.
  • Spread database space requirements over multiple data disks.
  • Correctly set Azure VM disk cache settings on Azure data disks at the point of creation.
  • Use LVM to create volume groups and logical volumes.
  • Stipe the logical volumes with (max) 128KB stripe size & test it.
  • Choose disk file system wisely.
  • Disable write barriers.
  • Segregate disk partitions correctly.
  • Use a good naming standard for volume groups (and LVs).
  • Remember the underlying cache settings on those affected disks.
  • Add new disks to new logical volumes (sapdata_n).
  • Add big whole new ASE devices to the new LVs.

Useful Links:

You may also be interested in:

Fixing SAP PI Open Channel Monitoring with Host FQDN

In some SAP landscapes, DNS is extremely complex and can result in problems with hostname resolution unless the host has the domain name appended.

In this post I show an issue with SAP PI 7.1 channel monitoring, which is resolved by using the fully qualified hostname.
Finding how to get that fully qualified hostname set, took some crazy tracing ideas which I won’t go into (they are crazy but it worked).

The Problem

From within the SAP PI Integration Builder, you open a communication channel object and then from the menu select “Communication Channel -> Open Channel Monitoring“:

The channel monitoring web page is opened in your default web browser of the PC where you are running the Integration Builder.
Except the web page is opened with just the hostname of the adapter host. Due to the DNS configuration, you need it to use the fully qualified domain name instead.

Where does the Hostname Come from?

In this specific case, the adapter hostname is actually determined from the System Landscape Directory (SLD) that the Integration Builder uses.
This SLD is usually the SAP PI local SLD, but it could be a central PI SLD or even the central landscape SLD.
You can check in the PI Exchange Profile/Aii Properties for the SLD host.

Fixing the Issue

To fix this issue, you will need to adjust the SLD. Before we adjust the SLD, I need to explain that in a PI system, certain data in the SLD is updated at system start up (application start up) and this information is documented in SAP note 1435392:

During start up, certain data in the SLD could be reset from the source, which is usually the Exchange Profile/Aii properties.
In this specific case, the SLD data does not seem to be influenced by the adapter start or system start. So I have to conclude that it is set by the CTC during installation only.

Log into the Administration page of the SLD and go to the “CIM Instances” section:

Filter for class “HTTP Service Port” and add a text filter for “SOAP“:

You should see your adapter (the one where you are trying to get to the channel monitoring page), select it:

Select the “Properties” tab:

Change the “SecureURL” and “URL” properties to have the required FQDN and then click save:

Once saved, you can log out of the SLD administration page.

Then, back in the SAP PI Integration Builder, you need to clear the SLD Cache, select “Environment -> Clear SLD Data Cache“:

Finally, retry the “Open Channel Monitoring” and you should now see the fully qualified domain name being used.

Something you will notice, is that there are a lot of instances of class “HTTP Service Port” in the SLD.
You may find you can fix some other hostname related issues, but remember the key point about where certain data gets updated; because you may also need to ensure that the Exchange Profile/Aii properties are also updated.

You may also be interested in:

Dropping Empty SAP BW Table Partitions in SAP ASE

In a SAP BW 7.4 on SAP ASE database, table partitions are used as a method of storing data, primarily for query performance but also for object (table) management.

In this post I show a simple way to identify tables with many empty partitions, so that you can more quickly drop those empty partitions.
Less empty partitions reduces the downtime of database migrations, and can also increase the performance of business-as-usual queries.

Partitioning in SAP ASE

To use SAP BW on SAP ASE, the “partitioning” license needs to be bought/included in the database license.
The license is automatically included in the runtime license for ASE for SAP Business Suite.

SAP note 2187579 “SYB: Pros and cons of physical partitioning of fact tables” list all of the benefits and the options of partitions for ASE 15.7 and ASE 16.0.

During normal business usage, the database can use less effort to obtain data from a partitioned table, when the partition key column is used as a predicate in the query.
This is because the database knows exactly where the data is held.
It’s in its own table partition and is therefore more manageable.

A good analogy is to imagine that you have two holding pens, one with all cats, and one with all dogs. The partition key is “animal type” and each holding pen is a partition.
Both holding pens together make the table.
If we wanted to get all data from the table where the occupant was a cat, we simply go to the pen with all the cats and read the data.

Now imagine that we had 3 partitions that make up our table, but one of those partitions was actually empty.
In some cases, depending on the database settings, certain types of data queries will still scan for data in that empty partition.
These additional scans do not take a huge amount of time individually, but it does cause extra effort nevertheless.

If we upscale our scenario to a large multi-terabyte SAP BW system, and to a BW FACT table with thousands of partitions.
Imagine if we had thousands of empty partitions and we were accessing all records of the table (a full table scan), this would give a reasonable delay before the query could return the results.
For this exact reason, SAP provide a BW report.

The Standard SAP BW Report

The standard ABAP report SAP_DROP_EMPTY_FPARTITIONS is specifically for the FACT tables of a BW system and it is a recommendation in the ASE Best Practices document for this report to be run before a BW system migration/copy is performed.

By reducing the empty partitions, we also reduce the system export time. Easy winner.

The problem with the SAP standard report, is that you will need to go through each individual BW info-cube and execute the report in “show” mode.
This is really, really painfully slow.

A Better Way

Instead of the standard report, I decided to go straight to the DB layer and use SQL.
The example below is for SAP ASE 16.0 (should work on 15.7 also):

select distinct 
       convert(varchar(20),so.name) as tabname, 
       t_spc.numparts-1 as num_parts, 
       t_spn.numparts-1 as num_emptyparts 
from sysobjects so, 
     (select sp1.id, 
             count(sp1.partitionid) as numparts 
      from syspartitions sp1 
      where sp1.indid = 0 
      group by sp1.id 
     ) as t_spc,
     (select sp2.id, 
            count(sp2.partitionid) as numparts 
      from syspartitions sp2, 
           systabstats sts 
      where sp2.indid = 0 
        and sp2.partitionid = sts.partitionid
        and sts.indid = 0 
        and sts.rowcnt = 0 
      group by sp2.id 
     ) as t_spn 
where so.name like '/BIC/F%' 
  and so.id = t_spc.id 
  and so.id = t_spn.id 
  and so.loginame = 'SAPSR3' 
  and t_spn.numparts > 1 
order by t_spn.numparts asc, so.name

It’s fairly crude because it restricts the tables to those owned by SAPSR3 (change this if your schema/owner is different) and it is looking for FACT tables by their name (“/BIC/F*”) which may not be conclusive.

Below is an example output of the SQL versus the report SAP_DROP_EMPTY_FPARTITIONS in “show” mode:

You can see we are only 1 count out (I’ve corrected in the SQL now) but essentially we get a full list of the tables on which we can have the most impact!

Let’s look at a sample SELECT statement against that table:

We used the following SQL:

set statistics time on 
select count(*) from [SAPSR3./BIC/FZPSC0201]
go

Execution time on that statement was 25.9 seconds (elapsed time of 25931 ms).
We spent 2 seconds parsing and compiling the SQL statement (lots of partitions probably doesn’t help this either).
Since the CPU time is only 7 seconds, we have to assume that I/O was the reason for the delay while ASE scanned over the partitions.

Dropping The Partitions

Let’s go ahead and actually drop those empty partitions using another standard ABAP report SAP_DROP_EMPTY_FPARTITIONS.

NOTE: See SAP note 1974523 “SYB: Adaption of SAP_DROP_EMPTY_FPARTITIONS for SAP ASE” for more details on how to use the report.


We need to run this in the background, because dropping over 1,000 partitions will take a while.

Once dropped, we can re-run our select statement:

Total elapsed time is now down to just 6 seconds.
Admittedly there could be some time saving due to the data cache and plan cache already being populated for this table, so I ran ASE stored procedure: sp_unbindcache, which seemed to have done something.
Then I re-ran the query:

Being unsure if the unbind worked or not (I could not bounce the DB to be absolutely sure), I’m going to just accept that we have improved the result by dropping those empty partitions.

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.

You may also be interested in: