Basic SQL Coding for DB2 Universal Database for OS/390

This chapter is from the book

DB2® Universal Database for OS/390 V7.1 Application Certification Guide

Learn More Buy

This chapter is from the book

This chapter is from the book 

DB2® Universal Database for OS/390 V7.1 Application Certification Guide

Learn More Buy

Data Modification

Up to now, 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 will concentrate on manipulating data in tables using SQL. There are three main statements that can be used to add and change data stored in a DB2 database table. They are the INSERT, DELETE, and UPDATE SQL statements.

To perform these operations, you must have the required privileges on the tables being accessed. Usually, these privileges are more strictly enforced, since they can allow the end user to modify data records.

Inserting Data Records

To initially populate a DB2 table with data, the INSERT statement can be used 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 actual 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 are violated during the processing of the 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 db2cert.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 of 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.

Depending on your column definition, the DEFAULT value can cause the insertion of 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. This error is because 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 db2cert.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.

Inserting Data into Specific Columns

There are times when 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 will insert data only for the columns CID, TCID, NUMBER, and SEAT_NO.

INSERT INTO db2cert.test_taken
(CID,TCID,NUMBER,SEAT_NO)
VALUES('888','TR01','508','1')

Remember that columns defined using WITH DEFAULT that are not listed in the INSERT statement will receive the null value or a default value.

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.

INSERT INTO db2cert.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 db2cert.test_taken
WHERE pass_fail='P')

The select list used in the fullselect in the above 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, it is recommended that you use the select list instead of the asterisk.

When you use a SELECT statement to insert data into a table, you must enclose it between parentheses. 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 you with 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. There is an SQL UPDATE statement that 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.

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. It 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 of the database constraint mechanisms are enforced during an UPDATE statement. There can be specific update constraint triggers and referential integrity (RI) constraints that 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 db2cert.test_taken
SET date_taken=date_taken + 3 days
WHERE char(cid) ='888'
AND number='500'

In this example, we use an operation known as a labeled duration to add three days to the original date.

It is very 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.

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 update a row using a SELECT statement to set the new value. Candidate ID 888 decides to take the DB2 Fundamentals test today in the test center located in Toronto, Canada.

UPDATE db2cert.test_taken
SET (date_taken,tcid)=
(SELECT current date,tcid FROM db2cert.test_center
WHERE substr(city,1,7)='Toronto'
AND country='Canada')
WHERE cid= '888' AND number='500';

In the above example, we update two different 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 located in Toronto, Canada. Notice the last WHERE clause in the statement will restrict the rows that 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.

Updating Large Amounts of Data

There are times when 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 using positioned updates, where you can easily control the commit frequency.

Removing Data

There are many methods available to remove data from a DB2 database. To remove all of the data within a database, perform 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 using the DROP TABLESPACE or DROP TABLE statements. These statements are usually only issued by the SYSADM or DBADM, since 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.

If the table is dropped, it must be re-created before any data can be populated again in the table.

To remove a single data record or a group or records from a table, the DELETE statement should be used. The syntax of the DELETE statement is different from 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, there are restrictions on the type of views that can be used within a DELETE statement.

In general, there are two kinds of DELETE statements:

• 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.

In this section we focus 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 db2cert.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 db2cert.candidate
WHERE cid IN (SELECT cid FROM db2cert.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 the month of February. This list will be used to search for the candidates we want to delete.

Deleting All the Rows in a Table

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 all the rows in a table 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 different types of views. Views are classified by the operations they allow, but are generally referred to as either read-only or non-read-only (updateable) 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, updateable, or deleteable view. This is because the insert restriction is located on the base table, not on the view definition.

Read-only Views

Depending on how a view 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, or identifies a table function.

• 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.

• It 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. The 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 db2cert.candidate c, db2cert.test_taken tt
WHERE c.cid=tt.cid

The view above is a read-only view 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 above to be considered a non-read-only view. Below 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 db2cert.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 updateable. 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.

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 db2cert.test_taken
WHERE tcid='TX01'

A view can be the object of an INSERT statement when all of its columns are updateable. Also, all of 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 db2cert.test

The view shown above would appear to allow inserts. However, an attempt to insert the view will fail. This is because there are columns in the base table that 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.