Usage Notes for Register Tables Wizards and the New Table Wizard :: SAS(R) Data Integration Studio 4.9: User's Guide

Access to Data on z/OS Platforms

Data on a z/OS platform must be stored in a UNIX System Services (USS) directory rather than in an MVS bound library. For a USS directory, the physical name of the library is the same as the directory path. For more information, see "LIBNAME Statement: z/OS" in the SAS Companion for z/OS.

Access to Tables Using ODBC DB2 z/OS Pass-through

To use the pass-through facility for ODBC DB2 z/OS to access tables, you must configure the password and user ID. Because the DB2 z/OS pass-through does not support the PASSWORD= and USER= options, you must configure these options on the ODBC DB2/zOS source using the ODBC Administrator.

Case and Special Characters in DBMS Names

SAS Data Integration Studio cannot access DBMS tables with case-sensitive names or with special characters in names unless all of the following name options are specified:

in the metadata for the database library that is used to access the table:
- set Preserve DBMS table names to YES
- set Preserve column names as in the DBMS to YES
in the metadata for the table itself:
- select Enable case-sensitive DBMS object names
- select Enable special characters within DBMS object names

Case and Special Characters in SAS Names

By default, the names for SAS tables and columns must follow the rules for SAS names. However, SAS Data Integration Studio supports case-sensitive names for tables, columns, and special characters in column names if you specify the following options in the metadata for the SAS table:

select Enable case-sensitive DBMS object names
select Enable special characters within DBMS object names

Double-byte character set (DBCS) column names are supported in this way, for example.

Control Whether SAS Formats and Informats Are Automatically Applied to Table Columns

You can control whether SAS formats and informats are automatically applied to table columns when you register tables or when code is generated for tables. For example, the Library Information tab in the Register Tables wizard has a new check box: Include formats and informats in column definitions. If you clear this check box, formats and informats will not be registered in metadata for the columns when the table information is created. Three options control the use of formats and informats in generated code. To control the use of formats and informats globally, select Tools

Options

Job Editor Tab, and then set the format or informat option in the Automatic Settings area. To control the use of formats and informats in a job, open the properties window for the job. Then click the Options tab, and then set the format or informat option on the General pane. To control the use of formats and informats in a transformation, open the properties window for the transformation. Then click the Options tab, and then set the format or informat option on the Advanced Options pane.

Generic Register Tables Wizard: When to Use

In general, a Register Tables wizard for a specific type of data generates more useful metadata than the Generic Register Tables wizard. Use the Generic Register Tables wizard only when a Register Tables wizard for a specific type of data is not available.

Importing Foreign Keys for DBMS Tables

Tables in a database management system often have primary keys, unique keys, and foreign keys. When you register a DBMS table with foreign keys, if you want to preserve the foreign keys, select all of the tables that are referenced by the foreign keys at the same time, in a single pass of the wizard. Similarly, when you export or import a DBMS table with foreign keys, select all of the tables that are referenced by the foreign keys at the same time, in a single pass of the wizard.

Importing Foreign Keys for SAS Tables

The Register Tables wizard for data in SAS format imports metadata for SAS tables, including the metadata for foreign keys. To successfully import the metadata for foreign keys in SAS tables, the following conditions must be met:

Primary keys and foreign keys must have unique names across all SAS tables in all SAS libraries from which metadata is imported.
In the Define Tables window in the Register Tables wizard, select the primary key table and all related foreign key tables. Otherwise, the metadata is incomplete. (If the metadata is incomplete, then all registrations must be deleted and the complete set of related tables would need to be imported again to get the complete set of metadata objects.)

After you import the metadata for a table, you can view the metadata for any keys by displaying the properties window for the table and clicking the Keys tab.

Importing Keys and Indexes from SAS/SHARE Libraries

You can import keys and indexes for SAS tables in a SAS/SHARE library but not for DBMS tables in a SAS/SHARE library.

LIBNAME Options Required for Support of Case and Special Characters in the Names for Keys and Indexes

The Register Tables wizard can register database tables, including the metadata for keys and indexes. However, when you select a database library in the Register Tables wizard, to preserve case-sensitive names in table keys and indexes, and to preserve names with special characters in table keys and indexes, you must specify the Preserve DBMS table names option and the Preserve column names as in the DBMS option for the selected library.

In a Register Tables Wizard, Limit Excel Connections to a Single User Name

Dedicate a single user name to use when you register Excel data with the SAS/ACCESS Excel engine and the SAS Data Integration Studio Register Tables wizard for ODBC or OLE DB. This restriction is necessary because of connection and LIBNAME errors that are generated when you register Excel data. Close and restart SAS Data Integration Studio and try to register Excel data using a different user name. The connection error states that the connection has been opened exclusively by another user or that you need permission to view the data. In either case, a connection to the Excel data cannot be established under the second user name. At this point, you must either use the original user name or restart the application server.

Limitations of Register Tables Wizards for MySQL and Informix

Primary keys, foreign keys, and index cannot be registered for Informix, ODBC Informix, and OLE DB Informix tables.

To set preserve_tab_names, open the Advanced Options window for an Informix library either from the New Library Wizard or from the properties window for the library. Select the Output tab and select a value of YES for the Preserve column names as in the DBMS field. To set preserve_col_names, from the Advanced Options window, select the Input/Output tab and select a value of YES for the Preserve DBMS table names field.

