Generating for SQL

#### Search Results

Search

# Generating for SQL

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

- Parameters for SQL

- Generating SQL CREATE Statements in Batch Mode

- Sample Output for SQL

## 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

### On this page

- Calling the Function: Generate SQL Create Statements
- Parameters for SQL
- Generating SQL CREATE Statements in Batch Mode
- Sample Output for SQL