DB2, JavaAgent

Create DB2 Database/Retrieve Data (Part 1)

Console



System Requirements:

Download DB2 Express-C

http://www-01.ibm.com/software/data/db2/express/download.html


Download Domino Designer 8.5.3 Environment (DDE)

http://www.ibm.com/developerworks/downloads/ls/dominodesigner/


Introduction:

Create a DB2 database, configure a DataSource, connect to the back-end, add records and retrieve DB2 data using a JavaAgent. Information that can be used to load records into Lotus Notes Domino Database located on your C Drive as documents; data that can also be accessed via Xpages Application...


Disclaimer:

Information contained in the following is presented as is. This tutorial assumes you have basic programming knowledge. All tutorials are based on an Eclipse/Eclipse-based software. Should you need to familiarize yourself with a certain Eclipse environment, prior to continuing this tutorial, please stop now and see our Tutorials page...


Download/Create DB2 Database (Configure DataSource)

At this point we suspect you have downloaded and installed DB2 Express-C and Domino Designer 8.5.3. Let's create a database, configure a DataSource, put some records, and access the back-end using a simple Java Program, run it as a JavaAgent, view data/documents via Console Window. Migrate code to Package Explorer and access via Xpages. Copy and paste below code samples, where necessary, to your environement... areas of interest have been highlighted for your convenience, screenshots included.


You should visit: http://www-01.ibm.com/software/data/db2/express/download.html for addtional information about install procedures and tips.


DB2ADMIN/DOMINODB

Use System User with DB2

Once you have created a DB2 database, it creates a system user on your Operating System (OS); this tutorial is based on Windows 7 OS. The DB2 user, namely DB2ADMIN is important to note because this the user your DB2 software will use to allow connection to the back-end sample databases, and those you will create from scratch (See Figure 1). In this case, we created a DOMINODB database to compliment part one of this tutorial (See Figure 2, 3, 4).




Figure 1




Running DB2 to create DOMINODB

DB2 is downloaded and installed! Access this bad boy and create DOMINODB. TIP: You can first run DB2 through command line to access the Environement you will create DOMINODB. Go to your Start menu in Windows 7 OS, click All Programs, and search for the DB2 folder, this should make available the name of your software, see Figure 2, and 3. It is important by the way to grab DB2 with all of the the bells and whistles in your download so you can manage the back-end appropriately.


Figure 2





Load DB2COPY1 and begin creating your first database...


Figure 3





Connect with DB2ADMIN System User

You are connecting to DB2COPY1 using the DB2ADMIN OS user created during the installation process. Go to Control Center to Open your copy of the software. Right-Click on the server, this is usually your computer/system name, and add the credentials you created when installing/creating your DB2 Server Environement.


Note: Default DB2 Control Center View can be whatever fits your purpose, for this tutorial we decided to go wtih Advanced, seems to work without issue. Please visit the URL provide earlier for additional information.


Figure 4





Create Database DOMINODB

At this point you need to expand, your servername (namely your computer name), expand the db2 instance of your choice (Figure 5,6,7), right-click on Databases to create your first database.


Figure 5






IMPORTANT:

If you are not connected to the Server, you will not be able to create a database. DB2ADMIN must be authenticated against IBM DB2 DB2COPY1 in in order for you to continue from this point on.


Once you are connected, your environement should resemble below screenshots; please note: these were completed prior as test for this tutorial. While your database may already be created, you must continue to use your login credentials to access your databases in order to access entities and perform tasks. For now, you will continue by adding a database table to your database.


Figure 6




Connection to your database is established, now what?


Figure 7





Create Table

Begin Creating table to house the data we will need to access from Java and Xpages (Figure 8 -17)


Figure 8




Figure 9






Figure 10






TIP: Above screen allows you to add all column names without having to leave the Pop-Up. You will type each column name and hit 'Apply' button to record the new column specified. In this excercise we decided to have all fields checked in nullable. We want to limit nulls from Java instead, although we recommend doing otherwise for your purpose.



Figure 11




After creating the columns, in this exercise, we are going to skip other bells and whistles for this database, let's just hit Next. Do continue to hit Next until you have the Finish option. This will give you an idea of what is important when constructing a database that demands ultimate care.


Figure 12



Figure 13





Figure 14




Figure 15






Figure 16




Right-click and Open the database to add data manually to test your connection


Figure 17




DOMINODB DataSource

Configure DataSource

You hace added a record to DOMINODB's USER_DETAILS table and are ready to configure a DataSource to allow Java to establish a connection to your DB2 database as first step. We will begin by going to Control Panel on Windows 7 (Figure 18-27) to access Administrative Tools/DataSources (ODBC) pane. Here are the Steps:


  1. Open Control Panel

  2. Select System and Secutiry

  3. Click on Administrative Tools

  4. Double-Click DataSources (ODBC)


Figure 18




Figure 19





Figure 20




We will skip adding a DOMINODB DataSource, since we've already done that for this tutorial. In your case, you would simply hit the 'Add' button to make available the subsequent steps in screenshots shown below.


Figure 21






Select your ODBC Driver and Click Finish


Figure 22




Figure 23





It is a good idea to browse the Advanced Settings Tab to have a look at additional Information


Figure 24




Once you check the 'Save Password' checkbox, under Data Source, you will be asked to save password data to a file, you should say Yes to this if you need to remember what the password is, otherwise, click No (Clicking No is preferred for security reasons).


Figure 25





We have also recorded support information about our DOMINODB DataSource


Figure 26





You are now ready to test the connection, you want to see a Pop-Up such as below...


