IBM PureData System for Analytics (Netezza) and Db2 SQL compatibility

In most cases, Db2® and Netezza® products provides equivalent SQL support. For example, in most cases, a particular Netezza SQL command has an equivalent Db2 SQL statement, which you can use without modifying your code. In a limited number of cases, changes are required.

The following tables identify areas where Netezza SQL support differs from Db2 SQL support. For SQL restrictions that are related to stored procedures, see Routines written in NZPLSQL.
Table 1. SQL compatibility: commands
Netezza SQL command Db2 support
ALTER AGGREGATE No Db2 ALTER AGGREGATE statement is available. Change the definitions of aggregates by using the Db2 ALTER FUNCTION statement. The IBM® Database Harmony Profiler tool converts the ALTER AGGREGATE command to the ALTER FUNCTION statement.
ALTER FUNCTION The only Netezza ALTER FUNCTION command parameters that are supported by the Db2 ALTER FUNCTION statement are FENCED and NOFENCED. Change the definitions of functions by using the Db2 CREATE [OR REPLACE] FUNCTION statement.
ALTER GROUP No Db2 ALTER GROUP statement is available. Use roles instead of groups.
ALTER HISTORY CONFIGURATION A Db2 ALTER HISTORY CONFIGURATION statement is not available. To collect and view historical data about database usage, use the Db2 monitoring capabilities.
ALTER LIBRARY No Db2 ALTER LIBRARY statement is available. The Database Harmony Profiler tool comments out the ALTER LIBRARY command.

To ensure that dependent libraries are accessible to your Db2 product, place them in the directory that is specified by the LIBPATH or PATH environment variable on Windows operating systems or in the instance-level function directory ($inst_home_dir/sqllib/function/) on Linux® and UNIX operating systems.

ALTER SCHEMA The Db2 ALTER SCHEMA statement does not support some clauses of the Netezza ALTER SCHEMA command:
  • The Db2 ALTER SCHEMA statement does not support the AUTHORIZATION TO clause. Instead, reassign ownership of the schema by using the Db2 TRANSFER OWNERSHIP statement.
  • The Db2 ALTER SCHEMA statement does not support the SET PATH clause.

    For dynamic SQL statements, the SQL path is the value of the Db2 CURRENT PATH special register, which you can change by using the SET PATH command. For static SQL statements, specify the SQL path by using the FUNCPATH bind option.
ALTER SESSION No Db2 ALTER SESSION statement is available. Consider using the WLM_CANCEL_ACTIVITY or WLM_SET_CONN_ENV procedure.
ALTER TABLE The Db2 ALTER TABLE statement does not support some of the functionality of the Netezza ALTER TABLE command:
  • The Db2 ALTER TABLE statement does not support the RENAME TO clause. Instead, use one of the following approaches:
    • Rename the table by using the RENAME statement.
    • Drop the table and re-create it with the new name.
    • Create an alias with the new name.
  • The Db2 ALTER TABLE statement does not support the SET PRIVILEGES TO clause.
  • The row size limit in the Db2 ALTER TABLE statement is 32677.
  • The Db2 ALTER TABLE statement does not support the DEFERRABLE, NOT DEFERRABLE, or INITIALLY DEFERRED clause. For alternative solutions, consult your IBM representative. The IBM Database Harmony Profiler tool comments out the clause.
  • The Db2 ALTER TABLE statement does not support the ORGANIZE ON clause. This clause is used in Netezza for clustered base tables (CBTs), which are not supported in Db2 products.
ALTER USER No Db2 ALTER USER statement is available. To alter users, you can use the web console.
ALTER VIEW The Db2 ALTER VIEW statement does not support all clauses of the Netezza ALTER VIEW command:
  • The Db2 ALTER VIEW statement does not support the SET PRIVILEGES TO clause.
  • The Db2 ALTER VIEW statement does not support the MATERIALIZE SUSPEND clause, which is used in Netezza for materialized views. Materialized query tables (MQTs) are a possible replacement for Netezza materialized views. The Database Harmony Profiler tool converts materialized views to MQTs.
    To suspend the use of an MQT by the query optimizer, use one of the following approaches:
    • Issue the ALTER TABLE ... DISABLE QUERY OPTIMIZATION statement.
    • Convert the MQT to a regular table by issuing the ALTER TABLE ... DROP MATERIALIZED QUERY statement.
ALTER VIEWS ON No Db2 ALTER VIEWS ON statement is available. The ALTER VIEWS ON command is used in Netezza for materialized views.

Materialized query tables (MQTs) are a possible replacement for Netezza materialized views. The Database Harmony Profiler tool converts materialized views to MQTs. Modify the MQTs that are associated with the base table in the ALTER VIEWS ON command.

BEGIN No Db2 BEGIN TRANSACTION statement is available.

The Db2 products automatically begin transactions (units of work). Remove the BEGIN TRANSACTION command and, if necessary, adjust the application code to disable the autocommit feature.
COMMENT ON The Db2 COMMENT ON statement does not support the LIBRARY and DATABASE object types. Instead on commenting on a database, comment on a schema.
COMMIT [WORK|TRANSACTION] The TRANSACTION parameter is not supported. Remove it or use the WORK parameter. (The ROLLBACK TRANSACTION statement behaves the same way regardless of whether you specify the WORK parameter.)
CREATE AGGREGATE No Db2 CREATE AGGREGATE statement is available. Define aggregates by using the Db2 CREATE FUNCTION statement. The Database Harmony Profiler tool converts the CREATE AGGREGATE command to the CREATE FUNCTION statement.
CREATE DATABASE A Db2 CREATE DATABASE statement is not available. The Database Harmony Profiler tool assumes that the database has only one schema and converts the CREATE DATABASE command to a Db2 SET CURRENT SCHEMA statement so that any subsequently created objects are placed under the same schema.
CREATE GROUP No Db2 CREATE GROUP statement is available. To create groups, you can use the web console.
CREATE HISTORY CONFIGURATION A Db2 CREATE HISTORY CONFIGURATION statement is not available. To collect and view historical data about database usage, use the Db2 monitoring capabilities.
CREATE [OR REPLACE] FUNCTION The Db2 CREATE FUNCTION statement does not support all of the clauses of the Netezza CREATE [OR REPLACE] FUNCTION command:
  • The TABLE ALLOWED and TABLE FINAL ALLOWED clauses have no effect in a Db2 CREATE FUNCTION statement. The Database Harmony Profiler tool comments out the TABLE ALLOWED and TABLE FINAL ALLOWED clauses.
  • In Db2 products, the DEPENDENCIES clause is not used. The Database Harmony Profiler tool comments out the DEPENDENCIES clause.

    To ensure that dependent libraries are accessible, place them in the directory that is specified by the LIBPATH or PATH environment variable on Windows operating systems or in the instance-level function directory ($inst_home_dir/sqllib/function/) on Linux and UNIX operating systems.

CREATE [OR REPLACE] LIBRARY No Db2 CREATE [OR REPLACE] LIBRARY statement is available. The Database Harmony Profiler tool comments out the CREATE [OR REPLACE] LIBRARY command.

To ensure that dependent libraries are accessible, place them in the directory that is specified by the LIBPATH or PATH environment variable on Windows operating systems or in the instance-level function directory ($inst_home_dir/sqllib/function/) on Linux and UNIX operating systems.

CREATE SCHEDULER RULE No Db2 CREATE SCHEDULER RULE statement is available. Consider using Db2 WLM capabilities.
CREATE SCHEMA The Db2 CREATE SCHEMA statement does not support the PATH clause.

For dynamic SQL statements, the SQL path is the value of the Db2 CURRENT PATH special register, which you can change by using the SET PATH command. For static SQL statements, specify the SQL path by using the FUNCPATH bind option.
CREATE TABLE There are several differences between the Db2 CREATE TABLE statement and the Netezza CREATE TABLE command:
  • The Db2 CREATE TABLE statement does not support the ROW SECURITY clause. Consider using row and column access control (RCAC) instead.
  • The NOT NULL constraint must be explicitly specified for unique and primary key columns in the Db2 CREATE TABLE statement.
  • The Db2 CREATE TABLE statement does not support the DEFERRABLE, NOT DEFERRABLE, or INITIALLY DEFERRED clause. For alternative solutions, consult your IBM representative. The Database Harmony Profiler tool comments out the clause.
  • The Db2 CREATE TABLE statement does not support the ORGANIZE ON clause. This clause is used in Netezza for clustered base tables (CBTs), which are not supported in Db2 products. The Db2 ORGANIZE BY clause is not equivalent to the Netezza ORGANIZE ON clause; substituting the ORGANIZE BY clause for the ORGANIZE ON clause will likely cause problems.
CREATE USER No Db2 CREATE USER statement is available. To create users, you can use the web console.
DROP GROUP No Db2 DROP GROUP statement is available. Use roles instead of groups.
DROP HISTORY CONFIGURATION A Db2 DROP HISTORY CONFIGURATION statement is not available. To collect and view historical data about database usage, use the Db2 monitoring capabilities.
DROP USER No Db2 DROP USER statement is available. To delete users, you can use the web console.
GENERATE [EXPRESS] STATISTICS A Db2 GENERATE [EXPRESS] STATISTICS statement is not available. Use the Db2 RUNSTATS command or automatic statistics collection.

The IBM Database Harmony Profiler tool converts the GENERATE STATISTICS command to the RUNSTATS command. The RUNSTATS command does not support the column range syntax as used in the GENERATE STATISTICS command, so the Database Harmony Profiler tool does not preserve that syntax.
GRANT The Db2 GRANT statement does not support all the object types and privileges that the Netezza GRANT command supports:
  • The Db2 GRANT statement does not support the following object types: AGGREGATE, DATABASE, EXTERNAL, GROUP, MANAGEMENT TABLE, MANAGEMENT VIEW, SYNONYM, SYSTEM TABLE, SYSTEM VIEW, and USER. For a suitable alternative, review the Db2 documentation.
  • Some administration privileges are not supported. For a suitable alternative, review the Db2 documentation.
GROOM TABLE A Db2 GROOM TABLE statement is not available. For column-organized tables, reorganization is done automatically. For row-organized tables, use the REORG TABLE statement.
INSERT The Db2 INSERT statement does not support the DEFAULT VALUES clause. Replace the DEFAULT VALUES clause with the DEFAULT keyword for each of the columns. For example, change INSERT INTO tb1(c1, c2) DEFAULT VALUES to INSERT INTO tb1(c1, c2) VALUES (DEFAULT, DEFAULT).
LOCK TABLE The Db2 LOCK TABLE statement does not support the NOWAIT parameter of the Netezza LOCK TABLE command. Also, you must use either the SHARE or the EXCLUSIVE lock mode parameter in the Db2 statement. Consider replacing the Netezza ACCESS EXCLUSIVE mode parameter with the Db2 EXCLUSIVE mode parameter, and consider replacing the Netezza SHARE, SHARE ROW EXCLUSIVE, and EXCLUSIVE mode parameters with the Db2 SHARE mode parameter.
The Database Harmony Profiler tool performs the following actions:
  • Removes the NOWAIT parameter
  • If you did not specify a mode parameter, adds the IN EXCLUSIVE MODE clause
  • Replaces the ACCESS EXCLUSIVE mode parameter with the EXCLUSIVE mode parameter
  • Replaces the SHARE, SHARE ROW EXCLUSIVE, and EXCLUSIVE mode parameters with the SHARE mode parameter
If you use the Database Harmony Profiler tool, you must manually convert all other mode parameters to SHARE or EXCLUSIVE. Also, review the LOCK TABLE statements to ensure that your concurrency requirements are still met.
REVOKE The Db2 REVOKE statement does not support all the object types and privileges that the Netezza REVOKE command supports:
  • The Db2 REVOKE statement does not support the following object types: AGGREGATE, DATABASE, EXTERNAL, GROUP, MANAGEMENT TABLE, MANAGEMENT VIEW, SYNONYM, SYSTEM TABLE, SYSTEM VIEW, and USER. For a suitable alternative, review the Db2 documentation.
  • Some administration privileges are not supported. For a suitable alternative, review the Db2 documentation.
ROLLBACK [WORK|TRANSACTION] The TRANSACTION parameter is not supported. Remove it or use the WORK parameter. (The ROLLBACK TRANSACTION statement behaves the same way regardless of whether you specify the WORK parameter.)
SELECT There are multiple differences between the Db2 SELECT statement and the Netezza SELECT command, some of which you can resolve by setting the SQL_COMPAT global variable to 'NPS':
  • In Db2 products, expressions on column aliases in the SELECT list, as shown in the following example, are not supported by default: 
    SELECT c1 AS a, a+3 AS 
b FROM t1;
    Enable support by setting the SQL_COMPAT global variable to 'NPS'.
  • Referencing SELECT list elements by ordinal positions in the GROUP BY clause, as shown in the following example, is not supported by default:
    SELECT c1 AS a, c2+c3 AS b, 
COUNT(*) AS c FROM t1 
GROUP BY 1, 2;
    Enable support by setting the SQL_COMPAT global variable to 'NPS'.

    Referencing SELECT list elements by ordinal positions in the GROUP BY clause is also not supported by default, but you can enable support by setting the SQL_COMPAT global variable to 'NPS'.

  • Referencing SELECT list elements by aliases in the GROUP BY clause, as shown in the following example, is not supported by default:
    
SELECT c1 as a, count(*) 
FROM t1 GROUP BY a 
ORDER BY a;
    Enable support by setting the SQL_COMPAT global variable to 'NPS'.

    Referencing SELECT list elements by aliases in the ORDER BY clause is also not supported by default, but you can enable support by setting the SQL_COMPAT global variable to 'NPS'.

  • The syntax in the following example is not supported by default:
    SELECT A + B as C , C+2 FROM T1
    Enable support by setting the SQL_COMPAT global variable to 'NPS'.
  • A column alias in a HAVING or WHERE clause of the Db2 SELECT statement is not supported. Replace the alias in the HAVING or WHERE clause with the corresponding column or expression.
  • The ORDER BY clause is not supported in the definition of a materialized query table (MQT), which is a possible replacement for a Netezza materialized view. The IBM Database Harmony Profiler tool converts materialized views to MQTs.

    Rewrite the query to move the ORDER BY clause to an inner SELECT statement. For example, instead of SELECT * from tb1 WHERE c1 < 10 ORDER BY 1, you could write SELECT * from (SELECT * FROM tb1 ORDER BY 1) tbSorted WHERE c1 < 10.

  • The FROM clause is mandatory. For example, the following SELECT statement is not supported: 
    SELECT constant as
alias;
    If you are referencing a value, use the VALUES statement or use FROM sysibm.sysdummy1 as. If you are invoking a procedure, use the CALL statement.
  • The Db2 SELECT statement does not support the TABLE WITH FINAL clause. The Database Harmony Profiler tool removes this clause. Review each occurrence of this clause to verify that removing it does not impact the logic of your queries.
  • The NATURAL keyword is not supported for joins. Instead, use a join with explicit conditions.
SET Db2 products do not support most Netezza session variables. Use other techniques to tune and configure your Db2 product.
SHOW LIBRARY No Db2 SHOW LIBRARY statement is available. The Database Harmony Profiler tool comments out the SHOW LIBRARY command.
TRUNCATE TABLE The Db2 TRUNCATE TABLE statement requires the IMMEDIATE parameter. This parameter specifies that the truncate operation is processed immediately and cannot be undone.
Table 2. SQL compatibility: operators
Netezza operator Db2 support
! factorial operator This operator is not supported. Instead, you can create a factorial function.
^ and ** exponential operators These operators are not supported by default. Enable support by setting the SQL_COMPAT global variable to 'NPS'.
# bitwise XOR operator This operator is not supported by default. Enable support by setting the SQL_COMPAT global variable to 'NPS'.
<< and >> bitwise left shift and right shift operators These operators are not supported. You can replace the operators with multiplication or division by powers of 2. For example, col1 << 4 is equivalent to col1 * power(2, 4).
Table 3. SQL compatibility: miscellaneous items
Netezza language construct Db2 support
System views (which have names of _V_viewname or _VT_viewname) and system tables (which have names of _T_tablename) System views and system tables are not supported. Use the Db2 SYSCAT views, where possible. Otherwise, create your own views.
Ampersand (&) in a column name This syntax is not supported unless you use delimiters, for example, "SALES&".
Underscore (_) as the starting character for an identifier This syntax is not supported unless you use delimiters, for example, "_SALES".