Generate Netezza Table DDL using nz_ddl_table

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

If you are working as a Netezza admin then you may be asked to get DDL of all tables available in the particular database. You can still do that in hard way such as using Aginity workbench, WinSQL, or Squirrel workbench. This this article, we are going to discuss the easy way to generate Netezza Table DDL using nz_ddl_table.

Generate Netezza Table DDL

Generate Netezza Table DDL using nz_ddl_table

nz_ddl_table is a Netezza provided utility and is used to generate the Netezza table DDL from particular database. Utility will generate the DDL for all table in the database if you do not provide the table name. This utility is available at /nz/support/bin path. You either add this path to the system PATH variable or use fully qualified path to execute nz_ddl_table utility.

Read:

nz_ddl_table 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 Table DDL – nz_ddl_table Usage

Below is the usage of the nz_ddl_table utility:

nz_ddl_table [-nocalc] [-strict] [-constraints] [database [<tablename> [-rename <new_tablename>]]]

Generate Netezza Table DDL using nz_ddl_table Examples

Generate Netezza Table DDL using nz_ddl_table

Below is the example to generate Netezza table DDL using IBM provided scripts:

[nz@netezza bin]$ nz_ddl_table TRAINING PATIENT_1 
\echo 
\echo ***** Creating table: "PATIENT_1" 
CREATE TABLE PATIENT_1 
( 
 ID integer, 
 NAME character varying(10) 
) 
DISTRIBUTE ON (ID) 
;

/* 
 Number of columns 2 
 (Variable) Data Size 6 - 16 
 Row Overhead 30 - 28 
 ====================== ============= 
 Total Row Size (bytes) 36 - 44 
*/

[nz@netezza bin]$

Generate Netezza all Table DDL using nz_ddl_table

[nz@netezza bin]$ nz_ddl_table TRAINING 
\echo 
\echo ***** Creating table: "ARRAY_T" 
CREATE TABLE ARRAY_T 
( 
 COL1 integer, 
 COL2 character varying(100) 
) 
DISTRIBUTE ON (COL1) 
;

/* 
 Number of columns 2 
 (Variable) Data Size 6 - 106 
 Row Overhead 30 
 ====================== ============= 
 Total Row Size (bytes) 36 - 136 
*/

\echo 
\echo ***** Creating table: "INT_SAMPLE_SALES" 
CREATE TABLE INT_SAMPLE_SALES 
( 
 MONTH_ID integer, 
 SALE_QTY integer 
) 
DISTRIBUTE ON (MONTH_ID) 
;

/* 
 Number of columns 2 
 (Constant) Data Size 8 
 Row Overhead 28 
 ====================== ============= 
 Total Row Size (bytes) 36 
*/

\echo 
\echo ***** Creating table: "MERGE_DEMO1" 
CREATE TABLE MERGE_DEMO1 
( 
 ID integer not null, 
 FIRSTNAME national character varying(100), 
 LASTNAME national character varying(100) 
) 
DISTRIBUTE ON (ID) 
;

/* 
 Number of columns 3 
 (Variable) Data Size 8 - 808 
 Row Overhead 28 
 ====================== ============= 
 Total Row Size (bytes) 36 - 836 
*/

[nz@netezza bin]$