DDL for CloverETL server database setup

Support questions related to CloverETL Server

jzhu
Posts: 19
Joined: Tue Jul 19, 2016 12:34 pm

DDL for CloverETL server database setup

Postby jzhu » Mon Aug 22, 2016 12:08 pm

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.

vazquezrosariop
Posts: 107
Joined: Mon Feb 29, 2016 5:33 pm

Re: DDL for CloverETL server database setup

Postby vazquezrosariop » Tue Aug 23, 2016 2:06 pm

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
---
Pedro Vazquez Rosario
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support

jzhu
Posts: 19
Joined: Tue Jul 19, 2016 12:34 pm

Re: DDL for CloverETL server database setup

Postby jzhu » Wed Aug 24, 2016 10:15 am

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.

vazquezrosariop
Posts: 107
Joined: Mon Feb 29, 2016 5:33 pm

Re: DDL for CloverETL server database setup

Postby vazquezrosariop » Wed Aug 24, 2016 3:16 pm

Hi,

Yes, all the scripts in the Oracle9Dialect will work on Oracle database version 11. To answer your second question you will need to run all the scripts in the Oracle9Dialect as well as in the dbpatches.
---
Pedro Vazquez Rosario
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support

jzhu
Posts: 19
Joined: Tue Jul 19, 2016 12:34 pm

Re: DDL for CloverETL server database setup

Postby jzhu » Thu Aug 25, 2016 9:45 am

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).

dpavlis
Posts: 180
Joined: Sat Mar 10, 2007 8:12 pm

Re: DDL for CloverETL server database setup

Postby dpavlis » Thu Aug 25, 2016 1:16 pm

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.
David Pavlis
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com


cron