Migrating a database for multiple schema support

By default, multiple schema support is turned off. To activate it, set the enable_schema_dbo_check postgres variable to one of the following values:
  • 0: Multiple schema support is disabled. This mode is the default mode.
  • 1: Multiple schema support is enabled in limited mode.
  • 2: Multiple schema support is enabled in full mode.
If you change the value of the enable_schema_dbo_check variable to switch between modes, and if you have databases that are enabled for Netezza Performance Server Analytics, you must complete the following steps:
  • Scenario 1: You work in mode 0, and you want to switch to mode 1 or 2.
    1. Set the enable_schema_dbo_check variable to 1 or 2.
    2. Restart Netezza Performance Server by using the nzstop command and the nzstart command.
    3. Call the /nz/export/ae/utilities/bin/update_inza_dbs.sh script.
  • Scenario 2: You work in mode 1 or 2, and you want to switch to mode 0.
    1. Ensure that the schema is not set as the default schema for all databases that are enabled for Netezza Performance Server Analytics.
    2. Ensure that the default schemas of your databases are set as necessary. After you switch to mode 0, other schemas are not available.
    3. Set the enable_schema_dbo_check variable to 0.
    4. Call the /nz/export/ae/utilities/bin/update_inza_dbs.sh -n script. For reverse migration, use the option -n that is noschema.
    5. Restart Netezza Performance Server by using the nzstop command and the nzstart command.
  • Scenario 3: You work in mode 1, and you want to switch to mode 2 or vice versa. In this case, extra migration steps are not necessary.
    1. Set the enable_schema_dbo_check variable to 1 or 2.
    2. Restart Netezza Performance Server by using the nzstop command and the nzstart command.

When you work in mode 0, a database has only one schema. Netezza Performance Server Analytics then creates its database objects, such as analytic models or matrices, on this schema. When you work in mode 1 or 2, a database can have multiple schemas. Netezza Performance Server Analytics then creates its database objects on a schema called INZA. The main purpose of the update script is to move the objects between these schemas for all databases that are enabled for Netezza Performance Server Analytics.

In mode 0, the created database objects are always in the current schema. In mode 1 or 2, the database objects are not in the current schema because typically, the INZA schema is not the current schema. However, you can access tables and views in the INZA schema without specifying the schema name explicitly.

For example, the statement SELECT * FROM V_NZA_MODELS works in all modes, regardless of the current schema. It is not necessary to write SELECT * FROM INZA.V_NZA_MODELS.