SQL command differences in the replication environment

In the replication environment, there are differences for some of the standard Netezza® SQL commands. The differences include syntax and behavior changes. For the complete Netezza SQL command reference, see Database user documentation.
When you use Netezza SQL with replication, the following rules apply:
  • You must execute the SQL commands on the primary.
  • On a replica, changes to replicated objects (DDL, DML, database tables, and sequences) cause the transaction to abort. Temporary tables are not considered replicated objects; therefore, you can modify them.
  • All data references, including to data in a non-replicated or system database, are supported. References to non-replicated data trigger by-value replication.

In addition, non-deterministic statements are handled by using by-value replication (change value replication).

An SQL command has one of three outcomes when run within replication:
  • Replicated: the command is executed on the primary and replayed on the replica
  • Not replicated: the command is executed on the primary or replica but is not replayed on another node
  • Not supported: the command is blocked from executing
The following table lists and describes the SQL commands that are affected by replication.
Table 1. SQL commands affected by replication
Command Notes See also
ALTER DATABASE You can issue this command only from a session connection to a replicated or the SYSTEM database. On a replicated database on a primary, the ALTER DATABASE command is replicated to the replica. On a replica, the ALTER DATABASE command is permitted only on a non-replicated database. CREATE DATABASE command
BEGIN and COMMIT Transactions with update queries (with corresponding BEGIN and COMMIT commands) that affect global or replicated objects are replicated. Transactions without update queries (read only, where no changes are made to the database) are not. ROLLBACK command
CALL, EXEC, EXECUTE, and SELECT stored procedure By-SQL or by-value replication is selected based on whether all objects that are referenced by a stored procedure are members of the replication set. Replication of stored procedures
COPY FROM This command is not supported.  
CREATE AGGREGATE, CREATE [OR REPLACE] AGGREGATE, DROP AGGREGATE All user-defined aggregates (and system windowed aggregate functions, which can’t be modified) use by-value replication.  
CREATE CATEGORY, ALTER CATEGORY, and DROP CATEGORY There are global objects that are related to multilevel security. Global objects
CREATE COHORT, ALTER COHORT, andDROP COHORT There are global objects that are related to multilevel security. Global objects
CREATE DATABASE REPLICATION SET A subcommand of the CREATE DATABASE command, it creates a new replicated database when issued on the primary. Creating new replicated databases
CREATE EXTERNAL TABLE An external file is created as part of the execution of the command on the replica, which is not the exact file that existed on the primary. The directory path must exist on the replica; otherwise, replication is suspended. External table creation
CREATE [OR REPLACE] FUNCTION, ALTER FUNCTION, and DROP FUNCTION You can specify theDETERMINISTIC or NOT DETERMINISTIC option. If you specify the NOT DETERMINISTIC option, replication by-value is selected when the function is called. Synchronization
CREATE GROUP, ALTER GROUP, and DROP GROUP Global object. Global objects
CREATE HISTORY CONFIGURATION, ALTER HISTORY CONFIGURATION, DROP HISTORY CONFIGURATION, and SET HISTORY CONFIGURATION History configuration-related commands are not replicated. Nodes in a replication set can have different or non-enabled history configurations.  
CREATE [OR REPLACE] LIBRARY, ALTER LIBRARY, and DROP LIBRARY User-defined libraries are objects in a database. The commands take two clauses, EXTERNAL HOST OBJECT and EXTERNAL SPU OBJECT, that identify compiled objects on the file system that is used for executing the library code on the host and the SPU. You must define each library (including optional dependent libraries) and compiled object in a replicated database; otherwise, the command fails.  
CREATE MATERIALIZED VIEW See CREATE VIEW.  
CREATE PROCEDURE, ALTER PROCEDURE, and DROP PROCEDURE   Replication of stored procedures
CREATE SECURITY LEVEL, ALTER SECURITY LEVEL, and DROP SECURITY LEVEL Global objects that are related to multilevel security. Global objects
CREATE SEQUENCE, ALTER SEQUENCE, and DROP SEQUENCE By-value replication is used. Sequence consistency
CREATE SYNONYM, ALTER SYNONYM, and DROP SYNONYM You can create cross-database synonyms. By-value replication is used for references to a nonreplicated table.  
CREATE TABLE, ALTER TABLE, and DROP TABLE The commands are replicated only for non-temporary tables in a replicated database.  
CREATE TABLE AS SELECT Replication is by SQL or by value, depending on whether all referenced objects are members of the replication set.  
CREATE TEMPORARY TABLE Temporary tables behave identically in replicated and non-replicated databases on the primary. Temporary tables are not replication set member objects, and references to temporary tables result in by-value replication. SQL replication implementation rules
CREATE USER, ALTER USER, and DROP USER Execution of the ALTER USER admin command is a local operation. You can issue the command on any node, and it is not replicated. You must perform all other user operations on the primary, and they are replicated with other global objects.  
CREATE VIEW and CREATE [OR REPLACE] VIEW The command can reference objects in a replicated database or system objects. The command fails if it references an object in a non-replicated database. Views when initializing a replica
DROP DATABASE When you issue the command against a replicated database on the primary, the database is dropped on all replication nodes in the set. Dropping a database during the execution of concurrent replicated transactions can result in a deadlock. Dropping a database while sessions are still connected to it causes the command to fail.  
EXECUTE AS and REVERT The commands must be replicated when they are in a multistatement update transaction against replicated or global data. No effect when issued as an implicit transaction.  
GRANT and REVOKE   Granting and revoking of privileges
GENERATE STATISTICS The command does not change data but does affect query performance.

In NPS® releases earlier than 7.2.0.4, the command is always replicated if you issue it on the primary. In NPS 7.2.0.4 or later, the command is replicated if you issue it on the primary and the replReplayMaintenance parameter in the replrqm.cfg file is set to true. The default value of the parameter is true; change it only in consultation with Customer Support.

You can issue the command against a replicated database on the replica, but the command is not replicated.		  
GROOM TABLE The command does not change data but does affect query performance.

In NPS releases earlier than 7.1.0.4-P2 and 7.2.0.3-P2, a replicated GROOM TABLE command with the RECLAIM BACKUPSET <backupsetid> option causes the replica to suspend.

In NPS 7.1.0.4-P2 and 7.2.0.3-P2 or later, the replica treats a replicated GROOM TABLE command with the RECLAIM BACKUPSET <backupsetid> option as a GROOM TABLE command without the RECLAIM BACKUPSET <backupsetid> option.

In NPS 7.2.0.4 or later, the GROOM TABLEcommand is replicated if you issue it on the primary and the replReplayMaintenance parameter in the replrqm.cfg file is set to true. The default value of the parameter is true; change it only in consultation with Customer Support.

You can issue the GROOM TABLE command against a replicated database on the replica, but the command is not replicated. Issuing the command against a replicated table on a replica does not purge deleted records that might need to be visible to subsequent replicated transactions from the primary or to current transactions on the replica.		  
INSERT, UPDATE, and DELETE The commands are replicated if applied on a database that is replicated, except when targeting a temporary or external table (unload). The commands are allowed on a replica only on a non-replicated database or temporary table in a replicated database. Unloads are not logged on the primary or replica and so are allowed on a replica as well.  
LOCK DATABASE statement The LOCK DATABASE statement is not replicated, but it is allowed on the primary to prevent transaction deadlocks and aborts.  
LOCK TABLE statement Not replicated, including for tables in a replicated database. LOCK TABLE statements and the IBM Netezza Database User's Guide.
ROLLBACK Is not replicated. BEGIN and COMMIT
SELECT queries Implicit transactions with SELECTs, read-only transactions, and SELECT statements that are part of a read/write transaction are not replicated.  
SET and RESET statements With a few exceptions, not replicated because the session state is not preserved between the primary and replica. NPS session variables
SHOW statement Not replicated.  
TRUNCATE TABLE The command cannot be part of an explicit transaction. If executed on the primary, the command is replicated if the database is replicated.