Tabelspace Informations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stefan - 01 Jun 2006 10:28 GMT

Hi,
is there a way to get informations from tabelspaces of a db2
(datafiles) via java (jdbc)?

Thanx...
Stefan

Reply to this Message

Peri - 01 Jun 2006 11:03 GMT

Stefan,
snapshot_tbs & snapshot_tbs_cfg are the table function u might want to
look at

For more information
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/admin/r0007101.htm

> Hi,
> is there a way to get informations from tabelspaces of a db2
> (datafiles) via java (jdbc)?
>
> Thanx...
> Stefan

Reply to this Message

Rhino - 01 Jun 2006 19:30 GMT

> Stefan,
> snapshot_tbs & snapshot_tbs_cfg are the table function u might want to
> look at
>
> For more information
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/admin/r0007101.htm

Assuming you are using DB2 for Windows/Linux/Unix version 8, the following
short program illustrates how you could get the information from the
snapshot_tbs table function via a Java program:

---------------------------------------------------------------------------------------------------------------------
package com.foo.db2v8;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
* @version 1.0
*
* 
* Demonstrate use of DB2 table functions for getting information via
Java/JDBC.
* 
*
* 
* This program:
* <ul>
* <li>loads the JDBC Driver used to access DB2. In order for the driver to
be
* loaded successfully, the appropriate zip or jar file containing the
driver
* needs to be visible during the compile/execution of this program. The
files containing
* the JDBC drivers are in the sqllib\java directory.
* <li>connects to the DB2 Sample database
* <li>queries the database via a table function and displays the results of
the function
* </ul>
* 
*
* 
* All results are displayed on the Java console to simplify the code; a
real
* program would more likely display information in a GUI (Graphical User
* Interface). When needed, input is obtained from the command line; a real
* program would more likely obtain input from a GUI.
* 
*
* 
* All of the SQL statements executed in this program used
java.sql.Statement
* objects. In other words, none of them use parameter markers ('?' symbol)
to
* indicate variables; all variable values are hard-coded right in the
program.
* 
*
* 
* This program assumes that no nulls are sent or returned by any SQL
statement.
* 
*
* 
* Error handling is used throughout the program to ensure that the program
* terminates with a meaningful message if something didn't work correctly.
The
* following try/catch block is very useful in obtaining diagnostic
information
* from JDBC, which gets it from your database:
* 
* <xmp>
* try {
* //your SQL statement
* }
* catch (SQLException sql_excp) {
* System.err.println("SQLState: " + sql_excp.getSQLState());
* System.err.println("SQLCode: " + sql_excp.getErrorCode());
* System.err.println("Message: " + sql_excp.getMessage());
* }
* </xmp>
*
* 
* A single connection and a single thread is used for the entire life of
this
* program. In a multi-user environment, this program might use a connection
* pool to ensure sufficient users could run this program simultaneously. If
the
* program had to do several things in parallel, additional threads could be
* used.
* 
*
* 
* This program leaves "autocommit" on so that all commits are done
automatically. Other
* approaches are possible and may be preferable, depending on your
circumstances.
* 
*
* 
* Variables that are only used in one method of this application are
defined as
* method variables; all other variables are defined as class variables.
Other
* approaches are possible and may be preferable, depending on your
* circumstances.
* 
*/
public class JDBC04 {

/*
*
* Class variables.
*
*/
/** The name of this class. */
final String CLASS_NAME = getClass().getName();

/*
*
* Instance variables.
*
*/
/** The database connection used by this program. */
Connection conn01 = null;

/**
* main method for this application.
*/
public static void main(String[] args) {

/* Validate the command-line arguments, if any. */
if (args.length != 0) {
System.err.println("This program should not receive any
command-line arguments but it received these " + args.length + " arguments:
"); //$NON-NLS-1$ //$NON-NLS-2$
for (String oneArg : args) {
System.err.println(" " + oneArg); //$NON-NLS-1$
}
System.err.println("These arguments were ignored.");
//$NON-NLS-1$
}

new JDBC04();
}

/**
* Constructor for this class.
*/
public JDBC04() {

System.out.println("Welcome to " + this.CLASS_NAME + "!\n");
//$NON-NLS-1$ //$NON-NLS-2$

loadDriver();
connectToDatabase();
queryTable();
closeConnection();
}

/**
* Load the JDBC driver.
*/
public void loadDriver() {

String METHOD_NAME = "loadDriver()"; //$NON-NLS-1$

/* Initialize the variable that contains the name of the JDBC
driver. */
String jdbcDriverName = "com.ibm.db2.jcc.DB2Driver"; //$NON-NLS-1$

/* Load the JDBC driver. */
try {
Class.forName(jdbcDriverName);
} catch (ClassNotFoundException cnf_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME + " -
Encountered ClassNotFoundException while attempting to load JDBC driver " +
jdbcDriverName + ". Message: " + cnf_excp.getMessage()); //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$
cnf_excp.printStackTrace();
System.exit(16);
}
}

/**
* Get a connection to the database.
*
*/
public void connectToDatabase() {

String METHOD_NAME = "connectToDatabase()"; //$NON-NLS-1$

/* Initialize the variables used to get the connection. */
String databaseName = "sample"; //$NON-NLS-1$
String url = "jdbc:db2:" + databaseName; //$NON-NLS-1$
String loginName = "db2admin"; //$NON-NLS-1$
String password = "db2admin"; //$NON-NLS-1$

/* Connect to the database. */
try {
this.conn01 = DriverManager.getConnection(url, loginName,
password);
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME + " -
Encountered SQLException on connect to URL " + url + ". Message: " +
sql_excp.getMessage()); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
sql_excp.printStackTrace();
System.exit(16);
}

/*
* Obtain JDBC version. NOTE: Only available in Java 1.4 and above.
*/
try {
DatabaseMetaData dbMeta = this.conn01.getMetaData();
System.out.println("JDBC Version: " +
dbMeta.getJDBCMajorVersion() + "." + dbMeta.getJDBCMinorVersion());
//$NON-NLS-1$ //$NON-NLS-2$
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME + " -
Encountered SQLException on attempt to turn autocommit off. Message: " +
sql_excp.getMessage()); //$NON-NLS-1$ //$NON-NLS-2$
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Display the tablespace information.
*/
public void queryTable() {

String METHOD_NAME = "queryTable()"; //$NON-NLS-1$

System.out.println("\nDisplay tablespace information: ");
//$NON-NLS-1$
String queryTableSQL = "select * from
table(snapshot_tbs('SAMPLE', -2)) as tf"; //$NON-NLS-1$

/*
* Query the demonstration table to get information about certain
* employees.
*/
Statement queryTableStmt = null;
ResultSet rs01 = null;
try {
queryTableStmt = this.conn01.createStatement();
rs01 = queryTableStmt.executeQuery(queryTableSQL);
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME + " -
Encountered SQLException while trying to get information about tablespaces.
Message: " + sql_excp); //$NON-NLS-1$ //$NON-NLS-2$
sql_excp.printStackTrace();
System.exit(16);
}

/*
* Print a title line above the result set. The static method pad()
is
* used to align the column titles and underlines.
*/
String spaces = " "; //$NON-NLS-1$
System.out.println(
pad("SNAPSHOT_TIMESTAMP", ' ', 'T', 26) + spaces +
//$NON-NLS-1$
pad("POOL_DATA_L_READS", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_DATA_P_READS", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_ASYNC_DATA_READS", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_DATA_WRITES", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_ASYNC_DATA_WRITES", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_INDEX_L_READS", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_INDEX_P_READS", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_INDEX_WRITES", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_ASYNC_INDEX_WRITES", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_READ_TIME", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_WRITE_TIME", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_ASYNC_READ_TIME", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_ASYNC_WRITE_TIME", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_ASYNC_DATA_READ_REQS", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("DIRECT_READS", ' ', 'T', 25) + spaces + //$NON-NLS-1$
pad("DIRECT_WRITES", ' ', 'T', 25) + spaces + //$NON-NLS-1$
pad("DIRECT_READ_REQS", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("DIRECT_WRITE_REQS", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("DIRECT_READ_TIME", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("DIRECT_WRITE_TIME", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("UNREAD_PREFETCH_PAGES", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_ASYNC_INDEX_READS", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_DATA_TO_ESTORE", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_INDEX_TO_ESTORE", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_INDEX_FROM_ESTORE", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("POOL_DATA_FROM_ESTORE", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("FILES_CLOSED", ' ', 'T', 25) + spaces + //$NON-NLS-1$
pad("TABLESPACE_NAME", ' ', 'T', 128) //$NON-NLS-1$
);
System.out.println(
pad("--------------------------", ' ', 'T', 26) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("-------------------------", ' ', 'T', 25) + spaces +
//$NON-NLS-1$
pad("---------------", ' ', 'T', 128) //$NON-NLS-1$
);

/*
* Print each line of the result set.
*/
try {
while (rs01.next()) {
System.out.println(
pad(rs01.getTimestamp("SNAPSHOT_TIMESTAMP").toString(),
' ', 'T', 26) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_DATA_L_READS")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_DATA_P_READS")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_ASYNC_DATA_READS")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_DATA_WRITES")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_ASYNC_DATA_WRITES")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_INDEX_L_READS")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_INDEX_P_READS")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_INDEX_WRITES")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_ASYNC_INDEX_WRITES")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_READ_TIME")), '
', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_WRITE_TIME")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_ASYNC_READ_TIME")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_ASYNC_WRITE_TIME")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_ASYNC_DATA_READ_REQS")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("DIRECT_READS")), '
', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("DIRECT_WRITES")), '
', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("DIRECT_READ_REQS")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("DIRECT_WRITE_REQS")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("DIRECT_READ_TIME")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("DIRECT_WRITE_TIME")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("UNREAD_PREFETCH_PAGES")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_ASYNC_INDEX_READS")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_DATA_TO_ESTORE")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_INDEX_TO_ESTORE")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_INDEX_FROM_ESTORE")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("POOL_DATA_FROM_ESTORE")),
' ', 'L', 25) + spaces + //$NON-NLS-1$
pad(Long.toString(rs01.getLong("FILES_CLOSED")), '
', 'L', 25) + spaces + //$NON-NLS-1$
pad(rs01.getString("TABLESPACE_NAME"), ' ', 'T',
128) //$NON-NLS-1$
);
}
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME + " -
Encountered SQLException while reading tablespace information. Message: " +
sql_excp); //$NON-NLS-1$ //$NON-NLS-2$
sql_excp.printStackTrace();
System.exit(16);
}

/* Close the result set and dispose of the statement. */
try {
rs01.close();
queryTableStmt.close();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME + " -
Encountered SQLException while closing result set or closing statement.
Message: " + sql_excp); //$NON-NLS-1$ //$NON-NLS-2$
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Close the database connection.
*/
public void closeConnection() {

String METHOD_NAME = "closeConnection()"; //$NON-NLS-1$

/* Close the database connection. */
try {
this.conn01.close();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME + " -
Encountered SQLException while closing database connection. Message: " +
sql_excp.getMessage()); //$NON-NLS-1$//$NON-NLS-2$
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Pad an input string with either leading or trailing occurrences of a
* given character until the string reaches a given length.
*
* @param input is the input String which is to be padded.
* @param padChar is the character which is to be used to pad the input
String.
* @param padPosition is the location of the padding, either leading or
trailing.
* @param finalLength is the final length of the padded String.
* @return the padded String.
*/
public static String pad(String input, char padChar, char padPosition,
int finalLength) {

String METHOD_NAME = "pad()"; //$NON-NLS-1$

/*
* Pad position must be 'l' or 'L' for "leading" or 't' or 'T' for
"trailing".
*/
if (padPosition != 'L' & padPosition != 'l' & padPosition != 'T' &
padPosition != 't') {
throw new IllegalArgumentException(METHOD_NAME + " - The pad
position requested, " + padPosition + ", is not equal to L, l, T, or t.");
//$NON-NLS-1$ //$NON-NLS-2$
}

/*
* Final length must be greater than or equal to the length of the
input string.
*/
if (finalLength < input.length()) {
throw new IllegalArgumentException(METHOD_NAME + " - The final
length requested, " + finalLength + ", is less than the length, " +
input.length() + ", of the input string, " + input + "."); //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
}

/*
* Determine the number of occurrences of the pad character that
will
* have to be added to the input string.
*/
int numPadChars = finalLength - input.length();

/*
* If the pad position is 'L' or 'l' for "leading", write the pad
* characters followed by the input string. Otherwise, write the pad
* characters after the input string.
*/
StringBuffer strbuffPaddedString = new StringBuffer();
if (padPosition == 'L' | padPosition == 'l') {
for (int ix = 0; ix < numPadChars; ix++)
strbuffPaddedString.append(padChar);
strbuffPaddedString.append(input);
} else {
strbuffPaddedString.append(input);
for (int ix = 0; ix < numPadChars; ix++)
strbuffPaddedString.append(padChar);
}

return (strbuffPaddedString.toString());
}
}---------------------------------------------------------------------------------------------------------------------

>> Hi,
>> is there a way to get informations from tabelspaces of a db2
>> (datafiles) via java (jdbc)?
>>
>> Thanx...
>> Stefan

Reply to this Message

Rhino - 01 Jun 2006 19:37 GMT

>> Hi,
>> is there a way to get informations from tabelspaces of a db2
>> (datafiles) via java (jdbc)?
>>
>> Thanx...
>> Stefan

Reply to this Message

Rhino - 01 Jun 2006 19:27 GMT

> Hi,
> is there a way to get informations from tabelspaces of a db2
> (datafiles) via java (jdbc)?

Quite a lot of information about DB2 is available via Java/JDBC but I'm not
sure if tablespace information is available. What specific information do
you want Java/JDBC to get for you?

Also, what version of DB2 and what operating system are you using?

--
Rhino

Reply to this Message