Creating tables

When you create a table in Db2 Admin Tool, you define all of the table attributes and columns. To save time, you can specify that you want to use another table as a model.

1. On the DB2 Administration Menu (ADB2) panel, specify option 2, and press Enter.
2. On the Execute SQL Statements (ADB22) panel, specify option 4, and press Enter:

    ADB22 min -------------------- Execute SQL Statements ------------------- 01:36
    Option ===> 4                                                                   
                                                                                   
       1 - Edit/run SQL statements                           DB2 System: DD1A      
       2 - Run or Explain SQL statements                     DB2 SQL ID: ADM001     
       3 - Build SQL SELECT, INSERT, UPDATE or DELETE prototype                    
       4 - Create/drop/label/comment on objects                                    
       5 - Grant/revoke privileges on objects

3. On the Create/Drop/Label/Comment On Objects (ADB26) panel, specify option CT, and press Enter.
4. On the Create Table (ADB26CT) panel, specify values for the following required parameters and any optional parameters, and press Enter:

   Required parameters

   Schema

   Specify the schema for the new table or use the default schema.

   Name

   Specify a table name for the new table. Enter a question mark (?) to look up existing table names on the Tables, Views, and Aliases (ADB21T) panel.

   Number of columns

   Specify the number of columns for the new table.

   Optional parameters

   LIKE

   Specify these parameters if you want to create your new table like an existing table.

   Schema

   Specify the schema for the existing table.

   Name

   Specify the name of the existing table.

   Identity attrs

   Specify whether to include identity column attributes in the new table.

   Row chg attrs

   Specify whether to include row change timestamp column attributes in the new table.

   As model only

   Specify whether to use the existing table as a model and edit its columns values.

   ADB26CT n ----------------------DD1A Create Table ---------------------- 16:31
   Command ===>                                                                   
                                                                      
    CREATE TABLE                                                      
                                                                      
    Schema  . . . . .           >           (default is )             
    Name  . . . . . . NEW                    > (? to look up)         
                                                                      
    LIKE                                                              
    Schema  . . . . .           >                                     
    Name  . . . . . .                     > (? to look up)            
    Identity attrs  .                   (Yes/No)                      
    Row chg attrs . .                   (Yes/No)                      
    As model only . .                   (Yes/No)                      
                                                                      
    (                                                                 
    Number of columns . . . 6                                           
                                                                      
                                                                            

5. On the Create Table Columns (ADB26CTF) panel, specify columns and column attributes for the new table:
   

   ADB26CTF  ------------------ DD1A Create Table Columns ------- Row 1 to 3 of 3 
   Command ===>                                                  Scroll ===> CSR  
                                                                                  
   Schema  . .          >            Database  . . .                              
   Name  . . . NEWTABLE           >  Table space . .
                                     Accelerator . .                              
                                                                                  
   Commands :  CREATE  PRIMKEY  TBLOPTS  LONGNAMES  PART  HASH                               
   Line commands:   M - Move  A - After  B - Before                               
    Inn - Insert  U - Update  D - Delete  Rnn - Repeat                            
    UM - Update XML modifiers                                                     
                                                                   Operation      
   Select Column Name        Col Type  Length Scale  Null D Col No Type           
          *                  *              *      * *    *      * *              
   ------ -----------------> --------- ------ ------ ---- - ------ ---------      
                                                                                  
   *      T1                 TIMESTMP      13     11 N    N      1 UPDATE         
   *      T2                 TIMESTZ       15     11 N    N      2 UPDATE         
   *      T3                 TIMESTZ       12      6 N    N      3 UPDATE         
   *      T4                 DATE           4      0 N    N      4 UPDATE         
   *      T5                 INTEGER        4      0 N    N      5 UPDATE         
   *      T6                 DATE           4      0 N    N      6 UPDATE         
   ******************************* END OF DB2 DATA *******************************  
   

   
6. Optional: If you want to specify additional table attributes, issue the TBLOPTS command and press Enter. Then, on the Create Table Options (ADB26TOP) panel, specify the appropriate values, and press Enter:
   

   ADB26TOP  --------------------- DD1A Create Table Options ---------------------
   Command ===>                                                                   
                                                                                  
   Enter values and press Enter.                                                  
                                                                                  
   EDITPROC  . . . . . . . .                                                      
     WITH ROW ATTRIBUTES . .           (Yes/No)                                   
   VALIDPROC . . . . . . . .                                                      
   AUDIT . . . . . . . . . .           (None, Changes, All)                       
   DATA CAPTURE  . . . . . .           (None, Changes)                            
   CCSID . . . . . . . . . .           (EBCDIC, UNICODE, ASCII)                   
   RESTRICT ON DROP  . . . .           (Yes/No)                                   
   VOLATILE  . . . . . . . .           (Yes/No)                                   
   APPEND  . . . . . . . . . YES       (Yes/No)                                   
   PBG size  . . . . . . . .           (in GB)                                    
   PBR . . . . . . . . . . .           (Yes/No)                                   
   PAGENUM . . . . . . . . .           (Relative/Absolute)                        
   DSSIZE  . . . . . . . . .           (in GB)                                   
   BUFFERPOOL  . . . . . . .           (BP0-BP49, BP8K0-9, BP16K0-9, BP32K-BP32K
   COMPRESS  . . . . . . . .           (Y-Yes, N-No, F-Fixedlength, H-Huffman)  
   LOGGED  . . . . . . . . .           (Yes/No)                                 
   MEMBER CLUSTER  . . . . .           (Yes/No)                                 
   TRACKMOD  . . . . . . . .           (Yes/No) 
   System period . . . . . .           (Yes/No)                                   
   Business period . . . . .           (Yes/No) 
   KEY LABEL . . . . . . . .                           > (optional: Key label, NO)

   
7. On the Create Table Columns (ADB26CTF) panel, issue the CREATE command, and press Enter.
8. If the Statement Execution Prompt (ADB2PSTM) panel is displayed, follow the instructions on that panel to complete and run the SQL statement.