PI98210: NEED DOCUMENT UPDATES ABOUT ORACLE DATABASE MIGRATION FOR FUTUREIBM SPSS C&DS RELEASE

APAR status

• Closed as documentation error.

Error description

• You have a functioning IBM SPSS Collaboration and Deployment
  Services (C&DS) installation and are planning on upgrading to
  C&DS 8.1.1. Your repository is housed in an Oracle database.
  
  During past migrations of C&DS, you observed failures due to XML
  tables that are not part of a normal export/import process of
  Oracle DB. These XML tables (SPSSDMRESPONSE_LOG and
  SPSSSCORE_LOG) are a requirement for C&DS, but is a constant
  source of problems as there are no full documentation on how to
  retain this during the migration process.
  
  
  C&DS documentation (Migrating to a different database) has the
  following statement:
  ################
  Note that because of differences between database environments
  and vendor copy tools, such as Db2 backup, MS-SQL Server backup,
  or Oracle RMAN, you must verify during migration that the
  following database features are supported by the tool you have
  selected:
  
  XML tables (SPSSDMRESPONSE_LOG and SPSSSCORE_LOG)
  Binary data/BLOB, CLOB
  Special date formats
  
  For example, Oracle 11g Data Pump does not support XML tables.
  Therefore, it can be used for restoring all repository tables
  except the two XML tables. The XML tables can be migrated using
  Oracle Export. Review all database vendor requirements, like XML
  schema registration in MS SQL Server and Oracle. It is
  recommended to consult IBM Corp. support before database
  migration.
  ################
  
  
  C&DS documentation (Installing with a copy of the repository
  database) has the following statement:
  ################
  Make a copy of the existing repository database. The database
  copy can be performed using the database vendor or third-party
  tools.
  ################
  
  
  Areas of concern are:
  * Why is the reminder to retain the XML tables is in the
  Migrating portion of the documentation and not in the Installing
  with a copy section?
  * Did IBM SPSS test the 'copy the repository database' method
  with Oracle? Was there any special requirement observed when
  Oracle is the database being used? If there were any
  'tips/tricks' that was needed, can it be documented? Which
  commands were used as Oracle comes with a number of tools that
  an end-user can leverage?
  *
      Suggestion to remove "It is recommended to consult IBM Corp.
  support before database migration" and replace it with "It is
  recommended to consult your DB Administrator before database
  migration." as it places greater emphasis on the customer to
  ensure their database is ready for migration.
  

Local fix

• This has been reported to the IBM SPSS Collaboration and
  Deployment Services development team and a review of our
  Installation/Migration and our Troubleshooting documentation has
  been submitted.
  
  
  If, during the migration, you observe the following error,
  please use the steps below to resolve the issue:
  
  ## Error ##
  ERROR: registerOracleSchema schema evolve failed for
  namespace:http://xml.spss.com/dms/logging
  java.sql.SQLException: ÝSPSSOEM¨ÝOracle JDBC
  Driver¨ÝOracle¨ORA-30944: Error during rollback for XML schema
  'http://xml.spss.com/dms/logging' table
  "<spssCaDS_DB>"."Info146_TAB" column ''
  ORA-02304: invalid object identifier literal
  ORA-30942: XML Schema Evolution error for schema
  'http://xml.spss.com/dms/logging' table <n/a> column ''
  ORA-31088: object
  "<spssCaDS_DB>"."BIN$aQKqfcp02AHgU3IgGaww4A==$0" depends on the
  schema
  ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 153
  ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 221
  ORA-06512: at "<spssCaDS_DB>.SPSSEVOLVE", line 1
  ORA-06512: at line 1
  
  
  ## Resolution ##
  1) Export all the tables/views/data/schemas.... from the old
  database using command "expdp".
  2) In the target database, create a new user which name and
  privilege are the same as the source database user
  3) Copy and register the following two schema files in the
  target database
      ?scoring-logging.xsd? which can be found under
  <CDS_INSTALL_DIR>\staging\scoring.package\logging\schemas\oracle
  ;
      'response-logging.xsd' which can be found under
  <CDS_INSTALL_DIR>\staging\DecisionManagement.package\logging\sch
  emas\oracle;
  
  4) Run the following sql script to register XML schema
  
  CREATE DIRECTORY CADS_DIR_MIGR AS '/home/oracle/data_migration';
  alter session set current_schema=L9571;
  begin
  dbms_xmlschema.registerschema(
  schemaurl => 'http://xml.spss.com/scoring/logging',
  schemadoc => bfilename('CADS_DIR_MIGR','scoring-logging.xsd')
  );
  end;
  /
  alter session set current_schema=L9571;
  begin
  dbms_xmlschema.registerschema(
  schemaurl => 'http://xml.spss.com/dms/logging',
  schemadoc => bfilename('CADS_DIR_MIGR','response-logging.xsd')
  );
  end;
  /
  
  
  5) Import all the tables/views/data/schemas.... to the new
  database using command "impdp".
  6) Install CaDS Repository Server in the new OS and run
  configTool, mapping the database to the new one.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * This affects users who are using Oracle Database for the IBM *
  * SPSS Collaboration and Deployment Services repository.       *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * NEED DOCUMENT UPDATES ABOUT ORACLE DATABASE MIGRATION FOR    *
  * FUTUREIBM SPSS C&DS RELEASE                                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Please upgrade to IBM SPSS Collaboration and Deployment      *
  * Services 8.2 to resolve this problem.                        *
  ****************************************************************
  

Problem conclusion

• This issue has been resolved in IBM SPSS Collaboration and
  Deployment Services 8.2 release. Please upgrade to this version
  of our product to view the updated information regarding the
  migration of the C&DS repository housed within an Oracle
  database.
  

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

Applicable component levels