//*************************************************************************** // (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