Troubleshooting errors that occur when you register tables in the metadata


The following are examples of messages that might occur when you are registering tables to a SAS® metadata repository or when you are updating existing table metadata:

Errors occurred during table registration. View the Message window for details.
ERROR: No tables were retrieved from your query.
ERROR: Invalid option name [some-character-or-word].
Segmentation Violation In Task [ METALIB ]
ERROR: No metadata objects found matching the specified URI.
ERROR: Read Access Violation In Task [ METALIB (2) ]

The approach that you take to troubleshooting depends on the version of SAS and of the SAS application that you are using to register tables or update table metadata. In general, you want to enable enhanced logging so that you can see the SAS steps that were run. These steps are usually either a METALIB procedure or series of SQL procedure steps. It can also be helpful to run the steps in a SAS programming environment in order to troubleshoot further.

 

Contents/Sections:

Options for SAS Management Console, SAS Data Integration Studio, and the METALIB Procedure

SAS Management Console 9.4_M7
SAS Data Integration Studio 4.905,
SAS Data Integration Studio 4.904 with Hot Fix D8U008 (a member of the container fix D8Y008)

SAS Management Console 9.4_M6
SAS Data Integration Studio 4.904 without Hot Fix D8U008

SAS Management Console 9.4_M5 and Earlier
SAS Data Integration Studio 4.903 and Earlier

Enable Options for Debugging

Running Code in SAS to Troubleshoot Further 

Working with Technical Support


Options for SAS® Management Console, SAS® Data Integration Studio, and the METALIB Procedure

This section provides an overview of troubleshooting options that you can use for SAS Management Console, SAS Data Integration Studio, and PROC METALIB.

metalibdebug: This option instructs SAS Management Console and SAS Data Integration Studio to enable trace logging on PROC METALIB by including the TL=16383 option on the procedure step. It also enables a prompt at the end of the table-registration process for you to view the log. The prompt is generated whether or not an error is encountered. The trace information is shown in the log.

Note: Adding the -metalibdebug option adds the TL= option only if you are registering a table. The option is not added to the METALIB step that is used to update existing table metadata.

ignoremetalib: This option instructs SAS Management Console and SAS Data Integration Studio to use a series of PROC SQL steps, Dictionary Tables, and SQL Pass-through queries to collect information that is used to register a table to the metadata. This is the legacy method that was used in versions 9.4_M5 and earlier, and 4.04 and earlier, respectively. It might be a viable circumvention to register tables if PROC METALIB is producing errors for which there is no circumvention.

Note: Adding the -ignoremetalib option changes only the method used to register tables. PROC METALIB is always used when updating table metadata. The option produces only a prompt to view the log if there are errors during table registration. You should also enable application logging to see the PROC SQL steps that are run. Application logging is enabled by adding the option -consoleLog.

consoleLog: This option instructs SAS Management Console and SAS Data Integration Studio to generate a log file. Each new instance of the application creates a log file in the system user's "application data" directory. For example, if you logged in to Windows or Linux with the user name jsmith, the file is in a path similar to the following:

For SAS Management Console: 

For SAS Data Integration Studio:

Note that, for each new instance of SAS Management Console or SAS Data Integration Studio, a new file of the name SASMCErrorLog.txt or DataIntegrationStudio_Log.txt replaces the existing file. Technical Support recommends that you close SAS Management Console or SAS Data Integration Studio (to unlock the log file), and then rename the log file to prevent it from being overwritten on the next invocation of the respective application.

TL=: This statement option instructs PROC METALIB to write trace data to the SAS log. This option can be added to the PROC METALIB step if you are running the step in a stand-alone SAS session. TL=16383 is added by SAS Management Console and SAS Data Integration Studio if the -metalibdebug option is provided to the application.

Here are the tracing level (TL) values for PROC METALIB:

To request multiple tracing levels, sum the numbers for each level together. For example, if you run TL=5, the following are returned: 1: routine entry/exit AND 4: current table being processed.

SAS® 9.2 through SAS® 9.3 TS1M1 support up to TL=4094.

SAS® 9.3 TS1M2 through SAS® 9.4 TS1M1 support up to TL=4095.

SAS® 9.4 TS1M2 and later support up to TL=16383.

sastrace=: This SAS system option generates trace information from a database management system (DBMS) engine. The argument value shown below specifies that all SQL statements that are sent to the DBMS are also included in the log. Also use sastraceloc and nostsuffix as shown below to direct the trace data to the SAS log and to make the log output easier to read:

sastrace=',,,d' sastraceloc=saslog nostsuffix

SAS Technical Support might request that you provide a different argument value for the sastrace option. The value determines which information is written to the SAS log. Refer to SASTRACE= SAS System Option in SAS/ACCESS 9.4 for Relational Databases: Reference, Ninth Edition for more information.

 

SAS Management Console 9.4_M7
SAS Data Integration Studio 4.905
SAS Data Integration Studio 4.904 with Hot Fix D8U008 (a member of the container fix D8Y008)

These versions of SAS Management Console and SAS Data Integration Studio use PROC METALIB to register tables to metadata. They support the following options:

