Configuring SQL Server for IBM Lotus Mashups

When you configure Microsoft SQL Server 2005 for IBM Mashup Center, you must configure it for both IBM InfoSphere MashupHub and Lotus Mashups. This topic covers only the configuration steps for Lotus Mashups in a Windows environment. To configure SQL Server for InfoSphere MashupHub, see the IBM InfoSphere MashupHub User and Administrator Guide.

PREREQUISITES: Fulfill the following prerequisites before starting to configure SQL Server for Lotus Mashups:

• Install IBM Mashup Center. For instructions on installing IBM Mashup Center, see the IBM Mashup Center Quick Start Guide.
.

• Install the IBM Mashup Center 1.1.0.1 fixpack, available on IBM Support Fix Central.


• Install the SQL Server database server Version 2005. For instructions on installing SQL Server, see the SQL Server documentation.

To configure SQL Server for Lotus Mashups, do the following steps:

Step 1: Creating a new database

Create a new database with the following settings:

• Provide a database name, for example MASHUPS.



• From the Options menu, select Collation Name in the list according to your environment. The best performance occurs when you match the default database collation. Also, we recommend that you use a case-sensitive collation. For example, on an English system, set the collation to SQL_Latin1_General_CP1_CS_AS.



• Create a schema, for example MASHUPS. Also specify the owner..

Step 2: Creating the SQL Server user and tables

To create the Lotus Mashups SQL Server user and the tables, do the following steps:

1. Open either Microsoft SQL Server Management Studio or the OSQL utility.

Note: If use osql to execute a script named xxx.sql, run the command following this format:
 
osql -U sa -i \XXX.sql


2. Run the following SQL scripts. Note that the command format to run SQL scripts is @<file_path
    
   /script.sql, where <file_path
    
   is the path of the script and script.sql is the script you want to run. In Lotus Mashups, the <file_path
    
   is <MASHUPS_ROOT
    
   \Config\dbscripts\sqlserver. Note: If you use the OSQL utility to run the scripts, use this command format: >osql -U sa -i <file_path
    
   \XXX.sql.
   
   
   
   
   • To create the schema, run createSchema_mashup.sql.
   
   
   • To create the application user, run createAppUser_mashup.sql.
   
   
   • To create the Lotus Mashups tables, run defineSchema_mashup.sql.
   
   
   • To grant access for the application user, run appGrants_mashup.sql.
   
   
   • To configure the database, run configureDatabase_mashup.sql.
   
   

Step 3: Configuring Lotus Mashups

To configure Lotus Mashups, do the following steps:

1. Open MashupCenter_install_root/Config/database_sqlserver.properties, and specify the connectivity information for the database and user, for example:
   
   sqlServerUser=MASHUPSUSR
   sqlServerUserPassword=
   
   sqlServerServerName=
   sqlServerPortNumber=1433
   
   sqlServerDatabaseName=MASHUPS


2. Create the data source and configure Lotus Mashups to use RDBMS persistence by changing to the MashupCenter_install_root/Config/ folder and running the following task:
   
   

   config.bat action-create-db-mashup-sqlserver



3. Start the WAS server.

Step 4: Testing the connection

To confirm that everything is working as expected, do the following steps:

1. Open the WebSphere Application Server (WAS) administrative console, and click Resources > JDBC > Data sources.



2. Select the check box beside the new data source and test the connection.