//************************************************************************************
//
// Copyright (c) 2006-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20207JD.java
// Header: none
// Purpose: Demonstrate FastLoad of data rows with auto-commit false
// and verify FastLoad sessions.
//
// The program will:
// - Connect as user guest/please
// - Insert data rows into a table using FastLoad with auto-commit false
// - Verify the updateCount returned by executeBatch
// - Verify that no SQLWarning has occurred
// - Verify the sessions used to FastLoad
// - Verify that the correct number of data rows were inserted (fastloaded)
// - Disconnect
//
// JDBC API: java.sql.Connection, java.sql.PreparedStatement,
// java.sql.PreparedStatement.executeBatch
//
// Version: new sample
//
//************************************************************************************
import java.io.*;
import java.sql.*;
import java.util.Arrays;
/**
* FastLoad with auto-commit false and verify FastLoad sessions.
*/
public class T20207JD
{
// Name of the user able to create and drop tables
public static String user = "guest";
public static String password = "please";
public static void main(String args[])
throws ClassNotFoundException
{
// URL Parameter for SESSIONS
int sessions = 1;
// URL to be passed to the JDBC driver
String url =
"jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8,TYPE=FASTLOAD,SESSIONS="
+ sessions;
// Size of CHAR column
int strLen = 100;
// Name of sample table
String tableName = "T20207JD";
// CREATE TABLE statement
String createTable = "CREATE TABLE " + tableName + "," +
"NO FALLBACK," +
"NO BEFORE JOURNAL," +
"NO AFTER JOURNAL," +
"CHECKSUM = DEFAULT" +
"(" +
" L_INDEX INTEGER NOT NULL," +
" L_TEXT CHAR(" + strLen + ")" +
" CHARACTER SET UNICODE NOT CASESPECIFIC" +
") " +
"UNIQUE PRIMARY INDEX ( L_INDEX ";
// INSERT statement
String insertTable = "INSERT INTO " + tableName + " VALUES(?,?)";
// SELECT statement
String selectTable = "SELECT COUNT(*) FROM " + tableName;
try {
System.out.println("Sample T20207JD 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...");
Connection con = DriverManager.getConnection(url, user, password);
System.out.println(" Connection to Teradata established.");
try {
System.out.println(" Creating a Statement object...");
Statement stmt = con.createStatement();
System.out.println(" Created a Statement object.");
try {
try {
stmt.executeUpdate("DROP TABLE " + tableName);
} catch (SQLException e) {
// Do nothing because the table likely does not exist yet!
System.out.println(" Drop table exception ignored: " + e);
}
System.out.println(" Creating table " + tableName + "...");
stmt.executeUpdate(createTable);
System.out.println(" Created table " + tableName + ".");
System.out.println(" Creating a PreparedStatement object...");
PreparedStatement pstmt = con.prepareStatement(insertTable);
System.out.println(" Created a PreparedStatement object...");
try {
int numRows = 0;
char[] charBuf = new char[strLen];
Arrays.fill(charBuf, 'a');
String strBuf = new String(charBuf);
con.setAutoCommit(false);
System.out.println(" Binding data rows...");
int batchCount = 0;
pstmt.setInt(1, 1);
pstmt.setString(2, strBuf);
pstmt.addBatch();
batchCount++;
pstmt.setInt(1, 2);
pstmt.setString(2, strBuf);
pstmt.addBatch();
batchCount++;
System.out.println(" Inserting data rows...");
int updateCounts[] = pstmt.executeBatch();
if (updateCounts == null) {
System.out.println(
"ERROR: A null update count was returned!");
} else {
if (updateCounts.length != batchCount) {
System.out.println(
"WARNING: The update count does not match the"
+ " number of rows batched: expected "
+ batchCount + ", got " + updateCounts.length);
}
for (int i = 0; i < updateCounts.length; i++) {
if (updateCounts[i] != 1) {
System.out.println(
"WARNING: The update count for row " + (i+1)
+ " failed: expected 1, got "
+ updateCounts[i]);
}
}
numRows += updateCounts.length;
}
SQLWarning w = pstmt.getWarnings();
while (w != null) {
System.out.println("*** SQLWarning caught ***");
StringWriter sw = new StringWriter();
w.printStackTrace(new PrintWriter(sw, true));
System.out.println("SQL State = " + w.getSQLState()
+ ", Error Code = " + w.getErrorCode()
+ "" + sw.toString());
w = w.getNextWarning();
}
// Note that FastLoad sessions can only be verified while
// FastLoad is active with auto-commit set to false,
// i.e. a setXXX method must have been called and commit
// must NOT have been performed yet!
verifyFastLoadSessions(con, pstmt.hashCode(), sessions);
con.commit();
// Note that at this point no further executeBatch is
// permitted unless all rows in the destination table are
// deleted!
con.setAutoCommit(true);
System.out.println(" Selecting data rows...");
ResultSet rs = stmt.executeQuery(selectTable);
try {
int rowCount = 0;
ResultSetMetaData rsmd = rs.getMetaData();
if (rs.next()) {
rowCount = rs.getInt(1);
System.out.println(" " + tableName + " row "
+ rsmd.getColumnName(1) + "=" + rowCount);
}
if (rowCount != numRows) {
System.out.println(
"ERROR: Didn't retrieve enough rows: expected "
+ numRows + ", got " + rowCount);
}
} finally {
rs.close();
}
} catch (SQLException e) {
try {
System.out.println(
" Rolling back due to exception: " + e);
con.rollback();
} catch (SQLException ex) {
System.out.println(
" Chaining exception from rollback: " + ex);
e.setNextException(ex);
}
throw e;
} finally {
System.out.println(" Closing PreparedStatement object...");
pstmt.close();
System.out.println(" PreparedStatement object closed.");
}
} finally {
System.out.println(" Closing Statement object...");
stmt.close();
System.out.println(" Statement object closed.");
}
} finally {
System.out.println(" Closing Connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed.");
}
System.out.println("Sample T20207JD 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 verifyFastLoadSessions(Connection con, int hashCode,
int sessions)
throws SQLException
{
System.out.println(" Verifying FastLoad SESSIONS for PreparedStatement="
+ hashCode + "...");
// Note that the method for verifying FastLoad SESSIONS may change at any
// time without prior notice!
String amps = con.nativeSQL("{fn teradata_amp_count()}");
System.out.println(" AMP count = " + amps);
int consExpected = sessions < Integer.parseInt(amps) ? sessions
: Integer.parseInt(amps);
String lsnList = con.nativeSQL("{fn teradata_logon_sequence_number()}");
System.out.println(" LSN(s) = " + lsnList);
String lsnArray[] = lsnList.split(",");
if ((lsnArray.length & 1) == 1) {
System.out.println(
"ERROR: Expected an even count of delimited strings, indicating pairs"
+ " of PreparedStatement hash codes and LSNs, but received an odd"
+ " count instead!");
throw new IllegalStateException (
"Received invalid list from teradata_logon_sequence_number") ;
}
for (int i = 0; i < lsnArray.length; i += 2) {
int hashCodeForLSN = Integer.parseInt(lsnArray[i]);
int lsnForHashCode = Integer.parseInt(lsnArray[i+1]);
if (hashCodeForLSN == hashCode)
{
System.out.println(" LogonSequenceNo=" + lsnForHashCode
+ " for PreparedStatement=" + hashCode);
String selectSessionCount = "SELECT COUNT(*) FROM DBC.SessionInfo"
+ " WHERE Partition='FASTLOAD' AND LogonSequenceNo=?";
PreparedStatement pstmt1 = con.prepareStatement(selectSessionCount);
try {
byte bytes[] = new byte[4];
bytes[1] = (byte) ((lsnForHashCode >> 24) & 0xFF);
bytes[0] = (byte) ((lsnForHashCode >> 16) & 0xFF);
bytes[3] = (byte) ((lsnForHashCode >> 8) & 0xFF);
bytes[2] = (byte) (lsnForHashCode & 0xFF);
pstmt1.setBytes(1, bytes);
ResultSet rs1 = pstmt1.executeQuery();
try {
int consFound = 0;
if (rs1.next()) {
ResultSetMetaData rsmd = rs1.getMetaData();
consFound = rs1.getInt(1);
System.out.println(" FastLoadConnection session "
+ rsmd.getColumnName(1) + "=" + consFound
+ " for LogonSequenceNo=" + lsnForHashCode);
}
if (consFound != consExpected) {
System.out.println(
"ERROR: Did not detect a correct count of"
+ " FastLoadConnection session(s): expected "
+ consExpected + ", got " + consFound);
}
} finally {
rs1.close();
}
String selectSessionNums =
"SELECT SessionNo,Partition FROM DBC.SessionInfo"
+ " WHERE LogonSequenceNo=?"
+ " ORDER BY SessionNo,Partition";
PreparedStatement pstmt2 =
con.prepareStatement(selectSessionNums);
try {
pstmt2.setBytes(1, bytes);
ResultSet rs2 = pstmt2.executeQuery();
try {
ResultSetMetaData rsmd = rs2.getMetaData();
while (rs2.next()) {
System.out.println(" Found " + rsmd.getColumnName(2)
+ "=" + rs2.getObject(2) + " with "
+ rsmd.getColumnName(1) + "=" + rs2.getObject(1)
+ " for LogonSequenceNo=" + lsnForHashCode);
}
} finally {
rs2.close();
}
} finally {
pstmt2.close();
}
} finally {
pstmt1.close();
}
break;
}
}
}
}
|
|
|
|