Skip to Content
Technical Articles

Create SAP Performance Analysis Dashboard with ABAP Download data

I had been working on SAP performance analysis with my customers in the past.
Based on my experience, the performance analysis process could be time-consuming and might need a large amount of time to prepare reports for sharing the finding with other team members.

Recently, I am working with a customer on a project to create a dashboard for analyzing SAP System performance and target to simplify the effort to perform performance analysis and finding insight from the data. This article is for demonstrating how to use an ABAP program for download performance data from the SAP System for performance analysis.

 

Fig. 1 – Sample report created with SAP Analytic Cloud for SAP Workload Analysis

 

The screenshots (Fig. 2 & Fig 3) below are from the sap workload analysis transaction (ST03N).

I do find it useful for finding a performance bottleneck in SAP Systems.

For this article, I would like to demonstrate how we can simplify the performance analysis on the SAP system with a visualization tool and extract the SAP workload statistic data using SAP workload functions.

Fig 2 – SAP Workload Analysis (ST03N) – Transaction Profile

 

Fig 3 – SAP Workload Analysis (ST03N) – Time Profile

 

In order to create the dashboard for analysis, you will need to download the data from the SAP system.

For this demonstration, I had created an ABAP program for download the following data into CSV files.

  1. STAD
  2. Background Job – Header
  3. Background Job – steps
  4. ABAP Dump

You may use this sample program and schedule it into a background task daily to download the performance data for analysis.

For those who are interested to analyze sap system performance with STAD data, you may refer to SAP old course code (BC315) or search for help.sap.com with the keyword as (ST03N, dialog response time, wait-time, database request time, processing time.).

Here are some of the sample benchmark:

Avg. Response Time (Avg Request Time) = Total Request Time / Total Dialog Step

Avg. Database Request Time = Total Database Request Time / Total Dialog Step

Avg. Processing Time = Total Processing Time / Total Dialog Step

Avg. CPU Time = Total CPU Time / Total Dialog Step

Avg. Frontend GUI Time = Total GUI Time / Total Dialog Step

Each Dialog Step = each application execution of PBO (Process before output) and PAI (Proces after input)

  • Avg. Wait-Time < 10% Avg. Response Time (Request_time) or  below 150ms
  • Avg. CPU Time < 40% of Avg. Response Time – Avg. Wait-Time
  • Avg. Database Request Time < 40% Response Time – Avg. Wait-Time
  • Avg. Processing Time < 2 times of Avg. CPU Time.
  • Avg. Load Time + Gen. Time < 50 ms
  • Avg. Frontend GUI Time – Network Time < 1000 ms

Fig 4. SAP response time analysis

Wait-time – amount of time for waiting for sap work process from the queue.

CPU-time – amount of time for using CPU resource on SAP application server.

Database Request Time – amount of time for database to process the request.

Processing time – amount of time for working in sap work process.

Load + Gen Time – amount of time for loading & generate sap programs into application server memory.

Frontend GUI time – amount of time from user’s workstation to communciate with sap application server.

 

High Database Request Time –> Database performance bottleneck – database loading or expensive SQL

High processing time –> Hardware bottleneck

High CPU time –>  Expensive SQL

High load & gen. time –> Mis-configuration of SAP buffer pools / SAP parameters

High Frontend GUI time –> Network issue.

 

The threshold value of the performance figure can be found in RZ20 –> CCMS Monitor Template –> alert object –> properties.

Fig 5. RZ20 – Threshold Value for SAP Performance Analysis

Fig 6. RZ20 – Threshold value from alert object’s properties

Once you have download the performance data, you may start to work on the dashboard.

Fig. 7 – Sample Output file for STAD.csv

 

For this demonstration, I am using the SAP Analytics Cloud for creating the performance analysis dashboard. However, you may use any visualization tools for prepare the analysis report.

(i.e. SAP BusinessObjects, Excel, etc).

 

1. Create a data model based on the CSV file. (i.e. STAD.csv)

Fig 8. SAC data model

 

2. Upload the additional data to the data model.

Fig 9. Refresh data for SAC data model.

 

3. Create a report with the data model.

Fig 10. Create a report on SAC.

 

4. Insert an object (Chart / Table)

Fig. 11 – insert a chart.

 

5. Select the dimension & measure required for analysis

Fig 12 – Create a chart

 

6. Add Threshold value based on the threshold value you found in R20 or SAP Performance Analysis wiki or help.sap.com

Fig 13 – Creating Thresholds for visualization

 

Now, we just have created a bar chart with thresholds for detects hardware bottleneck.

For the next step, we may continue to create the dashboard with the mentioned KPI / benchmarks mentioned above as a set of calculated measures or threshold meters.

I had also added a table for detail analysis on transactions & programs with the threshold value as color icons. This will help the users quickly identify a performance bottleneck and take follow-up actions.

7. Finalized Report for transaction profile analysis with key performance indicators.

Fig 14 – Detail Analysis with thresholds icon

Fig 15 – Example – Create Threshold based on different calculated measure.

 

Conclusion: 

In conclusion, we can use the visualization tools to help us to speed up the process of performance analysis and sharing our findings with other team members.

 

Fig 16 – comparison of traditional ABAP report and visualization report.

 

 

 

 

Appendix: 

Tips: You may use other visualization tools for creating the same dashboard with the dataset download with the sample program attached.

Below are sample reports created for reference.

Transaction Analysis – Sample 1

 

Transaction Analysis – Sample 2 –

 

Sample Code for Download Statistic Data for STAD, Background Job and ABAP Dumps

REPORT ZBCDATEXTRACT02.
TABLES: SAPWLSERV, TBTCO, TBTCP.

*--------------------------------------------------------------------*
*                        V A R I A B L E S                           *
*--------------------------------------------------------------------*
DATA: l_count type swncshcnt.
DATA: l_sdate type swncdatum.
DATA: ZSYSID(3) type C.
DATA: ZBNAME(12) type C.
DATA: ZACCESSKEY(20) type C.
DATA: ZUSTYP(1) type C.
DATA: ZUFLAG(3) type C.
DATA: ZTRDAT(8) type C.
DATA: ZPWDLGNDATE(8) type C.
DATA: ZLOCNT(3) type C.
DATA: ZGLTGV(8) type C.
DATA: ZGLTGB(8) type C.
DATA: ZCLIENT(3)  type C.
DATA: ZMSG(20) type C.
DATA: ZSTEXT_1(60) type C.
DATA: ZSTEXT_2(60) type C.
DATA: ZVAR_1(20) type C.
DATA: ZVAR_2(20) type C.
DATA: lvdate like SY-DATUM VALUE '20200101'.
DATA: lvtime TYPE SY-UZEIT.
DATA  HEADER(1500).
DATA: v_record(1500) TYPE C.
DATA: PPFILE_1(120) type C.
DATA: OUTFILE(255) type C.
DATA: OUTFILE2(255) type C.
DATA: OUTFILE3(255) type C.
DATA: OUTFILE4(255) type C.
DATA: MM(2).
DATA: YY(4).

lvdate = SY-DATUM.
lvtime = SY-UZEIT.
MM = lvdate+4(2).
YY = lvdate+0(4).

* Define input for user input
PARAMETERS: PPATH_1(60) DEFAULT 'R:\usr\sap\UD2\DVEBMGS12\DATA\'                   LOWER CASE.
PARAMETERS: YYYYMMDD(8) DEFAULT SY-DATUM                                           LOWER CASE.
PARAMETERS: FILE1(60)   DEFAULT 'STAD.csv'   LOWER CASE.
PARAMETERS: FILE2(60)   DEFAULT 'TBTCO.csv'  LOWER CASE.
PARAMETERS: FILE3(60)   DEFAULT 'TBTCP.csv'  LOWER CASE.
PARAMETERS: FILE4(60)   DEFAULT 'ABAPDUMP.csv' LOWER CASE.
PARAMETERS: ZPAR1       DEFAULT '0' LOWER CASE.
PARAMETERS: ZPAR2       DEFAULT ';' LOWER CASE.

*Call Function 'SWNC_STATREC_READ_INSTANCE'*
*Call Function 'SWNC_STAD_READ_STATRECS'*
*Call Function 'SAPWL_WORKLOAD_GET_STATISTIC'*
*Call Function 'SAPWL_WORKLOAD_GET_SUMMARY'*
*Call Function 'SAPWL_GET_SUMMARY_STATISTIC'*
*Call Function 'SWNC_STATREC_READ_INSTANCE'*
*--------------------------------------------------------------------*
*        I N T E R N A L   T A B L E   D E C L A R A T I O N         *
*--------------------------------------------------------------------*
DATA: lt_smr type SWNC_T_STATRECS.
DATA: wa_lt_smr like line of lt_smr.
DATA: ls_data type SWNCMAINREC.
DATA: lt_data type standard table of swncmainrec.
DATA: wa_lt_data like line of lt_data.
DATA: lt_fcat type slis_t_fieldcat_alv.
DATA: lt_tabrec type SWNC_T_SUBTAB.
DATA: wa_lt_tabrec like line of lt_tabrec.

DATA: begin of summary occurs 0.
include structure SAPWLSUMRY.
DATA: end of summary.

DATA: periodtype like SAPWLACCTP-PERIODTYPE,
hostid like SAPWLSERV-HOSTSHORT,
instance like SAPWLSERV-NAME,
startdat like SAPWLACCTP-STARTDATE.

DATA: begin of LT_SAPWLSERV occurs 0.
include structure SAPWLSERV.
DATA: end of LT_SAPWLSERV.
DATA: wa_sapwlserv like line of LT_SAPWLSERV.
DATA: SAPINSTANCE TYPE SWNCINSTANCE.

DATA: AVG(5).
DATA: TASKTYPE(4) TYPE C.
DATA: WPNO(10) TYPE C.
DATA: SWPID(10) TYPE C.
DATA: SSCREEN(4) TYPE C.
DATA: PRIVMODE(4).
DATA: DIALOGSTEP(12).
DATA: RESPTI(12).
DATA: QUEUETI(12).
DATA: PROCTI(12).
DATA: CPUTI(12).
DATA: DBREQTI(12).
DATA: GUITIME(12).
DATA: LOCKCNT(12).
DATA: LOCKTI(12).
DATA: GENERATETI(12).
DATA: REPLOADTI(12).
DATA: CUALOADTI(12).
DATA: DYNPLOADTI(12).
DATA: RFCTI(24).
DATA: HTTPTIME(24).

DATA: MEMSUM(24).
DATA: PRIVSUM(24).
DATA: USEDBYTES(24).
DATA: MAXBYTES(24).
DATA: MAXBYTESDI(24).
DATA: DIALOG_STEP TYPE SWNCDIALOGSTEP.

DATA: RESP_TIME TYPE SWNCTIMUSL.
DATA: WAIT_TIME TYPE SWNCTIMUSL.
DATA: PROC_TIME TYPE SWNCTIMUSL.
DATA: CPU_TIME TYPE SWNCTIMUSL.
DATA: DBREQ_TIME TYPE SWNCTIMUSL.
DATA: GUI_TIME TYPE SWNCTIMUSL.
DATA: LOCK_COUNT TYPE SWNCSHCNT.
DATA: LOCK_TIME TYPE SWNCTIMUSL.
DATA: GEN_TIME TYPE SWNCTIMUSL.
DATA: REPLOAD_TIME TYPE SWNCTIMUSL.
DATA: CUALOAD_TIME TYPE SWNCTIMUSL.
DATA: DYNPLOAD_TIME TYPE SWNCTIMUSL.
DATA: RFC_TIME TYPE SWNCTIMUSL.
DATA: HTTP_TIME TYPE SWNCTIMUSL.

DATA: BEGIN OF lt_tbtco OCCURS 0.
INCLUDE STRUCTURE TBTCO.
DATA: END OF lt_tbtco.
DATA: wa_lt_tbtco like line of lt_tbtco.

DATA: jobcount(8).
DATA: stepcount(10).

DATA: BEGIN OF lt_tbtcp OCCURS 0.
INCLUDE STRUCTURE TBTCP.
DATA: END OF lt_tbtcp.
DATA: wa_lt_tbtcp like line of lt_tbtcp.

DATA: EXITCODE(10).

DATA: BEGIN OF it_snap occurs 0.
INCLUDE STRUCTURE SNAP.
DATA: END OF it_snap.
DATA: wa_it_snap like line of it_snap.
DATA: POS TYPE I, POS2 TYPE I.
DATA: OFF TYPE I, OFF2 TYPE I.
DATA: LEN TYPE I, LEN2 TYPE I.
DATA: ZERRORID like RDUMPOV-ERRORID.
DATA: ZPROGRAM like RDUMPOV-GPROGRAM.
DATA: ZTID like RDUMPOV-TID.

DATA: BEGIN OF MASTERCAL OCCURS 0.
DATA:       SYSTEM_DATE like sy-datum ,
END OF MASTERCAL.
DATA: NEWDATE like sy-datum.

 

l_sdate = YYYYMMDD.
startdat = l_sdate.
hostid = sy-host.
*SELECT single NAME into instance from SAPWLSERV.
*call function 'SAPWL_WORKLOAD_GET_SUMMARY'
*  exporting
*  periodtype = 'D'
*  hostid = hostid
*  startdate = startdat
*  INSTANCE = instance
*  tables
*  summary = summary.
*  loop at summary.
* " if summary-tasktype = 'DIALOG'.
*  IF SUMMARY-COUNT > 0 .
*  avg = SUMMARY-RESPTI / SUMMARY-COUNT.
*  ELSE.
*  avg = 0.
*  ENDIF.
*  write:/ sy-datum, ';', sy-host, ';', summary-tasktype, ';', summary-count, ';', summary-CPUTI.

" endif.
*  endloop.
*  write:/ 'AVERAGE RESPONSE TIME:', avg, 'ms'.

CONCATENATE PPATH_1 YYYYMMDD FILE1 INTO OUTFILE.
OPEN DATASET OUTFILE FOR OUTPUT IN TEXT MODE encoding UTF-8.

concatenate
'SAP_INSTANCE'
'START_DATE'
'START_TIME'
'USER_ACCOUNT'
'WORKPROCESS_ID'
'WORKPROCESS_NO'
'TASK_TYPE'
'REPORT'
'TRANSACTION_CODE'
'SCREEN_NUMBER'
'DIALOG_STEP'
'REQUEST_TIME'
'WAIT_TIME'
'CPU_TIME'
'PROCESSING_TIME'
'DBREQUEST_TIME'
'GENERATE_TIME'
'GUI_TIME'
'LOCK_TIME'
'RFC_TIME'
'HTTP_TIME'
'REPOSITORY_LOAD_TIME'
'CUA_LOAD_TIME'
'DYNPRO_LOAD_TIME'
'END_DATE'
'END_TIME'
'TERMINAL_ID'
'TRANSACTION_ID'
'CONTEXT_ID'
into header separated by ZPAR2.

transfer header to OUTFILE.

SELECT NAME into SAPINSTANCE FROM SAPWLSERV.
CLEAR lt_smr.
REFRESH lt_smr.
call function 'SWNC_STATREC_READ_INSTANCE'
exporting
read_timezone               = 'UTC+8'
*     NO_OF_RECORDS               = -1
read_start_date             = l_sdate
read_start_time             = '000000'
read_end_date               = l_sdate
read_end_time               = '235959'
*     READ_CLIENT                 =
read_username               = ''
*     FLAG_EXCLUDE_USERNAME       = ' '
*     READ_WORKPROCESS            = 'FFFF'
*     READ_TRANSIDS               =
*     READ_TCODE                  =
*     READ_DIALOGSTEPID           =
*     STATISTIC_FILE              =
*     ASTAT_FILE                  =
*     NO_BUFFER_FLUSH             =
*     FLAG_READ_STAT              = 'X'
*     FLAG_READ_ASTAT             = ' '
TARGET_INSTANCE             = SAPINSTANCE
importing
statrecs                    = lt_smr
records_read                = l_count
*     SINGLE_RECORDS_READ         =
*     EOI_REACHED                 =
*     EOF_REACHED                 =
*     ANONYMOUS                   =
exceptions
read_error                  = 1
others                      = 2.

loop at lt_smr into wa_lt_smr.
move wa_lt_smr-mainrec to ls_data.
append ls_data to lt_data.
clear ls_data.
endloop.

loop at lt_data into wa_lt_data.
DIALOG_STEP    = wa_lt_data-DIALOG_STEP.
RESP_TIME      = wa_lt_data-RESPTI / 1000.
WAIT_TIME      = wa_lt_data-QUEUETI / 1000.
CPU_TIME       = wa_lt_data-CPUTI / 1000.
PROC_TIME      = wa_lt_data-PROCTI / 1000.
DBREQ_TIME     = wa_lt_data-DBREQTIME / 1000.
GUI_TIME       = wa_lt_data-GUITIME / 1000.
LOCK_TIME      = wa_lt_data-LOCKTI / 1000.
GEN_TIME       = wa_lt_data-GENERATETI / 1000.
REPLOAD_TIME   = wa_lt_data-REPLOADTI / 1000.
CUALOAD_TIME   = wa_lt_data-CUALOADTI / 1000.
DYNPLOAD_TIME  = wa_lt_data-DYNPLOADTI / 1000.
RFC_TIME       = wa_lt_data-RFCTI / 1000.
HTTP_TIME      = wa_lt_data-HTTPTIME / 1000.

MOVE wa_lt_data-LUW_INFO to WPNO.
CONDENSE WPNO.
MOVE wa_lt_data-WPID to SWPID.
CONDENSE SWPID.
MOVE wa_lt_data-TASKTYPE to TASKTYPE.
CONDENSE TASKTYPE.

MOVE DIALOG_STEP to DIALOGSTEP.
CONDENSE DIALOGSTEP.
MOVE RESP_TIME to RESPTI.
CONDENSE RESPTI.
MOVE WAIT_TIME to QUEUETI.
CONDENSE QUEUETI.
MOVE CPU_TIME to CPUTI.
CONDENSE CPUTI.
MOVE PROC_TIME to PROCTI.
CONDENSE PROCTI.
MOVE DBREQ_TIME to DBREQTI.
CONDENSE DBREQTI.
MOVE GUI_TIME to GUITIME.
CONDENSE GUITIME.
MOVE LOCK_TIME to LOCKTI.
CONDENSE LOCKTI.
MOVE GEN_TIME to GENERATETI.
CONDENSE GENERATETI.

MOVE REPLOAD_TIME to REPLOADTI.
CONDENSE REPLOADTI.
MOVE CUALOAD_TIME to CUALOADTI.
CONDENSE CUALOADTI.
MOVE DYNPLOAD_TIME to DYNPLOADTI.
CONDENSE DYNPLOADTI.
MOVE RFC_TIME to RFCTI.
CONDENSE RFCTI.
MOVE HTTP_TIME to HTTPTIME.
CONDENSE HTTPTIME.

concatenate
SAPINSTANCE
wa_lt_data-STARTDATE
wa_lt_data-STARTTIME
wa_lt_data-ACCOUNT
SWPID
WPNO
TASKTYPE
wa_lt_data-REPORT
wa_lt_data-TCODE
wa_lt_data-DYNPRONR
DIALOGSTEP
RESPTI
QUEUETI
CPUTI
PROCTI
DBREQTI
GENERATETI
GUITIME
LOCKTI
RFCTI
HTTPTIME
REPLOADTI
CUALOADTI
DYNPLOADTI
wa_lt_data-ENDDATE
wa_lt_data-ENDTIME
wa_lt_data-TERMINALID
wa_lt_data-TRANSID
wa_lt_data-CONTEXT_ID
into v_record separated by ZPAR2.
transfer v_record to OUTFILE.
endloop.
ENDSELECT.
CLOSE DATASET OUTFILE.
refresh lt_smr.
clear lt_smr.
refresh lt_data.
clear lt_data.

CLEAR lt_tbtco.
REFRESH lt_tbtco.
SELECT * FROM TBTCO into CORRESPONDING FIELDS of TABLE lt_tbtco WHERE RELDATE = l_sdate.

CONCATENATE PPATH_1 YYYYMMDD FILE2 INTO OUTFILE2.
OPEN DATASET OUTFILE2 FOR OUTPUT IN TEXT MODE encoding UTF-8.
concatenate
'EXECUTE_SERVER'
'JOB_STARTDATE'
'JOB_STARTTIME'
'JOB_ENDDATE'
'JOB_ENDTIME'
'STEP_COUNT'
'JOB_STATUS'
'LAST_STARTDATE'
'LAST_STARTTIME'
'JOB_NAME'
'JOB_ID'
'EXECUTE_USER'
'EXECUTE_CLIENT'
'JOB_RELEASED_BY'
'JOB_RELEASED_DATE'
'JOB_RELEASED_TIME'
'WORKPROCESS_NUMBER'
'WORKPROCESS_ID'
into header separated by ZPAR2.
transfer header to OUTFILE2.

LOOP AT lt_tbtco into wa_lt_tbtco.
move wa_lt_tbtco-JOBCOUNT to jobcount.
CONDENSE jobcount.
move wa_lt_tbtco-STEPCOUNT to stepcount.
CONDENSE stepcount.
move wa_lt_tbtco-WPNUMBER to WPNO.
condense WPNO.
move wa_lt_tbtco-WPPROCID  to SWPID.
condense SWPID.
concatenate
wa_lt_tbtco-REAXSERVER
wa_lt_tbtco-STRTDATE
wa_lt_tbtco-STRTTIME
wa_lt_tbtco-ENDDATE
wa_lt_tbtco-ENDTIME
stepcount
wa_lt_tbtco-STATUS
wa_lt_tbtco-LASTSTRTDT
wa_lt_tbtco-LASTSTRTTM
wa_lt_tbtco-JOBNAME
jobcount
wa_lt_tbtco-AUTHCKNAM
wa_lt_tbtco-AUTHCKMAN
wa_lt_tbtco-RELUNAME
wa_lt_tbtco-RELDATE
wa_lt_tbtco-RELTIME
WPNO
SWPID
into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
ENDLOOP.
CLOSE DATASET OUTFILE2.

