SQL failed with error "column ambiguously defined" in HANA Database

Symptom
Execution of SQL statement failed with error:

SAP DBTech JDBC: [268]: column ambiguously defined: $rowid$: line col (at pos xxxx)

Environment

    HANA 1.0
    HANA 2.0

Reproducing the Issue

Execute the SQL statement via HANA studio SQL console or hdbsql
Cause

When internal columns such as $rowid$ is used, table name needs to be defined, otherwise, error message "column ambiguously defined" will occure
 
Resolution

If your SQL statement contains

ORDER BY "$rowid$"
And the execution failed with error "column ambiguously defined", you need to modify the statement to include the table name, for example:

ORDER BY ."$rowid$"

More details Refer Snote : 2695943

How to install HANA license by OS commands

Symptom

You need to use OS commands to access the HANA Database and manage the license.

Because there's no HANA Studio or HANA Cockpit available to manage the license for some reason.

Environment

    HANA Platform 1.0
    HANA Platform 2.0

Resolution

1. Logon as sidadm user, connect to the Database via hdbsql.

    If it is a single container system, please try below command:

> hdbsql -n :315 -i -u -p

    If it is a MDC system, please try connecting to the system DB by below command:

> hdbsql -n :313 -i -u -p

2. Enable multiline mode in hdbsql

hdbsql => \mu

3. You need a new license key, which you can download from Support Portal.

4. Enter statement  SET SYSTEM LICENSE ''.

Please note to add quotation mark outside the content of the license file you just downloaded.

5. Execute the statement by command: \g

hdbsql => \g

Remarks:
Install the new license via hdbsql cannot replace the old license. Please consider to delete the old license key before installing the new one.

You can also delete license keys by executing the SQL statement UNSET SYSTEM LICENSE ALL. 

More Details refer Snote :2690863

 
 

Unable to schedule database job due to ASE Error SQL4002

Symptom

     Schedule database job failed with following error:

    [ASE Error SQL4002][SAP][ASE ODBC Driver][Adaptive Server Enterprise] Login failed.
    Exception CX_DB6_CALENDAR_ACTION in class CL_DB6_CALENDAR_ACTION method REFRESH_ACTIONS line 64/ RC=1074

    DBACOCKPIT - DB connection test failed and warned same error '[ASE Error SQL4002][SAP][ASE ODBC Driver][Adaptive Server Enterprise] Login failed.'

Environment

•     SAP Adaptive Server Enterprise (ASE) 15.7 Business Suite
•     SAP Adaptive Server Enterprise (ASE) 16.0 Business Suite

Solution

1.     Run T-code DBACOCKPIT;
2.     On left panel -> Choose 'Database Connections';
3.     Chose 'Remote Database Connection' - 'SAP ASE' and click 'Change User Credentials';
4.     Input the correct password of DB user.

More details refer snote 2692998

HANA : How to set memory allocation limit for tenant databases

Symptom

You want to manage and control the memory usage of your multiple-container system by configuring global allocation limit for individual tenant databases.
Environment

    As of SAP HANA Database 1.0 SPS9
    SAP HANA Database 2.0

Resolution

You can use allocationlimit in [memorymanager] to limit the maximum amount of memory that can be allocated per process for all services of a tenant database.
For example, execute below command from the system database (allocationlimit value is in MB ):
< SPS11

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'DATABASE', '') SET ('memorymanager', 'allocationlimit') = '8192' WITH RECONFIGURE;

>= SPS11

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'DATABASE', '') SET ('memorymanager', 'allocationlimit') = '8192' WITH RECONFIGURE;
For SPS09, you need to restart HANA database to take effect.

For later SPS,  memory alignment will happen on the fly, but take some time. To make it happen immediately, you can restart the database.

To confirm the changes with systemdb connection, you can use SQL query:

SELECT * FROM "SYS_DATABASES"."M_SERVICE_MEMORY";


More datails Refer Snote:2175606

ST22 dumps with runtime error CONVT_NO_NUMBER

ST22 dumps with runtime error CONVT_NO_NUMBER

Symptom

ST22 Dumps getting generated with runtime error: CONVT_NO_NUMBER
Following is the detailed ST22 dump that was constantly getting generated:

Category ABAP Programming Error
Runtime Errors CONVT_NO_NUMBER
Except. CX_SY_CONVERSION_NO_NUMBER
ABAP Program CL_HDB_ALERT_COLLECTOR_E2E====CP
Application Component HAN-DB

Short Text "1 since local time: "  cannot be interpreted as a number.

What happened? 
 
Error in the ABAP Application Program. The current ABAP program "CL_HDB_ALERT_COLLECTOR_E2E====CP" had to be terminated because it has come across a statement that unfortunately cannot be executed.
 
Environment

SAP HANA Platform Edition 1.0

SAP HANA Platform Edition  2.0

Resolution

Apply 2211415 - SAP HANA alerting composite SAP Note


More details refer Snote 2669097
 

How To Activate SAP HANA Memory Allocator Traces

Please follow the steps below in order to collect the allocator trace.

The hdbcons commands need to be executed as adm user from OS level.
The commands below are given for Single Database Container system.
If you are running MDC, please use hdbcons -p to execute sub commands for tenant DB.
In case you are running MDC in high isolation mode, please refer to SAP Note 2410143.

•     If possible, clear the SQL Plan Cache or restart the system:

    ALTER SYSTEM CLEAR SQL PLAN CACHE;
 

•     Reset possible existing trace entries. Please replace the allocator with the complete name including the hierarchy, e.g. the full string returned as CATEGORY from M_HEAP_MEMORY

    hdbcons "mm resetusage -r "
   

• Enable astrace for the specified allocator

    hdbcons "mm flag -sr astrace,dstrace"
 

•     Create initial Allocator Trace report, and write down the current size of the allocator. In case the SQL does not return any record, it means that there is currently no allocation on this allocator.

    hdbcons "mm top -l 20 " > report_0_$(date +%y%m%d%H%M%S).txt

    SELECT NOW(), HOST, PORT, CATEGORY, ROUND(EXCLUSIVE_SIZE_IN_USE/1024/1024) AS "SIZE(MB)" FROM M_HEAP_MEMORY WHERE PORT LIKE '%03' AND CATEGORY = '';
 

•     In case there is a suspected query/application transaction, execute it to reproduce. Otherwise, wait and monitor the size of the allocation, until you see a noticeable growth compared to the starting point.

    Save the result of the current allocation size, create a report of Allocator Trace and generate a callgraph.

    SELECT NOW(), HOST, PORT, CATEGORY, ROUND(EXCLUSIVE_SIZE_IN_USE/1024/1024) AS "SIZE(MB)" FROM M_HEAP_MEMORY WHERE PORT LIKE '%03' AND CATEGORY = '';
 
    hdbcons "mm top -l 20 " > report_$(date +%y%m%d%H%M%S).txt
 
    hdbcons "mm cg -r " > mmcallgraph_$(date +%y%m%d%H%M%S).dot
 
    Note, it is the best to check the allocator size and collect the report when there is no load (or relatively low) on the system if possible. Only in that case, we can make sure that the stacks recorded in allocator trace are pointing to the leakage, instead of meaningful allocations for the active queries.

•     Repeat step 5. and create 3-5 reports & callgraphs with the allocation size returned from the SQL.
•     Disable astrace

    hdbcons "mm flag -dr astrace,dstrace"

•     Cleanup

    hdbcons "mm resetusage "
 

•     Zip the allocator size returned by SQL, Allocator Trace reports and callgraphs generated in step 4 and 5 and contact SAP Support for root cause analysis.

More details refer Snote : 2620830