Figure 27





Connect Java to DataSource

At this point, we assume you have included the db2 JDBC jar file to your ClassPath and have also reference this file in your project via Build Path.


Copy and Paste below code sample to your environement, run Java Program to test your connection, add and Retrieve records from DB2.


DB2AddDataBean;

/**

* Created: 2012.10.28.1.40.PM

* DB2AddDataBean JavaAgent

* DB2 Data Loading Bean

*/

package com.dokoll.solutions.inc.db2.test;


/**

* @author Dököll Solutions, Inc.

* @version 2012.10.28.1.40.PM

*

*/

import java.sql.*;


public class DB2AddDataBean {

public static void main(String[] args) throws Exception {

System.out.println("Entering Driver connection...");

Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

System.out.println("Entering actual connection...");

Connection conn = DriverManager.getConnection("jdbc:db2://localhost:50000/DOMINODB","db2admin","yourpassport");

System.out.println("Begin statement...");

Statement stat = conn.createStatement();

System.out.println("Entering query...");

PreparedStatement prep = conn.prepareStatement("insert into DB2ADMIN.USER_DETAILS values (?,?,?, ?, ?, ?, ?, ?, ?, ?, ?)");

// //Added Data for report

// //2012.10.28.9.03.PM

prep.setString(1, "XYZ001EBC");

prep.setString(2, "username13");

prep.setString(3, "XYZ001EBCBOS");

prep.setString(4, "Boston");

prep.setString(5, "XYZ001EBCNAME13");

prep.setString(6, "01/01/1943");

prep.setString(7, "70");

prep.setString(8, "GuntherBerg, Inc.");

prep.setString(9, "33335555899");

prep.setString(10, "2000.01.11.11.10.PM");

prep.setString(11, "Commonwealth");

prep.addBatch();

prep.setString(1, "XYZ002FBC");

prep.setString(2, "username14");

prep.setString(3, "XYZ002FBCCAN");

prep.setString(4, "Montréal");

prep.setString(5, "XYZ002FBCNAME14");

prep.setString(6, "01/01/1980");

prep.setString(7, "39");

prep.setString(8, "SmashBook, Inc.");

prep.setString(9, "99994444811");

prep.setString(10, "2010.12.19.1.10.AM");

prep.setString(11, "Oh Canada");

prep.addBatch();


System.out.println("Values added...");

conn.setAutoCommit(false);

prep.executeBatch();

conn.setAutoCommit(true);

ResultSet rs = stat.executeQuery("select * from DB2ADMIN.USER_DETAILS");

System.out.println("Submitting to console...");

while (rs.next()) {

System.out.println("ID |"+"AllegationCode|"+"Office |"+"Investigator|"+"DateOpen |"+"Age |"+"AllegationNumber|"+"Licensee |"+"Subject |"+"Region |");

System.out.println(rs.getString("ID")+" |"+rs.getString("AllegationCode")+" |"+rs.getString("Office")+" |"+ rs.getString("Investigator")+" |"+ rs.getString("DateOpen")+" |"+ rs.getString("Age")+ " |"+ rs.getString("AllegationNumber")+ " |"+ rs.getString("Licensee")+" |" +rs.getString("Subject")+ " |"+ rs.getString("Region"));


}

rs.close();

conn.close();

}

}


Console data...


DB2 driver loaded...

DB2 Database Connected

ID |AllegationCode|Office |Investigator|DateOpen |Age |AllegationNumber|Licensee |Subject |Region |

XYZDB2007 |XYZDB2NAME10007 |New York |nyusername10xyz |10/28/2012 |42 |XYZDB2007USERNAME10 |007123456789XYZ |Dököll Solutions, Inc. |null

ID |AllegationCode|Office |Investigator|DateOpen |Age |AllegationNumber|Licensee |Subject |Region |

XYZ008ABC |XYZ008ABCALB |Albany |XYZ008CBCNAME11 |01/01/1971 |50 |2012.05.18.12.30.AM |10029870899 |Good Buddy |Capital District

ID |AllegationCode|Office |Investigator|DateOpen |Age |AllegationNumber|Licensee |Subject |Region |

XYZ009CBC |XYZ008CBCALB |Albany |XYZ008CBCNAME12 |10/01/1990 |25 |2010.11.11.1.10.PM |22224444811 |SmashBook, Inc. |Capital District

ID |AllegationCode|Office |Investigator|DateOpen |Age |AllegationNumber|Licensee |Subject |Region |

XYZ000DBC |XYZ000DBCALB |Albany |XYZ000DBCNAME13 |10/01/2003 |9 |2010.11.11.1.10.PM |00004444811 |BookWorm.com |Capital District

ID |AllegationCode|Office |Investigator|DateOpen |Age |AllegationNumber|Licensee |Subject |Region |

XYZ001EBC |XYZ001EBCBOS |Boston |XYZ001EBCNAME13 |01/01/1943 |70 |2000.01.11.11.10.PM |33335555899 |GuntherBerg, Inc. |Commonwealth

ID |AllegationCode|Office |Investigator|DateOpen |Age |AllegationNumber|Licensee |Subject |Region |

XYZ002FBC |XYZ002FBCCAN |Montréal |XYZ002FBCNAME14 |01/01/1980 |39 |2010.12.19.1.10.AM |99994444811 |SmashBook, Inc. |Oh Canada


Conclusion:

You can now create a DB2 database and a system user, create a table, configure a DataSource, and add/retrieve back-end Data, submit to console.


Questions, comments, please post a brief message on our Contact form on the main site.


Thank you for coming...