Overview

Skill Level: Any Skill Level

It shows how to define "JDBC data source for the analysis database" mentioned in
Configuring InfoSphere Information Analyzer
https://www.ibm.com/support/knowledgecenter/SSZJPZ_11.7.0/com.ibm.swg.im.iis.ia.administer.doc/topics/New_config_steps_super.html

Ingredients

- InfoSphere Information Server 11.3 or higher with Information Analyzer installed.

- WebSphere Application Server Network Deployment Liberty profile (LWAS) used as the Information Server Services tier.

- An SQL Server DB to be used as Information Analyzer DB (IADB) configured as below.

  • If possible, create the IADB using create_ia_db.sql as per

Pre-installing the IBM InfoSphere Information Analyzer analysis database in a Microsoft SQL Server database system
https://www.ibm.com/support/knowledgecenter/SSZJPZ_11.7.0/com.ibm.swg.im.iis.productization.iisinfsv.install.doc/topics/wsisinst_pre_install_sql_server_iadb_win.html

  • If creating a new IADB is not an option, make sure the existing DB is configured as per create_ia_db.sql, that is, set the DB options, create table / stored procedure, etc as per the script.
  • Enable XA Transaction, Install the XA stored procedures included in Microsoft JDBC driver, and install the XA stored procedures in cluded in DataDirect JDBC driver, see

Pre-installing the metadata repository database in a Microsoft SQL Server 2016 database system
https://www.ibm.com/support/knowledgecenter/SSZJPZ_11.7.0/com.ibm.swg.im.iis.productization.iisinfsv.install.doc/topics/wsisinst_pre_install_sql_server_db_win_2016.html

Pre-installing the metadata repository database in a Microsoft SQL Server 2014 database system
https://www.ibm.com/support/knowledgecenter/SSZJPZ_11.7.0/com.ibm.swg.im.iis.productization.iisinfsv.install.doc/topics/wsisinst_pre_install_sql_server_db_win_2014.html

Pre-installing the metadata repository database in a Microsoft SQL Server 2012 database system
https://www.ibm.com/support/knowledgecenter/SSZJPZ_11.5.0/com.ibm.swg.im.iis.productization.iisinfsv.install.doc/topics/wsisinst_pre_install_sql_server_db_win_2012.html

