Temenos DBImport error when trying to import from Oracle to H2

Question

I'm trying to import Temenos T24 database from Oracle to H2 with the following settings:

Target Database : H2
URL : jdbc:h2:tcp…data/temenos/h2/TAFJDB1
Driver : org.h2.Driver
User ID : ****
Password : ****
Layout : TEXT -> VARCHAR / VARCHAR
Use XMLBinary : No
Keep TAF_VOC : Yes
mode zOS : No

Source Database : Oracle
URL : jdbc:oracle:thin:@...:ORA12C
Driver : oracle.jdbc.driver.OracleDriver
User ID : ****
Password : ****

To Do
Create tables : Yes
Clear tables : No
Inserting Records : Yes
Creating Views : Yes

Tables to
process : All
exclude :
not insert :
exclude from XML : F.JOB.LIST.*
UD Exceptions : [F.DL.DATA, DW.EXPORT, &HOLD&, &COMO&]

Press to Continue ...

The import run for about 50% then it stopped with the below error in the error log:

[INFO ] 2020-03-06 09:50:13,683 [Thread-11] DBIMPORT {} - Creating TAFJ_CACHE table ...
[ERROR] 2020-03-06 09:50:13,698 [Thread-11] DBIMPORT {} - Exception while creating TAFJ_CACHE : 
org.h2.jdbc.JdbcSQLException: Table "TAFJ_CACHE" already exists; SQL statement:
CREATE TABLE TAFJ_CACHE (RECID VARCHAR(255) NOT NULL PRIMARY KEY, CACHENAME VARCHAR(255), WRITEBATCHROWS VARCHAR(5)) [42101-161]
                at org.h2.engine.SessionRemote.done(SessionRemote.java:538) ~[h2-1.3.161.jar:1.3.161]
                at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:183) ~[h2-1.3.161.jar:1.3.161]
                at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:177) ~[h2-1.3.161.jar:1.3.161]
                at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152) ~[h2-1.3.161.jar:1.3.161]
                at com.temenos.dbi.oracle.DBImport.createTAFJCacheTable(DBImport.java:3144) [TAFJDBImport.jar:19.30.0]
                at com.temenos.dbi.oracle.DBImport.doItForTwoDatabases(DBImport.java:1453) [TAFJDBImport.jar:19.30.0]
                at com.temenos.dbi.gui.MainWindow$4$1.run(MainWindow.java:330) [TAFJDBImport.jar:19.30.0]
                at java.lang.Thread.run(Thread.java:748) [?:1.8.0_241]
[INFO ] 2020-03-06 14:05:36,723 [Thread-11] DBIMPORT {} - Processing table :F.STANDARD.SELECTION
[INFO ] 2020-03-06 14:05:36,727 [Thread-11] DBIMPORT {} -  Processing table : F.STANDARD.SELECTION
[INFO ] 2020-03-06 14:05:37,449 [Thread-11] DBIMPORT {} - Adding table into currentWork : F_STANDARD_SELECTION
[INFO ] 2020-03-06 14:08:31,604 [Thread-11] DBIMPORT {} - Adding table into currentWork : D_F_STANDARD_SELECTION
[INFO ] 2020-03-06 14:08:36,998 [Thread-11] DBIMPORT {} - Processing table :F.PGM.FILE
[INFO ] 2020-03-06 14:08:36,999 [Thread-11] DBIMPORT {} -  Processing table : F.PGM.FILE
[INFO ] 2020-03-06 14:08:37,015 [Thread-11] DBIMPORT {} - Adding table into currentWork : F_PGM_FILE
[ERROR] 2020-03-06 14:08:37,017 [Thread-11] DBIMPORT {} - Failed Executing: CREATE TABLE "F_PGM_FILE" (RECID VARCHAR(255) NOT NULL PRIMARY KEY, XMLRECORD VARCHAR)
org.h2.jdbc.JdbcSQLException: Table "F_PGM_FILE" already exists; SQL statement:
CREATE TABLE "F_PGM_FILE" (RECID VARCHAR(255) NOT NULL PRIMARY KEY, XMLRECORD VARCHAR) [42101-161]
                at org.h2.engine.SessionRemote.done(SessionRemote.java:538) ~[h2-1.3.161.jar:1.3.161]
                at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:183) ~[h2-1.3.161.jar:1.3.161]
                at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:177) ~[h2-1.3.161.jar:1.3.161]
                at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152) ~[h2-1.3.161.jar:1.3.161]
                at com.temenos.dbi.oracle.DBImport.insertDDLSQL(DBImport.java:4438) [TAFJDBImport.jar:19.30.0]
                at com.temenos.dbi.oracle.DBImport.createTable(DBImport.java:2822) [TAFJDBImport.jar:19.30.0]
                at com.temenos.dbi.oracle.DBImportWorker.doWork(DBImportWorker.java:336) [TAFJDBImport.jar:19.30.0]
                at com.temenos.dbi.oracle.DBImport.doItForTwoDatabases(DBImport.java:1494) [TAFJDBImport.jar:19.30.0]
                at com.temenos.dbi.gui.MainWindow$4$1.run(MainWindow.java:330) [TAFJDBImport.jar:19.30.0]
                at java.lang.Thread.run(Thread.java:748) [?:1.8.0_241]
[ERROR] 2020-03-06 14:08:37,018 [Thread-11] DBIMPORT {} - Failed to create : 'F_PGM_FILE' Layout : 'TEXT' : org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "F_PGM_FILE" already exists; SQL statement:
CREATE TABLE "F_PGM_FILE" (RECID VARCHAR(255) NOT NULL PRIMARY KEY, XMLRECORD VARCHAR) [42101-200]
                at org.h2.message.DbException.getJdbcSQLException(DbException.java:453)
                at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
                at org.h2.message.DbException.get(DbException.java:205)
                at org.h2.message.DbException.get(DbException.java:181)
                at org.h2.command.ddl.CreateTable.update(CreateTable.java:89)
                at org.h2.command.CommandContainer.update(CommandContainer.java:198)
                at org.h2.command.Command.executeUpdate(Command.java:251)
                at org.h2.server.TcpServerThread.process(TcpServerThread.java:406)
                at org.h2.server.TcpServerThread.run(TcpServerThread.java:183)
                at java.lang.Thread.run(Unknown Source)

[ERROR] 2020-03-06 14:08:37,018 [Thread-11] DBIMPORT {} - SQL = :
[ERROR] 2020-03-06 14:08:37,020 [Thread-11] DBIMPORT {} - Exception on F.PGM.FILE/F_PGM_FILE : Error creating table in destination connection:F_PGM_FILE
[ERROR] 2020-03-06 14:08:37,021 [Thread-11] DBIMPORT {} - Exception on F.PGM.FILE/F_PGM_FILE : Failed to create pgm table.  Terminating.

Please let me know if you have any ideas of what can I do to fix this.

Thank you, Carina

PrecisionLex · Accepted Answer · 2020-03-10T16:08:13.613

0

Make sure you are using a blank H2 database / data file for this task. Use tCreateDB script to create a blank TAFJ H2 database. This script is located in TAFJ_HOME/dbscripts/h2 along with the TAFJFunctions.jar, which is required to be loaded into DB before you run DBImport.

In case you are re-starting a previously terminated DBImport, you case use the "-startwithfile" option and enter the last file (not)processed by previous run of DBImport:

DBImport -startwithfile FBNK.ACCOUNT

There is also a "-endwithfile" option.

From Temenos "TAFJ-DB Setup.pdf" doc:

-startwithfile DBImport can begin creating files from a particular starting point (ie. useful if your DBImport program crashed for some reason and you don’t want to start from scratch). PLEASE NOTE THAT THE FILENAME AFTER THE OPTION IS THE VOC (J4 FILENAME) AND NOT THE ORACLE FILENAME

edited Mar 10 '20 at 16:08

answered Mar 09 '20 at 17:19

PrecisionLex

801
11
26

1

I've loaded the TAFJFunctions.jar and the db was blank. This error appeared when running the import for the second time, because it didn't finished in the first run, I've killed the process and re run it for the second time the next day. With the check "Keep TAF_VOC : Yes", the process should resume from the last table . I've read here in another post, that there is a schema.sql where I can change the statement from CREATE TABLE to CREATE IF NOT EXIST TABLE. Any idea where is that script located? I've tried asking this question there but it got deleted. – Ecaterina Oniciuc Mar 10 '20 at 10:16
In this case, did you tried to use DBImport option "-startwithfile"? I have updated my answer regarding this. I am not sure you can modify the Temenos DBImport to use CREATE IF NOT EXIST... – PrecisionLex Mar 10 '20 at 16:08
Yes, I've tried this option and I've finished the import. Thank you – Ecaterina Oniciuc Mar 12 '20 at 09:43

Temenos DBImport error when trying to import from Oracle to H2

1 Answers1