Java 1.8 batch job process freezing in Teradata as queries are pull from Oracle

Connectivity
Connectivity covers the mechanisms for connecting to the Teradata Database, including driver connectivity via JDBC or ODBC.
Highlighted

Java 1.8 batch job process freezing in Teradata as queries are pull from Oracle

Teradata Community,

 

I've a Java 1.8 batch job that interacts first with Oracle 12c and then with Teradata version 15.10.00.37 using tdgssconfig.jar & terajdbc.jar to get more data from Teradata into Oracle.  The queries to execute in Teradata are getting pull from Oracle.  I have tried with below different Terada driver versions but I still get the same result:
<> tdgssconfig-16.20.00.06.jar & terajdbc4-16.20.00.06.jar
<> tdgssconfig-15.10.00.37.jar & terajdbc4-15.10.00.37.jar
<> tdgssconfig-14.10.00.01.jar & terajdbc4-14.10.00.39.jar
<> tdgssconfig-14.10.00.01.jar & terajdbc4-14.10.00.14.jar

Everything processes fine for all batches; except for three different types of alerts the process gets hung or stuck after executing several queries against Teradata.  I am trying to identify why the Java 1.8 batch job process gets hung or stuck when it executes a query against Teradata but after successfully executing several others.  

Note this same batch job works fine with Java 1.7 using tdgssconfig-14.10.00.01.jar & terajdbc4-14.10.00.39.jar

Has anyone in the Java/Teradata community have experience a similar issue that can provide some insight regarding this issue?
Do any of you know how to enable the tdgssconfig.jar & terajdbc.jar JDBC tracing?

Thanks in advance.

6 REPLIES 6
Teradata Employee

Re: Java 1.8 batch job process freezing in Teradata as queries are pull from Oracle


Do any of you know how to enable the tdgssconfig.jar & terajdbc.jar JDBC tracing?

Use the Teradata JDBC Driver's LOG connection parameter to enable debug logging. Documentation for the LOG connection parameter is here:

 

https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html...

Re: Java 1.8 batch job process freezing in Teradata as queries are pull from Oracle

I enable the Teradata JDBC tracing and as per the Teradata JDBC logs after completing executeStatement() method in JDK6_SQL_Connection class it is printing executeStatement() exit log and after that control is not coming back from Teradata to my Java 1.8 batch job.  Below are the logs printed at last after that logs is not rolling.

 

2019-06-24 12:13:35 DEBUG - 2019-06-24.12:13:35.246 TERAJDBC4 DEBUG [pool-7-thread-6] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad Thread pool-7-thread-6 now owns lock: StatementStateLock

2019-06-24 12:13:35 DEBUG - 2019-06-24.12:13:35.246 TERAJDBC4 DEBUG [pool-7-thread-6] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad Thread pool-7-thread-6 now owns lock: StatementStateLock

2019-06-24 12:13:35 DEBUG - 2019-06-24.12:13:35.246 TERAJDBC4 DEBUG [pool-7-thread-6] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad StatementState.setState enclosing instance=com.teradata.jdbc.jdk6.JDK6_SQL_Statement@131f4f1d(statecode=1 sess=com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad)

2019-06-24 12:13:35 DEBUG - 2019-06-24.12:13:35.246 TERAJDBC4 DEBUG [pool-7-thread-6] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad StatementState.setState enclosing instance=com.teradata.jdbc.jdk6.JDK6_SQL_Statement@131f4f1d(statecode=1 sess=com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad)

2019-06-24 12:13:35 DEBUG - 2019-06-24.12:13:35.246 TERAJDBC4 DEBUG [pool-7-thread-6] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad Thread pool-7-thread-6 attempt to unlock StatementStateLock

2019-06-24 12:13:35 DEBUG - 2019-06-24.12:13:35.246 TERAJDBC4 DEBUG [pool-7-thread-6] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad Thread pool-7-thread-6 attempt to unlock StatementStateLock

2019-06-24 12:13:35 DEBUG - 2019-06-24.12:13:35.246 TERAJDBC4 DEBUG [pool-7-thread-6] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad Thread pool-7-thread-6 attempt to unlock SessionLock

2019-06-24 12:13:35 DEBUG - 2019-06-24.12:13:35.246 TERAJDBC4 DEBUG [pool-7-thread-6] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad Thread pool-7-thread-6 attempt to unlock SessionLock

2019-06-24 12:13:35 DEBUG - 2019-06-24.12:13:35.246 TERAJDBC4 DEBUG [pool-7-thread-6] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad executeStatement() exit

2019-06-24 12:13:35 DEBUG - 2019-06-24.12:13:35.246 TERAJDBC4 DEBUG [pool-7-thread-6] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad executeStatement() exit

 

 

I have verified the logs for previous executeStatement() method and as per the logs once this executeStatement() method completed in JDK6_SQL_Connection class its printing below teradata log but in above scenario I don’t see below log and it’s hanged.

 

2019-06-24 12:13:35 DEBUG - 2019-06-24.12:13:35.016 TERAJDBC4 DEBUG [pool-7-thread-6] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@84b60ad Results.moreResults this=com.te

radata.jdbc.jdbc_4.ifsupport.Results@7f80efca(curidx=0 rsize=1 results=[com.teradata.jdbc.jdbc_4.ifsupport.Result@9956af(updc=55)] ctlr=null complete=true)


2019-06-24 10:21:01 DEBUG - 2019-06-24.10:21:01.837 TERAJDBC4 INFO [pool-11-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032 Exception caught from request execution begin-log-stack-trace>>> java.sql.SQLException: [Teradata Database] [TeraJDBC 15.10.00.14] [Error 3807] [SQLState 42S02] Object '<Teradata.Table>' does not exist.

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:308)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:308)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:103)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:103)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:311)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:311)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:200)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:200)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:137)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:137)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.statemachine.StatementController.run(StatementController.java:128)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.statemachine.StatementController.run(StatementController.java:128)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:387)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:387)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:329)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:329)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.TDStatement.doNonPrepExecute(TDStatement.java:292)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.TDStatement.doNonPrepExecute(TDStatement.java:292)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.TDStatement.execute(TDStatement.java:1121)

2019-06-24 10:21:01 DEBUG -     at com.teradata.jdbc.jdbc_4.TDStatement.execute(TDStatement.java:1121)

.......
my code establishing a connection with Teradata and executing a query and then closing the Teradata connection and doing it all over again for the next query
java.sql.Statement = java.sql.Connection.createStatement();   // in rt.jar
java.sql.Statement.execute(sql);  // where sql is the query, it could be select, create multiset table, delete, etc.
java.sql.Statement.close();
................

2019-06-24 10:21:01 DEBUG -     at java.util.concurrent.FutureTask.run(FutureTask.java:266)

2019-06-24 10:21:01 DEBUG -     at java.util.concurrent.FutureTask.run(FutureTask.java:266)

2019-06-24 10:21:01 DEBUG -     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

2019-06-24 10:21:01 DEBUG -     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

2019-06-24 10:21:01 DEBUG -     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

2019-06-24 10:21:01 DEBUG -     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

2019-06-24 10:21:01 DEBUG -     at java.lang.Thread.run(Thread.java:748)

2019-06-24 10:21:01 DEBUG -     at java.lang.Thread.run(Thread.java:748)

2019-06-24 10:21:01 DEBUG -  <<<end-log-stack-trace

2019-06-24 10:21:01 DEBUG -  <<<end-log-stack-trace

2019-06-24 10:21:01 DEBUG - 2019-06-24.10:21:01.837 TERAJDBC4 DEBUG [pool-11-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032 Thread pool-11-thread-1 now owns lock: StatementStateLock

2019-06-24 10:21:01 DEBUG - 2019-06-24.10:21:01.837 TERAJDBC4 DEBUG [pool-11-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032 Thread pool-11-thread-1 now owns lock: StatementStateLock

2019-06-24 10:21:01 DEBUG - 2019-06-24.10:21:01.837 TERAJDBC4 DEBUG [pool-11-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032 StatementState.setState enclosing instance=com.teradata.jdbc.jdk6.JDK6_SQL_Statement@7bf86b4f(statecode=1 sess=com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032)

2019-06-24 10:21:01 DEBUG - 2019-06-24.10:21:01.837 TERAJDBC4 DEBUG [pool-11-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032 StatementState.setState enclosing instance=com.teradata.jdbc.jdk6.JDK6_SQL_Statement@7bf86b4f(statecode=1 sess=com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032)

2019-06-24 10:21:01 DEBUG - 2019-06-24.10:21:01.837 TERAJDBC4 DEBUG [pool-11-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032 Thread pool-11-thread-1 attempt to unlock StatementStateLock

2019-06-24 10:21:01 DEBUG - 2019-06-24.10:21:01.837 TERAJDBC4 DEBUG [pool-11-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032 Thread pool-11-thread-1 attempt to unlock StatementStateLock

2019-06-24 10:21:01 DEBUG - 2019-06-24.10:21:01.837 TERAJDBC4 DEBUG [pool-11-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032 Thread pool-11-thread-1 attempt to unlock SessionLock

2019-06-24 10:21:01 DEBUG - 2019-06-24.10:21:01.837 TERAJDBC4 DEBUG [pool-11-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032 Thread pool-11-thread-1 attempt to unlock SessionLock

2019-06-24 10:21:01 DEBUG - 2019-06-24.10:21:01.837 TERAJDBC4 DEBUG [pool-11-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032 executeStatement() exit

2019-06-24 10:21:01 DEBUG - 2019-06-24.10:21:01.837 TERAJDBC4 DEBUG [pool-11-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@496d3032 executeStatement() exit




Have any of you experience this issue?

Teradata Employee

Re: Java 1.8 batch job process freezing in Teradata as queries are pull from Oracle

The log shows that your application is getting the following error:

 

Teradata Database Error 3807 Object '<Teradata.Table>' does not exist.

 

 

Your app has a placeholder string <Teradata.Table> that is supposed to be replaced with an actual table name, but the replacement did not happen, so the placeholder name is being sent to the Teradata Database in the SQL request text.

Re: Java 1.8 batch job process freezing in Teradata as queries are pull from Oracle

The placeholder string '<Teradata.Table>' is getting replaced, I just did not share the actual database and table name for privacy.  So it is getting replace, and the database and table exist in the Teradata database so what would be the problem.  This is not where the issue happens.

The Java 1.8 batch job executes several queries (create conn, execute sql statement, close conn) successfully in Teradata but then for certain batches as described in the initial/original post after executing several queries, the last query to execute (insert or delete) the Java 1.8 batch establishes the conn and then executes the sql statement and then we never hear back from Teradata.  It seems like Teradata is waiting to hear back from the Java 1.8 batch job and the Java 1.8 batch job is waiting for Teradata to complete the execution of the query and reply back with the response, we are in like a deadlock.  It seems like the handshake is not happening between the Java 1.8 batch job and Teradata for this last query after executing several (25 or 26 queries) of them previously.  Note that this works fine for other batches with thousands of records to process.  When we kill the Java 1.8 batch job in the logs we can see a response back from Teradata and we see an SQLException.

This is not an issue with our Java 1.7 batch job.  The Java 1.7 batch job uses Drools 5.4.0 and the Java 1.8 batch job uses Drools 6.5.0 (now known as KIE) and newer dependencies to conform to Java 1.8; but everything else is the same.  Not functionality or flow changes.

Teradata Employee

Re: Java 1.8 batch job process freezing in Teradata as queries are pull from Oracle

@gecastiThanks for the additional information. This looks more complex than something we can troubleshoot here in a forum thread. Please open an incident with Teradata Customer Serivce.

Re: Java 1.8 batch job process freezing in Teradata as queries are pull from Oracle

@tomnolan - I will, thank you for the help so far.