//-----------------------------------------------------------------------------
// (c) Copyright IBM Corp. 2008 All rights reserved.
//
// The following sample of source code ("Sample") is owned by International
// Business Machines Corporation or one of its subsidiaries ("IBM") and is
// copyrighted and licensed, not sold. You may use, copy, modify, and
// distribute the Sample in any form without payment to IBM, for the purpose of
// assisting you in the development of your applications.
//
// The Sample code is provided to you on an "AS IS" basis, without warranty of
// any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
// IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
// not allow for the exclusion or limitation of implied warranties, so the above
// limitations or exclusions may not apply to you. IBM shall not be liable for
// any damages you suffer as a result of using, copying, modifying or
// distributing the Sample, even if IBM has been advised of the possibility of
// such damages.
//-----------------------------------------------------------------------------
//
// SOURCE FILE NAME: Cgtt.java
//
// SAMPLE:
// The sample demonstrates the following:
// i) Use of Created Temporary table (CGTT) to store intermediate
// results.
// ii) Use of Created Temporary table with Procedures, Functions, Triggers
// and Views.
//
// PREREQUISITE:
// 1) Sample database is setup on the machine.
// 2) Valid system authorization IDs and password.
// bob with "bob12345"
// joe with "joe12345"
// 3) Execute 'CreateCGTT.db2' script. This script creates all database
// objects required for the executing this sample and GRANTS required
// privileges to bob an joe.
//
//
// USAGE SCENARIO:
// The scenario deals with the employee tax computation process.
// At the end of a financial year, the payroll department computes tax
// payable by all the employees. The sample demonstrates the use of
// created temporary table to store intermediate results during the tax
// calculation process. The database contains employee, and payroll tables.
// The employee table contains employee details and the payroll table
// contains details of employee salary and the total tax payable by the
// employee based on his income (salary + bonus) in a finnancial year.
//
// Each employee gets tax exemption for salary upto 100,000
// the proof for exemption is submitted at the end of the financial
// year. At the beginning of financial year the tax payable by an employee
// is calculated based on the employees income and the 100,000 exemption
// limit. At the end of the year after all the employees of a department
// have submitted their tax proofs, the tax calculation process for the
// department is trigered. The tax process updates the payroll table with
// the total tax and the balance tax payable. An Income Tax(IT) statement
// is also generated for each employee of the department.
//
// A TRIGGER is invoked after tax proofs is submitted by all
// the employee of a department. The trigger populates the created temporary
// table with the details of the employee and his income (salary + any bonus)
// details and calls a procedure to calculate the tax. All the intermediate
// results of tax calculation are updated in the CGTT. After the tax calculation
// is complete another procedure updates the payrool table with the tax data
// from the created temporary table. A function generates the IT sheet for
// all the employees.
//
// SAMPLE EXECUTION:
//
// 1) Execute 'CreateCGTT.db2' to create required database objects using
// command :
// db2 -td@ -vf CreateCGTT.db2
// 2) Compile and Execute this sample using commands :
// i) javac Cgtt.java
// ii) java Cgtt.java <Server-name> <Port-number>
// 3) Execute 'DropCGTT.db2' to drop all the database objects created by the
// sample :
// db2 -td@ -vf DropCGTT.db2
//
// SQL STATEMENTS USED:
// 1) CREATE GLOBAL TEMPORARY TABLE
// 2) CREATE INDEX
// 3) CREATE VIEW
// 4) CREATE TRIGGER
// 5) CREATE PROCEDURE
// 6) CREATE FUNCTION
// 7) CALL
// 8) RUNSTATS
// 9) TRUNCATE TABLE
//
//-----------------------------------------------------------------------------
// The sample has seven major steps:
//
// ************************************************************************* //
// Step1 to Step 5 creates all the database objects required for the //
// execution of the sample. //
// Execute 'CreateCGTT.db2' script to create the required database //
// objects. //
// To execute 'CreateCGTT.db2' issue the command : //
// db2 -td@ -vf CreateCGTT.db2 //
// ************************************************************************* //
// Step1: Create table 'payroll' and populate it.
// Step2: Create the following objects:
// - Create a CGTT 'tax_cal'.
// - View 'ViewOnCgtt' based on 'tax_cal'.
// - Index 'IndexOnCgtt' based on 'tax_cal'.
// Step3: Create three procedures and a function
// 1) tax_compute : Calculates the tax payable by an
// employee and returns the value to the
// CALLER.
// 2) initial_tax_compute: Calculates the tax payable by an
// employee initially with a tax
// exemption of 100,000. This procedure
// calls the function 'tax_compute' to
// do the calculation.
// 3) final_tax_compute : Calculates the tax payable by an
// employee based on his or her total
// income (salary + any bonus) and the
// tax proofs he or she submits. This
// procedure calls the function
// 'tax_compute' to do the calculation.
// 4) update : Updates the created temporary table
// 'tax_cal' with the final results, To
// update the 'payroll' table to reflect
// the created temporary table.
// Step4: Create a function 'printITSheet' to print the IT sheet for the
// employees, using the data in the created temporary table 'tax_cal'
// through the view 'ViewOnCgtt'.
// Step5: Create a Trigger 'tax_update' on 'Payroll' table to start the
// tax calculation process.
//
// ************************************************************************* //
// Step6 - calculation of tax to be paid by the employees of two //
// departments 'D11' and 'D21' is done by this sample 'Cgtt.java'. //
// ************************************************************************* //
// Step6: Start the tax computation process for two departments.
//
// ************************************************************************* //
// Step7 cleans up all the database objects created by 'CreateCGTT.db2'//
// Execute 'DropCGTT.db2' script to drop all the database objects //
// //
// To execute 'DropCGTT.db2' issue the command : //
// db2 -td@ -vf DropCGTT.db2 //
// ************************************************************************* //
// Step7: Run the Clean up scripts.
//-----------------------------------------------------------------------------
import java.sql.*;
class Cgtt
{
static Db db;
public static void main(String argv[])
{
try
{
Connection con1 = null, con2 = null, con3 = null;
String user1 = "joe";
String password1 = "joe12345";
String server = argv[0];
String port=argv[1];
// connect to sample database
con1 = ConHandler(user1,password1,server,port);
con1.setAutoCommit(false);
CalTax(con1,"D11",user1);
String user2 = "bob";
String password2 = "bob12345";
con2 = ConHandler(user2,password2,server,port);
con2.setAutoCommit(false);
CalTax(con2,"D21",user2);
db.disconnect();
}
catch (Exception e)
{
System.out.println("Error Msg: "+ e.getMessage());
}
}
public static Connection ConHandler(String user, String password, String serverAdd, String port)
{
Connection con = null;
String args[]=new String[4];
args[1]=port;
args[0]=serverAdd;
args[2]=user;
args[3]=password;
try
{
db=new Db(args);
}
catch (Exception e)
{
System.out.println(" Error loading DB2 Driver...\n");
System.out.println(e);
System.exit(1);
}
try
{
con = db.connect();
con.setAutoCommit(false);
}
catch (Exception e)
{
System.out.println("Error while Connecting to sample database.");
System.err.println(e) ;
System.exit(1);
}
return con;
}
static void CalTax(Connection con, String deptnum, String user)
{
try
{
System.out.print("\n-----------------------------------------------------"+
"----------------------------------------------------------------------\n");
System.out.print("Employee of the company '"+user+"' calculates "+
"the tax for department '"+deptnum+"'...\n");
System.out.print("'"+user+"' updates the 'payroll' table as per "+
"the tax proof submitted by the employees of "+
"department '"+deptnum+"'...\n");
System.out.print("As per the scenario, all employees of '"+deptnum+"' submit "+
"tax proofs for '50000'...\n");
System.out.print("\n------------------------------------------------------"+
"----------------------------------------------------------------------\n\n");
String st1 = "UPDATE cgtt.payroll SET tax_proof = 50000 "+
"WHERE deptno = '"+deptnum+"'";
System.out.print("\n'"+user+"' triggers the process of tax "+
"calculation by updating the 'calculate_tax' to one "+
"for all employees of \n department '"+deptnum+"' after all the "+
"employees of the department submit the tax proof\n");
String st2 = "UPDATE cgtt.payroll SET calculate_tax = 1 "+
"WHERE deptno = '"+deptnum+"'";
System.out.print("Once tax calculation is complete, '"+user+"' "+
"set the 'calculate_tax' column back to zero\n");
String stn = "UPDATE cgtt.payroll SET calculate_tax = 0 ";
System.out.println("\nUPDATE payroll SET tax_proof = 50000 "+
"WHERE deptno = '"+deptnum+"'\n ");
Statement stmt1 = con.createStatement();
stmt1.executeUpdate(st1);
System.out.println("\nUPDATE payroll SET calculate_tax = 1 "+
"WHERE deptno = '"+deptnum+"'\n \n");
Statement stmt2 = con.createStatement();
stmt2.executeUpdate(st2);
Statement stmt3 = con.createStatement();
stmt3.executeUpdate(stn);
Process(con);
PrintITReport(con);
con.commit();
}
catch(Exception e)
{
String Message = e.getMessage();
System.out.println(Message);
}
}
static void Process(Connection con)
{
try
{
java.sql.CallableStatement cstmt;
String Proc = "CALL cgtt.update()";
cstmt = con.prepareCall(Proc);
cstmt.execute();
}
catch(Exception e)
{
String Message = e.getMessage();
System.out.println(Message);
}
}
static void PrintITReport(Connection con)
{
try
{
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM "+
"TABLE(cgtt.printITSheet()) as ITSheet");
ResultSetMetaData rsm = rs.getMetaData();
int columnCount = rsm.getColumnCount();
int i=1;
while(i <= columnCount )
{
System.out.print(rsm.getColumnName(i)+"\t ");
i++;
}
System.out.print("\n-----------------------------------------------------"+
"----------------------------------------------------------------------\n");
while(rs.next()){
int j=1;
while(j <= columnCount )
{
System.out.print(rs.getString(rsm.getColumnName(j))+"\t ");
j++;
}
System.out.print("\n");
}
rs.close();
stmt.close();
System.out.print("\n-----------------------------------------------------"+
"----------------------------------------------------------------------\n");
}
catch(Exception e)
{
String Message = e.getMessage();
System.out.println(Message);
}
}
}