Data Set Options for Relational Databases

BL_CONTROL= Data Set Option

Identifies the file that contains control statements.

Alias:	FE_EXECNAME [Teradata]
Default value:	DBMS-specific
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Oracle, Teradata

Syntax
Syntax Description
Details
Examples
See Also

Syntax

BL_CONTROL=path-and-control-filename [Oracle]

BL_CONTROL=path-and-data-filename [Teradata]

Syntax Description

path-and-control-filename [Oracle]: specifies the SQL*Loader file to which SQLLDR control statements are written that describe the data to include in bulk load.
path-and-data-filename [Teradata]: specifies the name of the control file to generate for extracting data with SAS/ACCESS Interface to Teradata using FastExport multithreaded read.

On most platforms, the default filename is BL_<table>_<unique-ID>.ctl [Oracle, Teradata]:

table	specifies the table name
unique-ID	specifies a number that is used to prevent collisions in the event of two or more simultaneous bulk loads of a particular table. The SAS/ACCESS engine generates the number.

Details

To specify this option, you must first set BULKLOAD=YES.

Oracle: The Oracle interface creates the control file by using information from the input data and SAS/ACCESS options. The file contains Data Definition Language (DDL) definitions that specify the location of the data and how the data corresponds to the database table. It is used to specify exactly how the loader should interpret the data that you are loading from the DATA file (.DAT file). By default it creates a control file in the current directory or with the default file specifications. If you do not specify this option and a control file does not already exist, a file is created in the current directory or with the default file specifications. If you do not specify this option and a control file already exists, the Oracle interface reuses the file and replaces the contents with the new control statements.

Teradata: To specify this option, you must first set DBSLICEPARM=ALL as a LIBNAME or data set option for threaded reads. By default SAS creates a data file in the current directory or with a platform-specific name. If you do not specify this option and a control file does not exist, SAS creates a script file in the current directory or with the default file specifications. If you do not specify this option and a control file already exists, the DATA step. SAS/ACCESS Interface to Teradata creates the control file by using information from the input data and SAS/ACCESS options. The file contains FastExport Language definitions that specify the location of the data and how the data corresponds to the database table. It is used to specify exactly how the FastExport should interpret the data that you are loading from the DATA (.DAT) file. Because the script file that SAS generates for FastExport must contain login information in clear text, it is recommended that you secure the script file by specifying a directory path that is protected.

Examples

This example generates a Teradata script file, C:\protdir\fe.ctl on Windows.

DATA test;
SET teralib.mydata(DBSLICEPARM=ALL BL_CONTROL="C:\protdir\fe.ctl");
run;

This example generates a Teradata script file, /tmp/fe.ctl, on UNIX.

DATA test;
SET teralib.mydata(DBSLICEPARM=ALL BL_CONTROL="/tmp/fe.ctl");
run;

This example generates a script file, USERID.SECURE.SCR.CTL, by appending CTL and prepending the user ID.

DATA test;
SET teralib.mydata(DBSLICEPARM=ALL BL_CONTROL="SECURE.SCR");
run;