Using Google Chrome?

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

Thursday, December 08, 2016

HowTo: Delete Multiple Workmodes ABAP - Solution Manager 7.1

Scenario: You’ve setup lots of work modes and assigned them to lots of SAP systems and lots of databases.
However, you now would like to remove them all.

Unfortunately the only method provided by SAP to remove workmodes, is very slow as you must manually go through each SAP system and then each database and remove the workmodes.  This is very time consuming.

Instead, it’s possible to do this using the ABAP API provided by SAP.
I’ve provided below a sample of the ABAP I have used to remove all workmodes.
I created it to remove all workmodes from all SAP systems and all databases.
Below we make a call to get the workmodes assign to all SAP systems:

        IV_DOWNTIMES           = 'X'
        IV_UPTIMES             = 'X'
        IV_COMPONENT_KEY       = 'SID'
        IV_COMPONENT_TYPE      = 'SMSY'
        IV_IS_DB               = '-'
        EV_ERROR               = lv_error
        ET_ENTRIES             = lt_entries
We then call the function in a loop (looping on lt_entries) to delete the workmode assignment:

              IV_WORKMODE_ENTRY_GUID = ls_guid
              EV_DELETED = lv_error.
WRITE: lv_error.

Thursday, December 01, 2016

Solman 7.1 LMDB vs SMSY Row Count Mismatch

Scenario: From transaction LMDB in Solman 7.1, you can run variaous validation checks against the LMDB.
One of these validates the number of logical components in the LMDB versus the old SMSY.

When I ran this validation check, there was a slight mismatch, more rows were found in the LMDB compared to the SMSY.  But no further information was provided.

So, to be able to work out which logical component was missing, I had to manually compare the table content of table SMSY_SYSTEM and table LMDB_LC_HD.
From this I found the logical component that I needed to remove from SMSY and I then removed it using SMSY.

Thursday, November 24, 2016

SAP Change Non-Dialog User to Dialog User Using Function Module

Scenario: The DDIC account is locked in a SAP system.
You don’t know the password to DDIC, and to get access as SAP* you would need to bounce the system, which is not acceptable during this period of business usage.
You’ve got the password to an existing account with profile SAP_ALL, but the account is a non-dialog user (system user or communication user).
You’ve got access to a development system where you can create SM59 RFC connections,
You would like to gain access to unlock and reset the DDIC account, without bouncing the system.

What we are doing in the below process, is changing the non-dialog user account, to become a dialog user account.  We can then use the dialog account to logon to the SAP system and then unlock and reset the DDIC user account.

1, Log into the development SAP system as a user account that has SM59 access.
Create a new RFC connection to the destination SAP system (where DDIC is locked) set the authentication to use the non-dialog user account that has SAP_ALL.

2, In transaction SE37, from the menu select "Function Module -> Test -> Test Sequences".

3, Set two function modules to execute, BAPI_USER_CHANGE followed by BAPI_TRANSACTION_COMMIT.

4, Execute function module BAPI_USER_CHANGE for the non-dialog user account, with "LOGONDATA" field "U" changed to value "A" (dialog user).
Set the destination to be the RFC destination you created.

5, Then execute function module BAPI_TRANSACTION_COMMIT.
Set the destination to be the RFC destination you created.

6, You can now log onto the target SAP system as the non-dialog user account (which is now a normal dialog user account).

7, You can now unlock the DDIC user and change the password.
Once completed, reset the non-dialog account back to be a non-dialog account.

As you can see, this is very easy to do.

To mitigate against this security threat:
- You should also look to prevent giving SAP_ALL to any SAP user accounts, even if they are non-dialog.
- Finally, you can also configure the RFC Access Control Lists (ACLs) to permit calls to specific function modules only.

Thursday, November 17, 2016

SAP Secondary Oracle DB Connection–EasyConnect

When you run an SAP system on a non-Oracle database platform, you may sometimes need to connect to a secondary Oracle database (for example, in a SAP BW environment you could need a connection to multiple source database systems).

The process that is usually followed, is to create the TNSNAMES.ora in the appropriate location on *every* SAP application server of the SAP system.  Then put the TNS service name and username/password into the DBCO transaction within SAP.

There are a couple of downsides to this approach:

1, You generally have to put the TNSNAMES.ora file in /sapmnt/<SID>  as this is already shared across the SAP system’s application servers.

