General Usage Notes

SAS Data Integration Studio cannot successfully generate code for a job that includes a DBMS table in which the double quotation mark is used in the table name or the column names.

You should use the CONNECTION=GLOBAL LIBNAME option whenever you have difficulty running concurrent queries to Teradata tables using the Teradata Access Engine. This setting is recommended when these queries fail because a race condition can occur when there are two concurrent queries to a table that uses a LIBNAME with a connection that is not global. This global connection can prevent a disconnect at the end of the SQL procedure.

In the process flow for a job, you can display the properties window for an output table, click the Physical Storage tab, and select the Create as view option. If this option is selected, the table will be created as a view, if that is possible. If it is not possible to create a view, a physical table will be created even when the Create as view option is selected. For example, the Extract transformation generates implicit pass-through code. If the format of the output table is a DBMS other than SAS, the creation of a view might not be permitted in the target DBMS. Deselect this option to specify that the table should be created as physical table instead of a view.

If you create a process flow in a SAS Data Integration Studio job that transfers a DBMS table from one computer to another and the table name includes special characters, the transfer fails. Neither implicit nor explicit data transfer works in this case.

In jobs that include the SCD Type 2 Loader and a Sybase target, or a Netezza target, SQL pass-through is not supported, even when you specify Yes for the option Use SQL Passthru.

In jobs that include the SCD Type 2 Loader and a Sybase target, the job will run only if you include the value UNDOPOLICY=NONE in the SQL Options field.

When the SCD Type 2 Loader Transformation is used to load Netezza or Neoview tables, the Autocommit option should be turned off in the DBMS library. To turn off this option, display the properties window for the library, and then select OptionsAdvanced Options and then click the Input/Output tab. Set the Whether to COMMIT immediately after a transaction field to No.

The Expression Builder is a component that enables you to create SAS expressions that aggregate columns, perform conditional processing, and perform other tasks in a SAS Data Integration Studio job. When you are specifying expressions for columns in DBMS tables, the Functions tab of the Expression Builder might display a folder that is named after the DBMS, such as Teradata. Keep in mind that functions unique to a DBMS will resolve only in the context of explicit pass-through processing. Otherwise, you will receive an error when the job is executed. One example of explicit pass-through processing would be in the context of the SQL Join transformation, with the explicit pass-through option turned on.

You can enter labels for columns in a SAS data set. These labels can be easier for users to manipulate and remember than the original column names. When the SAS data set is registered in a metadata repository, the labels are translated into the Description field for the column.

If you are using these SAS data sets to build information maps, then the column descriptions must not contain double less-than signs (<<) or double greater-than signs (>>). In addition, no description should end in a single greater-than sign (>). If the column description contains these characters, then these columns are not directly usable in expressions in an information map. You can use SAS Management Console or SAS Data Integration Studio to fix this problem by changing the description of the column.

If a table is stored in a library that was assigned or pre-assigned by using the metadata LIBNAME engine (MLE), then do not specify that table as a target in a SAS Data Integration Studio job. If you use an MLE library table as a target in a job, executing the job could have an unexpected impact on the job's metadata.

When SAS Data Integration Studio generates code for a job, it checks for the following conditions, among others:

Previous releases of SAS Data Integration Studio did not check for these two conditions.

In most cases, if no column mappings are defined for a transformation, code generation fails with an error. This rule does not apply to the User Written Code transformation and the Mining Results transformation. It also does not apply to any transformation for which code is not generated (the transformation is turned off or it retrieves user-written code). If you need a transformation that does not define mappings, use a User Written Code transformation or specify user-written code on the Process tab for a transformation.

If the number of source columns is not equal to the number of target columns in a transformation, a warning message is displayed in the SAS log for the job. You can take action, if appropriate.

Perform the following steps to temporarily turn off the new validations until the job can be fixed:

After you have corrected the job to take advantage of the updated error checking, you can remove the line that turns off the error detection. Remember to close SAS Data Integration Studio before attempting to remove the line from the wadef.txt file.

You should modify existing jobs to take advantage of the enhanced error detection at your earliest opportunity. Future releases of SAS Data Integration Studio might not enable you to turn off error detection for generated code.

The External File wizards have a Column Definition page. The bottom half of that page has a Data tab that will display data from the external file, if you have specified enough information about the file. When viewing data in the Data tab, you cannot click a column heading and sort the displayed data by the values in that column. The ability to sort the columns is currently not supported.

Jobs that feed source data on one type of host (such as a PC) through a loader to a target table on a different type of host (such as MVS) fail. To enable a transfer between different hosts, you must insert a Data Transfer transformation before the loader. This step ensures that the data is already in the target format before it is loaded into the target table.

Limit line lengths in the Expression Builder to 128 characters to avoid problems caused by server line-length restrictions.

In SAS Data Integration Studio, for an integer column with the SAS format w., the maximum allowable value is 2,147,483,646 (Integer.MAX_VALUE - 1). A double (floating point) column with the SAS format BESTw. does not have this limitation.

The Microsoft Queue Writer transformation does not transform missing date values to some other value. If missing date values are encountered, then an error occurs. Tables that are transmitted on the Microsoft MQ messaging system should be handled in accordance with the following recommendations:

For information about migrating from SAS Warehouse Administrator to SAS Data Integration Studio, see Migration: Converting from SAS Warehouse Administrator to SAS Data Integration Studio, which is available at the following Web site: http://support.sas.com/rnd/migration/planning/software/etlstatement.html.

You can set the MINMAXVARLIST option for either an SPD Server Loader transformation or an SPD Server table. However, the scope of the option depends on the type of object that you select.

When you set the MINMAXVARLIST option for an SPD Server Loader transformation, the option is applied to any table that is loaded with the transformation. To set the option on a loader, open the property window for the SPD Server Loader transformation. Then, click the Options tab and enter minmaxvarlist=xxx in the Advanced Data Table Options field (where xxx denotes the range of variables that you want to specify).

When you set MINMAXVARLIST option for an SPD Server table, the option is applied to the table in any job when the transformation that uses the table supports table options. To set the option on a table, open the properties window for the SPD Server Table. Then, click the Physical Storage tab and click Table Options. You can then enter minmaxvarlist=xxx in the Additional Options field (where xxx denotes the range of variables that you want to specify).

If you include an out-of-date generated transformation in a job that is deployed or redeployed, SAS Data Integration Studio automatically updates the transformation in the deployed job. The updated transformation code is generated instead of the original code, so the results of the job can be affected.

The HP Neoview DBMS does not support constraints (keys) on tables. Accordingly, SAS Data Integration Studio jobs cannot output HP Neoview tables that have keys.

SAS Data Integration Studio enables you to import and export metadata in formats that are supported by a SAS Metadata Bridge, such as Common Warehouse Metamodel (CWM) format. Make sure that you have the appropriate bridge. Ask your SAS representative for details.

When you update a SAS Data Integration Studio job that has produced output tables at least once, any changes that you make to the column metadata for its tables are not reflected in the physical tables until you select Drop Target on the Load Technique tab of the Loader transformation for the tables and you successfully execute the job again. Drop Target is not selected by default.

Most transformations in a SAS Data Integration Studio job send their output to a temporary output table. By default, temporary output tables are stored in the WORK library. Perform the following steps to change the library where the temporary output table is stored for a transformation:

SAS Data Integration Studio uses a SAS/CONNECT server to submit generated SAS code to computers that are remote from the default SAS Application Server. A SAS/CONNECT server can also be used for interactive access to remote libraries.

For SAS Data Integration Studio to generate the appropriate code for scripted sign on to a SAS/CONNECT server, you must specify a valid user ID and password in the sign-on script. The sign-on script is specified in the metadata for the SAS/CONNECT server in SAS Management Console.

When you add more than one source table that includes a primary key to a job that includes the SQL Join transformation, only the first primary key match is shown in the query. The auto-join feature of SQL Join works in such a way that primary keys must have foreign key relationships with the other tables participating in the join before auto-join finds them.

If two tables have different primary keys and you want both keys to participate in the join and have auto-join find both of them for you, foreign key associations must be made to represent the relationships between the two tables. Then, you do not have to enter one key manually. This feature ensures that the key relationships found for the tables are consistent.

The SQL Merge transformation cannot accept a temporary output table as a source table. You must use a table from one of the supported database management systems. In addition, you cannot use the same table as both the input and the output table for the SQL merge transformation.

When an SQL Merge transformation writes to a table in SQL Server 2008 format, use the SQLNCLI10 driver (SQL Server Native Client 10.0), not the older SQLOLEDB driver, to access SQL Server 2008.

The SQL Update transformation and the SQL Merge transformation do not support table aliases on the target table for the transformation. However, the Subquery interface for these transformations generates a table alias (‘t’) in the code for the subquery. To specify a subquery for a table that is the target of an SQL Update or SQL Merge transformation, for databases other than Oracle, edit the generated code as follows:

If you want to specify a subquery for an Oracle table that is the target of an SQL Update or SQL Merge transformation, contact SAS Technical Support.

If the synchpoint option in the Advanced Setting window for the WebSphere Queue Reader transformation is set to Yes, you might expect that any error while reading messages from the queue would prevent any data from being written from the queue to the target. Instead, all of the messages that precede the message where the error occurred are written to the target and the remaining messages are not.

For example, if an error occurs on the fifth message in a ten-message queue, messages one to four are written to the target, and messages five to ten are not written. In addition, an Ended with errors status is displayed for the job, and an error message is added to the log. Finally, all of the messages remain in the queue when an error occurs. If no errors are present, all of these messages are removed.

Any transformations that generate code that uses PROC SQL rename SAS reserved words without issuing a warning. (These reserved words are sometimes used as column names.) However, you can change this behavior by selecting the Enable case-sensitive DBMS object names check box and the Enable special characters within DBMS object names check box on the Physical Storage tab. Then, the SAS reserved words are treated as n literals and are not renamed.

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 logins 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 wizards and the New Table 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.

Although you can use the Update Table Metadata feature to update DBMS table names and column names that have special characters, you cannot use it for SAS table names and column names with special characters. A special character is any character that is not an underscore, a letter of the alphabet, or a numeric digit (such as 0 through 9).

You also cannot use this feature for PC format files, such as Microsoft Excel, or for parameterized tables. Finally, you cannot use the feature for Hadoop tables.

The Update Table Metadata feature updates table metadata so that it matches the corresponding physical table. However, if the physical table resides on a z/OS platform, the update might fail for large tables. A z/OS limit on the number of characters in a single line causes this problem.

When very large tables or files of 500,000 rows or more are run in a job that includes a WebSphere message queue, memory overruns can occur. These overruns occur because memory handles are not freed during WebSphere MQ calls. Instead, the memory handles are freed only when the DATA step ends. This delay in freeing the memory means that a potential exists to run the server out of memory when you write very large files to a queue. You can correct the problem by splitting the writes and reads up into smaller sets of data. You can also modify the generated code to free up memory by adding code to the generated code for the SAS job.

The following lines of SAS code must be added in order to free handles when you write records from a table to a queue or when you read records into a table from the messages on a queue. Add the following lines immediately before the line that consists of the label etls_mqexit: in the generated SAS code:

if etls_hmd ^=0 then
do;
/* Free message descriptor handle */
CALL MQFREE(etls_hmd);
end;
if etls_hmap ^=0 then
do;
/* Free map descriptor handle */
CALL MQFREE(etls_hmap);
end;
if etls_hdata ^=0 then
do;
/* Free Data handle */
CALL MQFREE(etls_hdata);
end;

When reading messages from a queue to a table, the SAS code to free up memory must be moved if it is added. Move the following mqmap call code to a position immediately after the line do i=1 to .....;:

call mqmap(etls_hmap, etls_qrc, etls_desc0, ....);
if etls_qrc ^= 0 then
do;
%rcSetDS(8000);
etls_qmessage = sysmsg();
put "ERROR: MQMAP: failed with reason code: " etls_qmessage;
goto etls_mqexit;
end;