​ Configure External Databases to IBM Sterling B2B Integrator

We can add multiple

​​

external databases to IBM Sterling B2B Integrator. To add custom database need to do the following things,

1. Stop IBM Sterling B2B Integrator
2. Navigate to SI_INSTALL_DIR\properties and edit jdbc_customer.properties.in and add the below properties.
3. Run setupfiles.bat/sh (SI_INSTALL_DIR\bin\setupfiles.bat/sh)
4. Start IBM Sterling B2B Integrator

Note: SI_EXTENSION is my pool name. We can name whatever we want.

​​

MS SQL Server Database

Need to change the test query depending on server, For MS SQL Server Database
SI_EXTENSION.testOnReserveQuery=SELECT GETDATE()

Oracle Database

Need to change the test query depending on server, For Oracle Database
SI_EXTENSION.testOnReserveQuery=SELECT * FROM DUAL

jdbc_customer.properties.in

SI_EXTENSION.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
SI_EXTENSION.url=jdbc:sqlserver://localhost:1433;databaseName=SI_EXTENSION;SelectMethod=cursor
SI_EXTENSION.user=sa
SI_EXTENSION.password=
#SI_EXTENSION.maxconn=20
SI_EXTENSION.storedProcClassName=com.sterlingcommerce.woodstock.util.frame.jdbc.SybaseStoredProcQuery
SI_EXTENSION.varDataClassName=com.sterlingcommerce.woodstock.util.frame.jdbc.MSSQLVarData
SI_EXTENSION.catalog=SI_EXTENSION
SI_EXTENSION.type=remote
SI_EXTENSION.tr

​​

ansaction=true
SI_EXTENSION.testOnReserve=true
SI_EXTENSION.testOnReserveQuery=SELECT GETDATE()
SI_EXTENSION.testOnReserveInterval=60000
SI_EXTENSION.maxRetries=100
SI_EXTENSION.blobPageSize=1024000
SI_EXTENSION.compressBlob=true
SI_EXTENSION.dbvendor=mssql
SI_EXTENSION.buffersize=500
SI_EXTENSION.maxsize=20
SI_EXTENSION.initsize=0
SI_EXTENSION.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.ConnectionFactory
SI_EXTENSION.behaviour=2
SI_EXTENSION.lifespan=0
SI_EXTENSION.idletimeout=86400000
SI_EXTENSION.housekeepinginterval=3600000
SI_EXTENSION.waittime=1000
SI_EXTENSION.errorMissingTable=16945
SI_EXTENSION.systemPool=true       

How to ​Validate Connection Pool Creation

Go to Dashboard --> Operations --> System --> Troubleshooter --> Database Usage we will find connection pool.

Question

Can I use AD (Active Directory) Logins in Sterling B2B Integrator to communicate with SQL Server?

Answer

Active Directory logins cannot be used for Sterling B2B Integrator and SQL Server. The jdbc driver Sterling B2B Integrator uses does not allow using AD accounts. SQL Server accounts must be used.

http://www-01.ibm.com/support/docview.wss?uid=swg21683970

IBM B2B Sterling Integrator (SI) and Sterling File Gateway Installation Procedure

1. Install and Configure Sterling Integrator DB. Follow the below link to create and configure database,
         http://sirishg.blogspot.com/2012/12/sterling-integrator-oracle-user-and.html

2. Update License Set.
   Need to update the License Set because while installing SI it will take the out of box license file which needs to be overriden after post installation. Below command will update the Sterling Integrator license,
         InstallDirectory/bin$ AddLicenseSet.sh
        
3. Start SI and go to Operations --> System --> Licenses and do List All and check all carefully that we have got license for all
   required SI components for instance File Gateway Core
   Note: Check under InstallDirectory/properties/licensefiles for SI and SFG License.
 
4. Shutdown Sterling Integrator and proceed for Sterling File Gateway Installation
   InstallDirectory/bin$ InstallService.sh
   Note: We will find SFG under InstallDirectory/packages/filegateway_XXX.jar
 
5. After installation start SI we will be automatically pointed to filegateway URL.

​​Note: Only important factor here is before we trigger SFG installation we need to make sure we have valid licese for SFG, Otherwise your installation will be semi completed.

