DB Space and Record Count of ODSOs

former_member200038 · ‎2016 May 06

Hi,

Using TCODEs to get the DB table size one by one is very tedious job when you have more number of DSOs in Oracle or SQL DB. Usually this kind of information is required during DB upgrade, performance management, any other system performance analysis etc. .

But there is no other way to get the no of record count size of the ODSOs in a single go.

Here is the small snippet to get the size and record of the active table as well as change log table in a single go. In case required this can be enhanced as per requirement.

*&---------------------------------------------------------------------*

*& Report ZODSO_PROPERTIES

*&

*&---------------------------------------------------------------------*

*& Description : store the ODSO properties e.g. number of records , query name

*&

*&---------------------------------------------------------------------*

REPORT ZODSO_PROPERTIES.

*&---------------------------------------------------------------------*

*& Types declarations

*&---------------------------------------------------------------------*

TYPES :BEGIN OF ty_line,

line_name TYPE c LENGTH 16,

line_arec TYPE i,

line_asize TYPE SYCCURS,

line_crec TYPE i,

line_csize TYPE SYCCURS,

END OF ty_line.

TYPES ty_odso TYPE TABLE OF ty_line.

* &---------------------------------------------------------------------*

* & ALV display declaration

* &---------------------------------------------------------------------*

DATA: field_catalog TYPE SLIS_T_FIELDCAT_ALV,

line_fcat LIKE LINE OF field_catalog,

alv_layout TYPE SLIS_LAYOUT_ALV,

report_name TYPE sy-repid .

*&---------------------------------------------------------------------*

*& Intialization for Data Dictionary Types

*&---------------------------------------------------------------------*

DATA : wa_line TYPE ty_line,

itab_odso_cube TYPE ty_odso,

ods_name TYPE RSODSTECH,

clog_name TYPE RSDODSOBJECT,

tab_name TYPE TABNAME.

" tab_size LIKE SY-CCURS.

*&---------------------------------------------------------------------*

*& Variable declarations(Local and Global)

*&---------------------------------------------------------------------*

DATA: lv_records TYPE I,

lv_tab_size LIKE SY-CCURS,

lv_chg_log_records TYPE I,

gty_exp TYPE REF TO cx_root,

gty_exp_name TYPE string.

DATA: lv_logsys TYPE TBDLS-LOGSYS.

DATA : rfc_ty_str LIKE RFCSI,

gty_db_name TYPE RFCSI-RFCDBSYS.

*&---------------------------------------------------------------------*

*& Search the default logical name of the logged in system(Include Type)

*&---------------------------------------------------------------------*

TRY .

CALL FUNCTION 'OWN_LOGICAL_SYSTEM_GET'

IMPORTING

OWN_LOGICAL_SYSTEM = lv_logsys

EXCEPTIONS

OWN_LOGICAL_SYSTEM_NOT_DEFINED = 1

OTHERS = 2.

IF SY-SUBRC <> 0.

WRITE: 'No Logical System name found!!' .

NEW-LINE.

ENDIF.

WRITE: 'Logical System name is ::' ,lv_logsys.

NEW-LINE.

ULINE.

CATCH cx_root INTO gty_exp."For catching catch able exceptions of all kinds

NEW-LINE.

gty_exp_name = gty_exp->get_text( ).

WRITE : /'Error ::' , gty_exp_name.

CLEANUP.

NEW-LINE.

ENDTRY.

**&---------------------------------------------------------------------*

**& Display System Properties (Include Type)

**&---------------------------------------------------------------------*

CALL FUNCTION 'RFC_SYSTEM_INFO' " Find the System Properties

IMPORTING

RFCSI_EXPORT = rfc_ty_str.

** CURRENT_RESOURCES =

** MAXIMAL_RESOURCES =

** RECOMMENDED_DELAY =

.

gty_db_name = rfc_ty_str-RFCDBSYS. "backend DB.

NEW-LINE.

*ULINE.

*WRITE : 'System Status'.

*WRITE 😕 'Name of the SAP System ::',rfc_ty_str-RFCSYSID.

*WRITE 😕 'Release Status of SAP System ::',rfc_ty_str-RFCSAPRL.

*WRITE 😕 'Central Database System ::',rfc_ty_str-RFCDBSYS.

*WRITE 😕 'Database host name ::',rfc_ty_str-RFCDBHOST.

*WRITE 😕 'Operating System of Application Server ::' ,rfc_ty_str-RFCOPSYS.

*WRITE 😕 'RFC Protocol Version ::',rfc_ty_str-RFCPROTO.

*Uline.

*&---------------------------------------------------------------------*

*& Take DSO name as a select option

*&---------------------------------------------------------------------*

TRY .

SELECT-OPTIONS sel_cat1 FOR ods_name NO INTERVALS.

LOOP AT sel_cat1.

CONCATENATE '/BIC/A' sel_cat1-low '00' INTO ods_name. "active table name

