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

Tag: History

HowTo: Call sp_dump_history with Purge in ASE 16.0 SP03

Due to a bug in ASE 16.0 SP03 PL08 (and maybe earlier versions) the sp_dump_history call does not work properly.
It just returns an empty screen.
When you look at the procedure code, you will see that it fails to find the tempdb for whatever reason.

We can use the sp_dump_history procedure to look at the dump history (backup history) for an ASE database.
From ASE 15.7 onwards we can also use it to purge the history, reducing the size of the dumphist file and in some cases improving the performance of backups themselves (see SAP note 2753062 for details on issues).

By prefixing the tempdb onto the call from the master database, you can get sp_dump_history to work as it should.

Below is an example that purges all backup records from the dump history file (dumphist) from before January 29th @ 15:00:00 :

use master
go

tempdb..sp_dump_history @operation='purge', @until_time="january 29,2020 15:00:00:00"
go

Tip: In Linux the correct date format can be obtained with the date command:

date "+%b %d,%Y %T"

As another tip, you can use the sp_helptext procedure to look at the source code of any stored procedures. Take a look on help.sap.com for the parameters for sp_helptext.

You may also be interested in:

HowTo: SAP Kernel Patch History

Scenario: Your SAP system Kernel has been patched.
You would like to see the patch history of the system and you are running on Windows and SQL Server.

You can view the patch history for a DLL or EXEcutable (such as disp+work) by querying a table in the SQL Server database as follows (changing the <SID>):

SQL>  select * from <SID>.MSSDWDLLS
    where DLLNAME='disp+work.EXE'
order by DLLNAME, HOSTNAME, DLLPATH, LASTDATE, LASTTIME;

The results will provide a complete traceable history of the system including the previous identity of the SAP system, the different application instances and any inconsistencies in the DLL versions.

SAP Kernel Patch History SQL Server

HowTo: Use AWR snapshot data to provide a history of sequential read values

Scenario: You have changed your back-end storage hardware (SAN array or SATA disk storage) and you want to see a historical overview of sequential read times of your database.

It’s possible to query the Oracle AWR data (provided you have paid for the license), to provide a historical list of sequential read times acccording to the snapshots taken by AWR.
You are obviously limited to the amount of data retained by AWR and the frequency of the AWR snapshots.

set linesize 400 pagesize 400
SELECT event_start.snap_id,
       to_char(snap.begin_interval_time,'DD-MM-YY HH24:MI') as begin_time,
       to_char(snap.end_interval_time,'HH24:MI') as end_time,
       round(decode(
                 (event_end.total_waits - nvl(event_start.total_waits, 0)),0, to_number(NULL),
       ((event_end.time_waited_micro -    nvl(event_start.time_waited_micro,0))/1000) / (event_end.total_waits - nvl(event_start.total_waits,0))
),0) avgwait,
       event_end.event_name event_name,
      (event_end.time_waited_micro - nvl(event_start.time_waited_micro,0)/1000000) total_ms,
      event_end.total_waits
FROM dba_hist_system_event event_start,
     dba_hist_system_event event_end,
     dba_hist_snapshot snap
WHERE event_end.snap_id = event_start.snap_id + 1
  AND event_end.event_name = 'db file sequential read'
  AND event_start.event_name = event_end.event_name
  AND event_start.snap_id = snap.snap_id
  AND event_start.dbid = snap.dbid
  AND event_start.instance_number = snap.instance_number
  AND snap.begin_interval_time > SYSDATE - 14            -- max 14 days history.
-- AND to_char(snap.begin_interval_time,'HH24') IN ('09','10','11','12','13','14','15','16','17')
-- AND to_char(snap.begin_interval_time,'MI') = '50'
ORDER BY event_start.snap_id;

NOTE: You can restrict the snapshot intervals used to provide “hourly” values by uncommenting the additional two lines.