So far, we have discussed basic SELECT statements. The SELECT statement allows you to retrieve data from your database tables and assumes that data has been previously loaded into the tables. Now, we concentrate on getting data into the database tables by using SQL. Three main statements can be used to add and change data stored in a DB2 database table: the INSERT, UPDATE, and DELETE SQL statements.
To perform these operations, you must have the required privileges on the tables being accessed. Usually, these privileges are more strictly enforced, as they can allow the end user to modify data records.
Inserting Data Records
To initially populate a DB2 table with data, you can use the INSERT statement to store one data record at a time. The statement can be targeted to insert data directly into a base table, or a view can be used instead. If a view is being used as the target, remember that it is the base table where the data is being stored.
Every row that is populated using the INSERT statement must adhere to table-check constraints, data type validation, dynamic (trigger) constraints, and referential integrity constraints. An SQL error will occur if any of these conditions is violated during the processing of the INSERT statement.
Remember that you must have the necessary view or table privileges to perform an INSERT statement.
The first example is a simple INSERT statement. This statement will insert the data for the DB2 Data Propagation (#508) exam into the TEST table:
INSERT INTO DB2USER1.TEST (NUMBER, NAME, TYPE, CUT_SCORE, LENGTH, TOTALTAKEN, TOTALPASSED) VALUES ('508','DB2 DATA PROPAGATION','P',NULL,90,0,0);
In this example, we specify all the column names and their corresponding values for this data record. Following the VALUES portion of the statement, we include all the data values for this record.
In the VALUES clause, the number and order of the inserted elements must match the number and order of the column names defined in the INSERT statement. However, the order of the columns doesn't have to match the order in which they are defined in the table. For those columns that don't require a value, you can indicate null or default values. In this example, we use the null value for the cut_score column.
The number of elements following the VALUES clause must match the number of names in the INSERT column list.
Depending on your column definition, the DEFAULT value can cause to be inserted a system-defined default, a user-defined default, or null. Be aware that if the column doesn't accept nulls (NOT NULL) and wasn't defined as WITH DEFAULT, you will receive an error message when using the DEFAULT value. The reason is that the default value for those columns not using the WITH DEFAULT option is the null value.
When you want to insert values into all the columns of a table, you do not have to provide the column names in the INSERT statement. This example is shown next.
INSERT INTO DB2USER1.TEST VALUES ('508','DB2 DATA PROPAGATION','P',DEFAULT,90,79,11);
This method will work only if you specify a value for all the columns in a table. If you miss one of the columns of the table, DB2 will not allow you to insert the row into the table. The DEFAULT keyword used in this example will insert the default value for the CUT_SCORE column.
Remember that, depending on the column definition, the default value could be a user-defined default value, a system-defined default value, or null.
Inserting Data into Specific Columns
Sometimes, you need to add data to specific columns. Every column that is not included in the INSERT statement will receive its default value.
This operation can be accomplished only if the omitted columns accept nulls or have a default value definition. This means that you must specify a value for the columns defined as NOT NULL. This restriction excludes columns defined as NOT NULL WITH DEFAULT.
Let's insert a row into the TEST_TAKEN table. In the following example, we insert data only for the columns CID, TCID, NUMBER, and SEAT_NO:
INSERT INTO DB2USER1.TEST_TAKEN (CID,TCID,NUMBER,SEAT_NO) VALUES('888','TR01','508','1');
Remember that columns defined using WITH DEFAULT but not listed in the INSERT statement will receive the null value or a default value.
The TEST_TAKEN table has some referential integrity with other tables. If you want to insert a record into a TEST_TAKEN table, the appropriate values should be inserted into the other three tables in advance.
Inserting a Set of Values
Using SQL, you can insert the result of a SELECT statement into a different table. The SQL statement that generates the resulting set must follow these rules.
In some situations, it might be useful to create tables that are duplicates of others so that you can do multiple calculations against them. The next example uses a table called TEST_PASSED, which is a copy of the TEST_TAKEN table. This new table will be used to extract the information about those candidates who have passed any of the DB2 Certification exams.
INSERT INTO DB2USER1.TEST_PASSED (CID,TCID,NUMBER, DATE_TAKEN,START_TIME,FINISH_TIME, PASS_FAIL,SCORE,SEAT_NO) (SELECT CID,TCID,NUMBER, DATE_TAKEN,START_TIME,FINISH_TIME, PASS_FAIL,SCORE,SEAT_NO FROM DB2USER1.TEST_TAKEN WHERE PASS_FAIL='P');
The select list used in the fullselect in the preceding example can also be substituted by a select asterisk (*). This is possible because the TEST_PASSED table has the same column structure as the TEST_TAKEN table. However, to keep this query isolated from future table modifications, you should use the select list instead of the asterisk.
You can also use a table expression to insert values into a table or view, using the INSERT clause.
Inserting Large Amounts of Data
Using the SELECT or VALUES statements to insert data into a table can be very useful. However, it is not recommended to load large amounts of data into a table using the INSERT statement, as the transaction logging overhead can become unmanageable. DB2 provides the LOAD utility, which is designed to move large amounts of data into a table.
Updating Data Records
So far, we have looked at the INSERT statement as a method of moving data into your DB2 table. You may wish to update only a column with values for a group of data records. An SQL UPDATE statement can be used to specify the column and its new values. A table or a view can be referenced as the target for the UPDATE statement.
Remember that you must have the correct privileges in order to perform the UPDATE operation.
The UPDATE statement can be used in two forms:
We focus on searched updates in this chapter. Similar to the INSERT statement, all the database constraint mechanisms are enforced during an UPDATE statement. Specific update constraint triggers and referential integrity (RI) constraints could be different from the insert constraints. For example, the following is a transaction that updates candidate ID 888's exam day for the DB2 Fundamentals certification exam:
UPDATE DB2USER1.TEST_TAKEN SET DATE_TAKEN=DATE_TAKEN + 3 DAYS WHERE CHAR(CID) ='888' AND NUMBER='500';
In this example, the labeled duration operation is used to add 3 days to the original date.
It is important that you provide the proper WHERE clause to avoid updating unintended data records. In this example, we needed to specify the predicate NUMBER='500' to avoid changing the date for any of the other tests that the candidate can be scheduled for.
DB2-labeled durations for date-time data types include years, months, days, hours, minutes, seconds, and microseconds.
The UPDATE statement can also be used with fullselects. In this case, the fullselect must return a row with exactly the same number of columns and compatible data types of the row that will be updated. Observe that this fullselect must return only one row.
Let's use a SELECT statement to update a row to set the new value. Candidate ID 888 decides to take the DB2 Fundamentals test today in the Toronto, Canada, test center.
UPDATE DB2USER1.TEST_TAKEN SET (DATE_TAKEN,TCID)= (SELECT CURRENT DATE,TCID FROM DB2USER1.TEST_CENTER WHERE SUBSTR(CITY,1,7)='TORONTO' AND COUNTRY='CANADA') WHERE CID= '888' AND NUMBER='500';
In this example, we update two columns in the same operation. These columns are indicated in the parentheses following the SET clause.
After indicating which columns are going to be updated, we use a SELECT statement to retrieve the current date (today) and the test center ID for the test center in Toronto, Canada. Note the last WHERE clause in the statement will restrict the rows that will be updated.
If you forget to update the WHERE clause in a searched update, all the data in your table will be updated.
The SQL statement that will update the DATE_TAKEN and TCID columns is known as a row fullselect. This name is given because it returns only one row. Observe that the scalar fullselect can be considered a special case of a row fullselect.
CURRENT DATE is a DB2 special register that gives the system date. Some others include CURRENT TIME, CURRENT TIMESTAMP, CURRENT PACKAGESET, and USER (the authorization ID).
Sometimes, you need to update a large number of rows of a particular table. This can be accomplished by issuing a searched update. However, this also could allocate a large amount of transactional log space. You can accomplish updates by using positioned updates, where you can easily control the commit frequency.
Many methods are available to remove data from a DB2 database. To remove all the data within a database, use the DROP DATABASE command. This may remove more data than you intended because the entire database, including its configuration, will be physically removed.
It is also possible to remove data by using the DROP TABLESPACE or DROP TABLE statements. These statements are usually issued only by the SYSADM or DBADM, as they will remove large amounts of data. If you wish to remove all of the data records from a table, it is easier and quicker to perform the DROP TABLE statement. Even easier, and less destructive, is using the LOAD utility, with the REPLACE option specifying an empty input file.
A mass delete from a segmented table space limits logging and improves performance by merely updating the space map pages to indicate the deletion rather than deleting each data row.
If the table is dropped, it must be recreated before any data can be populated again in the table.
To remove a single data record or a group of records from a table, use the DELETE statement. The syntax of the DELETE statement is different from that of the SELECT and INSERT statements because individual columns cannot be deleted; only rows can be deleted.
The DELETE statement can also be used with views. However, restrictions apply on the type of views that can be used within a DELETE statement.
Remember that you must have the necessary privileges over a table to perform the DELETE operation.
In general, two kinds of DELETE statements are
This section focuses on the searched delete.
The following SQL statement deletes candidates who don't have a telephone number loaded into the table. We use a searched delete to accomplish this task:
DELETE FROM DB2USER1.CANDIDATE WHERE hphone IS NULL AND WPHONE IS NULL;
This example uses a WHERE clause to delete the data that meets a specific criterion. To verify the result of the DELETE statement, you can issue a SELECT statement with the same WHERE clause. If the DELETE was successful, the SELECT will return an empty set.
A delete can also become more sophisticated by using subselects. The next SQL statement deletes all the candidates who took the DB2 Certification exams in February of any given year:
DELETE FROM DB2USER1.CANDIDATE WHERE cid IN (SELECT cid FROM DB2USER1.test_taken WHERE MONTH(DATE_TAKEN)=2);
In this example, we use a subselect to retrieve the CID values of the candidates who took a DB2 Certification exam in February. This list will be used to search for the candidates we want to delete.
You can delete all the rows in a table if you don't specify a search condition in your DELETE statement. You must be aware of the implications of this type of statement.
However, this is not the only way to delete all the rows in a table. You can also delete them if all the rows meet the search condition.
Deleting all the rows in a table by using a DELETE statement may not be the most efficient method. This kind of statement can consume a lot of log space when your tables are large.
Now that we have examined various SQL DML statements, let's take a closer look at views. We have already discussed creating views. Now we'll examine the various types of views. Views are classified by the operations they allow but are generally referred to as either read-only or non-read-only (updatable) views. The referential and check constraints are treated independently. They do not affect the view classification.
For example, you may not be able to insert a value into a table, because of a referential constraint. If you create a view using that table, you also can't insert that value using the view. However, if the view satisfies the rules for a non-read-only view, it will still be considered an insertable, updatable, or deleteable view because the insert restriction is located on the base table, not on the view definition.
Depending on how it is defined, a view can be read-only or can be the object of an INSERT, UPDATE, or DELETE. A view is read-only if one of the following statements is true of its definition.
A read-only view cannot be the object of an INSERT, UPDATE, or DELETE statement. A view that includes GROUP BY or HAVING cannot be referred to in a subquery of a base predicate. Following is an example of a read-only view:
CREATE VIEW READ_ONLY_VIEW (NAME,WORK_PHONE,HOME_PHONE) AS SELECT DISTINCT FNAME,WPHONE,HPHONE FROM DB2USER1.CANDIDATE C, DB2USER1.TEST_TAKEN TT WHERE C.CID=TT.CID;
The view is read-only, as it uses the DISTINCT clause and the SQL statement involves more than one table.
A view must meet the rules listed earlier to be considered a non-read-only view. Following is an example of a view that can be used for a DELETE statement:
CREATE VIEW DELETABLE_VIEW (TCID,CID,NUMBER,DATE_TAKEN,START_TIME,SEAT_NO,SCORE) AS SELECT TCID,CID,NUMBER,DATE_TAKEN, START_TIME,SEAT_NO,SCORE FROM DB2USER1.TEST_TAKEN WHERE TCID='TR01';
A view that can be the object of an UPDATE statement is a special case of a non-read-only view, as at least one of its columns must be updatable. A column of a view can be updated when all of the following rules are true.
Even though the following view definition uses constant values that cannot be updated, it is a non-read-only view, and at least you can update one of its columns. Therefore, it can be the object of an UPDATE statement:
CREATE VIEW UPDATABLE_VIEW (TCID,CID,NUMBER,CURRENT_DATE,CURRENT_TIME,SEAT_NO,SCORE) AS SELECT TCID,CID,NUMBER,CURRENT DATE, CURRENT TIME,SEAT_NO,SCORE FROM DB2USER1.TEST_TAKEN WHERE TCID='TX01';
A view can be the object of an INSERT statement when all its columns are updatable. Also, all the columns that do not have a default value must be specified in the INSERT statement. The row being inserted must contain a value for each column in the view definition. The following is a view that can be used to insert rows:
CREATE VIEW INSERTABLE_VIEW (TEST_NUMBER,TEST_NAME,TOTAL_TAKEN) AS SELECT NUMBER,NAME,TOTALTAKEN FROM DB2USER1.TEST;
This view would appear to allow inserts. However, an attempt to insert the view will fail because some columns in the base table don't accept null values. Some of these columns are not present in the view definition. When you try to insert a value using the view, DB2 will try to insert a NULL into a NOT NULL column. This action is not permitted.
Remember, the constraints defined on the base table are independent of the operations that can be performed using a view.