All necessary Product 360 - Core Database migration scripts are included in the Product 360 Core Database setup, see Server Database. Normally no additional scripts need to be executed.

Nonetheless there are special situations. These and their solutions are described in the following:

Duplicate Article Attribute Names in the repository default language

This problem only occurs, when migrating from PIM Version < 8.0.03 to PIM Version >= 8.0.03, and if the PIM database originally arises from a PIM Version <= 5.3.

Problem/ Situation

In PIM Versions 5.3 and older it was possible, to have duplicate attribute names in the repository default language for the same article in the MASTER and/or SUPPLIER database. In newer PIM versions, this situation does not occur anymore, since it is prevented through business logic. But if the PIM Database arises from an older PIM Version (<=5.3), their might still exist such duplicate attribute names.

With PIM Version 8.0.03 an unique index XAK2_ArticleAttribute on the new column nameInKeyLanguage together with the deletionTimestamp and the articleRevisionID is introduced in the PIM Database. The creation of this index fails, if duplicate attribute names in the repository language do exist (see above). This situation becomes noticeable through the following issue:

[for MSSQL] the Database Setup shows one of the following errors for update script PCM_MASTER_Upd_V3.0.5.63 or PCM_SUPPLIER_Upd_V3.0.5.63:

Msg 1505, Level 16, State 1, ...
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ArticleAttribute' and the index name 'XAK2_ArticleAttribute'. The duplicate key value is (attribute1, Dec 31 9999 12:00AM, 1).

Msg 50000, Level 18, State 44, ...
1 duplicate article attribute name(s) in the repository key language (languageID=9) is/are existing!

[for MSSQL] the Database Setup shows one of the following errors for script 'shared_PPM4_Rep_Copy':
- ```
Invalid column name 'Origin'.
```
- In this special situation it is neccessary to rollback to the PIM database backup you have made before you started the PIM Core Database Setup, then first of all execute the steps described under "Solution" below and after that proceed with the migration.
- This may also occur if the repository.default.language is another than in the old environment. In this case please see solution 2.
[for Oracle] the Database Setup shows the following error for update script PCM_MASTER_Upd_V3.0.5.68 or PCM_SUPPLIER_Upd_V3.0.5.68:
- ```
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
```
In order to proactively find and correct duplicate attribute names, please use the corresponding SQL script in the chapter "SQL scripts to find and correct duplicate attribute names" below.

Solution 1

The duplicate article attribute names in the repository language have to be corrected in the PIM Database.

Therefor execute the SQL script to find duplicate attribute names (see chapter "SQL scripts to find and correct duplicate attribute names" below) in the MASTER and SUPPLIER database in order to find all duplicate attribute names and the corresponding Item/Variant/Product. Save the results of this SQL script, so you are able to find those Items/Variants/Products later on.
Then execute the SQL script to correct duplicate attribute names (see chapter "SQL scripts to find and correct duplicate attribute names" below) in the MASTER and SUPPLIER database in order to automatically rename all these duplicate attribute names to '<attributeName> (<counter>)', e.g. 'Length (2)'
Re execute the Database Setup. It shall now run through without the above mentioned error.
Correct the attribute names (that were found in step 1 and renamed in step 2) to your needs through PIM Desktop, PIM Web or Rest API.

If you have problems with the SQL scripts, please contact the Informatica Global Customer Support.

Solution 2

That kind of issue is also shown if the repository.default.language inside the server.properties is not set correct.

For example: The old environment was running in de_DE and now there is en_US set.

Ensure that the repository.default language is still the same like in the old environment.

ArticleTrading entries without existing Party entry are removed

This problem only occurs when migrating to a PIM version >= 8.0.00.00.

Problem/Situation

Since PIM 8.0.00.00 an update script exists, which runs every time the Database Setup is executed and (soft-)deletes all ArticleTrading objects, where the corresponding Party is (soft-)deleted (=not existing).

This update script also deletes ArticleTrading objects which meet the following conditions:

The ArticleTrading logical key for ArticleTradingType.LK.PartyMS is defined with a not editable default value (aka "fixed value"), for example 1001 and in the database MAIN.Party table is no record for this ID

(This can actually only happen in case the repsitory has been misconfigured. to disable a Party logical key one should always use the ID for the Public party.)

Solution