SELECT COUNT( * ) FROM (ods_name) INTO lv_records . "select number of records into local variable

IF sy-subrc EQ '8'.

"exception

ENDIF.

"logic for finding the database size

tab_name = ods_name. "input for FM

IF gty_db_name EQ 'MSSQL'.

"LOOP AT opt3.

"tab_name = opt3-low.

CALL FUNCTION 'GET_TABLE_SIZE_MSS'

EXPORTING

TABNAME = tab_name

"DATA_SPACE_ONLY =

IMPORTING

TABSIZE = lv_tab_size

EXCEPTIONS

NO_DATABASE_TABLE = 1

OTHERS = 2.

IF SY-SUBRC <> 0.

* Implement suitable error handling here

ENDIF.

"WRITE : / tab_name UNDER 'Table Name',tab_size UNDER 'Table size '.

"CLEAR tab_size.

"ENDLOOP.

*ENDIF.

ELSEIF gty_db_name EQ 'ORACLE'.

"LOOP AT opt3.

" tab_name = opt3-low.

CALL FUNCTION 'GET_TABLE_SIZE_ORA'

EXPORTING

TABNAME = tab_name

** DATA_SPACE_ONLY =

** SIGNI = 3

IMPORTING

TABSIZE = lv_tab_size

EXCEPTIONS

ERROR = 1

OTHERS = 2.

IF SY-SUBRC <> 0.

"exception

ENDIF.

ELSE.

"lv_tab_size = 'DB Not supported'.

ENDIF.

"End of Active table size logic

wa_line-line_name = sel_cat1-low.

wa_line-line_arec = lv_records.

wa_line-line_asize = lv_tab_size.

CLEAR : lv_tab_size, tab_name. "clear the active table tabsize

clog_name = sel_cat1-low. "type I_ODSOBJECT

CALL FUNCTION 'RSAR_CHANGELOG_NAME_GET' "to get he change log table name

EXPORTING

I_ODSOBJECT = clog_name

I_LOGSYS = lv_logsys

IMPORTING

* E_ODSNAME = "export datasource name

E_ODSNAME_DB = ods_name "changelog table name

* E_ODS_TABTYPE = "table for changelog

EXCEPTIONS

PARAMETER_FAILURE = 1

NO_ODS_FOUND = 2

NO_FIELDS_TO_ODS = 3

OTHERS = 4.

IF SY-SUBRC <> 0.

"exception

ENDIF.

SELECT COUNT( * ) FROM (ods_name) INTO lv_chg_log_records. "select the number of the change log records

IF sy-subrc EQ '8'.

"exception

ENDIF.

wa_line-line_crec = lv_chg_log_records.

"Logic for size of Change Log

tab_name = ods_name. "input for FM

IF gty_db_name EQ 'MSSQL'.

CALL FUNCTION 'GET_TABLE_SIZE_MSS'

EXPORTING

TABNAME = tab_name

"DATA_SPACE_ONLY =

IMPORTING

TABSIZE = lv_tab_size

EXCEPTIONS

NO_DATABASE_TABLE = 1

OTHERS = 2.

IF SY-SUBRC <> 0.

* Implement suitable error handling here

ENDIF.

"WRITE : / tab_name UNDER 'Table Name',tab_size UNDER 'Table size '.

"CLEAR lv_tab_size.

"ENDLOOP.

*ENDIF.

ELSEIF gty_db_name EQ 'ORACLE'.

CALL FUNCTION 'GET_TABLE_SIZE_ORA'

EXPORTING

TABNAME = tab_name

** DATA_SPACE_ONLY =

** SIGNI = 3

IMPORTING

TABSIZE = lv_tab_size

EXCEPTIONS

ERROR = 1

OTHERS = 2.

IF SY-SUBRC <> 0.

ENDIF.

"End of Active table size logic

else.

"Db not supported

ENDIF.

wa_line-line_csize = lv_tab_size.

APPEND wa_line TO itab_odso_cube. " insert LINE to internal table

CLEAR: lv_records,lv_chg_log_records. " clear counters

ENDLOOP.

PERFORM createALV.

PERFORM displayALV.

REFRESH itab_odso_cube. "clear the table so that same table can be used for Cube prorties but retail memory

CATCH cx_root INTO gty_exp.

"For catching catchable exceptions of all kinds

gty_exp_name = gty_exp->get_text( ).

WRITE : /'Error ::' , gty_exp_name.

CLEANUP.

ENDTRY.

*&---------------------------------------------------------------------*

*& Form CREATEALV

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

* --> p1 text

* <-- p2 text

*----------------------------------------------------------------------*

FORM CREATEALV .

clear line_fcat.

line_fcat-FIELDNAME = 'LINE_NAME' ." should be same as your field name and in Upper case

line_fcat-SELTEXT_M = 'DB Table Name' .

line_fcat-COL_POS = 0 .

line_fcat-outputlen ='20'.