Exposing IBM DataStage Jobs as WebService

1. Introduction

Business analysts and subject matter experts can use IBM^® InfoSphere™ Information Analyzer to manage the Information Services in their IBM Information Server (IIS).
IBM® InfoSphere™ Information Analyzer evaluates the content and structure of your data for consistency and quality. InfoSphere Information Analyzer also helps you to improve the accuracy of your data by making inferences and identifying anomalies.
InfoSphere Information Analyzer helps you to assess the quality of your data by identifying inconsistencies, redundancies, and anomalies in your data at the column, table, and cross-table level. InfoSphere Information Analyzer also makes inferences about the best choices regarding data structure. Inferences help you to learn more about the optimal structure of your data and what you can do to improve the quality of your data.

1.1 Information Analyzer Feature overview

• Import metadata from various data environments
• Configure system analysis options
• Create virtual columns from physical columns
• Analyze column data classification
• Analyze column data properties
• Analyze column data completeness and validity
• Analyze column data formats
• Analyze data value commonality across columns
• Analyze table primary keys
• Analyze duplicate primary key values
• Analyze table foreign keys
• Analyze referential integrity
• Create analytical notes to supplement system results
• Capture enterprise data management (EDM) data to supplement system results
• Produce system reports to display system results

This article focuses on setting up the IIS environment to generate WebService reference for the DataStage Jobs. By the end of this article you will see exposed WebServices for the DataStage ETL Jobs and the method of calling these exposed WebServices from Standalone java application.

            The objective of this article is to explain step by step the process of exposing the ETL Jobs as WebService Interface. So it’s not explaining the process of installation/configuration of the product stack or the process of developing the ETL Jobs.

2. Solution Scenario

            The sample application is based around customer profitability and churn management for Telecom industry. We have existing ETL jobs.

So we have following products already installed and configured.

• DataStage Server v8.1 (https://w3-103.ibm.com/software/xl/download/ticket.do, Part number: C1W3PML).
• Fixpack for DataStage Server v8.1

(http://iidemo1.svl.ibm.com/files/IF131646.zip ).

• VC++ Express edition

(http://www.microsoft.com/express/download/#webInstall).

• Platform SDK for windows (http://www.microsoft.com/downloads/details.aspx?FamilyId=A55B6B43-E24F-4EA3-A93E-40C0EC4F68E5&displaylang=en).

3. Configuration

Open the Information Analyzer from Start -> All Programs -> IBM Information Server -> IBM Information Server Console (or) Console for IBM Information Server icon at Desktop with provided valid User credentials (Suite Administrator, ex: isadmin/inf0server).

3.1 Create Information Service Connection

            Information Service Connection is used for Information Service Application to list down all ETL Jobs, User can select Jobs to create operations at Information Service Application.

1. Open the Information Service Connection at Home -> Configuration -> Information Service Connections.
2. Click “New” under Tasks, enter mandatory fields (Connection Name, Information Provider Type) as like below
3. On selection of Information Provider Type as “DataStage and QualityStage” from combo box, it will ask to fill the Agent Host, Username, and Password details.  Click on Test when the details filled.

                              

4. Click on “Save, Enable and Close” button at the bottom of the page.
5. On successful creation of Information Service connection User can see entry of Information Server Connection as like below.

4. Information Services

            Information Analyzer is client server application, User can create two kinds of projects they are Information Services and Information Analyzer. 

Information Service project is used to create the services/operations for the InfoSphere entities (Jobs, Database Queries etc).

Information Analyzer project is used to analyze the data in many ways example like Column Analysis, Cross Domain Analysis, Primary Key Analysis and etc.

Create an Information Service project to develop the Web services to access the DataStage Jobs from any language (ex: Java, .Net, etc.).

1. Click “New Project” button, enter the Project name as “CISProject”, select type as “Information Services”, click Ok button.

2. Go to Develop -> click on Information Services Application

3. Create a new Application using the “New” link under Tasks.
4. Enter the name, description at Overview tab.
5. Click “New” button at bottom of the “Select a View” pane at left side of the page then click on “New Service” button.

                    

              

6. In New Service window, user has to provide “Service Name” as “CISService” and description.

              

7. Click on “New Operation” button at bottom and enter the Operation details.

                      

8. Click Select button of “Select an information provider” at Information Provider tab of Operation.
9. Select Type as “DataStage and QualityStage” -> display the Information Service Connection at “Select a DataStage Job” section, double click on Connection -> double click on Project -> double click on Jobs folder -> select your Job -> Click on Ok button.

        

10. Repeat the steps 7 to 9 to add new operations for All Jobs.
11. Go to Bindings section -> select “SOAP Over HTTP” at Attach Bindings section.
12. Click on save application and click on Close application.
13. Click on Deploy from Tasks at right side of the screen of the CIS Application.
14. Wait until application deployed successfully, user can see the Activity Status window at bottom, once displayed as “Completed” means service is deployed successfully.
15. Open “Deployed Information Services Applications” under Operate.  Click on CISApplication -> click on CISService -> click “View Service in Catalog” button.

                            

16. Service details will display in default browser, go to Bindings section -> Extract SOAP Over HTTP Section -> Click on “Open WSDL Document” -> Save Document with .wsdl format.

4.1 Calling Information Services as Ant Task

1. Create a Java Project using your favorite IDE to create a WebService Client application for the saved .wsdl file
2. Copy the .wsdl file into Java Project; right click on Wsdl file -> Web Services -> Generate Client.

        

3. Select all default options to generate the WebService client.
4. User can see the generated port, service information as like below.

5. Open the wsdl file in text editor; right click on wsdl file -> Open with -> Text Editor.
6. Locate the tag <wsdlsoap:address> in the file, copy the location of URL to test in browser.

7. As active service will display the message as like below.

8.      Write a sample code to test the Information Service from Standalone Java application. (code is attached below)

                       

9. Run CISService application as Java Application with argument as “AllocationType”.

10. User can see the DataStage Jobs running status at “Websphere DataStage and QualityStage Director” as like below.

After successful running status changed from Running to Finished

11. User can see the exceptions, warnings, and information for the Jobs at Director by right click on the Job -> click on “View Log”.

                 

12. Log description will displays as Log view in Director, to see the detailed information for the log double click on the log entry.

13. Export Project as .jar file used as library API for Ant Tasks.
14. Write Ant Task for the Jobs to execute using build.xml, include the below code.

15. Execute ETL Jobs using ant task at Command prompt using the ant command as “ant –f build.xml runETLJobs”

Data Migration from MS SQL to DB2

How to import a MS Sql .bak file to DB2

Abstract:

This document helps to Import MS SQL Server Database Backup (.bak file) to IBM DB2 using IBM Migration Tool Kit 2.0.

Requirements:

1. MS SQL Server Database Backup (.bak) file.
2. Microsoft SQL Desktop Engine (MSDE2000A.exe) helps you to create virtual MS SQL Server 2000 at your Desktop/TP.
3. ODBC SQL Server connection to access the data.
4. IBM Migration Toolkit Software.

Process:

Step1: To Create MS SQL Virtual Server download MSDE2000A.exe (for windows) from http://www.microsoft.com/downloads/details.aspx?familyid=413744D1-A0BC-479F-BAFA-E4B278EB9147&displaylang=en and install using default options.

MSDE will install at default location: ‘C:\Program Files\Microsoft SQL Server\80\Tools\Binn\’.

MSDE2000A.exe will install the “osql utility”. Use the osql utility to restore the Microsoft SQL .bak file to an operational ODBC datasource.

Step2: Create Local MS Sql database using .bak file on MSDE2000:

1. Open a DOS Command window and set the PATH variable as:

“set PATH=%PATH%; C:\Program Files\Microsoft SQL Server\80\Tools\Binn\”

2. To create a database at MSDE, on the same command window

i.                    osql –E

ii.                  CREATE DATABASE SAMPLE

iii.                GO

DOS Console will display the SAMPLE database created as successfully with the following lines:

The CREATE DATABASE process is allocating 0.63 MB on disk 'SAMPLE'.

The CREATE DATABASE process is allocating 0.49 MB on disk 'SAMPLE_log'.

3. To verify the SAMPLE database was created successfully, use the following commands on the same DOS console.

i.                    SELECT name FROM sysdatabases

ii.                  GO

DOS Console will display SAMPLE database entry.

4. To restore the contents from the .bak file into the newly created database, the database recovery mode should be simple:

i.                    ALTER DATABASE SAMPLE SET RECOVERY SIMPLE

ii.                  GO

5. By default the database state will be Offline.  We need to set the database state to online before importing the .bak.

i.                    ALTER DATABASE SAMPLE SET ONLINE

ii.                  GO

6. To restore the contents from your .bak file:

i.                    RESTORE FILELISTONLY FROM DISK=’<PATH>/pubs.bak’

ii.                  GO

This will restore the .bak file as local file system in the form of .mdf and .ldf files.

DOS Console wills displays the Output is in the form: Physical Name, FileGroupName, Type, Size, and Max Size.

You will probably find that your FileGroupNames are ‘pubs’ and ‘pubs_log’.  (If you see FileGroupNames are ‘PRIMARY’ and ‘NULL’ instead of ‘pubs’ and ‘pubs_log’ ignore that).

Note: <PATH> should be replaced with .bak files location.

7. Restore the database (using .mdf and .ldf files):

i.                    RESTORE DATABASE SAMPLE FROM DISK='<PATH>\pubs.bak' WITH MOVE 'pubs' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\SAMPLE.mdf', MOVE 'pubs_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\SAMPLE_log.LDF', REPLACE

ii.                  GO

DOS Console will display the SAMPLE database is processed successfully (Check for the below statements)

Processed 216 pages for database 'SAMPLE', file 'pubs' on file 1.

Processed 1 page for database 'SAMPLE', file 'pubs_log' on file 1.

RESTORE DATABASE successfully processed 217 pages in 0.055 seconds (32.190 MB/sec).

Note: <PATH> should be replaced with .bak files location.

8. The SQL Service Manager will display at your services section on desktop (find at bottom right corner).

Step3: Create ODBC Datasource connection to access data from SAMPLE Database:

1. Open Data Sources (ODBC) from Administrative Tools under Control Panel.

2. Click Add button and select ‘SQL Server’ option at Create New Data Source pop-up window.

3. Enter ODBC name and Server in Create a new Data Source to SQL Server pop-up window.

4.Click next -> next, now select ‘Change the default database to’ Check box and select SAMPLE from list.

5. Click next -> Finish, Click ‘Test Data Source’ button on ODBC Microsoft SQL Server Setup pop-up window.

  

Step4: Migrate MS SQL Data to DB2 using IBM Migration Toolkit.

1. Download IBM Migration Toolkit 2.0 from the following site,

http://www-306.ibm.com/software/data/db2/migration/mtk/ and install on your system with default settings.

2. Open Migration Toolkit wizard from Start -> All Programs -> Toolkit, select ’Quickly convert database using the wizard’

3. Provide information for Project Name, Source database type, Target database type at Project Information pop-up window.

i.                    Enter Project Name as SAMPLEPROJ,

ii.                  Select Source database type as ‘Microsoft SQL Server’ from drop-down list.

iii.                Select Target database as DB2 Version respect to version, which you are using.

iv.                Click on Next button.

4. Select Extract from a Server radio button on Source Database pop-up window, it will display Connect to Database pop-up window.

i.                    Enter JDBC/ODBC Alias as SAMPLEDB (Created in Step3)

ii.                  Provide information for User ID (must have administrative access), Password fields.

iii.                Click on OK button.

5. On successful database connection, you will able to see Extract pop-up window.

i.                    Select the tables, views, procedures and triggers that you want to transfer to DB2 database.

            

ii.                  Click on Extract button.

iii.                Click on Next button.

        

6. To change target data types for your convenience at Global Type Mapping pop-up window using Edit Field option (In this we suggest you to change the following data types).

i.              Change GRAPHIC to CHAR.

ii.            Change VARGRAPHIC to VARCHAR.

iii.          Click on Apply button

Before                                                 After

        

iv.                Click on Next button on Global Type Mapping pop-up window.

Modified Global Type Mapping window:

7. To Extract the data and generate scripts for DB2 movement:

i.                          Select Extract the source data and generate the data movement scripts radio button at Data Movement pop-up window

ii.                        Click on Next button.

8. To specify the DB2 target database:

i.                          Select deploy radio button on Target Database pop-up window, it will pop-up Target Database window. 

ii.                        Provide database information, database name as ‘SAMPLE’ (the entered database must be created in DB2 before moving the data using this Migration Toolkit)

iii.                      Provide information for User ID (which has database access privilege), and Password; (If you want to specify particular user name, Otherwise select ‘Use your system current User ID and Password’ check box),

iv.                      Select ‘Load the Data into database’ check box (this option will load the data of selected tables in DB2 database).

v.                        Click on Ok button

vi.                      Click on Next button.

9. Verify summary details on Migration summary window Click on Finish button (It will take some time to finish the migrate data to DB2.

10. If you see any errors on Translation section ignore it.

11. Verify SAMPLE database with tables, views, procedures and triggers at DB2 Control Center.

Note: IBM Migration Toolkit will store the SAMPLEPROJ at ‘C:\MTK\projects\’ folder and you can find the DB2 script that will contain the entire script how the data is created to DB2 database respect to MS Sql database.

Reference- some useful links:

1. http://www.ibm.com/developerworks/db2/library/techarticle/dm-0411yip/
2. http://www.ibm.com/developerworks/edu/dm-dw-dm-0209jarzebowicz-i.html?S_TACT=104AHW11&S_CMP=LIB

Configure DB2 as Cognos Content Manager

Using DB2 as Content Manager for Cognos

Abstract:

            Cognos8.3 installs Derby10.1.2.1 as the default content manager. This article will help you, if you want to use DB2 as the content manager for Cognos.

This article will guide you to setup Cognos with DB2. It also covers how to setup IIS or IBM Http Server with Cognos.

            This article is tested on the following product version.

• IBM DB2 9.1
• Cognos 8.3
• IIS 5.1

             Steps

1.      Install DB2

For getting information on how to install DB2 please visit.

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp

This article expecting DB2 is installed into the default location

 i.e. : “C:\Program files\IBM\SQLLIB”. If DB2 is installed in other location, then please change the path later in this article.

2.      Install Cognos without Embedded Derby database.

Make sure the “Cognos Content Database” is deselected. Assume Cognos is installed to its default path i.e. ”C:\Program Files\cognos\c8”.

3.      Create a Database in DB2, which Cognos will use as Content Manager.

Cognos stores its contents in a Database. So we need to create a database in DB2. As discussed in the “INSTALLATION AND CONFIGURATION GUIDE”, the database should have specific buffer pools and table spaces. Script “CognosOnDB2.db2” is attached with this article, to avoid the tedious process of setting those buffer pools and table spaces. Save the attached script (say at C:\Documents and Settings\Administrator\Desktop”).

To execute the script follow these steps.

·         On a command widow type “db2cmd”. You will get a new command prompt.

·         On this new command prompt type  ‘db2 –tf  “<full path of the script>\CognosOnDB2.db2” ‘. This will cerate a database named “COGNOS”, required buffer pools, table spaces and set all the required authentications.

4.      Copies the required DB2 jar files into Cognos Environment.

Copy db2java.zip, db2jcc.jar file from “C:\Program files\IBM\SQLLIB\java” to “C:\Program Files\cognos\c8\webapps\p2pd\WEB-INF\lib”
 

5.      Set up Java.

On Windows platform, by default java will be installed with Cognos8.3. This java will be located at “C:\Program Files\cognos\c8\bin\jre\1.5.0”. You can set this Java as the default java for Cognos. For that, you need to create a new Environment variable named “JAVA_HOME”.

Create a new Environment variable

If you are using any other java (not the default Cognos java) then you need to copy this jar file.

Copy C:\Program Files\cognos\c8\bin\jre\1.5.0\lib\ext\bcprov-jdk14-134.jar to “<your java Home dir>\lib\ext”.

If you have not copied this jar file, you will get security exception, while starting Cognos service.

Note: Before using other Java, check the compatibility at Cognos Support Site, http://support.cognos.com/. 

6.      Install IIS server and setup Virtual Host.

If IIS is not installed on your system, then install it by following this.

Go to Control Panel -> Add remove Programs -> Add Remove Windows Components -> IIS

Cognos need two Virtual directories in IIS.

Visit http://support.microsoft.com/kb/172138 for the steps to create Virtual Directory in IIS.

Note: the “cgi-bin” virtual directory should be created inside virtual directory “cognos8”.

7.      If you are using IBM HTTP Server

If you are using IBM HTTP Server, then need to modify the httpd.conf file for adding virtual directories.

Add the following lines in httpd.conf in appropriate sections.

ScriptAlias /cognos8/cgi-bin/ "C:/Program Files/cognos/c8/cgi-bin/"

Alias /cognos8 “C:/Program Files/cognos/c8/webcontent"

    <Directory " C:/Program Files/cognos/c8/webcontent">

        Options Indexes MultiViews

    </Directory>

    <Directory "C:/Program Files/cognos/c8/cgi-bin">

        AllowOverride None

        AddOutputFilter Includes html

            Options ExecCGI

        AddHandler type-map var

        Order allow,deny

        Allow from all

        LanguagePriority en de es fr it nl sv

        ForceLanguagePriority Prefer Fallback

    </Directory>

8.      Changes for the “Cognos Configuration” to set the recently created Database as the content store.

·         Start the Cognos Configuration.

·         Delete the existing (default) content Manager.

·         Create a new Content manager.

·         Select “DB2 database” as type.

·         Set the user ID/Password for DB2 and specify the database name as “COGNOS”.

·         Save the Cognos configuration by “File->Save” on Cognos Configuration.

 Now your Cognos is ready to run with DB2.

·         Start Cognos by “Actions -> Start”  on Cognos Configuration.

                       

            Verification:

If every thing goes fine, while starting Cognos service, it will give the following message at Details window.

                        “CM-SYS-2057 Creating content store tables (schema version 4.0066).”

                        You will see around 115 new tables created on COGNOS database.

Script for the Congnos DB2 DB.  (Please save the below script in .sql file and execute in db2cmd prompt).



--------------------------------------------------------------------
-- This file will setup the database for Cognos---------------------
-- Developed by Gowreenath Boina for WebSphere Knowledge Community--
--------------------------------------------------------------------
CREATE DATABASE COGNOS AUTOMATIC STORAGE YES  ON 'C:\' ALIAS COGNOS USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 32768 WITH 'Cognos DB';

CONNECT TO COGNOS;

CREATE SCHEMA "DB2COGNOS" AUTHORIZATION DB2INST1;
------------------------------------
-- DDL Statements for BUFFERPOOLS --
------------------------------------

CREATE BUFFERPOOL "COGNOS_04KBP"  SIZE 250 PAGESIZE 4096;

CREATE BUFFERPOOL "COGNOS_08KBP"  SIZE 250 PAGESIZE 8192;

CREATE BUFFERPOOL "COGNOS_16KBP"  SIZE 250 PAGESIZE 16384;

CREATE BUFFERPOOL "COGNOS_32KPB"  SIZE 250 PAGESIZE 32768;

CONNECT RESET;
CONNECT TO COGNOS;

------------------------------------
-- DDL Statements for TABLESPACES --
------------------------------------


CREATE TEMPORARY TABLESPACE TSN_SYS_COGNOS IN DATABASE PARTITION GROUP IBMTEMPGROUP
     PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE
     EXTENTSIZE 16
     PREFETCHSIZE 16
     BUFFERPOOL COGNOS_32KPB
     OVERHEAD 12.670000
     TRANSFERRATE 0.180000
     FILE SYSTEM CACHING 
     DROPPED TABLE RECOVERY OFF;


CREATE USER TEMPORARY TABLESPACE TSN_USR_COGNOS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
     PAGESIZE 16384 MANAGED BY AUTOMATIC STORAGE
     EXTENTSIZE 16
     PREFETCHSIZE 16
     BUFFERPOOL COGNOS_16KBP
     OVERHEAD 10.500000
     TRANSFERRATE 0.140000
     FILE SYSTEM CACHING 
     DROPPED TABLE RECOVERY OFF;


CREATE REGULAR TABLESPACE TSN_REG_COGNOS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
     PAGESIZE 16384 MANAGED BY AUTOMATIC STORAGE
     AUTORESIZE YES
     INITIALSIZE 32 M
     MAXSIZE NONE
     EXTENTSIZE 16
     PREFETCHSIZE 16
     BUFFERPOOL COGNOS_16KBP
     OVERHEAD 10.500000
     TRANSFERRATE 0.140000
     DROPPED TABLE RECOVERY ON;


---------------------------------------
-- Authorization Statements on Schemas
---------------------------------------


GRANT CREATEIN ON SCHEMA "DB2COGNOS" TO USER "DB2USER" WITH GRANT OPTION;

GRANT DROPIN ON SCHEMA "DB2COGNOS" TO USER "DB2USER" WITH GRANT OPTION;

GRANT ALTERIN ON SCHEMA "DB2COGNOS" TO USER "DB2USER" WITH GRANT OPTION;

----------------------------------------
-- Authorization Statements on Database
----------------------------------------


GRANT CREATETAB ON DATABASE  TO USER "DB2USER" ;

GRANT BINDADD ON DATABASE  TO USER "DB2USER" ;

GRANT CONNECT ON DATABASE  TO USER "DB2USER" ;

GRANT IMPLICIT_SCHEMA ON DATABASE  TO USER "DB2USER" ;

---------------------------------------
-- Authorization statement on table space
---------------------------------------


GRANT USE OF TABLESPACE "TSN_USR_COGNOS" TO USER "DB2USER" ;

COMMIT WORK;

CONNECT RESET;

TERMINATE;

;