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

*&———————————————————————*

*& 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 SYCCURS,

       lv_chg_log_records  TYPE I,

       gty_exp             TYPE REF  TO cx_root,

       gty_exp_name        TYPE string.

DATA: lv_logsys       TYPE TBDLSLOGSYS.

DATA : rfc_ty_str     LIKE RFCSI,

        gty_db_name    TYPE RFCSIRFCDBSYS.

*&———————————————————————*

*& 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 SYSUBRC <> 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_strRFCDBSYS. “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_cat1low ’00’ INTO ods_name. “active table name

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

       IF sysubrc 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 SYSUBRC <> 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 SYSUBRC <> 0.

           “exception

         ENDIF.

       ELSE.

         “lv_tab_size = ‘DB Not supported’.

       ENDIF.

       “End of Active table size logic

       wa_lineline_name  = sel_cat1low.

       wa_lineline_arec  = lv_records.

       wa_lineline_asize = lv_tab_size.

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

       clog_name = sel_cat1low. “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 SYSUBRC <> 0.

         “exception

       ENDIF.

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

       IF sysubrc EQ ‘8’.

         “exception

       ENDIF.

       wa_lineline_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 SYSUBRC <> 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 SYSUBRC <> 0.

         ENDIF.

         “End of Active table size logic

       else.

         “Db not supported

       ENDIF.

       wa_lineline_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_fcatFIELDNAME = ‘LINE_NAME’ .” should be same as your field name and in Upper case

   line_fcatSELTEXT_M = ‘DB Table Name’ .

   line_fcatCOL_POS = 0 .

   line_fcatoutputlen =’20’.

   APPEND line_fcat to field_catalog.

   clear line_fcat.

   line_fcatFIELDNAME = ‘LINE_AREC’ . ” should be same as your field name and in Upper case

   line_fcatSELTEXT_L = ‘Number of Records(Active Table)’.

   line_fcatCOL_POS = 1 .

   line_fcatoutputlen =’30’.

   APPEND line_fcat to field_catalog.

   clear line_fcat.

   line_fcatFIELDNAME = ‘LINE_ASIZE’ . ” should be same as your field name and in Upper case

   line_fcatSELTEXT_L = ‘Size of Active Data Table(KB)’.

   line_fcatCOL_POS = 2 .

   line_fcatoutputlen =’30’.

   APPEND line_fcat to field_catalog.

   clear line_fcat.

   line_fcatFIELDNAME = ‘LINE_CREC’ . ” should be same as your field name and in Upper case

   line_fcatSELTEXT_L = ‘Number Of Records(Change Log)’.

   line_fcatCOL_POS = 3 .

   line_fcatoutputlen =’30’.

   APPEND line_fcat to field_catalog.

   clear line_fcat.

   line_fcatFIELDNAME = ‘LINE_CSIZE’ . ” should be same as your field name and in Upper case

   line_fcatSELTEXT_L = ‘Size Of change Log(KB)’.

   line_fcatCOL_POS = 4 .

   line_fcatoutputlen =’30’.

   APPEND line_fcat to field_catalog.

   clear line_fcat.

ENDFORM.                    ” CREATEALV

*&———————————————————————*

*&      Form  DISPLAYALV

*&———————————————————————*

*       text

*———————————————————————-*

*  –>  p1        text

*  <–  p2        text

*———————————————————————-*

FORM DISPLAYALV .

     report_name = syrepid.

CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY’

  EXPORTING

    I_CALLBACK_PROGRAM                = REPORT_NAME

    IT_FIELDCAT                       = FIELD_CATALOG

     I_SAVE                            = ‘X’

   TABLES

     T_OUTTAB                          = itab_odso_cube

  EXCEPTIONS

    PROGRAM_ERROR                     = 1

    OTHERS                            = 2.

IF SYSUBRC <> 0.

   MESSAGE ID SYMSGID TYPE SYMSGTY NUMBER SYMSGNO WITH SYMSGV1 SYMSGV2 SYMSGV3 SYMSGV4.

ENDIF.

ENDFORM.

Note : Code is optimized for ORACLE and SQL DB only.

Regards,

Praveen

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply