/*(c) Copyright IBM Corp. 2003  All rights reserved.                 */
 /*                                                                   */
 /*This sample program is owned by International Business Machines    */
 /*Corporation or one of its subsidiaries ("IBM") and is copyrighted  */
 /*and licensed, not sold.                                            */
 /*                                                                   */
 /*You may copy, modify, and distribute this sample program in any    */
 /*form without payment to IBM,  for any purpose including developing,*/
 /*using, marketing or distributing programs that include or are      */
 /*derivative works of the sample program.                            */
 /*                                                                   */
 /*The sample program 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 PARTIC-*/
 /*ULAR 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, modifying or distributing */
 /*the sample program or its derivatives.                             */
 /*                                                                   */
 /*Each copy of any portion of this sample program or any derivative  */
 /*work,  must include a the above copyright notice and disclaimer of */
 /*warranty.                                                          */
 /*                                                                   */
import  java.io.*;
import  java.sql.*;


/**
 * DBTrace program reads the output from the db2event monitor and inserts the data into database
 * Creation Date: January 15 2003.
 */
class DB2Trace {

    /**
     * Usage: java DB2Trace rk.txt jdbc:db2:db2mwh rkolluru rama
     * rk.txt is the input file
     * jdbc:db2:db2mwh is the database jdbc url connection
     * rkolluru is the username
     * rama is the password
     * @param args[]
     * @exception IOException, SQLException
     */
    public static void main (String args[]) throws IOException, SQLException {
        FileWriter fw = new FileWriter("DB2Trace.sql");
        PrintWriter pw = new PrintWriter(fw);
        BufferedReader in = new BufferedReader(new FileReader(args[0]));
        String s = "";
        String sqlString = "";
        boolean textYes = false;
        int exists = 0;
        COM.ibm.db2.jdbc.app.DB2Driver driver = new COM.ibm.db2.jdbc.app.DB2Driver();
        java.sql.DriverManager.registerDriver(driver);
        Connection conn = java.sql.DriverManager.getConnection(args[1], args[2],
                args[3]);
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("SELECT COUNT(*) FROM SYSCAT.TABLES WHERE TABSCHEMA=USER AND "
                + "TABNAME='DB2TRACE'");
        while (rset.next()) {
            exists = rset.getInt(1);
        }
        if (exists > 0)
            stmt.executeUpdate("DROP TABLE DB2TRACE");
        stmt.executeUpdate("CREATE TABLE DB2TRACE( OPERATION VARCHAR(30),SQLTXT VARCHAR(32000),"
                + "STARTTIME VARCHAR(30),STOPTIME VARCHAR(30) ," + "EXECTIME VARCHAR(20),"
                + "USRCPU   VARCHAR(20)," + "SORTS    VARCHAR(20)," + "TOTSORTTIME VARCHAR(20)) IN TEST ");
        stmt.executeUpdate("CREATE INDEX DB2TRACE_CPU ON DB2TRACE(USRCPU)");
        PreparedStatement p = conn.prepareStatement("INSERT INTO DB2TRACE(OPERATION,SQLTXT,EXECTIME,"
                              +" STARTTIME,STOPTIME,USRCPU,SORTS,TOTSORTTIME) VALUES(?,?,?,?,?,?,?,?)");
        while ((s = in.readLine()) != null) {
            if (s.startsWith("  Operation: ")) {
                p.setString(1, s.substring(13, s.length()));
            }
            if (s.startsWith("  Text     :")) {
                textYes = true;
                p.setString(2, s.substring(13, s.length()));
            }
            if (s.startsWith("  Exec Time:")) {
                p.setString(3, s.substring(14, s.length() - 7));
            }
            if (s.startsWith("  Start Time: ")) {
                p.setString(4, s.substring(14, s.length()));
            }
            if (s.startsWith("  Stop Time:  ")) {
                p.setString(5, s.substring(14, s.length()));
            }
            if (s.startsWith("  User CPU:")) {
                p.setString(6, s.substring(13, s.length() - 7));
            }
            if (s.startsWith("  Sorts:")) {
                p.setString(7, s.substring(8, s.length()));
            }
            if (s.startsWith("  Total sort time:")) {
                p.setString(8, s.substring(18, s.length()));
                if (textYes == true) {
                    p.executeUpdate();
                    textYes = false;
                }
            }
        }
        pw.close();
        stmt.close();
        p.close();
    }
}