Skip to Content

This is the second blog on my memory management & analysis series where today we will be determining which table is occupying how much space in HANA and which one tops the result.

The custom ABAP program will be utilizing SAP HANA SQL statement & following system views to achieve this mission:

  • M_CS_TABLES is the standard SAP HANA table which provides runtime data for column store tables. (You can add similarly on M_RS_TABLES)
  • DD02T table to get description of tables to get better understanding on type of information stored in that table.
  • TABLE_GROUPS system view to get an overview of table groups relationships.

HANA SQL code which needs to be replicated in ABAP program:

select top 2 a.table_name, sum(a.memory_size_in_total)/1024/1024 as size, b.ddtext,c.subtype from m_cs_tables as a
inner join sapbiw.dd02t as b on b.tabname = a.table_name
inner join table_groups as c on c.table_name = a.table_name and c.schema_name = a.schema_name
where a.loaded <> 'NO'
and  b.DDLANGUAGE = 'E'
and  b.as4local = 'A'.
group by a.table_name, b.ddtext , c.subtype order by 3 desc

In TABLE GROUPS, the list of possible values of SUBTYPE can be extensive, so we are going to turn them into meaningful category.

CUBE:

  • FACT_IMO
  • FACT_F
  • FACT_E
  • DIM

DSO

  • ACTIVE
  • QUEUE

MASTER

  • Y
  • T
  • Q
  • P
  • X
  • H
  • J
  • I
  • K
  • SID

 

For running HANA SQL via ABAP, we are going to use following classes:

  • CL_SQL_STATEMENT for creating SQL statement.
  • CL_SQL_RESULT_SET for storing resulting Set of an SQL Query
  • CL_SQL_CONNECTION for establishing Database Connection

You may go to class documentation to get familiarized with its significance and methods.

 

We can use below Kernel C system call function to get SCHEMA details but as already know CS Tables belong to SAPBIW schema, so we are not using it.

CALL ‘C_DB_FUNCTION’ ID ‘FUNCTION’ FIELD ‘DB_DBINFO’

ID ‘DBSCHEMA’ FIELD G_SCHEMA.

G_SCHEMA can be declared of TYPE MSSSCHEMA to store the value given by C_DB_FUNCTION

 

From M_CS_TABLES, field LOADED: Flag to show how many columns of table are loaded in memory (NO, PARTIALLY, and FULL); As we are taking as LOADED not equals to NO, the query via ABAP will return Partially and full loaded tables in HANA memory.

Please note that we have divided “memory_size_in_total” with 1024^2 to get it converted into MB. If you want you can divide it less or more to get result in desired memory unit.

Now, we are going to implement the given SQL query into ABAP via class CL_SQL_STATEMENT which contains methods for executing SQL statements.

From documentation of Class CL_SQL_STATEMENT:

“When an instance of this class – a statement object – is created by a simple CREATE OBJECT command, the SQL statements to be executed using this object (by calling the relevant methods) are executed using the default SAP system connection. A statement object can also be created if the method CREATE_STATEMENT of a connection object of the class CL_SQL_STATEMENT is called. In this case, all SQL statements that are executed using this statement object run in the context of the data connection that is represented by the creating connection object.

Use the method EXECUTE_DDL to execute DDL commands (such as CREATE, DROP, ALTER, and so on). EXECUTE_UPDATE is used to execute DML commands (such as INSERT, UPDATE  DELETE), and the method EXECUTE_QUERY is used to execute queries (SELECT statements).

EXECUTE_PROCEDURE is used to call database procedures in a consistent manner for all supported DB systems.

All SQL statements, except DDL commands, can contain placeholders for input variables. “?” is used in the statement text as the placeholder symbol. Before a statement is executed using the methods mentioned above, all placeholders contained in the statement must be bound to relevant database objects. This binding is performed by:

  • The method SET_PARAM, which needs to be called once for each placeholder
  • The method SET_PARAM_STRUCT, which binds the components of a structure in sequence to all of the placeholders in a statement
  • The method SET_PARAM_TABLE, which binds the components of an internal table in sequence to all of the placeholders of a statement. This is useful for the sources of writing SQL statements.

The binding passes a reference to the data object, such as an ABAP variable. The current value of the data object is used when the SQL statement is executed.”

Below is the ABAP program implementing required HANA SQL query:

REPORT ZTOP_TABLES_IN_MEMORY.
*&---------------------------------------------------------------------*
* Program to find TOP N tables loaded in HANA Memory
* By: Sushil Kumar Goel
*----------------------------------------------------------------------*


*&---------------------------------------------------------------------*
* Start of ALV Declarations
*----------------------------------------------------------------------*
* Types Pools
TYPE-POOLS:
   SLIS.
* Types
TYPES:
  T_FIELDCAT TYPE SLIS_FIELDCAT_ALV.
* Workareas
DATA:
  W_FIELDCAT TYPE T_FIELDCAT.
* Internal Tables
DATA:
  I_FIELDCAT TYPE STANDARD TABLE OF T_FIELDCAT.

**********************************************************************************
TYPES:
  TYP_LARGEDEC(11) TYPE P DECIMALS 2,
  BEGIN OF TY_RESULT,
*    SCHEMA_NAME TYPE SOBJ_NAME,
    TABLE_NAME  TYPE SOBJ_NAME,
    SUBTYPE     TYPE SOBJ_NAME,
    DISK_SIZE   TYPE TYP_LARGEDEC,
*    GOURP_NAME  TYPE SOBJ_NAME,
    DDTEXT      TYPE CHAR100,
  END OF TY_RESULT,
TY_T_RESULTS TYPE TABLE OF TY_RESULT.

DATA: IT_RESULTS TYPE TY_T_RESULTS.
FIELD-SYMBOLS: <FS_RESULTS> TYPE TY_RESULT.

DATA:
*  G_SCHEMA        TYPE MSSSCHEMA,
  R_CX_SQL        TYPE REF TO CX_SQL_EXCEPTION,
*  G_REC_2_PROCESS TYPE I,
*  G_REC_COMP      TYPE I,
  L_STMT        TYPE STRING,
  L_WHERE       TYPE STRING,
  COUNT(5)      TYPE C,
  LR_SQL        TYPE REF TO CL_SQL_STATEMENT,
  LR_RES        TYPE REF TO CL_SQL_RESULT_SET,
  LR_DATA       TYPE REF TO DATA,
  R_CON_DEFAULT TYPE REF TO CL_SQL_CONNECTION.

