Cannot Run Configuration Mini Checks on SP 9 Release 10 ?

[Former Member]

We've recently updated our HANA DB installation to SP9 Revision 1.00.100.00.1434512907 (fa/newdb100_rel), and can no longer run some of the useful SQL queries provided by Martin Frauendorfer in SAP Note 1969700 (the latest revision 71 dated 7/25/2015), specifically HANA_Configuration_MiniChecks_Rev90+_ESS.

When attempting to execute this SELECT statement from SAP HANA Studio (which always worked in previous versions of the DB), the failure is:

Could not execute 'SELECT /* [NAME] - HANA_Configuration_MiniChecks_Rev90+_ESS [DESCRIPTION] - General SAP HANA checks ...'

SAP DBTech JDBC: [3] fatal error: Execution flow must not reach here.  See error trace for details

Examining the indexserver_alert_<master_host>.trc file shows little additional detail, other than several hundred table access and join info lines, the last of which is on M_SYSTEM_OVERVIEW:

# TABLE M_SYSTEM_OVERVIEW_(XXXXXXXXXXXX:30003) (1) (opId: -1)   FILTER ( (1,0) = 'System' AND (1=1) = 'Version') ...

Has anyone been able to execute the Configuration Mini Checks on Revision 10 ?  Is there a work around ?

[Former Member]

I've the same problem for 1.00.101.00.1435831484

also other problems, such as:

1. memory leak
2. check for software updates does not work

[lbreddemann]

Well, first of all, you are using SPS 10 here (rev. 1.00.100 )

I've just used the command script in version 1.3 ( 2015/01/29) and didn't have any issues.

Please check the version of your script (it's in the header part of the script).

- Lars

[Former Member]

Sorry, I used the SPS value reported by HANA_Configuration_Overview, for Service Pack Stack, which is SP09.  However as you point out, this is wrong, because the provided query from 1969700 has a bug in the CASE statement that reports any revision greater than 09 as 09 when querying M_SYSTEM_OVERVIEW.

WHEN REVISION >= 90 THEN '09'.

I'm also using the latest version of the script (1.3 dated 2015/01/29) extracted from the latest SQL Statements.zip attached to SAP Note 1969700.

[Former Member]

We've just completed a fresh install of SPS10, and we continue to experience this runtime failure on the Mini Checks query.

By tediously executing each of the individual queries within the huge select statement, I found that on our system, SHARED_MEMORY_ALLOCATED_SIZE in M_SERVICE_MEMORY is actually 0 for SERVICE_NAME = 'nameserver' for all our hosts except for the master node.  This causes a divide by zero error in the following query found at line 1361 in the original SELECT statement (which I modified to avoid the divide by zero as shown):

SELECT

        'NAMESERVER_SHARED_MEMORY',

        HOST,

        TO_CHAR(ROUND(MAP(SHARED_MEMORY_ALLOCATED_SIZE, 0, 0, SHARED_MEMORY_USED_SIZE /                SHARED_MEMORY_ALLOCATED_SIZE * 100)))

      FROM

        M_SERVICE_MEMORY

      WHERE 

        SERVICE_NAME = 'nameserver'

         -- ADDED > 0 clause to avoid divide by zero

         AND SHARED_MEMORY_ALLOCATED_SIZE > 0

However, even after correcting this issue, the query still fails with the error:

SAP DBTech JDBC: [3]: fatal error: Execution flow must not reach here. See error trace for details

Further debugging via trial and error, and distilling to the minimal failure reveals that the following combination of 4 queries extracted from the original SQL,  UNIONed in any order, results in the Execution flow error, while executed individually or in ANY OTHER COMBINATION except all 4 there is no error:

     (SELECT

        'OPEN_ALERTS_HIGH',

        '',

        TO_CHAR(COUNT(*))

      FROM

        _SYS_STATISTICS.STATISTICS_CURRENT_ALERTS

      WHERE

        ALERT_RATING = 4

    )

    UNION ALL

        (SELECT

        'OPEN_ALERTS_ERROR',

        '',

        TO_CHAR(COUNT(*))

      FROM

        _SYS_STATISTICS.STATISTICS_CURRENT_ALERTS

      WHERE

        ALERT_RATING = 5

    )

    UNION ALL

    (SELECT

        N.NAME,

        '',

        TO_CHAR(SUM(MAP(T.TABLE_NAME, NULL, 0, 1)))

      FROM

      (SELECT 'QCM_TABLES' NAME, 'QCM%' PATTERN FROM DUMMY

      UNION ALL

         SELECT 'BPC_TABLES',      '$BPC$HC$%'    FROM DUMMY

      ) N LEFT OUTER JOIN

        TABLES T ON

          T.TABLE_NAME LIKE N.PATTERN AND

          T.IS_TEMPORARY = 'FALSE'

      GROUP BY

        N.NAME

    )

    UNION ALL

    ( SELECT

        'TEMPORARY_TABLES',

        '',

        TO_CHAR(COUNT(*))

      FROM

        M_TEMPORARY_TABLES

    )

Could not execute '(SELECT 'OPEN_ALERTS_HIGH', '', TO_CHAR(COUNT(*)) FROM _SYS_STATISTICS.STATISTICS_CURRENT_ALERTS ...'

SAP DBTech JDBC: [3]: fatal error: Execution flow must not reach here. See error trace for details

-------------------------------------

On our system, the QCM and BPC Tables don't exist, so I simplified those queries to be simple select from dummy as follows:

    (SELECT 'QCM_TABLES', '', '0' FROM DUMMY)

    UNION ALL

    (SELECT 'BPC_TABLES', '', '0' FROM DUMMY)

Which avoids the error in this small example, however, rolling that change back into the original mini checks query does not work around the execution flow error.

Any relevant suggestions for further debugging toward a resolution would be welcome.

[Former Member]

More investigation and I've found that extracting just these 2 queries from the original statement triggers the very informative, helpful, and descriptive "Execution flow must not reach here" error:

    ( SELECT

        'TABLES_AUTOMERGE_DISABLED',

        '',

        TO_CHAR(COUNT(*))

      FROM

        TABLES

      WHERE

        AUTO_MERGE_ON = 'FALSE' AND

        TABLE_NAME NOT LIKE '/BI%' AND

        TABLE_NAME NOT LIKE '/B28/%' AND

        TABLE_NAME NOT LIKE '0BW:BIA%' AND

        TABLE_NAME NOT LIKE '$BPC$HC$%' AND

        IS_COLUMN_TABLE = 'TRUE'

    )

    UNION ALL

    ( SELECT

        'TEMPORARY_TABLES',

        '',

        TO_CHAR(count(*))

      FROM

        M_TEMPORARY_TABLES

    )

Could not execute '( SELECT 'TABLES_AUTOMERGE_DISABLED', '', TO_CHAR(COUNT(*)) FROM TABLES WHERE AUTO_MERGE_ON = ...'

SAP DBTech JDBC: [3]: fatal error: Execution flow must not reach here. See error trace for details

In our case, we have some issue with the number of TEMPORARY_TABLES growing and never being cleaned up, on a system that is not being used except for debugging of this problem.  Currently we have over 189000 records in M_TEMPORARY_TABLES on a freshly installed server that was started yesterday. 

Modifying the original query from SAP Note 1969700 for the configuration mini checks, to change the count from M_TEMPORARY_TABLES to:

SELECT

     'TEMPORARY_TABLES',

     '',

     TO_CHAR(189467)  -- using the same number returned from count(*) on M_TEMPORARY_TABLES

FROM

     DUMMY

allows it to execute without errors on our system.  But clearly there is a problem with HANA SQLScript triggered by this particular set of circumstances. 

Anyone have thoughts about the ever increasing and excessively large number of records in M_TEMPORARY_TABLES (especially given the light use of this instance and short duration it has been running)  ? 

[lbreddemann]

The SQL works w/o problem on my Rev. 101 instance.

I suspect that there is something not quiet right with your temp. tables view.

Besides that the number of temp. tables appears to be very high. I'd assume that this is not intended.

For both issues, please have a support incident opened.

- Lars

[Former Member]

Thanks Lars -

The temp tables count is excessively high, and not intended. As I mentioned this is a new instance, installed and started yesterday, with nothing yet loaded, and just debugging of this mini-checks query - which would create a lot of temp tables on each run, but those should be getting cleaned up.

We're investigating further and will open incidents with all the information we gather.

- T