Get column store error: error occurs during bulk insertion when call procedure

[holger_blum]

Hi,

I created a procedure: ( only some code as example )

DROP PROCEDURE SB_TBL_BK230040_PI_WERTE_FILL;

CREATE PROCEDURE SB_TBL_BK230040_PI_WERTE_FILL ( IN I_GJAHR DECIMAL(4,0), IN I_PALEDGER NVARCHAR(2) )
LANGUAGE SQLSCRIPT AS
BEGIN

DECLARE V_JAHR0 DECIMAL(4,0) = :I_GJAHR;
DECLARE V_PALEDGER NVARCHAR(2) = :I_PALEDGER;
DECLARE V_HZDAT DATE = CURRENT_DATE;


DELETE FROM SB_TBL_BK230040_PI_WERTE WHERE JAHR0 = V_JAHR0 AND PALEDGER = V_PALEDGER;
COMMIT;


INSERT INTO SB_TBL_BK230040_PI_WERTE  
select (  150 colums ).....
from
          ( select (  150 colums from table 1
            union all
            select (  150 colums from table 2 )

......
            group by....;

COMMIT;            
END

When I start this procedure I get an error

SAP Hana Database Error: column store error: "SB_TBL_BK230040_PI_WERTE_FILL": line 16 col 1 (at pos 406): [2048] (range 3): column store error: error occurs during bulk insertion

When I run the Insert up to Commit statement as seperat script it works

When I run the procedure without union all ( only values from table 1 ) it works

When I run the procedure without union all ( only values from table 2 ) it works

I have no idea why this procedure does not work.

Holger

[former_member183326 · Accepted solution]

Hello,

You are running into an Out Of Memory issue here.

I would suggest checking Note 1999997 for further information on this.

[holger_blum · Accepted solution]

Hi Michael,

I found the error after looking into trc file

[11839]{417808}[1834/80472358] 2016-02-16 15:44:10.723201 e SQLQuery         qe_itab_materializer.cc(00452) : Insert into column[123] failed

...

!!INSERTION FAILED COLUMN!!

<<Column[123]>>

Fixed8(4, 0)  __trex_field_Fixed8__() ....... [3145]:039:<6/7 (8B)>:

  /1/ void*  "__rids__" ....... [3141]:NOP:<0/1 (8B)>:

  /2/ int32_t(10, 0) const := 0 ....... [3142]:LOAD:<3/4 (4B)>:

  /3/ int32_t(10, 0) const := 2 ....... [3143]:LOAD:<4/5 (4B)>:

  /4/ int32_t(10, 0) const := 0 ....... [3144]:LOAD:<5/6 (4B)>:

....

Then I changed

CREATE PROCEDURE SB_TBL_BK230040_PI_WERTE_FILL ( IN I_GJAHR DECIMAL(4,0), IN I_PALEDGER NVARCHAR(2) )

to

CREATE PROCEDURE SB_TBL_BK230040_PI_WERTE_FILL ( IN I_GJAHR DECIMAL, IN I_PALEDGER NVARCHAR(2) )

and

DECLARE V_JAHR0 DECIMAL(4,0) := :I_GJAHR;

to

DECLARE V_JAHR0 DECIMAL := :I_GJAHR;

now it works.

[former_member183326 · Accepted solution]

Hell Holger,

Glad to hear you solved this.

[holger_blum · Accepted solution]

Thx,

but I do not know the reason.

Target column is decimal(4,0)

Variable also....

[lucas_oliveira · Accepted solution]

Hi Holger,

Best thing to do in these cases is to have an isolated reproducible scenario and then open an SAP support incident.

Anyhow, from the application development point-of-view it seems strange to me to have year (I_GJAHR) as a decimal. Is there a particular reason why you do that?

BRs,

Lucas de Oliveira

[holger_blum · Accepted solution]

Hi Lucas,

I_GJAHR is year, SAP table CE1xxxx.

When I copy this table with DataServices from SAP to HANA,

DS creates Decimal(4,0),

in SAP it is NUMC4

[lucas_oliveira · Accepted solution]

Hi Holger,

Exactly. Usually we have those in the database as NVARCHAR(4). I wonder why Data Services creates it as Decimal... I can't see a good reason why you would try aggregation an year value.

I'm no DS expert so I can't comment much on that, but I'd believe this could be customized from the DS side.

Anyway, if you have a reproducible scenario I still believe it's worth creating the SAP Support ticket.

Regards,

Lucas de Oliveira

[Former Member · Accepted solution]

Hello

This is a common problem when reading from the SAP application layer, rather than the underlying database.

SAP date datatypes are processed by Data Services in this way because it uses the SAP metadata to determine the datatype, rather than the underlying database datatype (which it can't determine). 

It is possible to read the underlying database directly with Data Services and this would use the correct datatypes.  However, if you want to read from the SAP application and change the datatype before writing to a database, this is very easy in a query mapping.

Michael

[holger_blum · Accepted solution]

Hi Lucas,

this procedure creates a precalculated column table, actual vs. plan

group by

mandt,

bukrs,

jahr0 = GJAHR,

kndnr,

mvgr5,

mvgr4,

mvgr3,

artnr

and many many sum(columns )