The Big SQL 4.2 release introduces significant improvements in the Netezza application compatibility enabling virtually out-of-the-box support for Netezza’s SQL and procedure language (NZPLSQL) dialects. This enables many applications written against Netezza to run in Big SQL virtually unchanged. In the Big SQL 5.0.1 release, additional enhancements have been made including support for new data types (BINARY, VARBINARY, INT2, INT4, INT8, FLOAT4 and FLOAT8) as well as improved decimal division semantics which ensure a minimum of 6 for the scale of the result.
Being able to immediately develop Big SQL applications affords a considerable advantage to customers and developers who have invested in Netezza SQL application and NZPLSQL development skills.
The very fabric of the SQL dialects, their keywords and semantics differ in some areas. Big SQL has been extended to support many of the key features of the Netezza SQL dialect with no additional configuration needed. All database products provide libraries of functions to operate on the data. The problem is that no two use the same names for these functions, even if in the end, the functionality is very similar. In addition to the SQL standard set of functions, Big SQL supports a library of built-in functions compatible with Netezza.
Custom Netezza compatibility features
Although every effort was made to provide knobless support for Netezza features, there are cases in which Big SQL is either syntactically or semantically different from Netezza. To handle those cases, you can use the SQL_COMPAT global variable to activate the following optional NPS compatibility features:
- Double-dot notation
- TRANSLATE scalar function syntax
- Grouping by SELECT clause columns
- Expressions refer to column aliases
- Routines written in NZPLSQL
When operating in NPS compatibility mode, you can use double-dot notation to specify a database object.
The syntax of the TRANSLATE scalar function depends on whether NPS compatibility mode is being used.
Which symbols are used to represent operators in expressions depends on whether NPS compatibility mode is being used.
When operating in NPS compatibility mode, you can specify the ordinal position or exposed name of a SELECT clause column when grouping the results of a query.
When operating in NPS compatibility mode, an expression can refer to column aliases that are set in the select list.
When operating in NPS compatibility mode, the NZPLSQL language can be used in addition to the SQL PL language.
The following code snippet shows you how to use the SQL_COMPAT session variable to enable Netezza compatibility for NZPLSQL statements. A first attempt to create a simple NZPLSQL routine fails, because SQL PL procedural language is the default in Big SQL. After the SQL_COMPAT session variable is set to â€˜NPS’, the same NZPLSQL routine is created successfully.
create or replace procedure simple() RETURNS INTEGER LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE thisnum INTEGER := 1; BEGIN RETURN thisnum; END; END_PROC DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "RETURNS INTEGER LANGUAGE" was found following "procedure SIMPLE() ". Expected tokens may include: "
". LINE NUMBER=2. SQLSTATE=42601 set sql_compat='NPS' DB20000I The SQL command completed successfully. create or replace procedure simple() RETURNS INTEGER LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE thisnum INTEGER := 1; BEGIN RETURN thisnum; END; END_PROC DB20000I The SQL command completed successfully.
Thanks to its native multi-dialect SQL support, Big SQL facilitates the enablement of Netezza applications. Vendors of packaged applications can offer their applications for both Netezza and Big SQL at minimal additional cost. Customers can freely choose the vendor that offers the technology that they need without being limited by past choices.