By default the server uses embedded database (Derby) - this database is not recommended for production use.
We need to set up the database for production use. The database to use (Oracle) has strict access control, and does not allow the application to create tables etc. dynamically. We need to provide schema / DDL to the database administrator, and they will set up the database for CloverETL server to use.
Will this work with CloverETL? Where or how can we get the DDL?
We do not use database connection in our graph or job flow. The database is only for CloverETL server management data.
We need to set up the database for production use. The database to use (Oracle) has strict access control, and does not allow the application to create tables etc. dynamically. We need to provide schema / DDL to the database administrator, and they will set up the database for CloverETL server to use.
Will this work with CloverETL? Where or how can we get the DDL?
We do not use database connection in our graph or job flow. The database is only for CloverETL server management data.
-
Hi,
The following are the steps your DB administrator will take to setup the CloverETL Server:- Collect all the scripts, most of them are in the <WAR-ARCHIVE>/WEB-INF/dbpatches/Oracle9Dialect, the scripts that are not oracle SQL dialect specific are in <WAR-ARCHIVE>/WEB-INF/dbpatches
- Determine, which scripts have not run yet, table "sys_schema_patches" contains list of run scripts, if the DB schema does not exist yet, the table has to be created - the table has two columns: "path" of type varchar2(255) and "applied" of type varchar2(64), the first column contains filename of the script and the second time of the script execution in the format "YYYY-MM-DD hh:mm:ss:nnn"
- Execute the scripts one-by-one according to their number (first two digits of the filename)
- Fill table "sys_schema_patches" with information about executed scripts - othwerwise the Clover server will attempt to execute scripts not present in the table upon server start
- Collect all the scripts, most of them are in the <WAR-ARCHIVE>/WEB-INF/dbpatches/Oracle9Dialect, the scripts that are not oracle SQL dialect specific are in <WAR-ARCHIVE>/WEB-INF/dbpatches
-
Thanks, Pedro.
The folder name is Oracle9Dialect. I assume it should work on Oracle database version 11, right?
Do we need to run all 76 sql scripts to set up the database for CloverETL server initially?
They include 62 in dbpatches/Oracle9Dialect, and 14 in dbpatches which do not exist in dbpatches/Oracle9Dialect. -
Most scripts in dbpatches are of the same name as those in dbpatches\Oracle9Dialect.
For the same name script, shall I run both? In which order?
I was thinking to copy scripts in dbpatches\Oracle9Dialect on top of dbpatches, overwriting same name scripts, and run all non-empty scripts (9 of them are empty). -
Hi,
One creative idea. Why don't you let CloverETL Server create all the necessary Oracle DB objects on some test instance of DB (or just schema). Then your DB admin can a) analyze that it does not contain anything dangerous b) just clone/copy all the tables and DB objects into the production DB. Then just re-connect the server (change the properties).
This way you don't need to worry about the sequence of scripts and patches. BTW: the reason it is rather complex is that Clover uses Hybernate as an ORM manager.
Please sign in to leave a comment.
Comments 5