Generate DDL for a NonStop SQL database
Introduction
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
;