60051 - Running PROC METALIB deletes column mappings from SAS® Data Integration Studio jobs

Problem Note 60051: Running PROC METALIB deletes column mappings from SAS® Data Integration Studio jobs

In SAS Data Integration Studio, column mappings for Database Management System (DBMS) tables might be deleted.

This problem might happen when all the following conditions are true:

The DBMS contains tables that have the same name, but in different cases (for example, test, TesT, and TEST).
The SAS library is defined in the metadata with the Preserve DBMS table names option set to YES (the SAS LIBNAME statement contains PRESERVE_TAB_NAMES=YES).
Mixed-case table names are registered in the metadata (for example, test, TesT, and TEST, which can exist only in different folders in SAS Metadata).
PROC METALIB is used to update the SAS metadata about the DBMS tables.

When you encounter this problem, you might see that a transformation is marked as incomplete when you open a SAS Data Integration Studio job. You also might see the following in the SAS log when you run the job:

NOTE: The following column(s) do not have a column mapping, so the value(s) will be set to missing: <list of columns>

Here is example code that triggers this problem:

proc metalib ;

   omr (library="<DBMS Library>");

   report(type = detail);

   update_rule = (delete);

   dbauth(dbuser=<dbmsUserID>) dbpassword=<dbmsPassword>);

   select ("TEST") ;

run;

This code is intended to update the metadata for the TEST table. Instead, PROC METALIB updates the test registered table with columns from the TEST DBMS table. In this scenario, PROC METALIB generates no error, the output indicates that columns have been deleted and added, and the SAS log shows the following message:

NOTE: Metadata for 1 tables was updated.

To work around this issue, when using PROC METALIB, you must specify the Metadata ID of the table or qualify its location in the metadata by using the Folder Name or the Folder ID. Click the Full Code tab for examples of how to use PROC METALIB.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

Product Family	Product	System	Product Release		SAS Release
Product Family	Product	System	Reported	Fixed*	Reported	Fixed*
SAS System	Base SAS	z/OS	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		z/OS 64-bit	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft® Windows® for x64	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows 8 Enterprise 32-bit	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows 8 Enterprise x64	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows 8 Pro 32-bit	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows 8 Pro x64	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows 8.1 Enterprise 32-bit	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows 8.1 Enterprise x64	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows 8.1 Pro 32-bit	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows 8.1 Pro x64	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows 10	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows Server 2008	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows Server 2008 R2	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows Server 2008 for x64	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows Server 2012 Datacenter	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows Server 2012 R2 Datacenter	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows Server 2012 R2 Std	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Microsoft Windows Server 2012 Std	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Windows 7 Enterprise 32 bit	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Windows 7 Enterprise x64	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Windows 7 Home Premium 32 bit	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Windows 7 Home Premium x64	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Windows 7 Professional 32 bit	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Windows 7 Professional x64	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Windows 7 Ultimate 32 bit	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Windows 7 Ultimate x64	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		64-bit Enabled AIX	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		64-bit Enabled Solaris	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		HP-UX IPF	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Linux for x64	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4
		Solaris for x64	9.4_M2	9.4_M4	9.4 TS1M2	9.4 TS1M4

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

Use one of the following forms of PROC METALIB to work around the issue. You must provide Metadata Connection options in order to run the code.

proc metalib ; * Use Tables Metadata ID ;
   omr (library="DBMS Library");
   report(type = detail);
   update_rule = (delete);
   dbauth(dbuser=dbmsUserID) dbpassword=dbmsPassword);
   select ("RepositoryID.PhysicalTableID") ;
run;

proc metalib ; * Qualify the Tables locations use its Metadata Folder Location ;
   omr (library="DBMS Library");
   report(type = detail);
   update_rule = (delete);
   dbauth(dbuser=dbmsUserID) dbpassword=dbmsPassword);
   select ("TEST") ;
   folder="/FolderLocation";
run;

proc metalib ; * Qualify the Tables locations use its Metadata Folder ID ;
   omr (library="DBMS Library");
   report(type = detail);
   update_rule = (delete);
   dbauth(dbuser=dbmsUserID) dbpassword=dbmsPassword);
   select ("TEST") ;
   folderid="RepositoryID.FolderID";
run;

Type:	Problem Note
Priority:	high

Date Modified:	2017-03-28 08:51:31
Date Created:	2017-02-28 03:55:59

Support

Problem Note 60051: Running PROC METALIB deletes column mappings from SAS® Data Integration Studio jobs

Operating System and Release Information