Db2 compatibility for IBM PureData System for Analytics (Netezza) built-in and SQL Extensions toolkit functions

Db2® products provide many of the same functions that are available in IBM PureData® System for Analytics (Netezza®). In some cases, you must use an alternative, such as a Db2 function with a different name that provides similar support.

The following tables identify the Netezza functions that do not have corresponding Db2 functions with the same names and behaviors. Alternatives to the Netezza functions are mentioned where possible.

Netezza SQL functions

Table 1. Fuzzy string search functions
Netezza function Db2 alternatives
dle_dst No alternative is available.
le_dst No alternative is available.
Table 2. Phonetic matching functions
Netezza function Db2 alternatives
dbl_mp No alternative is available.
nysiis No alternative is available.
pri_mp No alternative is available.
score_mp No alternative is available.
sec_mp No alternative is available.
Table 3. Value functions
Netezza function Db2 alternatives
current_catalog Create a session variable.
current_db Create a session variable.
current_tx_path Create a session variable.
current_tx_schema Create a session variable.
current_userid Create a session variable.
current_useroid Create a session variable.

Netezza SQL extensions functions

Table 4. Trigonometric functions
Netezza function Db2 alternatives
pi Create a user-defined function (UDF).
Table 5. Random number functions
Netezza function Db2 alternatives
setseed Pass the seed to the RANDOM function.
Table 6. Numeric functions
Netezza function Db2 alternatives
dceil Use the CEIL function with an input type of DOUBLE.
dfloor Use the FLOOR function with an input type of DOUBLE.
fpow Use the POW function with an input type of DOUBLE.
numeric_sqrt Use the SQRT function with an input type of DECFLOAT.
n! No alternative is available.
Table 7. Binary mathematical functions
Netezza function Db2 alternatives
intNshl Use two's complement multiplication.
intNshr Use two's complement division.
Table 8. Date and time functions
Netezza function Db2 alternatives
age (both versions) A Db2 AGE function is available, but it returns INTEGER.
duration_add Use a labeled duration or use date, time, or timestamp arithmetic, as documented in Datetime operations and durations.
duration_subtract Use a labeled duration or use date, time, or timestamp arithmetic, as documented in Datetime operations and durations.
timeofday Use the VARCHAR_FORMAT function with the CURRENT_TIMESTAMP special register.
Table 9. Character string functions
Netezza function Db2 alternatives
translate A Db2 TRANSLATE function is available, but the default order of options for this function is different from the order of options for the Netezza translate function. To enable the Netezza order of options for the Db2 TRANSLATE function, set the SQL_COMPAT global variable to 'NPS'.
unichr No alternative is available.
unicode No alternative is available.
unicodes No alternative is available.
Table 10. Conversion functions
Netezza function Db2 alternatives
hex_to_binary Use the HEXTORAW function.
hex_to_geometry No alternative is available.
int_to_string No alternative is available.
string_to_int No alternative is available.
Table 11. Miscellaneous non-aggregate functions
Netezza function Db2 alternatives
get_viewdef Query the SYSCAT.VIEWS TEXT column.
Table 12. Additional functions
Netezza function Db2 alternatives
dense_rank This function is supported only in OLAP queries.
rank This function is supported only in OLAP queries.
trim There are differences in more complex usage.

SQL Extensions toolkit functions

Table 13. SQL Extensions toolkit functions
Category of SQL Extensions toolkit functions Specific functions Db2 alternatives
XML All functions, for example, IsValidXML There is no one-to-one mapping between Netezza XML functions and Db2 XML functions. Use the appropriate Db2 XML function.
Data transformation The compress, compress_nvarchar, decompress, decompress_nvarchar, uudecode, and uuencode functions No alternatives are available. However, Db2 products use compression on BLU tables by default, so you might not need to compress data at the column level yourself.
The encrypt, encrypt_nvarchar, decrypt, decrypt_nvarchar, fpe_decrypt, and fpe_encrypt functions The Db2 databases are encrypted. However, if you want to use a function, consider using the Db2 ENCRYPT, DECRYPT_BIN, and DECRYPT_CHAR functions. For masking, you can use row and column access control (RCAC).
Hashing The hash_nvarchar function Using the Db2 HASH function, you can create a sourced function with the name hash_nvarchar.
Text analytics functions The regexp_extract_all, regexp_extract_all_sp, regexp_extract_sp, and regexp_replace_sp functions Consider rewriting your code to use the Db2 REGEXP_EXTRACT function.
Array functions All functions, for example, add_element Consider using SQL PL array support.
Collection functions The collection and element_type functions Consider using SQL PL array support instead.
Miscellaneous functions The corr, covar_pop, and covar_samp functions The following Db2 functions are available, with synonyms that correspond to the Netezza names:
  • CORR is a synonym for the CORRELATION function.
  • COVAR_POP is a synonym for the COVARIANCE function.
  • COVAR_SAMP is a synonym for the COVARIANCE_SAMP function.
The mt_random function If using the Mersenne Twister pseudorandom number generator is not necessary, use the Db2 RANDOM function.