cancel
Showing results for 
Search instead for 
Did you mean: 

HANA PAL problem

Former Member
0 Kudos

Hi,

I am building a simple decision tree based on some sample data.

But when I have to call CALL _SYS_AFL.PAL_DT_3 (V_DT_DATA5, DT_PARAMS5, DT_MODEL_JSON5, DT_MODEL_PMML5) WITH OVERVIEW; I always receive an error.

I am  System user.

SET SCHEMA PAL;

-- PAL setup 2

DROP TYPE PAL_T_DT_DATA5;

CREATE TYPE PAL_T_DT_DATA5 AS TABLE (ACCOUNT INTEGER, CHURN VARCHAR (10));

DROP TYPE PAL_T_DT_PARAMS;

CREATE TYPE PAL_T_DT_PARAMS AS TABLE (NAME VARCHAR(60), INTARGS INTEGER, DOUBLEARGS DOUBLE, STRINGARGS VARCHAR (100));

DROP TYPE PAL_T_DT_MODEL_JSON;

CREATE TYPE PAL_T_DT_MODEL_JSON AS TABLE (ID INTEGER, JSONMODEL VARCHAR(5000));

DROP TYPE PAL_T_DT_MODEL_PMML;

CREATE TYPE PAL_T_DT_MODEL_PMML AS TABLE (ID INTEGER, PMMLMODEL VARCHAR(5000));

DROP TABLE PAL_DT_SIGNATURE5;

CREATE COLUMN TABLE PAL_DT_SIGNATURE5 (ID INTEGER, TYPENAME VARCHAR(100), DIRECTION VARCHAR(100));

INSERT INTO PAL_DT_SIGNATURE5 VALUES (1, 'PAL_T_DT_DATA5', 'in');

INSERT INTO PAL_DT_SIGNATURE5 VALUES (2, 'PAL_T_DT_PARAMS', 'in');

INSERT INTO PAL_DT_SIGNATURE5 VALUES (3, 'PAL_T_DT_MODEL_JSON', 'out');

INSERT INTO PAL_DT_SIGNATURE5 VALUES (4, 'PAL_T_DT_MODEL_PMML', 'out');

CALL SYSTEM.AFL_WRAPPER_GENERATOR ('PAL_DT_3', 'AFLPAL', 'CREATEDT', PAL_DT_SIGNATURE5);

-- app setup

DROP VIEW V_DT_DATA5;

CREATE VIEW V_DT_DATA5 AS

SELECT TOP 100 ACCOUNT, CHURN

-- FROM "PAL"."GameData"

FROM GAME

DROP TABLE DT_PARAMS5;

CREATE COLUMN TABLE DT_PARAMS5 LIKE PAL_T_DT_PARAMS;

DROP TABLE DT_MODEL_JSON5;

CREATE COLUMN TABLE DT_MODEL_JSON5 LIKE PAL_T_DT_MODEL_JSON;

DROP TABLE DT_MODEL_PMML5;

CREATE COLUMN TABLE DT_MODEL_PMML5 LIKE PAL_T_DT_MODEL_PMML;

INSERT INTO DT_PARAMS5 VALUES ('THREAD_NUMBER', 2, null, null);

INSERT INTO DT_PARAMS5 VALUES ('PERCENTAGE', null, 1.0, null);

INSERT INTO DT_PARAMS5 VALUES ('MIN_NUMS_RECORDS', 1, null, null);

INSERT INTO DT_PARAMS5 VALUES ('IS_SPLIT_MODEL', 0, null, null);

INSERT INTO DT_PARAMS5 VALUES ('PMML_EXPORT', 1, null, null);

INSERT INTO DT_PARAMS VALUES ('CONTINUOUS_COL', 0, 30, null);

TRUNCATE TABLE DT_MODEL_JSON5;

TRUNCATE TABLE DT_MODEL_PMML5;

CALL _SYS_AFL.PAL_DT_3 (V_DT_DATA5, DT_PARAMS5, DT_MODEL_JSON5, DT_MODEL_PMML5) WITH OVERVIEW;

Error message:

Could not execute 'CALL _SYS_AFL.PAL_DT_3133111 (V_DT_DATA5, DT_PARAMS5, DT_MODEL_JSON5, DT_MODEL_PMML5) WITH OVERVIEW' in 87 ms 983 µs . SAP DBTech JDBC: [2048]: column store error: search table error:  [2620] executor: plan operation failed;Can not insert data from temp table "SYSTEM:P3_51804C86234B0C25E10000007F000002en" into table ""PAL"."DT_MODEL_JSON5""

Thanks a lot,

Balazs


Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Hi All,

I'm getting the same error :

"dberror(CallableStatement.execute): 2048 - column store error: search table error: [2620] executor: plan operation failed;Can not insert data from temp table into Result_Table"

I have verified i/p & o/p data types and the same code works fine on studio SQL editor , but facing issue only executing the same from xsjs.

Please let me know any other suggestions ..

Thanks & Regards,

Anup Singh

Former Member
0 Kudos

Error is because of following line ,

INSERT INTO DT_PARAMS5 VALUES ('IS_SPLIT_MODEL', 0, null, null);

So change to

INSERT INTO DT_PARAMS5 VALUES ('IS_SPLIT_MODEL', 1, null, null);

Former Member
0 Kudos

