SAP HANA SQL

SELECT /* [NAME] - HANA_Configuration_MiniChecks_1.00.120+ [DESCRIPTION] - General SAP HANA checks [SOURCE] - SAP Note 1969700 [DETAILS AND RESTRICTIONS] - Checks below "Available as of revisions <rev>" are not possible with earlier revisions - Some checks related to extended statistics server are not possible with standalone statistics server - Long runtimes might be linked to TABLES_MOVED_LOCALLY check - NO_SUBPLAN_SHARING hint required to avoid terminations with "339: invalid number: not a valid number string 'yes' at function __typecast__()" (bug 124947) - The following checks are deprecated and no longer considered: ( SELECT 230, 'MEMORY_USED_CURRENT', 'Current memory utilization (%)', '1999997', '<=', '90', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 231, 'MEMORY_USED_HISTORY', 'Time since memory utilization > 95 % (h)', '1999997', '>=', '12.00', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 240, 'SWAP_SPACE_USED_CURRENT', 'Current swap utilization (GB)', '1999997', '=', '0', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 241, 'SWAP_SPACE_USED_HISTORY', 'Time since swap utilization > 1 GB (h)', '1999997', '>=', '480', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 310, 'MIN_IO_READ_THROUGHPUT_DATA', 'I/O read throughput data min. (MB/s, last day)', '1999930', '>=', '20', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 311, 'AVG_IO_READ_THROUGHPUT_DATA', 'I/O read throughput data avg. (MB/s, last day)', '1999930', '>=', '100', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 320, 'MIN_IO_READ_THROUGHPUT_LOG', 'I/O read throughput log min. (MB/s, last day)', '1999930', '>=', '30', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 321, 'AVG_IO_READ_THROUGHPUT_LOG', 'I/O read throughput log avg. (MB/s, last day)', '1999930', '>=', '100', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 325, 'MIN_IO_WRITE_THROUGHPUT_LOG', 'I/O write throughput log min. (MB/s, last day)', '1999930', '>=', '10', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 326, 'AVG_IO_WRITE_THROUGHPUT_LOG', 'I/O write throughput log avg. (MB/s, last day)', '1999930', '>=', '20', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 328, 'AVG_4K_WRITE_DURATION_LOG', '4 KB write duration log avg. (ms)', '1999930', '<=', '10.00', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 350, 'HIGH_CRIT_SAVEPOINT_PHASE', 'Blocking savepoint phases > 10 s (last day)', '2100009', '=', '0', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 352, 'MAX_CRIT_SAVEPOINT_PHASE', 'Blocking savepoint phase max. (s, last day)', '2100009', '<=', '10.00', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 365, 'WRONG_IO_PARAMETER_SETTINGS', 'Wrong SAP HANA I/O parameter settings', '1999930', '=', 'no', 122.04, -1 FROM DUMMY ) UNION ALL ( SELECT 453, 'CONCAT_ATTRIBUTES_SIZE', 'Size of non-unique concat attributes (GB)', '1986747', '<=', '30.00', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 1330, 'USER_EXPIRATION', 'Number of users with expiration date', '', '=', '0', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 1335, 'SAP_USER_PASSWORD_EXPIRATION', 'Number of SAP users with password expiration', '1702224', '=', '0', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 1850, 'SR_LOGREPLAY', 'System replication op. mode logreplay used', '1999880', '=', 'yes', 110, -1 FROM DUMMY ) UNION ALL ( SELECT 1930, 'SYNONYM_TABLE_NAMES', 'Synonyms with names identical to table name', '', '=', '0', -1, -1 FROM DUMMY ) UNION ALL ( SELECT 2112, 'LAST_STATSERVER_CTC_RUN', 'Last consistency check via stat. server (days)', '2116157', '<=', '32', -1, -1 FROM DUMMY ) UNION ALL [VALID FOR] - Revisions: >= 1.00.120 [SQL COMMAND VERSION] - 2014/04/13: 1.0 (initial version) - 2014/05/09: 1.1 (grouping of checks) - 2014/11/14: 1.2 (dedicated command for ESS) - 2015/01/29: 1.3 (dedicated command for ESS and Rev. 90 and higher) - 2015/11/12: 1.4 (dedicated command for Rev. 100 and higher) - 2016/01/19: 1.5 (dedicated command for Rev. 102.01 and higher) - 2016/10/28: 1.6 (dedicated command for Rev. 1.00.120 and higher) - 2017/06/22: 1.7 (CHECK_ID and CHECK_GROUP filtering included) [INVOLVED TABLES] - various [INPUT PARAMETERS] - HOST Host name 'saphana01' --> Specic host saphana01 'saphana%' --> All hosts starting with saphana '%' --> All hosts - ONLY_POTENTIALLY_CRITICAL_RESULTS Possibility to restrict the output to potentially critical issues 'X' --> Output only contains issues that can potentially indicate a problem ' ' --> no output restriction - MAX_VALUE_LENGTH Maximum length of VALUE output column 48 --> Maximum length of 48 characters -1 --> no length limitation - CHECK_ID Possibility to restrict the output to a specific check ID 250 --> Restrict output to check ID 250 -1 --> No restriction to a particular check ID - CHECK_GROUP Possibility to restrict output to a specific check group (i.e. checks below a check group heading) 'DISKS' --> Perform checks for check group DISKS '%' --> No restriction related to check groups - ORDER_BY Sort criteria (available values are provided in comment) 'HOST' --> Sorting by host name 'CHECK' --> Sorting by check identifier (CHID column) [OUTPUT PARAMETERS] - CHID: Check identifier (defined and used by this Mini Check command) - DESCRIPTION: Check description - HOST: Host name (empty if not host specific) - VALUE: Check value found on the system - EXPECTED_VALUE: Expected check values - C: 'X' if value found on the system is outside of the expected values (indication for potentially critical problem) - SAP_NOTE: Related SAP Note [EXAMPLE OUTPUT] ------------------------------------------------------------------------------------------------------------------------------------------------------ |CHID |DESCRIPTION |HOST |VALUE |EXPECTED_VALUE |C|DETAILS | ------------------------------------------------------------------------------------------------------------------------------------------------------ |**** |GENERAL | | | | | | | | | | | | | | | 10|Analysis date | |2014/05/30 12:27:35 | | | | | 11|Database name | |HP1 | | | | | 12|Revision level | |1.00.74.00.390550 (NewDB100_REL) | | | | | 110|Everything started | |yes |yes | | | | 111|Startup time variation (s) | |1108 |<= 600 |X| |