CLEAR lt_tbtcp.
REFRESH lt_tbtcp.
SELECT * FROM TBTCP into CORRESPONDING FIELDS of TABLE lt_tbtcp WHERE SDLDATE = l_sdate.
CONCATENATE PPATH_1 YYYYMMDD FILE3 INTO OUTFILE3.
OPEN DATASET OUTFILE3 FOR OUTPUT IN TEXT MODE encoding UTF-8.
concatenate
'JOBSTEP_SDATE'
'JOBSTEP_STIME'
'JOB_NAME'
'JOB_ID'
'STEP_COUNT'
'PROGRAM_NAME'
'EXECUTE_USER'
'SCHEDULE_USER'
'STATUS'
'REPORT'
'EXTERNAL_PROGRAM'
'EXTERNAL_COMMAND'
'EXTERNAL_PROCESS_ID'
'EXECUTE_SYSTEM'
'EXECUTE_LOGICAL_SYSTEM'
'EXTERNAL_EXITCODE'
into header separated by ZPAR2.
transfer header to OUTFILE3.

LOOP AT lt_tbtcp into wa_lt_tbtcp.
move wa_lt_tbtcp-EXITCODE to EXITCODE.
CONDENSE EXITCODE.
move wa_lt_tbtcp-STEPCOUNT to stepcount.
CONDENSE stepcount.
concatenate
wa_lt_tbtcp-SDLDATE
wa_lt_tbtcp-SDLTIME
wa_lt_tbtcp-JOBNAME
wa_lt_tbtcp-JOBCOUNT
stepcount
wa_lt_tbtcp-PROGNAME
wa_lt_tbtcp-AUTHCKNAM
wa_lt_tbtcp-SDLUNAME
wa_lt_tbtcp-STATUS
wa_lt_tbtcp-REPORT
wa_lt_tbtcp-XPGPROG
wa_lt_tbtcp-EXTCMD
wa_lt_tbtcp-XPGPID
wa_lt_tbtcp-XPGTGTSYS
wa_lt_tbtcp-XPGRFCDEST
EXITCODE
into v_record separated by ZPAR2.
transfer v_record to OUTFILE3.

ENDLOOP.
CLOSE DATASET OUTFILE3.

CONCATENATE PPATH_1 YYYYMMDD FILE4 INTO OUTFILE4.
clear it_snap.
refresh it_snap.
"  SELECT * FROM SNAP INTO CORRESPONDING FIELDS OF TABLE it_snap WHERE datum = l_sdate and SEQNO = '000'.
SELECT * FROM SNAP INTO CORRESPONDING FIELDS OF TABLE it_snap WHERE SEQNO = '000'.
OPEN DATASET OUTFILE4 FOR OUTPUT IN TEXT MODE encoding UTF-8.
concatenate
'SYSTEM_ID'
'DATE'
'TIME'
'HOST'
'USERNAME'
'CLIENT'
'ERROR_ID'
'PROGRAM'
'TRANSACTION_ID'
into header separated by ZPAR2.
transfer header to OUTFILE4.
LOOP AT it_snap INTO wa_it_snap.
SEARCH wa_it_snap-FLIST for 'AP0'.
POS  = SY-FDPOS.
LEN  = SY-FDPOS - 5.
ZERRORID = wa_it_snap-FLIST+5(LEN).

SEARCH wa_it_snap-FLIST for 'AI0'.
POS2 = POS + 5.
LEN2 = SY-FDPOS - POS.
IF LEN2 <= 0.
LEN2 = 12.
ENDIF.
ZPROGRAM = wa_it_snap-FLIST+POS2(LEN2).

"       WRITE: /'AP0', SY-SUBRC UNDER 'SY-SUBRC', SY-FDPOS UNDER 'SY-FDPOS', wa_it_snap-FLIST+5(LEN), wa_it_snap-FLIST+POS2(LEN2).

SELECT SINGLE TCODE INTO ZTID FROM TSTC WHERE PGMNA = ZPROGRAM.
IF SY-SUBRC <> 0.
ZTID = ''.
ENDIF.

CONCATENATE
SY-SYSID
wa_it_snap-DATUM
wa_it_snap-UZEIT
wa_it_snap-AHOST
wa_it_snap-UNAME
wa_it_snap-MANDT
ZERRORID
ZPROGRAM
ZTID
INTO v_record separated by ZPAR2.
transfer v_record to OUTFILE4.
ENDLOOP.
CLOSE DATASET OUTFILE4.

 

NEWDATE = CDATE - 730.
CONCATENATE PPATH_1 CDATE SY-SYSID 'MASTERCAL.csv' INTO OUTFILE2.
OPEN DATASET OUTFILE2 FOR OUTPUT IN TEXT MODE encoding UTF-8.
CONCATENATE 'SYSTEM_DATE' 'SYSTEM_TIME' INTO header separated by ZPAR2.
transfer header to OUTFILE2.

WHILE NEWDATE <= CDATE.
WRITE:/ NEWDATE, ';', '00:00:00'.
concatenate NEWDATE '00:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '01:00:00'.
concatenate NEWDATE '01:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '02:00:00'.
concatenate NEWDATE '02:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '03:00:00'.
concatenate NEWDATE '03:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '04:00:00'.
concatenate NEWDATE '04:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '05:00:00'.
concatenate NEWDATE '05:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '06:00:00'.
concatenate NEWDATE '06:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '07:00:00'.
concatenate NEWDATE '07:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '08:00:00'.
concatenate NEWDATE '08:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '09:00:00'.
concatenate NEWDATE '09:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '10:00:00'.
concatenate NEWDATE '10:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '11:00:00'.
concatenate NEWDATE '11:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '12:00:00'.
concatenate NEWDATE '12:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '13:00:00'.
concatenate NEWDATE '13:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '14:00:00'.
concatenate NEWDATE '14:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '15:00:00'.
concatenate NEWDATE '15:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '16:00:00'.
concatenate NEWDATE '16:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '17:00:00'.
concatenate NEWDATE '17:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '18:00:00'.
concatenate NEWDATE '18:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '19:00:00'.
concatenate NEWDATE '19:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '20:00:00'.
concatenate NEWDATE '20:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '21:00:00'.
concatenate NEWDATE '21:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '22:00:00'.
concatenate NEWDATE '22:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
WRITE:/ NEWDATE, ';', '23:00:00'.
concatenate NEWDATE '23:00:00' into v_record separated by ZPAR2.
transfer v_record to OUTFILE2.
NEWDATE = NEWDATE + 1.
ENDWHILE.
CLOSE DATASET OUTFILE2.

 

 

Sample program for download table statistic from SAP System.

