Error in creating PAL Procedure

[Former Member]

Hi,

I am trying to create a procedure in PAL using the following script and I get the following error -

Script - CALL SYSTEM.AFL_WRAPPER_GENERATOR ('PAL_ABC', 'AFLPAL', 'ABC', PAL_ABC_SIGNATURE);

Error -  Could not execute 'CALL SYSTEM.AFL_WRAPPER_GENERATOR ('PAL_ABC', 'AFLPAL', 'ABC', PAL_ABC_SIGNATURE)' in 117 ms 623 µs .           SAP DBTech JDBC: [423]: AFL error:  [423] SYSTEM.AFL_WRAPPER_GENERATOR: line 38 col 1 (at pos 1957): AFL error exception:                    AFL  error: registration finished with errors, see indexserver trace

Trace - 

pop.setNameOfMainFunction('main')

pop.addParameter('PROC','PAL_ABC',0,83, 5000, 0)

pop.addParameter('AREA','AFLPAL',0,83, 5000, 0)

pop.addParameter('AFL','ABC',0,83, 5000, 0)

pop.addInputNodeMapping('DATA','DATA')

pop.setNameOfContext('sp')

pop.setSchema('SYSTEM')

pop.setExecuteUser('SYSTEM')

pop.setScenarioName('SYSTEM:_SYS_SS_CE_4511176_TMP')

pop.setCacheKey('"SYSTEM"."_SYS_SS_CE_4511176_TMP".525E8F815F0DA183E10000000A749ED1')

pop.setOriginalNodeName('$$DUMMY$$')

[6324]{203808}[353/29262417] 2013-10-23 15:11:53.958468 e cePlanExec       cePlanExecutor.cpp(06282) : Error during Plan execution of model SYSTEM:_SYS_SS_CE_4511176_INS (-1), reason: SYSTEM.AFL_WRAPPER_GENERATOR: line 38 col 1 (at pos 1957): AFL error exception: AFL error: registration finished with errors, see indexserver trace

I have used the following scripts to create the other components -

CREATE TYPE PAL_T_ABC_DATA AS TABLE (CUSTOMER VARCHAR(60), LIFESPEND DOUBLE);

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

CREATE TYPE PAL_T_ABC_RESULTS AS TABLE (ABC VARCHAR(60), CUSTOMER VARCHAR(60));

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

INSERT INTO PAL_ABC_SIGNATURE VALUES (1, 'PAL_T_ABC_DATA', 'in');

INSERT INTO PAL_ABC_SIGNATURE VALUES (2, 'PAL_T_ABC_PARAMS', 'in');

INSERT INTO PAL_ABC_SIGNATURE VALUES (3, 'PAL_T_ABC_RESULTS', 'out');

Whats the issue and how can it be fixed, please advise. Thanks.

[Former Member · Accepted solution]

Hi, Any ideas on this. Pl assist. Thanks.

[rindia · Accepted solution]

Hi,

Try this.

In your script schema is not set.

SET SCHEMA schema_name;

.

.

.

INSERT INTO PAL_ABC_SIGNATURE VALUES (1, 'schema_name.PAL_T_ABC_DATA', 'in');

INSERT INTO PAL_ABC_SIGNATURE VALUES (2, 'schema_name.PAL_T_ABC_PARAMS', 'in');

INSERT INTO PAL_ABC_SIGNATURE VALUES (3, 'schema_name.PAL_T_ABC_RESULTS', 'out');

Message was edited by: Raj Kumar Salla

Message was edited by: Raj Kumar Salla

[Former Member · Accepted solution]

This is probably it. As a general piece of advice, when starting out with PAL, start by copying and pasting code from the Predictive Analysis Library Reference Guide.

They have a good example on Page 160 of the SP06 guide.

Then replace their data for your data. Then play with the settings.

This will get you going with the PAL most quickly because PAL is very sensitive to syntax and the index server error messages can be slightly obscure, as you can see.

John

[Former Member · Accepted solution]

Thanks guys. I was trying by opening an SQL script in the _SYS_AFL as I believed that the system would execute 'SET SCHEMA' command on its own.

I have changed the scripts (shown below) but still get the same error -

SET SCHEMA _SYS_AFL;

CREATE TYPE PAL_T_ABC_DATA AS TABLE (CUSTOMER VARCHAR(60), LIFESPEND DOUBLE);

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

CREATE TYPE PAL_T_ABC_RESULTS AS TABLE (ABC VARCHAR(60), CUSTOMER VARCHAR(60));

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

INSERT INTO PAL_ABC_SIGNATURE VALUES (1, '_SYS_AFL.PAL_T_ABC_DATA', 'in');

INSERT INTO PAL_ABC_SIGNATURE VALUES (2, '_SYS_AFL.PAL_T_ABC_PARAMS', 'in');

INSERT INTO PAL_ABC_SIGNATURE VALUES (3, '_SYS_AFL.PAL_T_ABC_RESULTS', 'out');

CALL SYSTEM.AFL_WRAPPER_GENERATOR ('PAL_ABC', 'AFLPAL', 'ABC', PAL_ABC_SIGNATURE);

Pl advise. Thanks for your replies.

[Former Member · Accepted solution]

Here is the sample code. PAL is VERY sensitive to syntax errors. You are using single quotes rather than double quotes around your schema names - you need to correct this.

If it still fails can you paste the relevant lines of the index server trace again?

John

