|SAS/ACCESS Software for Relational Databases: Reference|
You can use the DB2UTIL procedure to insert, update, or delete rows in a DB2 table using data from a SAS data set. You can choose one of two methods of processing: creating an SQL output file or executing directly. PROC DB2UTIL runs interactively, noninteractively, or in batch mode.
Note: The DB2UTIL
procedure is supported in order to provide compatibility with Version 5 of
the SAS/ACCESS interface to DB2. It
will not be added to other SAS/ACCESS DBMS
interfaces, nor will the enhancement of this procedure for future releases
of SAS/ACCESS be guaranteed. It is
recommended that new applications be written by using the new LIBNAME features.
The DB2UTIL procedure uses the data in an input SAS data set, along with your mapping specifications, to generate SQL statements that modify the DB2 table. The DB2UTIL procedure can perform the following:
|DELETE||deletes rows from the DB2 table according to the search condition that you specify.|
|INSERT||builds rows for the DB2 table from the SAS observations, according to the map that you specify, and inserts the rows.|
|UPDATE||sets new column values in your DB2 table by using the SAS variable values that are indicated in your map.|
When you execute the DB2UTIL procedure, you specify an input SAS data set, an output DB2 table, and how to modify the data. To generate data, you must also supply instructions for mapping the input SAS variable values to the appropriate DB2 columns.
In each execution, the procedure can generate and execute SQL statements to perform one type of modification only. However, you can also supply your own SQL statements (except the SQL SELECT statement) to perform various modifications against your DB2 tables, and the procedure will execute them.
Refer to Modifying DB2 Data for more information on the types of modifications that are available and how they are used. Refer to DB2UTIL Example for an example of using DB2UTIL.
|DB2UTIL Statements and Options|
The PROC DB2UTIL statement invokes the DB2UTIL procedure. The following statements are used with PROC DB2UTIL:
PROC DB2UTIL <options>;
You can also specify these options with PROC DB2UTIL:
When you specify the SQLOUT= option, the procedure edits your specifications, generates the SQL statements to perform the update, and writes them to the external file for later execution. When they are input to the later run for execution, the procedure passes them to DB2.
MAPTO SAS-name-1=DB2-name-1<... SAS-name-n=DB2-name-n>;
The MAPTO statement maps the SAS variable name
DB2 column name. You can specify as many values in one MAPTO statement as
RESET ALL | SAS-name | COLS;
Use the RESET statement to erase the editing that was done to SAS variables or DB2 columns. The RESET statement can perform one or more of the following actions:
|ALL||resets all previously entered map and column names to the procedure's default values.|
|SAS-name||resets the map entry for that SAS variable.|
|COLS||resets the altered column values.|
The SQL statement specifies an SQL statement that you
want the procedure to execute dynamically. The procedure rejects SQL SELECT
The UPDATE statement causes the table to be updated by using the mapping specifications that you supply. If you do not specify an input or an output mapping data set or an SQL output file, the table is updated by default.
If you have specified an output mapping data set in
the SQLOUT=option, PROC DB2UTIL creates the mapping data set and ends the
procedure. However, if you specify UPDATE, the procedure creates the mapping
data set and updates the DB2 table.
The WHERE statement specifies the SQL WHERE clause that you want to use in the update of the DB2 table. This statement is combined with the SQL statement generated from your mapping specifications. Any SAS variable names in the WHERE clause are substituted at that time. For example:
where db2col = %sasvar;
The ERRLIMIT statement specifies the number of DB2
that are permitted before the procedure terminates.
The EXIT statement exits from the procedure without further processing. NO output data is written, and no SQL statements are issued.
|Modifying DB2 Data|
The DB2UTIL procedure generates SQL statements by using data
from an input SAS data set. However, the SAS data set plays a different role
for each type of modification that is available through PROC DB2UTIL. The
following sections show how you use each type and how each type uses the SAS
data set to make a change in the DB2 table.
INSERT enables you to insert observations
from a SAS data set
into a DB2 table as rows in the table. To use the INSERT function, name the
SAS data set that contains the data you want to insert and the DB2 table to
which you want to add information in the PROC DB2UTIL statement. You can then
use the MAPTO statement to map values from SAS variables to columns in the
DB2 table. If you do not want to insert the values for all the variables in
the SAS data set into the DB2 table, map only the variables that you want
UPDATE enables you to change the values in DB2 table columns by replacing them with values from a SAS data set. You can change a column value to another value for every row in the table, or you can change column values only when certain criteria are met. For example, you can change the value of the DB2 column NUM to 10 for every row in the table. You can also change the value of the DB2 column NUM to the value in the SAS variable NUMBER, providing that the value of the DB2 column NAME and the SAS data set variable NAME match.
You specify the name of the SAS data set and the DB2 table to be updated when you execute PROC DB2UTIL. You can specify that only certain variables be updated by naming only those variables in your mapping specifications.
You can use the WHERE clause to specify that only the
rows on the DB2 table that meet certain criteria are updated. For example,
you can use the WHERE clause to specify that only the rows with a certain
range of values be updated, or you can specify that rows will be updated when
a certain column value in the row matches a certain SAS variable value in
the SAS data set. In this case, you could have a SAS data set with several
observations in it. For each observation in the data set, the DB2UTIL procedure
updates the values for all rows in the DB2 table that have a matching value.
Then the procedure goes on to the next observation in the SAS data set and
continues to update values in DB2 columns in rows that meet the comparison
DELETE enables you to remove rows from a DB2 table when a certain condition is met. You can delete rows from the table when a DB2 column value in the table matches a SAS variable value in the SAS data set. Name the DB2 table from which you want to delete rows and the SAS data set that contains the target deletion values in the PROC DB2UTIL statement. Then use the WHERE statement to specify the DB2 column name and the SAS variable whose values must match before the deletion is performed.
If you want to delete values that are based on criteria other than values in SAS data variables (for example, deleting every row with a department number of 600), then you can use an SQL DELETE statement in an SQL statement.
The following example uses DB2UTIL's UPDATE function to update a list of telephone extensions from a SAS data set. The master list of extensions is in the DB2 table TESTID.EMPLOYEES and will be updated from SAS data set TRANS. First you must create the SAS data set:
options db2dbug; data trans; empno=321783;ext='3999'; output; empno=320001;ext='4321'; output; empno=212916;ext='1300'; output; run;
Next, specify the data set in PROC DB2UTIL.
proc db2util data=trans table=testid.employees function=u; mapto ext=phone; where empid=%empno; update; run;
The row that includes EMPID=320001 was not found in the TESTID.EMPLOYEES table and therefore was not updated. The warning that appears in the SAS log can be ignored.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.