Micro Focus Enterprise Developer 2.3 Update 2 for Eclipse > Welcome > Getting Started > Web Services Tutorials > Tutorial: Reusing Existing SQL Programs in a Java Environment > Modifications for DB2 LUW

Modifications for DB2 LUW

Modifications to the tutorial for using a DB2 LUW database instead of a SQL Server database.

Prerequisites

This tutorial begins where Tutorial: Reusing Existing COBOL Programs in a Java Web Services Environment left off, using the Apache Tomcat application server, and the same CobolBook and JSPBookDemo projects. Therefore, you must complete that tutorial in its entirety, using the Tomcat 7 application server, before starting this tutorial. See Tutorial: Reusing Existing COBOL Programs in a Java Web Services Environment for complete instructions.

You must also install IBM DB2 LUW version 10 Express-C 32-bit.

Modifications

This section explains the modifications you need to make to the SQL Server version of the tutorial so that the application works with DB2 LUW instead of SQL Server.

Install and Configure a JDBC Driver

Install a JDBC Driver: This tutorial uses the IBM DB2 LUW 10.5 Express-C JDBC driver included with the database system installer; therefore, no download or installation is required.
Copy the JDBC Driver to Tomcat: Follow the instructions provided in the Copy the JDBC Driver to Tomcat section of the Install and Configure a JDBC Driver topic, but copy the db2jcc.jar and db2jcc_license_cu.jar files to the lib subdirectory of your Apache Tomcat installation.

Create and Load a DB2 LUW Database

Create the SAMPLE DB2 LUW Database

The tutorial uses the SAMPLE database you can create using the DB2 LUW First Steps tool. See your DB2 LUW documentation for further instructions.

Start Enterprise Developer

If you have closed Enterprise Developer since completing Tutorial: Reusing Existing COBOL Programs in a Java Environment, open it again; then open the Java EE perspective and the Project Explorer.

Define a DB2 Connection

Follow the instructions provided in the Define a SQL Server Connection section of the Create and Load a SQL Server Database topic, with the following modifications:

Connection Profile dialog box, select the DB2 for Linux, Unix, and Windows profile type.
On the New Driver Definition dialog box Name/Type tab, select IBM Data Server Driver for JDBC and SQLJ.
On the JAR List tab, follow these instructions:
1. On the Driver file list, select the db2jcc.jar file; then click Edit JAR/Zip.
2. Browse to the java subdirectory of your DB2 LUW installation, and double-click the db2jcc.jar file located there.
  This replaces the db2jcc.jar file on the list, and prompts you to update the other .jar file paths to use the same location. Because you are just adding a single .jar file in an alternative directory, updating the path for other files is not required.
3. Click No.
4. On the Driver file list, select the db2jcc_license_cisuz.jar file; then click Remove JAR/Zip.
5. Click Add JAR/Zip.
6. Browse to the java subdirectory of your DB2 LUW installation, and double-click the db2jcc_license_cu.jar file.
  This adds the file to the Driver file list.

On the Properties tab, use the following information:

Property	Value
Connection URL	`jdbc:db2://localhost:50000/SAMPLE`
Database Name	`SAMPLE`
Driver Class	`com.ibm.db2.jcc.DB2Driver`
Password	blank
User ID	blank

Follow these steps on the New Connection Profile dialog box:
1. Check Save password.
2. In the User name and Password fields respectively, type your DB2 user name and password credentials.
  Note: If your DB2 database does not use its own user name and password, type in your Windows authentication credentials instead.
3. Click Test Connection.
4. When the connection succeeds, clear the message box; then click Finish to save the connection and close the New Connection Profile dialog box.

Add an SQL Script

Follow the instructions provided in the Add an SQL Script section of the Create and Load a SQL Server Database topic, with the following modifications:

Name the new script file SetupDB2Books.sql.

Add the following contents:

DROP TABLE BOOKS;

CREATE TABLE BOOKS (
	TITLE	VARCHAR(50) NOT NULL,
	TYPE    	VARCHAR(20) NOT NULL,
	AUTHOR  	VARCHAR(50) NOT NULL,
	STOCKNO CHAR(4) NOT NULL PRIMARY KEY,
	ISBN    	DECIMAL (13, 0) NOT NULL, 
	RETAIL	DECIMAL(4,2) NOT NULL,
    	ONHAND	INT NOT NULL,
    	SOLD	INT NOT NULL
);

INSERT INTO BOOKS VALUES(
	'OLIVER TWIST',
	'CHARLES DICKENS',
	'CLASSIC',
	'1111',
	9780140620467,
	5.00,
	10,
	30
);

INSERT INTO BOOKS VALUES(
	'A GAME OF THRONES',
	'GEORGE R. R. MARTIN',
	'FANTASY',
	'1112',
	7428545,
	3.86,
	17,
	75
);

INSERT INTO BOOKS VALUES(
	'A CLASH OF KINGS',
	'GEORGE R. R. MARTIN',
	'FANTASY',
	'1113',
	7447833,
	6.49,
	17,
	75
);

