Creating the IBM Intelligent Operations Center databases

Create the IOCDB and IOCDATA databases and tables; and populate the tables with configuration and sample data.

About this task

Three .bat files are included in the installation files. The .bat files create the databases by using a set of database DDL and SQL files.

The create_db.bat database file creates the IOCDB database.
The create_datadb.bat database file creates the IOCDATA database.
The run_i18n.bat database file adds the language translations to the IOCDB database. Run this file after you run the two previous files.
If you want to use Integrated Crime Analytics, the updateIOCDB_db2.bat database file updates the IOCDB database.

First, create the IOCDB database, and then create the IOCDATA database. When you run the scripts, use the database instance user name that you specified during the database installation process; for example, you might have specified the suggested user name, db2i1own, as the value for the db2_instance_user property in the ioc.install.properties file. A Windows account was created for the user name that you selected.

Pipe the output statements to a trace log file to maintain a record of the execution after the script finishes. The script can take several minutes to finish. After the script has finished running, check the log file to ensure that the script completed successfully. You can ignore some errors that are listed in the log file that are expected and benign; for example, some delete statements fail because nothing exists yet to delete. The following list shows the main errors that do require action:

Failures to create the databases
Failures to connect to the databases
Errors that occur while geospatial indexes are being created in the databases
Hundreds of successive lines of failures to insert into the databases

Two database files are included in the installation files that, if necessary, you can use to remove the databases:

The drop_db.bat script file removes the IOCDB database.
The drop_datadb.bat script file removes the IOCDATA database.

After you remove the databases, you can re-create them by using the create batch files.

Procedure

Create the IOCDB database

Log on to Windows as the db2_instance_user user. For example, log on as the db2i1own user.
Open a command prompt with the Run as administrator option.
Go to the C:\home\ibmadmin\workspaces\spf\dev_ioc_install\ioc\config\db directory, which is where the database script batch files are located.
If your database home installation location is not the default location at C:\Program Files\IBM\SQLLIB, choose one of the following options:
- Set the value of the DB2INSTHOME environment variable to your database home installation location. For example, for IBM® Intelligent Operations Center V5.2.1, set the value of the environment variable to your Db2® home installation location.
- Edit the value of the database home installation location in the create_db.bat script file, and in the drop_db.bat script file.
Enter the following command:
```
create_db.bat > trace.log
```

Create the IOCDATA database

If your database home installation location is not the default location at C:\Program Files\IBM\SQLLIB, choose one of the following options:
- Set the value of the DB2INSTHOME environment variable to your database home installation location.
- Edit the value of the database home installation location in the create_datadb.bat script file, and in the drop_datadb.bat script file.
Enter the following command:
```
create_datadb.bat > trace_data.log
```
If your database home installation location is not the default location at C:\Program Files\IBM\SQLLIB, choose one of the following options:
- Set the value of the DB2INSTHOME environment variable to your database home installation location.
- Edit the value of the database home installation location in the run_i18.bat script file.

Add the language translations to the database

Enter the following command:
```
run_i18n.bat > trace_lang.log
```

If you want to use Integrated Crime Analytics, configure the database for Integrated Crime Analytics:

Go to the C:\home\ibmadmin\workspaces\spf\ioc_install\ioc\ica\db directory, which is where the Integrated Crime Analytics database script batch files are located.
If your database home installation location is not the default location at C:\Program Files\IBM\SQLLIB, choose one of the following options:
- Set the value of the DB2INSTHOME environment variable to your database home installation location.
- Edit the value of the database home installation location in the updateIOCDB_db2.bat script file.
Enter the following command:
```
updateIOCDB_db2 > traceica.log
```

Results

If you do not configure the database for Integrated Crime Analytics, you can ignore the following error that occurs in the WebSphere® Application Server Liberty Profile console log file when the WebSphere Application Server Liberty Profile server is started:

[err] com.ibm.db2.jcc.am.SqlSyntaxErrorException: "CAST.CRIME_MODEL" is
    an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.63.75[err]  at
    com.ibm.db2.jcc.am.fd.a(fd.java:679)[err]  at
    com.ibm.db2.jcc.am.fd.a(fd.java:60)[err]  at
    com.ibm.db2.jcc.am.fd.a(fd.java:127)[err]  at
    com.ibm.db2.jcc.am.yn.c(yn.java:2644)[err]  at
    com.ibm.db2.jcc.am.yn.d(yn.java:2632)[err]  at
    com.ibm.db2.jcc.am.yn.a(yn.java:2097)[err]  at
    com.ibm.db2.jcc.am.zn.a(zn.java:7197)[err]  at
    com.ibm.db2.jcc.t4.cb.h(cb.java:141)[err]  at
    com.ibm.db2.jcc.t4.cb.b(cb.java:41)[err]  at com.ibm.db2.jcc.t4.q.a(q.java:32)[err]
    at com.ibm.db2.jcc.t4.sb.i(sb.java:135)[err]  at
    com.ibm.db2.jcc.am.yn.gb(yn.java:2066)[err]  at
    com.ibm.db2.jcc.am.zn.pc(zn.java:3446)[err]  at
    com.ibm.db2.jcc.am.zn.b(zn.java:4236)[err]  at
    com.ibm.db2.jcc.am.zn.cc(zn.java:720)[err]  at
    com.ibm.db2.jcc.am.zn.executeQuery(zn.java:694)[err]  at
    com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery
     (WSJdbcPreparedStatement.java:552)[err]
    at
    com.ibm.iss.cros.dao.impl.CrimeModelDaoImpl.getAllModels(CrimeModelDaoImpl.java:92)[err]
    at com.ibm.iss.cros.timer.CrimeModelTimer.initializeTimers(CrimeModelTimer.java:95)[err]
    at
    com.ibm.iss.cros.timer.CrimeModelTimer.autoInitializeTimers(CrimeModelTimer.java:75)[err]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)[err]  at
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95)[err]  at
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)[err]
    at java.lang.reflect.Method.invoke(Method.java:620)[err]  at
    com.ibm.ejs.container.interceptors.InterceptorProxy.invokeInterceptor
     (InterceptorProxy.java:206)[err]
    at [internal classes][err]  at
    java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1157)[err]  at
    java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:627)[err]  at
    java.lang.Thread.run(Thread.java:798)