Specifying Options for Tables :: SAS(R) Data Integration Studio 4.2: User's Guide

Problem

You want to set options for tables that are used in SAS Data Integration Studio jobs, such as DBMS name options; library, name, and schema options; and compression scheme and password protection options.

Solution

You can set global options for tables on the General tab of the Options menu. The Options menu is available on the Tools menu on the SAS Data Integration Studio menu bar. You can set local options on the tabs that are available on the properties window for each table.

Tasks

Set Global Options for Tables

Global Table Options

Option Name	Description
Enable case-sensitive DBMS object names	Specifies whether SAS Data Integration Studio generates code when registering and using the table in jobs that supports case-sensitive table and column names by default. If you do not select the check box, no case-sensitive support is provided. If you select the check box, support is provided.
Enable special characters within DBMS object names	Specifies whether SAS Data Integration Studio generates code when registering and using the table in jobs that supports special characters in table and names by default. If you select the check box, support is provided by default. When you select this check box, the Enable case-sensitive DBMS object names check box is also automatically selected.

The global settings apply to any new table metadata object, unless the settings are overridden by a local setting. For more information about DBMS object names, see Supporting Case and Special Characters in Table and Column Names.

Set Local Options for Tables

You can set local options that apply to individual tables. These local options override global options for the selected table, but they do not affect any other tables. For example, you can set local DBMS name options on the Physical Storage tab of the properties window for a table. These DBMS name options are listed in the following table.

Local Table Options on the Physical Storage Tab

Option Name	Description
DBMS	Specifies the database management system (DBMS) where the table is stored. To select a DBMS from a list, click the down arrow. The DBMSs that are valid in the current context are listed.
Library	Specifies a library that you can use to access the table. To select a library, click the selection arrow. To create a new library, click New, which opens the New Library wizard. To edit the properties of the existing library, click Edit, which opens the properties window for the data library.
Name	Specifies the name of the table. The name must follow the rules for table names in the DBMS that is selected in the DBMS field. For example, if SAS is the selected DBMS, the name must follow the rules for SAS data sets. If you select another DBMS, the name must follow the rules for tables in that DBMS. If the table is used for iterative or parallel processing, the name might contain a variable. For example, suppose you have the variable &myvar. You can name the table &myvar. You can also imbed the variable in the name (for example, September_&myvar). This usage ensures that parameters that are set for the iteration are recognized and that the table is included when the iterative process works through the list of tables contained in the control table.
Enable case-sensitive DBMS object names	Specifies whether SAS Data Integration Studio generates code that supports case-sensitive table and column names for the current table. If the check box is deselected, case sensitivity is not supported. If the check box is selected, case sensitivity is supported. This option overrides the global option with the same name.
Enable special characters within DBMS object names	Specifies whether SAS Data Integration Studio software generates code that supports special characters in table and names by default. If you select the check box, support is provided by default. When you select this check box, the Enable case-sensitive DBMS object names check box is also automatically selected. This option overrides the global option with the same name.
Create as view	Select this option to specify the current table as a view. Deselect this option to specify the current table as a physical table. In the context of a SAS Data Integration Studio job, if Create as view is selected for an output table, and the transformation that creates the table can create views, then the table is created as a view. Some transformations do not support views and might ignore the setting.
Table Options	Displays the Table Options window, where you can specify a compression scheme, password protection, or other options for the current table.

See Supporting Case and Special Characters in Table and Column Names for more information about DBMS object names.

You can set additional SAS table options in the Table Options window. To access this window, click Table Options on the Physical Storage tab of the properties window for a table. These options are covered in following table.

Local Table Options in the Table Options Window

Option name	Description
Compress	Specifies the kind of compression used, if any, for a SAS data set. (You cannot compress SAS data views because they contain no data.) Compression reduces storage requirements, but it increases the amount of CPU time that is required to process the file. Although compression affects performance by increasing the amount of CPU time that is required to process the file, it significantly reduces storage requirements. In particular, you want to enable compression for files such as Web logs that have columns with large widths that are sparsely filled. Select one of the following: NO (default): The SAS data set is not compressed. YES: The SAS data set is compressed using Run Length Encoding (RLE). Use this method for character data. BINARY: The SAS data set is compressed by using Ross Data Compression (RDC). Use this method for medium to large (several hundred bytes or larger) blocks of binary data (numeric variables).
Encrypt	Specifies whether a SAS data set is encrypted (YES) or not encrypted (NO). You cannot encrypt SAS data views because they contain no data.
Additional options	Specifies options for SAS data sets or views. Separate each option with a blank. The field is restricted to a maximum of 200 characters. You can specify a password for the table in this field. Use table option syntax, such as `read=readpw write=writepw`. When using SAS 9.2 as your workspace or batch server, passwords can be encoded using SAS 9.2 encoding.

DBMS Table Options in the Table Options Window

Option Name	Description
Table Options	Specifies the appropriate SAS/ACCESS table options for the specific DBMS. Separate each option with a space. The field is restricted to a maximum of 200 characters.