SAP Advanced SQL Migration has be used so far for SAP internal projects/PoCs (proof of concepts) and also for many different customer projects and in both cases we have detected some common used structures that cannot be directly converted to Hana at the moment, one of them is column defaults including complex expressions calling built in functions, user defined functions, using the concatenation operator, getting numbers from sequences using sequence functions… etc. That kind of defaults is not allowed in Hana yet, check SAP Hana 2.0 SP4 documentation:
SAP Hana 2.0 SP4 Create table statement
SAP Advance SQL Migration tool is catching those situations and pointing out a suggestion to implement those defaults using a INSERT TRIGGER and that has to be done manually. More precisely said a BEFORE INSERT ROW TRIGGER can be used for that purpose.
Here I am giving a couple of examples to implement this:
- Oracle table with a default calling to_char + concatenation operator:
CREATE TABLE javier.TEST_1
(
ID int,
D DATE DEFAULT SYSDATE NOT NULL,
D2 VARCHAR2(20) DEFAULT '01.' || to_char(sysdate,'mm.yyyy') NOT NULL
)
This can be converted as table without any default but a BEFORE INSERT ROW trigger:
CREATE COLUMN TABLE JAVIER.TEST_1 (
ID INT NULL
, D TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
, D2 VARCHAR(20) NOT NULL
);
CREATE TRIGGER TEST_1_BI BEFORE INSERT ON JAVIER.TEST_1
REFERENCING NEW ROW mynewrow
FOR EACH ROW
BEGIN
mynewrow.D2 = '01.' || to_varchar(current_date,'mm.yyyy');
END;
The behavior we get with this trigger is similar to Oracle:
hdbsql SP4=> insert into javier.test_1 (id) values (1);
1 row affected (overall time 10,316 msec; server time 2481 usec)
hdbsql SP4=> select * from javier.test_1;
ID,D,D2
1,2019-09-16 13:16:05.322000000,01.09.2019
""""
1 row selected (overall time 111,693 msec; server time 277 usec)
- Oracle table with a default calling nextval for a SEQUENCE
CREATE SEQUENCE JAVIER.SEQ_1
START WITH 1
MAXVALUE 9999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
CREATE TABLE JAVIER.TEST_2
(
ID int,
D DATE DEFAULT SYSDATE NOT NULL,
NUM_SEQ NUMBER(10) DEFAULT SEQ_1.nextval NOT NULL
);
The first approach would be convert this as a sequence + a BEFORE INSERT ROW trigger:
CREATE SEQUENCE JAVIER.SEQ_1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999999999999
START WITH 1
CACHE 20
NO CYCLE;
CREATE COLUMN TABLE JAVIER.TEST_2 (
ID INT NULL
, D TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
, NUM_SEQ BIGINT NULL
);
CREATE TRIGGER TEST_2_BI BEFORE INSERT ON JAVIER.TEST_2
REFERENCING NEW ROW mynewrow
FOR EACH ROW
BEGIN
mynewrow.NUM_SEQ := JAVIER.SEQ_1.NEXTVAL;
END;
But this returns an error because of a Hana limitation:
Could not execute 'CREATE TRIGGER TEST_2_BI BEFORE INSERT ON JAVIER.TEST_2 REFERENCING NEW ROW mynewrow FOR EACH ROW ...'
SAP DBTech JDBC: [7]: feature not supported: sequence number not allowed here: line 6 col 13 (at pos 140)
This can be solved using an intermediate variable or even using a user function to get the NEXTVAL for the sequence:
CREATE TRIGGER TEST_2_BI BEFORE INSERT ON JAVIER.TEST_2
REFERENCING NEW ROW mynewrow
FOR EACH ROW
BEGIN
declare v bigint;
select JAVIER.SEQ_1.NEXTVAL into v from dummy;
mynewrow.NUM_SEQ := :v;
END;
hdbsql SP4=> insert into JAVIER.TEST_2 (id) values (1);
> go
1 row affected (overall time 18,577 msec; server time 7005 usec)
hdbsql SP4=> insert into JAVIER.TEST_2 (id) values (2);
> go
1 row affected (overall time 10,614 msec; server time 1159 usec)
hdbsql SP4=> select * from JAVIER.TEST_2
> go
ID,D,NUM_SEQ
1,2019-09-16 14:29:07.800000000,1
2,2019-09-16 14:29:12.337000000,2
""""
2 rows selected (overall time 73,749 msec; server time 228 usec)
hdbsql SP4=>
Other related posts:
