Re: SQL performance: creating tables using SQL DDL

[rob@xxxxxxxxx]

Reeve,

You can get the text and column heading via the following sample code:
-- MENU3PF - This is a table containing menu options customized by each 
user.
-- Note:  Option '33' may not mean the same for everybody.
-- Reserve option 90 for Sign off.

-- Compilation instuctions.
-- You "compile" this using RUNSQLSTM.

-- Modification log:
-- 07/15/05 R. Berendt, CDP,   Group Dekko Services, LLC
--    Created as a replacement for the Accounting systems MENUSPF in order 
to
--    facilitate the Single Signon project
--    Also, increase the size of the menu option from 2 to 5.
--    Kept the field names the same to facilitate CPYF conversion.
Create Table Menu3pf
   ( MNName  char(  10) Not Null
             constraint MNNameNotBlank Check(MNName<>' '),
     MNUser  char(  10) Not Null
             constraint MNUserNotBlank Check(MNUser<>' '),
     MNOpt#  Dec (   5) Not Null
             constraint MNOpt# Check(MNOpt#<>0 and MNOpt#<>90),
     MNTitl  char(  32)
             constraint MNTitlNotBlank Check(MNTitl is null or MNTitl<>' '
),
     MNDesc  char(  50) Not Null
             constraint MNDescNotBlank Check(MNDesc<>' '),
     MNPgm   char(  10)
             constraint MNPgmNotBlank Check(MNPgm is null or MNPgm<>' '),
     MNCmd   char( 154)
             constraint MNCmdNotBlank Check(MNCmd is null or MNCmd<>' '),
     MNNew   char(  10) With default Null,
   Primary Key(MNUser, MNName, MNOpt#),
   Constraint EitherPgmCmdOrMnu
     Check(( MNPgm is Null and MNCmd is Null and MNNew is not null) or
           ( MNNew is Null and MNCmd is Null and MNPgm is not null) or
           ( MNPgm is Null and MNNew is Null and MNCmd is not null)
          )
   );

Label on Table Menu3pf
  is 'Menu options for each user';

-- Add text of field descriptions
Label on Menu3pf
  ( MNName   Text Is 'Menu Name',
    MNUser   Text Is 'User Name',
    MNOpt#   Text Is 'Option#',
    MNTitl   Text Is 'Menu title',
    MNDesc   Text Is 'Description',
    MNPgm    Text Is 'Program name',
    MNCmd    Text Is 'Command name',
    MNNew    Text Is 'New name'
  );

-- Add Column Headings, 20 characters per column
Label on Menu3pf
  ( MNName   Is 'Menu                Name',
    MNUser   Is 'User                Name',
    MNOpt#   Is 'Opt                 Nbr',
    MNTitl   Is 'Menu                Title',
    MNDesc   Is '                    Description',
    MNPgm    Is 'Program             Name',
    MNCmd    Is 'Command             Name',
    MNNew    Is 'New                 Name'
  ); 


Not sure how to get edit codes, etc.

Rob Berendt