REPORT ZRCABA004_TABLESTATISTIC no standard page heading message-id s1 line-size 210.
include rstabl12.
*--------------------------------------------------------------------*
*                        V A R I A B L E S                           *
*--------------------------------------------------------------------*
DATA: l_count type swncshcnt.
DATA: l_sdate type swncdatum.
DATA: ZSYSID(3) type C.
DATA: ZBNAME(12) type C.
DATA: ZACCESSKEY(20) type C.
DATA: ZUSTYP(1) type C.
DATA: ZUFLAG(3) type C.
DATA: ZTRDAT(8) type C.
DATA: ZPWDLGNDATE(8) type C.
DATA: ZLOCNT(3) type C.
DATA: ZGLTGV(8) type C.
DATA: ZGLTGB(8) type C.
DATA: ZCLIENT(3)  type C.
DATA: ZMSG(20) type C.
DATA: ZSTEXT_1(60) type C.
DATA: ZSTEXT_2(60) type C.
DATA: ZVAR_1(20) type C.
DATA: ZVAR_2(20) type C.
DATA: lvdate like SY-DATUM VALUE '20200101'.
DATA: lvtime TYPE SY-UZEIT.
DATA  HEADER(1500).
DATA: v_record(1500) TYPE C.
DATA: PPFILE_1(120) type C.
DATA: OUTFILE(255) type C.
DATA: OUTFILE2(255) type C.
DATA: OUTFILE3(255) type C.
DATA: OUTFILE4(255) type C.
DATA: MM(2).
DATA: YY(4).
DATA: ppath_1(128) type C. "LOG 0001

lvdate = SY-DATUM.
lvtime = SY-UZEIT.
MM = lvdate+4(2).
YY = lvdate+0(4).

*Call Function 'SWNC_STATREC_READ_INSTANCE'*
*Call Function 'SWNC_STAD_READ_STATRECS'*
*Call Function 'SAPWL_WORKLOAD_GET_STATISTIC'*
*Call Function 'SAPWL_WORKLOAD_GET_SUMMARY'*
*Call Function 'SAPWL_GET_SUMMARY_STATISTIC'*
*Call Function 'SWNC_STATREC_READ_INSTANCE'*

*--------------------------------------------------------------------*
*        I N T E R N A L   T A B L E   D E C L A R A T I O N         *
*--------------------------------------------------------------------*
DATA: lt_smr type SWNC_T_STATRECS.
DATA: wa_lt_smr like line of lt_smr.
DATA: ls_data type SWNCMAINREC.
DATA: lt_data type standard table of swncmainrec.
DATA: wa_lt_data like line of lt_data.
DATA: lt_fcat type slis_t_fieldcat_alv.
DATA: lt_tabrec type SWNC_T_SUBTAB.
DATA: wa_lt_tabrec like line of lt_tabrec.

DATA: begin of summary occurs 0.
 include structure SAPWLSUMRY.
DATA: end of summary.

DATA: periodtype like SAPWLACCTP-PERIODTYPE,
      hostid like SAPWLSERV-HOSTSHORT,
      instance like SAPWLSERV-NAME,
      startdat like SAPWLACCTP-STARTDATE.

DATA: begin of LT_SAPWLSERV occurs 0.
      include structure SAPWLSERV.
DATA: end of LT_SAPWLSERV.
DATA: wa_sapwlserv like line of LT_SAPWLSERV.
DATA: SAPINSTANCE TYPE SWNCINSTANCE.

DATA: AVG(5).
DATA: TASKTYPE(4) TYPE C.
DATA: WPNO(10) TYPE C.
DATA: SWPID(10) TYPE C.
DATA: SSCREEN(4) TYPE C.
DATA: PRIVMODE(4).
DATA: DIALOGSTEP(12).
DATA: RESPTI(12).
DATA: QUEUETI(12).
DATA: PROCTI(12).
DATA: CPUTI(12).
DATA: DBREQTI(12).
DATA: GUITIME(12).
DATA: LOCKCNT(12).
DATA: LOCKTI(12).
DATA: GENERATETI(12).
DATA: REPLOADTI(12).
DATA: CUALOADTI(12).
DATA: DYNPLOADTI(12).
DATA: RFCTI(24).
DATA: HTTPTIME(24).

DATA: MEMSUM(24).
DATA: PRIVSUM(24).
DATA: USEDBYTES(24).
DATA: MAXBYTES(24).
DATA: MAXBYTESDI(24).
DATA: DIALOG_STEP TYPE SWNCDIALOGSTEP.

DATA: RESP_TIME TYPE SWNCTIMUSL.
DATA: WAIT_TIME TYPE SWNCTIMUSL.
DATA: PROC_TIME TYPE SWNCTIMUSL.
DATA: CPU_TIME TYPE SWNCTIMUSL.
DATA: DBREQ_TIME TYPE SWNCTIMUSL.
DATA: GUI_TIME TYPE SWNCTIMUSL.
DATA: LOCK_COUNT TYPE SWNCSHCNT.
DATA: LOCK_TIME TYPE SWNCTIMUSL.
DATA: GEN_TIME TYPE SWNCTIMUSL.
DATA: REPLOAD_TIME TYPE SWNCTIMUSL.
DATA: CUALOAD_TIME TYPE SWNCTIMUSL.
DATA: DYNPLOAD_TIME TYPE SWNCTIMUSL.
DATA: RFC_TIME TYPE SWNCTIMUSL.
DATA: HTTP_TIME TYPE SWNCTIMUSL.

DATA: BEGIN of itab occurs 0,
          NAME like SAPWLSERV-NAME,
      END of itab.
DATA: wa_itab like line of itab.

DATA: BEGIN OF lt_tbtco OCCURS 0.
      INCLUDE STRUCTURE TBTCO.
DATA: END OF lt_tbtco.
DATA: wa_lt_tbtco like line of lt_tbtco.

DATA: jobcount(8).
DATA: stepcount(10).

DATA: BEGIN OF lt_tbtcp OCCURS 0.
      INCLUDE STRUCTURE TBTCP.
DATA: END OF lt_tbtcp.
DATA: wa_lt_tbtcp like line of lt_tbtcp.

DATA: EXITCODE(10).

DATA: BEGIN OF it_snap occurs 0.
      INCLUDE STRUCTURE SNAP.
DATA: END OF it_snap.
DATA: wa_it_snap like line of it_snap.
DATA: POS TYPE I, POS2 TYPE I.
DATA: OFF TYPE I, OFF2 TYPE I.
DATA: LEN TYPE I, LEN2 TYPE I.
DATA: ZERRORID like RDUMPOV-ERRORID.
DATA: ZPROGRAM like RDUMPOV-GPROGRAM.
DATA: ZTID like RDUMPOV-TID.

"DATA: SYSNO(2).
DATA: BEGIN OF TABLE_CALLS OCCURS 1000.   "contains data about
        INCLUDE STRUCTURE DBSTATDEC.   "buffered tables
DATA: END OF TABLE_CALLS.
DATA: wa_table_calls like line of TABLE_CALLS.

*DATA: BEGIN OF CALC_VALUES OCCURS 1000."table with calculated values
*        INCLUDE STRUCTURE DBSTATDEC64.
*DATA:   TOTAL(12)    TYPE P,           "total # of requests
*        MODIFIES(12) TYPE P,           "sum of inserts & updates
*        FAILS(12)    TYPE P,           "insert, update, read,... fails
*        CHANGES(12)  TYPE P,           "modifies, fails, delete
*        RATIO        TYPE P DECIMALS 2,"changes/total accesses
*        DB_CALLS(12) TYPE P,           "# of DB calls used
*        ROWS(12)     TYPE P,           "# of affected rows
*        OPEN(12)     TYPE P,            " dir_opens and seq_opens
*        FETCH(12)    TYPE P,           " dir_fetches and seq_fetches
*        DBCALLS(14)  TYPE P ,"total DB duration (ms)
*        PREPARES(14) TYPE P ,"alle prepares
*      END OF CALC_VALUES.
DATA: wa_calc_values like line of CALC_VALUES.
data: begin of direc_table occurs 0.
          include structure sapwltadir.