2, You have to keep the TNSNAMES.ora file updated.  Any changes require a complete restart of the SAP system in order for the file to be re-read.

This is where the Easy Connect string can be used.
Instead of entering the TNS service name into the DBCO transaction, you simply enter all the service details, removing the need for the TNSNAMES.ora file.

An example of the Easy Connect string is:
We are supposing that:

- “tns-service” is the TNS service name for your target database (listened for on the target Oracle listener)
- Port 1521 (default port) is used by the listener.
- The server on which the listener is located is

You must include the double quotes in the DBCO entry.

Based on the above entry, you can then dynamically change the value as and when needed.
No need for a restart of the SAP application server.

Reference: SAP note: 808505 - Secondary connections to Oracle database

Power Notes Searcher Updated to v2.1

The free Google Chrome extension Power Notes Searcher, has been updated!
A small bug fix has been released which fixes a problem with the highlighting of note numbers in the SAP note pages.

The update also includes a few more direct links in the “Options” area, for things like SSCR keys and incidents.

Saturday, July 16, 2016

Power Notes Searcher Updated to v2.0

I’ve finally managed to update the Power Notes Searcher to v2.0, which now supports the new SAP ONE support portal.
This is my free Google Chrome Extension to make life easier if you have to wade through a lot of SAP notes on a regular basis.  This is my tried and trusted tool for 3 years now and it was time to provide an update.

Whilst the main features of Power Notes Searcher remain the same, a few notable features are:

- Highlighted note numbers can now be double clicked to open the note, even if it’s not an actual HTML link.
- Increased the history size to 150 notes.
-  Smaller code base due to the use of the Google code compiler for JavaScript.
- Note content searches now highlight the note numbers without needing to initiate the search through the Power Notes Searcher popup.
- Collapsable settings area, simplifies the screen layout and increases viewing area for the history table.

I’ve also installed some basics into the code to provide for a future “tag” feature to allow notes to be tagged and organised in the history table.

Thursday, May 19, 2016

What is: SAP AS Java Flight Recorder

In Solution Manager 7.1 "Managed System Configuration" step 7, you are requested to manually apply an instance profile parameter (jstartup/recorder) that configures the "Flight Recorder":

The help for the item shows the following:


But the help doesn't tell you what the Flight Recorder does and when you might use it.
So it was time to do a little research.

What does the flight recorder do?

Simply put, the flight recorder starts up only when a SAP Java server process (JVM) crashes (e.g. server0 crashes).
The flight recorder's job is to gather up any available logs and traces and zip them up, before the system restarts the process.

Where are the dump zip files?

The zip files are stored by default in DIR_GLOBAL/dumps (e.g. /sapmnt/<SID>/global/dumps).
Typically the zip file is about 1-2MB in size and can contain around 1250 files.

What is included in a dump zip?

If you unzip a dump zip file, you will notice you get 3 different types of files extracted.
1- File system properties and config files, that were present on the file system at the time of the dump.
2- Database properties and config, that were stored in the database at the time of the dump.
3- Log files and traces, that were stored on the file system at the time of the dump.

Apart from looking in the DIR_GLOBAL/dumps area, trace or logs files, how can I see these dump zip files?

When you log into the System Info page of the Java server, you will see on the right hand side "Info for crashes of this node":



How can we trigger a dump to see if this is all true?

Simple, we can just kill the server0 process like this:

sidadm> ps -ef | grep server0

sidadm> kill 12345         <--  This is the PID output from the previous command.
You will not really see the flight recorder process active, it's very quick.
You will see the dump generated in the dumps folder.

Hopefully now, you can make use of the flight recorder.

Thursday, May 12, 2016

HowTo: Shrink SAP ASE Transaction Log

Providing that you’ve understood that with SAP ASE, you cannot shrink the device size, then you may be looking to shrink the size of a transaction log or datafile within a device.
From isql, list out the segments of the transaction log using “sp_helpdb <dbid>” as follows (I’ve cleaned up the output a little):
1> sp_helpdb saptools
2> go

         owner                dbid

             4628.0 MB
         sapsa                   5
         Jul 20, 2015
         trunc log on chkpt, ddl in tran, allow nulls by default, abort tran on
         log full, allow wide dol rows, allow incremental dumps,full logging for all

(1 row affected)

device_fragments               size          usage
         created                   free kbytes