A record in the MAIN.Party table needs to be created which has the ID which was used in the repository, BEFORE the database setup is executed! It's up to the Partner or Customer DBA to create this record with a simple Insert statement. Please make sure that the ID has the needed value from the repository (in our example: 1001)

This issue is also described in the Chapter "Domain Model (Repository)" of the Product 360 Development Guide, and here under "Logical Key".

Invalid decimal formatting in Article Attribute Values or Structure Group Attribute Values

This problem only occurs, when migrating from Product 360 Version prior to 5.1

Problem/ Situation

The attribute value is persisted as a string value in the database. In former Product 360 versions there was no datatype validation and no consistent format for decimal values. Values were persisted as they have been typed in. This means, that decimal values could have been entered (and persisted in the database) in different formats, e. g. "1,2" (with "," as decimal separator) or "1.000,23" (with "." as thousands separator) or 1,000.23 (with "," as thousands separator and "." as decimal separator).

In the current Product 360 Version, decimal values are persisted in a language-independent format, with "." as decimal separator and no thousands separator.

Having old values in the database and interpreting them with a current client leads to validation errors in the Product 360 Desktop Client and in worst cases to wrong interpretation of these values.

Solution

The correct solution for this problem depends strongly on the individual customer situation. Because of this it is important in the first step to analyze the article attribute values and structure group attribute values in the customers database and then decide, how to migrate the "invalid" values in the database with a sql script.

Here you can find an example sql script for the following situation:

there are no article attribute values and structure group attribute values with thousands separator (like "1.000,23")
in all attribute values with comma (like "1,2"), the comma can always be interpreted as decimal seperator

Find the migration sql scripts for this situation in the chapter "SQL scripts to check and migrate invalid decimal formatting in Article Attribute Values" below.

If you have problems in creating the correct migration sql scripts for your current customer situation, please contact the support department.

Structure Migration

First of all, please read the following knowledge base article, in order to understand the impacts of the new structure paradigm: Structure Types

All structures in the Product 360 System are in the first step migrated automatically through the Core Database setup in the following way:

Structure type before migration (id)	Structure type after migration (id)
Catalog structure (1)	Catalog structure (1)
Standard classification system (2)	Standard classification system (2)
User-defined structure (3)	Output structure (3)
Print structure (4)	Output structure (3)
-	Primary maintenance structure (4)
-	Secondary maintenance structure (5)

After this automatic migration, please refer to the structure migration steps described in chapter Product 360 Server and Desktop Migration.

While migrating to 8.0.03

The following database model changes have been made with the hotfix 8.0.03:

Table	Change description
<all tables of versionable root entities (e.g. `Article`, `Structure`, `StructureGroup`, ...)	The columns `AclID` and `AclFlag` have been moved from the root table (e.g. Article) to the respective revision table (e.g. ArticleRevision)
ArticleAttribute	The new column `NameInKeyLanguage` was added.
<all tables with `DeletionTimestamp` column>	For deleted objects, the `DeletionTimestamp` is now '`9999-12-31 00:00:00`' instead of `null`.
Catalog	The column `SupplierID` has been moved to the `CatalogRevision` table.

Qualification permission info entries in the log

This problem can only occur when migrating Product 360 Version < 8.0.05 to Product Version >= 8.0.05 and having GDSN deactivated

With Product 360 version 8.0.05 the "PublicationStatusEntry" entity will be deactivated if GDSN is not enabled in the application_modules.properties. If you have maintained any qualified field rights for a user group in a version previous 8.0.05 and you are not having GDSN activated, then you might have a similar entry in the log:

INFO   | jvm 1    | 2017/07/19 14:06:26 | 14:06:25,993 WARN  [Worker 2] [QualificationPermissionElementImpl] 
INFO   | jvm 1    | 2017/07/19 14:06:26 | No enum provider has been found for any logical key with the qualification permission identifier "gdsnMessageType".
INFO   | jvm 1    | 2017/07/19 14:06:26 | May be this identifier is not anymore used for any logical key in the repository
INFO   | jvm 1    | 2017/07/19 14:06:26 | or no enumeration is defined for the logical keys that use this identifier.
INFO   | jvm 1    | 2017/07/19 14:06:26 | The permissions for this identifier will be reset when the permissions are changed the next time.

