Netezza Synonyms Best Practices and Examples

Netezza synonyms allow you to create easily typed names (you can remember and type easily) for long table, view names, or any other objects. You can create SQL synonyms as an alternate way of referencing tables or views that present in the current or other databases on the Netezza same system.

Netezza Synonyms allow you to create another level of abstraction for the database objects (tables, views) and that allows and enable you to swap the underlying database objects without modifying the code that references these objects. You can even create a synonym for a non-existent table or view as Netezza will expand the table_reference to its fully qualified form at the run-time.

Read:

• Netezza nzload command and its usage
• nzsql command and its usage
• Netezza Cross Database Access and its Restrictions

Netezza Create Synonyms

You can create the synonyms using Netezza SQL CREATE SYNONYM statement. All synonyms are public and view-able by all users.

Syntax:

CREATE SYNONYM synonym_name FOR table_reference;

Where:

The synonym_name is a name that follows the table and view naming conventions and table_reference can be Plain name (table or view name) or database qualified name.

Netezza Synonyms Example

For example, to create a synonym for PATIENT in database REF, You can enter:

TRAINING.ADMIN(ADMIN)=>CREATE SYNONYM ref_pat FOR ref..patinet;

Commonly used Netezza Synonyms commands

Just like other database objects you can create, drop or alter the Netezza synonyms. Below are the some commonly used commands to manipulate the synonyms.

To drop Netezza synonyms:

DROP SYNONYM ref_pat;

To alter Netezza synonyms:

ALTER SYNONYM ref_pat RENAME to new_ref_pat;

To grant Privileges to synonyms:

GRANT [CREATE] SYNONYM TO user_or_group_name;

To revoke Privileges to synonym:

REVOKE [CREATE] SYNONYM FROM user_or_group_name;

Netezza Synonym Restrictions

• You cannot create synonyms for temporary tables, remote databases, or other synonyms
• You cannot create a synonym with the same name as a table or view that already exists in the same database
• And also you cannot create a table or view with a name that matches an existing synonym.