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.
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:
|
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:
|
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:
|
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:
|
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:
|
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:
|
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:
|
REVOKE | The Db2 REVOKE statement does not support all the
object types and privileges that the Netezza REVOKE
command supports:
|
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':
|
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. |
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) . |
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". |