Generate Netezza View DDL using nz_ddl_view

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:3 mins read

If you are working as a Netezza admin or developer then you may be asked to get DDL or definition of all views available in the particular database. You may have to generate view DDL when you are performing the reverse engineering too. In this article, we will discuss on how to generate Netezza view DDL using nz_ddl_view.

Generate Netezza View DDL using nz_ddl_view

If you are looking for the tool or script to generate the Netezza DDL or definition, then it might be your lucky day. IBM has provided script nz_ddl_view that generate the view DDL or definition for you. The script is available at /nz/support/bin path.

nz_ddl_view Prerequisites

You have to export the Netezza environmental variables (NZ_USER, NZ_PASSWORD, NZ_DATABASE, NZ_HOST) or explicitly provide the details to script during execution.

Generate Netezza View DDL or Definition – nz_ddl_view Usage

Below is the usage of the nz_ddl_view utility:

nz_ddl_view [-format|-noformat] [database [viewname]];

The database name is optional. If not specified, then $NZ_DATABASE will be used instead.

View name is optional. If not specified, then a CREATE VIEW statement will be generated for each view in the database.

Other Options that can be used along with nz_ddl_view

Below are the pattern matching options that can be used while you generate view DDL:

-in <string ...>
-NOTin <string ...>
-like <string ...>
-NOTlike <string ...>

For example;

nz_ddl_table DEV -like %fact% %dim% -notlike %_bu test% -in SALES INVENTORY -notin SALES_FACT

Generate Netezza View DDL or Definition Example

You can automate by wrapping up this script to generate the database view DDL or definition.

Below is the example to generate DDL using nz_ddl_view:

[nz@netezza bin]$ nz_ddl_view TRAINING -format patient 

\echo 
\echo ***** Creating view: "PATIENT"

CREATE or replace VIEW PATIENT 
( 
 ID, 
 NAME 
) AS SELECT 
 PATIENT_1."ID", 
 PATIENT_1."NAME" 
FROM 
 ADMIN.PATIENT_1 ; 
[nz@netezza bin]$

Read:

Tags: