Lesson 1.3: Modifying data interactively
Just as you can query data in Db2 tables and to create database objects by issuing SQL statements in SPUFI, you can also insert, update, and delete data in Db2 tables. In this lesson, you will add a row for a new employee to the MY_EMP table that you created in the previous lesson, modify a value in the row, and finally delete the new row. Along the way, you will learn how to verify the result of each of the actions, all by using SPUFI.
Before you begin
You created the MY_EMP table under your authorization ID, as described in Lesson 1.2: Creating a table.
About this task
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.
-
Delete the SQL statements that you entered in the previous lessons.
You can delete rows from the data set by issuing the Dn line command, where n is the number of lines to delete.For example, the following command deletes nine rows from the data set:
File Edit Edit_Settings Menu Utilities Compilers Test Help EDIT USER.SRCLIB.TUTORIAL(SQLTEXT) - 01.29 Columns 00001 00072 Command ===> Scroll ===> PAGE ****** ***************************** Top of Data ****************************** 000100 00d901 --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 -- SELECT * FROM MY_EMP; 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 -
Suppose that your company is preparing to hire a new employee. You must add a row of
data to the MY_EMP table for the new employee.
In the following example INSERT statement, each item in the first set of parentheses is a column name in the MY_EMP table. Each value in the second set of parentheses is the value that will be added to the corresponding column. For example, 300010 will be added to the EMPNO column, VICTORIA will be added to the FIRSTNME column, and so on. For more information about the syntax and options of an INSERT statement, see INSERT statement.
Remember that you created the MY_EMP table in the previous lesson, based on the format of the sample EMP table. For detailed information about the format and contents of both tables, see Employee table (DSN8C10.EMP).
File Edit Edit_Settings Menu Utilities Compilers Test Help EDIT USER.SRCLIB.TUTORIAL(SQLTEXT) - 01.30 Columns 00001 00072 Command ===> Scroll ===> PAGE ****** ***************************** Top of Data ****************************** 000100 INSERT INTO MY_EMP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, 000600 WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, 000610 SEX, BIRTHDATE, SALARY, BONUS, COMM) 000620 VALUES ('300010', 'VICTORIA', 'M', 'OFALLON', 'D11', '5240', 000630 '2014-04-21', 'DESIGNER', 16, 'F', '1996-07-03', 30000.00, 000640 300.00, 0.00); 000700 ****** **************************** 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 -
Press PF3 and then press Enter to issue the statement.
The results are displayed in a BROWSE panel:
Menu Utilities Compilers Help BROWSE TUTOR01.MYOUTPUT Line 00000000 Col 001 080 Command ===> Scroll ===> PAGE ********************************* Top of Data ********************************** ---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO MY_EMP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, 00010030 WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, 00060030 SEX, BIRTHDATE, SALARY, BONUS, COMM) 00061030 VALUES ('300010', 'VICTORIA', 'M', 'OFALLON', 'D11', '5240', 00062030 '2014-04-21', 'DESIGNER', 16, 'F', '1996-07-03', 30000.00, 00063030 300.00, 0.00) 00064030 00070023 00080023 ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE615I NUMBER OF ROWS AFFECTED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE617I COMMIT PERFORMED, SQLCODE IS 0 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=CancelRemember: In lesson 1.1, you learned that you can use any messages and codes that are issued to troubleshoot problems with your statements. -
Verify that the row was inserted by issuing a SELECT statement to query the MY_EMP
table.
The SELECT statement in the following example includes a WHERE clause that returns only the new row that you added.
File Edit Edit_Settings Menu Utilities Compilers Test Help EDIT USER.SRCLIB.TUTORIAL(SQLTEXT) - 01.30 Columns 00001 00072 Command ===> Scroll ===> PAGE ****** ***************************** Top of Data ****************************** 000100 --INSERT INTO MY_EMP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, 000600 -- WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, 000610 -- SEX, BIRTHDATE, SALARY, BONUS, COMM) 000620 --VALUES ('300010', 'VICTORIA', 'M', 'OFALLON', 'D11', '5240', 000630 -- '2014-04-21', 'DESIGNER', 16, 'F', '1996-07-03', 30000.00, 000640 -- 300.00, 0.00); 000700 000800 SELECT * FROM MY_EMP WHERE EMPNO='300010'; ****** **************************** Bottom of Data **************************** F1=Help F2=Split F3=Exit F5=Rfind F6=Rchange F7=Up F8=Down F9=Swap F10=Left F11=Right F12=CancelThe following result is returned:---------+---------+---------+---------+---------+---------+---------+---------+ EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE J ---------+---------+---------+---------+---------+---------+---------+---------+ 300010 VICTORIA M OFALLON D11 5240 2014-04-21 D DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+---------+ -
Suppose that the new employee has excellent negotiation skills, so that the company has
decided to increase her salary offer by ten percent. Issue an UPDATE statement to increase
the salary of the employee that you added.
For more information about the syntax and options of an UPDATE statement, see UPDATE statement.
File Edit Edit_Settings Menu Utilities Compilers Test Help EDIT USER.SRCLIB.TUTORIAL(SQLTEXT) - 01.32 Columns 00001 00072 Command ===> Scroll ===> PAGE ****** ***************************** Top of Data ****************************** 000100 --INSERT INTO MY_EMP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, 000600 -- WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, 000610 -- SEX, BIRTHDATE, SALARY, BONUS, COMM) 000620 --VALUES ('300010', 'VICTORIA', 'M', 'OFALLON', 'D11', '5240', 000630 -- '2014-04-21', 'DESIGNER', 16, 'F', '1996-07-03', 30000.00, 000640 -- 300.00, 0.00); 000700 -- SELECT * FROM MY_EMP WHERE EMPNO='300010'; '''''' 000710 UPDATE MY_EMP 000720 SET SALARY = SALARY + 3000 000730 WHERE EMPNO = '300010'; 000800 ****** **************************** Bottom of Data **************************** F1=Help F2=Split F3=Exit F5=Rfind F6=Rchange F7=Up F8=Down F9=Swap F10=Left F11=Right F12=CancelThe following messages indicate that the UPDATE statement was successful:---------+---------+---------+---------+---------+---------+---------+---------+ DSNE615I NUMBER OF ROWS AFFECTED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 ---------+---------+---------+---------+---------+---------+---------+---------+ -
Verify that the change was applied by issuing a SELECT statement.
For example, you might issue a statement that returns the following result:
Menu Utilities Compilers Help BROWSE TUTOR01.MYOUTPUT Line 00000000 Col 001 080 Command ===> Scroll ===> PAGE ********************************* Top of Data ********************************** ---------+---------+---------+---------+---------+---------+---------+---------+ --INSERT INTO MY_EMP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, 00010031 -- WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, 00060031 -- SEX, BIRTHDATE, SALARY, BONUS, COMM) 00061031 --VALUES ('300010', 'VICTORIA', 'M', 'OFALLON', 'D11', '5240', 00062031 -- '2014-04-21', 'DESIGNER', 16, 'F', '1996-07-03', 30000.00, 00063031 -- 300.00, 0.00); 00064031 SELECT EMPNO, LASTNAME, FIRSTNME, SALARY FROM MY_EMP 00070034 WHERE EMPNO='300010'; 00070134 ---------+---------+---------+---------+---------+---------+---------+---------+ EMPNO LASTNAME FIRSTNME SALARY ---------+---------+---------+---------+---------+---------+---------+---------+ 300010 OFALLON VICTORIA 31000.00 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+---------+ F1=Help F2=Split F3=Exit F5=Rfind F7=Up F8=Down F9=Swap F10=Left F11=Right F12=Cancel -
Despite the increased salary offer, the new employee decided to take a job offer
elsewhere. Issue a DELETE statement to remove the row that you added for the new
employee.
The following example shows the DELETE statement to remove the row and the result of issuing the DELETE statement. For more information about the syntax and options of a DELETE statement, see DELETE statement.
Menu Utilities Compilers Help BROWSE TUTOR01.MYOUTPUT Line 00000000 Col 001 080 Command ===> Scroll ===> PAGE ********************************* Top of Data ********************************** ---------+---------+---------+---------+---------+---------+---------+---------+ DELETE FROM MY_EMP 00010012 WHERE EMPNO = '300010'; 00011012 00020012 ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE615I NUMBER OF ROWS AFFECTED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE617I COMMIT PERFORMED, SQLCODE IS 0 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72 DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1 DSNE621I NUMBER OF INPUT RECORDS READ IS 3 DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 15 ******************************** Bottom of Data ******************************** F1=Help F2=Split F3=Exit F5=Rfind F7=Up F8=Down F9=Swap F10=Left F11=Right F12=Cancel -
Verify that the row was deleted by issuing a SELECT statement.
For example, you might issue a SELECT statement that returns that following result:
Menu Utilities Compilers Help BROWSE TUTOR01.MYOUTPUT Line 00000000 Col 001 080 Command ===> Scroll ===> PAGE ********************************* Top of Data ********************************** ---------+---------+---------+---------+---------+---------+---------+---------+ SELECT * FROM MY_EMP 00010013 WHERE EMPNO = '300010'; 00011012 00020012 ---------+---------+---------+---------+---------+---------+---------+---------+ EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE J ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE610I NUMBER OF ROWS DISPLAYED IS 0 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE617I COMMIT PERFORMED, SQLCODE IS 0 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72 DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1 DSNE621I NUMBER OF INPUT RECORDS READ IS 3 DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 17 F1=Help F2=Split F3=Exit F5=Rfind F7=Up F8=Down F9=Swap F10=Left F11=Right F12=CancelThe DSNE610I message indicates that 0 rows were returned, which means that employee number 300010 is no longer included in the MY_EMP table.
Lesson checkpoint
In this lesson, you learned how to complete the following tasks interactively in SPUFI:
- Insert a row of data
- Update a row of data
- Delete a row of data
- Verify the results of the actions