Uninstall​ IBM Sterling File Gateway (SFG) from IBM B2B Sterling Integrator (SI) Installation

Below process explains how to remove IBM Sterling File Gateway (SFG) from IBM B2B Sterling Integrator(SI) Installations. Please do not consider this as an official procedure, This is a just a WORK AROUND to remove SFG from SI.


1. Shutdown all Sterling Integrator nodes and perform below tasks on all nodes.

2. Go to installFolder/container/Applications - Remove filegateway exploded folder and filegateway.war.

3. Go to installFolder/noapp/deploy - Remove filegateway exploded folder and filegateway.war.

4. Go to installFolder/jar/ - Remove filegateway folder.

5. Go to installFolder/properties/dynamicclasspath.cfg.in - Remove entries for FileGateway-ui.jar and filegateway.jar.

6. Run setupfiles.cmd/sh

7. Run deployer.cmd/sh

8. Bring up Sterling Integrator.

Note:​ Please note that above process will not clean database and it will leave few database tables related to SFG but they don't harm any of the SI processes. ​

Install MESA Developer Studio on IBM B2B Sterling Integrator

To install MESA Developer Studio

​ on Eclipse (Europa) follow the below steps, Below screen shot shows the eclipse version details,


                    
                     

1. Open Eclipse.

2. Select a default workspace folder location. You can add additional workspace folder locations at anytime. The Package View in the lower left area of the MESA Developer Studio workspace displays a local explorer view of your project folders. This enables you to store files that you check out.

3. From the Eclipse Help menu,

​​

​Navigate to Software Updates > Find and install.

4. Select Search for new features to install.

5. Click Next.

6.

​Now need to add new Remote Site so click on New Remote Site.

7. Complete the following information and click OK.

1.  Name –
   ​Type a descriptive name for the application server.
2.  URL –
   ​Type the IP address or name of your server. Format is

                                http://<servername>:<WebDAVportnumber>/eclipse.

                                http://HOST:17046/eclipse 

                        

​Note: In this case my base port was 17000.

​​​

Note: The WebDAVportnumber can be found in webdav.log under  <GIS_install_dir>\SterlingCommerce\SI\logs

Your new site will appear in the list of sites to include in the search.

8. Select the check-box to the left of the new site. Click Finish.

                The system verifies the selected site and displays the results. On the search results page, expand the update site node and select from the following plug-ins, according to your licenses:

1.  MESA Developer Studio (For Custom Service)
2. ​ ​
   Service SDK
3.  Skin Editor

9. Click Next. Accept the terms of the license and click Next.

10. Click Finish.

11. Click Install All to accept the feature verification.

You must restart Eclipse for the changes to take affect.

Sterling Integrator Active and DR Site Diagram

MySQL Installation and Database creation on Ubuntu 12.10

1. Install mysql on Ubuntu 12.10. (Use apt-get install mysql-server)
2. Log on to mysql as a root user
     $ mysql -u root
     $ mysql -u root -p
       Enter password: ENTER ROOT PASSWORD
3. Create a Database for Sterling Integrator
     mysql> create database SI52;
            Query OK, 1 row affected (0.00 sec)
4. Add User called sterling to SI52 Database
    mysql> grant usage on *.* to sterling@localhost identified by 'password';
    Query OK, 0 rows affected (0.00 sec)
    mysql>GRANT ALL PRIVILEGES ON SI52.* TO sterling@localhost IDENTIFIED BY password WITH GRANT OPTION;
    mysql>FLUSH PRIVILEGES;
5. Grant all priviliges to sterling user on SI52 database
   mysql> grant all privileges on amarokdb.* to amarokuser@localhost ;
   Query OK, 0 rows affected (0.00 sec)
6. Login as sterling user and check the database access to SI52
$ mysql -u sterling -p'password' SI52
    Your MySQL connection id is 12
    Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    mysql>

      

Remove Custom Service from Sterling Integrator

If we need to uninstall a custom service in IBM B2B Sterling Integrator follow the blow steps. Please note you have to clean from File system and from database as well.

File System Cleanup

C:\Sirish\SterlingIntegrator\install\installed_data\Your Custom Service Name directory.

C:\Sirish\SterlingIntegrator\install\jar\Your Custom Service Name directory.

