Answer

The problem is that the connection becomes idle while the IBM Netezza system is
processing the query or executing the stored procedure, and as designed, the
system does not communicate to the client that submitted the query/procedure.

Several factors that can cause a lengthy idle connection to be closed
automatically by an entity other than the IBM Netezza system include the
following:
Client timeout settings
Firewall settings/policies
Network settings/policies
In one case, the network team confirmed an idle timeout setting on a network
device (such as a switch or router) that closes idle connections lasting longer
than one hour. Although identifying the source of the closed connection is
ideal, it is more important to determine the length of time a connection is
idle before it is terminated. This way, we can set a keep-alive setting on the
IBM Netezza side to prevent a re-occurrence.
Assuming that idle connections are timed out after one hour, you should enable
the following three settings in the postgresql.conf configuration file:
1. Log into the IBM Netezza system as the 'nz' user.
2. Edit the file /nz/data.1.0/postgresql.conf using vi or another editor.
3. Find the following lines, uncomment them, and make sure that the values
match those below:

tcp_keepidle = 900
tcp_keepinterval = 75
tcp_keepcount = 9

4. Restart the NPS database by running the following commands:
nzstop
nzstart
Re-run the query/procedure that had previously been timing out, and verify that
it now completes successfully.
For reference, these three TCP settings are described as follows:
tcp_keepidle: The number of seconds between keepalive messages sent on an
otherwise
idle connection. A value of 0 uses the system default (7200 seconds). If users
report SQL client session disconnects, set this parameter to the recommended
value of 900.

tcp_keepinterval: The number of seconds to wait for a keepalive response before
retransmitting the message. A value of 0 uses the system default (75 seconds).

tcp_keepcount: The number of retransmission attempts that must occur before the
connection is considered dead. A value of 0 uses the system default (9
attempts).