INSERT INTO BOOKS VALUES(
	'THE DAY OF THE JACKAL',
	'FREDERICK FORSYTH',
	'ADVENTURE',
	'1114',
	99552710,
	2.00,
	26,
	75
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE PHILOSOPHER''S STONE',
	'J. K. ROWLING',
	'ADVENTURE',
	'1116',
	747558191,
	5.24,
	48,
	100
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE CHAMBER OF SECRETS',
	'J. K. ROWLING',
	'ADVENTURE',
	'1117',
	747562180,
	5.24,
	44,
	100
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE PRISONER OF AZKABAN',
	'J. K. ROWLING',
	'ADVENTURE',
	'1118',
	747573760,
	5.24,
	49,
	100
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE GOBLET OF FIRE',
	'J. K. ROWLING',
	'ADVENTURE',
	'1119',
	747582386,
	6.74,
	44,
	100
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE ORDER OF THE PHOENIX',
	'J. K. ROWLING',
	'ADVENTURE',
	'1120',
	747591261,
	6.74,
	37,
	100
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE HALF-BLOOD PRINCE',
	'J. K. ROWLING',
	'ADVENTURE',
	'1121',
	747598460,
	6.74,
	41,
	100
);

INSERT INTO BOOKS VALUES(
	'HARRY POTTER AND THE DEATHLY HALLOWS',
	'J. K. ROWLING',
	'ADVENTURE',
	'1122',
	1408810298,
	6.74,
	40,
	100
);

INSERT INTO BOOKS VALUES(
	'THE FELLOWSHIP OF THE RING',
	'J. R. R. TOLKIEN',
	'FANTASY',
	'1123',
	7123825,
	4.95,
	23,
	100
);

INSERT INTO BOOKS VALUES(
	'THE TWO TOWERS',
	'J. R. R. TOLKIEN',
	'FANTASY',
	'1124',
	261102362,
	7.99,
	28,
	100
);

INSERT INTO BOOKS VALUES(
	'LORD OF THE FLIES',
	'WILLIAM GOLDING',
	'SCARY',
	'2222',
	9780571191475,
	4.50,
	30,
	100
);

INSERT INTO BOOKS VALUES(
	'CATCH 22',
	'JOSEPH HELLER',
	'WAR',
	'3333',
	9780099477310,
	6.50,
	50,
	200
);

INSERT INTO BOOKS VALUES(
	'THE HITCHHIKER''S GUIDE TO THE GALAXY',
	'DOUGLAS ADAMS',
	'COMEDY',
	'4444',
	9780345453747,
	6.73,
	100,
	360
);

INSERT INTO BOOKS VALUES(
	'TO KILL A MOCKINGBIRD',
	'HARPER LEE',
	'CLASSIC',
	'5555',
	9780099466734,
	4.87,
	50,
	75
);

SELECT * FROM yourSchema.BOOKS;
SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY

On the second to last line of the script, replace yourSchema with the name of the schema that your SAMPLE database was created under.

Execute the SQL Script

Follow the instructions provided in the Execute the SQL Script section of the Create and Load a SQL Server Database topic, with the following modifications:

Use the SetupDB2Books.sql file.
Set the Type to DB2 UDB_V9.1.
Set the Name to the name of your DB2 connection.
In the Database field, select SAMPLE.

Modify COBOL Source

Following the instructions in the Modify COBOL Source topic, with these modifications:

Replace book.cbl with sqlbook.cbl

Follow the instructions provided in the Replace book.cbl with sqlbook.cbl of the Modify COBOL Source topic.

Note: If you have previously done this tutorial and replaced the book.cbl with the sqlbook.cbl file, skip this step and continue with Modify the EXEC SQL Statement.

Modify the EXEC SQL Statement

In the Project Explorer, double-click the sqlbook.cbl entry to open the file in the editor.

Change:

exec sql connect to PUBS end-exec

To:

exec sql connect to db2 end-exec

Save sqlbook.cbl.

Modify book-rec.cpy

Follow the instructions provided in the Modify book-rec.cpy section of the Modify COBOL Source topic. No modifications are required.

Note: If you have previously done this tutorial and modified the book-rec.cpy, skip this step and continue with Modify Java Source.

Modify Java Source

Complete all sections of the Modify Java Source topic.

Note: If you have previously done this tutorial and modified the java source, skip this step and continue with Modify XML Source.

Add and Modify XML

Add context.xml

Following the instructions in the Add context.xml section of the Add and Modify XML topic, with this modification:

Copy and paste the following code into the context.xml file:

<Resource name="db2" auth="Container" type="javax.sql.DataSource"
               maxActive="10" maxIdle="10" maxWait="10000"
               username="db2UserName" password="db2Password"
               driverClassName="com.ibm.db2.jcc.DB2Driver"
               url="jdbc:db2://localhost:50000/sample:retrieveMessagesFromServerOnGetMessage=true;"/>

Replacing db2UserName and db2Password with your DB2 database credentials, or if applicable, your Windows authentication credentials.

Note: If you have previously done this tutorial and added the context.xml file, simply replace the contents of the file with the code shown here.

Modify web.xml

Follow the instructions in the Modify web.xml section of the Add and Modify XML topic with the following modification:

Replace the entire contents of web.xml with the following:

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xmlns="http://java.sun.com/xml/ns/javaee"
         xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
         version="2.5">
  <display-name>JSPBookDemo</display-name>
  <servlet>
    <servlet-name>BookServlet</servlet-name>
    <servlet-class>com.microfocus.book.BookServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>BookServlet</servlet-name>
    <url-pattern>/view</url-pattern>
  </servlet-mapping>
  <welcome-file-list>
    <welcome-file>view</welcome-file>
  </welcome-file-list>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>db2</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>

Run the JSP Application

No modifications are required for DB2 LUW. Follow the instructions in the Run the JSP Application topic.