C:\Sirish\SterlingIntegrator\install\properties\lang\en\Your Custom Service Name_en.properties

C:\Sirish\SterlingIntegrator\install\properties\services\Your Custom Service Name.xml

Database Cleanup

select * from service_def_guid where current_id = 461;

select * from service_def_parms where def_id = 461

select * from service_def where def_lookup_name like '%com.mypackage%';  

Note: 

Please check def_look_up name in service instance page under Development --> Services --> Installation/Setup  

delete from service_def_guid where current_id = 461;

delete from service_def where def_id = 461;

delete from service_def_parms where def_id = 461;

commit;

Note: 461 is my CS unique Id in a database.

IBM B2B Sterling Integrator - Oracle Database User and Table Space creation Scripts

Below process explains how to create Oracle database for Sterling Integrator with all required user permissions.

1. Create a new database using Oracle Database Configuration Assist for Sterling Integrator with all required Initilation perameters. Initilation perameters are very critical so refer installation document very carefully while creating new database.

2. Log on to Database as a Power User (system or sys as sysdba) and create new table space,
       CREATE TABLESPACE SI_TABLESPACE DATAFILE
       '/u01/app/sterling/SI52.DBF' SIZE 2000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED
        LOGGING
        ONLINE
        PERMANENT
        EXTENT MANAGEMENT LOCAL AUTOALLOCATE
        BLOCKSIZE 8K
        SEGMENT SPACE MANAGEMENT AUTO;

3. Log on to Database as a Power User (system or sys as sysdba) and create new user for Sterling Integrator Database in this case the user is "Sterling". Excute below scripts one by one.

    DROP USER STERLING CASCADE; --Not Required This Statement.
    CREATE USER STERLING IDENTIFIED BY STERLING DEFAULT TABLESPACE SI_TABLESPACE TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;
    GRANT EXP_FULL_DATABASE TO STERLING;
    GRANT IMP_FULL_DATABASE TO STERLING;
    GRANT RESOURCE TO STERLING;
    GRANT CONNECT TO STERLING;
    ALTER USER STERLING DEFAULT ROLE ALL;
    GRANT UNLIMITED TABLESPACE TO STERLING;
    GRANT DBA TO STERLING;
    GRANT ALTER SESSION TO STERLING;
    GRANT CREATE PROCEDURE TO STERLING;
    GRANT CREATE SEQUENCE TO STERLING;
    GRANT CREATE SESSION TO STERLING;
    GRANT CREATE SYNONYM TO STERLING;
    GRANT CREATE TABLE TO STERLING;
    GRANT CREATE VIEW TO STERLING;
    GRANT EXECUTE ANY PROCEDURE TO STERLING;
    GRANT INSERT ANY TABLE TO STERLING;
    GRANT UPDATE ANY TABLE TO STERLING;
    GRANT SELECT ANY TABLE TO STERLING;
    GRANT SELECT_CATALOG_ROLE TO STERLING;
    ALTER USER STERLING DEFAULT ROLE "CONNECT", "RESOURCE",SELECT_CATALOG_ROLE;
    GRANT CREATE TRIGGER TO STERLING;
    GRANT CREATE TYPE TO STERLING;
    GRANT EXECUTE ANY TYPE TO STERLING;
    GRANT SELECT ANY TABLE TO STERLING;
    GRANT SELECT ANY DICTIONARY TO STERLING;

4. Connect to Database as new user Sterling just to make sure you don't have any issues while connecting or accessing Sterling Integrator database.

5. You can proceed with the SI installations :)

IBM Support Portal How-to videos on YouTube

IBM Electronic Support Channel
 

IBM Support Portal How-to videos on YouTube

via IBM Sterling B2B Support Blog

• Electronic Support Channel (all videos are posted here by scrolling down to "Uploaded Videos" section) - http://www.youtube.com/IBMElectronicSupport
• What is the IBM Support Portal? - http://www.youtube.com/watch?v=0VzVfdKA8zo
• IBM Support Portal Overview - http://www.youtube.com/watch?v=laOCDj5xdAs
• Getting started with IBM Support Portal using Quick Start - http://www.youtube.com/watch?v=2mORmlZGLWI
• Inside and IBM Support Centre - http://www.youtube.com/watch?v=TUy2cq8IPL0
• Managing Your Product List in the IBM Support Portal - http://www.youtube.com/watch?v=P7vqYH6pH0E
• Getting Started with IBM Support: How to Obtain an IBM ID - http://www.youtube.com/watch? v=afQmxE4wjIg&feature=results_main&playnext=1&list=PL421D5E967BB1398D
• Downloading Products from Passport Advantage  - http://www.youtube.com/watch?v=HuatqV8jpu4&feature=related
• How to download software fixes from Fix Central - http://www.youtube.com/watch?v=G0Q02Mvx07c&feature=related
• Installing and running IBM Support Assistant  - http://www.youtube.com/watch?v=TUv8mge8PIk&feature=related
• The IBM Web Problem Reporting Initiative  - http://www.youtube.com/watch?v=HphQ3qYMy1M&feature=results_video&playnext=1&list=PL421D5E967BB1398D
• Glossary of Support Terms  - http://www.youtube.com/watch?v=YHbo9HDrI98
• Accelerated Value Program features of the IBM Support Portal - http://www.youtube.com/watch?v=UrNbdIIXgUA
• Electronic Support Channel (all videos are posted here) - http://www.youtube.com/IBMElectronicSupport 

Sterling Integrator - Database to XML Mapping

Here is the process for oracle and I am sure it's gonna be same for other databases as well,

1. Install Oracle Client Software. (Not Server).
2. Put database server entry in tnsnames.ora file.
3. Go to Control Panel where you installed Map editor create System DSN. When you click on
   Add you should see Oracle client there. (If you installed oracle client property)
4. Provide required database credentials and complete DNS creations.
5. Bring up your map editor create new map choose data format as SQL (Database
    Connectivity).
6. Go to Source Side properties in map editor go to Data Source tab select your DNS name and
    it will list all your tables and when you select table you can auto generate fields.

From here regular mapping. Be careful while editing tnsnames.ora.

IBM Sterling B2B Integrator Code List Deployment through Command Prompt

C:\Sirish\SterlingIntegrator\install\tp_import>import.cmd -update -passphrase passphrase -input C:\Sirish\bpml_coding\Sirish.xml

This is the format requires to import from command prompt.

Sirish.xml

<?xml version="1.0" encoding="UTF-8"?>

<SI_RESOURCES xmlns="http://www.stercomm.com/SI/SI_IE_Resources" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" GISVersion="6000" FrameworkVersion="2">

            <CODE_LISTS>

                        <CODE_LIST_XREF>

                                    <LIST_NAME>Sirish</LIST_NAME>

                                    <SENDER_ID/>

                                    <RECEIVER_ID/>

                                    <LIST_VERSION>2</LIST_VERSION>

                                    <SIResourceDefaultVersion>true</SIResourceDefaultVersion>

                                    <STATUS>1</STATUS>

                                    <COMMENTS/>

                                    <USERNAME>Sirish Reddy Gongal Reddy</USERNAME>

                                    <CREATE_DATE>2012-09-21 14:43:16.087</CREATE_DATE>

                                    <CODE_LIST_XREF_ITEMS>

                                                <CODE_LIST_XREF_ITEM>

                                                            <SENDER_ITEM>Sirish Reddy</SENDER_ITEM>

                                                            <RECEIVER_ITEM>Gongal Reddy</RECEIVER_ITEM>

                                                            <TEXT1>Target System - One</TEXT1>

                                                            <TEXT2>Target System - Two</TEXT2>

                                                            <TEXT3>Target System - 3</TEXT3>

                                                            <TEXT4>Target System - 4</TEXT4>

                                                            <TEXT5>Target System - 5</TEXT5>

                                                            <TEXT6>Target System - 6</TEXT6>

                                                            <TEXT7>Target System - 7</TEXT7>

                                                            <TEXT8>Target System - Eight</TEXT8>

                                                            <TEXT9>Target System - Nine</TEXT9>

                                                            <DESCRIPTION>Just Testing the Auto Import</DESCRIPTION>

                                                </CODE_LIST_XREF_ITEM>

                                    </CODE_LIST_XREF_ITEMS>

                        </CODE_LIST_XREF>

            </CODE_LISTS>

</SI_RESOURCES>