# Netezza Plugin

## Author

- Rob Manning

## Introduction

The Netezza plugin adds Netezza-specific functionality to the SQuirreL SQL Client. Read access is required to the following system views in order for this additional functionality to work correctly:

- _v_objs_owned
- _v_procedure
- _v_synonym
- _v_view

All objects can be found in folders by object type beneath the tree node of the schema (Netezza User), which is also directly beneath the tree node for the catalog (Netezza Database). In the screenshot below the catalog (or database) is RManning46_DB and the schema (or user) is RManning46.

## New Tabs

Synonyms, Sequences, Stored Procedures and Views are shown in the object tree when using this plugin. Synonyms, Stored Procedures and Views have a "Source" tab which displays the source of the selected object. Synonyms also have a "Details" tab which gives synonym-specific information about the object.

## Synonym Source Tab

The source tab for synonyms displays the source for a synonym and can be accessed by navigating to the "SYNONYM" folder beneath a table object in the object tree. An example of this is shown in the following picture:

The source code for triggers is derived from the following query:

    	SELECT
    	'create synonym ' || synonym_name || ' for ' || refobjname
    	FROM _v_synonym
    	where refdatabase = ?
    	and refschema = ?
    	and synonym_name like ?

## Synonym Details Tab

The details tab for a synonym displays information about the selected synonym according to the system catalog. This includes information such as the object that the synonym is an alias for, and what database that object belongs to. An example of this is shown in the following picture:

The information in the details tab for a synonym is derived from the following query:

    	SELECT
    	SYNONYM_NAME,
    	refobjname as Referenced_Object,
    	refdatabase as Referenced_Database ,
    	refdatabase || '.' || synonym_name as Qualified_Name
    	FROM _v_synonym
    	where synonym_name = ?
    	and refschema = ?

## Stored Procedure Source Tab

The source tab for stored procedures displays the source for the selected stored procedure and can be accessed by navigating to the "PROCEDURE" folder in the object tree. An example of this is shown in the following picture:

The source code for stored procedures is derived from the following query:

    	SELECT
    	'create or replace procedure ' || proceduresignature || ' returns ' || returns ||
    	' LANGUAGE NZPLSQL AS BEGIN_PROC ' || proceduresource || ' END_PROC;'
    	FROM _v_procedure
    	WHERE owner = ?
    	and procedure = ?

## View Source Tab

The source tab for views displays the source for a view and can be accessed by navigating to the "VIEW" folder beneath a schema object in the object tree. An example of this is shown in the following picture:

The source code for views is derived from the following query:

    	SELECT 'create or replace view ' || v.VIEWNAME || ' as ' ||
    	v.definition FROM _v_view v, _v_objs_owned o
    	where v.objid = o.objid
    	and o.DATABASE = ?
    	and v.VIEWNAME = ?

## Sequence Info Tab

Netezza's data dictionary doesn't provide enough details about sequences to show detailed information such as next value, increment, start value, max value, cycle, etc. Without this information it is also impossible to automatically reconstruct the source code that could be used to re-create the sequence. However, the sequences are listed in the object tree and minimal information about them is shown in the info tab. The info tab for sequences displays minimal info about a sequence and can be accessed by navigating to the "SEQUENCE" folder beneath a schema object in the object tree. An example of this is shown in the following picture: