Lesson 1.2: Creating a table

You can create tables and other database objects interactively by issuing SQL statements from SPUFI. In this lesson, you will create a new table that has the same format and contains the same data as the sample EMP table that you queried in the previous lesson. In the next lesson, you can modify data in the new table that you created, without making any changes to the original sample data.

Procedure

1. Open DB2I, set the subsystem ID, and open the USER.SRCLIB.TUTORIAL(SQLTEXT) data set in SPUFI as described in steps 1–5 of Lesson 1.1: Querying data interactively.
2. If your data set contains statements from the previous lesson, comment them out by adding two hyphens in front of them.
3. Create a table and add rows to it by issuing SQL statements.
   1. In the Edit panel, type a CREATE TABLE statement and an INSERT statement. For more information about the syntax and options of these SQL statements, see CREATE TABLE statement and INSERT statement.
      For example, the following CREATE statement creates a table named MY_EMP under your authorization ID, which has the same format and contains the same data as the sample DSN8C10.EMP table. The following INSERT statement adds rows to the MY_EMP table. For more information about the data in the EMP table in the sample database, see Employee table (DSN8C10.EMP).

         File  Edit  Edit_Settings  Menu  Utilities  Compilers  Test  Help            
       
       EDIT       USER.SRCLIB.TUTORIAL(SQLTEXT) - 01.25           Columns 00001 00072 
       Command ===>                                                  Scroll ===> PAGE 
       ****** ***************************** Top of Data ******************************
       000100                                                                         
       000101 --SELECT * FROM DSN8C10.EMP;                                             
       000102 --SELECT EMPNO, SALARY + COMM AS TOTAL_SAL;                              
       000103   --FROM DSN8C10.EMP                                                    
       000104   --ORDER BY TOTAL_SAL;                                                  
       000110    CREATE TABLE MY_EMP LIKE DSN8C10.EMP;                                
       000200    INSERT INTO MY_EMP                                                   
       000300       SELECT * FROM DSN8C10.EMP;                                        
       000400                                                                         
       000500                                                                         
       000600                                                                         
       ****** **************************** Bottom of Data ****************************
                                                                                      
        F1=Help      F2=Split     F3=Exit      F5=Rfind     F6=Rchange   F7=Up        
        F8=Down      F9=Swap     F10=Left     F11=Right    F12=Cancel                 

   2. To issue the statements, press PF3, and then press Enter.
      The table is created, the rows are inserted, and the following result is displayed.

         Menu  Utilities  Compilers  Help                                             
       
       BROWSE    TUTOR01.MYOUTPUT                           Line 00000000 Col 001 080 
       Command ===>                                                  Scroll ===> PAGE 
      ********************************* Top of Data **********************************
      ---------+---------+---------+---------+---------+---------+---------+---------+
                                                                              00010023
      --SELECT * FROM DSN8C10.EMP                                             00010125
      --SELECT EMPNO, SALARY + COMM AS TOTAL_SAL                              00010225
      --FROM DSN8C10.EMP                                                      00010326
      --ORDER BY TOTAL_SAL                                                    00010426
         CREATE TABLE MY_EMP LIKE DSN8C10.EMP;                                00011027
      ---------+---------+---------+---------+---------+---------+---------+---------+
      DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                       
      ---------+---------+---------+---------+---------+---------+---------+---------+
         INSERT INTO MY_EMP                                                   00020027
            SELECT * FROM DSN8C10.EMP;                                        00030027
      ---------+---------+---------+---------+---------+---------+---------+---------+
      DSNE615I NUMBER OF ROWS AFFECTED IS 42                                          
      DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                       
      ---------+---------+---------+---------+---------+---------+---------+---------+
        F1=Help    F2=Split   F3=Exit    F5=Rfind   F7=Up      F8=Down    F9=Swap     
       F10=Left   F11=Right  F12=Cancel                                               

      Remember: In lesson 1.1, you learned that you can use any messages and codes that are issued to troubleshoot problems with your statements.
4. Press PF3 to return to SPUFI.
   For a reminder of the pattern that you follow to issue successive SQL statements from SPUFI, see the tip in Step 9 of the previous lesson.
5. Issue a SELECT statement to verify that the table was created and contains the data that you inserted.
   For example, the following query returns all rows from the table that you created:

      Menu  Utilities  Compilers  Help                                             
    
    BROWSE    TUTOR01.MYOUTPUT                           Line 00000000 Col 001 080 
    Command ===>                                                  Scroll ===> PAGE 
   ********************************* Top of Data **********************************
   ---------+---------+---------+---------+---------+---------+---------+---------+
                                                                           00010023
   --SELECT * FROM DSN8C10.EMP                                             00010125
   --SELECT EMPNO, SALARY + COMM AS TOTAL_SAL                              00010225
   --FROM DSN8C10.EMP                                                      00010326
   --ORDER BY TOTAL_SAL                                                    00010426
   -- CREATE TABLE MY_EMP LIKE DSN8C10.EMP;                                00011029
   -- INSERT INTO MY_EMP                                                   00020029
   --    SELECT * FROM DSN8C10.EMP;                                        00030029
                                                                           00040026
      SELECT * FROM MY_EMP;                                                00050028
   ---------+---------+---------+---------+---------+---------+---------+---------+
   EMPNO   FIRSTNME      MIDINIT  LASTNAME         WORKDEPT  PHONENO  HIREDATE    J
   ---------+---------+---------+---------+---------+---------+---------+---------+
   000010  CHRISTINE     I        HAAS             A00       3978     1965-01-01  P
   000020  MICHAEL       L        THOMPSON         B01       3476     1973-10-10  M
     F1=Help    F2=Split   F3=Exit    F5=Rfind   F7=Up      F8=Down    F9=Swap     
    F10=Left   F11=Right  F12=Cancel