//*********************************************************************
//
//                 Copyright (c) 2007-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20914JD.java
//  Header:     none
//  Purpose:    Demonstrate basic Monitor partition SQL using the JDBC API by
//              performing a SET SESSION RATE and a MONITOR SESSION.
//              The program will:
//                -  Connect as user guest,please
//                -  bind and execute SET SESSION RATE
//                -  bind and execute MONITOR SESSION
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.PreparedStatement.setXXX
//            java.sql.PreparedStatement.execute,
//            java.sql.ResultSet, java.sql.ResultSetMetaData
//
//  Version: Updated for Teradata V2R6
//
//*********************************************************************

import java.sql.*;

public class T20914JD
{
    // Name of the user able to execute MONITOR statement.
    // NOTE that "GRANT MONITOR TO " user is assumed!
    public static String user = "guest";
    public static String password = "please";

    public static void main(String args[])
    throws ClassNotFoundException, InterruptedException
    {
        // URL to be passed to the JDBC driver
        String url = "jdbc:teradata://whomooz/PARTITION=MONITOR";

        try {
            System.out.println("Sample T20914JD starting: " + new java.util.Date());
            System.out.println(" Looking for the Teradata JDBC driver... ");
            Class.forName("com.teradata.jdbc.TeraDriver");
            System.out.println(" Teradata JDBC driver loaded.");

            System.out.println(" Attempting to connect to Teradata: " + url);
            Connection con = DriverManager.getConnection(url, user, password);
            System.out.println(" Connection to Teradata established: user=" + user
                + ",password=" + password);

            try {
                String setSessionRate = "SET SESSION RATE";
                System.out.println(" Creating a PreparedStatement for "
                    + setSessionRate + "...");
                PreparedStatement pstmt1 = con.prepareStatement(setSessionRate);
                System.out.println(" PreparedStatement for "
                    + setSessionRate + " created.");

                try {
                    executeSetSessionRate(pstmt1, setSessionRate);
                } finally {
                    System.out.println(" Closing PreparedStatement for "
                        + setSessionRate + "...");
                    pstmt1.close();
                    System.out.println(" PreparedStatement for "
                        + setSessionRate + " closed.");
                }

                String monitorSession = "MONITOR SESSION";
                System.out.println(" Creating a PreparedStatement for "
                    + monitorSession + "...");
                PreparedStatement pstmt2 = con.prepareStatement(monitorSession);
                System.out.println(" PreparedStatement for "
                    + monitorSession + " created.");

                try {
                    executeMonitorSession(pstmt2, monitorSession);
                } finally {
                    System.out.println(" Closing PreparedStatement for "
                        + monitorSession + "...");
                    pstmt2.close();
                    System.out.println(" PreparedStatement for "
                        + monitorSession + " closed.");
                }
            } finally {
                System.out.println(" Closing connection to Teradata...");
                con.close();
                System.out.println(" Connection to Teradata closed.");
            }

            System.out.println("Sample T20914JD finished. " + new java.util.Date());
        }
        catch (SQLException ex)
        {
            // A SQLException was generated.  Catch it and display
            // the error information.
            // Note that there could be multiple error objects chained
            // together.
            System.out.println();
            System.out.println("*** SQLException caught ***");

            while (ex != null)
            {
                System.out.println(" Error code: " + ex.getErrorCode());
                System.out.println(" SQL State: " + ex.getSQLState());
                System.out.println(" Message: " + ex.getMessage());
                ex.printStackTrace();
                System.out.println();
                ex = ex.getNextException();
            }

            throw new IllegalStateException ("Sample failed.") ;
        }
    }

    private static void executeSetSessionRate(PreparedStatement pstmt,
        String setSessionRate)
    throws SQLException, InterruptedException
    {
        System.out.println(" Binding arguments for " + setSessionRate + "...");
        pstmt.setShort(1, (short)2); // set the version number
        short sampleRate = 5;
        pstmt.setShort(2, sampleRate); // set sample rate
        pstmt.setString(3, "y"); // set local change mode

        System.out.println(" Executing " + setSessionRate + "...");
        if (pstmt.execute()) {
            printMonitorResults(pstmt, setSessionRate);
        }
        System.out.println(" Waiting for " + setSessionRate + " to take affect...");
        Thread.sleep((sampleRate + 2) * 1000);
    }

    private static void executeMonitorSession(PreparedStatement pstmt,
        String monitorSession)
    throws SQLException
    {
        System.out.println(" Binding arguments for " + monitorSession + "...");
        pstmt.setShort(1, (short)5); // set the version number
        pstmt.setNull(2, Types.SMALLINT); // set the host id
        pstmt.setNull(3, Types.INTEGER); // set the session number
        pstmt.setObject(4, null, Types.CHAR, 30); // set the user name

        System.out.println(" Executing " + monitorSession + "...");
        if (pstmt.execute()) {
            printMonitorResults(pstmt, monitorSession);
        }
    }

    private static void printMonitorResults(PreparedStatement pstmt,
        String monitorStmt)
    throws SQLException
    {
        ResultSet rs = pstmt.getResultSet();
        boolean result = false;
        if (rs != null) {
            System.out.println(" Retrieving " + monitorStmt + " ResultSet(s)...");
            try {
                int set = 1;
                int row = 0;
                ResultSetMetaData rsmd = rs.getMetaData();
                while (rs.next()) {
                    row++;
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        System.out.println("  ResultSet=" + set + ",Row=" + row
                            + ",Col=" + i + ",Type=" + rsmd.getColumnTypeName(i)
                            + ",DisplaySize=" + rsmd.getColumnDisplaySize(i)
                            + ",Val=" + rs.getObject(i));
                    }
                    result = true;
                }
                while (pstmt.getMoreResults()) {
                    set++;
                    row = 0;
                    rs = pstmt.getResultSet();
                    rsmd = rs.getMetaData();
                    while (rs.next()) {
                        row++;
                        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                            System.out.println("  ResultSet=" + set + ",Row=" + row
                                + ",Col=" + i + ",Type=" + rsmd.getColumnTypeName(i)
                                + ",DisplaySize=" + rsmd.getColumnDisplaySize(i)
                                + ",Val=" + rs.getObject(i));
                        }
                    }
                }
            } finally {
                rs.close();
            }
        }
        if (!result) {
            System.out.println("  No ResultSet was found for " + monitorStmt + ".");
        }
    }
}