Executing DB2 stored procedure using Java JDBC

   
Accesing and executing DB2 stored procedure using Java JDBC
Example : Inserting data into COUNTRY table:
Table Schema:

COUNTRY
----------------------------
COUNTRY_CODE    VARCHAR(20)
COUNTRY_NAME    VARCHAR(30)


1. Crating a db2 stored procedure for inserting data in COUNTRY table:

CREATE PROCEDURE INSERT_CITY_MAS
(IN countryCode VARCHAR(20), IN countryName VARCHAR(30))
LANGUAGE SQL
BEGIN
    insert into COUNTRY values(countryCode, countryName);
END


This procedure takes to params country name and country code as input
and inserts it into COUNTRY table.

2. Accessing the Stored procedure from Java class:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.*;
public class TestSQLProcedure
{
    public static void main(String[] args)
    {
        if (args.length < 1)  {
            System.out.println("Usage: ");
        }
        try
        {
            // load the DB2 Driver
            Class.forName("com.ibm.db2.jcc.DB2Driver");
            // establish a connection to DB2
            Connection db2Conn = DriverManager.getConnection("jdbc:db2://HOSTNAME:PORTNO/DB_NAME","DB_USER_NAME","PASSWORD");

            // use a statement to gather data from the database
            CallableStatement cs = db2Conn.prepareCall("{call INSERT_CITY_MAS(?, ?)}");
            // Set the value for the IN parameter
            cs.setString(1, args[0]);
            cs.setString(2, args[1]);
            // Execute the stored procedure
            cs.execute();
        cs.close();
            db2Conn.close();

        } catch (ClassNotFoundException cnfe) {
            cnfe.printStackTrace();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}


3. Compile Above class and Add db2jcc.jar file in $CLASSPATH (use [ $export $CLASSPATH="$CLASSPATH:db2jcc.jar" ] command)

4. Execute above class it will insert the argument provided at command line to database using above stored procedure.