Creating UrbanCode databases in Oracle

Published: Wed, Apr 8, 2015
categories: ibm
tags: urbancode, deploy, oracle

Just a quick one, working with a client and noticed that the database install documentation is very prescriptive for most of the databases, but strangely spartan for Oracle.

The process is not difficult but it is helpful just to see it in a complete example. I have a local copy of Oracle 11.2 Express Edition installed on one of my local vms, I have these scripts hosted on github¹ if you want to grab the PL/SQL versions, but this is just what you can actually run if you don’t want to mess with that, by just substituting your information for mine below.

The example below is not production ready, but it should be enough to get started with an installation in dev/test while you evaluate the product and start the discussion with a DBA as to how to deploy this to production better or at least more inline without how your organization implements Oracle applications.

-- Create Tablespace
CREATE TABLESPACE ibm_uc_ts
	DATAFILE '/u01/app/oracle/11.2/oradata/XE/ibm_uc_ts.dbf'
		SIZE 750M
		AUTOEXTEND ON NEXT 10M
		MAXSIZE 20000M
	NOLOGGING
	PERMANENT
	EXTENT MANAGEMENT LOCAL	UNIFORM SIZE 1M;

-- Create User
CREATE USER ibm_ucb IDENTIFIED BY secretpassword
       DEFAULT TABLESPACE ibm_uc_ts
       TEMPORARY TABLESPACE temp;
CREATE USER ibm_ucd IDENTIFIED BY secretpassword
       DEFAULT TABLESPACE ibm_uc_ts
       TEMPORARY TABLESPACE temp;
CREATE USER ibm_ucr IDENTIFIED BY secretpassword
       DEFAULT TABLESPACE ibm_uc_ts
       TEMPORARY TABLESPACE temp;

-- Grant for user
GRANT CONNECT,RESOURCE,CREATE SESSION,CREATE TABLE TO ibm_ucb;
GRANT CONNECT,RESOURCE,CREATE SESSION,CREATE TABLE TO ibm_ucd;
GRANT CONNECT,RESOURCE,CREATE SESSION,CREATE TABLE TO ibm_ucr;

Before you get to installing your UrbanCodes, ensure that you have the latest JDBC driver for your version, there are some known issues with the >11.2.0.3 versions where you will see this pleasant error during install, trying to use the deafult odbc6.jar file that came with XE:

 [echo] Creating IBM UrbanCode Deploy Database Schema ...
 : java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
 ORA-01882: timezone region not found

Not suprisingly there is a solution on good ole StackOverflow², but it pretty much involves getting the latest version so I recommend you start there :)

Now the UrbanCode installs will require a distinct user for each product, and if you are using a very small setup for dev or something and want to use a DBA user, you need to create a schema to specify during the setup, for normal users this is just the username. I recommend you create unprivileged users for each app as a best practice, since it is actually harded to use a dba that to create a new user, just create the new user already…

The rest of the process is standard stuff, you are prompted by the installer for your JDBC connection string, here’s an example from a successful UCD installation:

...
[echo] Create database schema? Y,n [Default: Y]
Y
[echo] The following database types are supported: derby, mysql, oracle, sqlserver, postgres, db2.
[echo] Enter the database type to use. [Default: derby]
oracle
[echo] Enter the database driver. [Default: oracle.jdbc.driver.OracleDriver]

[echo] Enter the database connection string, including hostname, port, and SID. Eg. jdbc:oracle:thin:@localhost:1521:ORCL
jdbc:oracle:thin:@192.168.1.232:1521:XE
[echo] Enter the database schema name. (required if user has DBA role)
ibm_ucd
[echo] Enter the database username. [Default: ibm_ucd]
ibm_ucd
[echo] Enter the database password. [Default: password]
secretpassword
...

Hope this helps for anyone looking for this information in the meantime.

Sean's Musings as a Service

Creating UrbanCode databases in Oracle

Reference:

FootNotes