JR34795: DataStore value is not populated in the Locator section when importing table definitions with ORAOCI9 Plug-in

Download Fix Pack 2 for InfoSphere Information Server Version 8.1

APAR status

• Closed as program error.

Error description

• When a table definition is imported with Oracle OCI Plug-in
  (ORAOCI9) The SQL Builder is then used to create SQL statement
  and the generated statement does not qualify the table name
  withthe  schema (owner) name.
      What is charateristic for this problem is that it happens
  only  when the global database name does not include domain
  name   portion and it can be observed that in this case the
  DataStore  attribute value is not set in the Locator section of
  the    imported table definition.
  

Local fix

• Explicitly provide the value for the plug-in to use for the
  DataStore and Computer attributes in the Locator section of the
  imported table definition.
  
  
  This can be done by defining a new table
  MetaStage_Loc_Info in  the database. The table needs to be
  created under the same schema under which the table that
  needs to be imported resides. The table should contain the
  following columns:
  
  Computer VARCHAR2(64),
  SoftwareProduct VARCHAR2(64),
  DataStore VARCHAR2(64),
  DataSchema VARCHAR2(64))
  
  Insert and commit a single row of data in this table.
  
  If you are not sure which values to specify for the columns in
  the table, consider the
  following suggestions:
  
  For the Computer column specify the host name of the computer
  hosting the database.
  This will become the Computer value in the Locator.
  For example, use the value returned from the following
  statement:
  
  SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST') FROM SYS.DUAL;
  
  For the Product column use the hard-coded value 'Oracle'.
  This will become the SoftwareProduct value in the Locator.
  
  For the DataStore column use the name of the service.
  This will become the DataStore value in the Locator.
  For example, use the value returned from the following
  statement:
  
  SELECT SYS_CONTEXT('USERENV', 'SERVICE_NAME') FROM SYS.DUAL;
  
  The DataSchema value is not used by the plug-in, but to be
  consistent set it to the name
  of the current schema (matches the name of the user connected
  todatabase).
    For example, use the value returned from the following
    statement:
  
  SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM
  SYS.DUAL;
  
  Now import the table definition.
  Notice that Computer, SoftwareProduct and DataStore values in
  the Locator of the imported table nare set to match
  the values  from the MetaStage_Loc_Info table.
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  DataStage users who import table definitions using Oracle OCI
  (ORAOCI9) Plug-in
  ****************************************************************
  PROBLEM DESCRIPTION:
  When a table definition is imported with Oracle OCI Plug-in
  (ORAOCI9) and the global name of the database is in format
  db_name.db_domain, the DataStore attribute in the Locator
  section of the imported table definition is set to db_name, and
  the Computer attribute is set to db_domain. In case the
  db_domain part is missing from the global database name, the
  DataStore attribute is not set and the Computer attribute is set
  to the Database Source Name connection string value that was
  specified during the import. As a result of DataStore attribute
  not being set, the SQL Builder does not qualify the table name
  with the schema (owner) name in the SQL statement generated for
  the table.
  ****************************************************************
  RECOMMENDATION:
  A patch was not produced for this particular APAR. The fix for
  this problem is included in the patch JR34982. Install patch
  JR34982 or later.
  ****************************************************************
  

Problem conclusion

• Fix was added to the plug-in to support handling of situation
  when global database name does not include domain name. In this
  case the entire database name is used as DataStore attribute
  value in the Locator section. This in turn results in qualifying
  table names with schema names in the statements generated with
  SQL Builder. The Computer name is set to the Database Source
  Name connection string value which was specified during the
  import.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• R810 PSN

     UP