Data connection management for remote AEs

The nzaejobcontrol function includes functionality to manage remote AE data connections. This functionality is useful in situations where an issue arises with the AE, for example if a user-created AE fails or a session on the Netezza appliance abruptly terminates

In either case, one side of an AE data connection could potentially be hung waiting for data. In addition, it is possible that temporary files used for data communication are left behind. Two nzaejobcontrol function commands—connections and repair—can be used to recognize and repair these problems.

The connections command lists all of the remote AE data connections, on either the SPUs or the host. Note that a single query running on the SPUs uses multiple data connections, one per dataslice, while a query running on the host uses only a single data connection.

The following examples are run on a system with one SPU consisting of four dataslices.

Here, the connections command is run with SPU locus.

SELECT * FROM _v_dual_dslice, TABLE WITH FINAL(inza..nzaejobcontrol('connections',
dsid, null, false, null, null));
 DSID | AERESULT | AERC | AETOTAL | AENAME | AEDSLICE | AESESSION
| AETRANS | AEHOSTNAME | AEPID | AECOMMAND | AETID |
AEBUILD | AENZREPVER | AEVERMISMATCH 
------+----------------------+------+---------+------------+----------
+-----------+---------+------------+-------+------------------------------------
+-------+---------+------------+---------------
 3 | OK | 0 | 8 | datamatrix | 1 | 17205
| 43310 | spu0101 | 25495 | /var/opt/nz/local/tmp/nzrep_D2zMNV | |
| | 
 3 | OK | 0 | 8 | datamatrix | 4 | 17205
| 43310 | spu0101 | 25495 | /var/opt/nz/local/tmp/nzrep_wIJ5mV | |
| | 
 3 | OK | 0 | 8 | datamatrix | 2 | 17205
| 43310 | spu0101 | 25495 | /var/opt/nz/local/tmp/nzrep_GWL2dV | |
| | 
 3 | OK | 0 | 8 | datamatrix | 3 | 17205
| 43310 | spu0101 | 25495 | /var/opt/nz/local/tmp/nzrep_DpCZeV | |
| | 
 3 | ERROR: (AE Stopped) | -1 | 8 | datamatrix | 3 | 17099
| 43304 | spu0101 | 18120 | /var/opt/nz/local/tmp/nzrep_HAyLm0 | |
| | 
 3 | ERROR: (AE Stopped) | -1 | 8 | datamatrix | 2 | 17099
| 43304 | spu0101 | 18120 | /var/opt/nz/local/tmp/nzrep_uTHvp3 | |
| | 
 3 | ERROR: (AE Stopped) | -1 | 8 | datamatrix | 4 | 17099
| 43304 | spu0101 | 18120 | /var/opt/nz/local/tmp/nzrep_oHGbE0 | |
| | 
 3 | ERROR: (AE Stopped) | -1 | 8 | datamatrix | 1 | 17099
| 43304 | spu0101 | 18120 | /var/opt/nz/local/tmp/nzrep_vROdw0 | |
| | 
(8 rows)

In this example two queries are running, which use a total of eight data connections. The query on session 17205 is running fine, but on session 17099, the remote AE has crashed and the Netezza system session is hung. For local AEs, the Netezza software can detect and resolve this situation; however the Netezza system does not manage remote AE processes and therefore cannot resolve the issue for the remote AE.

The problem can be corrected by running the nzaejobcontrol function's repair command. This command aborts any hung connections and cleans up leftover machine resources.

SELECT * FROM _v_dual_dslice, TABLE WITH FINAL(inza..nzaejobcontrol('repair', dsid,
null, false, null, null)); 
 DSID | AERESULT | AERC | AETOTAL | AENAME | AEDSLICE | AESESSION | AETRANS |
AEHOSTNAME | AEPID | AECOMMAND | AETID | AEBUILD |
AENZREPVER | AEVERMISMATCH 
------+----------+------+---------+------------+----------+-----------+---------
+------------+-------+------------------------------------+-------+---------
+------------+---------------
 1 | OK | 0 | 4 | datamatrix | 3 | 17099 | 43304 |
spu0101 | 18120 | /var/opt/nz/local/tmp/nzrep_HAyLm0 | | |
| 
 1 | OK | 0 | 4 | datamatrix | 2 | 17099 | 43304 |
spu0101 | 18120 | /var/opt/nz/local/tmp/nzrep_uTHvp3 | | |
| 
 1 | OK | 0 | 4 | datamatrix | 4 | 17099 | 43304 |
spu0101 | 18120 | /var/opt/nz/local/tmp/nzrep_oHGbE0 | | |
| 
 1 | OK | 0 | 4 | datamatrix | 1 | 17099 | 43304 |
spu0101 | 18120 | /var/opt/nz/local/tmp/nzrep_vROdw0 | | |
| 
(4 rows)

In this case, the hung connections for session 17099 are aborted and the original hung query returns an error message.

If the connections command is now run only the working connections remain:

SELECT * FROM _v_dual_dslice, TABLE WITH FINAL(inza..nzaejobcontrol('connections',
dsid, null, false, null, null));
 DSID | AERESULT | AERC | AETOTAL | AENAME | AEDSLICE | AESESSION | AETRANS |
AEHOSTNAME | AEPID | AECOMMAND | AETID | AEBUILD |
AENZREPVER | AEVERMISMATCH 
------+----------+------+---------+------------+----------+-----------+---------
+------------+-------+------------------------------------+-------+---------
+------------+---------------
 2 | OK | 0 | 4 | datamatrix | 1 | 17205 | 43310 |
spu0101 | 25495 | /var/opt/nz/local/tmp/nzrep_D2zMNV | | |
| 
 2 | OK | 0 | 4 | datamatrix | 4 | 17205 | 43310 |
spu0101 | 25495 | /var/opt/nz/local/tmp/nzrep_wIJ5mV | | |
| 
 2 | OK | 0 | 4 | datamatrix | 2 | 17205 | 43310 |
spu0101 | 25495 | /var/opt/nz/local/tmp/nzrep_GWL2dV | | |
| 
 2 | OK | 0 | 4 | datamatrix | 3 | 17205 | 43310 |
spu0101 | 25495 | /var/opt/nz/local/tmp/nzrep_DpCZeV | | |
| 
(4 rows)

If the repair command is run again there is no output since there is nothing to fix.

SELECT * FROM _v_dual_dslice, TABLE WITH FINAL(inza..nzaejobcontrol('repair', dsid,
null, false, null, null)); 
 DSID | AERESULT | AERC | AETOTAL | AENAME | AEDSLICE | AESESSION | AETRANS |
AEHOSTNAME | AEPID | AECOMMAND | AETID | AEBUILD | AENZREPVER | AEVERMISMATCH 
------+----------+------+---------+--------+----------+-----------+---------
+------------+-------+-----------+-------+---------+------------+---------------
(0 rows)

These commands can also be run on the host. The host locus versions of the commands are as follows:

SELECT * FROM TABLE WITH FINAL(inza..nzaejobcontrol('connections', 0, null,
false, null, null));

SELECT * FROM TABLE WITH FINAL(inza..nzaejobcontrol('repair', 0, null, false,
null, null));

Note: These commands apply only to data connections and do not directly stop AE processes. The manner in which the AE is written determines whether the AE stops when a data connection is aborted.