Skip to Content
Author's profile photo Former Member

SPACE and Memory information using SQL statement

Some SQL statement consolidated to execute the following details for DB running on HANA.

1. GENERAL INFORMATION  – eg DB name, OS User name, Instance name and number, Support Packages, Hosts etc.

2. PATCH HISTORY

3. SPACE INFORMATION

4. MEMORY INFORMATION

_________________________________________________________________________

Select ‘GENERAL INFORMATION:’ NAME,

  ‘ ‘ VALUE

FROM

  DUMMY

UNION ALL

( SELECT

    ‘ ‘,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘Database type’,

    ‘HANA’

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘Evaluation time’,

    TO_CHAR(CURRENT_TIMESTAMP, ‘YYYY/MM/DD HH24:MI:SS’)

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘Evaluation user’,

    CURRENT_USER

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘OS user’,

    VALUE || ‘ (‘ || COUNT(*) || ‘ host’ || CASE WHEN COUNT(*) = 1 THEN ” ELSE ‘s’ END || ‘)’

  FROM

    M_HOST_INFORMATION

  WHERE

    KEY = ‘os_user’

  GROUP BY

    VALUE

  ORDER BY

    VALUE

)

UNION ALL

( SELECT

    ‘Startup time’,

    TO_CHAR(START_TIME, ‘YYYY/MM/DD HH24:MI:SS’)

  FROM

    M_DATABASE

)

UNION ALL

( SELECT

    ‘ ‘,

    TO_CHAR(TO_TIMESTAMP(SUBSTR(VALUE, 1, 19), ‘YYYY-MM-DD HH24:MI:SS’), ‘YYYY/MM/DD

HH24:MI:SS’) || ‘ (‘ || HOST || ‘)’

  FROM

    M_HOST_INFORMATION

  WHERE

    KEY = ‘start_time’

  ORDER BY

    HOST

)

UNION ALL

( SELECT

    ‘Database name’,

    DATABASE_NAME

  FROM

    M_DATABASE

)

UNION ALL

( SELECT

    ‘Instance name’,

    VALUE

  FROM

    M_SYSTEM_OVERVIEW

  WHERE

    NAME = ‘Instance ID’

)

UNION ALL

( SELECT

    ‘Instance number’,

    VALUE

  FROM

    M_SYSTEM_OVERVIEW

  WHERE

    SECTION = ‘System’ AND

    NAME = ‘Instance Number’

)

UNION ALL

( SELECT

    ‘OS Name’,

    VALUE

  FROM

    M_HOST_Information

  WHERE

    Key=’os_name’

)

UNION ALL

( SELECT

    ‘Support package stack’,

    ‘SPS’ || TO_CHAR(CASE

      WHEN REVISION >= 90             THEN 9

      WHEN REVISION BETWEEN 80 AND 89 THEN 8

      WHEN REVISION BETWEEN 70 AND 79 THEN 7

      WHEN REVISION BETWEEN 60 AND 69 THEN 6

      WHEN REVISION BETWEEN 45 AND 59 THEN 5

      WHEN REVISION BETWEEN 28 AND 44 THEN 4

      WHEN REVISION BETWEEN 20 AND 27 THEN 3

      WHEN REVISION BETWEEN 12 AND 19 THEN 2

      WHEN REVISION BETWEEN  1 AND 11 THEN 1

    END)

  FROM

  ( SELECT

      SUBSTR(VALUE, LOCATE(VALUE, ‘.’, 1, 2) + 1, LOCATE(VALUE, ‘.’, 1, 3) – LOCATE(VALUE, ‘.’,

1, 2) – 1) REVISION

    FROM

      M_SYSTEM_OVERVIEW

    WHERE

      SECTION = ‘System’ AND

      NAME = ‘Version’

   )

)

UNION ALL

( SELECT

    ‘Number of hosts’,

    TO_CHAR(COUNT (DISTINCT HOST))

  FROM

    M_SERVICES

)

UNION ALL

( SELECT

    MAP(ROW_NUMBER () OVER (ORDER BY COORDINATOR_TYPE, HOST), 1, ‘Host role’, ‘ ‘),

    RPAD(COORDINATOR_TYPE, 8) || ‘ (‘ || HOST || ‘)’

  FROM

    M_SERVICES

  WHERE

    SERVICE_NAME = ‘indexserver’

  ORDER BY

    COORDINATOR_TYPE,

    HOST

)

UNION ALL

( SELECT

    MAP(ROW_NUMBER () OVER (ORDER BY HOST), 1, ‘Host directory’, ‘ ‘),

    VALUE || ‘ (‘ || HOST || ‘)’

  FROM

    M_HOST_INFORMATION

  WHERE

    KEY = ‘sap_retrieval_path’

  ORDER BY

    HOST

)

UNION ALL

( SELECT

    ‘Timezone’,

    VALUE || ‘ (‘ || COUNT(*) || ‘ host’ || CASE WHEN COUNT(*) = 1 THEN ” ELSE ‘s’ END || ‘)’

  FROM

    M_HOST_INFORMATION

  WHERE

    KEY = ‘timezone_name’

  GROUP BY

    VALUE

  ORDER BY

    VALUE

)

UNION ALL

( SELECT

    ‘Database log mode’,

    VALUE || ‘ (default)’

  FROM

    M_INIFILE_CONTENTS

  WHERE

    SECTION = ‘persistence’ AND

    KEY = ‘log_mode’ AND

    LAYER_NAME = ‘DEFAULT’ AND

    NOT EXISTS ( SELECT * FROM M_INIFILE_CONTENTS WHERE SECTION = ‘persistence’ AND

      KEY = ‘log_mode’ AND LAYER_NAME = ‘SYSTEM’ )

)

UNION ALL

( SELECT

    ‘ ‘,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘PATCH HISTORY:’,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘ ‘,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

    TO_CHAR(INSTALL_TIME, ‘YYYY/MM/DD HH24:MI:SS’),

    VERSION

  FROM

    M_DATABASE_HISTORY

  ORDER BY

    INSTALL_TIME DESC

)

UNION ALL

( SELECT

    ‘ ‘,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘FEATURE INFORMATION:’,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘ ‘,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘Database log mode’,

    VALUE || ‘ (‘ || MAP(LAYER_NAME, ‘HOST’, HOST, ‘SYSTEM’, ‘system wide’) || ‘)’

  FROM

    M_INIFILE_CONTENTS

  WHERE

    SECTION = ‘persistence’ AND

    KEY = ‘log_mode’ AND

    LAYER_NAME IN (‘HOST’, ‘SYSTEM’)

)

UNION ALL

( SELECT

    ‘Automatic log backup’,

    VALUE

  FROM

    M_INIFILE_CONTENTS

  WHERE

    SECTION = ‘persistence’ AND

    KEY = ‘enable_auto_log_backup’

)

UNION ALL

( SELECT

    MAP(ROW_NUMBER () OVER (ORDER BY REPLICATION_PATH DESC), 1, ‘Replication’, ‘ ‘),

    IFNULL(REPLICATION_PATH, ‘No’)

  FROM

  ( SELECT DISTINCT

      REPLICATION_MODE || ‘ (‘ || SITE_NAME || ‘ -> ‘ || SECONDARY_SITE_NAME || ‘)’

REPLICATION_PATH

    FROM

      DUMMY LEFT OUTER JOIN

      M_SERVICE_REPLICATION ON

        1 = 1

    WHERE

      REPLICATION_MODE != ”

  )

  ORDER BY

    REPLICATION_PATH DESC

)

UNION ALL

( SELECT       /* SAP Note 2014148 */

    ‘Query result cache’,

    MAX(VALUE)

  FROM

    M_INIFILE_CONTENTS

  WHERE

    FILE_NAME = ‘indexserver.ini’ AND

    SECTION = ‘cache’ AND

    KEY = ‘resultcache_enabled’

)

UNION ALL

( SELECT

    ‘ ‘,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘SPACE INFORMATION:’,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘ ‘,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘Database size on disk (GB)’,

    Value

  FROM

    M_SYSTEM_OVERVIEW

    Where

    Section = ‘Disk’ and Name = ‘Data’

)

UNION ALL

( SELECT

    ‘Log size on disk (GB)’,

    Value

  FROM

    M_SYSTEM_OVERVIEW

    Where

    Section = ‘Disk’ and Name = ‘Log’

)

UNION ALL

( SELECT

    ‘Data backup size (GB)’,

    LPAD(TO_DECIMAL(SUM(BACKUP_SIZE) / 1024 / 1024 / 1024, 12, 2), 13)

  FROM

  ( SELECT

      BACKUP_ID

    FROM

      M_BACKUP_CATALOG

    WHERE

      UTC_START_TIME =

      ( SELECT

          MAX(UTC_START_TIME) START_TIME

        FROM

          M_BACKUP_CATALOG

        WHERE

          ENTRY_TYPE_NAME = ‘complete data backup’ AND

          STATE_NAME = ‘successful’

      )

  ) MB,

    M_BACKUP_CATALOG_FILES B

  WHERE

    B.BACKUP_ID = MB.BACKUP_ID

)

UNION ALL

( SELECT

    MAP(ROW_NUMBER () OVER (ORDER BY HOST), 1, ‘Row store size total (GB)’, ‘ ‘),

    LPAD(TO_DECIMAL(SUM(ALLOCATED_SIZE) / 1024 / 1024 / 1024, 12, 2), 13) || ‘ (‘ || HOST || ‘)’

  FROM

    M_RS_MEMORY

  GROUP BY

    HOST

  ORDER BY

    HOST

)

UNION ALL

( SELECT

    ‘Column store size total (GB)’,

    LPAD(TO_DECIMAL(SUM(TABLE_SIZE) / 1024 / 1024 / 1024, 12, 2), 13)

  FROM

    M_TABLES

  WHERE

    TABLE_TYPE = ‘COLUMN’

)

UNION ALL

( SELECT

    ‘Row store tables’,

    LPAD(COUNT(*), 10) || ‘    (SAP schema: ‘ || LPAD(SUM(MAP(SUBSTR(SCHEMA_NAME, 1, 3), ‘SAP’,

1, 0)), 6) || ‘)’

  FROM

    TABLES

  WHERE

    TABLE_TYPE = ‘ROW’

)

UNION ALL

( SELECT

    ‘Column store tables’,

    LPAD(COUNT(*), 10) || ‘    (SAP schema: ‘ || LPAD(SUM(MAP(SUBSTR(SCHEMA_NAME, 1, 3), ‘SAP’,

1, 0)), 6) || ‘)’

  FROM

    TABLES

  WHERE

    TABLE_TYPE = ‘COLUMN’

)

UNION ALL

( SELECT

    ‘ ‘,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘MEMORY INFORMATION:’,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

    ‘ ‘,

    ‘ ‘

  FROM

    DUMMY

)

UNION ALL

( SELECT

   ‘Memory’,

   Value

   From

    M_SYSTEM_OVERVIEW

     WHERE

    SECTION = ‘Memory’ AND

    NAME = ‘Memory’

   )

UNION ALL

( SELECT

   ‘Resident Memory used’,

    LPAD(TO_DECIMAL(SUM(USED_PHYSICAL_MEMORY) / 1024 / 1024 / 1024), 5)

    FROM

    M_HOST_RESOURCE_UTILIZATION

)

UNION ALL

( SELECT

   ‘HANA instance memory (used)’,

    LPAD(TO_DECIMAL(INSTANCE_TOTAL_MEMORY_USED_SIZE / 1024 / 1024 / 1024), 5)

    FROM

    M_HOST_RESOURCE_UTILIZATION

  ORDER BY

    HOST

)

Assigned tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.