SAS/ACCESS® Interface to Greenplum might not show the "NOT NULL" attribute correctly


When you are using SAS/ACCESS Interface to Greenplum or SAS/ACCESS® Interface to HAWQ, displaying column attributes by querying the dictionary table might not show the correct NOT NULL attribute. For example, the following code would show the attribute as No.

libname gplib greenplm  database=database  stringdate=yes
server="server-name"  schema=public  user=user-ID
password="xxxxx";

proc sql;
    select memname, memtype, name, type, length, notnull
    from dictionary.columns
    where libname='libref'
    and memname='table-name';
quit;

The result would be as follows:

A table displaying Not NULL as no

The NOT NULL attribute might or might not be correct, depending on how the table was created. If the table was created with the column having the NOT NULL constraint, the display is certainly incorrect. However, it does not affect the functionality. If the column in the table has such a constraint, and the constraint is violated, the database gives an error and SAS returns the error.

If it is important to display this attribute correctly, set the Extended Column Metadata attribute on the driver. To do this in a Windows operating environment, select the Extended Column Metadata box on the Advanced tab of the data source in the ODBC Administrator.

Extended Column Metadata is checked and highlighted

Or, if you are working on a UNIX host, such as Linux, enable this option in the odbc.ini file by changing 0 to 1 for this option.

[gpdsn]
...
ExtendedColumnMetaData=1...

On either platform, if you are using a Server=, Port=, or Database= connection instead of a DSN=, you can add a CONOPTS option to the LIBNAME as follows:

libname gplib greenplm  database=database  stringdate=yes
server="server-name"  schema=public  user=user-ID
password="xxxxx" conopts="ECMD=1";

Any of the three options above used for the same code should display the option correctly.

A table showing NOT NULL? as yes

Note: Enabling this option might result in performance degradation, which is why the option is initially disabled.

For more information about this option and what other attributes it affects, see the Progress DataDirect Documentation for ODBC Connectors on the Progress website.