I had some new issues while migrating a customer environment from IBM Connections 5.0 to 5.5 (Oracle) and I would like to document them here:
ORA-01722: invalid number during Homepage upgrade
While running the script “homepage/oracle/upgrade-50CR4-55.sql” we got the following errors in the log file:
|
1 2 3 4 5 |
DECLARE * ERROR at line 1: ORA-01722: invalid number ORA-06512: at line 17 |
The reason for that was, that the sequence of the columns in the table “HOMPAGE.NR_DISCOVERY_VIEW” was different from the sequence of the columns when you create that table from scratch. I guess it was because this table has been upgraded already many times (since IBM Connections 3.0). We had another (test) environment where the sequence of the columns were correct (but the database of that environment has been created from scratch somewhere in Connections 4.5 or so).
The SQL script does read the content of a database row into an array and then uses this array to insert the data to another table. While inserting the data via an array, the target table needs to have exactly the same order of the columns in order to insert the values into the right column.
To solve the issue I changed the following statement from
|
1 2 3 4 5 6 7 |
DECLARE CURSOR s_cur IS SELECT CATEGORY_READER_ID, READER_ID, 23 CATEGORY_TYPE, SOURCE, CONTAINER_ID, ITEM_ID, ROLLUP_ENTRY_ID, RESOURCE_TYPE, CREATION_DATE, STORY_ID, SOURCE_TYPE, USE_IN_ROLLUP, IS_NETWORK, IS_FOLLOWER, EVENT_TIME, IS_STORY_COMM, IS_BROADCAST, ORGANIZATION_ID, ACTOR_UUID, ROLLUP_AUTHOR_ID, IS_VISIBLE, COMMUNITY_ID FROM HOMEPAGE.NR_DISCOVERY_VIEW; |
to
|
1 2 3 4 5 6 7 |
DECLARE CURSOR s_cur IS SELECT CATEGORY_READER_ID, READER_ID, CONTAINER_ID, ITEM_ID, RESOURCE_TYPE, CATEGORY_TYPE, CREATION_DATE, SOURCE, STORY_ID, SOURCE_TYPE, ROLLUP_ENTRY_ID, USE_IN_ROLLUP, IS_NETWORK, IS_FOLLOWER, EVENT_TIME, IS_STORY_COMM, IS_BROADCAST, ORGANIZATION_ID, ACTOR_UUID, IS_VISIBLE, ROLLUP_AUTHOR_ID, COMMUNITY_ID FROM HOMEPAGE.NR_DISCOVERY_VIEW; |
which then solved the issue.
No migrated entries in “Discover” view of the Homepage
After the migration, the “Discover” view on the Homepage was empty. Only new entries appeared but all entries from before the migration were not displayed. I solved that by changing the same SELECT statement mentioned above from
|
1 2 3 4 |
... SELECT ..., 23 CATEGORY_TYPE, ... FROM HOMEPAGE.NR_DISCOVERY_VIEW; |
to
|
1 2 3 4 |
... SELECT ..., CATEGORY_TYPE, ... FROM HOMEPAGE.NR_DISCOVERY_VIEW; |
The old statement writes the value “23” to that column in the target table, regardless of the original value. The original value of all entries in our case was “17”. So we just removed the “23” in front of the column name so that the correct values were written to the target table. Afterwards the “Discover” view has been populated correctly with the values from before the migration.
Additional Homepage java migration necessary for 5.0 to 5.5
We also realized that a Java migration for Homepage is not only necessary while migrating from 4.5 to 5.0 but also while migrating from 5.0 to 5.5. This is not currently mentioned in the documentation. There is one hint here, but the description in this chapter is currently not correct and should not be used that way. The correct way to do the Java migration from 5.0 to 5.5 for Homepage including the missing JAR file can be found in that technote.
Be aware of the fact that the parameter for JDBC URL, DB User and DB password do not have a prefix like “-dburl / -dbuser / -dbpassword” but just written without that. This is different from the Java migration for version 4.5 to 5.0. And also make sure you got the newest version of that JAR file in the technote. It has been updated January 11th, 2017 with a new version (fixed bugs for Oracle).
Wrong documentation which scripts should be run for Homepage migration
The documentation has currently a wrong description which SQL scripts you should run for upgrading Homepage if your DB schema version is “479”. The documentation currently says:
|
1 2 3 4 5 6 7 8 9 |
If schema version is "479", or , then enter the following commands: @upgrade-45-45CR3.sql @upgrade-45CR3-45CR4.sql @upgrade-45CR4-50.sql @upgrade-50-50CR1.sql @upgrade-50CR1-50CR2.sql @upgrade-50CR2-50CR3.sql @upgrade-50CR3-55.sql @appGrants.sql |
but it should read:
|
1 2 3 |
If schema version is "479", or , then enter the following commands: @upgrade-50CR4-55.sql @appGrants.sql |
I requested to update the documentation with all the issues found, so hopefully in future the documentation will be correct again.