Connecting to Teradata is very solwly(5 sec)

Connectivity
Enthusiast

Connecting to Teradata is very solwly(5 sec)

That's my code below, I connected to Teradata used java JDBC but It took 5 sec to do "stmt = conn.createStatement();".

Could anyone help me to fix this problem?

CODE

public class dw_targetmonthjdbc {

List<dw_targetmonth> tmList = new ArrayList<dw_targetmonth>();

public List<dw_targetmonth> tmjdbc() throws ClassNotFoundException {

Connection conn = null;

Statement stmt = null;

try {

String connurl="jdbc:teradata://192.168.206.1";

Class.forName("com.teradata.jdbc.TeraDriver");

conn=DriverManager.getConnection(connurl, "dwauto", "dwauto");

stmt = conn.createStatement();

String strSelect ="select A.* FROM dw_targetmonth A inner join (SELECT  monthcode, Max(DTLDate) as dtldate FROM dw_targetmonth GROUP BY monthcode) B on A.monthcode=B.monthcode and A.dtldate=B.dtldate";

ResultSet rset = stmt.executeQuery(strSelect);

System.out.println("The records selected are:");

int rowCount = 0;

while (rset.next()) { 

dw_targetmonth tmsc = new dw_targetmonth();

tmsc.setMonthcode(rset.getString("monthcode"));

tmsc.setTargetmonthstore(rset.getDouble("targetmonthstore"));

tmsc.setDtldate(rset.getDate("dtldate"));

tmList.add(tmsc);

}

} catch (SQLException ex) {

ex.printStackTrace();

} finally {

try {

if (stmt != null)

stmt.close(); // This closes ResultSet too

if (conn != null)

conn.close();

} catch (SQLException ex) {

ex.printStackTrace();

}

}

return tmList;

}

}

3 REPLIES
Teradata Employee

Re: Connecting to Teradata is very solwly(5 sec)

I suspect that you are mistaken about the amount of time being spent in the call to conn.createStatement. That method call typically executes very fast, because it does not communicate with the Teradata Database.

I recommend adding some System.out.println(new Date ()) calls in between your existing lines of code, in order to see what is actually taking time.

I also recommend adding the LOG=TIMING connection parameter to the JDBC connection URL.

Enthusiast

Re: Connecting to Teradata is very solwly(5 sec)

Dear Tomnolan,

Thank for your replying, I have used System.out.println(new Date()) to prove what process get many sec and then getting "stmt = conn.createStatement()" takeing 5 Sec long.

I got the messages below after setting "LOG=TIMING connection parameter to the JDBC connection URL". 

Plz help me find out what it really is problem.

2015-03-03.16:02:37.819 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Hostname lookup for 192.168.206.1 took 0 ms and found 1 address(es)

2015-03-03.16:02:37.824 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Attempting connection 1 to /192.168.206.1:1025

2015-03-03.16:02:37.825 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Connection attempt to /192.168.206.1:1025 with timeout 10000 ms took 0 ms and succeeded, waiting for thread took 0 ms

2015-03-03.16:02:37.825 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Wrote Config request message, 106 bytes, time: 0 ms

2015-03-03.16:02:37.825 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Read Config response message 1, 824 bytes, time: 0 ms

2015-03-03.16:02:37.856 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 TdgssManager initialization took 0 ms, TdgssConfigApi initialization took 0 ms

2015-03-03.16:02:37.888 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 TdgssContext.initSecContext took 16 ms

2015-03-03.16:02:37.888 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Wrote Assign request message, 238 bytes, time: 0 ms

2015-03-03.16:02:37.903 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Read Assign response message 1, 1110 bytes, time: 15 ms

2015-03-03.16:02:37.934 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 TdgssContext.initSecContext took 31 ms

2015-03-03.16:02:37.934 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Wrote SSO request message, 332 bytes, time: 0 ms

2015-03-03.16:02:37.934 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Read SSO response message 1, 62 bytes, time: 0 ms

2015-03-03.16:02:42.654 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Wrote Connect request message, 712 bytes, time: 0 ms

2015-03-03.16:02:42.737 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Read Connect response message 1, 216 bytes, time: 83 ms

2015-03-03.16:02:42.753 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Wrote Start request message, 100 bytes, time: 0 ms

2015-03-03.16:02:42.815 TERAJDBC4 TIMING [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@65c38146 Read Start response message 1, 106 bytes, time: 62 ms
Teradata Employee

Re: Connecting to Teradata is very solwly(5 sec)

Your log output does not show any operations taking an unexpectedly long time.