Difference between Application Server and Web Server

Application Server vs Web Server

1. Application Server supports distributed transaction and EJB. While Web Server only supports Servlets and JSP.

2. Application Server can contain web server in them. most of App server e.g. JBoss or WAS has Servlet and JSP container.

3. Though its not limited to Application Server but they used to provide services like Connection pooling, Transaction management, messaging, clustering, load balancing and persistence. Now Apache tomcat also provides connection pooling.

4. In terms of logical difference between web server and application server. web server is supposed to provide http protocol level service while application server provides support to web service and expose business level service e.g. EJB.

5. Application server are more heavy than web server in terms of resource utilization.

Use Concurrent HashMap instead of hashtable & synchronizedMap | JDK 1.5 new features Concurrent HashMap

Use Concurrent HashMap instead of hashtable & synchronizedMap
Some of the drawbacks of  Synchronized collection such as  HashTable , Collections.synchronizedMap are as follows .

  Synchronized collection classes such as  Hashtable and  the synchronized wrapper classes created by the Collections.synchronizedMap are thread safe  with poor concurrency, less performance and scalabilty  .

1. Poor concurrency : When these collections  are  accessed by two or more threads, they  achieve thread safety  by  making the collection's data private and synchronizing all public methods  so that   only  one  thread at a time can access the  collection (hashtable /  synchronizedMap )   data.  This leads to poor concurrency.  As  Single lock is used for the whole collection , multiple threads struggle for the collection wide lock which reduces the performance

2. ConcurrentModificationException :
                When one thread is traversing the hashtable / Collections.synchronizedMap through an Iterator ,  while another thread  changes it  by mutative operations (put, remove , etc) , iterator  implemented in the java.util collections classes  fails by throwing ConcurrentModificationException . The exception occurs when  the hasNext() or next() method of Iterator class is called.  The same error also occurs (See  Code Part 1 : )  , when elements are added  in hashtable or  synchronizedMap , once the  iterator is constructed.  While iterating the collection (hashtable) through iterator  , collection / table- wide locking is required ,  otherwise ConcurrentModificationException is occured .

3. Scalabilty Issues :
      Scalabilty is the major issue when we use synchronized collections .  When the workload of the application increases ,   increasing the resources like processor , memory  should also  increase the throughtput of the application.   Unfortunately , it does not happen .  A scalable program can handle   a proportionally larger workload with more resources.  As synchronized collections synchronize on a single common lock , it restricts access  to  a  single thread at a  time,  other threads are restricted to access that collections , even if the resources  are available to schedule those threads.


4.  Some of the  common sequences of operations , such as put-if-absent (to check if an element is in the collection before adding it)    or iteration ,  require external synchronization (i.e. client side locking ) (See Code Part  3 )  to avoid data races  .


Code Part 1 :



//Map hm=Collections.synchronizedMap(new HashMap());

Map hm=new Hashtable(new HashMap());

//ConcurrentHashMap hm=new ConcurrentHashMap();

hm.put(1, "Blue");

hm.put(2, "Green");

hm.put(3, "Yellow");

Iterator entries = hm.entrySet().iterator();

hm.put(4, "Red");

hm.put(5, "Orange");



while (entries.hasNext()) {

    Map.Entry entry = (Map.Entry) entries.next();

    Integer key = (Integer)entry.getKey();

    String value = (String)entry.getValue();

    System.out.println("Key = " + key + ", Value = " + value);}
To overcome the above  issues with the synchronized collections , a new version of HashMap with concurrent access   has been designed  that is ConcurrentHashMap. This class is packaged with  java.util.concurrent  in JDK 1.5)

The main purpose to create ConcurrentHashMap is to provide
 
 1. better concurrency
  2  high scalability
  3. thread safe

and it supports
   1.  full concurrency of retrievals. Allows all readers to read the table concurrently  .  No lock is used for retrival operations.

  2.   concurrency for  writes . Allows  a limited number of writers to update the table concurrently

  3. full thread safe .

ConcurrentHashMap can be used where more read operation is required  ( i.e.  traversal is the dominant  operation )

How a ConcurrentHashMap is implemented ? or How it works?  or how concurrency is achieved?

          Volatile fields  and  lock striping  plays major role for  to achieve concurrency .
Lock striping :   Synchronizing  every method  on  a  single  lock,  restricts access  to  a  single  thread at a  time.   Instead of using single lock ,  ConcurrentHashMap  uses  different  locking mechanism  called lock  striping  to access the shared collection  concurrently which increases the scalabilty and performance .     Using different locks to allow different  threads to operate  on different portions of the same data structure  called lock striping. Splitting the lock into more  than one  improves the scalability .  For example two locks allow two threads to execute concurrently instead of one.

    Lock splitting can sometimes be extended to partition locking on a variablesized set of independent objects, in which case it is called lock striping.    


              Now let see that how lock striping mechanism is applied to ConcurrentHashMap .  The  strategy is to subdivide the  collection (hashtable) into independent  subsets called segments each guarded by a lock sothat  each subset (itself a hashtable)   can be  accessed  concurrently.    It uses an array of 16 locks each of which guards 1/16 of the hash buckets.  N/16 locks are used  for a hashtable having  N hash buckets.  Hash  bucket N  is guarded by  lock N mod 16.  16 locks allow  maximum of 16 threads to modify the hashtable at same time.   Mutative operations such as put() and remove() use locks  where as read operation does not use locks .


Note : The  number  of  locks  can  be  increased


Volatile Fields :   Some of the volatile fileds declared in the ConcurrentHashMap are


transient volatile int count;
    static final class HashEntry<K,V> {
         final K key;
         final int hash;
        volatile V value;
         volatile HashEntry<K,V> next;

        HashEntry(K key, int hash, HashEntry<K,V> next, V value) {
            .....
            .....
         }
 transient volatile HashEntry<K,V>[] table;

From the source of ConcurrentHashMap


As we know , volatile field ensures visibilty i.e.  one thread reads the most up-to-date value written by another thread .  For example count is the volatile field which is used to track the number of  elements  . When one thread  adds  an element to the table , the count is increased  by one  , Similarly when one  thread  removes  an element from the table , the count is decreased  by one .  Now the other threads doing   many read operations  get the count variable's most  recent  updated value.


Similarly   HashEntry<K,V>[] table , value  , volatile HashEntry<K,V> next     fileds  are declared as volatile.  This ensures that all the threads see the the most  recent written  value of  those  fields at all times.       


When iterating the collection (hashtable) through iterator  , it does not  throw ConcurrentModificationException, but the elements  added  or removed after the iterator was constructed  may or may not be reflected . No collection / table- wide locking is required  while iterating the collection.
 Issue:   How to  protect  / lock the entire collection?  . There is no support for locking the entire table in a way that prevents all access. Then  One way is to acquire all of the locks recursively  which is costlier than using a single lock .
 ConcurrentHashMap provides three new update methods:
 putIfAbsent(key, value)  -  check if the key  is in the collection before adding the specified key  and  associate it with the given value
 replace( key, value)  - Replace the existing  key with given key  ,  only if  the key is  mapped to  given value.
 remove(key, value) - Remove the key only if the key is mapped to  given value.


The following program using ConcurrentHashMap  helps to keep the  accessed files in a cache .
 Code Part 2 :



import java.util.*;

import java.util.concurrent.ConcurrentHashMap;

import java.io.*;



public class CacheUsingMap2 {



  ConcurrentHashMap  cache;



  public CacheUsingMap2() {

    cache = new ConcurrentHashMap();

   }



  public String getFile2(String fname) {

     cache.putIfAbsent(fname,  readFile(fname));

      return ((myFile)cache.get(fname)).getFileData();

     }





  public myFile readFile(String name)

                   {

                  File file = new File(name);

                String fileData="";

                  try {

                      

              Scanner scan = new Scanner(file);

              scan.useDelimiter("\\Z");

              fileData = scan.next();



                         } catch (FileNotFoundException e){

                               System.out.println(e);



                                }

                             catch ( IOException e) {

                         System.out.println(e);

                                }



         return (new myFile( fileData));

                }



  public static void main(String args[]) {

  CacheUsingMap2 cache=new CacheUsingMap2();



 String filePath="D:/Files/";

System.out.println( cache.getFile2(filePath+"k.txt"));

System.out.println( cache.getFile2(filePath+"k1.txt"));

System.out.println( cache.getFile2(filePath+"k.txt"));

System.out.println( cache.getFile2(filePath+"k1.txt"));



  }

}



class myFile {


          String fileData;

    public myFile(String data)

          {

              fileData=data; 

          }


  public String getFileData() {

         return fileData;

     }



Code Part  3 :

Sample code to createt cache using  Hashtable  (implements put-if-absent operation) which requires client side locking




....

Hashtable  cache =new Hashtable();

....



  public String getFile(String fname) {



//   if (cache.get(fname)==null)

         if (!cache.containsKey(fname))

                 {

  synchronized(cache)

                                        {

  cache.put(fname, readFile(fname));

                                         }

                 }


                 return ((myFile)cache.get(fname)).getFileData();     } 

Write a SImple Java program to determine a integer whether it is prime

Below Simple program
Write a program that repeatedly prompts the user to enter a positive integer > 1. The program should then take this integer and determine whether it is prime. (A prime integer is evenly divisible only by itself and 1.) Then display whether the integer is prime or composite (not prime). If the user enters 0, then exit from the program. Hint: Use a for loop and the mod function

package com.prime;

import java.util.Scanner;

public class PrimeTest {
public static void main(String[] args)
{
    Scanner sc= new Scanner(System.in);
    boolean value=false;
//    while ()
    int number= 2;
   
    if(number==0)
    {
    System.exit(1);   
    }
    //System.out.println("Please Enter Number");
    while (number >0)
    {
    System.out.println("enter a positive integer > 1 : ");
    number =     sc.nextInt();
    if(number==0)
    {
        System.exit(1);   
       
    }
    value= TestPrimeNumber(number);
    if(value=true)
    {
        System.out.println("Prime Number");
    }
    else
    {
        System.out.println("Not Prime Number");
    }
   
    }
    }
private static boolean TestPrimeNumber(int n)
{
      //check if n is a multiple of 2
    if (n%2==0) return false;
    //if not, then just check the odds
    for(int i=3;i*i<=n;i+=2) {
        if(n%i==0)
            return false;
    }
    return true;


}
}



 

TLD or attribute directive in tag file, attribute value does not accept any expressions in JSP

When i run the following simple code in  eclipse with   App. Server Information : Apache Tomcat/7.0.26 , Sevlet Version : 3.0 ,  JSP Version : 2.1 and  Java Version : 1.6.0_20

  <c:forEach var="i" begin="1" end="50" step="1" >
   <c:out value="${i}"/ >
   </c:forEach >

I got the  error "According to TLD or attribute directive in tag file, attribute value does not accept any expressions" .  If you get the same error , making  the below changes in the JSP  will save your day.

 Replace the line       <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c'% >   in the JSP  with    either      

       <%@ taglib uri='http://java.sun.com/jsp/jstl/core' prefix='c'% >              OR

         <%@ taglib uri='http://java.sun.com/jstl/core_rt' prefix='c'% >  

     
Let us see how the problem is solved .

Tag library descriptor (TLD)  files play major  role  because it  maps the tag and the tag handler class  . You need to just include taglib directive in the JSP with absolute URI  mentioned in the tld file  sothat the page can use

the tags defined in a tag library. TLD file has various attributes to be passed to the tag handler class.  One of the attribute is value  which accepts value or the expression to be evaluated depending upon the node

rtexprvalue (run time expression value)   value  as given below   <rtexprvalue >true </rtexprvalue >  . If it is true , it accepts , the expression , otherwise not.  Now let us see the reason.

Suppose you are using jstl-1.2.jar and standard-1.1.2.jar  , it has three .tld files for each library
 For example , core library  has  three tld files  (c-1_0.tld ,   c-1_0-rt.tld   ,  c.tld ) ,  format library has three tld files that are  fmt-1_0.tld ,   fmt-1_0-rt.tld   ,  fmt.tld .  Similary xml ,  sql


library have three tld files each , functions library has only one tld file.  You can see the tld files in the META-INF folder when you extract the jar files.

Now let us take the core library tld files c-1_0.tld ,   c-1_0-rt.tld   ,  c.tld . Each tld files have some changes . I have noticed  some of the changes .  Please gothrough the tld files given below which are related to

our problem

According to the old  c-1_0.tld , some of the specifications are as follows
 1.  attribute value does not accept any runtime expressions(rt)   ,  2. it uses DTD to define the  struture  of XML document ,  3 .it uses the  encoding  format "ISO-8859-1" ,  4. URI to  identify   the tag library

descriptor (TLD) file uniquely  is   http://java.sun.com/jstl/core    , 5. JSTL version 1.0   , 6. required JSP version  is 1.2

 Part of the tld file is given below



   <?xml version="1.0" encoding="ISO-8859-1" ? >

 <!DOCTYPE taglib   PUBLIC "-//Sun Microsystems, Inc.//DTD JSP Tag Library 1.2//EN"   "http://java.sun.com/dtd/web-jsptaglibrary_1_2.dtd" >

 <taglib >

  .........

.........

 <jsp-version >1.2 </jsp-version >

   <short-name >c </short-name >

   <uri >http://java.sun.com/jstl/core </uri >

   <description >JSTL 1.0 core library </description >



    <attribute >

         <name >value </name >

         <required >false </required >

         <rtexprvalue >false </rtexprvalue >   // false - does not accept rt expression

     </attribute >

.................... 

According to the   c-1_0-rt.tld file ,
 1.  attribute value accepts runtime expressions(rt)   ,  2. URI to  identify   the tag library descriptor (TLD) file is   http://java.sun.com/jstl/core_rt   , 3.  JSTL version 1.0   , 4.  required JSP version  is 1.2

 Part of the tld file is given below



 <?xml version="1.0" encoding="ISO-8859-1" ? >

 <!DOCTYPE taglib   PUBLIC "-//Sun Microsystems, Inc.//DTD JSP Tag Library 1.2//EN"   "http://java.sun.com/dtd/web-jsptaglibrary_1_2.dtd" >



....

......

 <jsp-version >1.2 </jsp-version >

   <short-name >c_rt </short-name >

   <uri >http://java.sun.com/jstl/core_rt </uri >

   <display-name >JSTL core RT </display-name >

   <description >JSTL 1.0 core library </description >



 <attribute >

         <name >value </name >

         <required >false </required >

         <rtexprvalue >true </rtexprvalue >   // true - accepts rt expression

     </attribute >

......................

According to the  latest   c.tld   file ,
 1.  attribute value accepts any runtime expressions(rt)   ,  2. it uses XSD (XML Schema)   to define the  struture  of XML document ,  3. it uses the  encoding  format "UTF-8" ,  4. URI to  identify   the

tag library descriptor (TLD) file   is   http://java.sun.com/jsp/jstl/core    ,  5. data type is fixed for most of the variables which ensures the  type safe  , 5. JSTL version 1.1    , 6.  required JSP version is 2.0

Part of the tld file is given below



 <?xml version="1.0" encoding="UTF-8" ? >

 <taglib xmlns="http://java.sun.com/xml/ns/javaee"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee



http://java.sun.com/xml/ns/javaee/web-jsptaglibrary_2_1.xsd"     version="2.1" >

.....

.....

 <description >JSTL 1.1 core library </description >

   <short-name >c </short-name >

   <uri >http://java.sun.com/jsp/jstl/core </uri >

.....



    <attribute >

         <description >

   Expression to be evaluated.

         </description >

         <name >value </name >

         <required >false </required >

         <rtexprvalue >true </rtexprvalue >

         <deferred-value >

      <type >java.lang.Object </type >

         </deferred-value >

     </attribute >

............... 


Now you have to decide which URI you have to use with taglib directive .

Summary to remember

JSTL 1.0  is compatible with : Servlet 2.3  ,  JSP 1.2  , suitable URI is  http://java.sun.com/jstl/core . To accept RT expression ,  use  http://java.sun.com/jstl/core_rt
 JSTL 1.1  is compatible with  Servlet 2.4  ,  JSP 2.0  , suitable URI is  http://java.sun.com/jsp/jstl/core . To work with old .tld file , use the URI   http://java.sun.com/jstl/core_rt
 JSTL 1.2  is compatible with  Servlet 2.5 onwards  ,  JSP 2.1 ,  suitable URI is  http://java.sun.com/jsp/jstl/core .Still you have chance to work with old .tld file  using the URI    http://java.sun.com/jstl/core_rt
 

use JSTL with JSP and Struts with example code

Generic tasks such as iteration, conditional processing, data  formatting, internationalization, XML manipulation, and data access are made easy for JSP developers by  JavaServer Pages Standard Tag Library (JSTL ) which includes  a variety  of  tags . Now JSP developers can make use of   JSTL  tags which is  a good alternative to  scriptlets and  JSTL expression language (EL)  which  simplifies  the access to the java language .Two jar files (jstl.jar and standard.jar) are required to make use of JSTL . Now let us download the jar files required to use the tags and EL


There may be  confusion where to download  the two jar files (JSTL.jar and Standard.jar)   which contains various JSTL tag handler classes and .tld files . I am providing the  exact location to download the above two files.

You can download the Standard.jar file  from the location  http://repo2.maven.org/maven2/taglibs/standard/1.1.2/   and click on  the file  standard-1.1.2.jar  to save into local system . You can download the jstl.jar

 file  from the location  http://repo2.maven.org/maven2/javax/servlet/jstl/1.2/  and click on  the file  jstl-1.2.jar   to save into local system. jstl-1.2.jar and  standard-1.1.2. are compatible with Java EE 5 / JSP 2.1.

To check your JSP  ,Java , servlet and  server version which you are using , you can visit my earier post JSP / Java / Server version
 Old versions of jstl & standard jars  can be download   from   http://search.maven.org/#browse%7C2056229056 and http://search.maven.org/#search%7Cgav%7C1%7Cg%3A%22taglibs%22%20AND%20a%3A%22standard%22 respectively

Now let us see how to use the JSTL tags in JSP either using struts or without using struts. Steps to follow

1. Download the two jar files from the above location
 2. Put the two jar files into the Web application's library directory (/WEB-INF/lib)
 3. Make a Reference of the Tag Library Descriptors (TLD file) in the JSP .
              Two ways are there to make reference between your JSP pages and the tag library
  i) Make use of  an absolute URI for core library which is used for iteration , condtional processing , etc .. in the JSP as given below

  <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c'%>

  When you use value attribute to accept any expression  like   <c:out value="${country}"/>  , if you get the error  according to TLD or attribute directive in tag file, attribute value does


not accept any expressions , you can use the following URIs instead of above URI.

   <%@ taglib uri='http://java.sun.com/jsp/jstl/core' prefix='c'%>  OR


   <%@ taglib uri='http://java.sun.com/jstl/core_rt' prefix='c'%>

   For more detail on  why the above error occurs and how to solve , you can go through , Solution for attribute value does not accept any expressions
  Similarly , for XML library which is used for XML Processing ,  you can use the following absolute URI

             <%@ taglib uri="http://java.sun.com/jstl/xml"  prefix="x"%>  
                        OR
       <%@ taglib uri="http://java.sun.com/jstl/xml_rt"  prefix="x"%>

                        OR

      <%@ taglib uri="http://java.sun.com/jsp/jstl/xml" prefix="x"%>

  for SQL library (prefix : sql)   which is used for data access
           
               <%@ taglib uri="http://java.sun.com/jstl/xml"  prefix="sql"%> 
                         OR
            <%@ taglib uri="http://java.sun.com/jstl/sql_rt"  prefix="sql"%>
                         OR
             <%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>

  and for format library  (prefix : fmt) which helps for localization  , the following URI is used in the JSP

             <%@ taglib uri="http://java.sun.com/jstl/fmt"  prefix="fmt"%>
                       OR
            <%@ taglib uri="http://java.sun.com/jstl/fmt_rt"  prefix="fmt"%>
                      OR
             <%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>

 ii) Copy the necessary .tld files like  c.tld , x.tld , sql.tld etc  to the folder WEB-INF .You can get the .tld files in the folder META-INF  when you extract the latest jar files
          a)   You can directly add a JSP declaration to any jsp page that needs to use the tag
   <%@ taglib uri="/WEB-INF/c.tld" prefix="c"%>

    OR

            b)  To make a static reference , add the following entry to the web.xml file

  <taglib>
  <taglib-uri>ctld</taglib-uri>
  <taglib-location>/WEB-INF/c.tld</taglib-location>
  </taglib>
         then you can add the following JSP declaration to any jsp page   <%@ taglib uri="ctld" prefix="c"%> 

Ist method is the preferred one.

Some of the struts  tags are replaced with JSTL tags . You can use struts tags only  when there is no equivalent JSTL tags as  .
 Sample use of JSTL tags

 1.  Example code using struts logic:iterate


  <logic:iterate id="country" collection ="<%=countries%>">
  Country : <%=country%><br> 
  </logic:iterate>  

  the JSTL equivalent code  for the struts  logic:iterate


   <c:forEach var="country" items="${countries}">
  <LI><c:out value="${country}"/>   </c:forEach> 

 2. <bean:define id="empcode" value ="employeeObj"/>
  
 equivalent JSTL is   <c:set var ="empcode" value="${employeeObj}"/>

Oracle SQL Query for two timestamp difference

Below query where given to Sysdate is between two TimeStamp

to_char(AF_AN_S, 'DDMMYYYY HH24:MI'),      AF_EET,     trim(FT_NAME) as AVT_NAME,     AC_REG,      FLB_PAX,      FLB_INF,     P_NAME,    
P_VNAME,      CLG_ACD_CODE,     P_LTR_CODE,     P_PERS_NR,      P_NR   
from     IBSDBA.MV_ACT_FLIGHT,IBSDBA.MV_AIRCRAFT,
IBSDBA.MV_FL_TYP,    
IBSDBA.MV_FLT_ART,
IBSDBA.MV_FLT_BLNG,IBSDBA.MV_VP_FLIGHT_ASSIGN,     
IBSDBA.MV_CREW_LOG, IBSDBA.MV_PERSONAL    
where      P_OWNER = 'TUI'    
and     CLG_P_PERS_NR = P_PERS_NR   
and     VAS_SEQ = CLG_VAS_SEQ    
and     VAS_AF_ID = AF_ID  
and AF_AB_S between TO_TIMESTAMP ('05.04.2012:03:00:00','DD.MM.YYYY:HH24:MI:SS')
and   TO_TIMESTAMP ('05.04.2012:17:00:00','DD.MM.YYYY:HH24:MI:SS')

Call DB2 SP from Java CallableStatement . CallableStatement in java 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- You can visit my earlier post for detail on PreparedStatement object and example in DB2
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 ? Use 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

   
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");
             } 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 :

2. 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 >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>200000 and sumsales<=300000) THEN
 set bonus=300;
ELSEIF (sumsales>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