Storing Service Images in a Separate Database

Document ID : KB000048512
Last Modified Date : 14/02/2018
Show Technical Document Details

The relevant properties can be changed in LISA_HOME/local.properties. At the bottom of that file is the following section:

# ======================================================================
# VSE Service Image database settings
# lisa.properties defines & uses Derby, the same database instance as the reports database.
# If you want to use another database for VSE service images, modify these properties. Oracle
# is used in the example below (don't forget to copy the relevant database driver jars to LISA_HOME/lib)
#
eclipselink.jdbc.driver=oracle.jdbc.driver.OracleDriver
eclipselink.jdbc.url=jdbc:oracle:thin:@_HOST:1521:SID_
eclipselink.jdbc.user=USER
eclipselink.jdbc.password=PASS
eclipselink.ddl-generation=create-tables
eclipselink.ddl-generation.output-mode=both

However, there is one property that is missing that is actually documented in lisa.properties in the same directory:

# VSE service image database settings. LISA VSE uses the open source EclipseLink JPA provider
#
# By default we will use our Reports database as the repository for service images
#
eclipselink.jdbc.driver=org.apache.derby.jdbc.ClientDriver
eclipselink.jdbc.url=jdbc:derby://localhost:1527/reports/lisa-reports.db;create=true
eclipselink.jdbc.user=rpt
# If you need to change the password, make the following line something like eclipselink.jdbc.password=tiger (note there
# is no trailing '_enc') and LISA will encrypt the password for you when you restart.
eclipselink.jdbc.password_enc=76f271db3661fd50082e68d4b953fbee
eclipselink.ddl-generation=create-tables
eclipselink.ddl-generation.output-mode=database

# database types. iTKO supports all of these and has verified LisaDerbyPlatform, Oracle, SQLServer and MySQLPlatform
# If you are using Derby or JavaDB (they are synonyms), use the 'com.itko.lisa.vse.stateful.model.LisaDerbyPlatform' value to
# work around an eclipselink bug limiting generated IDs to 99,999
#
#Auto - EclipseLink accesses the database and uses the metadata that JDBC provides to determine the target database. Applicable to JDBC drivers that support this metadata.
#
#Database - configure the persistence provider to use a generic choice if your target database is not listed here and your JDBC driver does not support the use of metadata that the Auto option requires.
#DB2
#DB2Mainframe
#Derby - accepted but use com.itko.lisa.vse.stateful.model.LisaDerbyPlatform instead - see above
#HSQL - accepted but NOT supported in a production environment (it just doesn't work under load)
#Informix -
#JavaDB - accepted but use com.itko.lisa.vse.stateful.model.LisaDerbyPlatform instead - see above
#MySQLPlatform
#Oracle generic Oracle Database.
#Oracle8
#Oracle9
#Oracle10
#Oracle11
#PointBas
#PostgreSQL
#SQLAnywhere
#SQLServer
#Sybase
#TimesTen
eclipselink.target-database=com.itko.lisa.vse.stateful.model.LisaDerbyPlatform

So in your case you would set the following properties in local.properties (if you don't already have this file, use _local.properties as a template) and assuming you are using the SQLServer2005 driver.

eclipselink.jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
eclipselink.jdbc.url=jdbc:sqlserver://DATABASE_HOST;databaseName=DBNAME;
eclipselink.jdbc.user=USERNAME
eclipselink.jdbc.password=PASSWORD
eclipselink.ddl-generation=create-tables
eclipselink.ddl-generation.output-mode=database
eclipselink.target-database=SQLServer

You will of course need the Microsoft SQL Server client jar files in LISA_HOME/lib or on your classpath.

If you are using the older SQLServer200 driver you need to change the URL to
jdbc:microsoft:sqlserver://DB_HOST:1433
and the driver to
com.microsoft.jdbc.sqlserver.SQLServerDriver

The MS Drivers are a bit painful that way....

As far as where you store your images it is entirely up to you but as always, if you store the image on a remote machine you can expect 10-20 milliseconds of network overhead per fetch so it won't be as fast as keeping the image locally on a Derby instance - but you probably won't notice unless you have high throughput requirements.

And I have seen in the past that SQLServer really doesn't scale out for these sorts of applications without a good deal of tuning (there are database locks applied even for a SELECT that prevent other VSE threads from following the same conversation path or worse, other conversation paths until the first thread has finished. But again, unless you have high throughput requirements, you probably won't even notice).

Hope that helps... and if you're feeling adventurous there is a brand new driver at http://www.microsoft.com/DownLoads/details.aspx?familyid=99B21B65-E98F-4A61-B811-19912601FDC9&displaylang=en
more info about the older JAR connection strings:

http://support.microsoft.com/kb/313100