Data Modification

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:


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.


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:


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.

  • The number of columns from the SELECT statement must equal the number of columns in the INSERT column list.

  • The data type of each of the columns in the SELECT list must be compatible with the data type of those columns in the INSERT list.

  • Column names can be omitted from the INSERT list only if values are inserted into all the columns in the table.

  • Only columns defined to allow null or defined as NOT NULL WITH DEFAULT can be omitted from the INSERT list.

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.


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:

  • Searched update. This type of UPDATE statement is used to update one or more rows in a table. It requires a WHERE clause to establish the update condition: which rows are to be updated.

  • Positioned update. This kind of UPDATE statement is always embedded into a program and uses cursors to update the row where the cursor is positioned. As the cursor is repositioned using the FETCH statement, the target row for the UPDATE statement changes.

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:


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.


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.

Removing Data

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

  • Searched delete. This DELETE statement is used to delete one or multiple rows from a table. It can use a WHERE clause to establish the delete condition.

  • Positioned delete. This DELETE operation is always embedded into a program. It uses cursors to delete the row where the cursor is positioned.

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:


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:


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.

View Classification

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.

Read-Only Views

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.

  • The first FROM clause identifies more than one table or view a table function, a common or nested table expression, or an MQT.

  • The first SELECT specifies the keyword DISTINCT.

  • The outer fullselect contains a GROUP BY clause.

  • The outer fullselect contains a HAVING clause.

  • The first SELECT clause contains a column function.

  • The view contains a subquery such that the base object of the outer fullselect and of the subquery is the same table.

  • The first FROM clause identifies a read-only view.

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:


The view is read-only, as it uses the DISTINCT clause and the SQL statement involves more than one table.

Non-Read-Only Views

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:


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.

  • The view is not a read-only view.

  • The column resolves to a column of a base table.

  • The FOR READ ONLY or FOR FETCH ONLY option is not specified.

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:


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:


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.

DB2 for z. OS Version 8 DBA Certification Guide
DB2 for z/OS Version 8 DBA Certification Guide
ISBN: 0131491202
EAN: 2147483647
Year: 2003
Pages: 175
Authors: Susan Lawson © 2008-2017.
If you may any questions please contact us: