The following topics provide necessary knowledge on Generating for SQL.
With the function Generate SQL Create Statements you can generate CREATE
TABLE or CREATE VIEW statements. For Adabas SQL
Server you can generate CREATE TABLE DESCRIPTION and
CREATE CLUSTER DESCRIPTIONS. These SQL statements
are stored as Natural members.
This document covers the following topics:
Calling the Function: Generate SQL Create Statements
The Generate SQL Create Statements screen is called with
function code G and object code CR in a Predict main menu or with the command
GENERATE CR.
13:13:49 ***** P R E D I C T ***** 2007-05-31
Plan 0 - Generate SQL Create Statements -
File ID ..................*
SQL Server ...............* <Default Server>
Save as member ............ Save in library ........... CRELIB
Overwrite option .......... Y (Y,N) Op. system member .........
Punch / output ...........* N
List generated code ....... Y (Y,N)
Truncate creator .......... N (Y,N) Label on .................. Y (Y,N)
Generate defaults ......... Y (Y,N) Comment on ................ Y (Y,N)
Generate procedures ....... Y (Y,N)
Generate indexes .......... Y (Y,N) Defer option for indexes .* N
Command ===>
Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
Help Next Stop Last LnkEl Flip Print Impl AdmFi SelFi Prof Main
|
Parameters for SQL
Values for fields which have been locked by your data dictionary administrator cannot be
overwritten. These fields are skipped when positioning the cursor with the TAB
key. See Generation
Defaults.
| Parameters |
| The parameters Save as member, Save in library,
Overwrite option and Op. system member, Punch/output and List generated code are
described in the section Generation Defaults. The parameter SQL Server
is described in Common Parameters for
All DB2 Types. The parameter SQL Server only applies to
files of type D and E (DB2 tables and views).
|
| File ID |
ID of the Predict file object from which
the CREATE statement is to be generated.Enter an asterisk to
display a selection screen. Alternatively, use an asterisk as a wildcard.
For example, enter A* to display IDs beginning with A only.
|
CREATE statements can be
generated from the following file types:
|
| A, B |
(with parameter Adabas SQL usage set to Y) Adabas tables
and views
|
| AT |
Adabas table clusters |
| D, E |
DB2 tables and views |
| BT, BV |
Adabas D tables and views |
| JT, JV |
Ingres tables and views |
| OT, OV |
Oracle tables and views |
| XT, XV |
Informix tables and views |
| YT, YV |
Sybase tables and views |
| X |
General SQL file |
|
| Truncate creator |
| Y |
The creator is truncated from all object names in the
CREATE statement.
|
For DB2 CREATE statements: If
parameter Generate indexes is set to Y, the creator is truncated in the
generated distinct types and indexes, too.
|
|
| Generate indexes |
| Y |
Only applicable to DB2: CREATE INDEX
statements are also generated.
|
|
| Generate defaults |
| Y |
For Sybase tables: If default values have been specified
in Predict for fields in the file, the following statements are generated:
-
create default
-
sp_binddefault
For other SQL systems: A corresponding DEFAULT
clause is generated as part of the generated CREATE TABLE
statement. If a default for non-null values has been specified in Predict
for fields in the file, this value is used in the generated
CREATE statement.
|
|
| Generate procedures |
| Only applicable to Sybase, Informix and
Ingres tables if triggers are defined and linked to the corresponding file
object in Predict via "Has TR" or to field of the
file via "Triggered by TR" and these triggers
contain references to procedures.
|
| Y |
For Sybase, Informix and Ingres tables, the statement
CREATE PROCEDURE is also generated.
|
|
| Label on |
| Y |
The string specified in the Predict field attribute
Header1 is used as label for the columns (SQL statement LABEL ON).
|
|
| Comment on |
| Y |
The first eight abstract lines of the field and file
objects in Predict are entered as comments for the columns and the table
(SQL Statement COMMENT ON).
|
|
| Defer option for indexes |
| Y |
The SQL statement CREATE INDEX is generated
with the clause DEFER YES.
|
| N |
The SQL statement CREATE INDEX is generated
without the clause DEFER YES.
|
| S |
A list containing all indexes is displayed. Enter Y for
indexes which you would like to generate with the DEFER YES
clause.
|
If you enter N or leave the field blank,
the CREATE INDEX statement is generated without this clause.
|
|
| Generate Adabas dialect |
This parameter can only be set in the Generation Defaults screen for object
type SQL Create Statement (CR).
|
Generating SQL CREATE Statements in Batch Mode
Command: GENERATE SQL-CREATE
Enter parameters on next line in positional or keyword form. File ID is obligatory, all
other parameters are optional.
| Field |
Keyword |
Position |
| File ID |
FILE-ID |
1 |
| Save as member |
MEM |
2 |
| Save in library |
LIB |
3 |
| Overwrite option |
REP-OPT |
4 |
| Op. system member |
OS-MEMBER |
5 |
| Punch / output |
PUNCH |
6 |
| Truncate creator |
TRUNC-CREATOR |
7 |
| Generate defaults |
DEFAULTS |
8 |
| Generate procedures |
PROCEDURES |
9 |
| Generate indexes |
INDEX |
10 |
| Label on |
LABEL |
11 |
| Comment on |
COMMENT |
12 |
| Defer option for indexes |
DEFER |
13 |
| If you are working with Entire
System Server
|
| - DB-ID |
NP-DBID |
14 |
| - Data set |
NP-DSNAME |
15 |
| - Volume |
NP-VOLSER |
16 |
| - Library |
NP-LIB |
17 |
| - Sublibrary |
NP-SUBLIB |
18 |
| - Member type |
NP-MEMTYPE |
19 |
| - VSAM catalog |
NP-VSAMCAT |
20 |
If a parameter is not specified, the default value is taken.
Sample Output for SQL
File ID .. DSN8230-EMPPROJACT
CREATE TABLE DSN8230.EMPPROJACT
(EMPNO CHAR(6) FOR SBCS DATA
NOT NULL,
PROJNO CHAR(6) FOR SBCS DATA
NOT NULL,
ACTNO SMALLINT NOT NULL,
EMPTIME DECIMAL(5, 2),
EMSTDATE DATE,
EMENDATE DATE,
UNIQUE ( PROJNO,
ACTNO,
EMSTDATE,
EMPNO),
FOREIGN KEY REPAE (EMPNO)
REFERENCES DSN8230.EMP ON DELETE RESTRICT,
FOREIGN KEY REPAPA (PROJNO,
ACTNO,
EMSTDATE)
REFERENCES DSN8230.PROJACT ON DELETE RESTRICT)
IN DATABASE DSN8D23A
AUDIT NONE
DATA CAPTURE NONE;
CREATE INDEX DSN8230.XEMPPROJACT2
ON DSN8230.EMPPROJACT
(EMPNO ASC)
USING STOGROUP DSN8G230
PRIQTY 12
SECQTY 12
ERASE NO
PCTFREE 10
SUBPAGES 8
BUFFERPOOL BP0
CLOSE NO;
CREATE UNIQUE INDEX DSN8230.XEMPPROJACT1
ON DSN8230.EMPPROJACT
(PROJNO ASC,
ACTNO ASC,
EMSTDATE ASC,
EMPNO ASC)
USING STOGROUP DSN8G230
PRIQTY 12
SECQTY 12
ERASE NO
PCTFREE 10
SUBPAGES 8
BUFFERPOOL BP0
CLOSE NO;
DIC1800 SUMMARY: 16 FIELD(S) PROCESSED
