Introduction to IBM solidDB Universal Cache 6.3, Part 2: IBM solidDB Universal Cache setup

In-memory data caching for extreme speed

In Part 2 of this two-part series, set up IBM® solidDB® Universal Cache with IBM DB2® for Linux®, UNIX®, and Windows® to accelerate access to data. Learn about system and environment requirements and ways to acquire IBM solidDB Universal Cache code. Learn also how to install, configure, and use the IBM solidDB Universal Cache solution.

Share:

Paul Read (paul_read@uk.ibm.com), Product Introduction Manager, βetaWorks, IBM

Paul ReadPaul Read is a product introduction manager in the Global BetaWorks team, based in the IBM Lab in Hursley, Hampshire, United Kingdom. Paul is the lead technical professional for Beta and Early Support Programs for IBM Information Management Data Servers on Linux, Unix, and Windows. He also provides technical consultancy and skills transfer for the Information Management software products across all platforms.



04 May 2009

Also available in Chinese Vietnamese

Before you start

The introduction of IBM solidDB 6.3 Universal Cache provides you with the ability to accelerate access to IBM DB2 and IBM Informix® Dynamic Server (IDS) databases, as well as Oracle, Microsoft SQL Server, and Sybase, increasing their performance up to ten times. When storing performance-critical data from one of the supported disk-based databases into solidDB's in-memory cache, applications can access data with extreme speed because it is always kept in the computer's memory, rather than on disk. With IBM solidDB Universal Cache, both existing and new applications can now generate data workloads of more than 120,000 transactions per second and safely rely on predictable response times measured in microseconds to support growing numbers of users and data volumes, allowing companies to quickly unlock the business value of their data, which is what Information on Demand is all about.

In this tutorial, set up IBM solidDB Universal Cache with DB2 for Linux, UNIX, and Windows (DB2) to accelerate access to data. This tutorial assumes that you already have DB2 installed. This tutorial uses a simple application to demonstrate the basic principles of the Universal Cache and the synchronization of data with the back-end database. For the replication element of this tutorial to work, the DB2 database must be in logretain mode.

You will need to get a licensed copy of the IBM solidDB Universal Cache 6.3 code. Business Partners and ISVs can utilise the IBM PartnerWorld Software site to get up to speed with detailed product information, software downloads, and service options (see Resources). Customers can use the Trials and demos Web page to download a trial version (see Resources).

The InfoSphere CDC Management Console (part of the IBM solidDB Universal Cache solution) is a Windows application. However, because the exercises for this tutorial were performed using a SUSE Linux Enterprise Server V10, we have used the "Wine" emulator within the operating system to install and run the console to simplify the tutorial. This is not a supported architecture, and for normal operations, the CDC Management Console would be installed on a separate workstation.

About this series

This tutorial is the second part of a two-part series. The first installment explained the architecture and setup of IBM solidDB Universal Cache with DB2 for Linux, UNIX, and Windows.

About this tutorial

This tutorial will take you through the steps to:

  • Install the component parts of the solution
  • Configure a basic bidirectional replication solution
  • Demonstrate the effects of the solution using a simple application

Figure 1, below, shows the basic architecture and components of an IBM solidDB Universal Cache solution, including the InfoSphere Change Data Capture (CDC) technology:

Figure 1. IBM solidDB Universal Cache architecture
IBM solidDB Universal Cache architecture

Figure 1 is intended to assist you with the installation and configuration steps by providing a pictorial description of the processes.

The key elements of the architecture include:

  • IBM solidDB: The front-end database or cache.
  • RDBMS: The back-end database used for replication.
  • InfoSphere CDC: The replication tool that allows you to replicate data between the cache and the RDBMS.
  • InfoSphere CDC Access Server: The server that manages the replication processes for the cache and the RDBMS.
  • InfoSphere CDC Management Console: A GUI application used to configure, manage, and monitor the replication processes.

Note: In this tutorial, all components are installed on the same computer.

Objectives

In this tutorial, you will learn about system and environment requirements, the ways to acquire IBM solidDB Universal Cache code, and how to install, configure, and use the IBM solidDB Universal Cache solution. This tutorial includes a simple application written using IBM Data Studio Developer that demonstrates the abilities of the cache to synchronise and manage the data movement between the cache and the back-end database.

Prerequisites

This tutorial is written for relational database professionals whose skills and experience are at a beginning to intermediate level. You should have a general familiarity with using a UNIX command-line shell and a working knowledge of the SQL language and database administration.

System requirements

To run the examples in this tutorial, you need a Linux environment or vmware image with at least 1GB of memory, 1GB of free disk space, root access on the Linux box (or a sympathetic admin), and an installed DB2 database environment or the ability to create a DB2 for LUW environment. The tutorial assumes that you have created the userid called "solid" to manage and control the IBM solidDB Universal Cache environment.

The InfoSphere CDC Management Console will be installed in "Wine" to contain the whole exercise within the Linux environment. For details on Wine, see the WineHQ Web site (see the Resources section for a link). You should install Wine before you start. Some distributions of Linux may include a variant of Wine.


Install and configure IBM solidDB

This section focuses on installing and configuring the IBM solidDB, and creating the tables and indices. In this section, you will:

  • Install the IBM solidDB product code
  • Create and configure the front-end database, including the creation of the exercise tables
  • Create the tables and indices in the DB2 sample database

Basic instructions

  1. Log on to your machine with a valid userid.
  2. Ensure that you have the IP address of your machine, as you will use it later. Open a terminal window and verify your IP Address.
  3. Make a directory for the samples and installation images. The rest of this tutorial assumes that you created the directories.
    Listing 1. Creating Samples and installation images directories
    /opt/IBM/TUT/			(Samples)
    /opt/IBM/TUT/install_images/	(installation images)

Install the IBM solidDB code

  1. Become the root userid using the following command:
    su -
  2. When prompted, enter root's password.
  3. Create an installation directory for the code and switch to that directory using the following commands:
    mkdir –p /opt/IBM/solid
    cd /opt/IBM/solid
  4. The IBM solidDB code is contained in a self-installing image file. Execute the image file using the following command:
    /opt/IBM/TUT/install_images/solidDB-6.3-linux-x86.bin
  5. The installation GUI will present the start-up panel. Select the required language from the pull-down tab, and click on OK:
    Figure 2. IBM solidDB installation GUI
    IBM solidDB installation GUI
  6. Click on Next on the introduction panel.
  7. On the License panel, select the radio button to accept the terms in the license agreement, and click on Next:
    Figure 3. License panel
    License panel
  8. On the Install set panel, select Full, and click on Next:
    Figure 4. Install panel
    Install panel
  9. On the Install folder panel, enter the path /opt/IBM/solid/soliddb-6.3, and click on Next:
    Figure 5. Install folder
    Install folder
  10. On the Pre-installation panel, click on Next, and when complete, click on Done to complete the installation.
  11. Return to the developer userid by entering the following command:
    exit

    Note: You can achieve the same action by pressing Ctrl+d.

Configure the environment for the solid user

You should still be in the terminal window used in the previous section.

  1. From the terminal window, become the solid userid using the following command:
    su - solid
  2. When prompted, enter the password solid.
  3. You now need to alter the profile for the solid user (/home/solid/.profile).

    Note: To save editing the profile, you can copy the sample profile from the TUT sub-directory.

    cp /opt/IBM/TUT/solid.profile .profile

    Note the full-stop (.) before the final profile.

    Alternatively, you can edit the solid user profile and add the following:

    Listing 2. Addition to solid user profile
    # if running bash
    if [ -n "$BASH_VERSION" ]; then
        # include .bashrc if it exists
        if [ -f "$HOME/.bashrc" ]; then
        . "$HOME/.bashrc"
        fi
    fi
    
    # set PATH so it includes user's private bin if it exists
    if [ -d "$HOME/bin" ] ; then
        PATH="$HOME/bin:$PATH"
    fi
    
    export SOLID_HOME=/opt/IBM/solid/soliddb-6.3
    PATH=$SOLID_HOME/bin:$PATH
    export CLASSPATH=$SOLID_HOME/jdbc/SolidDriver2.0.jar:/opt/IBM/SDP70Shared/plugins/:.
    
    # The following three lines have been added by IBM DB2 instance utilities.
    if [ -f /home/db2inst1/sqllib/db2profile ]; then
        . /home/db2inst1/sqllib/db2profile
    fi
    
    export JAVA_HOME=/opt/ibm/db2/V9.5/java/jdk32
    PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
    
    #set the paths for the Informix IDS instance
    INFORMIXSERVER=demo_on
    INFORMIXDIR="/opt/IBM/informix"
    ONCONFIG=onconfig.demo_on
    INFORMIXSQLHOSTS="/opt/IBM/informix/etc/sqlhosts.cheetah2"
    PATH=$INFORMIXDIR/bin:$PATH:$JAVA_HOME/bin
    TERMCAP=$INFORMIXDIR/etc/termcap
    TERM=vt100
    export INFORMIXSERVER INFORMIXDIR ONCONFIG INFORMIXSQLHOSTS PATH TERMCAP TERM
    export CLASSPATH=/opt/IBM/SDP70Shared/plugins/
    	com.ibm.datatools.informix.driver_1.0.0.v200709190130/ifxjdbc.jar:$CLASSPATH
    
    #set the paths for the DataStudio project
    DATASTUDIO_WORKSPACE=/home/developer/IBM/rationalsdp7.0/workspace
    LAB_APPL_DIR=$DATASTUDIO_WORKSPACE/TelcoAp/
    CLASSPATH=$LAB_APPL_DIR:$CLASSPATH
    PATH=$LAB_APPL_DIR:$DATASTUDIO_WORKSPACE/TelcoApC/Debug/:$PATH
    
    export TUT=/opt/IBM/TUT
  4. Refresh the user profile using the following command:
    . ./.profile

    Note the spacing in the above command — full-stop, space, full-stop, oblique, full-stop, "profile".

    Note: Closing the current shell and opening a new one will have the same effect.

Create and configure the IBM solidDB Universal Cache front-end database

You should still be in the terminal window used in the previous section and still logged in as solid.

  1. Copy the solidDB license file to the current directory using the following command:
    cp $SOLID_HOME/solideval.lic .
  2. You now need to edit the solidDB configuration file(/home/solid/solid.ini).
    cp $TUT/solid.ini .

    Note: To save typing, copy the sample from TUT sub-directory.

    Or edit the solid.ini file to match the following:

    Listing 3. solid.ini sample
    [General]
    DefaultStoreIsMemory=yes
    [IndexFile]
    CacheSize=100M
    [LogReader]
    LogReaderEnabled=yes
    
    [Logging]
    Durabilitylevel=1
    [Com]
    Listen=tcp 1315
    [Data Sources]
    SOLDB=tcp 1315
    [SQL]
    Isolationlevel=1

    Notes:

    • The sample configuration files in /opt/IBM/solid/soliddb-6.3/samples/configuration contain detailed descriptions of all the server parameters. You can use IBM solidDB without the configuration file, in which case, the factory settings are used. However, for use with solidDB Universal Cache, you must specify a solid.ini configuration file with at least the LogReaderEnabled parameter as the default setting — no. CDC will therefore not work. In the solidDB configuration file, everything after the first semi-colon (;) on a line is regarded as a comment.
    • Durabilitylevel=1 is relaxed logging, which results in better performance but can also result in data loss.
    • If you are going to run more than one solidDB database server concurrently, you will need to select a unique TCP/IP port number for each database.
  3. Create the solidDB front-end database using the command:
    solid -f -x exit -Usoluser -Psolpwd -CSOLDB

    Note the spacing in the above command.

    Notes:

    • The userid and password are internal to the solidDB database and do not have to exist in the operating system.
    • -f starts the task in the foreground.
    • -x exit prompts for the database administrator's user name and password, creates a new database, and exits.
    • Options -U and -P can be used to give the database administrator's user name and password.
    • -C specifies the database catalog.
  4. Start the solidDB database server using the following command:
    solid

    The solid database server starts in the background.

    Note: If you encounter problems creating the database or starting the database server, check for errors in the file solerror.out.

  5. Verify that the solidDB database server started successfully by examining the output file solmsg.out using the following command:
    cat solmsg.out

    If at any time you need to stop the solidDB database server, enter the following command:

    solcon -eshutdown SOLDB soluser solpwd

Create the database objects

You should still be in the terminal window used in the previous section and still logged in as solid.

  1. Create an SQL file solidCreateObjects.sql containing the following:
    cp $TUT/solidCreateObjects.sql .

    Note: To save typing, copy sample sql script to your local directory.

    Or create a file to match the following:

    Listing 4. solidDB DDL
    DROP TABLE TUT.HLR_SUBS;
    DROP TABLE TUT.VLR;
    DROP SCHEMA TUT;
    
    CREATE SCHEMA TUT;
    CREATE TABLE TUT.HLR_SUBS (
    	IMSI CHAR(15) NOT NULL,
        MSISDN CHAR(12) NOT NULL PRIMARY KEY,
        MS_CATEGORY CHAR(5) NOT NULL
        VLR_NUMBER CHAR(5) NOT NULL,
        MSC_NUMBER CHAR(5) NOT NULL,
        RESTRICTED_FLAG CHAR(1) NOT NULL,
        COUNTRY_CODE CHAR(2) NOT NULL,
        ORGANIZATION VARCHAR(20) NOT NULL,
        PORTED_FROM VARCHAR(20),
        BEARER_SERVICE CHAR(1) NOT NULL,
        TELE_SERVICE CHAR(1) NOT NULL,
        PID VARCHAR(10) NOT NULL,
        UPSERTER VARCHAR(10) NOT NULL
    ) STORE IN MEMORY;
    CREATE INDEX TUT.HLR_SUBS_IDX01 ON TUT.HLR_SUBS (IMSI);
    
    CREATE TABLE TUT.VLR (
    	IMSI CHAR(15) NOT NULL,
    	MSISDN CHAR(12) NOT NULL PRIMARY KEY,
    	MS_CATEGORY CHAR(5) NOT NULL,
    	LOCATION_AREA_IDENTITY CHAR(10) NOT NULL,
    	BEARER_SERVICE CHAR(1) NOT NULL,
    	TELE_SERVICE CHAR(1) NOT NULL,
    	CONF_RADIO_CONTACT CHAR(1) NOT NULL,
    	CONF_SUBS_HLR CHAR(1) NOT NULL,
    	CONF_LOCATION_HLR CHAR(1) NOT NULL,
    	PID VARCHAR(10) NOT NULL,
    	UPSERTER VARCHAR(10) NOT NULL
    ) STORE IN MEMORY;
    CREATE INDEX TUT.VLR_IDX01 ON TUT.VLR (IMSI);
    
    COMMIT WORK;

    Notes:

    • You can create a template for the SQL to create the front-end tables by using a metadata extraction tool like db2look or dbschema. The resulting SQL file will not be exact but should only require minimal editing.
    • Be aware that the schema is case-sensitive.
  2. Create the user tables in the front-end database using the following command:
    solsql -a SOLDB soluser solpwd solidCreateObjects.sql
  3. Create an SQL file db2CreateObjects.sql, which contains the following:
    cp $TUT/db2/db2CreateObjects.sql .

    Note: To save typing, copy sample sql script to your local directory.

    Or create a file to match the following:

    Listing 5. DB2 DDL
    drop table TUT.HLR_SUBS;
    drop table TUT.VLR;
    CREATE TABLE TUT.HLR_SUBS (
    		IMSI CHAR(15) NOT NULL,
    		MSISDN CHAR(12) NOT NULL,
    		MS_CATEGORY CHAR(5) NOT NULL,
    		VLR_NUMBER CHAR(5) NOT NULL,
    		MSC_NUMBER CHAR(5) NOT NULL,
    		RESTRICTED_FLAG CHAR(1) NOT NULL,
    		COUNTRY_CODE CHAR(2) NOT NULL,
    		ORGANIZATION VARCHAR(20) NOT NULL,
    		PORTED_FROM VARCHAR(20),
    		BEARER_SERVICE CHAR(1) NOT NULL,
    		TELE_SERVICE CHAR(1) NOT NULL,
    		PID VARCHAR(10) NOT NULL,
    		UPSERTER VARCHAR(10) NOT NULL
    	) ;
    
    CREATE TABLE TUT.VLR (
    		IMSI CHAR(15) NOT NULL,
    		MSISDN CHAR(12) NOT NULL,
    		MS_CATEGORY CHAR(5) NOT NULL,
    		LOCATION_AREA_IDENTITY CHAR(10) NOT NULL,
    		BEARER_SERVICE CHAR(1) NOT NULL,
    		TELE_SERVICE CHAR(1) NOT NULL,
    		CONF_RADIO_CONTACT CHAR(1) NOT NULL,
    		CONF_SUBS_HLR CHAR(1) NOT NULL,
    		CONF_LOCATION_HLR CHAR(1) NOT NULL,
    		PID VARCHAR(10) NOT NULL,
    		UPSERTER VARCHAR(10) NOT NULL
    	) ;
    
    ALTER TABLE TUT.HLR_SUBS ADD CONSTRAINT HLR_SUBS_PK PRIMARY KEY (MSISDN);
    
    ALTER TABLE TUT.VLR ADD CONSTRAINT VLR_PK PRIMARY KEY (MSISDN);
  4. Create the user tables in the back-end database using the following commands:
    db2 connect to sample
    db2 -tvf db2CreateObjects.sql

    Note: You can ignore any SQL0204N errors since the script is dropping objects that may not yet exist.


Install and set up CDC

This section focuses on installing and configuring the IBM solidDB Universal Cache components. In this section you will:

Install the InfoSphere CDC for DB2

You should still be in the terminal window used in the previous section, and you will now install the InfoSphere CDC components. DB2 must be running before you start the install, and if you have not already set the log retain on in the DB2 database, you must do it now.

This section assumes that you have put the installation code into the /opt/IBM/TUT/install_images/ sub-directory.

  1. The InfoSphere CDC for DB2 code is contained in a self-installing image file. Change to the directory where the InfoSphere products are installed, and execute the image file:
    cd $TUT/install_images/ISCDC_LinuxDB2UDB/ISCDC_DB2\ UDB_6.3/
    ./setup-linux-x86-udb.bin -i swing

    The installation GUI will be displayed, and the stages of installation will be checked as they are completed. The This will be installed in the users home directory.

  2. Create a temporary directory, which will be used to store log files, using the following command:
    mkdir -p /tmp/CDC_DB2
  3. Change to the home directory for the DB2 CDC code, and run the configuration tool in the background so that the graphical interface can be readily viewed and status checked at any time.
    cd $HOME/Transformation\ Server\ for\ UDB/bin
    ./dmconfigurets &
  4. The GUI is presented to you. Click OK on the first screen.
  5. On the next screen, enter the following information in their respective fields:
    • Instance name: db2CDCinstance
    • Database name: sample
    • Username: db2inst1
    • Password: db2inst1
    • Refresh Loader: /tmp/CDC_DB2
    Figure 6. Create DB2 CDC instance
    Create DB2 CDC instance
  6. Click on OK to create the instance. Once the instance is created, you will see a message indicating the instance was created successfully:
    Figure 7. Successful creation of CDC instance
    Successful creation of CDC instance
  7. Select Yes to start the instance. The running CDC instance for DB2 is shown in the panel:
    Figure 8. DB2 CDC instance running
    DB2 CDC instance running
  8. Click on Close to exit the CDC Configuration Tool.

    Notes:

    • The authorization code (license) would normally be added; however, for this exercise, leave it blank.
    • You may need to execute Ctrl+c in the terminal window to return to a command prompt.

The installation of InfoSphere CDC for DB2 is complete.

Install the InfoSphere CDC for solidDB

Your solidDB database should still be running, unless you have stopped the database.

To check, use the ADMIN COMMAND 'status'; command in the solidDB SQL Editor (solsql).

  1. The InfoSphere CDC for solidDB code is contained in a self-installing image file. Change to the directory where the InfoSphere products are installed, and execute the image file:
    cd $TUT/install_images/
    ./setup-linux-x86-solid.bin -i swing

    The installation GUI will be displayed, and the stages of installation will be checked as they are completed. This will be installed in the users home directory.

  2. Change to the home directory for the solidDB CDC code, and run the configuration tool in the background so that the graphical interface can be readily viewed and status checked at any time.
    cd $HOME/Transformation\ Server\ for\ solidDB/bin
    ./dmconfigurets &
  3. The GUI is presented to you. Click on OK on the first screen
  4. On the next screen, enter the following information in their respective fields:
    • Instance name: solidCDCinstance
    • Username: soluser
    • Password: solpwd
    • Host name: [IP address]
    • Port: 1315
    Figure 9. Create solid CDC instance
    Create solid CDC instance
  5. Click on OK to create the instance. Once the instance is created, you will see a message indicating the instance was created successfully:
    Figure 10. Successful creation of solid CDC instance
    Successful creation of solid CDC instance
  6. Select Yes to start the instance. The running CDC instance for solid will be shown in the CDC Configuration Tool.
  7. Click on Close to exit the CDC Configuration Tool.

    Note: You may need to execute Ctrl+c in the terminal window to return to a command prompt.

The installation of InfoSphere CDC for solidDB is complete.

Install the InfoSphere CDC Access Server

  1. The InfoSphere CDC Access Server code is contained in a self-installing image file. Change to the directory where the InfoSphere products are installed, and execute the image file:
    cd $TUT/install_images//ISCDC_LinuxAccessServer/ISCDC_AccessServer_6.3
    ./dmaccess-6.3.1309.7-linux-x86-setup.bin -i swing
  2. The GUI is presented to you. Click on OK on the Introduction panel to move to the license panel.
  3. Select the radio button to accept the terms in the license agreement, then click on Next:
    Figure 11. Access Server License
    Access Server License
  4. Select the install folder, and enter /home/solid/Transformation Server Access Control for the install path (note the change in the installation path):
    Figure 12. Access Server install folder
    Access Server install folder
  5. Click on Next to continue.
  6. Choose link folder.
  7. Select the radio button next to Don't create links, and then click on Next to continue.
  8. Configure the Access Server by entering the following:
    • Port Number: 10101
  9. Click on Next.
  10. Configure the Access Account by entering the following in the respective fields:
    • Username: CDCAdmin
    • Password: CDCAdmin01
    • Confirm Password: CDCAdmin01
    Figure 13. Access Server Admin ID
    Access Server Admin ID
  11. Click on Next to continue.
  12. On the install summary, select Install.
  13. When the install has completed, select Done to finish.
  14. Start the Access Server in the background:
    cd $HOME/Transformation\ Server\ Access\ Control/bin
    ./dmaccess &

    This will return you to the command prompt. The Access Server is ready to manage the replication.

  15. Create the CDCAdmin user:
    ./dmcreateuser CDCAdmin CDCAdmin CDCAdmin CDCAdmin01 sysadmin true false false

The installation of InfoSphere CDC Access Server is complete.

Install the InfoSphere CDC Management Console

You're now going to install and configure the Management Console. At this time, the InfoSphere Management Console is available for Windows only. For this tutorial, please use Wine, as this will allow you to run an implementation of Windows under your Linux O/S.

  1. The InfoSphere CDC Management Console code is contained in a self-installing image file. Change to the directory where the InfoSphere products are installed, and execute the image file in Wine:
    cd $TUT/install_images/ISCDC_winMngmConsole/ISCDC_MgmtConsole_6.3
    wine dmclient-6.3.1309.7-setup.exe
  2. The GUI is presented to you. Click on OK on the Introduction panel to move to the license panel.
  3. Select the radio button to accept the terms in the license agreement, then click on Next:
    Figure 14. Management Console License
    Management Console License
  4. Select the install folder, and leave the installation directory as the default:
    Figure 15. Management Console install folder
    Management Console install folder
  5. Select Next to continue.
  6. Choose shortcut folder.
  7. Make sure the radio button for Other is selected with its default, then click on Next to continue.
  8. On the install summary, select Install.
  9. When the install has completed, click on Done to finish.

Configure IBM solidDB Universal Cache

Restarting IBM solidDB Universal Cache

If you closed down your system or stopped your vmware image at the end of previous exercise, then you need to restart the components of the IBM solidDB Universal Cache:

  1. Open a terminal window.
  2. Log on as the solid user
    su - solid
  3. Start the solid database:
    solid
  4. Start the solidCDCinstance:
    cd $HOME/Transformation\ Server\ for\ solidDB/bin
    ./dmconfigurets &
  5. Highlight the solidCDCinstance in the GUI, and click on Start.
  6. Once the status has changed to running, click on Close.
  7. Start the db2CDCinstance:
    cd $HOME/Transformation\ Server\ for\ UDB/bin
    ./dmconfigurets &
  8. Highlight the db2CDCinstance in the GUI, and click on Start.
  9. Once the status has changed to running, click on Close.

Configure the replication using the InfoSphere CDC Management Console

  1. Change to the location of the Management Console Installation and run the InfoSphere CDC Management Console:
    cd $HOME/Transformation\ Server\ Management\ Console/
    wine DmClient.exe
  2. Enter the CDCAdmin user name and password CDCAdmin01 along with the IP address of the Linux machine running under the VMWare image (your own IP address), then click on Login:
    Figure 16. Log on to the Management Console
    Log on to the Management Console

You are now running the Console and are ready to start defining the replication information.

Note: Sometimes the mouse does not respond within Wine. To make sure the focus is in place, right-click the mouse.

  1. To create a datastore for the back-end DB2 database, click on the Access Manager toolbar button:
    Figure 17. Create DB2 datastore
    Create DB2 datastore
  2. Select File > Access Server > New DataStore.
  3. Enter the following details for the db2 datastore, then select Ping, and the Connection Parameters are automatically filled in and are greyed out (do NOT click on OK):
    • Name: db2ds
    • Server: [system IP address]
    • Port: 10901
    Figure 18. Define datastore
    Define datastore
  4. Click on Connection Parameters, and enter the following details for the db2 datastore:
    • Login: db2inst1
    • Password: db2inst1
      (The passwords are blocked out automatically.)
    Figure 19. Connection parameters
    Connection parameters
  5. Select OK and OK again when back on the New Datastore screen. The Datastore for DB2 has now been created.
  6. In the right-hand Connection Management panel, you should see the db2ds datastore. Right-click on the db2ds, and click on Assign User:
    Figure 20. Assign user
    Assign user
  7. Confirm the connection details for the db2ds datastore.
  8. Double-click on the CDCAdmin user, check the userid is correct, that the Allow connection parameter saving option is selected, and click on OK.
  9. You will receive a confirmation message telling you that details will be updated after you reconnect to the server. Click on OK. The CDCAdmin user should appear under the db2 datastore db2ds in the right-hand Connection Management panel.
  10. Create a datastore for the front-end solidDB database. While still in the Access Manager tab, make sure your mouse focus is in the Datastore Management tab on the left. Click on the Access Manager, then select File > Access Server > New DataStore.
  11. Enter the following details for the db2 datastore, then select Ping, and the Connection Parameters are automatically filled in and are greyed out (do NOT click on OK):
    • Name: solidds
    • Server: 192.168.179.100
    • Port: 11101
    Figure 21. Create solid datastore
    Create solid datastore
  12. Click on Connection Parameters, and enter the following details for the db2 datastore:
    • Login: soluser
    • Password: solpwd
      (The passwords are blocked out automatically.)
    Figure 22. Define datastore
    Define datastore
  13. Select OK and OK again when back on the New Datastore screen. The Datastore for solidDB has now been created.
  14. In the right-hand Connection Management panel, you should see the solidds datastore. Right-click on the solidds, and click on Assign User:
    Figure 23. Assign user
    Assign user
  15. Confirm the connection details for the solidds datastore.
  16. Double-click on the CDCAdmin user, check the userid is correct, that the Allow connection parameter saving option is selected, and click on OK.
  17. You will a confirmation message telling you that details will be updates after you reconnect to the Server. Click on OK. The CDCAdmin user should appear under the solidDB datastore solidds in the right-hand Connection Management panel.
  18. Disconnect and reconnect to the Access Server.
    • To disconnect, select File > Access Server > Disconnect.
    • To reconnect, select File > Access Server > Connect
  19. Enter the CDCAdmin user and CDCAdmin01 password, and choose the Server Name ip address from the drop-down list. Make sure the port is set to 10101.
  20. Change ddl_awareness parameter for db2ds, and ensure that you are in the configuration view.
    Figure 24. Configuration view
    Configuration view
  21. Right-click on the db2ds datastore.
  22. Choose Properties > System Parameters > Add, and you should see an Add System Parameter window:
    Figure 25. Change ddl_awareness parameter
    Change ddl_awareness parameter
  23. Enter the following information in the respective fields, then click on OK twice to accept the change:
    • Parameter name: ddl_awareness
    • Value: false

Configure the subscriptions with InfoSphere Change Data Capture Management Console

Configure the DB2 to IBM solidDB Universal Cache subscription

  1. Click on the Configuration tab and then the Subscription tab.
  2. To create a new subscription that will define a source of DB2 and a target of solid, click on Subscription > New Subscription.
  3. Enter the following details, then select OK:
    • Name: db2_solid
    • Source: db2ds
    • Target: solidds
    Figure 26. DB2 to solidDB subscription
    DB2 to solidDB subscription

    You should see the new subscription listed under Default Project in the Subscription panel to the left.

  4. With your focus on the DB2_SOLID subscription, right-click on DB2_SOLID, and select Map Tables.
  5. You're going to configure a one-to-one mapping type, so ensure the radio button for One-to-One Mappings is selected, then click on Next.
    Figure 27. One-to-one mapping
    One-to-one mapping
  6. Expand the TUT schema, select the two TUT tables, then click on Next:
    Figure 28. Select TUT tables
    Select TUT tables
  7. Select the radio button to Map to existing target tables, then click on Next:
    Figure 29. Map to existing tables
    Map to existing tables
  8. Create a mapping for each of the tables in the source TUT schema.
  9. Expand the TUT schema, select the first table (HLR_SUBS), then click on Next:
    Figure 30. HLR_SUBS
    HLR_SUBS
  10. The mappings for both tables are listed. Check they are correct, then select Next:
    Figure 31. Check mappings
    Check mappings
  11. Select the check box to Prevent Recursion, then select Next:
    Figure 32. Prevent recursion
    Prevent recursion
  12. You'll get a confirmation panel. Select Finish, and you should now see both table mappings in the right-hand Table Mappings panel.
  13. The mapping now needs to be adjusted to specify what to do with conflicts.

    You want to specify what happens if a row is attempted to be inserted into the target database with the same primary key.

    With the Table Mappings tab in focus, right-click on the TUT.HLR_SUBS table, and choose Edit Mapping Details.

  14. Click on the Conflicts tab, and select the primary key —MSISDN.
  15. Select Source Wins for the Conflict Resolution Method, and click on Apply:
    Figure 33. Conflict resolution
    Conflict resolution
  16. Close the DB2_SOLID: HLR_SUBS – HLR_SUBS panel.
  17. Repeat the steps for the TUT.VLR table, then close the DB2_SOLID: VLR panel.

Configure the IBM solidDB Universal Cache to DB2 subscription

  1. Click on the Configuration tab and then the Subscription tab.
  2. To create a new subscription that will define a source of solid and a target of db2, select Subscription > New Subscription.
  3. Enter the following details, then select OK:
    • Name: solid_db2
    • Source: solidds
    • Target: db2ds

    You should see the new subscription listed under Default Project in the Subscription panel to the left.

  4. With your focus on the SOLID_DB2 subscription, right-click on SOLID_DB2, and select Map Tables.
  5. You're going to configure a one-to-one mapping type, so ensure the radio button for One-to-One Mappings is selected, then click on Next:
    Figure 34. One-to-one mapping
    One-to-one mapping
  6. Expand the TUT schema, and select the two TUT tables, then click on Next:
    Figure 35. Select TUT tables
    Select TUT tables
  7. Map to the target tables, and ensure that you select the radio button to Map to existing target tables, then click on Next:
    Figure 36. Map target tables
    Map target tables
  8. Create a mapping for each of the tables in the source TUT schema.
  9. Expand the TUT schema, and select the first table (HLR_SUBS), then click on Next:
    Figure 37. Select HLR_SUBS
    Select HLR_SUBS
  10. The mappings for both tables are listed. Check they are correct , then select Next:
    Figure 38. Check mappings
    Check mappings
  11. Select the check box to Prevent Recursion flag, then select Next:
    Figure 39. Prevent recursion
    Prevent recursion
  12. You will get a confirmation panel. Select Finish, and you should now see both table mappings in the right-hand Table Mappings panel.
  13. The mapping now needs to be adjusted to specify what to do with conflicts.

    You want to specify what happens if a row is attempted to be inserted into the target database with the same primary key.

    With the Table Mappings tab in focus, right-click on the TUT.HLR_SUBS table, and choose Edit Mapping Details.

  14. Click on the Conflicts tab, and select the primary key — MSISDN.
  15. Select Target Wins for the Conflict Resolution Method, and click on Apply:
    Figure 40. Conflict resolution
    Conflict resolution
  16. Close the SOLID_DB2: HLR_SUBS – HLR_SUBS panel.
  17. Repeat the steps for TUT.VLR table, then close the SOLID_DB2: VLR panel.

Set the bi-directional replication to active

Now that the datasources and subscriptions have been added, and the relevant tables mapped correctly, you can turn on the replication.

  1. Make sure you are in the correct panel for switching the replication on. Click on the Monitoring tab and then on the Subscriptions tab.
  2. To set the mirroring on for the DB2_SOLID subscription, right-click on the DB2_SOLID subscription, and select Start Mirroring (Continuous):
    Figure 41. Set mirroring
    Set mirroring
  3. You will see a confirmation panel. Click on Yes to continue. You should see the status switch to "Mirror Continuous" on the Monitoring Subscription panel:
    Figure 42. DB2-solid replication started
    DB2-solid replication started
  4. Repeat the previous steps for the SOLID_DB2 replication:
    Figure 43. solid-DB2 replication started
    solid-DB2 replication started

Bi-directional replication is now set up. Both the IBM solidDB and DB2 databases can partake in bi-directional replication. In the next section, you will use the Telco App application to insert data into either database and see it replicate to the corresponding database. The application will also allow you to run a set of transactions against either database — the updates being replicated to the corresponding target database.

Note: Do not close the Management Console, as you will use it later.


Simulate a TelCo environment using the TelCo Application

If you closed your vmware image at the end of the previous exercise, please refer to the "Restarting IBM solidDB Universal Cache" in the previous exercise.

You are now ready to load data into either database and see it replicate to the corresponding target. The Telco Application can be used to:

  • Load large amounts of data into a database
  • Run a number of different transaction types against the data within the database and display TPS (transactions per second) information

The TelCo Application has been adapted specifically for this tutorial. You will use the application in a number of ways within the tutorial exercises that follow. The programs have been developed using Data Studio. Both the source and run-time code are included in the tutorial's package (see Downloads).

Load data into the back-end database

You will use the Telco Application to insert some rows into the two tables on the back-end database. You will see them replicate to the corresponding target database.

  1. Open a new terminal window, and switch to the "solid" user.
  2. Run the Telco Application insert routine to insert 10 HLR_SUBS and occasional associated VLR rows into the back-end tables using the following command:
    Insert.sh [number of HLR_SUBS rows] [database type] [port number]
    Insert.sh 10 db2 50002
    Figure 44. Insert 10 HLr_SUBS records
    Insert 10 HLr_SUBS records

    Note: For inserts of 10 rows or less, you can see the data output to the screen. The number of VLR rows added will vary.

    For the purposes of this tutorial, the application records specific audit data within each data row for both tables. In both tables there are two columns that will aid you in visualizing the origin of the insert or update that occurred on a specific data row. A 'process id' identifies which process updated the row, and the 'upserter' identifies the database where the row was updated.

  3. Manually check that the rows have been inserted into the back-end database using the following command (or use your preferred method for interrogating databases):
    db2 CONNECT TO sample;
    db2 select IMSI ,MSISDN ,VLR_NUMBER ,COUNTRY_CODE ,PID ,UPSERTER from TUT.hlr_subs ;
    db2 select IMSI ,MSISDN ,PID ,UPSERTER from TUT.vlr;
    Figure 45. Check rows in DB2
    Check rows in DB2
  4. Manually check that the rows have been inserted into the front-end database using the following command (or use your preferred method for interrogating databases):
    solsql -a SOLDB soluser solpwd
    select IMSI ,MSISDN ,VLR_NUMBER ,COUNTRY_CODE ,PID ,UPSERTER from TUT.hlr_subs ;
    select IMSI ,MSISDN ,PID ,UPSERTER from TUT.vlr;
    exit ;
    Figure 46. Check rows in solidDB
    Check rows in solidDB

    The solid database now has the same rows that were inserted into the db2 back end database.

  5. Use the Values.sh shell script to further check and understand how the rows have been populated.
    Values.sh db2 solid

    You will see the following results. Having just inserted 10 rows into DB2, you can see that in both databases, the data has originated from the DB2 back-end database.

    Figure 47. Check row counts for both databases
    Check row counts for both databases

Set statistics gathering on the Management Console

