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 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]$