How to avoid error "[2048]: column store error: search table error: [47] message not found"?

[Former Member]

Dear experts, 

I do experience an error thats most likely caused by the hana system running out of memory.

Could not execute 'create local temporary column table #[...] as (select [...], ...'

SAP DBTech JDBC: [2048]: column store error: search table error:  [47] message not found

The error occurs while executing a full join statement on two rather big tables.

• Am I right with my initial guesswork and is the error really caused by hana running out of memory?
• Any Ideas how i can avoid this error without reducing the data (e.g. by adding "where"- constraints) would be highly appreciated!

Many thanks in advance for your responses,

Tobias Müller

[former_member184768 · Accepted solution]

Hi Tobias,

Couple of suggestions:

1) Can you please try WITH the WHERE clause (reducing data) just to be sure that there is no syntax error in your code and the local temporary table is created. This will also ensure that the issue is likely to be related to memory issue.

2) Can you try to unload some of the other tables / data / perform delta merge, in short, reduced the consumed memory and try the code WITHOUT where clause.

Also it will help if you can post your code to understand it better. Also mention how much data is expected in the SELECT statement, some data size in the underlying tables used in the SELECT statement.

I also hope when you mentioned FULL join, you don't mean FULL OUTER join.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

1) tried that by adding a rather restrictive where clause --> works fine and therefore confirms my initial guess that hana is running out of memory.

2) not possible for operative reasons 😞

I'll try to achieve my goal by using a analytical view. Seems promising right now 🙂

[former_member1055931]

Check the size of the MAXIMUM_SIZE_OF_ROW_STORE parameter. Make sure you have not exceed either the physical RAM, allocated RAM, or the size of the parameter. Keep in mind that columnar databases are an old type of legacy database that was replaced with relational databases more than 40 years ago because of these memory issues. Instead of finding data by unique identifiers, they search through all the data using columns on every execution. The size of the data in memory can easily exceed the physical RAM, allocated RAM, or the size of the parameter. IBM has this same issue with DASHDB/DB2 WAREHOUSE. It seems that the lessons of the past have been forgoten are being repeated in the present. To prevent the issue simply plan the size of the RAM and parameters to be larger than the largest possible execution or larger than your data times the number of concurrent users. Since SAP Hana S4 is an in-memory database that uses column store, it can exceed the size of the data. Also, you may want to check if the database can exceed the 32-bit memory limit of 2GB, the old limit from 40 years ago. Most of the legacy technologies have the legacy memory limitations. For example, a tabular database has a 2 billion minus 3 record limit. Some of the old columnar databases had a 2GB hard limit for the size of the query itself, or the return of the data from the query. Since this error is reproducible on every column store database, it's likely that HANA has the same limitation. (ERROR CODE 2048 = the old 32-bit 2GB memory limit of columnar databases). (See database history on Edgar F. Codd and ACID compliant databases systems) https://help.sap.com/viewer/7c78579ce9b14a669c1f3295b0d8ca16/Cloud/en-US/20a760537519101497e3cfe07b3...

[lbreddemann]

No idea whether this is memory related.

Why don't you check the indexserver-trace file?

And posting the full SQL surely won't be bad 🙂

- Lars