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.
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.
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?
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.
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.
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).
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;"
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)
Set prm2 = cmdPrep1.CreateParameter("param_nm2", adInteger, adParamInput, 1, 2)
For i = 1 To 1000
cmdPrep1("param_nm1") = i
cmdPrep1("param_nm2") = i + 1
MsgBox "This took: " & Timer - t
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.
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?
Error 9128, and its remedies, was discussed in another thread:
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: