DB2 10 - Introduction - Lesson 1.2: Creating a table

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.

Before you begin

Before you begin

Complete Lesson 1.1: Querying data interactively. Lesson 1.2 builds upon what you learned in the previous lesson.

Procedure

Procedure

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.
If your data set contains statements from the previous lesson, comment them out by adding two hyphens in front of them.

Create a table and add rows to it by issuing SQL statements.

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 and INSERT. 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 DSN8A10.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 (DSN8A10.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 DSN8A10.EMP;                                             
 000102 --SELECT EMPNO, SALARY + COMM AS TOTAL_SAL;                              
 000103   --FROM DSN8A10.EMP                                                    
 000104   --ORDER BY TOTAL_SAL;                                                  
 000110    CREATE TABLE MY_EMP LIKE DSN8A10.EMP;                                
 000200    INSERT INTO MY_EMP                                                   
 000300       SELECT * FROM DSN8A10.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

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 DSN8A10.EMP;                                00011027
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                       
---------+---------+---------+---------+---------+---------+---------+---------+
   INSERT INTO MY_EMP                                                   00020027
      SELECT * FROM DSN8A10.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.

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.

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 DSN8A10.EMP                                             00010125
--SELECT EMPNO, SALARY + COMM AS TOTAL_SAL                              00010225
--FROM DSN8A10.EMP                                                      00010326
--ORDER BY TOTAL_SAL                                                    00010426
-- CREATE TABLE MY_EMP LIKE DSN8A10.EMP;                                00011029
-- INSERT INTO MY_EMP                                                   00020029
--    SELECT * FROM DSN8A10.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

What to do next

What to do next

Press PF3 and then press Enter to return to the Edit panel.

You will modify data in the MY_EMP table in the next lesson.

Lesson checkpoint

In this lesson, you learned how to complete the following tasks interactively in SPUFI:

Create a table
Insert rows from one table into another
Verify that the new table was created and contains data

Related concepts:

Creation of tables