DB2 : Creating a global temporary table using stored procedure

Global Temporary Table :

The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current session. The declared temporary table description does not appear in the system catalog. It is not persistent and cannot be shared with other sessions. Each session that defines a declared global temporary table of the same name has its own unique description of the temporary table. When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped.

Example :
DECLARE GLOBAL TEMPORARY TABLE SESSION.USER_TEMP_TABLE
(
    userid varchar(40),
    password varchar(40)
 ) ON COMMIT DELETE ROWS NOT LOGGED WITH REPLACE;


Description :

ON COMMIT
Specifies the action taken on the global temporary table when a COMMIT operation is performed.

DELETE ROWS
All rows of the table will be deleted if no WITH HOLD cursor is open on the table. This is the default.

NOT LOGGED
Specifies that insert, update, or delete operations against the table are not to be logged, but that the creation or dropping of the table is to be logged.  During a ROLLBACK (or ROLLBACK TO SAVEPOINT) operation:
* If the table had been created within a unit of work (or savepoint), the table is dropped
* If the table had been dropped within a unit of work (or savepoint), the table is recreated, but without any data

WITH REPLACE
    Indicates that, in the case that a declared global temporary table already exists with the specified name, the existing table is replaced with the temporary table defined by this statement (and all rows of the existing table are deleted).


Sample Stored procedure:

Suppose we are selecting rows from user table and storing them in global temporary table.
Then fetching all rows from global temporary table and returning them as cursor.

USERTABLE
---------------------------
USERNAME    VARCHAR(40)
PASSWORD    VARCHAR(40)
FULLNAME    VARCHAR(100)
DOB            DATE
----------------------------

Writing a stored procedure

CREATE OR REPLACE PROCEDURE USER_LIST(
            DOB DATE
        )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN

    DECLARE GLOBAL TEMPORARY TABLE SESSION.USER_TEMP
        (
            USERNAME VARCHAR(40)
        ) ON COMMIT DELETE ROWS NOT LOGGED WITH REPLACE;

    IF (DOB IS NULL) THEN
        SET DOB = current date;
    END IF;

    INSERT INTO SESSION.USER_TEMP (USERNAME) (
            SELECT USERNAME FROM USER_LIST WHERE DOB = DOB
        );

BEGIN
     DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
             SELECT * FROM SESSION.USER_TEMP;

    open  cursor1;

END;
END@


save file as user.sql and run using
$ db2 -td@ -f user.sql

running the stored procedure:
$db2 "call user_list('1983/01/11')"

DB2 commands cheat book

Some DB2 commands in brief : 

1. Connecting to database 

    $ db2 'CONNECT TO [DATABASE NAME]'

2. Disconnect from database

    $ db2 'terminate'

3. Creating Schema 

    $ CREATE SCHEMA [SCHEMA_NAME] AUTHORIZATION [AUTHORIZATION NAME]

4. Creating table:

    $ db2 " CREATE TABLE  USERS (

              userid varchar(30) NOT NULL, 

              password varchar(40) NOT NULL,

              insertedat TIMESTAMP DEFAULT CURRENT TIMESTAMP,

              primary key(userid)

        ) IN USERSPACE 1 "

Here : 

VARCHAR is datatype of the column. primay key defines the primary key for the table. DEFAULT specifies the default value when nothing is specified while inserting row in table.

5.  Inserting values in table:

     $ db2 "insert in to users(userid, password) values ('pgadekar', 'mypassword')"

     Here the above value will be inserted as row in table with values for column userid and password 

and for INSERTEDAT will be assigned the default value i.e. current time stamp.

6. Select rows from table : 

    $ db2 'SELECT * FROM USERS'

7. Delete rows from table : 

   $db2 "delete from users where userid = 'pgadekar'"

8. Update rows in table 

   $db2 "update users set password = 'newpass' where userid='pgadekar'"

9. Inserting data from CSV file 

    assume content of csv file are as : 

     users.csv

    ---------------x------------x---------------

    'pgadekar','mypassword'

    'vijay','hispassword'

    ---------------x------------x---------------

    

    $ db2 'import from users.csv of del modified by coldel | commitcount 100  insert in to users (username, password)'

    This will upload the csv file data in to table users. Commit operation will be performed after every 100 rows. 

10. Running sql file : 

      sample sql  file content:

      schema.sql

    ---------------------------------x---------------------------x------------------------

     CREATE TABLE  USERS (

              userid varchar(30) NOT NULL, 

              password varchar(40) NOT NULL,

              insertedat TIMESTAMP DEFAULT CURRENT TIMESTAMP,

              primary key(userid)

        ) IN USERSPACE 1@

    ---------------------------------x---------------------------x------------------------

    $db2 -td@ -f schema.sql 

    here @ specifies the deliminator. You can use any.


11.  Dropping table: 
      $db2 'DROP TABLE [TABLE_NAME]'


12.  Getting table schema: 
      $db2 "DESCRIBE TABLE [TABLE_NAME]"

Getting select query result from DB2 database using perl script

First Install following modules

1. DBI
2. DBD (DB2 Database module)

Use following script to connect to database and fetch the rows from required table.


#!/usr/bin/perl
use strict;
use DBI;

# Open a connection
my $dbh = DBI->connect('dbi:DB2:DATABASE_NAME',
        "DB_USERNAME", "DB_PASSWORD", {RaiseError => 1}) or 
        die "can not connect to database" . $dbh->errstr;

my $stmt = "SELECT * FROM TABLE_NAME";

# Prepare query
my $sth = $dbh->prepare($stmt) or die "Can not prepare statement" . $dbh->errstr;

# Execute Query
my $x = $sth->execute() or  die "Cannot execute: " . $sth->errstr;


# Fetch result
while (my $hash = $sth->selectrow_hashref) {
    print $hash;
}


selectrow_hashref will contain the columname => value pairs for each row.

Writing DB2 stored procedure

Writing a db2 stored procedure.

1. Creating a empty body procedure

    CREATE [OR REPLACE] PROCEDURE schemaname.proc_name(
        [IN|OUT] variable1 [datatype(char|varchar|integer|decimal|date|timestamp..)] ,
        ....
    )

    [DYNAMIC RESULT SETS 1] if you dont specify then DYNAMIC RESULT SETS is set to 0 meaning returns all resultset's ... however giving some no. will restrict it to that upper limit throwing SQL0464W
    LANGAUGE SQL
    BEGIN
           
             -- Procedure Body goes here --
      
    END;


    Sample DB2 stored procedure:
    CREATE OR REPLACE PROCEDURE SCHEMA1.EMPLOYEE_SUMMARY(
                                    IN EMP_ID VARCHAR(40)
                                )
    DYNAMIC RESULT SETS 1
    LANGAUGE SQL
    BEGIN

    END;


    Calling Stored procedure :
    call SCHEMA1.EMPLOYEE_SUMMARY('E_123_0001');


2. Inserting From stored procedure:
    CREATE OR REPLACE PROCEDURE SCHEMA1.CREATE_EMPLOYEE(  IN i_emp_id VARCHAR(40),
                                  IN i_emp_dob DATE,
                                  IN i_emp_name VARCHAR(100))
    LANGAUGE SQL
    BEGIN

        /* All the variable declration should be done at start */
        declare rcount integer DEFAULT 0;

        /* Insert the employee record */
        INSERT INTO EMPLOYEE(EMP_ID, EMP_DOB, EMP_NAME) VALUES(i_emp_id, i_emp_dob, i_emp_name);

        /* Get number of records inserted in to database */
        GET DIAGNOSTICS rcount = ROW_COUNT;
      
        /* Check last insert was successfull */
        if ( rcount <> 0) then
            return 1
        else
            return 0
        end if;
    END;


3. Getting result set from stored procedure
    CREATE OR REPLACE PROCEDURE SCHEMA1.SEARCH_EMPLOYEE(IN i_emp_name varchar(200))
    LANGAUGE SQL
    BEGIN

    /* DECLARE Cursor for the employee records */
    DECLARE result_set_1 CURSOR WITH RETURN TO CLIENT FOR
     SELECT
        *
    FROM
        EMPLOYEE
    WHERE
        EMP_NAME LIKE '%' || i_emp_name || '%';
    ORDER BY
        EMP_NAME ASC;
  
    /* Check if the search key is empty search all employee */
    if (i_emp_name is NULL || i_emp_name == '') then
    set i_emp_name = '*';
    end if;

    /* open the result set to be get fetched by the calling programm */
    open result_set_1;

    END;


