Speed up your JDBC/ODBC applications

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

Re: Speed up your JDBC/ODBC applications

mingtotti, the Teradata ODBC Driver and Teradata .NET Data Provider do not support FastLoad or FastExport at the present time. 

aimam, Teradata Database error 5966 applies to PreparedStatement batches using a regular SQL operation. Teradata Database error 5966 does not occur with FastLoad. Please remember that even when your application specifies the TYPE=FASTLOAD connection parameter, FastLoad is only used if your operation qualifies for FastLoad.

Please refer to the Teradata JDBC Driver Reference section about JDBC FastLoad, to find information about which SQL operations qualify for FastLoad.

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BABFGFAF

In this particular case, since your application is not using FastLoad for some reason, you can avoid Teradata Database error 5966 by reducing the number of rows in the PreparedStatement batch to below 16383, which is the limit.

Fan

Re: Speed up your JDBC/ODBC applications

So i've added the TYPE=FASTLOAD in the jdbc connection string, and this used to work, but now no longer does.  Is ther e asetting that would ignore the TYPE property in the connection string?

Teradata Employee

Re: Speed up your JDBC/ODBC applications

TYPE=FASTLOAD instructs the Teradata JDBC Driver to use the FastLoad protocol for INSERT statements that are compatible with FastLoad. A regular SQL INSERT will be performed for INSERT statements that aren't compatible with FastLoad.

If your application specifies TYPE=FASTLOAD and the FastLoad protocol was used in the past, but is no longer used, then something must have changed. For example, your application may now be using a data type that is not supported by the FastLoad protocol.

There are two possible ways to obtain the reason why your SQL statement does not qualify for FastLoad.

1. You can specify the LOG=INFO connection parameter, and the Teradata JDBC Driver will log to System.out the reason why the SQL statement does not qualify for FastLoad.

2. After calling the Connection.prepareStatement method, your application can call the Connection.getWarnings method, and then walk the SQLWarning chain. A SQLWarning will list the reason why the SQL statement does not qualify for FastLoad.

Fan

Re: Speed up your JDBC/ODBC applications

Hello,

This looks like it's probably what I need but I can't figure out how to use it!

How can I adapt the 'batch ready' script to run from Excel VBA?

What I'm trying to do is write some VBA that will load whatever is in a datasheet to a specified teradata table.

I can use ODBC or ADO but don't know anything about JDBC or Java.

Thanks.

Fan

Re: Speed up your JDBC/ODBC applications

I'm making some progress! I have the code below which works. I would appreciate it if someone could tell me how I can execute the statements in batches (or any other way to make the multi insert efficient).

/code

Sub test_param_this_works()

Dim cn As New ADODB.Connection

Dim cmdPrep1 As New ADODB.Command

Dim prm1 As New ADODB.Parameter

Dim prm2 As New ADODB.Parameter

Dim strCn As String

t = Timer

pwd = InputBox("password?")

strCn = "DSN=GDWPROD1;UID=COJNH;DATABASE=NONPERS;Password=" & pwd & "; Session Mode=ANSI;"

cn.Open strCn

Set cmdPrep1.ActiveConnection = cn

cmdPrep1.CommandText = "INSERT INTO jhtest (col1,col2) VALUES (?,?)"

cmdPrep1.CommandType = adCmdText

cmdPrep1.Prepared = True

Set prm1 = cmdPrep1.CreateParameter("param_nm1", adInteger, adParamInput, 1, 1)

cmdPrep1.Parameters.Append prm1

Set prm2 = cmdPrep1.CreateParameter("param_nm2", adInteger, adParamInput, 1, 2)

cmdPrep1.Parameters.Append prm2

For i = 1 To 1000

cmdPrep1("param_nm1") = i

cmdPrep1("param_nm2") = i + 1

cmdPrep1.Execute

Next i

cn.Close

MsgBox "This took: " & Timer - t

End Sub

code/

Teradata Employee

Re: Speed up your JDBC/ODBC applications

This article is primarily about Java and JDBC. You are unlikely to receive Visual Basic help by posting here. I recommend that you repost your question in the Connectivity forum.

Fan

Re: Speed up your JDBC/ODBC applications

Will do. Thanks Tom.

Re: Speed up your JDBC/ODBC applications

How do I do this in .NET?

Re: Speed up your JDBC/ODBC applications

I am using Batch insert to insert 250,000 records (50,000 record/batch), and I am getting the following error:

java.sql.BatchUpdateException: [Teradata Database] [TeraJDBC 15.10.00.05] [Error 9128] [SQLState HY000] :The transaction exceeded the maximum number of rowhash locks allowed.

any recommendations? I am thinking about moving to FastLoad, will that solve the issue? or its related to Teradata server hardware?

Thanks

Teradata Employee

Re: Speed up your JDBC/ODBC applications

Error 9128, and its remedies, was discussed in another thread:

https://forums.teradata.com/forum/database/failure-9128-the-transaction-exceeded-the-maximum-number-...

Every row of data that you attempt to insert may require its own row hash lock. The Teradata Database imposes a limit on the number of row hash locks that can be held by a transaction. When your transaction exceeds the maximum number of row hash locks, then the Teradata Database returns error 9128.

There are at least 3 ways to avoid error 9128, listed in order from easiest to hardest:

  • Use a smaller batch size and commit the transaction after fewer inserted rows. If you attempt to insert fewer rows per transaction, then you will avoid hitting the Teradata Database limit on row hash locks.
  • Lock the table for writing before doing any inserts, within the same transaction as the inserts. You can lock a table for writing by executing the SQL command "locking table foobar for write". If your transaction has a table locked for writing, then no row hash locks are needed for inserted rows, and you will avoid hitting the Teradata Database limit on row hash locks. Keep in mind, however, than nobody else can use the table while you have it locked for writing.
  • Use JDBC FastLoad, because the FastLoad protocol does not use row hash locks, so JDBC FastLoad is not subject to the Teradata Database limit on row hash locks. The FastLoad protocol has a bunch of limitations and caveats, so be sure that you application will work OK with FastLoad before trying to use it. The table must be empty, and nobody else can use the table, while you are loading data into the table with FastLoad.