Using Google Chrome?

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

Recent Posts

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:

3ea55e82-f4d0-4eac-b0f2-6cb9d5fe1273

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":

http://<server>:5##00/monitoring/SystemInfo

ec8778de-6288-49ab-a7fe-cfd1bd271e69



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

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

DO NOT DO THIS IN PRODUCTION.
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

name
         db_size
         owner                dbid
         created
         durability
         lobcomplvl
         inrowlen
         status                       
--------------------------------------------------------

saptools
             4628.0 MB
         sapsa                   5
         Jul 20, 2015
         full
                100
             2000
         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%

completed.

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

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

name
         db_size
         owner                dbid
         created
         durability
         lobcomplvl
         inrowlen
         status                       
--------------------------------------------------------

saptools
             4320.0 MB
         sapsa                   5
         Jul 20, 2015
         full
                100
             2000
         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):
SELECT
    "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"
  FROM
    "PA0006"
  WHERE
    "MANDT" = ? AND "PERNR" IN ( ? , ? , ? , ? , ? )  /* R3:SAPDBPNP:11498 T:PA0006 M:100 */  /*unc. rd.*/
Variable
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:
SELECT
    "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"
  FROM
    "PA0006"
  WHERE
    "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>
go
We want to show the resultant execution (EXPLAIN) plan.
set showplan on
go
We would like some additional useful details from the EXPLAIN plan.
set statistics io on
go
set statistics time on
go
set statistics plancost on
go
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
go
We make life easier by enabling the use of quotes, since SAP has already put them there for us.
set quoted_identifier on
go
Finally, we paste the SQL itself, followed by the ASE SQL command terminator “go”:
SELECT
    "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"
  FROM
    "PA0006"
  WHERE
    "MANDT" = ‘100’ AND "PERNR" IN (‘00000001’, ‘00000002’ , ‘00000003’ , ‘00000004’ , ‘00000005’ )
Here’s the sample output plan:
================ Lava Operator Tree ================
                        Emit
                        (VA = 3)
                        r:36 er:39
                        cpu: 0
             /
            NestLoopJoin
            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.