Netezza Database Schema: CREATE, ALTER, DROP and SET SCHEMA

In the previous version, the Netezza system supported one default schema per database. You can configure multiple schema within database in latest release. In this article, we will check on Netezza database schema and how to create, alter, drop and set schema for Netezza database.

Netezza Database Schema

In the latest release, Netezza system can validate the schema information i.e. you can configure the system to return an error for any queries that specify an invalid or non-existent schema, or you can configure the system to return a warning message for queries that use an invalid schema and to use the default schema for a database.

Enables support for Netezza database schema

You can enable the support for Netezza Database schema by editing the property enable_schema_dbo_check in /nz/data/postgresql.conf file.

You will endup getting below error if you have enable_schema_dbo_check = 0.

TRAINING.ADMIN(ADMIN)=> CREATE SCHEMA test_schema; 
ERROR: CREATE SCHEMA: can not be called with enable_schema_dbo_check = 0

Use below steps to enable Netezza database schema:

[nz@netezza ~]$ vi /nz/data/postgresql.conf 
[nz@netezza ~]$ grep -i "enable_schema_dbo_check" /nz/data/postgresql.conf 
 enable_schema_dbo_check = 2 
[nz@netezza ~]$ nzstop 
Warning: nzstop does not stop SPU virtual machines! 
If you want to stop them, use the following command: 
 nzpush -all power off

[nz@netezza ~]$ nzstart 
nzstart: Warning: Using user-specified topology: /nz/nse/initTopology.cfg 
(startupsvr) Info: NZ-00022: --- program 'startupsvr' (5576) starting on host 'netezza' ... --- 
[nz@netezza ~]$

Disable support for Netezza database schema

Youcan disable the support for Netezza Database schema by editing the property enable_schema_dbo_check in /nz/data/postgresql.conf file. The Value should be 0 i.e. enable_schema_dbo_check = 0

Netezza Database CREATE SCHEMA

You should be admin or have appropriate permission to create, alter drop and set schema.

To create a schema, use the CREATE SCHEMA command:

TRAINING.ADMIN(ADMIN)=> CREATE SCHEMA test_schema; 
CREATE SCHEMA

Set the Current Netezza Database Schema

To change the current schema, use the SET SCHEMA command:

TRAINING.ADMIN(ADMIN)=> SET SCHEMA test_schema; 
SET SCHEMA

Check the current schema in Netezza use below statement:

TRAINING.TEST_SCHEMA(ADMIN)=> select current_schema;
CURRENT_SCHEMA
—————-
TEST_SCHEMA
(1 row)

Alter Netezza Database Schema

You can alter a schema to change its name, its authorization user, or the contents of the schema path.

To change the name of the schema, use the following command:

TRAINING.TEST_SCHEMA(ADMIN)=> ALTER SCHEMA test_schema RENAME TO new_test_schema; 
ALTER SCHEMA

Drop Netezza Database schema

If you are logged in as the admin user or you have been granted privilege to drop a schema, you can drop the schema using the DROP SCHEMA command.

TRAINING.ADMIN(ADMIN)=> DROP SCHEMA new_test_schema CASCADE; 
DROP SCHEMA

You cannot drop the current schema. You will end-up getting below error if you try to drop current schema:

TRAINING.NEW_TEST_SCHEMA(ADMIN)=> DROP SCHEMA new_test_schema CASCADE; 
ERROR: DROP SCHEMA: current schema 'TRAINING'.'NEW_TEST_SCHEMA' cannot be dropped.

List All Schema’s in Netezza Current Database

Use the SHOW SCHEMA command to list all of the schemas defined for the current database.

TRAINING.ADMIN(ADMIN)=> show schema; 
 DATABASE | SCHEMA | OWNER 
----------+--------------------+------- 
 TRAINING | ADMIN | ADMIN 
 TRAINING | DEFINITION_SCHEMA | ADMIN 
 TRAINING | INFORMATION_SCHEMA | ADMIN 
 TRAINING | TEST_SCHEMA | ADMIN 
(4 rows)

Netezza Database Default Schema

Usually, ADMIN schema is the default schema in the Netezza Database. However, you can verfy the default schema for the database by querying _v_database system view.

TRAINING.ADMIN(ADMIN)=> select database, defschema from _v_database; 
 DATABASE | DEFSCHEMA 
----------+----------- 
 SYSTEM | ADMIN 
 TEST | ADMIN 
 TRAINING | ADMIN 
(3 rows)

Read: