Skip to Content

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

)

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