Use -metalibdebug as the first approach to troubleshooting. SAS Management Console and SAS Data Integration Studio return a prompt at the end of processing for you to view the log. The log contains METALIB trace information and requests to the SAS Metadata Server shown in XML format.

Evaluate the log for messages that might help you identify the cause of the problem. For example, you might see an incorrect schema used in the database connection options.

Use -ignoremetalib to register tables using the legacy method, which uses PROC SQL steps to query the database for table information. This option might be a viable circumvention to register tables if PROC METALIB is producing errors for which there is no circumvention.

Use -ignoremetalib -consoleLog together to see the specific queries that were used to get tables, columns, indexes, primary keys, and foreign keys. Running the SQL steps in a separate SAS session is valuable to compare to the process in METALIB, and to more thoroughly investigate specific queries to the database. See Running Code in SAS to Troubleshoot Further.

 

SAS Management Console 9.4_M6
SAS Data Integration Studio 4.904 without Hot Fix D8U008

These versions of SAS Management Console and SAS Data Integration Studio use PROC METALIB to register tables to metadata. They support the following options:

Use -consoleLog as the first approach to troubleshooting. Each new instance of SAS Management Console or SAS Data Integration Studio creates a log file in the system user's "application data" directory. Find the PROC METALIB step in that log and run it in a separate SAS programming session. In that program, you can specify other options such as TL= and sastrace=. See Running Code in SAS to Troubleshoot Further.

Use -ignoremetalib to register tables using the legacy method, which uses PROC SQL steps to query the database for table information. That might be a viable circumvention to register tables if PROC METALIB is producing errors for which there is no circumvention.

Use -ignoremetalib -consoleLog together to see the specific queries that were run to get tables, columns, indexes, primary keys, and foreign keys. Running the SQL steps in a separate SAS session is valuable to compare to the process in METALIB, and to more thoroughly investigate specific queries to the database. See Running Code in SAS to Troubleshoot Further.

 

SAS Management Console 9.4_M5 and Earlier
SAS Data Integration Studio 4.903 and Earlier

These versions of SAS Management Console and SAS Data Integration Studio use a series of PROC SQL steps, Dictionary Tables, and SQL Pass-through queries to collect information that is used to register a table to the metadata. The sections above refer to this as the "legacy" method. PROC METALIB is supported in SAS code. These client versions did not use METALIB to register tables. METALIB is always used to update table metadata. They support the following option:

Use -consoleLog to begin troubleshooting. Each new instance of SAS Management Console or SAS Data Integration Studio creates a log file in the system user's "application data" directory. Find the PROC SQL steps in that log and run them in a separate SAS programming session. In that program, you can specify other options such as sastrace=. See Running Code in SAS to Troubleshoot Further.

 

Enable Options for Debugging

The options for SAS Management Console and SAS Data Integration Studio are specified in the application's .ini file.

Here are the steps for SAS Management Console:

  1. Exit all instances of SAS Management Console on the machine on which you are running that application. These steps apply only to SAS Management Console on the machine that is running the application.
  2. Edit the sasmc.ini file. The path should be similar to the samples here:
  1. On or near the last line in the file (which begins with MainClass=), add option(s) after the JAR name. Here are four examples of that line after editing.
MainClass=com.sas.console.visuals.MainConsole -metalibdebug
MainClass=com.sas.console.visuals.MainConsole -ignoremetalib
MainClass=com.sas.console.visuals.MainConsole -ignoremetalib -consoleLog
MainClass=com.sas.console.visuals.MainConsole -consoleLog
  1. Save and close the .ini file. The option(s) are used on the next invocation of SAS Management Console.

Here are the steps for SAS Data Integration Studio:

  1. Exit all instances of SAS Data Integration Studio on the machine on which you are running that application. These steps apply only to SAS Data Integration Studio on the machine that is running the application.
  2. Edit the distudio.ini file. The file location should be similar to the following example:
  1. On or near the last line in the file (which begins with MainClass=), add option(s) after the JAR name. Here are four examples of the line after editing:
MainClass=com.sas.wadmin.application.TheAppWA -metalibdebug
MainClass=com.sas.wadmin.application.TheAppWA -ignoremetalib
MainClass=com.sas.wadmin.application.TheAppWA -ignoremetalib -consoleLog
MainClass=com.sas.wadmin.application.TheAppWA -consoleLog
  1. Save and close the .ini file. The option(s) are used on the next invocation of SAS Data Integration Studio.

 

Running Code in SAS to Troubleshoot Further

Since SAS Management Console and SAS Data Integration Studio use a SAS® Workspace Server session on the SAS® Application Server that is associated with the library (such as SASApp), you should also run your troubleshooting steps in a workspace server session, or be certain that your SAS session uses the relevant configuration properties and environment variables to assign the libname. SAS® Enterprise Guide® and SAS Data Integration Studio provide coding environments that meet this goal. Log on to the client application using the same user name as you do when logging in to SAS Management Console or SAS Data Integration Studio to register tables.

 

