J
Jose_Manuel_Jurado
In the context of a recent service request, a customer encountered connectivity and execution issues while interacting with Very Large Databases (VLDB) using Java and JDBC. The customer's operation involves reading chunks of rows, processing the data, and subsequently reading the next set of rows until the end of the database. This article reviews a custom script designed to alleviate these issues, highlighting the TCP Keep-Alive mechanism, which can be observed using network tools.
Introduction
The provided script shows critical considerations including reconnection policy, adaptive command timeout during execution, customizable Fetch Size, and illustrates TCP Keep Alive functionality. These features are invaluable for users working with Very Large Databases (VLDBs) using Java and JDBC, ensuring resilient and efficient data retrieval and processing in ETL process.
1. Reconnection Policy:
2. Adaptive Command Timeout:
3. Fetch Size Customization & Pause Illustration:
4. TCP Keep Alive Illustration:
Conclusion
package testconnectionms;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
public class SQLTest1
{
static {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (Exception ex) {
System.err.println("Unable to load JDBC driver");
ex.printStackTrace();
System.exit(1);
}
}
private static final String USERNAME = "UserName";
private static final String PASSWORD = "Password!";
private static final String URL = "jdbc:sqlserver://servername.database.windows.net;database=dbname;sslProtocol=TLSv1.2;loginTimeout=30;PacketSize=8096";
public static void main(String[] args)
throws SQLException
{
Connection connection = connectToDatabase(URL, USERNAME, PASSWORD);
if (connection == null) {
System.exit(1);
}
performQuery(connection, 120, 1000000, "select * from table1",1000000,60*2);
}
private static Connection connectToDatabase(String url, String username, String password) {
Connection connection = null;
int retryCount = 3;
while (retryCount > 0) {
try {
System.out.println("-- Connecting to " + url);
connection = DriverManager.getConnection(url, username, password);
break;
} catch (SQLException e) {
System.err.println("Failed to connect to database. Retrying...");
e.printStackTrace();
retryCount--;
if (retryCount == 0) {
System.err.println("Unable to connect to database after multiple attempts.");
return null;
}
}
try {
Thread.sleep(5000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
return connection;
}
private static ResultSet executeQueryWithRetries(Statement st, String query, int initialQueryTimeout) {
int retryCount = 3;
int queryTimeout = initialQueryTimeout;
ResultSet rs = null;
while (retryCount > 0) {
try {
st.setQueryTimeout(queryTimeout);
rs = st.executeQuery(query);
break;
} catch (SQLException e) {
System.err.println("Failed to execute query. Retrying...");
e.printStackTrace();
retryCount--;
queryTimeout += 30;
if (retryCount == 0) {
System.err.println("Unable to execute query after multiple attempts.");
return null;
}
}
}
return rs;
}
private static void performQuery(Connection connection, int initialQueryTimeout, int iFetchSize, String sSQL, int iChunk, int iHowManySeconds) {
try (Statement st = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
st.setFetchSize(iFetchSize);
ResultSet rs = executeQueryWithRetries(st, sSQL, initialQueryTimeout);
if (rs != null) {
processResultSet(rs, iChunk, iHowManySeconds);
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("Finished");
}
private static void processResultSet(ResultSet rs,int iChunk, int iHowManySeconds) throws SQLException {
int count = 0;
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (rs != null && rs.next()) {
count++;
for (int i = 1; i <= columnCount; i++)
{ System.out.print(rs.getString(i) + " ");}
System.out.println(" ");
if (count % iChunk == 0) {
try {
System.out.println("Processed batch of " + iChunk + " records " + count);
ShowDateNow(iHowManySeconds,"Next execution: ");
Thread.sleep(iHowManySeconds * 1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
}
private static void ShowDateNow(long numberSecondsToAdd,String sMsg) {
LocalDateTime fechaHoraActual = LocalDateTime.now().plusSeconds(numberSecondsToAdd);
DateTimeFormatter formato = DateTimeFormatter.ofPattern("dd-MM-yyyy HH:mm:ss");
String fechaHoraFormateada = fechaHoraActual.format(formato);
System.out.println( sMsg + " " + fechaHoraFormateada);
}
}
Continue reading...
Introduction
The provided script shows critical considerations including reconnection policy, adaptive command timeout during execution, customizable Fetch Size, and illustrates TCP Keep Alive functionality. These features are invaluable for users working with Very Large Databases (VLDBs) using Java and JDBC, ensuring resilient and efficient data retrieval and processing in ETL process.
1. Reconnection Policy:
1.1 Objective:
Sustain continuous database connectivity.
1.2 Implementation & Rationale:
The script is designed to attempt reconnection to the database three times with intervals. This mechanism significantly improves the robustness of the connection process, accommodating transient connectivity issues without user intervention.
2. Adaptive Command Timeout:
2.1 Objective:
Flexible and resilient query execution.
2.2 Implementation & Rationale:
The script adaptively increases the command timeout during the query execution phase. This adaptive approach ensures that the query has sufficient time to complete, accounting for variable execution times, particularly with VLDBs.
3. Fetch Size Customization & Pause Illustration:
3.1 Objective:
Demonstrate connection persistence during data fetching and processing pauses.
3.2 Implementation & Rationale:
- The script allows Fetch Size customization, enhancing its adaptability to various data sizes and system resources.
- For illustration, after reading a specified number of rows, the script introduces a pause. During this pause, it’s crucial to observe how the connection remains active, making it a practical demonstration of real-world long-running database operations.
4. TCP Keep Alive Illustration:
4.1 Objective:
Showcase TCP Keep Alive’s role in maintaining active connections.
4.2 Implementation & Rationale:
- During the script-induced pause, TCP Keep Alive activates, ensuring the connection stays live.
- Users can monitor this using network tools to see TCP Keep Alive packets being sent and acknowledged, underscoring the stability and reliability of the connection even during periods of inactivity.
Conclusion
The script serves as a practical guide, spotlighting several pivotal considerations and strategies for managing connectivity when working with Java, JDBC, and VLDBs. Through intelligent reconnection policies, adaptive command timeouts, Fetch Size customization, and illustrative pauses, users gain insight into maintaining and observing active, resilient database connections, even in the context of extensive operations and intentional delays. Understanding each of these functionalities is imperative for users seeking to navigate and mitigate connectivity and execution challenges effectively.
package testconnectionms;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
public class SQLTest1
{
static {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (Exception ex) {
System.err.println("Unable to load JDBC driver");
ex.printStackTrace();
System.exit(1);
}
}
private static final String USERNAME = "UserName";
private static final String PASSWORD = "Password!";
private static final String URL = "jdbc:sqlserver://servername.database.windows.net;database=dbname;sslProtocol=TLSv1.2;loginTimeout=30;PacketSize=8096";
public static void main(String[] args)
throws SQLException
{
Connection connection = connectToDatabase(URL, USERNAME, PASSWORD);
if (connection == null) {
System.exit(1);
}
performQuery(connection, 120, 1000000, "select * from table1",1000000,60*2);
}
private static Connection connectToDatabase(String url, String username, String password) {
Connection connection = null;
int retryCount = 3;
while (retryCount > 0) {
try {
System.out.println("-- Connecting to " + url);
connection = DriverManager.getConnection(url, username, password);
break;
} catch (SQLException e) {
System.err.println("Failed to connect to database. Retrying...");
e.printStackTrace();
retryCount--;
if (retryCount == 0) {
System.err.println("Unable to connect to database after multiple attempts.");
return null;
}
}
try {
Thread.sleep(5000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
return connection;
}
private static ResultSet executeQueryWithRetries(Statement st, String query, int initialQueryTimeout) {
int retryCount = 3;
int queryTimeout = initialQueryTimeout;
ResultSet rs = null;
while (retryCount > 0) {
try {
st.setQueryTimeout(queryTimeout);
rs = st.executeQuery(query);
break;
} catch (SQLException e) {
System.err.println("Failed to execute query. Retrying...");
e.printStackTrace();
retryCount--;
queryTimeout += 30;
if (retryCount == 0) {
System.err.println("Unable to execute query after multiple attempts.");
return null;
}
}
}
return rs;
}
private static void performQuery(Connection connection, int initialQueryTimeout, int iFetchSize, String sSQL, int iChunk, int iHowManySeconds) {
try (Statement st = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
st.setFetchSize(iFetchSize);
ResultSet rs = executeQueryWithRetries(st, sSQL, initialQueryTimeout);
if (rs != null) {
processResultSet(rs, iChunk, iHowManySeconds);
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("Finished");
}
private static void processResultSet(ResultSet rs,int iChunk, int iHowManySeconds) throws SQLException {
int count = 0;
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (rs != null && rs.next()) {
count++;
for (int i = 1; i <= columnCount; i++)
{ System.out.print(rs.getString(i) + " ");}
System.out.println(" ");
if (count % iChunk == 0) {
try {
System.out.println("Processed batch of " + iChunk + " records " + count);
ShowDateNow(iHowManySeconds,"Next execution: ");
Thread.sleep(iHowManySeconds * 1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
}
private static void ShowDateNow(long numberSecondsToAdd,String sMsg) {
LocalDateTime fechaHoraActual = LocalDateTime.now().plusSeconds(numberSecondsToAdd);
DateTimeFormatter formato = DateTimeFormatter.ofPattern("dd-MM-yyyy HH:mm:ss");
String fechaHoraFormateada = fechaHoraActual.format(formato);
System.out.println( sMsg + " " + fechaHoraFormateada);
}
}
Continue reading...