Schema support

You can configure the Netezza Performance Server system to support multiple schemas in a database. By default, the behavior allows only one schema, which matches the user name of the user that created the database. The enable_schema_dbo_check variable controls whether the system allows users to create and manage multiple schemas in a database, and if so, the actions the system takes when processing a query with an invalid schema:

• 0 (the default) causes the system to ignore any user-specified schema and use the default schema. The system does not allow commands to set, create, or manage schema objects.
• 1 enables multiple schema support in limited mode. Users can create, alter, set, and drop schemas. If a query references an invalid schema, the system displays a warning message and uses the current schema for the database session or a database’s default schema for cross-database queries.
• 2 enables enforced support for multiple schemas. Users can create, alter, set, and drop schemas. If a query references an invalid schema, the query returns an error for the query. This is the default value.

You can set the enable_schema_dbo_check variable in the postgresql.conf file and you must restart the Netezza Performance Server software for the change to take effect. For more information on setting the variable, see the IBM® Netezza® System Administrator’s Guide.

The enable_user_schema variable is set to FALSE by default, which means that if users do not specify a schema when they connect to a database, the system connects them to the default schema for the database. If enable_user_schema is set to TRUE, the system connects them to a schema that matches the database user name, and creates that schema if one does not exist in the database.