CONSTANTS:  C_QUOTE(1)   VALUE '''',
            C_CHECKED(1) VALUE 'X'.


**********************************************************************************
PARAMETERS: P_COUNT TYPE I DEFAULT 100.


SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.
SELECTION-SCREEN SKIP 1.


PARAMETERS: P_CUBE  RADIOBUTTON GROUP RAD1. "Cube
SELECTION-SCREEN SKIP 1.

PARAMETERS: P_DSO  RADIOBUTTON GROUP RAD1. "DSO
SELECTION-SCREEN SKIP 1.

PARAMETERS: P_MASTER  RADIOBUTTON GROUP RAD1. "Master Data
SELECTION-SCREEN SKIP 1.

PARAMETERS: P_ALL RADIOBUTTON GROUP RAD1. "Among all
SELECTION-SCREEN SKIP 1.
SELECTION-SCREEN END OF BLOCK B1.

AT SELECTION-SCREEN.
  IF P_COUNT > 500.
    MESSAGE E003(RSDA) WITH 'Max 500 entries allowed.'.
  ENDIF.

COUNT = P_COUNT.

START-OF-SELECTION.

L_STMT = 'select top'.
CONCATENATE L_STMT COUNT INTO L_STMT SEPARATED BY SPACE.
CONCATENATE L_STMT 'a.table_name, c.subtype, sum(a.memory_size_in_total)/1024/1024 as size, b.ddtext from m_cs_tables as a' INTO L_STMT SEPARATED BY SPACE.
CONCATENATE L_STMT 'inner join sapbiw.dd02t as b on b.tabname = a.table_name' INTO L_STMT SEPARATED BY SPACE.
CONCATENATE L_STMT 'inner join table_groups as c on c.table_name = a.table_name and c.schema_name = a.schema_name' INTO L_STMT SEPARATED BY SPACE.
CONCATENATE L_STMT 'where a.loaded <> ' INTO L_STMT SEPARATED BY SPACE.
CONCATENATE L_STMT  C_QUOTE 'NO' C_QUOTE INTO L_STMT.
CONCATENATE L_STMT 'and b.DDLANGUAGE = ' C_QUOTE INTO L_STMT SEPARATED BY SPACE.
CONCATENATE L_STMT 'E' C_QUOTE INTO L_STMT.
CONCATENATE L_STMT 'and b.as4local = ' C_QUOTE  INTO L_STMT SEPARATED BY SPACE.
CONCATENATE L_STMT 'A' C_QUOTE INTO L_STMT.


IF P_CUBE IS NOT INITIAL.

  CONCATENATE '(' C_QUOTE 'FACT_IMO' C_QUOTE ','
                  C_QUOTE 'FACT_F' C_QUOTE ','
                  C_QUOTE 'FACT_E' C_QUOTE ','
                  C_QUOTE 'DIM' C_QUOTE ')'
  INTO L_WHERE.
  CONCATENATE L_STMT 'and c.subtype in ' L_WHERE INTO L_STMT.

ELSEIF P_DSO IS NOT INITIAL.

  CONCATENATE '(' C_QUOTE 'ACTIVE' C_QUOTE ','
                  C_QUOTE 'QUEUE' C_QUOTE ')'
  INTO L_WHERE.
  CONCATENATE L_STMT 'and c.subtype in ' L_WHERE INTO L_STMT.

ELSEIF P_MASTER IS NOT INITIAL.
*y, t, Q, p, x,h,j,i,k,sid
  CONCATENATE '(' C_QUOTE 'Y' C_QUOTE ','
                  C_QUOTE 'T' C_QUOTE ','
                  C_QUOTE 'Q' C_QUOTE ','
                  C_QUOTE 'P' C_QUOTE ','
                  C_QUOTE 'X' C_QUOTE ','
                  C_QUOTE 'H' C_QUOTE ','
                  C_QUOTE 'J' C_QUOTE ','
                  C_QUOTE 'I' C_QUOTE ','
                  C_QUOTE 'K' C_QUOTE ','
                  C_QUOTE 'SID' C_QUOTE ')'
  INTO L_WHERE.
  CONCATENATE L_STMT 'and c.subtype in ' L_WHERE INTO L_STMT.

ELSE.

ENDIF.


CONCATENATE L_STMT ' group by a.table_name, b.ddtext , c.subtype order by 3 desc' INTO L_STMT.

TRY.
      CREATE OBJECT LR_SQL
        EXPORTING
          CON_REF = R_CON_DEFAULT.

      LR_RES = LR_SQL->EXECUTE_QUERY( L_STMT  ).
      GET REFERENCE OF IT_RESULTS INTO LR_DATA.
      LR_RES->SET_PARAM_TABLE( LR_DATA ).
      LR_RES->NEXT_PACKAGE( ).
      LR_RES->CLOSE( ).

    CATCH CX_SQL_EXCEPTION INTO R_CX_SQL.
      MESSAGE X024(SHDB) WITH R_CX_SQL->SQL_CODE R_CX_SQL->SQL_MESSAGE '' ''.
  ENDTRY.

SORT IT_RESULTS BY TABLE_NAME.
DELETE ADJACENT DUPLICATES FROM IT_RESULTS COMPARING TABLE_NAME.


LOOP AT IT_RESULTS ASSIGNING <FS_RESULTS>.
  IF ( <FS_RESULTS>-SUBTYPE = 'ACTIVE' OR <FS_RESULTS>-SUBTYPE = 'QUEUE' ).
    <FS_RESULTS>-SUBTYPE = 'DSO'.
  ELSEIF ( <FS_RESULTS>-SUBTYPE = 'FACT_IMO' OR <FS_RESULTS>-SUBTYPE = 'FACT_F' OR <FS_RESULTS>-SUBTYPE = 'FACT_E' OR <FS_RESULTS>-SUBTYPE = 'DIM' ).
    <FS_RESULTS>-SUBTYPE = 'CUBE'.
  ELSEIF ( <FS_RESULTS>-SUBTYPE = 'Y' OR <FS_RESULTS>-SUBTYPE = 'T' OR <FS_RESULTS>-SUBTYPE = 'Q' OR <FS_RESULTS>-SUBTYPE = 'P'
    OR <FS_RESULTS>-SUBTYPE = 'X' OR <FS_RESULTS>-SUBTYPE = 'H' OR  <FS_RESULTS>-SUBTYPE = 'J' OR <FS_RESULTS>-SUBTYPE = 'I'
    OR <FS_RESULTS>-SUBTYPE = 'K' OR <FS_RESULTS>-SUBTYPE = 'SID' ).
    <FS_RESULTS>-SUBTYPE = 'MASTER_DATA'.
  ELSE.

  ENDIF.

ENDLOOP.

PERFORM DISPLAY_DATA.

***********************************************************************************

*&--------------------------------------------------------------------*
*&      Form  build_fieldcatlog
*              TABLE_NAME  TYPE SOBJ_NAME,
*              SUBTYPE     TYPE SOBJ_NAME,
*              DISK_SIZE   TYPE TYP_LARGEDEC,
*              DDTEXT      TYPE CHAR100
*&---------------------------------------------------------------------*
FORM BUILD_FIELDCATLOG .
  CLEAR:W_FIELDCAT,I_FIELDCAT[].

  PERFORM BUILD_FCATALOG USING:
        'TABLE_NAME' 'IT_RESULTS' 'TABLE NAME'.

  PERFORM BUILD_FCATALOG USING:
        'SUBTYPE' 'IT_RESULTS' 'SUBTYPE'.

  PERFORM BUILD_FCATALOG USING:
        'DISK_SIZE' 'IT_RESULTS' 'Size (MB)'.

  PERFORM BUILD_FCATALOG USING:
        'DDTEXT' 'IT_RESULTS' 'Description'.

ENDFORM.                    "BUILD_FIELDCATLOG

*&---------------------------------------------------------------------*
*&      Form  BUILD_FCATALOG
*&---------------------------------------------------------------------*
FORM BUILD_FCATALOG USING L_FIELD L_TAB L_TEXT.

  W_FIELDCAT-FIELDNAME      = L_FIELD.
  W_FIELDCAT-TABNAME        = L_TAB.
  W_FIELDCAT-SELTEXT_M      = L_TEXT.

  APPEND W_FIELDCAT TO I_FIELDCAT.
  CLEAR W_FIELDCAT.

ENDFORM.                    " build_fieldcatlog


*&---------------------------------------------------------------------*
*&      Form  DISPLAY_DATA
*&---------------------------------------------------------------------*
FORM DISPLAY_DATA .

  PERFORM BUILD_FIELDCATLOG. "Build Catalog for displaying results

  DATA: L_LAYOUT  TYPE SLIS_LAYOUT_ALV,
        L_PROGRAM TYPE SY-REPID,
        LT_SORT   TYPE SLIS_T_SORTINFO_ALV,
        L_WA_SORT TYPE SLIS_SORTINFO_ALV.

  L_PROGRAM = SY-REPID.

  L_WA_SORT-FIELDNAME = 'DISK_SIZE'.
  L_WA_SORT-DOWN      = 'X'.
  APPEND L_WA_SORT TO LT_SORT.

  L_LAYOUT-COLWIDTH_OPTIMIZE = C_CHECKED.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      I_CALLBACK_PROGRAM = L_PROGRAM
      IS_LAYOUT          = L_LAYOUT
      IT_FIELDCAT        = I_FIELDCAT
*     it_events          = i_events
      IT_SORT            = LT_SORT
    TABLES
      T_OUTTAB           = IT_RESULTS
    EXCEPTIONS
      PROGRAM_ERROR      = 1
      OTHERS             = 2.
  IF SY-SUBRC <> 0.
    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
                WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.


ENDFORM.      "DISPLAY_DATA

 

Output of Report:

 

 

With the help of this program, we can track which top N tables are occupying largest space in HANA Memory and then can be determined for unloading. The unloading of tables will be covered in my next blog.

  • PSA and changelog tables are non-reportable tables, so they should be unloaded from HANA memory to save space on HANA which in turn can be used to increasing performance of system.
  • DSO which are in non-reportable layer can also be unloaded.
  • Large tables can be checked for archiving to freeing HANA space
  • With this we can keep a check on maintenance tasks of BI, like cleanup of PSA & changelog or deletion of old data which is not required anymore after analysis.

Thanks for reading the post.  I will next be writing blog on unloading of tables from HANA to achieve few above listed benefits and will share one real-time example of benefit.

Please feel free to add comments / suggestions.

 

Cheers

Sushil

To report this post you need to login first.

1 Comment

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

Leave a Reply