Create Table DDL
Defining a table in DDL
The CREATE TABLE statement defines a table at the current server. The definition must include its name and the names and attributes of its columns. The definition may include other attributes of the table such as primary key. On the IBM Power i system a list of the tables defined may be queried using the SQL statement:
SELECT system_table_name FROM qsys2.systables WHERE table_name = 'OAS_ELEMENT' and table_schema = 'CODA12TST' SELECT system_table_name, TABLE_NAME FROM qsys2.systables WHERE table_schema = 'CODA12TST'
SYSTABLES identifies tables: Long names are in the column table_name, while ten character (short) names are
in the column table_name. Table_schema represents the library name.
/*----------------------------------------------------------------------*/
/* Loan Supplemental Financial table */
/*----------------------------------------------------------------------*/
--DROP TABLE P3076801/FPMSUPP CASCADE;
CREATE TABLE P3076801/FPMSUPP
(
Loan for FSLOAN Dec (7,0) not null default,
Transfer for FSTXFER Num (1,0) not null default,
Spread for FSSPREAD Char(10) not null default,
DefaultForExcessPayment for FSDFTADL Char(2) not null default,
LastStatementPrintDate for FSSTMDT Timestamp not null default,
InterestOnlyExpiration for FSINTEXP Date not null default,
SupressStatementPrint for FSSUSPRT Char(1) not null default,
CreatedBy for FSENBY Char(10) not null default,
CreatedIn for FSENIN Char(10) not null default,
CreatedOn for FSENTS Timestamp not null default,
ChangedBy for FSUPBY Char(10) not null default,
ChangedIn for FSUPIN Char(10) not null default,
ChangedOn for FSUPTS Timestamp not null default
'0001-01-01-00.00.00.000000'
)
NOT VOLATILE
RCDFMT FPFSUPP;
RENAME TABLE FPMSUPP TO Loan_Supplemental_Financials
FOR SYSTEM NAME FPMSUPP;
-- put text descriptions on file and fields
LABEL ON TABLE FPMSUPP
IS 'Loan Supplemental Financial table';
LABEL ON COLUMN FPMSUPP
( -- <--text-visible-in-DBU--><--remainder-of-text---->
FSLOAN text is 'Loan Number',
FSTXFER text is 'Transfer Code',
FSSPREAD text is 'Spread Method',
FSDFTADL text is 'Default for additional $ OV=overage, PB-prinbal',
FSSTMDT text is 'Last statement print date',
FSINTEXP text is 'Interest-Only Expiration Date',
FSSUSPRT text is 'Supress Statement Print',
FSENBY text is 'Created by User Profile',
FSENIN text is 'Created in program',
FSENTS text is 'Created on date-time',
FSUPBY text is 'Changed by user profile',
FSUPIN text is 'Changed in Program',
FSUPTS text is 'Changed on date-time'
);
-- put column headings on file and fields
LABEL ON COLUMN FPMSUPP
( -- <-----col-hdg-1----><-----col-hdg-2----><-----col-hdg-3---->
FSLOAN is 'Loan ',
FSTXFER is 'Tfr ',
FSSPREAD is 'Spread Method ',
FSDFTADL is 'Dft Addl Amt ',
FSSTMDT is 'Last Statement Date ',
FSINTEXP is 'Interest Only Expiration ',
FSSUSPRT is 'Supress Statement Print ',
FSENBY is 'Rcd Added by User ',
FSENIN is 'Rcd Added in Pgm ',
FSENTS is 'Rcd Added on Date-Time ',
FSUPBY is 'Rcd Chged by User ',
FSUPIN is 'Rcd Chged in Pgm ',
FSUPTS is 'Rcd Chged on Date-Time '
);
-- add commentary
COMMENT ON TABLE FPMSUPP is
'This table contains additional financial information for a Loan. A Loan can opt
ionally have one record here for each Loan in FPMFINC.'
;
COMMENT ON COLUMN FPMSUPP
( FSLOAN is 'Loan Number'
, FSTXFER is 'Transfer Code'
, FSSPREAD is 'Spread Method :: Currently only for HELOCs'
, FSDFTADL is 'Default for additional $ OV=overage, PB-prinbal :: What to do wit
h payment above what is due. Only for CONVENTIONAL.'
, FSSTMDT is 'Last statement print date'
, FSINTEXP is 'Expiration date of Interest-Only period'
, FSSUSPRT is 'Supress statement print'
, FSENBY is 'Created by User Profile'
, FSENIN is 'Created in program'
, FSENTS is 'Created on date-time'
, FSUPBY is 'Changed by user profile'
, FSUPIN is 'Changed in Program'
, FSUPTS is 'Changed on date-time'
);
;
Syntax
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
>>-CREATE--+------------+--TABLE--table-name--+----------------------------------------+-->
'-OR REPLACE-' '-FOR SYSTEM NAME--system-object-identifier-'
.-,------------------------------------------.
V |
>--+-(----+-column-definition----------------------+-+--)-+----->
| +-LIKE--+-table-name-+--+--------------+-+ |
| | '-view-name--' '-copy-options-' | |
| +-unique-constraint----------------------+ |
| +-referential-constraint-----------------+ |
| '-check-constraint-----------------------' |
+-LIKE--+-table-name-+--+--------------+---------------+
| '-view-name--' '-copy-options-' |
+-as-result-table--+--------------+--------------------+
| '-copy-options-' |
'-materialized-query-definition------------------------'
LIKE table
/*----------------------------------------------------------------------*/
--DROP TABLE VRWSTMTS CASCADE;
CREATE TABLE VRWSTMTS
LIKE VRHSTMTS
VOLATILE
RCDFMT VRFSTMTS;
RENAME TABLE VRWSTMTS TO ARM_Current_Statement_Headers
FOR SYSTEM NAME VRWSTMTS;
-- put text descriptions on file and fields
LABEL ON TABLE VRWSTMTS
IS 'Statement Header Workfile';
-- add commentary
COMMENT ON TABLE VRWSTMTS is
'This is workfile contains information about Billing Statements being processed.
A loan should have only one record at most for the current cycle (month) being
billed. Once processed, the records are moved into the Billing Statement Header
(Repository) File.'
;