Schema reconciliation issue

[ashik_punar]

Hi All,

I have job which runs perfectly fine in DEV/UAT environment( DataStage & DB). However, when the same job was moved to pre production environment i started getting the schema reconciliation issue. I checked there is no issue with the schema but still the issue is there.

I can change the job and set no failure on reconciliation issues. However, I would like to understand why it should be behave like this. I have asked my DBA to share the DB setup details for all 3 environments to check if there is some DB parameter difference.

Is anyone aware of such parameters in Oracle 11g which lead to this issue?

Please share your thoughts.

With Regards,
Punar

[chulett]

What exactly is "the schema reconciliation issue"? If you are getting error messages, please post them.

[ashik_punar]

Hi Craig,

Thanks for the reply.

Here is the error:

SRC_DB: Schema reconciliation detected a size mismatch for column CC_CD. When reading database column WCHAR(min=32,max=32) into column WVARCHAR(min=0,max=3), truncation, loss of precision or data corruption can occur. (CC_DBSchemaRules::reportSizeMismatch, file CC_DBSchemaRules.cpp, line 1685).

With this message the job fails in pre production DataStage project and DB whereas the same job in SIT/UAT project pointing to SIT/UAT DB is running fine without any changes.

I suspect there is some DB param which is generating this issue and have requested the DBAs to compare the 2 DBs. However, just thought of asking everyone here for some guidance.

Please help.

With Regards,
Punar

[ssnegi]

Please check the length definition between database and datastage. It seems like database is min32, max32 whereas datastage is min0,max3. Define datastage same as database field length.

[ashik_punar]

Yes. I know that and i can fix that. However, my question is the length in DB is same across all DBs then why the job didn't fail in other DBs and failing only in pre production DB?

[ssnegi]

you are on the correct track in comparing the datatype definition between the DBs. You might also want to compare the datatype definition in the datastage environments.

[ray.wurlod]

"Schema" here does not mean the database schema in which the table resides - it means the record structure. (There are historical reasons for this, with which I won't bore you.)

I would suggest that one of the columns (or more than one) is defined differently in your pre-production environment than in your other environments. You'll have to do the detective work to discover which it is. The error message should help you to find it (in this case it's the CC_CD column). Otherwise compare the DDL scripts.

It is possible to have Connector stage types throw a warning, rather than a fatal error, when schema reconciliation is not perfect.

[ashik_punar]

Hi Ray,

Thank you for the response.

I will start investigating in order to find the culprit column. Though the probability of having different definitions is very slim given the fact that we exported our UAT DB (RMAN backup) and imported it into an empty schema to create our pre production DB. So, ideally the 2 schemas should be exactly same.

I will share my findings.

With Regards,
Punar

[ashik_punar]

Hi All,

I did some analysis and found that the metadata in 2 DBs for this table is same and it matches with the one defined in job as well. Now, I have no idea what to do.

I have switched off the failure on schema reconciliation issues. But, still want to know the reason why this is happening when we move from one DB to another. My DBAs tell me that the DBs are exactly same no changes in param at all.

Please share your thoughts.

With Regards,
Punar

[ray.wurlod]

Don't necessarily believe the DBAs. They are telling you what they believe to be the case. Check. Dump the DDL and verify that they are the same - check not only data type but also size. Verify the record schema (table definition) that you have used in your job design.