data:        end of direc_table.

DATA: C_TOTAL(12)    TYPE C,
      C_MODIFIES(12) TYPE C,
      C_FAILS(12)    TYPE C,
      C_CHANGES(12)  TYPE C, "modifies, fails, delete
      C_RATIO        TYPE C, "changes/total accesses
      C_DB_CALLS(12) TYPE C, "# of DB calls used
      C_ROWS(12)     TYPE C, "# of affected rows
      C_OPEN(12)     TYPE C, " dir_opens and seq_opens
      C_FETCH(12)    TYPE C, " dir_fetches and seq_fetches
      C_DBCALLS(14)  TYPE C, "total DB duration (ms)
      C_PREPARES(14) TYPE C, "alle prepares
      C_DIRCNT(12)   TYPE C,
      C_DIRROW(12)   TYPE C,
      C_DIRFETCH(12) TYPE C,
      C_SEQCNT(12)   TYPE C,
      C_SEQROW(12)   TYPE C,
      C_SEQFETCH(12) TYPE C,
      C_UPDCNT(12)   TYPE C,
      C_UPDROW(12)   TYPE C,
      C_DELCNT(12)   TYPE C,
      C_DELROW(12)   TYPE C,
      C_INSCNT(12)   TYPE C,
      C_INSROW(12)   TYPE C.


DATA: BEGIN OF MASTERCAL OCCURS 0.
DATA:       SYSTEM_DATE like sy-datum ,
      END OF MASTERCAL.
DATA: NEWDATE like sy-datum.

* ====================================================================*
* parameters
* ====================================================================*
parameters:  buf_mode default 'A'
                     , "show infos about f)ull/generic buffered tables
                                       "p)artial buffered tables
             servtype default 'A',     "l)ocal r)emote or a)ll
             server like rfcdes-rfcdest default ' ',
*           server like sapwlserv-name default ' ',
             startday like sy-datum default sy-datum,
             period like sapwlacctp-periodtype default 'M'.
* Define input for user input
*PARAMETERS: PPATH_1(60) DEFAULT 'R:\usr\sap\UD2\DVEBMGS12\data'                   LOWER CASE.
PARAMETERS: CDATE LIKE sy-datum OBLIGATORY DEFAULT SY-DATUM LOWER CASE.
PARAMETERS: FILE1(60)   DEFAULT 'TABLESTATISTIC.csv'        LOWER CASE.
PARAMETERS: FILE2(60)   DEFAULT 'MASTERCAL.csv'             LOWER CASE.
"PARAMETERS: FILE3(60)   DEFAULT 'TBTCP.csv'     LOWER CASE.
"PARAMETERS: FILE4(60)   DEFAULT 'ABAPDUMP.csv'  LOWER CASE.
PARAMETERS: ZPAR1       DEFAULT '0' LOWER CASE.
PARAMETERS: ZPAR2       DEFAULT ';' LOWER CASE.



PERFORM f_set_output_path.
CALL 'C_GET_SYSTEM_NUMBER' id 'SYSTEM' field sysno.

CONCATENATE PPATH_1 CDATE SY-SYSID FILE1 INTO OUTFILE.
*CALL function 'SAPWL_TABSTAT_GET_STATISTIC'
*     exporting
*                periodtype         = period
*                hostid             = sy-host
*                instanceno         = sysno
*                startdate          = startday
*           importing
*                statdate           = CDATE
*           tables
*                table_calls        = TABLE_CALLS
*           exceptions
*                unknown_periodtype = 1
*                no_data_found      = 2
*                others             = 3.
*perform get_calc_values.
*loop at calc_values.
*     write:/ calc_values-tname, ';Total Request:', calc_values-total, ';Database Call:', calc_values-dbcalls, ';Rows:', calc_values-rows, ';Sequential Reads:', calc_values-seqcnt, ';Direct Reads:',  calc_values-dircnt.
*endloop.

OPEN DATASET OUTFILE FOR OUTPUT IN TEXT MODE encoding UTF-8.
"write:/ 'SYSTEM_ID', ';', 'TABLE_NAME', ';', 'TOTAL_REQUEST', ';', 'TABLE_MODIFIES',  ';', 'FAILED_REQUEST', ';', 'TOTAL_CHANGES', ';', 'DATABASE_CALL',';', 'DATABASE_PREPARE', ';', 'ROW_AFFECTED_RECORDS'.

    concatenate
    'SYSTEM_ID'        "SY-SYSID
    'SYSTEM_DATE'      "CDATE
    'HOST_ID'          "Table Statistic Host ID
    'TABLE_NAME'       "TABLE_CALLS-TNAME
    'TOTAL_REQUESTS'   "C_TOTAL
    'MODIFIES'         "C_MODIFIES
    'FAILS'            "C_FAILS
    'Database calls'   "DC_DBCALLS
    'Prepares'         "C_PREPARES
    'Row affected'     "C_ROWS
    'Direct Reads'     "C_DIRCNT
    'Direct Fetch'     "C_DIRFETCH
    'Sequential Reads' "C_SEQCNT
    'Seq. Read Fetch'  "C_SEQFETCH
    'Seq. Read Rows'   "C_SEQROW
    'Insert'           "C_INSCNT
    'Update'           "C_UPDCNT
    'Delete'           "C_DELCNT
    'Execute SQL'
    into header separated by ZPAR2.
transfer header to OUTFILE.

perform get_table_statistic.

