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
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 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 DSN8D10.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 (DSN8D10.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 DSN8D10.EMP; 000102 --SELECT EMPNO, SALARY + COMM AS TOTAL_SAL; 000103 --FROM DSN8D10.EMP 000104 --ORDER BY TOTAL_SAL; 000110 CREATE TABLE MY_EMP LIKE DSN8D10.EMP; 000200 INSERT INTO MY_EMP 000300 SELECT * FROM DSN8D10.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 DSN8D10.EMP; 00011027 ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 ---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO MY_EMP 00020027 SELECT * FROM DSN8D10.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.
-
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.
- 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 DSN8D10.EMP 00010125 --SELECT EMPNO, SALARY + COMM AS TOTAL_SAL 00010225 --FROM DSN8D10.EMP 00010326 --ORDER BY TOTAL_SAL 00010426 -- CREATE TABLE MY_EMP LIKE DSN8D10.EMP; 00011029 -- INSERT INTO MY_EMP 00020029 -- SELECT * FROM DSN8D10.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
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