Migrating Trading Networks data between v6.5 and v8 on different servers

The client I’m currently working for requires a new webMethods 8 environment, but this has to be a copy of an existing 6.5 environment, which should stay. They created new servers so I could install all required components on this new machine for a version 8 with Trading Networks.

They required a clean install, but needed to have the code and TN data from the version 6.5 on the new version 8 installation. The installation itself was easy, just as the setup of the environments (e.g. ports, security, LDAP, …)

When I came to the point where I wanted to migrate the Trading Networks data, I came up with some issues. The biggest problem was that we have a current table wm65tn which holds all the data and we had to create a new one wmTN for the version 8, as they still want to use the version 6.5.

The first thing I tried was to deploy the Trading networks settings. This was already an issue as the old environment was behind a firewall and this needed to be opened for traffic towards the new version 8 environment. Once the firewall was opened I could finally deploy all TN settings, but this wouldn’t work.

WebMethods Deployer won’t deploy TN data between different versions (6.5 and 8).

Next I tried to Export and Import all data into the new version. Again the same issue appeared, Not allowed to copy data between 6.5 and 8.

My next option was to install a second database version 6.5, copy all the data there and then run a migration for that table.

So with this last step in mind I started. First bumble on the road, the disk where all tables was stored didn’t have enough free space. After creating the required space I could create the database. (wmTN) After the database was created. I disconnected the old version 6.5 table and copied the MDF and LDF file (SQL server).

Now we need to disconnect the wmTN table and rename the log files to the required log file names which are linked with this table.

Now the new database has the same data as the old one. So we can connect the tables again to the SQL server.

Next step is to upgrade the database according to the webmethods documentation.

Unfortunatly there was a step which mentioned “if errors occured, contact Software AG Customer Care” and yes lucky me, I can contact software AG.

For getting this error :

[wm-cjdbc40-0034][SQLServer JDBC Driver][SQLServer]Foreign key 'fk_PtnrUser_PtnrID' references invalid table 'Partner'.

MIGRATE TRADINGNETWORKS [31] FAILED

After some mails and telephone calls with webMethods support the issue is still not resolved.

They managed to solve the foreign key issue by just removing this, but when that issue is solved we got this issue.

*** Data Migration from TN 6.5 to TN 7.1 started...

* Creating Partner-User mappings...

java.sql.SQLException: [wm-cjdbc36-0007][SQLServer JDBC Driver][SQLServer]Invali d object name 'PartnerUser'.

        at com.wm.dd.jdbc.base.BaseExceptions.createException(Unknown Source)

        at com.wm.dd.jdbc.base.BaseExceptions.getException(Unknown Source)

        at com.wm.dd.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Sou

rce)

        at com.wm.dd.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Sou

rce)

        at com.wm.dd.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown

Source)

        at com.wm.dd.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)

        at com.wm.dd.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unk

nown Source)

        at com.wm.dd.jdbc.base.BaseStatement.commonTransitionToState(Unknown Sou

rce)

        at com.wm.dd.jdbc.base.BaseStatement.postImplExecute(Unknown Source)

        at com.wm.dd.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Sou

rce)

        at com.wm.dd.jdbc.base.BaseStatement.commonExecute(Unknown Source)

        at com.wm.dd.jdbc.base.BaseStatement.executeUpdateInternal(Unknown Sourc

e)

        at com.wm.dd.jdbc.base.BasePreparedStatement.executeUpdate(Unknown Sourc

e)

        at com.wm.app.tn.util.migrate.migratedata_to_tn_71.createPartnerUsers(mi

gratedata_to_tn_71.java:137)

        at com.wm.app.tn.util.migrate.migratedata_to_tn_71.main(migratedata_to_t

n_71.java:47)

This issue is related to the partner table (which holds all the partner profiles). The first thought was that it was related to the unknown profile because it has an id of 00000000000000. But the contact person of SAG confirmed that this wasn’t the issue as this is the default entry that comes with every TN database.

At this moment the ticket is already open for more than one week and still no solution found.

Today I received another call from SAG and they said to have found the issue. I recovered the original 6.5 TN database. So I could follow the correct guidelines from SAG without touching anything to the table’s structure (removing foreign keys).

The problem was that the original 6.5 database was created with a user specific for this table and the table had a schema defined.

Solution:

I had to create a user which had a default schema equal to wm65tn (which was the old value) and then run the upgrade script via the command line.

dbConfigurator.bat -a migrate -d sqlserver -c TradingNetworks -v latest -l <db_server_URL> -u <existing_db_user> -p <password> -fv 10

 Now the table was upgraded to version 8 and all new tables have the correct schema (wm65tn).

Now run this command migratedata_from_tn_7-1 6.5 from the correct folder as this copies the data from the old table to the correct new table. (all partner profiles, agreements, …)

E.g : Partner to PartnerUser

Now a restart of the environment is needed, unless your environment was already down, and the data is available in TN version 8.

Author : Jeroen W.