DBCONTERM= LIBNAME Option

Specifies a user-defined termination command to execute before every disconnect from the DBMS that is within the scope of the LIBNAME statement or libref.

Valid in:	SAS/ACCESS LIBNAME statement
Default:	none
Data source:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata
See:	DBCONINIT= LIBNAME option

Syntax

Details

Examples

Example 1: Drop a Table Before Disconnecting

Example 2: Execute a Stored Procedure at Each DBMS Connection

Syntax

DBCONTERM=<'>DBMS-user-command<'>

Syntax Description

DBMS-user-command: any valid command that the SAS/ACCESS engine can execute and that does not return a result set or output parameters.

Details

The termination command that you select can be a stored procedure or any DBMS SQL statement that might provide additional control over the interaction between the SAS/ACCESS engine and the DBMS. The command executes immediately before SAS terminates each connection to the DBMS. If the command fails, SAS provides a warning message, but unassigning the library and disconnecting from the DBMS still occur. You must specify the command as a single quoted string.

Note: The termination command might execute more than once because one LIBNAME statement might have multiple connections (for example, one for reading and one for updating).

Examples

Example 1: Drop a Table Before Disconnecting

In this example, the DBMS drops the Q1_SALES table before SAS disconnects from the DBMS.

libname mydblib db2 user=testuser using=testpass
        datasrc=invoice dbconterm='drop table q1_sales';

Example 2: Execute a Stored Procedure at Each DBMS Connection

In this example, the stored procedure, SALESTAB_STORED_PROC, is executed each time SAS connects to the DBMS, and the BONUSES table is dropped when SAS terminates each connection.

libname mydblib db2 user=testuser
        using=testpass datasrc=sales
        dbconinit='exec salestab_stored_proc'
        dbconterm='drop table bonuses';