We have just migrated from DI to BODS 3.2 (All Oracle 11.2 DB’s): Export ATL from DI local repo. and import to new BODS local repo.
A number of Table Comparisons are no longer working. We get a unique constraint violation as the Table Comparison is doing an insert instead of an update so not comparing the keys properly.
The input keys are correct, however the input column type for one of the key columns is decimal 10,0 (Number on the DB) whereas on the target table it is varchar(10) (CHAR on the DB).
I’ve replicated the failing DF and cloned the tables and placed them in a new job no changes to the query transforms or to the table comparison apart from updating them to use the cloned tables. This new job works as expected!
Could the problem with the original job be related to an incorrect migration or a difference on how BODS handles this scenario v’s DI?
I can’t seem to find an answer for this particular problem, any help would be much appreciated.
Just to confirm, you migrated TO Data Services 3.2? That version is very far out of date and long out of support.
In any case, I have seen cases when migration causes corruption in a data flow. In those cases I would not replicate the data flow but instead recreate it again completely from scratch. However, if you replicated the data flow and it worked, it sounds like corruption did occur during the migration and you have already solved the problem?
Unfortunately the client doesn’t want to upgrade to BI 4 yet… long story…
It definitely looks like that the original migration wasn’t done properly.
The problem I have now is that we cannot find the DI install files to perform a correct migration.
I’ve looked around SAP’s website but unable to find the old DI software.
Do you know where I can get the install files from?
That you can at least still buy. There are copies of Windows 2003 Server on Amazon for purchase. I think folks even still sell older versions of XP Server.
I’ve ran some tests after doing the proper migration of the repository and have hit the same issue.
I’m getting a Unique constraint violation of the PK.
This seems to be only happening when the source key column is defined as decimal (NUMBER on the DB) and the target key column is varchar (CHAR on the DB).
The table definition in BODS metadata was showing VARCHAR(40), the table is CHAR(10). When I reimport the table the metadata now shows VARCHAR(10) and the dataflow/table comparison is now working.
Do you know if this behavior is due to the differences between DI and BODS or could it be related to a different version of Oracle (previous version was 11.1.0.7 and the new version is 11.2.0.4)?
I doubt you are going to find anyone who can answer that for you at this point. DS 3.2 has been out of support for over 4 years, I think I last worked on it in 2011. If you have a working solution, I would be happy and run with it. =)
There was a table import bug in DS 3.x where it would multiple the length of the field by a factor of 2 or 3, as I recall. It could have been 4. It’s been a very long time since I ran into that. I posted a fairly lengthy message here on the forum about this at the time. Good luck finding it.
The problem is that the import is thinking that a varchar2 column needed twice (or three/four) times the number of bytes to hold the data because of how the column was defined in Oracle. NVARCHAR2 vs VARCHAR2 as well as if the column was declared VARCHAR2(10 BYTES) vs VARCHAR2(10 CHAR).
This wasn’t really an Oracle problem. It was a DS problem.
(BOB member since 2005-09-16)
(BOB member since 2007-01-10)