Before you start to load a large amount of data, it is worth showing how the statistics can be viewed on the Management Console.

  1. With the Monitoring and Subscription tabs selected, right-click on the DB2_SOLID subscription, then select Show Statistics. The below statistics panel appears:
    Figure 48. Show statistics
    Show statistics
  2. To set the collect statistics on both subscriptions, first make sure the mouse focus is on the DB2_SOLID subscription in the upper subscription panel.
  3. In the statistics panel, click on the Collect statistics tab.
  4. Now make sure the mouse focus is on the SOLID_DB2 subscription in the upper subscription panel.
  5. In the statistics panel, click on the Collect statistics tab.

Load data into the front-dnd database

Now that you have seen 10 rows inserted into the back-end database and replicated to the front-end database, you will now insert a larger batch into the solidDB front-end database.

With this exercise, you will first load the solidDB database with 10000 HLR_SUBS rows and the associated VLR records.

  1. Insert 10,000 rows into the solidDB database using the TelCo application:
    Insert.sh 10000 solid 1315
    Figure 49. Insert 10,000 rows into the solidDB
    Insert 10,000 rows into the solidDB
  2. Use the Values script to check the data has replicated:
    Values.sh db2 solid
    Figure 50. Check data has replicated
    Check data has replicated
  3. In the Management Console, you can click on Operations/s for each subscription to see a throughput graph.
    Figure 51. Check throughput graph
    Check throughput graph
  4. Insert 10,000 rows into the DB2 database using the TelCo application:
    Insert.sh 10000 db2 50002
    Figure 52. Insert 10,000 rows into the DB2
    Insert 10,000 rows into the DB2
  5. Use the Values script to check the data has replicated:
    Values.sh db2 solid
    Figure 53. Check data has replicated
    Check data has replicated
  6. There is also a Counts script that will show counts of both tables in any database. Run the Counts script using the following command:
    Counts.sh db2 solid
    Figure 54. Check row count in both databases
    Check row count in both databases

Use the Telco Application to run transactions against either database

The Telco Application can be used to perform a series of transactions against the data and gather timing statistics. Details of the specific transaction types that the code simulates are provided in the Appendix B (see the "Details of the sample application" PDF in the Downloads section).

The Telco applications can also be run from Data Studio. (See Appendix B for further details.)

  1. Using the data in the solid database, run the TelCo Application in Transaction mode to simulate 10,000 transactions against the database. The usage for the command is as follows:
    Transact.sh [number of HLR_SUBS rows] [database type] [port number]
    Transact.sh 10000 solid 1315
    Figure 55. Simulate 10,000 transactions against solidDB
    Simulate 10,000 transactions against solidDB
  2. Use the Values script to check the data has replicated:
    Values.sh db2 solid
    Figure 56. Check replication
    Check replication
  3. There's also a fully compiled 'c' code executable that can be used in the same way as above against the solid database. Run the following command, and compare the results with the results you obtained from the first step above:
    TelcoApC [number of Transactions to take place] [port number]
    TelcoApC 10000 1315
    Figure 57. Simulate 10,000 transactions against solidDB
    Simulate 10,000 transactions against solidDB
  4. Use the Values script to check the data has replicated:
    Values.sh db2 solid

Run concurrent transactions on both databases

In this step, you will have two sessions running transactions against a database. One session will run against the front-end database, and the other will run against the back-end database. Bi-directional replication will occur between them both.

  1. Open a new terminal window, and switch to the "solid" user.
  2. Run the Transact script to perform 2000 transactions against the db2 database:
    Transact.sh 2000 db2 50002

    Make sure that you start the second step before the first step completes.

  3. From the original terminal, run the Transact script to run a set of transactions against the solid database at the same time as the transactions are occurring against the db2 database above:
    Transact.sh 2000 solid 1315
  4. Once the first two steps have completed, use the Values script to check the data has been replicated:
    Values.sh db2 solid
  5. Use the Counts script to check the counts of both tables on both databases:
    Counts.sh db2 solid

Conclusion

IBM solidDB Universal Cache is relational in-memory database software that delivers extreme speed, performing up to ten times faster than conventional databases. It uses the familiar SQL language to allow applications to achieve throughput of tens of thousands of transactions per second with response times measured in microseconds, also providing applications extreme data availability with sub-second failover. It can be deployed as a cache for IBM DB2, IDS, or Oracle RDBMS to deliver performance-critical data with extreme speed.

This tutorial has presented an IBM solidDB Universal Cache solution and explained how to install and configure its component parts. It has taken you step-by-step through a sample solution, the IBM solidDB Universal Cache, and a DB2 LUW back-end database. It has included a full bi-directional replication and a simple sample application. You should now be able to develop and implement your own solution.


Downloads

DescriptionNameSize
Sample scripts and application codeTutorial.rar10KB
Details of the sample application1Tutorial_extras.rar472KB

Note

  1. Includes a PDF that's provided to describe the sample application.

Resources

Learn

Get products and technologies

  • IBM solidDB Universal Cache software: Download the IBM solidDB Universal Cache software. The trial software has a 90-day licence and includes:
    • IBM solidDB Universal Cache
    • IBM InfoSphere CDC for IBM solidDB
    • IBM InfoSphere CDC for other back-end database (including IBM DB2 and IDS)
    • IBM InfoSphere CDC Access Server
    • IBM InfoSphere CDC Management Console
    • IBM solidDB Documentation Package
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=387405
ArticleTitle=Introduction to IBM solidDB Universal Cache 6.3, Part 2: IBM solidDB Universal Cache setup
publish-date=05042009