//
// Source File Name: Browser.java 1.3
//
// Licensed Materials -- Property of IBM
//
// (c) Copyright International Business Machines Corporation, 1999.
// All Rights Reserved.
//
// US Government Users Restricted Rights -
// Use, duplication or disclosure restricted by
// GSA ADP Schedule Contract with IBM Corp.
//
// PURPOSE :
// Prompts for a Schema and table search pattern string.
// Display a list of tables that macth the search pattern.
// Allows the user to select a varitey of catalog information,
// for each of the tables.
// For more information about these samples, refer to the README file.
// For more information on Programming in Java, refer to the
// "Programming in Java" section of the Application Development Guide.
// For more information on building and running Java programs for DB2,
// refer to the "Building Java Applets and Applications" section of the
// Application Building Guide.
// For more information on the SQL language, refer to the SQL Reference.
import java.io.*;
import java.lang.*;
import java.sql.*;
import java.util.*;
class Browser {
static {
try {
// register the driver with DriverManager
// The newInstance() call is needed for the sample to work with
// JDK 1.1.1 on OS/2, where the Class.forName() method does not
// run the static initializer. For other JDKs, the newInstance
// call can be omitted.
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main (String[] argv) {
Connection con = null;
try {
// connect to server
if (argv.length == 3)
{ // connect using command line arguments
con = Tools.DBConnect(argv[0], argv[1], argv[2]);
}
else
{ // prompt user for database name, user ID, password
con = Tools.DBConnect();
}
con.setAutoCommit (false);
boolean quit = false;
int tableChoice = 0;
String action = new String("");
String schema = new String("");
String table = new String("");
Vector tables = new Vector();
tables = listAndStoreTables (con);
// main program asks which methods to run and then executes
while (!quit) {
System.out.println ("\n|Q=Quit C=cols P=Primary Key I=Index M=Imported Key |");
System.out.println ( "|T=Tab Priv O=Col Priv S=Stats X=Exported Key L=List Tables |");
System.out.println ("Enter an action: [Q | C | P | I | F | T | O | L]");
action = Tools.readString();
if (action.equalsIgnoreCase ("q")) { quit = true; }
else {
// depending on letter provided by user specific methods will perform
// note user entries must be perfect or exceptions will be thrown
if (action.equalsIgnoreCase ("l")) { tables = listAndStoreTables (con); }
else {
System.out.println ("Enter a table number:");
tableChoice = Integer.valueOf (Tools.readString()).intValue();
// a vector stores the names of the tables and schemas
// vectors were used because of expandability
// however, only one vector contains both the schema and table values
schema = ((String)tables.elementAt((tableChoice-1)*2)).trim();
table = ((String)tables.elementAt((tableChoice-1)*2+1)).trim();
if (action.equalsIgnoreCase ("c")) { listColumns (con, schema, table); }
if (action.equalsIgnoreCase ("p")) { listPrimaryKeys (con, schema, table); }
if (action.equalsIgnoreCase ("i")) { listIndices (con, schema, table); }
if (action.equalsIgnoreCase ("m")) { listForeignKeys (con, schema, table, true); }
if (action.equalsIgnoreCase ("t")) { listTablePriv (con, schema, table); }
if (action.equalsIgnoreCase ("o")) { listColPriv (con, schema, table); }
if (action.equalsIgnoreCase ("s")) { listStats (con, schema, table); }
if (action.equalsIgnoreCase ("x")) { listForeignKeys (con, schema, table, false); }
System.out.print ("\n>> Hit Enter to Continue <<");
action = Tools.readString ();
}
}
}
// disconnect from connection made
System.out.println ("\n>Disconnecting...");
con.commit();
con.close();
} catch (Exception e) { e.printStackTrace(); }
}
//
// listAndStoreTables
// - lists tables for user and stores tables and schemas in a vector
//
public static Vector listAndStoreTables (Connection con) {
Vector storedTables = new Vector();
try {
System.out.println ("Enter Table Schema Name Search Pattern:");
String tableSchemSearchPat = Tools.readString().toUpperCase();
System.out.println ("Enter Table Name Search Pattern:");
String tableSearchPat = Tools.readString().toUpperCase();
DatabaseMetaData dmd = con.getMetaData();
System.out.println ("# TABLE_SCHEMA TABLE_NAME TABLE_TYPE");
System.out.println (" ----------------------- ----------------------- ----------");
// create a vector to store all table types
// this method insures that only as much memory as needed is used
// to store the data types
ResultSet rs1 = dmd.getTableTypes();
Vector tableTypes = new Vector();
while (rs1.next()) {
tableTypes.addElement(rs1.getString (1));
}
rs1.close();
// copy vector to an array to be used in getTables
String[] tableType = new String[tableTypes.size()];
tableTypes.copyInto (tableType);
String tableSchema = new String("");
String table = new String("");
String tType = new String("");
// output tables and types that fit search pattern
ResultSet rs2 = dmd.getTables (null, tableSchemSearchPat, tableSearchPat, tableType);
int indicator = 0;
while (rs2.next()) {
// format and read in text for output
tableSchema = rs2.getString (2);
table = rs2.getString (3);
tType = rs2.getString (4);
indicator = indicator + 1;
System.out.println (Tools.padLength (String.valueOf(indicator), 3) + " " + Tools.padLength(tableSchema, 23) + " " + Tools.padLength(table, 23) + " " + tType);
storedTables.addElement (tableSchema);
storedTables.addElement (table);
}
rs2.close();
} catch (Exception e) { e.printStackTrace(); }
return storedTables;
}
//
// listColumns
// - schemaName and tableName should be exact strings, NOT search patterns
//
public static void listColumns (Connection con, String schemaName, String tableName) {
try {
DatabaseMetaData dmd = con.getMetaData();
ResultSet rs = dmd.getColumns (null, schemaName, tableName, "%");
System.out.println ("\nSchema: " + schemaName + "\tTable: " + tableName);
// variables to be used in loop through the result set
int n = 0;
int decLength = 0;
int colLength = 0;
String nlble = new String("");
String colName = new String("");
String colType = new String("");
String remarks = new String("");
String numOutput = new String("");
while (rs.next()) {
colName = rs.getString (4);
colType = rs.getString (6);
colLength = rs.getInt (7);
decLength = rs.getInt (9);
// formated output
numOutput = "(" + colLength;
if (!rs.wasNull()) { numOutput = numOutput + ", " + decLength; }
numOutput = numOutput + ")";
// if n = tyepNullable then column values are nullable else not
n = rs.getInt (11);
if (n == DatabaseMetaData.typeNullable) { nlble = "NULLABLE"; } else { nlble = "NOT NULLABLE"; }
remarks = rs.getString (12);
// output column characteristics to screen
System.out.print (" " + colName + ", " + nlble + ", " + colType + ", " + numOutput);
if (!rs.wasNull() && !remarks.equals("")) { System.out.print (", " + remarks); }
System.out.print ("\n");
}
rs.close();
} catch (Exception e) { e.printStackTrace(); }
}
//
// listPrimaryKeys
// - schemaName and tableName should be exact strings, NOT search patterns
//
public static void listPrimaryKeys (Connection con, String schemaName, String tableName) {
try {
DatabaseMetaData dmd = con.getMetaData();
ResultSet rs = dmd.getPrimaryKeys (null, schemaName, tableName);
System.out.println ("\nPrimary Keys for " + schemaName + "." + tableName);
String colName = new String("");
String pkName = new String("");
int num = 1;
// retrieve data and output information
while (rs.next()) {
colName = rs.getString (4);
pkName = rs.getString (6);
System.out.print (" " + num + " Column: " + Tools.padLength(colName, 18) + " Primary Key Name: ");
if (rs.wasNull()) { System.out.print ("NULL\n"); } else { System.out.print (pkName + "\n"); }
num = num + 1;
}
rs.close();
} catch (Exception e) { e.printStackTrace(); }
}
//
// listIndices
// - schemaName and tableName should be exact strings, NOT search patterns
//
public static void listIndices (Connection con, String schemaName, String tableName) {
try {
DatabaseMetaData dmd = con.getMetaData();
ResultSet rs1 = dmd.getIndexInfo (null, schemaName, tableName, true, true);
System.out.println ("\nPrimary Key or Unique Index for " + schemaName + "." + tableName);
Vector indexCols = new Vector();
// getIndexInfo will return the names of all index columns
// however it will not return the information about those columns
// therfore store column names into a vector and perform another query
// using getColumns where columnsearchpattern = column name in vector
String colName1 = new String("");
while (rs1.next()) {
colName1 = rs1.getString (9);
if (!rs1.wasNull()) { indexCols.addElement (colName1); }
}
rs1.close();
String colName2 = new String("");
String type = new String("");
int length = 0;
int scale = 0;
// for every column name stored in indexColumns query the column
// characteristics and output
for (int cnt = 0; cnt < indexCols.size(); cnt = cnt + 1) {
ResultSet rs2 = dmd.getColumns (null, schemaName, tableName, (String)indexCols.elementAt(cnt));
if (rs2.next()) {
colName2 = rs2.getString (4);
type = rs2.getString (6);
length = rs2.getInt (7);
scale = rs2.getInt (9);
System.out.print (" " + colName2 + ", " + type + ", (" + length);
if (!rs2.wasNull()) { System.out.print (", " + scale + ")\n"); } else { System.out.print (")\n"); }
}
rs2.close();
}
} catch (Exception e) { e.printStackTrace(); }
}
//
// listForeignKeys
// - schemaName and tableName should be exact strings, NOT search patterns
//
public static void listForeignKeys (Connection con, String schemaName, String tableName, boolean importedKey) {
try {
DatabaseMetaData dmd = con.getMetaData();
ResultSet rs;
// since the getImportedKeys and getExportedKeys methods return the same types of
// information to the result set use the same method to do both
// however we must choose which one through the boolean importedKey
String keyType = new String("");
if (importedKey) {
rs = dmd.getImportedKeys (null, schemaName, tableName);
keyType = "Imported";
}
else {
rs = dmd.getExportedKeys (null, schemaName, tableName);
keyType = "Exported";
}
System.out.println ("\nPrimary Key and " + keyType + " Keys for " + schemaName + "." + tableName);
String updateRuleType = new String("");
String deleteRuleType = new String("");
String pkColName = new String("");
String fkSchema = new String("");
String fkTable = new String("");
String fkColName = new String("");
String pkName = new String("");
String fkName = new String("");
short updateRule = 0;
short deleteRule = 0;
while (rs.next()) { // retrieve data and output
// if the values for either rule type match values for the defined variables
// in the DatabaseMetaData class then the rule type is found
updateRule = rs.getShort (10);
switch (updateRule) {
case DatabaseMetaData.importedKeyCascade: updateRuleType = "CASCADE "; break; // non IBM only
case DatabaseMetaData.importedKeyRestrict: updateRuleType = "RESTRICT "; break; // always for IBM DBMs
default: updateRuleType = "SET NULL "; break;
}
deleteRule = rs.getShort (11);
switch (deleteRule) {
case DatabaseMetaData.importedKeyNoAction: deleteRuleType = "NO ACTION "; break; // most are non-IBM
case DatabaseMetaData.importedKeyCascade: deleteRuleType = "CASCADE "; break;
case DatabaseMetaData.importedKeyRestrict: deleteRuleType = "RESTRICT "; break; // always for IBM DBMs
default: deleteRuleType = "SET NULL "; break;
}
pkColName = rs.getString (4);
fkSchema = rs.getString (6);
fkTable = rs.getString (7);
fkColName = rs.getString (8);
System.out.println (" " + pkColName + " " + fkSchema + "." + fkTable + "." + fkColName);
System.out.println (" Update Rule: " + updateRuleType + ", Delete Rule: " + deleteRuleType);
pkName = rs.getString (13);
fkName = rs.getString (12);
if (pkName != null) { System.out.println (" Primary Key Name: " + pkName); }
if (fkName != null) { System.out.println (" Foreign Key Name: " + fkName); }
}
rs.close();
} catch (Exception e) { e.printStackTrace(); }
}
//
// listTablePriv
// - schemaName and tableName should be exact strings, NOT search patterns
//
public static void listTablePriv (Connection con, String schemaName, String tableName) {
try {
DatabaseMetaData dmd = con.getMetaData();
ResultSet rs = dmd.getTablePrivileges (null, schemaName, tableName);
System.out.println ("\nCurrent User's Privileges for: " + schemaName + "." + tableName);
System.out.println (" Grantor Grantee Privilege Grantable");
System.out.println (" --------------- ----------------- ---------- ---");
String grantor = new String("");
String grantee = new String("");
String privilege = new String("");
String grantable = new String("");
while (rs.next()) {
// retrieve data and output necessary information
grantor = rs.getString (4);
grantee = rs.getString (5);
privilege = rs.getString (6);
grantable = rs.getString (7);
System.out.println (" " + Tools.padLength(grantor, 16) + Tools.padLength(grantee, 18) + Tools.padLength(privilege, 11) + grantable);
}
rs.close();
} catch (Exception e) { e.printStackTrace(); }
}
//
// listColPriv
// - schemaName and tableName should be exact strings, NOT search patterns
//
public static void listColPriv (Connection con, String schemaName, String tableName) {
try {
DatabaseMetaData dmd = con.getMetaData();
System.out.println ("Enter Search Pattern for Column Name:");
String colSearchPat = Tools.readString();
// search using column privileges for all columns under search pattern
ResultSet rs = dmd.getColumnPrivileges (null, schemaName, tableName, colSearchPat);
String colName = new String("");
String colOld = new String("");
String grantor = new String("");
String grantee = new String("");
String privilege = new String("");
String grantable = new String("");
while (rs.next()) {
// retrieve data and display under formated output
colName = rs.getString (4);
if (!colName.equals(colOld)) {
System.out.println ("\n Column: " + colName + "\n");
System.out.println (" Grantor Grantee Privilege Grantable");
System.out.println (" --------------- ----------------- ---------- ---");
colOld = colName;
}
grantor = rs.getString (5);
grantee = rs.getString (6);
privilege = rs.getString (7);
grantable = rs.getString (8);
System.out.println (" " + Tools.padLength(grantor, 16) + Tools.padLength(grantee, 18) + Tools.padLength(privilege, 11) + grantable);
}
rs.close(); // close statement handle
} catch (Exception e) { e.printStackTrace(); }
}
//
// listStats
// - schemaName and tableName should be exact strings, NOT search patterns
//
public static void listStats (Connection con, String schemaName, String tableName) {
try {
DatabaseMetaData dmd = con.getMetaData();
System.out.println ("\nStatistics for " + schemaName + "." + tableName);
ResultSet rs = dmd.getIndexInfo (null, schemaName, tableName, true, true);
String colName = new String("");
String indexName = new String("");
int card = 0;
int pages = 0;
while (rs.next()) {
// if the data refers to the table not the columns indicate as such
if (rs.getShort(7) == DatabaseMetaData.tableIndexStatistic) {
System.out.println (" Table Statistics:");
}
// retrieve data and output to screen
else {
colName = rs.getString (9);
indexName = rs.getString (6);
System.out.println (" Columns: " + Tools.padLength(colName, 19) + "Index Name: " + indexName);
}
card = rs.getInt (11);
pages = rs.getInt (12);
System.out.println (" Cardinality: " + card + "\t\tPages: " + pages);
}
rs.close();
} catch (Exception e) { e.printStackTrace(); }
}
}