Usage Notes for Register Tables Wizards and the New Table Wizard

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.

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 z/OS source using the ODBC Administrator.

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:

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:

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

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 ToolsOptions 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.

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.

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.

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:

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.

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

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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:

There are two main ways to supply valid logon credentials to the Teradata database 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.