BL_CONTROL= Data Set Option

Identifies the file that contains control statements.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Alias:	FE_EXECNAME [Teradata]
Default:	DBMS-specific
Requirement:	To specify this option, you must first set BULKLOAD=YES.
Data source:	Oracle, Teradata
See:	BL_DATAFILE= data set option, BL_DELETE_DATAFILE= data set option, BL_DELETE_ONLY_DATAFILE= data set option, BULKLOAD= data set option, DBSLICEPARM= LIBNAME option, DBSLICEPARM= data set option

Syntax

Details

Example

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 (where 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.

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

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

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.

Example: Generate Teradata Script Files

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;