SCHEMA= is not honored with the SORT procedure when running against Netezza


When you run the SORT procedure against a Netezza table, the schema specified with the SCHEMA= LIBNAME option might be ignored. This behavior might result in errors referencing the table in the ADMIN schema rather than the one you specified, or it might cause incorrect results.

Example 1

In the following example, the expected results are that the sort of the Netezza table TEST.MYSCHEMA.TESTSORT is written to a new Netezza table TEST.MYSCHEMA.TESTSORTOUT.

libname nz netezza server=server-name user=user-ID pwd=password database=database-name schema=MY-SCHEMA; proc sort data=nz.testsort out=nz.testsortout nodupkey;
by a;
run;

The following error occurs when the SAS generated query does not include the schema and the table TESTSORT does not exist in the ADMIN schema:

ERROR: CLI execute error: ERROR: relation does not exist TEST.ADMIN.TESTSORTOUT
ERROR: Error during in-database processing.
ERROR: SQL generation encountered a critical failure and cannot revert to standard processing. To avoid this failure, set the SQLGENERATION option to NONE.

The error "CLI execute error: ERROR: Relation 'TEST.ADMIN.TESTSORTOUT' does not have attribute 'varname'" might occur if the following conditions are true:

Example 2

In the following example, the expected results are that the sort of the Netezza table TEST.MYSCHEMA.TESTSORT is written to the SAS data set WORK.TESTSORTOUT.

libname nz netezza server=server-name user=user-ID pwd=password database=database-name schema=MY-SCHEMA; proc sort data=nz.testsort out=work.testsortout nodupkey;
by a;
run;

However, when the Netezza table TESTSORT is in both the ADMIN schema and the MY-SCHEMA schema, but the data in the two tables is different, the output from the sort might be from the ADMIN.TESTSORT.

Workaround

To work around the above behaviors, set the system option SQLGENERATION to NONE as shown below:

options sqlgeneration=none;