*LOOP AT TABLE_CALLS.
*  move-corresponding table_calls to calc_values.
*                                       "calculate # of modifies
*    calc_values-modifies = table_calls-updcnt +
*                           table_calls-inscnt.
*                                       "calculate # of total requests
*    calc_values-total    = table_calls-dircnt +
*                           table_calls-seqcnt +
*                           table_calls-delcnt +
*                           calc_values-modifies.
*    calc_values-fails    = table_calls-updfail +
*                           table_calls-delfail +
*                           table_calls-insfail +
*                           table_calls-dirfail.
*    calc_values-changes  = calc_values-modifies +
**                        calc_values-fails    +
*                           table_calls-delcnt.
*
*    if calc_values-total <> 0.
*       calc_values-ratio    = ( calc_values-changes / calc_values-total )
*                                * 100 .
*    else.
*       calc_values-ratio    = 0.
*    endif.
*                                       "calculate # of database calls
*    calc_values-dbcalls = table_calls-upd_prep +
*                           table_calls-upd_exec +
*                           table_calls-del_prep +
*                           table_calls-del_exec +
*                           table_calls-ins_prep +
*                           table_calls-ins_exec +
*                           table_calls-dir_prep +
*                             table_calls-dir_open +
*                             table_calls-dir_fetch +
*                             table_calls-seq_prep +
*                             table_calls-seq_open +
*                             table_calls-seq_fetch.
*
*    calc_values-prepares = table_calls-upd_prep +
*                           table_calls-del_prep +
*                           table_calls-ins_prep +
*                           table_calls-dir_prep +
*                             table_calls-seq_prep .
*    calc_values-rows = table_calls-dir_fetch +
*                       table_calls-upd_row +
*                       table_calls-del_row +
*                       table_calls-ins_row +
*                       table_calls-seq_row +
*                       table_calls-load_row.
*    "calculate amount of time spent for DB access
**    calc_values-dbcalls =  calc_values-open +
**                           calc_values-fetch.
*
*    C_TOTAL    = CALC_VALUES-TOTAL.
*    C_MODIFIES = CALC_VALUES-CHANGES.
*    C_FAILS    = CALC_VALUES-FAILS.
*    C_DBCALLS  = CALC_VALUES-DBCALLS.
*    C_PREPARES = CALC_VALUES-PREPARES.
*    C_ROWS     = CALC_VALUES-ROWS.
*    C_DIRCNT   = TABLE_CALLS-DIRCNT.
*    C_DIRFETCH = TABLE_CALLS-DIR_FETCH.
*    C_DIRROW   = TABLE_CALLS-DIR_FETCH.
*    C_SEQCNT   = TABLE_CALLS-SEQCNT.
*    C_SEQFETCH = TABLE_CALLS-SEQ_FETCH.
*    C_SEQROW   = TABLE_CALLS-SEQ_ROW.
*    C_UPDCNT   = TABLE_CALLS-UPDCNT.
*    C_INSCNT   = TABLE_CALLS-INSCNT.
*    C_DELCNT   = TABLE_CALLS-DELCNT.
*
*    concatenate
*    SY-SYSID
*    CDATE
*    TABLE_CALLS-TNAME
*    C_TOTAL
*    C_MODIFIES
*    C_FAILS
*    C_DBCALLS
*    C_PREPARES
*    C_ROWS
*    C_DIRCNT
*    C_DIRFETCH
*    C_SEQCNT
*    C_SEQFETCH
*    C_SEQROW
*    C_INSCNT
*    C_UPDCNT
*    C_DELCNT
*    TABLE_CALLS-SQL
*    into v_record separated by ZPAR2.
*
*    transfer v_record to OUTFILE.
*    append calc_values.
*
*ENDLOOP.



CLOSE DATASET OUTFILE.


NEWDATE = CDATE - 730.
CONCATENATE PPATH_1 CDATE SY-SYSID FILE2 INTO OUTFILE2.
OPEN DATASET OUTFILE2 FOR OUTPUT IN TEXT MODE encoding UTF-8.
CONCATENATE 'SYSTEM_DATE' 'SYSTEM_TIME' INTO header separated by ZPAR2.
transfer header to OUTFILE2.

WHILE NEWDATE <= CDATE.
"    WRITE:/ NEWDATE, ';', '00:00:00'.
    concatenate NEWDATE '00:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '01:00:00'.
    concatenate NEWDATE '01:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '02:00:00'.
    concatenate NEWDATE '02:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '03:00:00'.
    concatenate NEWDATE '03:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '04:00:00'.
    concatenate NEWDATE '04:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '05:00:00'.
    concatenate NEWDATE '05:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '06:00:00'.
    concatenate NEWDATE '06:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '07:00:00'.
    concatenate NEWDATE '07:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '08:00:00'.
    concatenate NEWDATE '08:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '09:00:00'.
    concatenate NEWDATE '09:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '10:00:00'.
    concatenate NEWDATE '10:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '11:00:00'.
    concatenate NEWDATE '11:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '12:00:00'.
    concatenate NEWDATE '12:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '13:00:00'.
    concatenate NEWDATE '13:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '14:00:00'.
    concatenate NEWDATE '14:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '15:00:00'.
    concatenate NEWDATE '15:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '16:00:00'.
    concatenate NEWDATE '16:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '17:00:00'.
    concatenate NEWDATE '17:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '18:00:00'.
    concatenate NEWDATE '18:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '19:00:00'.
    concatenate NEWDATE '19:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '20:00:00'.
    concatenate NEWDATE '20:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '21:00:00'.
    concatenate NEWDATE '21:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '22:00:00'.
    concatenate NEWDATE '22:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
"    WRITE:/ NEWDATE, ';', '23:00:00'.
    concatenate NEWDATE '23:00:00' into v_record separated by ZPAR2.
    transfer v_record to OUTFILE2.
    NEWDATE = NEWDATE + 1.
ENDWHILE.
CLOSE DATASET OUTFILE2.


form get_calc_values.
        call function 'SAPWL_TABSTAT_GET_STATISTIC'
           exporting
                periodtype         = period
                hostid             = sy-host
                instanceno         = sysno
                startdate          = startday
           importing
                statdate           = CDATE
"                stattime           = statitime
           tables
                table_calls        = buffered_tables
           exceptions
                unknown_periodtype = 1
                no_data_found      = 2
                others             = 3.
      case sy-subrc.
        when 1.

        when 2.
          message e759.
      endcase.

  data: size like sy-tabix.
  refresh calc_values. clear calc_values.
  describe table buffered_tables_64 lines size.
  if size = 0.
    loop at buffered_tables.
      move-corresponding buffered_tables to buffered_tables_64.
      append buffered_tables_64.
    endloop.
  endif.
  loop at buffered_tables_64.
    if buffered_tables_64-buf = 'R' or buffered_tables_64-buf = 'X'.
      buffered_tables_64-buf = 'F'.
    elseif buffered_tables_64-buf = ' '.
      buffered_tables_64-buf = 'N'.
    elseif buffered_tables_64-buf = 'S'.
      buffered_tables_64-buf = 'P'.
    endif.
    move-corresponding buffered_tables_64 to calc_values.
                                       "calculate # of modifies
    calc_values-modifies = buffered_tables_64-updcnt +
                           buffered_tables_64-inscnt.
                                       "calculate # of total requests
    calc_values-total    = buffered_tables_64-dircnt +
                           buffered_tables_64-seqcnt +
                           buffered_tables_64-delcnt +
                           calc_values-modifies.
    calc_values-fails    = buffered_tables_64-updfail +
                           buffered_tables_64-delfail +
                           buffered_tables_64-insfail +
                           buffered_tables_64-dirfail.
    calc_values-changes  = calc_values-modifies +
*                        calc_values-fails    +
                           buffered_tables_64-delcnt.

    if calc_values-total <> 0.
       calc_values-ratio    = ( calc_values-changes / calc_values-total )
                                * 100 .
    else.
       calc_values-ratio    = 0.
    endif.
                                       "calculate # of database calls
    calc_values-dbcalls = buffered_tables_64-upd_prep +
                           buffered_tables_64-upd_exec +
                           buffered_tables_64-del_prep +
                           buffered_tables_64-del_exec +
                           buffered_tables_64-ins_prep +
                           buffered_tables_64-ins_exec +
                           buffered_tables_64-dir_prep +
                             buffered_tables_64-dir_open +
                             buffered_tables_64-dir_fetch +
                             buffered_tables_64-seq_prep +
                             buffered_tables_64-seq_open +
                             buffered_tables_64-seq_fetch.

    calc_values-prepares = buffered_tables_64-upd_prep +
                           buffered_tables_64-del_prep +
                           buffered_tables_64-ins_prep +
                           buffered_tables_64-dir_prep +
                             buffered_tables_64-seq_prep .


* REMOVED BUFFER CALLS (ALREADY INCLUDED IN SEQU. CALLS)     GD 1995062
*                          buffered_tables-load_prep +
*                          buffered_tables-load_open +
*                          buffered_tables-load_fetch.
                                       " calculate opens & fetches
