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

• Parameters

• Generating SQL CREATE Statements in Batch Mode

• Sample Output

Calling the Function

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

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.

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.

If a parameter is not specified, the default value is taken.

Sample Output

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