Created by Shared Doc User on Feb 16, 2022
This documentation is not maintained. Please refer to doc.castsoftware.com/technologies to find the latest updates.

On this page:

Summary: This page explains how to generate DDL for a NonStop SQL database with SHOWDDL command, for use with the SQL Analyzer extension.

Extraction examples

The SHOWDDL command provided by NonStop SQL generates the DDL statements by object type. See the full documentation of the statement here : http://nonstoptools.com/manuals/SqlMx-Reference.pdf, page 691, SHOWDDL Command. Before analysis, check if you're SQL code is correct.

Show the CREATE statement for a table

>>SHOWDDL CAT.SCH.T1;
CREATE TABLE CAT.SCH.T1
 (
 N INT NO DEFAULT -- NOT NULL NOT DROPPABLE
 , C INT NO DEFAULT -- NOT NULL NOT DROPPABLE
 , CONSTRAINT CAT.SCH.PK PRIMARY KEY (N ASC) NOT DROPPABLE
 , CONSTRAINT CAT.SCH.T1_102261179_0003 CHECK
 (CAT.SCH.T1.N IS NOT NULL AND
 CAT.SCH.T1.C IS NOT NULL) NOT DROPPABLE
 )
 LOCATION \FIGARO.$DATA1.ZSDQXXBK.B7VVVW00
 NAME FIGARO_DATA1_ZSDQXXBK_B7VVVW00
 ATTRIBUTES MAXEXTENTS 600
 STORE BY (C DESC, N ASC)
 ;
-- The following index is a system created index --
CREATE UNIQUE INDEX T1_102261179_0004 ON CAT.SCH.T1
 (
 C ASC
 )
 LOCATION \FIGARO.$DATA2.ZSDUXXBK.B7VVVW00
 NAME FIGARO_DATA2_ZSDQXXBK_B7VVVW00
 ATTRIBUTES MAXEXTENTS 600
 ;
-- The following index is a system created index --
--CREATE UNIQUE INDEX PK ON CAT.SCH.T1
-- (
-- N ASC
-- )
-- LOCATION \FIGARO.$DATA1.ZSDXXXBK.B7VVVW00
-- NAME FIGARO_DATA1_ZSDQXXBK_B7VVVW00
-- ATTRIBUTES MAXEXTENTS 600
-- ;
ALTER TABLE CAT.SCH.T1
 ADD CONSTRAINT CAT.SCH.T1_102261179_0004 UNIQUE
(C) DROPPABLE;

Show the CREATE statement for a view

>>showddl cat.sch.v1;
CREATE VIEW CAT.SCH.V1 ENABLE SIMILARITY CHECK AS SELECT
CAT.SCH.T1.I, CAT.SCH.T1.J from CAT.SCH.T1;

Show the CREATE statement for a stored procedure

>>showddl procedure T110_IO_NN;
CREATE PROCEDURE CAT.SCH.T110_IO_NN
 (
 IN IN1 NUMERIC(9,3),
 OUT OUT2 NUMERIC(9,3)
 )
 EXTERNAL NAME 't110.T110_io_nn
(java.math.BigDecimal,java.math.BigDecimal[])'
 EXTERNAL PATH '/usr/ned/regress/udr'
 LANGUAGE JAVA
 PARAMETER STYLE JAVA
 CONTAINS SQL
 NOT DETERMINISTIC
 ISOLATE
 ;