------------------------------ ------------- --------------------
         ------------------------- ----------------
saptools_data_001                  2048.0 MB data only
         Jul 20 2015  6:50PM                1626832
saptools_log_001                    204.0 MB log only
         Jul 20 2015  6:50PM       not applicable
saptools_log_001                    308.0 MB log only
         Oct 26 2015 10:16AM       not applicable
saptools_data_001                  2048.0 MB data only
         Nov 10 2015 11:51AM                2088960
saptools_data_001                    20.0 MB data only
         Nov 10 2015 11:53AM                  20400

log only free kbytes = 522128                       

(return status = 0)
You can see that the saptools_log_001 has been extended in the past as there are multiple lines under “device fragments” for the saptools_log_001 device.
There are two segments, one is 204MB and the other 308MB.
We can remove one of these segments to free up space within the device saptools_log_001 (remember this will not return the space to the operating system, you can’t do that in ASE).
Since this is a transaction log, we will need to ensure that the segment we are removing is no longer used for redo information.
In our case we are happy to simply truncate, but you may wish to actually dump the transaction log to your backup tool or to disk.
1> dump tran saptools with truncate_only
2> go
NOTE: You may need to do the above multiple times before the log segments become free.
Once cleared, we can then tell the saptools database to remove the 308MB segment, by specifying the exact size of the segment we would like to remove.
NOTE: You cannot just choose a segment, you must start with the last segment and work backwards else you will create “holes” in your device.
1> alter database saptools log off saptools_log_001 = 308
2> go

Removing 19712 pages (308.0 MB) from disk 'saptools_log_001' in database 'saptools'.

Processed 571 allocation unit(s) out of 640 units (allocation page 145920). 89%


Processed 635 allocation unit(s) out of 640 units (allocation page 162304). 99%

Now let’s check:
1> sp_helpdb saptools
2> go

         owner                dbid

             4320.0 MB
         sapsa                   5
         Jul 20, 2015
         trunc log on chkpt, ddl in tran, allow nulls by default, abort tran on
         log full, allow wide dol rows, allow incremental dumps,full logging for all

(1 row affected)

device_fragments               size          usage
         created                   free kbytes
------------------------------ ------------- --------------------
         ------------------------- ----------------
saptools_data_001                  2048.0 MB data only
         Jul 20 2015  6:50PM                1626832
saptools_log_001                    204.0 MB log only
         Jul 20 2015  6:50PM       not applicable
saptools_data_001                  2048.0 MB data only
         Nov 10 2015 11:51AM                2088960
saptools_data_001                    20.0 MB data only
         Nov 10 2015 11:53AM                  20400

log only free kbytes = 207968, log only unavailable kbytes = 315392

(return status = 0)
Only one segment for the saptools_log_001 device remains.
We have increased the “log only unavailable kbytes” as the space is still used by the device, but is there for us to expand into again if we wish.
NOTE: The saptools_log_001 is a “log only” device.  So we cannot use that new free space for a data segment expansion.  Only for log expansion.

Thursday, May 05, 2016

Manual Explain Plan in SAP ASE

When running SAP on top of SAP ASE database, you can use the DBACOCKPIT transaction to help produce an EXPLAIN PLAN (execution plan).
It’s also possible to use the “EXPLAIN” button from an SQL trace (from ST05 or ST12).
However, under certain circumstances it may not be possible (or it just may not function – it’s Java based) and you may want to use command line iSQL to generate the EXPLAIN PLAN output at the database level directly.
Here’s how.
From the SQL trace (ST05 or ST01) in the SAP GUI, you can double click to obtain the SQL text, which will usually be a prepared statement (contain ‘?’ in place of actual data).
It may look like the following (sorry for the large statement):
    "MANDT" ,"PERNR" ,"SUBTY" ,"OBJPS" ,"SPRPS" ,"ENDDA" ,"BEGDA" ,"SEQNR" , "AEDTM" ,"UNAME" ,"HISTO" ,"ITXEX" ,"REFEX" ,"ORDEX" ,"ITBLD" ,"PREAS" , "FLAG1" ,"FLAG2" ,"FLAG3" ,"FLAG4" ,"RESE1" ,"RESE2" ,"GRPVL" ,"ANSSA" ,   "NAME2" ,"STRAS" ,"ORT01" ,"ORT02" ,"PSTLZ" ,"LAND1" ,"TELNR" ,"ENTKM" , "WKWNG" ,"BUSRT" ,"LOCAT" ,"ADR03" ,"ADR04" ,"STATE" ,"HSNMR" ,"POSTA" , "BLDNG" ,"FLOOR" ,"STRDS" ,"ENTK2" ,"COM01" ,"NUM01" ,"COM02" ,"NUM02" , "COM03" ,"NUM03" ,"COM04" ,"NUM04" ,"COM05" ,"NUM05" ,"COM06" ,"NUM06" , "INDRL" ,"COUNC" ,"RCTVC" ,"OR2KK" ,"CONKK" ,"OR1KK" ,"RAILW"
    "MANDT" = ? AND "PERNR" IN ( ? , ? , ? , ? , ? )  /* R3:SAPDBPNP:11498 T:PA0006 M:100 */  /*unc. rd.*/
