LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dinokape

Every week I drop and recreate a table and then load some 10-15K records into a Teradata database. The table is simple, it only has 13 digit account numbers which are defined as VARCHAR(13). Even for such a relatively small dataset, the load takes some 20 minutes and our DBA is complaining that I should be using the FastLoad or MultiLoad functionality. How can I do that using the DB Store node? Does anybody know of an alternative using scripts?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

Hi,

I did a quick search on Teradata fastload/multiload and it looks like these are utilities accessed/started from the system command prompt, after which you can run your SQL scripts.

DB Store, or even DB Execute do not have the ability to execute system level commands. So unless you can access or activate those modes from inside a SQL script, it won't work.

What you may be able to do, is write a simple python or java node that passes a sql script file to that fastload or multiload function call. If your sql is fairly static, you might keep it as a separate script file, or use one of the output nodes to construct your SQL statement and dump it to a file, which you can pass to the Python or Java node. I will say I have not done this myself.

-Timon

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mermacora

The Teradata JDBC User Guide states that JDBC FastLoad can be enabled by adding TYPE=FASTLOAD in the URL connection string.
There are other constraints around the types of SQL Inserts that can be performed via FastLoad and I’m not sure if these are in effect when using the JDBC Store node from LAE, but it’s worth a try.
I do not have access to a Teradata database to test this myself, but would be interested to know if this works and provides any performance benefit.

Hope this helps,
Mario

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: shaunmbg

I've been able to execute a JDBC Query node against a Teradata Database using the following DbUrl parameter:

jdbc:teradata://nun017.edw.in.telstra.com.au/LOGMECH=LDAP,TYPE=FASTLOAD

I'll test this on some JDBC Execute nodes during a weekly run later this week and report on feasibility/performance gains.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: shaunmbg

Using an Insert Statement in a JDBC Execute Node returns the following:

"error on row 0. com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 12.00.00.114] [Error 1093] [SQLState HY000] This method is not implemented!"

I've been informed by EDW DBAs that JDBC drivers aren't supported.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: kursattaskin

How Can we use teradata multiload option with db store or something else?

Kind Regards,

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

As far as I know, and what seems to be corroborated above, this functionality is only supported in command line tools (Teradata command line tools, to be clear). There is not currently any LAE specific functionality that supports this option.

Cheers
Rich

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: shaunmbg

Hi guys, Teradata Fastload (and presumably Multiload) can be called using Python nodes, this has been used within LAE to perform some large inserts into Teradata DWs.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ghalt

On a separate note, try deleting from and inserting instead of dropping and recreating the table. When you drop and recreate, TD has to update the DBC, which is a little more intensive. Check with your DBA.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jegnj

Can anyone post a python node that does a fastload to Teradata? I am not familiar with python today.