SQLite module
An SQLite database is embedded inside of nzLua to provide a way to persist data outside of a Netezza transaction. The primary use case for this feature is for debug logging, but it can also be used to store lookup tables or additional data that cannot be directly returned to the Netezza database from a UDX (for example, when a UDTF needs to return multiple distinct result sets instead of a single result set).
The maximum amount of data that can be stored in a SQLite database is capped at 1GB. A large SQLite database could substantially impact overall performance of the Netezza database due to random I/O requests for index lookups and index maintenance during inserts. The SQLite database will be cached in memory where possible to avoid excessive random I/O. However, as the database grows in size, memory may not be available. This results in many small I/O requests, which then interferes with sequential reads for the Netezza database.
A unique SQLite database exists for each data slice. Two concurrently running UDXs on the same data slice will access the same SQLite database. It is not possible for a UDX to share data across data slices using SQLite. The SQLite database does not support row level locking. Any insert, update, or delete statement locks the entire SQLite database for the duration of a transaction. By default, SQLite operates in autocommit mode where each DML statement is committed immediately.
Detailed documentation on SQLite, including the SQL syntax supported by SQLite, can be found at http://www.sqlite.org. Some examples of using SQLite from within an nzLua UDX can be found in the directory /nz/extensions/nz/nzlua/examples/SQLite.