Changing the IS_SPLIT_MODEL parameter to 1 did not eliminate the runtime error for me.  However, referencing from the 3.2.2 C4.5 Decision Tree section (SPS v9.0), I changed the output TREE MODEL table’s second column’s datatype from varchar(5000) to CLOB—Character Large Object.  This eliminated the error. The 5000 character column width cannot store the temporary object’s results, which increase in size as the input / training data increase.  It seems that the temporary object’s results size increase does not vary IN DIRECT PROPORTION to the input data increase, but possibly DOES vary more directly with each input data column’s distinct value set.  Using small input datasets, which circumvented this size restriction altogether, my observation of the TREE MODEL table’s second column suggests that the data structure is similar to that of factors in R.  My error resolution experience is entirely consistent earlier comment that error vanished-result generated after reduction of number of (input) rows and columns.

rohitkumardas
Explorer
0 Kudos

Hi,

I also got the same error, but when I reduced the number of columns and the rows, the error got vanished and result got generated.

Former Member
0 Kudos

I have modified the code but still have the same error: (and it is the only error)

SET SCHEMA PAL;

-- PAL setup

DROP TYPE PAL_T_DT_DATA;

CREATE TYPE PAL_T_DT_DATA AS TABLE ( ACCLENGTH INTEGER, INGAMEMSG INTEGER, DAYHOURS DOUBLE, EVEHOURS DOUBLE,    NIGHTHOURS DOUBLE,    AVERAGEE DOUBLE, TOTAL DOUBLE, DIFFSERVERMINS DOUBLE, CUSTSERVERCALLS INTEGER, DIFFSERVERPLAN INTEGER, INGAMEMESSAGEPLAN INTEGER,    DAYGAMES INTEGER,    DAYCHARGE DOUBLE, EVEGAMES INTEGER,    NIGHTGAMES INTEGER,    NIGHTCHARGE DOUBLE, DIFFSERVERGAMES INTEGER, DIFFSERVERCHARGE DOUBLE, STATE VARCHAR(10), AREACODE INTEGER,    PHONE VARCHAR(10),    CHURN INTEGER);

DROP TYPE PAL_T_DT_PARAMS;

CREATE TYPE PAL_T_DT_PARAMS AS TABLE (NAME VARCHAR(60), INTARGS INTEGER, DOUBLEARGS DOUBLE, STRINGARGS VARCHAR (100));

DROP TYPE PAL_T_DT_MODEL_JSON;

CREATE TYPE PAL_T_DT_MODEL_JSON AS TABLE (ID INTEGER, JSONMODEL VARCHAR(5000));

DROP TYPE PAL_T_DT_MODEL_PMML;

CREATE TYPE PAL_T_DT_MODEL_PMML AS TABLE (ID INTEGER, PMMLMODEL VARCHAR(5000));

DROP TABLE PAL_DT_SIGNATURE;

CREATE COLUMN TABLE PAL_DT_SIGNATURE (ID INTEGER, TYPENAME VARCHAR(100), DIRECTION VARCHAR(100));

INSERT INTO PAL_DT_SIGNATURE VALUES (1, 'PAL_T_DT_DATA', 'in');

INSERT INTO PAL_DT_SIGNATURE VALUES (2, 'PAL_T_DT_PARAMS', 'in');

INSERT INTO PAL_DT_SIGNATURE VALUES (3, 'PAL_T_DT_MODEL_JSON', 'out');

INSERT INTO PAL_DT_SIGNATURE VALUES (4, 'PAL_T_DT_MODEL_PMML', 'out');

CALL SYSTEM.AFL_WRAPPER_GENERATOR ('PAL_DT01011101', 'AFLPAL', 'CREATEDT', PAL_DT_SIGNATURE);

DROP VIEW V_DT_DATA;

CREATE VIEW V_DT_DATA AS

SELECT TOP 100     ACCLENGTH,    INGAMEMSG,    DAYHOURS,    EVEHOURS,    NIGHTHOURS,    AVERAGEE,    TOTAL,    DIFFSERVERMINS,    CUSTSERVERCALLS,DIFFSERVERPLAN,    INGAMEMESSAGEPLAN,    DAYGAMES,    DAYCHARGE,    EVEGAMES,    NIGHTGAMES,    NIGHTCHARGE,    DIFFSERVERGAMES,    DIFFSERVERCHARGE,    STATE,    AREACODE,    PHONE,CHURN

  FROM GAME4;

DROP TABLE DT_PARAMS;

CREATE COLUMN TABLE DT_PARAMS LIKE PAL_T_DT_PARAMS;

DROP TABLE DT_MODEL_JSON;

CREATE COLUMN TABLE DT_MODEL_JSON LIKE PAL_T_DT_MODEL_JSON;

DROP TABLE DT_MODEL_PMML;

CREATE COLUMN TABLE DT_MODEL_PMML LIKE PAL_T_DT_MODEL_PMML;

INSERT INTO DT_PARAMS VALUES ('THREAD_NUMBER', 2, null, null);

INSERT INTO DT_PARAMS VALUES ('PERCENTAGE', null, 1.0, null);

INSERT INTO DT_PARAMS VALUES ('MIN_NUMS_RECORDS', 1, null, null);

INSERT INTO DT_PARAMS VALUES ('IS_SPLIT_MODEL', 0, null, null);

INSERT INTO DT_PARAMS VALUES ('PMML_EXPORT', 1, null, null);

CALL _SYS_AFL.PAL_DT01011101 (V_DT_DATA, DT_PARAMS, DT_MODEL_JSON, DT_MODEL_PMML) WITH OVERVIEW;

and the variables of the data table with Types:


0 Kudos

Hi Balazs

Just wondering if this has anything to do with the AFL__SYS_AFL_AFLPAL_EXECUTE role (or the lack thereof)?