Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Thursday, April 28, 2016

Dynamic SSH X-11 Forwarding on RHEL Linux

Sometimes you need to use an X-client in order to perform certain operations with an SAP system.
An example would be using SUM (if the firewall ports are blocked) or SWPM to perform an installation, or if you're running an AS Java system, the offline editor (configtool).

If you're already using PuTTY to get into the remote system via SSH, then you are already part way there.

In this post, I will show you how to dynamically adjust the PuTTY and Linux configuration so that you can use MobaXterm (or any other X-client) to connect into the Linux using X11 (X-Windows).
This is not the same as using the standard method of ticking the X-11 forwarding option, because this option must be ticked prior to establishing the connection into the server.

Instead of exiting from your session and re-connection, you can simply use standard port forwarding to enable your X-client connection over SSL.

Step 1 – Connect into Linux using PuTTY.

You should ideally already be connected into the remote system using SSH and PuTTY (that is the aim of this post).
If you're not already connected, then open your PuTTY session as normal.

Step 2 – Open X-Client on your PC/virtual server.

Open your X-Client on your local PC.  Ideally it will default to display 0.
The "0" simply refers to a subset of a port range.  It's a virtual display.

In our example, we used the free MobaXterm tool to provide X-client capability.  Mainly because the tool is free and integrated into one nice binary that can be placed on a USB stick for easy access.

In MobaXterm, hover the mouse over the "X" in to top right frame of the window.
It should be green, and if so, it will display the current IP and display port it has been configured with.

However, you can use any other X-Client tool (XManager, Exceed, X-Ming, WRQ  etc).

Step 3 – Adjust Linux SSH configuration.

The Linux SSH configuration may not be setup to enable X11 forwarding.
This is usually performed by the Linux administrator at a global level in the ssh daemon configuration file.
However, it is possible to adjust the config for individual users only.

As your Linux user (in an SAP system this would usually be the <sid>adm user account), enable the X-11 forwarding:
echo "ForwardX11 yes" > $HOME/.ssh/config
The above command simply enables the current user to forward X11 connections over the SSH connection.
It puts this config into a file called "config" within the .ssh directory of the current Linux user's home directory.

Step 4 – Setup the port forwarding.

Since this tutorial is going to show the setup of X11 dynamically, with an already established SSH connection, from within your already established PuTTY session, select the Windows window menu from the top left of the PuTTY window and select "Change Settings…":


Expand the "Connection -> SSH -> Tunnels" settings area on the left hand tree menu:
(Notice there is no option to enable or disable the usual X-11 forwarding option)

On the right hand side, now add port 6000 as source and loclahost:6000 as destination, then select "Remote" and click "Add":

Click Apply:

What is the above doing?
It is telling PuTTY to establish a listening port on the Linux server, listening on port 6000 (the port for display "0.0").

The port is "remote" because it is remote to the PuTTY session (i.e. not on the computer where you are running PuTTY).

We're telling PuTTY to only consider IPv4 because we don't use IPv6 and have no interest in having it listen for an IPv6 connection.

The above setup will have now established a port forward over the SSH port.
If you were to run "netstat -an | grep 6000" on the Linux server, you would see one port in status LISTEN.

Now the port forwarding is established, all we need to do is configure our Linux session to make use of the display setting.

Step 5 – Configure DISPLAY

In your Linux session, set the DISPLAY variable appropriately.
In a C-shell you would use:
>  setenv DISPLAY localhost:0.0
In a Bash, Bourne or Korn shell you would use:
$  export DISPLAY=localhost:0.0
From within the Linux session you should now be able to run an X11 application and see the window on your PC/virtual server.
You can use the standard "xeyes" or "xclock" to perform the test, however sometimes on Linux installs these do not get installed.
I've found that it's generally possible to call "firefox" or "totem" to perform an X11 test.

Alternatively, just call your intended SAP X11 application as discussed at the beginning.

That's it.

Thursday, April 21, 2016

Convert SAP AS Java Trace Timestamp

Sometimes, just sometimes, you wish you could just take a look at the defaulttrace.log or one of the other many log files at the O/S level for an SAP AS Java based system.
Except, it’s not that simple.  Whilst the file is text based, you’re not able to read the date/time stamp that exists on each line.
Well, here’s a simple couple of methods to do this conversion, which is basically converting from the epoch.
The first method is using a pure Korn Shell (KSH) method, useful for converting maybe one or two values:
echo $(date -d "1970-01-01 ${dt:0:10} seconds")
This second method uses AWK which makes it much better at converting all values by simpling piping in the original file to see the output:

NOTE: Below I’m searching for entries related to “”.
NOTE 2: The below command is all on one line.
cat responses.0.trc | awk -F# '/  { print strftime("%F %T",substr($4,1,10))"#"$24 }'

Thursday, April 14, 2016

SAP ASE - Blocking Factor Madness

I spent around a day looking into a performance issue with a specific peice of SQL in an ERP 6 system running on a SAP ASE database.
The system has recently been migrated from Oracle, so we were expecting issues with hints, however this didn’t seem to be an index choice issue.
Look at the following two SQL statements, the first one is the system experiencing a performance problem:


The second picture is a system where the performance issue doesn’t exist:


Can you spot the difference?
Hint: Look at the number of question marks in the prepared statement.
The number of question marks indicate the number of items included in the “IN LIST” of the WHERE clause.

Since the ABAP SQL statement will be interpreted at the Kernel level, there is no way to see any difference in the ABAP layer other than this output from a SQL trace.
The consequence of the first statement (with fewer question marks) are that the SQL statement is executed multiple times in order to query for the same “PERNR” records.  This can result in as much as 4 to five times more effort for the SAP layer, plus the database layer.  Which adds more to the database response time and a little to the “processing time”.

What impacts the number of question marks?  Simple, the parameters “rsdb/*blocking_factor” at the Kernel level, will adjust how many parameters are fed into the prepared statement in the DBSL layer.

SAP Note 1996340 - SYB: Default RSDB profile parameters for SAP ASE  will provide all the answers.
The SAP note also answered my specific issue, which was why was one system in the landscape different.  The answer was that the production system (where the problem was seen) had it’s parameters mostly carried across during a migration from Oracle.  Whereas a smaller “release” system had it’s parameters left behind to die with the Oracle database.

As you will read in the SAP note 1996340, these rsdb parameters are pretty essential and should be re-evaluated when changing database platform.

Always re-evaluate all parameters when migrating from one platform to another.
Don’t assume that someone more experienced has set them with some future knowledge of the landscape/setup.

Thursday, April 07, 2016

SAP ASE SQL Script - Who Has Allow HADR Login

Whilst working with SAP ASE, I found it useful to have a ready made SQL statement for determining who (which user accounts) has “allow hadr login” privileges.
In my scenario, I needed to be able to check if a specific backup server login account had the “allow hadr login” privilege, to permit backups of a standby SAP SRS replicated database.
Normally you are unable to login as any “normal” user into the standby database without the “allow hadr login” privilege.
use master
select p.uid,,CONVERT(char(20),
from sysprotects p,
     master.dbo.spt_values v,
     sysusers u
where p.action=v.number
  and'Allow Hadr Login'
  and p.uid = u.uid