Re: [EXT] AW: ExecuteSQL and Teradata

["Peter Wicks (pwicks)" <pw...@micron.com>]

Micron Confidential

Can you try setting `FINALIZE_AUTO_CLOSE=ON` in your connection string to Teradata? It’s not a best practice, but based on what the docs say, I think it might work.

From the docs:


FINALIZE_AUTO_CLOSE values are OFF (default) or ON:

·  When set to OFF (the default), the Teradata JDBC Driver provides the JDBC 4.0 API Specification behavior such that JDBC objects are not closed automatically during finalize. The application is responsible for closing or freeing JDBC objects.

·  When set to ON, the Teradata JDBC Driver provides the JDBC 3.0 API Specification behavior to close JDBC objects during finalize. This will have a performance impact on garbage collection, and is not recommended.

Java programming best practice is to avoid finalize methods altogether. If a finalize method is used, best practice is to minimize its processing time, and to avoid operations that can take a long time, such as network communications. The JDBC 3.0 API Specification contradicted these best practices by requiring a JDBC Driver to close JDBC objects automatically during garbage collection. The JDBC 4.0 API Specification dropped the requirement for automatic closing of JDBC objects during garbage collection, so the JDBC 4.0 API Specification is in agreement with these best practices.

Garbage collection can be blocked indefinitely when FINALIZE_AUTO_CLOSE is set to ON, and the Teradata JDBC Driver does not receive a response from the database after sending a message to the database to close the response spool.

This parameter is available for SQL connections beginning with Teradata JDBC Driver 14.00.00.08.


From: christian.gumpert@ezv.admin.ch <ch...@ezv.admin.ch>
Date: Monday, January 11, 2021 at 2:05 PM
To: users@nifi.apache.org <us...@nifi.apache.org>
Subject: [EXT] AW: ExecuteSQL and Teradata
CAUTION: EXTERNAL EMAIL. Do not click links or open attachments unless you recognize the sender and were expecting this message.

Hi Phil,

we are facing
Christian exactly the same issue (using Nifi 1.11.2) and I opened a ticket for this https://issues.apache.org/jira/browse/NIFI-8119#<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fbrowse%2FNIFI-8119&data=04%7C01%7Cpwicks%40micron.com%7C30cded2689c9440b77b708d8b6749694%7Cf38a5ecd28134862b11bac1d563c806f%7C0%7C0%7C637459959187869776%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=KTEC97r3BrvPnWc36cOaizjA3RMYXKtMM%2F0flMpSu%2Bw%3D&reserved=0>
I already have a patch for that locally and will submit a PR shortly.

Cheers,
Christian

Von: Toivo Adams <to...@gmail.com>
Gesendet: Montag, 11. Januar 2021 21:24
An: users@nifi.apache.org
Betreff: Re: ExecuteSQL and Teradata

Hi,

And you are able to recognize you have received all data?
(My Teradata knowledge is limited, sorry.)
One solution is to create a customized version of ExecuteSQL.
And either close connection (return to pool) or send signal to Teradata.
This is not that hard.

You can also try to set QUERY_TIMEOUT for standard ExecuteSQL,
does Teradata JDBC support this?

BR
Toivo

Kontakt <gr...@mailnull.com>> kirjutas kuupäeval R, 18. detsember 2020 kell 20:24:
Dear all

In the ExecuteSQL processor, I'm facing the following problem:

I want to execute a stored procedure in a Teradata database. This stored
procedure returns LOB data. Since I receive LOB data, I have LOB_SUPPORT on in
the JDBC driver (it's the default anyway). Since LOB data is not stored inline
in the database, Teradata expects a signal from the receiver that all data has
been received (in Teradata lingo that means KeepResp is on).

The problem now is, that NiFi does not send this signal. It keeps the connection
open. After NiFi has 16 connections to Teradata open, Teradata refuses to open
another connection and the following error is thrown:

[Teradata Database] : Response limit exceeded.

There's even a nice explanation about this error here [1].

I have set the maximum number of connections to 8, as is the default in the
controller. But that does not seem to prevent my issue. If I set a max timer for
the connections, it works, but of course I do not know how long I would need to
keep the connections open.

My question now is: How can I tell NiFi to close the connection to the Teradata
database, once it has received all data?

I appreciate all the help. Thanks,
Phil

[1] https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_5.html#CHDGCHBB<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fteradata-docs.s3.amazonaws.com%2Fdoc%2Fconnectivity%2Fjdbc%2Freference%2Fcurrent%2Fjdbcug_chapter_5.html%23CHDGCHBB&data=04%7C01%7Cpwicks%40micron.com%7C30cded2689c9440b77b708d8b6749694%7Cf38a5ecd28134862b11bac1d563c806f%7C0%7C0%7C637459959187879767%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=wAvPt0o4jtzvRLBR1oJ0yUgFU1nfsJeS7Nu1IlbMWD0%3D&reserved=0>


----------
This message was sent from a MailNull anti-spam account.  You can get
your free account and take control over your email by visiting the
following URL.

   http://mailnull.com/<https://nam10.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailnull.com%2F&data=04%7C01%7Cpwicks%40micron.com%7C30cded2689c9440b77b708d8b6749694%7Cf38a5ecd28134862b11bac1d563c806f%7C0%7C0%7C637459959187879767%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Yx4A1H7DE9fHwooR7bs%2BL4pGi6JbCr06CghegE8Z2E4%3D&reserved=0>


Micron Confidential