Run PROC METLIB in a separate SAS session with trace options enabled:

  1. Find the PROC METALIB step in the application log. Copy the entire step to a new SAS program. If a LIBNAME and/or OPTIONS statement precedes the METALIB step, copy those statements too. Remove log information other than the SAS statements, being careful to keep semicolons, which might appear on the next line. Skip to the next step if you already have a PROC METALIB step that you want to debug.

The step contains objectIds for the library and metadata folder. Here is an example showing LIBNAME and OPTIONS statements and a METALIB step where a table named CLASS was selected to register. The line spacing and line breaks look different in the log.

LIBNAME MYLIB ORACLE PATH=MYORADB
  PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
  USER=scott PASSWORD="XXXXXXXX" ;

options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;

proc metalib;
   omr (LIBURI="A5WFRYW0.B5000006"
     user="sasdemo" password=XXXXXXXX);
   REPORT(type = summary out = "tab_info");
   SELECT ("CLASS" );
   FOLDERID = "A5WFRYW0.AA000014";
run;
  1. Edit your program as shown below to add metadata server connection options and values appropriate for your environment (if the SAS session you are using is not already connected to the SAS Metadata Server), the SAS system options and TL= statement option to enable tracing and the user and password options on the OMR statement to authenticate to the metadata. Replace the password masking characters with your database and metadata passwords. Here is an example with the changes made:
options metaserver="sasbi.demo.sas.com"
   metaport=8561
   metarepository=Foundation
   metaprotocol=BRIDGE;

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

LIBNAME MYLIB ORACLE PATH=MYORADB
  PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
  USER=scott PASSWORD="tiger" ;

options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;

proc metalib TL=16383;
   omr (LIBURI="A5WFRYW0.B5000006"
     user="sasdemo" password="DemoMetaPass");
   REPORT(type = summary out = "tab_info");
   SELECT ("CLASS" );
   FOLDERID = "A5WFRYW0.AA000014";
run;
  1. Submit the program and evaluate the SAS job log.

 

Run PROC SQL steps in a separate SAS session with trace options enabled:

The PROC SQL steps are used only to query for table information. That information is used to register tables by internal processes in SAS Management Console and SAS Data Integration Studio. As such, the steps that follow do not write to the metadata and so metadata connection options are not required. You can use these steps to help troubleshoot the gathering of information from the database.

  1. Find the PROC SQL steps in the application log and copy each of them to a new SAS program. If a LIBNAME and/or OPTIONS statement precedes the first PROC SQL step, copy those statements too. Remove log information other than the SAS statements, being careful to keep semicolons, which might appear on the next line.
  2. Edit your program as shown below to add the SAS system options to enable database tracing. Replace the password masking characters (if present) with your database password. Here is an example after the code changes were made showing the steps to collect information for a table named EMP from an Oracle library.
options sastrace=',,,d' sastraceloc=saslog nostsuffix;

LIBNAME MYLIB ORACLE PATH=MYORADB
  PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
  USER=scott PASSWORD="tiger" ;

options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;

PROC SQL;
  CREATE TABLE work._memTypes AS
  Select MEMTYPE, MEMLABEL, COMPRESS, ENCRYPT, DBMS_MEMTYPE
  from dictionary.tables
  where libname="MYLIB" and memname="EMP" and MEMTYPE in ("DATA", "VIEW");;
quit;

PROC SQL;
   CREATE TABLE work._cols AS
   Select NAME, TYPE, LENGTH, FORMAT, INFORMAT, LABEL, NOTNULL
   from dictionary.columns
   where libname="MYLIB" and memname="EMP" and MEMTYPE in ("DATA", "VIEW");
quit;
  
PROC SQL;
   CONNECT TO ORACLE( PATH=MYORADB USER=scott PASSWORD="tiger" );
   CREATE TABLE _Indexes AS
   SELECT COLUMN_NAME, INDEX_NAME, SEQ_IN_INDEX, UNIQUE, 0 as ISPRIMARY
   FROM CONNECTION TO ORACLE(DBMS::Indexes("","","EMP"))
   order by INDEX_NAME desc;
QUIT;

PROC SQL;
   CONNECT TO ORACLE( PATH=MYORADB USER=scott PASSWORD="tiger" );
   CREATE TABLE _PrimaryKeys AS
   SELECT COLUMN_NAME
   FROM CONNECTION TO ORACLE(DBMS::PrimaryKeys("","","EMP"))
QUIT;

PROC SQL;
   CONNECT TO ORACLE( PATH=MYORADB USER=scott PASSWORD="tiger" );
   CREATE TABLE _ForeignKeys AS
   SELECT PKCOLUMN_NAME, FKTABLE_NAME, FKCOLUMN_NAME
   FROM CONNECTION TO ORACLE(DBMS::ForeignKeys("","","EMP", "", "", ""))
QUIT;
  1. Submit the program and evaluate the SAS job log.

 

Working with Technical Support

If you are working with SAS Technical Support, please attach the following to your Technical Support case:

If a password is present in the file, mask the password value before sharing the file with SAS Technical Support.