Topic
  • No replies
SystemAdmin
SystemAdmin
708 Posts

Pinned topic Issues with IDMT 2.00-b2126

‏2012-12-05T08:05:01Z |
Hi,

I found the following problems using the IDMT 2.00-b2126 for porting Oracle to DB2 LUW:

1. Savepoints

IDMT converts ROLLBACK WORK TO SAVEPOINT <savepoint> to the incorrect value ROLLBACK WORK TO SAVEPOINT SAVEPOINT <savepoint> ON ROLLBACK RETAIN CURSORS.

Usually I have such problems with 2 files:
  • db2function.db2
  • db2procedure.db2

Example:
Original:
CREATE OR REPLACE FUNCTION RollbackTest
RETURN NUMERIC
AS
a NUMBER := 1;
b NUMBER := 2;
BEGIN
SAVEPOINT spRollbackTest;
IF a < b THEN
ROLLBACK WORK TO SAVEPOINT spRollbackTest;
END IF;
RETURN 0;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
ROLLBACK WORK TO SAVEPOINT spRollbackTest;
RETURN -1;
WHEN OTHERS
THEN
RETURN 1;
END RollbackTest;

After IDMT:
CREATE OR REPLACE FUNCTION "TEST"."ROLLBACKTEST"
RETURN NUMERIC
AS
a NUMBER := 1;
b NUMBER := 2;
BEGIN
SAVEPOINT spRollbackTest ON ROLLBACK RETAIN CURSORS;
IF a < b THEN
ROLLBACK WORK TO SAVEPOINT SAVEPOINT spRollbackTest ON ROLLBACK RETAIN CURSORS;
END IF;
RETURN 0;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
ROLLBACK WORK TO SAVEPOINT SAVEPOINT spRollbackTest ON ROLLBACK RETAIN CURSORS;
RETURN -1;
WHEN OTHERS
THEN
RETURN 1;
END RollbackTest;

2. Sequences

IDMT creates same sequence twice in the file db2sequences.sql. It’s ok if we use IDMT GUI (i.e., Interactive Deploy). But if we try to run this script using CLPPlus we will receive an error in the second sequence (since the sequence already exists).

Example:
Original:
CREATE SEQUENCE "TEST_SEQ"
MINVALUE 1
MAXVALUE 999999999999999999999999999
INCREMENT BY 1
START WITH 1
NOCACHE
NOCYCLE
ORDER;

After IDMT:
CREATE SEQUENCE "TEST"."TEST_SEQ" AS NUMERIC(31)
START WITH 2
MINVALUE 1
MAXVALUE 999999999999999999999999999
INCREMENT BY 1
NO CACHE
NOCYCLE
ORDER;

CREATE SEQUENCE "TEST"."TEST_SEQ" AS NUMERIC(31)
  • START WITH 2*
  • MINVALUE 1*
  • MAXVALUE 999999999999999999999999999*
  • INCREMENT BY 1*
  • NO CACHE*
  • NOCYCLE*
  • ORDER;*

3. The caret symbol

IDMT removes the caret symbol instead of the proper replacement. It changes the expression logic to opposite.

Example:
Original:
CREATE OR REPLACE FUNCTION CaretTest
RETURN NUMERIC
AS
a NUMBER := 1;
b NUMBER := 2;
BEGIN
IF a ^= b THEN
RETURN 1;
ELSE
RETURN 2;
END IF;
END CaretTest;

After IDMT:
CREATE OR REPLACE FUNCTION "TEST"."CARETTEST"
RETURN NUMERIC
AS
a NUMBER := 1;
b NUMBER := 2;
BEGIN
IF a = b THEN
RETURN 1;
ELSE
RETURN 2;
END IF;
END CaretTest;

Thanks