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

HowTo: Extract SAP PI/PO Message Payload from SAP ASE DB

Sometimes you may need to directly the extract the SAP PO message payload from the underlying database tables such as BC_MSG_LOG in SAP ASE 16.0 database.
This could also potentially be called: extracting hex encoded ASCII data from an ASE image column. Because the SAP PO tables use an ASE image data type to store the payload as an off-row LOB.

There are plenty of examples for doing this extraction in Oracle, but in ASE it is not so easy because the message size could be larger than that supported by the page size of ASE (usually 16k in an ASE for BusSuite).
This means you won’t be able to store it into a T-SQL variable and use the ASE functions.

Instead, we can use the below simple approach to extract the raw hex, and then use Python 2 to convert it to ASCII:

1, Execute the selection SQL using isql at the Linux command prompt on the database server:

isql -USAPSR3DB -S<SID> -w999 -X

select MSG_BYTES
from [SAPSR3DB.BC_MSG_LOG]
where MSG_ID='<YOUR MSG ID>'
and DIRECTION='OUTBOUND'
and LOG_LOCATION='MS'

go

The output will consist of hexadecimal output, which starts with “0x” and should look something like this:

0x2d2d5341505f6

Copy & paste into a text file on the Linux server (use your favourite text editor) and call the file data.txt.

Edit the data.txt file and remove the first “0x” characters from the data.
Remove all newlines and carriage returns in the file.

Now create a simple Python script to read the data from our file data.txt and translate from hex to ASCII then print to the screen:

with open('data.txt', 'r') as file:
    data = file.read()
print data.decode('hex')

Run the Python script:

python ./myscript.py

The output should contain a header and a footer which start with:  “–SAP_”.
If you get an error from the Python script, then it could be because there are additional newlines or carriage returns in the data.txt file.

When SLES for SAP is not SLES for SAP

I recently downloaded and installed “SUSE Enterprise Linux for SAP 12 SP3” into a local virtual machine.
It seemed to contain everything that I thought it would contain with regards to included SAP Linux packages.

Noteable were the following in my local VM:

# which saptune
/usr/sbin/saptune
# rpm -qa | grep sap
cyrus-sasl-gssapi-32bit-2.1.26-7.1.x86_64
sap-netscape-link-0.1-1.2.noarch
sap-installation-wizard-3.1.81-3.1.x86_64
yast2-sap-scp-1.0.3-11.2.noarch
saptune-1.1.3-1.1.x86_64
saprouter-systemd-0.2-1.1.noarch
cyrus-sasl-gssapi-2.1.26-7.1.x86_64
patterns-sles-sap_server-12-77.8.x86_64
patterns-sles-sap_server-32bit-12-77.8.x86_64
yast2-saptune-1.2-1.5.noarch
sap-locale-32bit-1.0-92.4.x86_64
sapconf-4.1.8-1.18.noarch
sap-locale-1.0-92.4.x86_64
yast2-sap-scp-prodlist-1.0.2-4.2.noarch
# cat /etc/os-release
NAME=”SLES”
VERSION=”12-SP3″
VERSION_ID=”12.3″
PRETTY_NAME=”SUSE Linux Enterprise Server 12 SP3″
ID=”sles”
ANSI_COLOR=”0;32″
CPE_NAME=”cpe:/o:suse:sles_sap:12:sp3″
# uname -a
Linux hana01 4.4.73-7-default #1 SMP Fri Jul 21 13:26:40 UTC 2017 (6beeafd) x86_64 x86_64 x86_64 GNU/Linux

All looks good to me.

I then created an Azure hosted virtual machine using the image “SLES for SAP 12 SP3 (BYOS)”:

 

The Azure VM seems to be missing a lot of the packages that I would expect to be in place:

# which saptune
which: no saptune in (/sbin:/usr/sbin:/usr/local/sbin:/root/bin:/usr/local/bin:/usr/bin:/bin:/usr/games:/usr/lib/mit/bin)
# rpm -qa | grep sap
patterns-sles-sap_server-12-77.8.x86_64
yast2-sap-scp-prodlist-1.0.2-4.2.noarch
yast2-sap-scp-1.0.3-11.2.noarch
cyrus-sasl-gssapi-2.1.26-7.1.x86_64
sapconf-4.1.10-40.37.1.noarch
# cat /etc/os-release
NAME=”SLES”
VERSION=”12-SP3″
VERSION_ID=”12.3″
PRETTY_NAME=”SUSE Linux Enterprise Server 12 SP3″
ID=”sles”
ANSI_COLOR=”0;32″
CPE_NAME=”cpe:/o:suse:sles_sap:12:sp3″
# uname -a
Linux hana01 4.4.82-6.3-default #1 SMP Mon Aug 14 14:14:02 UTC 2017 (4c72484) x86_64 x86_64 x86_64 GNU/Linux

Notice also that the Kernel release is slightly newer on the Azure image, plus the version of the sapconf package is slightly newer.
The most important point is that the Azure image is missing the saptune package.
This is important as it is a method presented in numerous SAP notes for automatically applying the recommended O/S settings (that’s right, they don’t all get applied out-of-the-box).