Netezza: failed to create external table for bulk load

I came across an interesting problem recently in some JDBC code that was inserting rows into a Netezza database.

The Java code was doing something like this:

Statement stmt ...
for( SomeObject o : listOfObjects) {
	String sql = "INSERT INTO tbl(col1, col2) values (?,?)";
	...
	stmt.addBatch(sql);
}
stmt.executeBatch();

On the “executeBatch()”, a SQLException was being thrown that said “failed to create external table for bulk load”.

What? But it wasn’t a bulk load. There were no external tables involved - it was a batch of vanilla inserts.

In an attempt to debug the problem, I tried executing the statements inside the loop. No problem. It worked fine. The SQL was only a problem when executed as a batch.

So I opened up a PuTTY session and logged into the Netezza box and tailed the logs.

This is an excerpt from the NZ log file (found in /nz/kit.7.1.0.0/log/postgres/pg.log, for the curious).

2014-11-28 13:00:18.222493 EST [17672]  DEBUG:  QUERY: drop table bulkETL_17672_0
2014-11-28 13:00:18.232294 EST [17672]  DEBUG:  QUERY: CREATE EXTERNAL TABLE bulkETL_17672_0 ( c0 nvarchar(5),c1 nvarchar(48),c2 nvarchar(48),c3 nvarchar(6),c4 nvarchar(5),c5 nvarchar(5),c6 nvarchar(1),c7 nvarchar(2),c8 nvarchar(2) )  USING (  DATAOBJECT('/tmp/junk')  REMOTESOURCE 'jdbc'  DELIMITER '   '  ESCAPECHAR '\'  CTRLCHARS 'YES'  CRINSTRING 'YES'  ENCODING 'INTERNAL'  MAXERRORS 1 QUOTEDVALUE 'YES' );
2014-11-28 13:00:18.232295 EST [17672]  ERROR: create external table: permission denied

So it seems that Netezza is optimizing my individual batch inserts, and executing them by writing the data to a file on disk and bulk-loading that file using an external table. This is a seemingly obvious optimization, since Netezza is really fast at performing bulk operations, and that it isn’t so good at single-row operations. In fact, it takes about the same amount of time to insert a single row as it does to insert a million rows.

The problem is that my userid didn’t have permissions to create an external table. The application userid can only perform basic SQL operations: SELECT, INSERT and UPDATE. So I get a permission denied error even though I’m trying to perform an INSERT - an operation that I have permission to do.

Granting the “create external table” permission fixed the problem, but it isn’t an ideal solution. I shouldn’t require permission to create an external table when all I want to do is insert a couple of rows.