A0(CH,3)  = 100
A1(NU,8)  = 00000001
A2(NU,8)  = 00000002
A3(NU,8)  = 00000003
A4(NU,8)  = 00000004
A5(NU,8)  = 00000005
The text after the first “/*” is comment text added by the SAP DBSL layer to indicate the calling module and line number, as well as the client and table against which it is executing.
All you need to do to use this SQL directly at the DB level is to populate the ‘?’ with the actual data which you can see in the variable list at the bottom left.
The variables run in order, left to right.
Therefore, A1 will be the first ‘?’ in the SQL statement.
You will also see from the variable list that the ABAP data type is included.
CH = CHARacter and NU = Number.
All you need to know, is that SAP rarely uses the underlying database data type, ensuring the agnostic nature of the OPEN SQL.
This means you enclose all of the variables in quotes as though they were characters.
Based on the example above, removing the comment area, you should then have the following SQL statement:
    "MANDT" ,"PERNR" ,"SUBTY" ,"OBJPS" ,"SPRPS" ,"ENDDA" ,"BEGDA" ,"SEQNR" , "AEDTM" ,"UNAME" ,"HISTO" ,"ITXEX" ,"REFEX" ,"ORDEX" ,"ITBLD" ,"PREAS" , "FLAG1" ,"FLAG2" ,"FLAG3" ,"FLAG4" ,"RESE1" ,"RESE2" ,"GRPVL" ,"ANSSA" , "NAME2" ,"STRAS" ,"ORT01" ,"ORT02" ,"PSTLZ" ,"LAND1" ,"TELNR" ,"ENTKM" , "WKWNG" ,"BUSRT" ,"LOCAT" ,"ADR03" ,"ADR04" ,"STATE" ,"HSNMR" ,"POSTA" , "BLDNG" ,"FLOOR" ,"STRDS" ,"ENTK2" ,"COM01" ,"NUM01" ,"COM02" ,"NUM02" , "COM03" ,"NUM03" ,"COM04" ,"NUM04" ,"COM05" ,"NUM05" ,"COM06" ,"NUM06" , "INDRL" ,"COUNC" ,"RCTVC" ,"OR2KK" ,"CONKK" ,"OR1KK" ,"RAILW"
    "MANDT" = ‘100’ AND "PERNR" IN (‘00000001’, ‘00000002’ , ‘00000003’ , ‘00000004’ , ‘00000005’ )
Now to run it at the database level.
Log onto the database server as either the syb<sid> or <sid>adm (I’m using Unix/Linux) user (both usually have the isql binary in their path).
Once logged on, run isql and connect into the <SID> database as the SAPSR3 (or SAPSR3DB on Java stacks) user:
isql –USAPSR3 –S<SID> -D<SID> -W999 –X
Now you’re connected, we need to set some session settings so that we get the explain plan output.
NOTE: The “use” is optional as we’ve already specified the DB we want to use with the “-D” parameter at the isql command line.
use <SID>
We want to show the resultant execution (EXPLAIN) plan.
set showplan on
We would like some additional useful details from the EXPLAIN plan.
set statistics io on
set statistics time on
set statistics plancost on
We don’t want any of our SQL to be cached, just incase we want to change it and the system decides to try and use the most efficient one.
set statement_cache off
We make life easier by enabling the use of quotes, since SAP has already put them there for us.
set quoted_identifier on
Finally, we paste the SQL itself, followed by the ASE SQL command terminator “go”:
    "MANDT" ,"PERNR" ,"SUBTY" ,"OBJPS" ,"SPRPS" ,"ENDDA" ,"BEGDA" ,"SEQNR" , "AEDTM" ,"UNAME" ,"HISTO" ,"ITXEX" ,"REFEX" ,"ORDEX" ,"ITBLD" ,"PREAS" , "FLAG1" ,"FLAG2" ,"FLAG3" ,"FLAG4" ,"RESE1" ,"RESE2" ,"GRPVL" ,"ANSSA" , "NAME2" ,"STRAS" ,"ORT01" ,"ORT02" ,"PSTLZ" ,"LAND1" ,"TELNR" ,"ENTKM" , "WKWNG" ,"BUSRT" ,"LOCAT" ,"ADR03" ,"ADR04" ,"STATE" ,"HSNMR" ,"POSTA" , "BLDNG" ,"FLOOR" ,"STRDS" ,"ENTK2" ,"COM01" ,"NUM01" ,"COM02" ,"NUM02" , "COM03" ,"NUM03" ,"COM04" ,"NUM04" ,"COM05" ,"NUM05" ,"COM06" ,"NUM06" , "INDRL" ,"COUNC" ,"RCTVC" ,"OR2KK" ,"CONKK" ,"OR1KK" ,"RAILW"
    "MANDT" = ‘100’ AND "PERNR" IN (‘00000001’, ‘00000002’ , ‘00000003’ , ‘00000004’ , ‘00000005’ )
Here’s the sample output plan:
================ Lava Operator Tree ================
                        (VA = 3)
                        r:36 er:39
                        cpu: 0
            Inner Join
            (VA = 2)
            r:36 er:39
            l:0 el:31
            p:0 ep:25
/                      \
OrScan                  IndexScan
Max Rows: 5             PA0006~0
(VA = 0)                (VA = 1)
r:5 er:-1               r:36 er:39
l:0 el:-1               l:35 el:31
p:0 ep:-1               p:1 ep:25

Table: PA0006 scan count 5, logical reads: (regular=35 apf=0 total=35), physical
reads: (regular=1 apf=0 total=1), apf IOs used=0
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 10 ms.  Adaptive Server elapsed time: 24 ms.

(36 rows affected)
Reading the plan from the lowest “VA” value, we start with an OrScan (breaks down the 5 “IN” list values we passed into the query, into an SQL “OR” statement).
The OrScan is returning 5 rows (r:5) and performed zero logical reads (l:0) and zero physical reads (p:0).
There were no estimated logical or physical reads (el & ep) due to the type of operation.
We were then using an index (IndexScan) of PA0006~0 (a primary key on table PA0006).  Of which we estimated that we would return 39 rows (er:39), based on statistics (I would hope), but we actually returned 36 (r:36).
We performed 35 logical reads and 1 physical read on the index (I believe that logical reads encompass physical reads, just like Oracle).
Which was better than we anticipated with the estimated logical and physical values of 31 and 25 respectively.
Both the OrScan and the IndexScan are accessed from the parent NestLoopJoin(VA = 2).
We return 36 rows (r:36) upto the EMIT for return back to the client (isql in this case, but it would normally be our SAP dialog work process).
The summary at the very bottom of the SQL output shows a nice set of easy to interpret values.
Because our query was broken into an “OR” statement like this:
SELECT column1
  FROM table1
WHERE column1 = value1
      OR   column1 = value2
      OR   column1 = value3
      OR   column1 = value4
      OR   column1  = value5
It means that the index was scanned 5 times against the matching key column (scan count 5).
The sum of the logical reads and physical reads is shown (APF reads – Asynchronous Pre-Fetch, are reads to/from the data cache in a hopeful way).
We then have visibility of the actual SQL execution time plus the required CPU time and the overall elapsed time.
Additional information can be found on the Sybase infocenter site, although I feel it’s lifetime is limited.

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

Thursday, March 31, 2016

SAP ASE Job Server Error

Whilst administering a SAP ASE based SAP system, I came across an issue in the ASE Job server error log “JSTASK.log”:
00:140737306879744:140737340581728:2016/02/24 16:50:00.87 worker  ct_connect() failed.
00:140737306879744:140737340581728:2016/02/24 16:50:00.87 worker  jsj__RunSQLJob: jsd_MakeConnection() failed for user sapsa to server SID
00:140737306879744:140737340581728:2016/02/24 16:50:00.87 worker  jsj__RunSQLJob() failed for xid 66430
00:140737317369600:140737340581728:2016/02/24 16:55:00.87 worker  Client message: ct_connect(): protocol specific layer: external error: The attempt to connect to the server failed.
The issue was caused by a change of the sapsa user password whereby the SAP recommended method of using the hostctrl process, wasn’t followed.
The recommended method updates the sapsa user, the secure storage file plus also the external login for the Job Server.
This is mentioned at the very end of SAP note 1706410 (although it is suggested that the process in this note is no longer followed to change the passwords).
To fix the issue, follow finals steps in the SAP note 1706410:
isql -X -Usapsa -S<SID> -w999

use master

Server                 Login                Externlogin
---------------------- -------------------- ------------
SYB_JSTASK             sapsa                sapsa
Drop the SYB_JSTASK entry:
exec sp_dropexternlogin SYB_JSTASK, sapsa

Re-create it with the new password:
exec sp_addexternlogin SYB_JSTASK, sapsa, sapsa, '<new sapsa password>'
This should fix the issue.

Thursday, March 24, 2016

SAP ASE–login to Secondary HADR DB as sapsso

When running SAP ASE in HADR mode with SAP SRS, you are not able to log into the secondary database as the sapsso database user.

This is because the user is redirected to the primary database in the HADR pair.

To get around this, you must grant the ASE privilege ‘allow hadr login’ to the sso_role as follows:
isql -Usapsa –S<SID> -w999 -X

1> use master
2> go

1> grant allow hadr login to sso_role
2> go
Once you’re done doing what you needed to do as the sapsso user on the secondary database, then you can simply revoke the privilege as follows:
isql -Usapsa –S<SID> -w999 -X

1> use master
2> go

1> revoke allow hadr login from sso_role
2> go

Thursday, March 17, 2016

SAP ASE with SAP Replication Server - Node ID

While working on a SAP ASE database configured with SAP Replication Server (SRS) as part of a HADR pair, you may want to know how to identify which of the two databases in the HADR pair, you’re working on.

The only sure method I found is to query the @@nodeid global variable.
This nodeid value is different for each of the ASE databases.
Therefore if you’re logged onto the primary node, you will see a different ID.

I found this to be exceptionally useful when logging into a database as sapsso, since this user is redirected to the primary database in a HADR setup.

UPDATE: July 2016, I've since found this little gem:

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

1> select ASEHOSTNAME()
2> go


(1 row affected)

Thursday, March 10, 2016

SAP ASE Backup Server Error Writing to Archive

When running SAP Business Suite on the SAP ASE database platform, I was trying to dump and load from one database to another database.
A backup server error was seen on the target database and in the backup server log file (<SID>_BS.log) during the LOAD statement execution:
Backup Server: [3] Error for database/archive device while working on stripe device '/<file1>'. Error writing to archive device /<file1>. Attempted to write 65536 bytes, 32768 bytes were written.
This specific issue turned out to be caused by my target DB not having exactly the same size for the data or log devices.

I even found in some cases that the log device needed to be a tiny little bit bigger (we’re talking about 1MB bigger) than the source database.

Thursday, March 03, 2016

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.

Thursday, February 25, 2016

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

Saturday, February 06, 2016

SAP SLD Change Log Cleanup Table Reorg

When you apply changes or content updates to the SLD (system landscape directory) in SAP, the change log grows.
It's possible to see the change log entries from the SLD administrator page.
Due to this growth, the underlying database table will benefit from reorganisation at some point.
In our scenario, this was on DB2.
We decided to run the cleanup, but first here was the size of the table BC_SLD_CHANGELOG:
TABNAME                       NPAGES
---------------------------- --------------------
BC_SLD_CHANGELOG              14162
BC_SLD_INST                   12830
J2EE_CONFIGENTRY              5870
BC_SLD_ASSINST                3905
EP_ATTR_VALUES3               3082
J2EE_CONFIG                   2163
SYSCOLDIST                    1193
Then delete the change log in http://<server>/sld
Select "Administration -> Maintenance -> Clean Up Change Log -> Remove Entries".
I then scheduled a standard job to do this work from the "Cleanup Task Configuration" tab.
Finally, reorg the following tables to release the space:



TABNAME                       NPAGES
---------------------------- --------------------
BC_SLD_CHANGELOG              1205
That’s it.