Tip:
Highlight text to annotate it
X
With the introduction of NPS v7.0.3 full schema support was implemented.
This video will give you an introduction into the
features of full schema support, the modes
that are supported by full schema support and how to implement
full schema support. Implementing full schema support brings with it many benefits.
These include multiple developers able to work on their
own applications and projects isolated from other developers.
It enables easy migration of third party database systems
into the PureData environment. It simplifies the third party database
schema transformations
into the PureData environment. And it allows multiple applications and users
to access individual schemas without impacting
other applications and users. Prior to NPS 7.0.3,
there was only a single default schema name per database.
And this could not be changed. It had to match the name of the database
owner. And if the database owner changed
so did the schema name. With the introduction NPS 7.0.3
multiple schema support has been implemented.
This allows a user to define multiple schemas
within each database. A schema is a catalog or namespace
for related objects such as tables, views, synonyms, etcetera.
Also allows objects with the same name
to be defined in multiple schemas or different schemas
within the same database and yet be entirely separate.
Within each database, besides multiple schemas,
the default schema can now be changed and is not dependent upon the database
owner.
After multi-schema support has been enabled,
there are two modes to be considered. The first mode to consider is the
compatibility mode.
And this is the default after implementation
of multi-schema support. It allows users who upgraded
to NPS 7.0.3 to continue using their system
without having to make any changes to their existing objects.
This allows the system to operate in exactly the same way as it did
prior to an upgrade to NPS 7.0.3. All new schema syntax is ignored
and any new schemas or syntax associated with schemas
is disallowed. Unlike compatibility mode,
multi-schema mode has several options. There's the relaxed enforcement option
where warnings are given for incompatibilities.
Or there is a strict enforcement option where
errors are given and processing is stopped. There are also
the default end-user schema options. These determine which schema a user will
be placed into
when they connect to a database. Configuration steps to enable the multi
schema mode are very straightforward.
Firstly, you have to edit postgresql configuration file
that resides in the /nz/data
folder. Within this file we have to edit
the enable_schema_dbo_check
variable. This variable has three possible values.
These values are related to the levels of enforcement of
the scheme support: zero means
ignore any schema references; one
is the relaxed enforcement. If a schema is used
it's not the correct schema, a warning will be issued; two
is the equivalent of the strict enforcement. If a schema reference is made
and it is not the correct schema then error message will be issued.
If you want to apply this change to the system immediately
then it is necessary to restart the appliance.
Secondly, you have to decide whether you can use default
or use the schema option. This is enabled
by editing, in the same file, the variable
enable_user_schema
By default, it is set to false which means that the
default schema option is in place.
If you change this to enable_user_schema
equal true then the user schema
option is enforced. Now we're going to see the system
without schema support enabled through the eyes of the tools that are available
with Netezza.
In all of these tools, there are no references to schemas.
These will only become apparent once we have enabled schema support.
Now we will edit the postgresql configuration file
to enable multi-schema support. We do this by going into the folder
where the file resides and doing VI
on that file.
Now that we are in edit mode on the file, we need to find the variable
that has to be changed. This is the enable_schema_dbo_check
variable.
For this demonstration we're going to use the strict
enforcement and we're going to use the user schema
option. We are going to edit these two variables
appropriately. So the enable_schema_dbo_check needs to be set to
two. And the enable_user_schema variable
needs to be set to true. Having done that,
we now save the file and return to the command line.
For this demonstration, we're going to activate the full schema support
immediately. To do this we're going to stop
and start the appliance so the configuration files are re-read
and activated. We do this by issuing the NZSTOP
followed by the NZSTART. Once these have successfully completed,
we will check that the system is up and running by using the NZSTATE command.
Now the system is back online, we can look at the effect
the enablement has had on the system.
Here, using the Aginity Workbench, we can see
that the schemas are now showing up.
Here, we can see a set of schemas predefined by the system
upon enablement called the definition schemas.
Using the command-line interface, we're going to log into the system
and use some of the new functionality that is available
with full schema support. Having logged into the system,
we use NZSQL to attach to a database.
In this instance, we have connected to the admin database as the user STUDENT.
Here, we issue one of the new commands to see what the current schema is
that we are running under. And this is the default
admin, in this case. Now we are creating a new database
to demonstrate further functionality of schema support.
Here, the database called student DB.
After clearing the screen and reconnecting to the admin database,
now I connect to the new Student database.
Here, we can compare the default schemas
shown by ADMIN in the Admin Database
and STUDENT in the Student database.
As you can see, I did not have to create Student schema.
It was created automatically for me by the system.
Now we are issuing a Select for the new view
that has been created with the implementation of
full schema support.
This command is also demonstrating the new schema level
in the object naming, in this case
definition_schema preceding the actual
view name. This particular view
is going to provide us with the list of all the schemas
that are available to us.
Now, we are creating a second schema in the Student database.
This will allow us later to create objects with the same name
but different schema names.
Having successfully created the new schema, we now list the available schemas
and there, in the last line, we can see the new schema.
Next, we are reconnecting to the Student database to see the effective of two
schemas.
Firstly, we will see the default schema when we connect to a database
by using the current schema command.
And in this case, it is Student as per the user ID.
Then we set the schema name to our new schema
student_db2
Now, if we look at the current schema, we can see that we have changed.
The next step in this demonstration is to create
tables with the same name in different schemas.
Here we are creating table Tab1
while we are in the schema Student.
Having
successfully created the table Tab1, we can now look at the list of
relations that we have within this database. As you can see, we have a schema
of Student and a table of Tab1. Now,
we're going to change our current schema to the new schema
Student_db2. Having changed schemas
we can now create a table with the same name
but under a new schema. Here you can see the list of relations under the new schema.
No,w we're going to demonstrate how to select from
tables in multiple schemas without having to change from the current schema.
Having successfully executed Select from Tab1 in
the current schema of Student, we're now selecting from
Tab1 in the schema Student_db2
And to show that nothing is impacted
by selecting from schemas other than the current schema
here we're repeating the Select on
the default schema Tab1. This concludes our demonstration
of the introduction to full schema support. During this video
we have introduced you to the newly implemented for schema support.
We have given you a demonstration of some of the functionality that is available
with that support
and we have introduced you to some of the options and modes that are available.
To get a greater understanding of all the new functionality that is available
the following are some of the courses and certifications that are available
for the PureData System for Analytics.