IBM Change Data Capture (CDC) Replication step by step guide to install, configure and create instance

This tutorial covers step by step guide  to install and  configure  IBM Change Data Capture (CDC) (Data Replication)  in Linux machine. Also covers that how to create CDC instance for replications. Now let us see the steps to install and configure Change Data Capture in Linux.


Brief Steps

1. Install IBM Change Data Capture Engine (IBM CDC) (in this example, version 6.5) on both source and target machines

2. Create CDC instance on both servers

3. Install Access Server, create  user and start Access Server.

4. Install Management Console in the  client machine and connect to Access Server

5. Create Subscriptions and start replication


Installing  IBM Change Data Capture (IBM DB2) 6.5

1. Copy necessary setup files to any folder in the server where you want to install IBM CDC.

2. Change to the folder where installation binary file is located

3. Make the installation binary file executable and start installation

[root@test ISCDC_652_DB2LUW]# ./setup-cdc-linux-x86-db2luw.bin
-bash: ./setup-cdc-linux-x86-db2luw.bin: Permission denied
[root@test ISCDC_652_DB2LUW]# chmod 777 setup-cdc-linux-x86-db2luw.bin
[root@test ISCDC_652_DB2LUW]# ./setup-cdc-linux-x86-db2luw.bin

Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...

Launching installer...

Preparing CONSOLE Mode Installation...

===============================================================================
Installer                                        (created with InstallAnywhere)
-------------------------------------------------------------------------------


===============================================================================
Introduction
------------

InstallAnywhere will guide you through the installation of IBM InfoSphere
Change Data Capture (IBM DB2) 6.5

Respond to each prompt to proceed to the next step in the installation.  If you
want to change something on a previous step, type 'back'.

You may cancel this installation at any time by typing 'quit'.

PRESS <ENTER> TO CONTINUE:


Now Press Enter

===============================================================================


    International Program License Agreement is displayed

Press Enter to continue viewing the license agreement, or enter "1" to
   accept the agreement, "2" to decline it, "3" to print it, or "99" to go back
   to the previous screen.: 1

===============================================================================
Choose Install Folder

Where would you like to install?

  Default Install Folder: /opt/IBM/InfoSphereChangeDataCapture/ReplicationEngineforIBMDB2


Change install folder if required and  press Enter


ENTER AN ABSOLUTE PATH, OR PRESS <ENTER> TO ACCEPT THE DEFAULT
      : /opt/IBM/CDC/Engine

INSTALL FOLDER IS: /opt/IBM/CDC/Engine
   IS THIS CORRECT? (Y/N): Y

Press Y

===============================================================================
Pre-Installation Summary


Please Review the Following Before Continuing:

Product Name:
    IBM InfoSphere Change Data Capture (IBM DB2)

Install Folder:
    /opt/IBM/CDC/Engine

Link Folder:
    /tmp/install.dir..../Do_Not_Install

Disk Space Information (for Installation Target):
    Required:  .... bytes
    Available: .... bytes

PRESS <ENTER> TO CONTINUE:

Press enter to continue..


===============================================================================
Installing...
-------------

 [==================|==================|==================|==================]
 [------------------|------------------|------------------|------------------]



===============================================================================

Install Complete

Congratulations. IBM InfoSphere Change Data Capture (IBM DB2) has been successfully installed to:
   /opt/IBM/CDC/Engine

You can launch the Configuration Tool at any time by running
   /opt/IBM/CDC/Engine/bin/dmconfigurets



Now IBM CDC installation is complete.  Now let us configure and create CDC Instance



Creating New Instance 

Launch Configuration Tool? (1=Yes, 2=No) (DEFAULT: 1): 1
Welcome to the configuration tool for IBM InfoSphere Change Data Capture (IBM DB2). Use this tool to create instances of IBM InfoSphere Change Data Capture (IBM DB2).

Press ENTER to continue...
Initializing. Please wait...

Select 1 to create new instance

CONFIGURATION TOOL - CREATING A NEW INSTANCE


Enter the name of the new instance: sourcedb
Enter the server port number [10901]:
Enter the auto-discovery port number or type 'DISABLE' [DISABLE]:

Staging Store Disk Quota is used to limit the disk space used by IBM InfoSphere Change Data Capture staging Store. If this space is exhausted, this instance may run at a lower speed. The minimum value allowed is 1 GB.

Enter the Staging Store Disk Quota for this instance (GB) [100]: 10
Enter the Maximum Memory Allowed for this instance (MB) [1024]:
Enter the bit version (32/64) [64]:
Select a DB2 Instance

1. db2inst1
2. Other...

Select a DB2 Instance: 1
Select a database name

1. test
2. Other...

Select a database name: 1
Would you like to configure advanced parameters (y/n) [n]:
Enter the username: db2inst1
Enter the password:
Retrieving schema list...
Metadata schema:

1. ADMINISTRATOR
2. DB2INST1
3. NULLID
4. SQLJ
5. SYSCAT
6. SYSFUN
7. SYSIBM
8. SYSIBMADM
9. SYSIBMINTERNAL
10. SYSIBMTS
11. SYSPROC
12. SYSPUBLIC
13. SYSSTAT
14. SYSTOOLS


Press ENTER to continue...
15. Other...

Select a database schema for metadata tables: 2
Enter the refresh loader path: /opt/IBM/CDC/Loader
Note : Please ensure The Refresh Loader Path directory is created and the user  have read and write permission for the directory.

Creating a new instance. Please wait...


Instance sourcedb was successfully created.

Would you like to START instance db2inst1 now (y/n)?y


Starting instance sourcedb. Please wait...

Instance sourcedb started successfully. Press ENTER to go to the Main menu...



Now the CDC instance for the source database is created and started



To launch configuration tool by command.
Change to the folder
 /opt/IBM/CDC/Engine/bin

 run the below command

 #./dmconfigurets


Initializing. Please wait...


MAIN MENU


1. List Current Instances
2. Add an Instance
3. Edit an Instance
4. Delete an Instance
5. Consolidate Instances

6. Exit

Enter your selection: 1


LIST OF CURRENT INSTANCES
-------------------------

Name      Server Port Database      Schema      Status
--------- ----------- ------------- ----------- ------------
sourcedb  10901       test          DB2INST1    started



to start the CDC instance in 32 bit through command


[root@test bin]# ./dmts32 -I sourcedb
IBM InfoSphere Change Data Capture is running.


to start in 64 bit


[root@test bin]# ./dmts64 -I sourcedb
IBM InfoSphere Change Data Capture is running.


to stop IBM Change Data Capture:

./dmshutdown -I sourcedb



Follow the above steps for the target server where target database is installed. Target database may be in the same server or any different server.  i.e.  Install, Configure CDC  and create CDC instance for the target database.

Start CDC instance 

Now assume that CDC instance is running in both servers.


[root@test bin]# ./dmts64 -I targetdb
IBM InfoSphere Change Data Capture is running.



Example

Source Instance : sourcedb

Destination Instance : targetdb




To make communications between Management Console and replication engine processes, IBM CDC  Access servcer needs to be installed. It can be installed in the same or different machine. Default port to connecte to access server is 10901.  The port can be changed during installation time.

To Install Access Server:

1. Copy necessary setup files to any folder in the server where you want to install access server

2. Change to the folder where installation binary file is located

3. Make the installation binary file executable and start installation

       #./<installation_file>



Create an  user so that access server can be connected from Management console.

To create an Admin user

./dmcreateuser admin admin admin password ADMIN TRUE FALSE TRUE


To start the access server:

[root@test# cd /opt/IBM/CDC/AccessServer/bin
[root@accesssvr bin]# ./dmaccessserver



Now install, Management console in your windows machine. This is a gui based tool to create and manage subscriptions. The same task can also be done in Accessserver with commands. But it is always easy to manage subscriptions using Management console

Once management console is installed, open the Management Console, create subscriptions and start mirroring.

In the next tutorial, we will see how to create and manage subscriptions for data replication.

Reference: IBM Change Data Capture Guide

Read More

db2diag log file path location and how to change location, size and reset db2diag.log file

db2diag.log file is useful for DBA for trouble shooting the database.  All errors, warnings, event messages, and administration notification messages  starting from installation are captured in the db2diag.log file. Also any failure in the db2 server / database are captured in db2diag.log file. DBA  can use this log file and trace. 

We can use db2diag tool with various options for reading and displaying db2diag.log file.

db2diag.log grows indefinitely. db2diag.log can either be a single file or a rotating file with fixed size  depending upon the database configuration made.

Now let us see where the db2diag.log file is stored in Windows and Linux by default.

In Windows (Vista or any Latest OS)

C:\ProgramData\IBM\DB2\<DB2 COPY NAME>\DB2\DIAG0000

eg. C:\ProgramData\IBM\DB2\DB2COPY1\DB2\DIAG0000

where ProgramData is hidden folder.

In Windows (XP or any Old OS)

C:\Documents and Settings\All Users\Application Data\IBM\DB2\<DB2 copy name>\DB2\DIAG0000


You can retrieve the DIAGPATH  from dbm configuration using the command

db2 get dbm cfg | find/I "diagpath"   in windows.

In Linux :

/home/db2inst1/sqllib/db2dump


Get the current diagpath using command in Linux.


db2 get dbm cfg | grep -i "diagpath"   


1. How to change the default path location of db2diag.log?

Command to change db2diag.log location:

db2 update dbm cfg using DIAGPATH local_path

For Eg in Windows, I issued the following command.

C:\Program Files\IBM\SQLLIB_01\BIN>db2 update dbm cfg using DIAGPATH c:/db2log
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

Now restart the db2 server.

db2 get dbm cfg | find/I "diagpath"
 Diagnostic data directory path               (DIAGPATH) = c:\db2log\
 Current member resolved DIAGPATH                        = c:\db2log\


2. How to reset the db2diag.log file?


If db2diag.log file is single file, it grows indefinitely. You can reset at point of time so that the content of the file is moved to db2diag.log_current_timestamp and  new db2diag.log file is created.

To reset, use the db2diag tool with the option A

db2diag -A

For Example :

c:\ProgramData\IBM\DB2\DB2COPY2\DB2\DIAG0000>db2diag -A db2diag.log
db2diag: Moving "c:\ProgramData\IBM\DB2\DB2COPY2\DB2\DIAG0000\db2diag.log"
         to     "c:\ProgramData\IBM\DB2\DB2COPY2\DB2\DIAG0000\db2diag.log_2015-05-12-08.20.34"



3. How to make db2diag.log  a rotating file with fixed size?


Simply set the size of the db2diag.log file using the below command.

db2 update dbm cfg using DIAGSIZE 10

Now the size of the log file size is fixed to 10 MB. The rotating file starts with db2diag0.log

C:\db2log>db2 get dbm cfg | find/I "diagsize"
 Size of rotating db2diag & notify logs (MB)  (DIAGSIZE) = 10

Read More

JNDI DataSource Configuration in JBoss with DB2 in Eclipse Example

Assume that Eclipse is configured with JBoss Application Server 4.0. Before an application can get data from any Database (db2, Postgress, MySql , Oracle, etc..), it needs to establish a connection to the database. In our example we are going to connect with db2 database (eg. Employee). This can be done using JDBC in following ways.

1. Use the DriverManager class to establish a connection -> Load the Db2 JDBC Driver using Class.forName() & Create a connection to a db2 database using DriverManager.getConnection() method

2. Connect to the db2 database through a DataSource object. This is to use an implementation of the javax.sql.DataSource interface in conjunction with the Java Naming and Directory Interface (JNDI).

Now we are going to connect using the IInd way i.e using an implementation of the javax.sql.DataSource interface in conjunction with JNDI. Please follow the following steps for successful data base connection with db2 . .

It is always better to recycle and reuse existing connections to a data base than opening a new connection. So it needs to maintain a DB connection pool. JBoss supports DBCB which uses the Jakarta-Commons Database Connection Pool

Steps to be followed to configure JNDI Data Source in JBoss 4.0 with DB2 (in our example: jdbc/JbossTestDS)

1) Make an appropriate JDBC driver for DB2 available to JBoss & to your web application (Necessary driver class files for Db2 are available in the JARs db2jcc.jar, db2jcc_license_cisuz.jar, db2jcc_license_cu.jar. Copy these jars to Jboss (Drive:\jboss-4.0.0\server\default\lib) also include these jars to build path of the application )

2) Three Important XML files are to be configured. They are web.xml, Jboss-web.xml, *-ds.xml. Details are given below.

Assume that installation folder of Jboss 4.0 Application Server is D:\jboss-4.0.0. JNDI name is JbossTestDS, Database Name is Employee.

a) Define a reference lookup name (eg. jdbc/JbossTestDS) to an external resource in the WEB-INF\web.xml. Modify the web application deployment descriptor (/WEB-INF/web.xml) to define the JNDI name for the Datasource (ie. jdbc/datasourcename) for the web application level.

web.xml

The following lines will be added in the web.xml before </web-app> for declaring resource requirements.

<resource-ref>
<description>Database Connection</description>
<res-ref-name>jdbc/JbossTestDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref> 


b) Next Modify the jboss-web.xml located in the WEB-INF in the application.

jboss-web.xml

The following lines will be added in the jboss-web.xml between <jboss-web> </jboss-web>

<resource-ref>
<res-ref-name>jdbc/JbossTestDS
<res-type>javax.sql.DataSource<jndi-name>java:/jdbc/JbossTestDS </resource-ref>

More than one resource reference also can be defined.

c) Deployment descriptor (web.xml) only define a resource adaptor. This resource adapter can be used by integrating into the JBoss application server using a ds.xml descriptor file. Configure JBoss *-ds.xml ( in our example : jdbc-db.xml ) located in D:\jboss-4.0.0\server\default\deploy
jdbc-ds.xml

The following lines will be added in the jdbc-ds.xml between <datasources> </datasources>

<local-tx-datasource>
<jndi-name>jdbc/JbossTestDS</jndi-name>
<connection-url>jdbc:db2://localhost:50000/employee</connection-url>
<driver-class>com.ibm.db2.jcc.DB2Driver</driver-class>
<user-name>abc</user-name>
<password>xyz</password>
<min-pool-size>0</min-pool-size>
<metadata>
<type-mapping>DB2</type-mapping>
</metadata>
</local-tx-datasource>

Note that the jndi name jdbc/JbossTestDS must match the resource reference specified in the web application deployment descriptor

Finally write Java Code to use the resource.

ConnectionFactory.java

    
 public static Connection getConnection() throws CDRCFatalException {

  Context  m_ctxLookup = null;

  DataSource  ds = null;

  Connection  conn = null;

 try {

    Context initCtx = new InitialContext();

    Context envCtx = (Context) initCtx.lookup("java:comp/env");

     ds = (DataSource)   envCtx.lookup("jdbc/JbossTestDS");

 } catch (NamingException exc) {

   System.out.println("Lookup fail db " + exc.toString()+ " The context is " + m_ctxLookup);

   throw new CDRCFatalException( exc.getMessage(), "01001" );

  }



  try {

      conn = ds.getConnection();

  }catch (SQLException exc) {

   System.out.println(" Fail to connect database " + exc.toString()+ " The context is " + m_ctxLookup);

   throw new CDRCFatalException( exc.getMessage(), "02006");

  }

  return conn;

 }

You can call the above getConnection() static method whereever connection is required in Java Code

 Connection conn = null;

conn = ConnectionFactory.getConnection();

 

Read More

Error "The CODEPAGE option is incompatible with the LOBSINFILE option" Solution

Error : The "CODEPAGE" option is incompatible with the "LOBSINFILE" option

Cause of the Error: The error comes due to code page mismatch between source database and target database. See the below example.  CODEPAGE is page encoding. By default OS also having default encoding (1252).

db2move sourceDB export
                          - where codepage of sourceDB was set to 1208

db2move targetDB import
                           - where codepage of sourceDB was set to 1252

in the above case, error is caused due to codepage mismatch.

Solution : Change the target database codepage to 1208. Follow the below steps to change code page.
1. db2set db2codepage=1208
2. db2start
3. db2stop


You can find out the code page of a database using the following commands
1. db2 connect to dbname
2. db2 get db cfg | find "code"

You may get the below lines

Database code page                                      = 1208
Database code set                                       = utf-8
Database country/region code                            = 1

Read More

How to Configure JDBC Datasource (JNDI) in Tomcat 7 with DB2 in Ecplise Example.

The following steps tells how to Configure JDBC Data Resources (JNDI) in Tomcat 7 with DB2 in Ecplise with Example. Assume that Eclipse is configured with Apache Tomcat Application Server 7.0.

A database connection pool creates and manages a pool of connections to a database. It is always better to recycle and reuse already existing connections to a dB than opening a new connection. Tomcat supports DBCP connection pool from the Commons project by default. It is also possible to use any other connection pool that implements javax.sql.DataSource

Suppose if you need to access db2 database via a JDBC driver, follow the following steps for successful data base connection with db2.

Steps to be followed to configure JDBC Data Resources (JNDI) in Tomcat 7 with DB2 (in our example jdbc/javaOnlineDS)

In Brief :
1) Place appropriate JDBC drivers of DB2 to Tomcat for  your web application (i.e place it inside WEB-INF\lib)
2) Configure web.xml, context.xml files as described in the detail section.

In Detail :
Copy all necessary JDBC drivers of DB2 database, to the lib folder under WEB-INF. i.e  copy db2 jdbc drivers db2java.zip, db2jcc.jar, db2jcc_license_cisuz.jar, db2jcc_license_cu.jar to the lib folder.

Next, Declare Resource Requirements in web.xml. Modify the web application deployment descriptor (/WEB-INF/web.xml) to declare the JNDI name for the Database resource (ie. jdbc/javaOnlineDS) for the web application level.

web.xml
The following lines will be added in the web.xml before </web-app> for declaring resource requirements.

<resource-ref>

<description>DB Connection Pool</description>

<res-ref-name>jdbc/javaOnlineDS</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

</resource-ref >

context.xml

The following lines will be added in the context.xml under META-INF between <Context> </Context>

<Resource name="jdbc/javaOnlineDS" auth="Container"   type="javax.sql.DataSource" username="abc" password="*****" driverClassName="com.ibm.db2.jcc.DB2Driver"  url="jdbc:db2://localhost:50000/test" maxActive="8" maxIdle="4" />

Note that the resource name jdbc/javaOnlineDS must match the value specified in the web application deployment descriptor


Now let us write Java code to use the resource. Create a connection class

ConnectionFactory.java

package javaonline;

import java.sql.Connection;

import java.sql.SQLException;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.sql.DataSource;


public class ConnectionFactory {

 
  public static Connection getConnection()  {

    Context  m_ctxLookup = null;

    DataSource  dataSource = null;

    Connection  conn  = null;


   try {
 
      Context initCtx = new InitialContext();

      Context envCtx = (Context) initCtx.lookup("java:comp/env");

      dataSource = (DataSource)   envCtx.lookup("jdbc/javaOnlineDS");

    } catch (NamingException exc) {

      System.out.println("Lookup fail of database " + exc.toString()+ "The context is " + m_ctxLookup);

       }


    try {

      conn = dataSource.getConnection();

    }catch (SQLException exc) {

      System.out.println("Getting Connection. Fail of database " + exc.toString()+ "The context is " + m_ctxLookup);

    }

    return conn;

   }

}



Call the above getConnection() static method in DAO class

public class GetDetailsDAO { 
public String getData() throws  JSONException

 {  

  PreparedStatement ps = null;

     ResultSet rs = null;

     Connection conn = null;

    try

     {     

      conn = ConnectionFactory.getConnection();


      String strQry = "SELECT * from Employee";

      ps = conn.prepareStatement(strQry);

      rs = ps.executeQuery();

  ----

  ----
         } catch (SQLException se) {}

 }
}

Read More

Java to create & use DataSource Object in DB2 in the application using DataSource interface without using tools

The preferred way to connect to a database is using DataSource interface rather than using DriverManager interface. DriverManager class requires JDBC driver class name and driver URL. So we have to mention the Driver class and Driver URL which are specific to a JDBC vendor, driver implementation. This reduces the Portabilty of the Application. To improve potability of your applications among data sources, DataSource interface may be used. JDBC version 2.0 provides the DataSource interface. The following program creates datasource object to connect to a database using the DataSource interface in the application itself without using any tool like WAS. Let us see the steps involved to create datasource object and connect to database.

1. Import the package which contains the DataSource interface implementation.

2. Create object of the DataSource implementation (in our example DB2DataSource which has built in support for connection pooling )

3. Set the properties like DatabaseName, Description, User, and Password of the DataSource object.

4. To associate the datasource object with the logical name jdbc/studentDS, register the object with the Java Naming and Directory Interface Naming (JNDI). This is optional.

// Code Starts here

import java.sql.*;        
import javax.sql.*;       // JDBC 2.0 
import com.ibm.db2.jcc.DB2DataSource;   //DB2 Universal JDBC Driver interface supports connection pool
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
import javax.naming.*;    
public class DB2DS {
  public static void main(String[] argv) {
DB2DataSource ds=null;
 try {
ds=new DB2DataSource();        //inbuilt support for connection pool
ds.setDatabaseName("student");          
ds.setDescription("Student Database");
ds.setUser("abc"); //db user id
ds.setPassword("*******"); // db password

 } catch (Exception e) {
      System.out.println("Error"); 
      e.printStackTrace();
      return;
      }
Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs=null;
   try {
    conn=ds.getConnection();      
    if (conn != null) System.out.println("Database Connection Established ");  else System.out.println("DB Connection Failed ");
    pstmt=conn.prepareStatement("Select * from stu_detail");
    rs=pstmt.executeQuery();
    if(rs!=null)
      {
      while(rs.next())
       {
       System.out.println("Student ID: "+rs.getString("ID"));
       System.out.println("Student Name: "+rs.getString("name"));
       }
      }
     } catch (SQLException e) {
        System.out.println("Error in Connection");
        e.printStackTrace();
        return;
     }
}
}

Read More

How to display the number of rows affected in DB2 INSERT / DELETE / UPDATE / MERGE commands

 By default, DB2 does not print the number of records affected when we run the DML  commands like INSERT,  DELETE, UPDATE or MERGE in the command prompt. So to display number of records affected when you run the above DML commands, you can use  following options.

You  can use the option m, in the command itself as given below

db2 -m update employee set payscale='42000-3%-72000' where doj<='1976-01-01'
  Number of rows affected : 50
DB20000I  The SQL command completed successfully.


Also, You can use the below db2set command to set the command options "m" permanently for all instances and databases,

db2set DB2OPTIONS=-m

db2stop

db2start

Or you can update the options m to ON  permanently using the update command options.

db2 update command options using m ON

            In windows, if you may get the  below error OR your changes done to the command option may not be  retained when you run the above command,

error :   db2 update command options using m ON SQL0104N  An unexpected token "m" was found following "USING".  Expected tokens may include:  "A".  SQLSTATE=42601. Solution will be

Just go to db2 prompt.

db2=> update command options using m ON

You can cehck the command options using the below command

list command options


db2 => delete from Employee where emp_code='E1022'
  Number of rows affected : 1
DB20000I  The SQL command completed successfully.

Read More

DB2 Gui Tools and Commands to start GUI tools

DB2 Universal Database (UDB) has many graphical tools that make the DBA’s database management job easier and it improves the productivity of the developer in application development. The following are the Gui Tools available in DB2 UDB. 1. Control Center , 2. Command Center , 3. Configuration Assistance , 4. Health Center, 5. Task Center , 6. Development Center , 7. Journal , 8. Memory Visualizer , 9. Information Center , 10. Replication Center

Now We are going to the use of db2 gui tools and how to invoke these Tools
All the GUI tools can be run through Control Center. It is better to invoke the GUI tools directly from the command line. 

1. Control Center : Using the Control Center, We can perform administrative tasks, monitor systems, and develop applications.This is the Master of all DB2 graphical tools We can invoke various DB2 GUI tools directly from the Control Center. Command to invoke Contral Center : db2cc

2. Command Center : Allows you to write and execute DB2 commands and SQL statements interactively. It also lets you write scripts containing DB2 commands &amp; SQL statements and run them immediately or schedule for later execution. Command to invoke Command Center : db2cmdctr

3. Task Center :  Tool to manage the scheduling and execution of DB2 Command and OS scripts. Upon completion of a job, it can create notifications to be sent to e-mail or pager. Command to invoke Task Center : db2tc

4. Journal:  Keeps a running history of all interactive command and script executions submitted using the DB2 GUI tools. Command to invoke Journal : db2journal

5. Memory Visualizer :  Used to monitor the memory allocation of a DB2instance. Command to invoke Memory Visualizer : db2memvis 

6. Health Center: Used to monitor the state and utilization of many parts of the database manager, the databases, and user agents. Command to invoke Health Center : db2hc

7. Development Center :  It has the features and functions that make it easier for developers to build and deploy applications for either Java or Microsoft environments. The Stored Procedure Builder has been included in the Development Center. Command to invoke Development Cente : db2dc 

8. Configure Assistance :  The Configuration Assistant has many new features. One is to connect &amp; access the remote data base locally using TCP/IP . Command to invoke Configure Assistance : db2memvis

9. Replication Center :  We can use to set up and administer our replication environment which include DB2-to-DB2 replication and DB2 and non-DB2 relational databases. Command to invoke Replication Center : db2rc 

10. Information Center : Provides quick access to DB2 product information and is available on all OS for which the DB2 administration tools are available. Command to invoke Information Center : db2ic 

11. Data Studio Client : In latest version, DB2 control center in replaced with Data Studio Client. You may need to install the Data Studio Client separately.

 To start, Data Studio Client, Change to the home directory where you have installed DataStudio. Then run eclipse.exe 

For example : F:\Program Files\IBM\DSWC4.1>Eclipse.exe


Commands in Brief :

Control Center : db2cc 
Command Center : db2cmdctr 
Configuration Assistance : db2ca 
Health Center : db2hc 
Task Center : db2tc 
Development Center : db2dc 
Journal :  db2journal 
Memory Visualizer : db2memvis 
Information Center : db2ic 
Replication Center : db2rc 

Data Studio : eclipse.exe

The commands can be used in both Linux &amp; Windows environment. The command can be run in db2 command prompt window .

In windows you can get the command prompt (command line processor (CLP)) by running the command db2cmd.

In Linux,  to invoke the DB2 CLP or issue DB2 command directly from the Linux command prompt, we need to run the db2profile in the profile of the login user ID. For that we need to include the following line in login profile. INSTHOME/sqllib/db2profile Where INSTHOME is the DB2 instance home directory.

Read More

How to Write own Java user-defined functions in DB2 & call UDFs in DB2 SQL with Example Step by Steps

            User Defined Functions (UDFs) are own functions written by the user which can be used in SQL, DDL or DML statements. UDfs can be written in two ways on DB2 Universal Database. One is SQL UDFs which is based on procedural extensions to the SQL language, highly used by other DBMS. The Other one is External UDFs which is written host languages like Java and others. Now let us write a user defined function in Java which can be used in in SQL. For example, In oracle INITCAP function returns the first letter of each word in uppercase, all other letters will be in lowercase. This function may not be available in DB2. Now we can write this function in Java can be used in DB2 .

Steps involved to write, deploy and run the UDFs

1. Write program for initCaps in Java

import java.io.*;
import java.io.*;
public class initCaps
{
public static String firstCaps(String line)  throws IOException 
{
 StringReader in = new StringReader(line); 
 boolean isNextCharSpace = true;
 StringBuffer proper = new StringBuffer();    
int i=0;
     while((i=in.read())!=-1) 
 {      
      char c = (char)i;      
      if (c == ' ')  {      proper.append(c);      isNextCharSpace = true;     } 
......
.....
}
}

Now compile the above program .

E:\DB2\IBM\SQLLIB\java\jdk\bin> javac initCaps.java

You can have more function in the same java file. Please ensure the java file is compiled and running using the same java version.

Covert the class file to Jar file by using the following command

jar cf myjar6.jar initCaps.class

Now register the jar file in DB2 using the following steps

Open the DB2 command prompt , then run the following command

CALL SQLJ.INSTALL_JAR('file:E:\DB2\IBM\SQLLIB\java\jdk\bin\myjar6.jar',testjar);

      By running the above command, the testjar.jar is file is copied to E:\DB2\IBM\SQLLIB\FUNCTION\jar\Schema Name\

Now Create a user-defined function in Db2 using Create Function

CREATE FUNCTION firstCaps(source varchar(3000)) RETURNS varchar(3000) LANGUAGE JAVA DETERMINISTIC NO SQL NOT FENCED EXTERNAL NAME 'testjar:initCaps!firstCaps' PARAMETER STYLE JAVA NO EXTERNAL ACTION

              where firstCaps is the function name which accepts string variable and returns a string variable. testjar is the jarfile name. initCaps is the Class file name. Again firstCaps used in External Name is the Java Method name. The string variable passed to the firstCaps function is passed to Java Method then processed and returns the Proper case of the string.

Now let us write the SQL query using the above user defined function.

select SchemaName.firstCaps(empname) from emp

Some of the Trouble shooting :

         You may get the error : SQL4301N Java or .NET interpreter startup or communication failed, reason code "0". SQLSTATE=58004

          Please check JDK_PATH by the command

In windows :             db2 get dbm cfg | find "JDK".
In Linux :               db2 get dbm cfg | grep -i JDK


The JDK_PATH is one level up to bin directory. So Update the path using the following command.

db2 update dbm cfg using JDK_PATH = E:\DB2\IBM\SQLLIB\java\jdk

           If the above error with reaon code "4" means you can check JAVA_HEAP_SZ. It should be enough to handle the User Defined function / Stored Procedure. 512 may be enough to handle. You can change the value using the following command.

db2 update dbm cfg using JAVA_HEAP_SZ 4096

Java stored procedure or user-defined function could not load Java class reason code "1". SQLSTATE=42724

Class Name mentioned in the Create Function may be mis-spelled. This error may be generated using java.lang.UnsupportedClassError. You have to ensure you are compiling and running the java class file using the same jdk version. Error may be seen E:\DB2\IBM\SQLLIB\DB2\db2dialog.log.

If No errors, then the statement select SchemaName.firstCaps(empname) from emp will give the output. Suppose the database having the employee names akash kumar, Micky jen, jackson Durai.

The output will be

Akash Kumar

Micky Jen

Jackson Durai

Read More

How to call db2 stored procedure from java program with example?

JDBC provides three statement interface which is used to send an SQL statement to the database server.

1. Statement

2. PreparedStatement which extends Statement

3. CallableStatement which extends PreparedStatement

Vendors of JDBC Driver provide classes that implement the above interfaces. Without a JDBC driver, you cannot create objects based on these interfaces. Database connection is required to to create statement object. This tutorial explains about CallableStatement objects and also how to call a DB2 stored procedure from a java application. Please go through my earlier post on how to create stored procedure in db2. Stored procedure can be called using the SQL CALL statement in DB2. Now how to call the stored procedures located on the database server from your Java application?

Using CallableStatement interface in java:

      CallableStatement object enable you to call and execute stored procedures stored on the database server from your Java application. Three types of JDBC parameters are there. They are IN, OUT, and INOUT

1. IN - parameters used for input. You can set values to IN parameters with the setXXX() methods.

2. OUT - result parameter used for output which returns output value of the stored procedure.

3. INOUT - parameter used for both input and output values parameters

A question mark (?) symbol serves as a placeholder for a parameter. The call to invoke the Stored procedure is written in an JDBC escape syntax that may take the followings forms

1. {call procedure_name[(?, ?, ...)]} - which accepts input parameters but no result parameter

2. {? = call procedure_name[(?, ?, ...)]} which returns a result parameter

3. {call procedure_name} - for no input / output parameters

A CallableStatement can return one or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement.
                            cstmt.getMoreResults(); -used to point to the second or next result set.

Now let us see the steps to create a CallableStatement Object. Before creating it, let us create a stored procedure in DB2. In our example, I have created two Stored procedures in db2.

Ist Stored Procedure (SP) example : to return all records matching with a given salesman_id and date of sale

      input - salesmanid, sales_date

      output - result set (salesman_id, salesman, item_name , sales_date, sales_amt)

Stored Procedure for the above problem is given below

Stored Procedure I:

    
CREATE PROCEDURE  ItemSalesBy (salesmanid  varchar(5), dateofsale date)     SPECIFIC sp10    DYNAMIC RESULT SETS 1 
P1: BEGIN
    DECLARE cursor1 CURSOR WITH RETURN FOR   select salesman_id, (select c.name from salesman c where c.salesman_id=b.salesman_id) SalesMan,  (select a.item_name from item_master a where a.item_code=b.item_code) ItemName, sales_date, sales_amt from salesmantxn b  where b.salesman_id=salesmanid and b.sales_date=dateofsale;
    OPEN cursor1;
END P1 
@

We can create and call (execute) the above stored procedure using command line processor (CLP) and through java program.

To create stored procedure using CLP, store above stored procedure in a file . for example salesman.sql . Now run the following commands

db2 connect to test
db2 -td@ -vf salesman.sql

to call the above SP through CLP

db2 call ItemSalesBy('101','2012-02-25'), where 101 is the salesman id, 2012-02-25 is the date of sale

Now let us create and call (execute) the above stored procedure using java program

To create CallableStatement object, the following statements are used

CallableStatement cstmt = null;
cstmt = conn.prepareCall ("{ call ItemSalesBy(?,?)}"); // Callablestatement object to call the stored procedure

           where ItemSalesBy is the SP name and two question mark (?,?) is used to pass input parametters. In our example, salesman_id , sales_date. The following java program drops the existing SP named ItemSalesBy and creates the same SP and executes with the given input parameters. The statement

              ResultSet rs = cstmt.executeQuery();       returns the result set returned by the SP.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class CSPRS2 {
    static Connection conn; 
    public static void main(String[] args) {
     
       try {
                    //Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

                      Class.forName("com.ibm.db2.jcc.DB2Driver");

} catch (ClassNotFoundException e) 

                  {
                     e.printStackTrace();

                     return;

                   }

       try
        {
               conn = DriverManager.getConnection("jdbc:db2:test");
            }
        catch( Exception e )
        {
            e.printStackTrace();
            System.exit(1);
        }


  //  dropSP(); // to drop procedure if already exists  

    createSP();  // to create stored procedure (SP)


    callSP("101", "2012-02-25"); // to execute SP
    }


   private static void dropSP() {
        String str=null;
        Statement stmt = null;
        try 
        {
            stmt = conn.createStatement();
           str="drop procedure ItemSalesBy";
            stmt.executeUpdate (str);
        } 
        catch (SQLException e1) 
        { e1.printStackTrace();}
       }

 

  private static void createSP() {
        String strSP=null;
        Statement stmt = null;

   strSP="CREATE PROCEDURE  ItemSalesBy (salesmanid  varchar(5), dateofsale date)  SPECIFIC sp10    DYNAMIC RESULT SETS 1 \n" +
             "P1: BEGIN \n" +
             "DECLARE cursor1 CURSOR WITH RETURN FOR   select salesman_id, (select c.name from salesman c  where c.salesman_id=b.salesman_id) SalesMan,  (select a.item_name from item_master a where a.item_code=b.item_code) ItemName, sales_date, sales_amt from salesmantxn b  where  b.salesman_id=salesmanid and b.sales_date=dateofsale; \n" +
             "OPEN cursor1; \n" +
              "END P1 \n";
   

     try
        {
            stmt = conn.createStatement();
            stmt.executeUpdate (strSP);
            System.out.println("Stored Procedure created successfully\n");
        }
        catch (SQLException e)
        {
            System.out.println("Error in creating SP: " + e.toString());
            System.exit(1);
        }  
    }


    private static void callSP(String sid, String sdate) 
    {
        CallableStatement cstmt = null;
        try 
        {
            cstmt = conn.prepareCall ("{ call ItemSalesBy(?,?)}");  // CallableStatement object  to call the stored procedure 
            cstmt.setString(1,sid);
            cstmt.setDate(2, java.sql.Date.valueOf(sdate));
            ResultSet rs = cstmt.executeQuery();
            System.out.println("SALESMAN_ID SALESMAN              ITEMNAME      SALES_DATE    SALES_AMT\n");
            System.out.println("----------- ---------------- ------------------- ----------  ------------\n");
         

   if (rs != null) {        

          while (rs.next())
           {
             System.out.println(rs.getInt(1) + "             " + rs.getString(2) + "       " + rs.getString(3) + "      " + rs.getString(4) +"       "+rs.getString(5));
            }
           }
        } 
        catch (SQLException e) 
        {
            e.printStackTrace();
        }
    }
}

Output :

Stored Procedure II: To calculate wages for a salesman on a particular date based on sales_amt. wages is calculated with the following formula wages = total_sales_amt * 0.5 /100 + bonus ; and Rs. 100 bonus is added if the sales_amt &gt;10000 and sales_amt<=200000, Rs. 200 bonus if sales_amt>200000 and sales_amt<=300000 , Rs. 300/- bonus if sales_amt>300000 IN Parameter : salesman_id, Sales_date OUT parameter : wages Stored Procedure for the above problem is given below

    
CREATE PROCEDURE wagesCalc(IN salesmanid varchar(5),IN SalesDate date , OUT  wages  double)   LANGUAGE SQL
  BEGIN
     DECLARE bonus double;
     DECLARE sumsales double;
     DECLARE wages_temp double;
     DECLARE cursor1 CURSOR FOR SELECT SUM(SALES_AMT),  SUM(SALES_AMT)*0.5/100 FROM SALESMANTXN where SALESMAN_ID=salesmanid  and SALES_DATE=SalesDate;
      SET bonus= 0;
     OPEN cursor1;
     FETCH FROM cursor1 INTO sumsales, wages_temp;
    
  IF (sumsales>300000) THEN
     set bonus=300;
  ELSEIF (sumsales&gt;200000 and sumsales<=300000) THEN
     set bonus=300;
  ELSEIF (sumsales&gt;100000 and sumsales<=200000) THEN
    set bonus=100;
  END IF;
     CLOSE cursor1;
 SET wages = wages_temp+ bonus; 
 END%  

               to create above SP using CLP , store above stored procedure in a file . eg. wages.sql . Now run the following commands db2 connect to test db2 -td% -vf wages.sql to call the above SP through CLP db2 call wagesCalc('101', '2012-02-25', ?), which returns wages for the saleman_id=101 and sales_date='2012-02-25' Now let us see how to call (execute) the above stored procedure using java program

     private static void callSP(String sid, String sdate) 
        {
          Double wages=0.0;
          CallableStatement cstmt = null;
        try 
        {
            cstmt = conn.prepareCall ("{ call wagesCalc(?,?,?)}");  // CallableStatement object  to call the stored procedure 
            cstmt.setString(1,sid);
            cstmt.setDate(2, java.sql.Date.valueOf(sdate));
            cstmt.registerOutParameter(3, Types.DOUBLE);
            cstmt.execute();
            wages  = cstmt.getDouble(3);
               System.out.println("Sales Man Id = " + sid  + " Wages= " + wages);
        } 
        catch (SQLException e) 
        {
            e.printStackTrace();
        }
    } 

         
   Output of the above program : 

D:\as2\JF5>java CSPRS4 Sales Man Id = 101 Wages= 650.0

