SAS/ACCESS Software for Relational Databases: Reference

The DB2UTIL Procedure

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. [cautionend]

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>;

MAPTO SAS-name-1=DB2-name-1 <...SAS-name-n=DB2-name-n>;

RESETALL|SAS-name| COLS;

SQL SQL-statement;

UPDATE;

WHERE SQL-WHERE-clause;

ERRLIMIT=error-limit;

EXIT;

Options:

DATA=SAS-data-set | <libref.>SAS-data-set: specifies the name of the SAS data set that contains the data with which you want to update the DB2 table. DATA= is required unless you specify an SQL file with the SQLIN= option.
TABLE=DB2-tablename: specifies the name of the DB2 table that you want to update. TABLE= is required unless you specify an SQL file with the SQLIN= option.
FUNCTION= D | I | U | DELETE | INSERT | UPDATE: specifies the type of modification to perform on the DB2 table by using the SAS data set as input. Refer to Modifying DB2 Data for a detailed description of this option. FUNCTION= is required unless you specify an SQL file with the SQLIN= option.

You can also specify these options with PROC DB2UTIL:

COMMIT=number

specifies the maximum number of SQL statements to execute before issuing an SQL COMMIT statement to establish a syncpoint. The default is 3.

ERROR=fileref |fileref.member

specifies an external file where error information is logged. When DB2 issues an error return code, the procedure writes all relevant information, including the SQL statement that is involved, to this external file. If you omit the ERROR= statement, the procedure writes the error information to the SAS log.

LIMIT=number

specifies the maximum number of SQL statements to issue in an execution of the procedure. The default value is 5000. If you specify LIMIT=0, no limit is set. The procedure processes the entire data set regardless of its size.

SQLIN=fileref | fileref.member

specifies an intermediate SQL output file that is created by a prior execution of PROC DB2UTIL by using the SQLOUT= option. The file that is specified by SQLIN= contains SQL statements to update a DB2 table. If you specify an SQLIN= file, then the procedure reads the SQL statements and executes them in line mode. When you specify an SQLIN= file, DATA=, TABLE=, and SQLOUT= are ignored.

SQLOUT=fileref | fileref.member

specifies an external file where the generated SQL statements are to be written. This file is either an OS/390 sequential data set or a member of an OS/390 partitioned data set. Use this option to update or delete data.

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.

SSID=subsystem-name

specifies the name of the DB2 subsystem that you want to access. If you omit DB2SSID=, the subsystem name defaults to DB2.

MAPTO Statement

MAPTO SAS-name-1=DB2-name-1<... SAS-name-n=DB2-name-n>;

The MAPTO statement maps the SAS variable name to the DB2 column name. You can specify as many values in one MAPTO statement as you want.

RESET Statement

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.

SQL Statement

SQL SQL-statement;

The SQL statement specifies an SQL statement that you want the procedure to execute dynamically. The procedure rejects SQL SELECT statements.

UPDATE Statement

UPDATE;

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.

WHERE Statement

WHERE SQL-WHERE-clause;

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;

ERRLIMIT Statement

ERRLIMIT=error-limit;

The ERRLIMIT statement specifies the number of DB2 errors that are permitted before the procedure terminates.

EXIT Statement

EXIT;

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.

Inserting Data

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 to insert.

Updating Data

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

Deleting Data

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.

DB2UTIL Example

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.

Chapter Contents
Previous
Next
Top of Page

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.

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.