SET SCHEMA DM_PAL; 

DROP TYPE PAL_ABC_DATA_T; CREATE TYPE PAL_ABC_DATA_T AS TABLE("ITEM" VARCHAR(100),"VALUE" DOUBLE); 

DROP TYPE PAL_CONTROL_T; CREATE TYPE PAL_CONTROL_T AS TABLE("Name" VARCHAR(100), "intArgs" INT, "doubleArgs" DOUBLE,"strArgs" VARCHAR(100)); 

DROP TYPE PAL_ABC_RESULT_T; CREATE TYPE PAL_ABC_RESULT_T AS TABLE("ABC" VARCHAR(10),"ITEM" VARCHAR(100)); 

DROP table PAL_ABC_PDATA_TBL; CREATE column table PAL_ABC_PDATA_TBL("ID" INT,"TYPENAME" VARCHAR(100),"DIRECTION" VARCHAR(100)); 

insert into PAL_ABC_PDATA_TBL values (1,'DM_PAL.PAL_ABC_DATA_T','in'); insert into PAL_ABC_PDATA_TBL values (2,'DM_PAL.PAL_CONTROL_T','in'); insert into PAL_ABC_PDATA_TBL values (3,'DM_PAL.PAL_ABC_RESULT_T','out'); 

GRANT SELECT ON DM_PAL.PAL_ABC_PDATA_TBL to SYSTEM; 

call SYSTEM.afl_wrapper_eraser('PAL_ABC');
call SYSTEM.afl_wrapper_generator('PAL_ABC','AFLPAL','ABC',PAL_ABC_PDATA_TBL);

DROP TABLE #PAL_CONTROL_TBL; 

CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_CONTROL_TBL ("Name" VARCHAR(100), "intArgs" INT, "doubleArgs" DOUBLE,"strArgs" VARCHAR(100)); INSERT INTO #PAL_CONTROL_TBL VALUES ('THREAD_NUMBER',1,null,null); INSERT INTO #PAL_CONTROL_TBL VALUES ('PERCENT_A',null,0.7,null); 

INSERT INTO #PAL_CONTROL_TBL VALUES ('PERCENT_B',null,0.2,null); INSERT INTO #PAL_CONTROL_TBL VALUES ('PERCENT_C',null,0.1,null); 

DROP TABLE PAL_ABC_DATA_TBL; CREATE COLUMN TABLE PAL_ABC_DATA_TBL("ITEM" VARCHAR(100),"VALUE" DOUBLE); INSERT INTO PAL_ABC_DATA_TBL VALUES ('item1', 15.4); INSERT INTO PAL_ABC_DATA_TBL VALUES ('item2', 200.4); INSERT INTO PAL_ABC_DATA_TBL VALUES ('item3', 280.4); INSERT INTO PAL_ABC_DATA_TBL VALUES ('item4', 100.9); INSERT INTO PAL_ABC_DATA_TBL VALUES ('item5', 40.4); INSERT INTO PAL_ABC_DATA_TBL VALUES ('item6', 25.6); INSERT INTO PAL_ABC_DATA_TBL VALUES ('item7', 18.4); INSERT INTO PAL_ABC_DATA_TBL VALUES ('item8', 10.5); INSERT INTO PAL_ABC_DATA_TBL VALUES ('item9', 96.15); INSERT INTO PAL_ABC_DATA_TBL VALUES ('item10', 9.4); 

DROP TABLE PAL_ABC_RESULT_TBL; CREATE COLUMN TABLE PAL_ABC_RESULT_TBL("ABC" VARCHAR(10),"ITEM" VARCHAR(100)); 

CALL _SYS_AFL.PAL_ABC(PAL_ABC_DATA_TBL, "#PAL_CONTROL_TBL", PAL_ABC_RESULT_TBL) with overview;
select * from PAL_ABC_RESULT_TBL;

[Former Member · Accepted solution]

Thanks John, using the eraser helped. I have another query regarding the ABC Function within PAL - can it be used with multiple dimensions. For example, is it possible to perform ABC classification on the basis of Material and Plant rather than using only MAterial or only Plant. Thanks again.

[Former Member · Accepted solution]

No, you would have to concatenation material and plant as a string to do this.

John

[Former Member · Accepted solution]

I have a related but a slightly different question.

However, when I tried a very large table with PAL's Apriori procedure, the procedure never stop.

How do I terminate this Apriori procedure?

Thanks.

Chih

[Former Member · Accepted solution]

Yes, I have also found this. Sometimes you can cancel it in HANA Studio, other times you havd to wait for it to finish, or restart HANA.

You will find newer HANA Revisions especially Rev.72, handle this better.

[Former Member · Accepted solution]

John,

Thanks for the reply.

After PAL Apriori worked for too long, I checked CPU utilization and table distribution. I often found out CPU utilization is almost 0, and the size and number of records in the  Apriori Rules table never change for a long time (> hours).

It looks to me the Aprioir has finished, it just never returns.

By the way, when you say "sometime you can cancel it", can you please let me know how do I cancel a procedure?

Also, if I restart HANA at this point, will I lose existing results in the Aprior Rule table?

Chih

[rindia]

Hi Saurabh,

Could you please check whether PAL_ABC_SIGNATURE table has distinct values.

Regards

Raj

[Former Member]

Hi Raj,

I confirm that the table PAL_ABC_SIGNATURE has distinct values. Thanks.