3

When constructing a rather large table in netezza, I get the following ERROR when using a JDBC connection:

org.netezza.error.NzSQLException: The update count exceeded Integer.MAX_VALUE.

The table does get created properly, but the code throws an exception. When I try running the same SQL using nzsql I get:

INSERT 0 2395423258

i.e. no thrown exceptions. It seems the variable storing the count of records in JDBC is not large enough?

Has anyone else encountered this error? How did you deal with it?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
tipanverella
  • 3,477
  • 3
  • 25
  • 41
  • Please show the relevant code and the full exception stacktrace – Mark Rotteveel Jul 05 '18 at 12:56
  • @MarkRotteveel, I can show the exact code, but I am pretty sure that the *ERROR* has little to do with the actual `sql`. I believe the issue is with the size of the constructed table. I will try to get a full error message asap. – tipanverella Jul 05 '18 at 13:05
  • 1
    The error is not directly related to you query, but it is related to **how you execute it**. I don't know Netezza itself, but I am very familiar with the JDBC API. The most likely cause is using `executeUpdate`, which returns an `int` update count, which means the count must be less than or equal to 2,147,483,647 and 2395423258 is larger. Some drivers will simply overflow, others will issue a warning, but apparently Netezza throws an error. – Mark Rotteveel Jul 05 '18 at 13:06
  • ```create table mytable as ( select * from verylartable ) distribute on (appropriate_key) organize on (date_field); ``` is essentially the skeleton on my `sql`. – tipanverella Jul 05 '18 at 13:09
  • Please show the relevant JDBC code, that shows how it is executed (and edit your question, don't use comments for relevant additions to your question). – Mark Rotteveel Jul 05 '18 at 13:09
  • I am using the dbeaver client and the `python` library `jaydebeapi` to connect to netezza, using in both cases the netezza provided driver. – tipanverella Jul 05 '18 at 13:12
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/174425/discussion-between-tipanverella-and-mark-rotteveel). – tipanverella Jul 05 '18 at 13:14
  • 1
    In other words, your question is incomplete and does not give us all information necessary. You need to provide all **relevant** information (including a [mcve] and full exception information) in your question. The JDBC solution would be to either use `execute(..)` or `executeLargeUpdate(..)`. I don't know how that would be possible to do with DBeaver, nor do I know anything about the jaydebeapi. – Mark Rotteveel Jul 05 '18 at 13:15
  • Thank you again for looking into this. I think `executeLargeUpdate` would probably be a good remedy. I am sorry that my question is incomplete, but I am using tools at least twice removed from the actual `JDBC` api, soit is hard for me to get detailed JAVA stacktraces of the errors. – tipanverella Jul 05 '18 at 13:21
  • This is the extent of the error message I get from dbeaver: ```SQL Error [1012] [HY000]: netezza.update.count.exceed.int.range netezza.update.count.exceed.int.range netezza.update.count.exceed.int.range ``` – tipanverella Jul 05 '18 at 14:09

1 Answers1

3

Modify your connection string to include ignoreUpdateCount=on as a parameter and try again.

joebeeson
  • 4,159
  • 1
  • 22
  • 29
  • 1
    First of all @joeb, THANK YOU! Also, the following connection string template seems to work well: `jdbc:netezza://{host}:{port}/{database};ignoreUpdateCount=True` – tipanverella Nov 14 '18 at 14:21
  • 1
    @tipanverella I'm happy to hear that answered your question. – joebeeson Nov 14 '18 at 14:55