You can check with following SQL statements which user groups are affected:

Main database - Oracle

SELECT * FROM "UserGroup" WHERE "QualiPermModel" LIKE '%identifier="gdsnMessageType"%';

Main database - MSSQL

SELECT * FROM [dbo].[UserGroup] WHERE [QualiPermModel] LIKE '%identifier="gdsnMessageType"%';

Solution:

There are two ways to solve this problem. The first one is by using the desktop client. As soon as you change any qualified field right for a structure group, the invalid entry "gdsnMessageType" will be removed. The second approach is to change the saved data directly on the database. Please use following SQL statements:

Main database - Oracle

UPDATE "UserGroup" 
SET "QualiPermModel" = REPLACE("QualiPermModel", '<element sys-id="1" sys-type="dp" sys-class="com.heiler.ppm.std.core.internal.permission.QualificationPermissionElementImpl" identifier="gdsnMessageType" key-class="java.lang.String"/>', '') 
WHERE ID IN ( SELECT ID FROM "UserGroup" WHERE "QualiPermModel" LIKE '%identifier="gdsnMessageType" key-class="java.lang.String"/>%');
COMMIT;

Main database - MSSQL

UPDATE [dbo].[UserGroup] 
SET [QualiPermModel] = CAST(REPLACE(CAST([QualiPermModel] AS VARCHAR(MAX)), '<element sys-id="1" sys-type="dp" sys-class="com.heiler.ppm.std.core.internal.permission.QualificationPermissionElementImpl" identifier="gdsnMessageType" key-class="java.lang.String"/>', '') AS NTEXT) 
WHERE ID IN (SELECT ID FROM [dbo].[UserGroup] WHERE [QualiPermModel] LIKE '%identifier="gdsnMessageType" key-class="java.lang.String"/>%');

Multiple Purposes for Import Mappings in the GenericData Table

Problem/ Situation

Import mappings are not supposed to have more than one purpose.
Starting with version 8.1.1.07 there have been improvements with which it is possible to sanitize the purposes column of import mappings as user.
You can have a look at database entries containing a flawed purposes value via following sql statement:

Main database - Oracle

SELECT "Purposes", "GenericData",*
FROM "GenericData" WHERE "Classifier" = 'ImportProfile' AND "Purposes" LIKE '%;%';

Main database - MSSQL

SELECT [Purposes], *
FROM [dbo].[GenericData] WHERE [Classifier] = 'ImportProfile' AND [Purposes] LIKE '%;%';

If there is more than one purpose in the 'Purposes' column e.g. "HSX;HSX;HSX" or "ManualImport;AutoInbox" this means that the purposes column has bad data.

Solution

Automatic Sanitization for multiple same purposes values

In case multiple purposes of an import mapping all purposes have the same value, that kind of bad data is sanitized automatically during server start.
Meaning a Purposes column with "HSX;HSX;HSX" becomes "HSX".

Manual Sanitization

In case of of multiple different purposes like "ManualImport;AutoInbox" there is no deterministic logic to find out the original desired purpose.
Therefor, this has to fix this manually which can be done in two ways:

Manual Sanitization - Desktop Client (recommended)

The first way to do this is done as a administrative action in the Desktop Client. This is now possible with the improvements and also the recommended way to do this.
In the Manage Import Mappings Dialog look for those import mappings that show "Several purposes" in the Purpose dropdown when selecting that mapping.
Then choose the desired purpose and click 'Apply'.
Note that selecting multiple mappings now disables the purposes dropdown as opposed to previous versions. This is to prevent further possible bad multi purpose values.

Manual Sanitization - Update Script

The second way is doing this by executing a SQL statement to explicitly set the single Purpose. This can already be done in all Product360 versions.
Example for setting Purposes to AutoInbox.

Main database - Oracle

UPDATE "GenericData"
SET "Purposes" = 'AutoInbox'
WHERE ID = <insert ID of desired import mapping in GenericData table>;
COMMIT;

Main database - MSSQL

UPDATE [dbo].[GenericData]
SET [Purposes] = 'AutoInbox'
WHERE ID = <insert ID of desired import mapping in GenericData table>;

Possible values for a purpose are

'HSX': for 'Supplier Portal' imports
'AutoInbox': for 'Hotfolder' imports
'ManualImport': for 'Manual' imports