*   calc_values-open = buffered_tables-dir_open +
*                       buffered_tables-seq_open.
*    calc_values-fetch = buffered_tables-dir_fetch +
*                        buffered_tables-seq_fetch.
                                       "calculate # of affected rows
    calc_values-rows = buffered_tables_64-dir_fetch +
                       buffered_tables_64-upd_row +
                       buffered_tables_64-del_row +
                       buffered_tables_64-ins_row +
                       buffered_tables_64-seq_row +
                       buffered_tables_64-load_row.
    "calculate amount of time spent for DB access
*    calc_values-dbcalls =  calc_values-open +
*                           calc_values-fetch.
    append calc_values.
  endloop.
endform.

FORM get_table_statistic.
 refresh direc_table. clear direc_table.
      call function 'SAPWL_TABSTAT_GET_DIRECTORY'
          exporting
               periodtype         =  period
*               HOSTID             =
*               INSTNOCHAR         = '  '
               startdate          =  startday
           tables
                directory          = direc_table
           exceptions
                directory_is_empty = 1
                others             = 2.
      if sy-subrc eq 0.
         clear buffered_tables. refresh buffered_tables.
         loop at direc_table.

               call function 'SAPWL_TABSTAT_GET_STATISTIC'
               exporting
                    periodtype         = period
                    hostid             = direc_table-hostid
                    instanceno         = direc_table-instno
                    startdate          = startday
               importing
                    statdate           = statidate
                    stattime           = statitime
               tables
                    table_calls        = buffered_tables
               exceptions
                    unknown_periodtype = 1
                    no_data_found      = 2
                    others             = 3.

              data: size like sy-tabix.
              refresh calc_values. clear calc_values.
              describe table buffered_tables_64 lines size.
              if size = 0.
                 loop at buffered_tables.
                      move-corresponding buffered_tables to buffered_tables_64.
                      append buffered_tables_64.
                      endloop.
              endif.
              loop at buffered_tables_64.
                  if buffered_tables_64-buf = 'R' or buffered_tables_64-buf = 'X'.
                        buffered_tables_64-buf = 'F'.
                  elseif buffered_tables_64-buf = ' '.
                        buffered_tables_64-buf = 'N'.
                  elseif buffered_tables_64-buf = 'S'.
                        buffered_tables_64-buf = 'P'.
                  endif.
                  move-corresponding buffered_tables_64 to calc_values.
                                       "calculate # of modifies
                  calc_values-modifies = buffered_tables_64-updcnt +
                                         buffered_tables_64-inscnt.
                                       "calculate # of total requests
                  calc_values-total    = buffered_tables_64-dircnt +
                                         buffered_tables_64-seqcnt +
                                         buffered_tables_64-delcnt +
                                         calc_values-modifies.
                  calc_values-fails    = buffered_tables_64-updfail +
                                         buffered_tables_64-delfail +
                                         buffered_tables_64-insfail +
                                         buffered_tables_64-dirfail.
                  calc_values-changes  = calc_values-modifies +
*                        calc_values-fails    +
                                         buffered_tables_64-delcnt.

                  if calc_values-total <> 0.
                        calc_values-ratio    = ( calc_values-changes / calc_values-total )
                                * 100 .
                  else.
                        calc_values-ratio    = 0.
                  endif.
                                       "calculate # of database calls
                  calc_values-dbcalls = buffered_tables_64-upd_prep +
                                        buffered_tables_64-upd_exec +
                                        buffered_tables_64-del_prep +
                                        buffered_tables_64-del_exec +
                                        buffered_tables_64-ins_prep +
                                        buffered_tables_64-ins_exec +
                                        buffered_tables_64-dir_prep +
                                        buffered_tables_64-dir_open +
                                        buffered_tables_64-dir_fetch +
                                        buffered_tables_64-seq_prep +
                                        buffered_tables_64-seq_open +
                                        buffered_tables_64-seq_fetch.

                  calc_values-prepares = buffered_tables_64-upd_prep +
                                        buffered_tables_64-del_prep +
                                        buffered_tables_64-ins_prep +
                                        buffered_tables_64-dir_prep +
                                        buffered_tables_64-seq_prep .


                  calc_values-rows = buffered_tables_64-dir_fetch +
                                     buffered_tables_64-upd_row +
                                     buffered_tables_64-del_row +
                                     buffered_tables_64-ins_row +
                                     buffered_tables_64-seq_row +
                                     buffered_tables_64-load_row.

                  C_TOTAL    = CALC_VALUES-TOTAL.
                  C_MODIFIES = CALC_VALUES-CHANGES.
                  C_FAILS    = CALC_VALUES-FAILS.
                  C_DBCALLS  = CALC_VALUES-DBCALLS.
                  C_PREPARES = CALC_VALUES-PREPARES.
                  C_ROWS     = CALC_VALUES-ROWS.
                  C_DIRCNT   = buffered_tables_64-DIRCNT.
                  C_DIRFETCH = buffered_tables_64-DIR_FETCH.
                  C_DIRROW   = buffered_tables_64-DIR_FETCH.
                  C_SEQCNT   = buffered_tables_64-SEQCNT.
                  C_SEQFETCH = buffered_tables_64-SEQ_FETCH.
                  C_SEQROW   = buffered_tables_64-SEQ_ROW.
                  C_UPDCNT   = buffered_tables_64-UPDCNT.
                  C_INSCNT   = buffered_tables_64-INSCNT.
                  C_DELCNT   = buffered_tables_64-DELCNT.

                  concatenate
                  SY-SYSID
                  startday
                  direc_table-hostid
                  buffered_tables_64-TNAME
                  C_TOTAL
                  C_MODIFIES
                  C_FAILS
                  C_DBCALLS
                  C_PREPARES
                  C_ROWS
                  C_DIRCNT
                  C_DIRFETCH
                  C_SEQCNT
                  C_SEQFETCH
                  C_SEQROW
                  C_INSCNT
                  C_UPDCNT
                  C_DELCNT
                  buffered_tables_64-SQL
                  into v_record separated by ZPAR2.

                  transfer v_record to OUTFILE.

                  append calc_values.
              endloop.
         endloop.
      endif.
ENDFORM.

"Prepare Output Path"
FORM f_set_output_path.
     DATA l_host     TYPE paramvalue-PARAMVALUE.
     IF ppath_1 IS INITIAL.
*  SELECT SINGLE intfhost
*      INTO l_host
*      FROM zisba0003
*      WHERE sysid = sy-sysid.
"   SELECT SINGLE PARAMVALUE INTO l_host FROM PARAMVALUE WHERE FILEPARAM = 'Z_EAI'.
        SELECT SINGLE PARAMVALUE INTO l_host FROM PARAMVALUE.
        IF sy-subrc <> 0 OR l_host IS INITIAL.
            MESSAGE 'NO SERVER NAME DEFINED FOR Z_EAI' TYPE 'E'.
        ENDIF.
        CONCATENATE 'R:\usr\sap\UD2\DVEBMGS12\data' '\' INTO PPATH_1.
"        CONCATENATE '\\' l_host '\Batch\RCABA001\CRM\' INTO ppath_1.
    ENDIF.
ENDFORM.

 

1 Comment
You must be Logged on to comment or reply to a post.
  • Hello Chi Chiu Wong,

    Thank you for sharing such nice and good looking dashboards to make our life easier when analyzing performance problems.