//*************************************************************************** 
// (c) Copyright IBM Corp. 2007 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: TbRunstats.sqlj 
// 
// SAMPLE: How to perform runstats on a table 
// 
// SQL STATEMENTS USED: 
//         SELECT  
//         CONNECT 
//         RUNSTATS 
// 
// JAVA 2 CLASSES USED: 
//         Statement 
//         File 
//         FileWriter 
//         Process 
//         BufferedReader 
//         InputStreamReader 
// 
// Classes used from Util.java are: 
//         Db 
//         SqljException 
// 
//                           
// Output will vary depending on the JDBC driver connectivity used. 
//*************************************************************************** 
// 
// For more information on the sample programs, see the README file. 
// 
// For information on developing Java applications see the Developing Java Applications book. 
// 
// For information on using SQL statements, see the SQL Reference. 
// 
// For the latest information on programming, compiling, and running DB2 
// applications, visit the DB2 Information Center at 
//     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp 
//*************************************************************************** 
 
import java.sql.*; 
import java.lang.*; 
import java.io.*; 
import sqlj.runtime.*; 
import sqlj.runtime.ref.*; 
 
#sql iterator TbRunstats_Cursor0(String);

public class TbRunstats 
{ 
  public static void main(String argv[])  
  { 
    DefaultContext ctx = null;

    try 
    { 
      Db db = new Db(argv); 
 
      // connect to the 'sample' database
      ctx = db.getDefaultContext();
       
      // call tbRunstats that updates the statistics of employee table 
      tbRunstats(); 
       
      // disconnect from the 'sample' database 
      db.disconnect();   
    } 
    catch (Exception e) 
    { 
      SqljException sqljExc = new SqljException(e); 
      sqljExc.handle(); 
    } 
  } // main 
   
  // call runstats on 'employee' table to update its statistics 
  static void tbRunstats() throws Exception 
  { 
    System.out.print( 
      "\n-----------------------------------------------------------\n" + 
      "\nUSE THE SQL STATEMENT:\n"+ 
      "  RUNSTATS\n" + 
      "TO UPDATE TABLE STATISTICS.\n"); 
     
    // get fully qualified name of the table 
    String tableName = "EMPLOYEE";  
    String schemaName = schemaNameGet(tableName);     
    String fullTableName = schemaName + "." + tableName; 
     
    try 
    { 
      // store the CLP commands in a file and execute the file 
      File outputFile = new File("RunstatsCmd.db2"); 
      FileWriter out = new FileWriter(outputFile); 
             
      String cmd = "RUNSTATS ON TABLE "+ fullTableName + 
                     " WITH DISTRIBUTION ON KEY COLUMNS" + 
                     " DEFAULT NUM_FREQVALUES 30 NUM_QUANTILES -1" + 
                     " ALLOW READ ACCESS"; 
       
      out.write("CONNECT TO SAMPLE;\n"); 
      out.write(cmd + ";\n"); 
      out.write("CONNECT RESET;\n"); 
       
      out.close(); 
             
      Process p = Runtime.getRuntime().exec("db2 -vtf RunstatsCmd.db2"); 
                                         
      BufferedReader stdInput = new BufferedReader(new  
                                      InputStreamReader(p.getInputStream())); 
      BufferedReader stdError = new BufferedReader(new 
                                      InputStreamReader(p.getErrorStream())); 
      String s; 
      
      // read the output from the command and set the output variable with  
      // the value 
      while ((s = stdInput.readLine()) != null) 
      { 
        System.out.println(s); 
      } 
       
      // read any errors from the attempted command and set the error   
      // variable with the value 
      while ((s = stdError.readLine()) != null)  
      { 
        System.out.println(s); 
      } 
      
      // destroy the process created
      p.destroy();           
      
      // delete the temporary file created 
      outputFile.deleteOnExit();  
    } 
    catch (IOException e) 
    { 
      e.printStackTrace(); 
      System.exit(-1); 
    }  
  } // tbRunstats 
   
  // function to get the schema name for a particular table 
  static String schemaNameGet(String tableName) throws Exception 
  { 
    // declare a cursor to run through the result of the query
    TbRunstats_Cursor0 cur0; 
     
    #sql cur0 = {SELECT tabschema 
                    FROM syscat.tables 
                    WHERE tabname = :tableName}; 
     
    String schemaName = null; 
    #sql {FETCH :cur0 INTO :schemaName};  
     
    // remove the trailing white space characters from schemaName before  
    // returning it to the calling function 
    return schemaName.trim(); 
     
  } // schemaNameGet     
} // TbRunstats