Step-by-step

  1. Add appropriate variables for the new analysis database in bootstrap.properties file

    1.1. Go to <InformationServer-install-dir>/wlp/usr/servers/iis/ directory. For example, cd /opt/IBM/InformationServer/wlp/usr/servers/iis in Unix/Linux, C:\IBM\InformationServer\wlp\usr\servers\iis in Windows.

    Backup bootstrap.properties file.

    Open bootstrap.properties in a text editor, and add next lines

     

    mssql.ia.db.hostname=mysqlsvr.example.com

    mssql.ia.db.name=iadb

    mssql.ia.db.port=1433

    mssql.ia.db.username=iauser

    mssql.ia.db.password=

     

    at the end of the file.

    Save the file and exit the editor.

     

    1.2. Encrypt the DB user’s password using AppServerAdmin command.

    For example, you can use

    /opt/IBM/InformationServer/ASBServer/bin/AppServerAdmin.sh -was -file /opt/IBM/InformationServer/wlp/usr/servers/iis/bootstrap.properties -key mssql.ia.db.password -value XXX

    or

    C:\IBM\InformationServer\ASBServer\bin\AppServerAdmin.bat -was -file C:\IBM\InformationServer\wlp\usr\servers\iis\bootstrap.properties -key mssql.ia.db.password -value XXXX

     

    Check if it’s encrypted in the file, e.g. using

    grep mssql.ia.db.password  bootstrap.properties

    command.

     

    Note: use your actual value for above variables.

  2. Define data source for SQL Server in iadb.dataSources.xml file

    Backup wlp/usr/servers/iis/iadb.dataSources.xml file.

    Open iadb.dataSources.xml in a text editor.

    Add next lines just above the </server> line.

     

    <jdbcDriver id=”iadb_DD_MSSQLDriver libraryRef=”iis” javax.sql.ConnectionPoolDataSource=”com.ibm.isf.jdbcx.sqlserver.SQLServerDataSource40″/>
    <dataSource jndiName=”jdbc/IADB_MSSQL”
        id=”DataSource_IADB_MSSQLDataSource” jdbcDriverRef=”iadb_DD_MSSQLDriver
        isolationLevel=”TRANSACTION_READ_COMMITTED”
        beginTranForResultSetScrollingAPIs=”true” connectionManagerRef=”ConnectionPool_IADB_MSSQLDataSource>
      <properties.datadirect.sqlserver databaseName=”${mssql.ia.db.name}” serverName=”${mssql.ia.db.hostname}” portNumber=”${mssql.ia.db.port}” user=”${mssql.ia.db.username}” password=”${mssql.ia.db.password}”/>
    </dataSource>
    <connectionManager agedTimeout=”7200″ connectionTimeout=”180″ id=”ConnectionPool_IADB_MSSQLDataSource maxPoolSize=”100″ minPoolSize=”1″ purgePolicy=”FailingConnectionOnly”/>

     

    Save the file and exit the editor.

    Note:

    – Keep the existing lines as is.

    – You can use your preferred name for the properties in bold like jndiName, jdbcDriverRef.

     

    If the original file looks like below,

     

    <?xml version="1.0" encoding="UTF-8"?>
    <server>
    <jdbcDriver id="iadb_db2Driver" libraryRef="iis" javax.sql.ConnectionPoolDataSource="com.ibm.db2.jcc.DB2ConnectionPoolDataSource"/>
    <dataSource jndiName="jdbc/IADB"
    id="DataSource_IADBDataSource" jdbcDriverRef="iadb_db2Driver"
    isolationLevel="TRANSACTION_READ_COMMITTED"
    beginTranForResultSetScrollingAPIs="true" connectionManagerRef="ConnectionPool_IADBDataSource">
    <properties.db2.jcc databaseName="${ia.db.name}" portNumber="${ia.db.port}" driverType="4"
    serverName="${ia.db.hostname}" password="${ia.db.password}" user="${ia.db.username}"/>
    </dataSource>

    <connectionManager agedTimeout="7200" connectionTimeout="180" id="ConnectionPool_IADBDataSource" maxPoolSize="100" minPoolSize="1" purgePolicy="FailingConnectionOnly"/>
    </server>

    modified one would look like

    <?xml version="1.0" encoding="UTF-8"?>
    <server>
    <jdbcDriver id="iadb_db2Driver" libraryRef="iis" javax.sql.ConnectionPoolDataSource="com.ibm.db2.jcc.DB2ConnectionPoolDataSource"/>
    <dataSource jndiName="jdbc/IADB"
    id="DataSource_IADBDataSource" jdbcDriverRef="iadb_db2Driver"
    isolationLevel="TRANSACTION_READ_COMMITTED"
    beginTranForResultSetScrollingAPIs="true" connectionManagerRef="ConnectionPool_IADBDataSource">
    <properties.db2.jcc databaseName="${ia.db.name}" portNumber="${ia.db.port}" driverType="4"
    serverName="${ia.db.hostname}" password="${ia.db.password}" user="${ia.db.username}"/>
    </dataSource>

    <connectionManager agedTimeout="7200" connectionTimeout="180" id="ConnectionPool_IADBDataSource" maxPoolSize="100" minPoolSize="1" purgePolicy="FailingConnectionOnly"/>

    <jdbcDriver id="iadb_DD_MSSQLDriver" libraryRef="iis" javax.sql.ConnectionPoolDataSource="com.ibm.isf.jdbcx.sqlserver.SQLServerDataSource40"/>
    <dataSource jndiName="jdbc/IADB_MSSQL"
    id="DataSource_IADB_MSSQLDataSource" jdbcDriverRef="iadb_DD_MSSQLDriver"
    isolationLevel="TRANSACTION_READ_COMMITTED"
    beginTranForResultSetScrollingAPIs="true" connectionManagerRef="ConnectionPool_IADB_MSSQLDataSource">
    <properties.datadirect.sqlserver databaseName="${mssql.ia.db.name}" serverName="${mssql.ia.db.hostname}" portNumber="${mssql.ia.db.port}" user="${mssql.ia.db.username}" password="${mssql.ia.db.password}"/>
    </dataSource>
    <connectionManager agedTimeout="7200" connectionTimeout="180" id="ConnectionPool_IADB_MSSQLDataSource" maxPoolSize="100" minPoolSize="1" purgePolicy="FailingConnectionOnly"/>
    </server>

  3. Include iadb.dataSource.xml in server.xml

    Backup wlp/usr/servers/iis/server.xml file.

    Open server.xml file in a text editor, check if

    <include location=”iadb.dataSources.xml” optional=”true”/>

    is there below

    <include location=”dataSources.xml”/>

    line.

    If not, add

    <include location=”iadb.dataSources.xml” optional=”true”/>

    line below

    <include location=”dataSources.xml”/>

    Save the file exit editor.

  4. Restart WAS

    You can use next command.

    – In Unix/Linux: ASBServer/bin/MetadataServer.sh restart

    – In Windows: ASBServer\bin\MetadataServer.bat restart

  5. Complete remaining steps to use the SQL Server DB as IADB

    Proceed to complete the steps explained in “Procedure” section of

    Configuring InfoSphere Information Analyzer

    https://www.ibm.com/support/knowledgecenter/SSZJPZ_11.7.0/com.ibm.swg.im.iis.ia.administer.doc/topics/New_config_steps_super.html

    Note: To create a data connection for the analysis database, you need to

    (1) define ODBC DSN in Information Server Engine tier exploiting SQL Server Native Wire Protocol Driver VMsqls*.so or VMSQLS*.DLL.

    (2) then in IMAM, import metadata of IADB in SQL Server using this DSN.

Join The Discussion