When updating table metadata using PROC METALIB, or the Update Metadata menu item in SAS^® Management Console or SAS^® Data Integration Studio, you might experience a longer time to execute than when you initially registered the table.

When registering a new table, the process loads all columns as a single metadata DoRequest. The single request contains multiple updates. To illustrate, here is a representation of the request:

When updating an existing table in metadata, the process makes comparisons between attributes in metadata and attributes on the physical table. Changes for a column requiring an update are passed as a single DoRequest. To illustrate, here is a representation of the requests:

Each DoRequest requires an exclusive lock on the SAS^® Metadata Repository in order to perform the update. These update requests might be interspersed with other requests coming in to the SAS^® Metadata Server. Because of the additional work to perform the comparison, the requirement for multiple exclusive locks on metadata, the more real time the update step will take. The greater the number of columns requiring updating and the more overall activity on the SAS Metadata Server will increase the total time to perform the update.

Circumvention

In cases where a large number of columns need to be updated, consider deleting the table from metadata and registering it again as a new table. This can be a faster process overall. Be aware, however, that Data Integration Studio jobs rely on a table's objectId in metadata. Deleting and re–registering a table will cause the registered table to have a new objectId. You will have to rerun your jobs to use the new objectId for the table.

The following example demonstrates a programming technique using SAS Procedures to cause a table to be deleted from metadata, then added again. For this example, the Library Name in metadata is "My_Data_Library", the Libref is "MYLIB", and the table name is "My_Table".

Operating System and Release Information