Metadata for a Library and Its Tables Must Be Stored in the Same Metadata Repository

The metadata for a library and the metadata for the tables in the library must be stored in the same metadata repository. Other configurations are not supported in this release.

Microsoft Windows Administrative Rights Required to Connect to OLE DB Data Sources

Users must have Microsoft Windows administrative rights on the server that contains the data before they can connect to any OLE DB data source. If a user does not have these administrative rights, an attempt to connect to an OLE DB data source generates the following text:

ERROR: Error trying to establish connection: 
Unable to load OLE DB conversion library
ERROR: Error in the LIBNAME statement.

This error message is displayed even when the user has a valid Microsoft Windows user ID and password to log on to the server. After the administrative rights have been granted, the user can connect to OLE DB data sources without generating the error.

ODBC Informix Library: Preserving Case in Table Names

Perform the following steps to preserve the case of table names when using an ODBC Informix library with a Register Tables wizard:

From the SAS Data Integration Studio desktop, right-clock the folder in the Folders tree where the metadata for the ODBC table should be saved. Then select Register Tables. The wizard selection window is displayed.
Open the ODBC Sources folder in the wizard selection window.
In the ODBC Sources folder, select ODBC Informix. The ODBC Informix Register Tables wizard is displayed. The first window enables you to select an ODBC Informix library.
Select the appropriate ODBC Informix library and then click Edit. A library properties window is displayed.
On the library properties window, click the Options tab.
On the Options tab, click Advanced Options. The Advanced Options window is displayed.
In the Advanced Options window, select the Output tab.
On the Output tab, select Yes in the Preserve column names, as in the DBMS field.
Enter the following in the Options used in DBMS CREATE TABLE field:
```
QUOTE_CHAR=
```
Click the Input/Output tab.
Select Yes in the Preserve DBMS table names field.
Click OK to save your changes.

Registering SAS/SHARE Tables

The SAS/SHARE Register Tables wizard enables you to select a library that contains the tables to be registered. Be sure to select a SAS/SHARE client library, not a SAS/SHARE server library.

Registering a Sybase Table with a Clustered Index

If you use the Sybase source designer wizard to register a Sybase table with a clustered index, the wizard does not register the clustered index by default. To register a clustered index, select the Clustered values check box on the Index tab of the wizard.

Separate Logon Credentials for Each Authentication Domain for Database Servers

Administrators define the metadata for users and groups as part of the setup tasks for a data warehousing project. The logon metadata for each user and group includes an authentication domain.

Each user (or a group to which the user belongs) must have a user ID and password for the authentication domain that is associated with the relevant database server definition. That user ID and password must correspond to an account that has been established with the database. Otherwise, the user cannot read any existing tables in the relational database, and the user cannot use the Register Tables wizard or the New Tables wizard to access tables in the relational database.

Accordingly, administrators must define separate logon credentials for each authentication domain that contains a database server that you need to access. For more information about defining logon metadata for users and groups, see the SAS Intelligence Platform: Security Administration Guide.

Setting Table Options in the New Table Wizard

The New Table wizard and the property windows for tables include a physical storage tab or window. This tab or window includes a Table Options button. Click that button to specify options for the current table. For details about options for SAS tables (data sets and views), see SAS Language Reference: Dictionary.

Table Names over 32 Characters Are Truncated in the New Table Wizard

You can create a table with a name that has more than 32 characters in the New Table wizard. However, the name of the table will be truncated to its first 32 characters. If another table that begins with the same first 32 characters is created in the New Table wizard in the same library, this new table name is also truncated to the same 32-character name. If the second table is used in a SAS Data Integration Studio job, then its data could overwrite the data in the physical table from the identically named first table.

Teradata Register Tables Wizard Hangs Unless a User ID and Password Can Be Supplied

The Register Tables wizard enable you to import metadata for one or more tables in a library. One of the first windows in the wizard enables you to select the library that contains the tables. When you select a library, a connection is made to a SAS Application Server. The server accesses the selected library and lists any tables that are associated with that library.

The Teradata Register Tables wizard cannot connect to a Teradata database library unless both of the following conditions are met:

A Windows environment variable, GUILOGON, is defined and set to NO on the computer where SAS/ACCESS to Teradata is running. (This variable is typically set for the SAS Workspace Server component of the SAS Application Server that is used to access the Teradata database.) For details about how to define Windows environment variables, see the appropriate Windows documentation.
Valid logon credentials are supplied to the Teradata database server.

There are two main ways to supply valid logon credentials to the Teradata database server:

Add a default user ID and password to the metadata for the Teradata database library.
Implement single sign-on (SSO) for the Teradata database on Windows. For details about SSO, the database administrator should see the appropriate Teradata documentation.

Unrestricted Users Cannot Perform Tasks That Require Logon Credentials from the Metadata Server

An unrestricted user is one of the administrative users that can be defined for a SAS Metadata Server. If SAS Data Integration Studio and related software have been configured with the SAS Deployment wizard, a default unrestricted user called sasadm is created.

An unrestricted user such as sasadm cannot access other servers by retrieving logon credentials from the metadata server. For example, you cannot log on to SAS Data Integration Studio as an unrestricted user and access the servers that are required by the Register Tables wizard or the New Tables wizard. It also means that an unrestricted user cannot use the Metadata Export wizard to include or replace physical tables in a DBMS.

For details about the unrestricted user, see the SAS Intelligence Platform: Security Administration Guide.