IBM PureData System for Analytics, Version 7.1

Enabling multiple schema support

About this task

By default, the IBM® Netezza® system does not support the ability to create and manage multiple schemas within a database. The default and only schema for each database is based on the name of the owner of the database. Starting in Release 7.0.3 and later, you can configure the system to support multiple schemas.

For a new Netezza system with no existing data, enable multiple schema support. This can improve the efficiency and ease when you are migrating data to the Netezza system from other platforms, especially those that use multiple schemas in a database.

Note: For an existing Netezza system, do not enable multiple schema support until after you verify the behavior of the system and your existing queries. After you enable multiple schema support and users create schemas, the downgrade process becomes more complicated. For example, before you can downgrade to a release that does not support multiple schemas, no database can have more than one schema. You must update each database with multiple schemas to drop all but one of those schemas.

If you are upgrading to Release 7.1.x from a 7.0.4 or later system that has multiple schema support enabled, the multiple schema support remains enabled after the upgrade. Upgrades preserve the schema configuration setting that your system had before the upgrade.

Procedure

Log in to the Netezza active host as the nz user.
Using a text editor, open the /nz/data/postgresql.conf file.
Locate the enable_schema_dbo_check variable and uncomment it by deleting the # character at the beginning of the line.
Change the value of the variable to one of the following values:
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 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.
Save the changes to the postgresql.conf file and close the file.
Restart the Netezza software by using the nzstop and then the nzstart commands for the changes to take effect.

What to do next

Set enable_schema_dbo_check to 1 to allow users to create and manage schemas and return a warning when queries reference an invalid or nonexistent schema. After you are sure that queries and users are using schemas correctly, you can change the variable value to 2 to return errors for queries that use invalid schemas.

The variable has a default setting of 0, which causes the system to operate in a compatible mode with the older releases; that is, users cannot create, manage, set, or drop schemas. The system ignores any schema information and uses the default schema for the database to which the client is connected.