          In the above program, registerOutParameter(3, Types.DOUBLE); Registers the OUT parameter in ordinal position parameterIndex to the JDBC type sqlType The execute() returns boolean value. if it returns false means, first result is an update count or there is no result; true means, the first result is a ResultSet object

Read More

Solution for DB2 transaction log for the database is full. SQLSTATE=57011 .

Before go to the solution, Please go through the post to understand the LOGFILSIZ parameter and how to change the log file size. In DB2 ,Some times you may get the following error,when any transaction like Insert, Update or Delete occurs on a database.

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0964C The
transaction log for the database is full. SQLSTATE=57011.

The above error may be caused due to the database log file is not large enough for the application or due to the transaction to delete / update rows in the table is overfilling the transaction logs. This error can be solved by increasing of LOGFILSIZ, LOGPRIMARY, and LOGSECOND .

To get the DB2 log file configuration, from a DB2 command prompt, run the following command

db2 get db cfg for database name

The output related to log file is displayed as follows .

Log file size (4KB) (LOGFILSIZ) = 1000
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2

In the above configuration , number of Primary Logs are 03 and Number of Secondary Logs are 02 .

Steps to increase number of Primary Logs / Secondary Logs

1. Stop all the DB2 UDB applications. The following command is used to stop all the applications

db2 force applications all

2) Increase LOGPRIMARY, LOGSECOND as per your requrement, if need, increase size of  LOGFILSIZ. The fllowing commands are used to update the logprimary & logsecond.

db2 update db cfg for dbname using logprimary N



db2 update db cfg for dbname using logsecond N



db2 update database config for db2name using LOGFILSIZ filesize

          -where N is greater than what is currently defined

eg.
  db2 update database config for ldapdb2 using LOGFILSIZ 5000

db2 update database config for ldapdb2 using LOGPRIMARY 5

db2 update database config for ldapdb2 using LOGSECOND 10
       

3) Restart DB2 using db2stop and db2start.

Now the problem is  solved..

Read More

Create Database in DB2 with Unicode (UTF-8) support - Command

By default, in DB2, databases are created in the code page of the application which creates them. In windows, Simply create a database with the following command

db2 create db employee
            - where employee is the database name.

By default employee database is created with the code page 1252 where as code page 1208 will have utf-8 support. You can see the codeset of a database by giving the following command.

In Windows :  db2 get db cfg for employee | find "code"
In Linux :   db2 get db cfg for employee | grep -i  code

Database code page = 1252
Database code set = IBM-1252

 You can also run the following query to get the code-page.

db2 connect to dbname

db2 "Select codepage from syscat.datatypes where typename = 'VARCHAR'"

which returns 1252


We can create the database with unicode (UTF-8) support by the following ways

1) We can create our database from a Unicode (UTF-8) client (for eg. the UNIVERSAL locale of AIX or set DB2CODEPAGE registry variable on the client to 1208)

2) We can explicitly specify CODESET as "UTF-8" with valid TERRITORY code supported by DB2 Universal Database.

To create a Unicode database named "emp_utf" with the territory code for the US, give the following command.

db2 create db emp_utf using codeset utf-8 territory us collate using system

Now again check the code page with the command

db2 get db cfg for emputf |  | find "code" 

which will give following output

Database code page = 1208
Database code set = utf-8

Note : When a Unicode database is created, CHAR, VARCHAR, LONG VARCHAR, and CLOB data are stored in UTF-8 form, and GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, and DBCLOB data are stored in UCS-2 big-endian form.

Read More

Db2 Jars and Class Path set to connect db2 database using java code with JDBC driver.

If your are using DB2 database in your java application, it is always required to write code for connecting DB2 database. Db2 Database can be connected by either using connection pool or  using JDBC. The following example java code is for connecting and accessing DB2 database using JDBC. It loads the DB2 JDBC Driver using Class.forName  and connects to the DB2 database Employee and prints the records of the table Department. For running the program you have to do the following. Assume that Employee database, qual table already been created in DB2.

1. Download DB2 JDBC driver for your db2 version from http://www-01.ibm.com/support/docview.wss?uid=swg21363866.

2. Include db2 driver path in the CLASSPATH

Steps to include to db2 drivers in the class path:

Copy the below db2 driver files  to any folder for example d:\jars\db2drivers.
 1. db2jcc.jar
 2. db2jcc_license_cisuz.jar
 3. db2jcc_license_cu.jar
4,  db2java.zip

              Now set the classpath to include the driver files as follows.

d:\db2test>set CLASSPATH=.;d:\jars\db2drivers\db2jcc.jar;d:\jars\db2drivers\db2jcc_license_cisuz.jar;d:\jars\db2drivers\db2jcc_license_cu.jar;d:\jars\db2drivers\db2java.zip;.

OR permanantly set by Right click on MyComputer ->Advanced Tab - > Environment variables - > select CLASSPATH and edit , set the variable value to above path.

 For connecting db2 in Servlet / DAO in struts, include the above driver files in the libraries (WebContent\WEB-INF\lib). If you use any development tool (IDE) like WSAD or RAD or Myeclipe, then you can include drivers for your application by accessing
                      Project->Properties->  Add External Jar from Project -> Java Build Path -> Libraries Tab -> Add External Jars in the IDE. 

package com.javaonline;

 import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

 public class Db2ConnectJava {
  public static void main(String[] argv) {
  try {
   
 // Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
  Class.forName("com.ibm.db2.jcc.DB2Driver");
  
  } 
  catch (ClassNotFoundException e) 
  {
   System.out.println("Class not found. Please include the right db2 jars in the Classpath");
   e.printStackTrace();
   return;
  }
  
  System.out.println("Great. DB2 driver is loaded successfully");
  
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rs=null;
  try {
   conn = DriverManager.getConnection("jdbc:db2:employee");
   
  if (conn != null)  System.out.println("Connection Sucessful");
  else System.out.println("Connection Failed ");
  
  pstmt=conn.prepareStatement("Select * from EmpList");
  rs=pstmt.executeQuery();
  
  System.out.println("S.No          Emp.Code                        Name                     Pay Scale ");
  System.out.println("----------------------------------------------------------------------------------");
  
  int sno=0;
  if(rs!=null)
  {
   while(rs.next())
   {
    sno++;
    System.out.println(sno + ".           "+rs.getString("empcode") +"                          "+ rs.getString("name") +"               "+ rs.getString("payscale"));
       }
  }
  
  if (sno<=0) 
  {
   System.out.println("No Employees Found");
  }
  
  } catch (SQLException e) {
   System.out.println("Some Error Occurs...");
   e.printStackTrace();
   return;
  }
  }
  }
 

Running the above program, you will get the below output.

Read More

How to read images from BLOB field in db2 database table using java code.

     Tn this tutorial, we are going to see how to read Blob images stored in a db2 database table using java code. Let us assume that large objects like images or audio file (eg. Employee photos or Mp3 file) are stored in a db2 database table using BLOB fields. For storing images in db2 table, you can visit Store Images in BLOB. The images or audio files are stored as bytes in DB2. Db2 table stores only descriptor (locator or pointer) which points the external location where the actual bytes are stored. 

         JDBC 2.0 provides methods like getBytes , getBlob to read Binary Data or BLOB data from the table. To get the actual images (jpeg , jpg, gif,..) or audio files (mp3,mp4, ..) , we have to read the bytes from the database and write into a external file. Now let us see how the following code works?.

        In our example Employee table has the fields empid, empname, empphoto,filepath where as empphoto is the blob field which is used to store employee photos. filepath contains the external file path including file name. The bytes read from the blob field is written into the external file in the location using filepath. To read the bytes from the blob field, we can either use

           byte[] image = rs.getBytes("empphoto") 
                     OR
           Blob blob_temp =rs.getBlob("empphoto"),
      byte[]image=blob_temp.getBytes(1,(int)blob_temp.length()).

             Static method writeToFile (byte[] image, String fileName) accepts image bytes and fileName. Bytes are written to file using the write method of FileOutputStream. Now the employee photo is ready in the location given in the filePath field.

package com.javaonline;
import java.sql.*;
import java.io.*;

public class Db2BlobRead {

  public static void main(String[] args)    throws SQLException
  {
      try {
     
         //Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
      Class.forName("com.ibm.db2.jcc.DB2Driver");
       
     } catch (Exception e) {
System.out.println(e);
                System.exit(1);
                     }
     Connection con = DriverManager.getConnection("jdbc:db2:test");  
     Statement stmt=null;
     ResultSet rs = null;
try {
    stmt = con.createStatement();
    rs = stmt.executeQuery("select empid , empname, empphoto, filepath from Employee");
while (rs.next())
{  
    if ((rs != null) ) {
 String empcode =rs.getString("empid");
 String empname =rs.getString("empname");
// Blob blob_temp =rs.getBlob("empphoto");
//  byte[] image=blob_temp.getBytes(1,(int)blob_temp.length());
         byte[] image = rs.getBytes("empphoto");
         String fileName = rs.getString("filepath");
         writeToFile(image, fileName);

System.out.println("Employee Code => " + empcode);
System.out.println("Employee Name => " + empname);
System.out.println("Employee Photo read and stored at  : " + fileName);
System.out.println();
                       }
}
     } catch (SQLException e)
                {
              e.printStackTrace();
                } finally {
                     try {
                       if (rs != null) rs.close();
                         if (stmt != null) stmt.close();
                         } catch (SQLException e)
                            {
                             e.printStackTrace();
                              }
                        }
}


// to write bytes into physical file.
private static void writeToFile(byte[] image, String fileName) {
    FileOutputStream file = null;
    try {
         file = new FileOutputStream(fileName);
         file.write(image);
         } catch (FileNotFoundException e) {
               e.printStackTrace();
              } catch (IOException e) {
              e.printStackTrace();
          } finally {
                try {
                   if (file != null) file.close();
                     } catch (IOException e)
                       {
                       e.printStackTrace();
                        }
                  }
            }
}

Output of above program :

Read More

Execute query using PreparedStatement in DB2 with example code in java

       Some times you may need to execute the same SQL statement many times with different parameters (input values). In this situation the best option is to use a PreparedStatement object. PreparedStatement in java is an interface that define the methods and properties that enable you to send SQL statements and receive data from database server. JDBC Drivers developed by database vendors provide classes that implement the PreparedStatement interface. Without a driver you cannot create objects on this interface. 

      As PreparedStatement interface extends Statement, methods of Statement objects such as execute(), executeQuery(), executeUpdate(), and executeBatch() work with the PreparedStatement object. But these methods are implemented to take input the parameters also. To bind values to input (IN) parameters, you can use the setXXX() methods where XXX represents the Java data type of the value. setXXX is used to convert the Java data type to the appropriate SQL data type for your target database. 

Use of the above methods are as follows

1. executeUpdate() is used to execute DML Statements like INSERT, UPDATE, DELETE, or DDL SQL statements

2. excuteQuery() is used to execute SELECT statements to query database.

3. excute() is used to process any DDL or DML statements

4. executeBatch() is used execute group of SQL statements (DDL, DML statements)

The following code snippet describes how the methods executeUpdate(), setInt(), setString() are used with PreparedStatement object. 

 
 String strSql  = "UPDATE student  SET MobileNo = ?  WHERE studentNo = ?"; 
       PreparedStatement pstmt = conn.prepareStatement(strSql);
       pstmt.setString(1,"9876543579");
       pstmt.setInt(2,5321);
       pstmt.executeUpdate(); 

PreparedStatement object has advantages over a generic Statement object.

1. Quicker than the generic Statement object because the SQL statement is precompiled and stored in a PreparedStatement object.  It can improve performance of frequently used SQL statements because this compiled statement is in memory and remains ready to use during different iterations.

2. Gives flexibility to supply input parameters at runtime

3. PreparedStatement object can also be used for statements that take no parameters.

4. PreparedStatement helps in defending against SQL Injection

Let us see an example of using PreparedStatement object, suppose you have a STUDENT table , which is having Student No, Student Name, Class, Address1, Address2 , City , State , Mobile No . Now you have the list of classes, suppose if you want to print the student details of MCA, MBA and B.Com . The following example shows how to use a PreparedStatement object to execute a SELECT statement for the above said example which has one input parameter: Note : either you can use classname or classcode as a parameter . Always better to use classcode.

public void loadStudentDetails() throws CDRCException, CDRCFatalException {
   String strQuery;
   Connection conn = null;
   ResultSet rs = null;
   PreparedStatement pstmt = null;
   ArrayList className = new ArrayList();

   try {
    conn = ConnectionFactory.getConnection();
    className.add("MCA");
    className.add("MBA");
    className.add("B.Com");
   
    strQuery = "select StudentNo, StudentName , Address1, Address2, City , State , MobileNo from student where className=?   ";

    pstmt = conn.prepareStatement(strQuery);  
    
    Iterator it=className.iterator();
    while (it.hasNext())
    {
     String clname=it.next().toString();
    pstmt.setString(1,clname);
    rs = pstmt.executeQuery();

      System.out.println("Class Name :" + clname ); 
    while(rs.next())
    {
     System.out.println(rs.getInt("StudentNo"));
     System.out.println(rs.getString("StudentName"));
     System.out.println(rs.getString("Address1"));
     System.out.println(rs.getString("City"));
     System.out.println(rs.getString("MobileNo"));
    }
     
    }

   } catch (SQLException e) {
    System.out.println( "Error :" + e.toString());
    
   } finally {
    try {
   if (rs != null) {
      rs.close();
      rs = null;
     }
     if (pstmt != null) {
      pstmt.close();
      pstmt = null;
     }
  
     if (conn != null) {
      conn.close();
      conn = null;
     }
    } catch (SQLException e) {
     System.out.println( "Error :"  + e.toString());
    }
       }
   } 

In the above example, same query is used three times with different input values (parameters). When you use a PreparedStatement object to execute an SQL statement, Don't forget to use the PreparedStatement.close() method to explicitly close the PreparedStatement object when you finish processing the results. This frees (deallocates) the resources that were allocated to the SQL statement.

For more methods on PreparaedStatement, you can visit  http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html

Read More