4. Getting average percentage of the total salary for the employee.
    How much percetnage employee is costing to company.
    CREATE OR REPLACE PROCEDURE SCHEMA1.SALARY_SUMMARY()
    LANGAUGE SQL
    BEGIN

    /* DECLARE Cursor for the employee records */
    DECLARE result_set_1 CURSOR WITH RETURN TO CLIENT FOR
     SELECT
        EMP_NAME,
        SALARY / SUM(EMP_SALARY)  * 100 AS PERCENTAGE_SALARY
    FROM
        EMPLOYEE
  
    /* open the result set to be get fetched by the calling programm */
    open result_set_1;

    END;


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.

DB2 Connection Pooling in tomcat 6

The Context element represents a web application, which is run within a particular virtual host. Each web application is based on a Web Application Archive (WAR) file, or a corresponding directory containing the corresponding unpacked contents.


The web application used to process each HTTP request is selected by Catalina based on matching the longest possible prefix of the Request URI against the context path of each defined Context. Once selected, that Context will select an appropriate servlet to process the incoming request, according to the servlet mappings defined in the web application deployment descriptor file (which MUST be located at /WEB-INF/web.xml within the web app's directory hierarchy).


You may define as many Context elements as you wish. Each such Context MUST have a unique context path. In addition, a Context must be present with a context path equal to a zero-length string. This Context becomes the default web application for this virtual host, and is used to process all requests that do not match any other Context's context path.


Sample context.xml ($CONTEXT_PATH/MET-INF/context.xml):


<Context path="/CONTEXT_PATH" docBase="DOC_BASE" crossContext="true" reloadable="true" debug="1">
   <Resource name="jdbc/RESOURCE_NAME" auth="Container" type="javax.sql.DataSource" driverClassName="com.ibm.db2.jcc.DB2Driver" url="jdbc:db2://HOST_NAME:PORT_NUMBER/DATABASE_NAME" username="USER_NAME" password="PASSWORD" maxActive="10" maxIdle="5" maxWait="100000" removeAbandoned="true">
         <parameter>
         <name>factory</name>
         <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
      </parameter>
   </Resource>
</Context>


Note:

CONTEXT_PATH - The context path of this web application, which is matched against the beginning of each request URI to select the appropriate web application for processing. All of the context paths within a particular Host must be unique. If you specify a context path of an empty string (""), you are defining the default web application for this Host, which will process all requests not assigned to other Contexts. The value of this field must not be set except when statically defining a Context in server.xml, as it will be inferred from the filenames used for either the .xml context file or the docBase.

DOC_BASE - The Document Base (also known as the Context Root) directory for this web application, or the pathname to the web application archive file (if this web application is being executed directly from the WAR file). You may specify an absolute pathname for this directory or WAR file, or a pathname that is relative to the appBase directory of the owning Host. The value of this field must not be set when the Context is configured using a META-INF/context.xml file as it will be inferred by the automatic deployment process. If a symbolic link is used for docBase then changes to the symbolic link will only be effective after a Tomcat restart or by undeploying and redeploying the context. A context reload is not sufficient.

RESOUCE_NAME - Name of the resource (JNDI name)

HOST_NAME - Host name where the DB2 database is running

PORT_NUMBER - Post number on which DB2 instance is running

DATABASE_NAME - Name of database where the table is located

USER_NAME - Dabatabase user name

PASSWORD - Database user password

Other Parameter Specification:
maxActive - Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to 0 for no limit.

maxIdle - Maximum number of idle dB connections to retain in pool. Set to -1 for no limit. See also the DBCP documentation on this and the minEvictableIdleTimeMillis configuration parameter.

maxWait - Maximum time to wait for a dB connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely.

driverClassName - JDBC Class name for Connector/J.

url - The JDBC connection url for connecting to your dB. The autoReconnect=true argument to the url makes sure that the JDBC Driver will automatically reconnect
if db closed the connection.



Put dc2jcc.jar in $CATALINA_HOME/lib directory

Getting Database connection in HttpServlet sample code:



import java.io.*;
import java.sql.*;
import javax.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.naming.*;

public class TestDBCP extends HttpServlet
{
     public void doPost(HttpServletRequest request, HttpServletResponse response)
     throws ServletException,IOException     {

          PrintWriter out = response.getWriter();
          Connection con = null;
          PreparedStatement pstmt = null;
          ResultSet rs = null;
          String username = "TestUSER";
          String password = "TESTPWD";

          try {
              InitialContext context = new InitialContext();

              // Look up the data source
              DataSource dataSource =
              (javax.sql.DataSource)context.lookup("java:comp/env/jdbc/RESOURCE_NAME");


              // Get a connection from the pool
              con = dataSource.getConnection();

              String query = "select fullname from user where username = ? and password = ? ";
              // ...Use the connection...
              pstmt = con.prepareStatement(query);
              pstmt.setString(1, userName);
              pstmt.setString(2, password);
              rs = pstmt.executeQuery();
              String result = null;

              if (rs.next()) {
                  String result = rs.getString("fullname");              }

              if (result != null) {
                  out.println("Welcome : " + result);

              } else {

                  out.println("Not a valid user, please try again");

              }
           } catch (Exception e) {
              out.println("ERROR : " + e.getMessage());

           } finally {
              try {
                  if (rs != null) rs.close();
                  if (pstmt != null) pstmt.close();

               } catch (Exception e) {

               } finally {
                  try {

                   if (con != null) con.close();

                  } catch (e) { }

                }
           }
      }
}


Accessing DB2 database using JDBC

1. Accessing DB2 database using Java:

Sample code:

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

public class TestSQL
{
    public static void main(String[] args)
    {
         try {           
             // load the DB2 Driver
            Class.forName("com.ibm.db2.jcc.DB2Driver");            // establish a connection to DB2
            Connection db2Conn = DriverManager.getConnection("jdbc:db2://HOST_NAME:PORT_NUMBER/DATABASE_NAME","USER_NAME","USER_PASSWORD");
            // use a statement to gather data from the database
            Statement st = db2Conn.createStatement();
            String myQuery = "SELECT * FROM COUNTRY";
            // execute the query
             ResultSet resultSet = st.executeQuery(myQuery);
            // cycle through the resulSet and display what was grabbed
           System.out.println("COUNTRY_CODE\tCOUNTRY_NAME");
            while (resultSet.next())            {
                String id = resultSet.getString("COUNTRY_CODE");
                String name = resultSet.getString("COUNTRY_NAME");
                System.out.println("-------------------------------");
                System.out.println(id + "\t" + name);
           }
            // clean up resources
            resultSet.close();
            st.close();
            db2Conn.close();
        } catch (ClassNotFoundException cnfe) {
            cnfe.printStackTrace();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } 
    }
}


Note:
HOST_NAME: specifies the hostname or ipaddress if the system on which DB2 database is running. 
PORT_NUMBER: Port number on which db2 instance is running.
DATABASE_NAME: Name of the database in which tables resides.


2. Change above params in Sample code, compile java file.

3. Add db2jcc.jar in classpath
    $export CLASSPATH="$CLASSPATH:db2jcc.jar"

4. Run the Sample code

Starting DB2 instance to run on TCP port

To run DB2 listen on Port (UNIX) follow the following steps:

There are 2 types of database user for DB2:
1. DB2 database user (DB user)
2. DB2 database instance user(DB_INSTANCE user).

By using root user check if db2 instance is running or not:
# netstat -lnp | grep

Here port number may be 50001 or 50000

If it is allready running skip it or using db user run the following commands:
$db2stop
$db2set DB2COMM=TCPIP
$db2 update dbm cfg using SVCENAME

Run the following command from DB_INSTANCE user
$db2admin start

Now check from root user if the db2sys is started on PORTNUMBER or not use following command:
# netstat -lnp | grep

The output may looks like:
tcp 0 0 0.0.0.0:50001 0.0.0.0:* LISTEN 2278/db2sysc