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
Netezza function | Db2 alternatives |
---|---|
dle_dst | No alternative is available. |
le_dst | No alternative is available. |
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. |
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
Netezza function | Db2 alternatives |
---|---|
pi | Create a user-defined function (UDF). |
Netezza function | Db2 alternatives |
---|---|
setseed | Pass the seed to the RANDOM function. |
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. |
Netezza function | Db2 alternatives |
---|---|
intNshl | Use two's complement multiplication. |
intNshr | Use two's complement division. |
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. |
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. |
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. |
Netezza function | Db2 alternatives |
---|---|
get_viewdef | Query the SYSCAT.VIEWS TEXT column. |
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
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:
|
The mt_random function | If using the Mersenne Twister pseudorandom number generator is not necessary, use the Db2 RANDOM function. |