Oracle Enterprise Stage silently dropping records.

[alow]

Hi All,

I have recently discovered an issue with a simple parallel job I am working on.

The job design is as follows;

Oracle Enterprise --> Parallel Transformer --> Oracle Enterprise (Write Method = Load, Write Mode = Truncate)


My source Oracle Enterprise stage is sourcing several million records. Of these records, I noticed that two records were not being loaded to the output Oracle table, even though they meet all the required criteria to be loaded (i.e. don't violate any constraints or business rules). Upon further inspection I found that for the two records that did not load, they both contained non ascii chars in one of the VarChar columns. It appears that the output Oracle Enterprise stage is rejecting them quietly (ie. not writing any warnings to the job log) when using the Write Mode setting = "Load".

To test why the job was behaving this way I tried the following;

TEST 1:
*******

I made a copy of the original job and changed the output Oracle Enterprise stage to use a Write Method = "Upsert" and Write Mode = "Insert then Update". When I run this version of the job I receive the fatal error "Unable to insert a record into the table".

TEST 2:
*******

I re-created this job as a server job (OCI --> Transformer --> OCI). This job runs fine (no warnings or fatal errors) and loads the records in question to the target table.

QUESTION 1:
***********

Has anyone else noticed this sort of behaviour when using the Oracle Enterprise stage? From what I can gather, it may not be an Oracle Enterprise stage (when Write Method = "Load") problem as such, but more a SQL Loader problem? I'm guessing that when the Oracle Enterprise stage executes the SQL Loader to load the records, SQL Loader fails but does not return an error code back to DataStage?

QUESTION 2:
***********

I have read several posts about handling special chars on dsxchange. It appears you can remove special chars from a string using DataStage functions as long as you know what special characters you are looking for. I have resolved this problem by writing a C++ function to remove all non ascii chars from a string, and then created a parallel routine to call this C++ object. Is this the recommended approach to this sort of generic string problem or have people resolved this issue using the core DataStage functionality?

[abhijain]

Did you tried inserting the records from SQL command prompt. Check whether Oracle is loading these 2 records properly or not.

Based on your explaination, it seems there is no problem with Oracle, as you are able to load the record in Server job. But, do check by inserting the record using Insert statement on Oracle.

[munch9]

Hi,

SQLLDR by default allows 50 errors.

try adding 'ERROR=<value>' to APT_ORACLE_LOAD_OPTIONS

[priyadarshikunal]

Hi,

SQLLDR by default allows 50 errors.

try adding 'ERROR=<value>' to APT_ORACLE_LOAD_OPTIONS

Yes, SQLLDR allows 50 errors but even though there are less errors you will see a fatal error message at the end. I don't know why is causing a problem in your case.

Can you try setting load option DIRECT=FALSE, PARALLEL=TRUE and let us know the results?

[pavan_test]

Talk to your unix admin. This can also happen if your network has switch port issues.

[alow]

In our scratch directory, SQL Loader is producing .bad files which contain the records in question, so I would have expected that DataStage would at least produce a warning regardless of how many records are recorded as errors?

I know that these records can be inserted into the target Oracle table because I have done so using different load methods (server job, from the command line etc).

priyadarshikunal, I assume the "DIRECT" and "PARALLEL" options you are talking about would be found in the produced SQL Loader .ctl file? Is there somewhere in the Oracle Enterprise stage where I can force these values?


I probably should have posted my second question as a seperate post, but has anyone else handled non ascii chars using the core DataStage application, or is a C++ function (Linked to a parallel routine) the way to go?

[priyadarshikunal]

In our scratch directory, SQL Loader is producing .bad files which contain the records in question. I know that these records can be inserted into the target Oracle table because I have done so using different load methods (server job, from the command line etc).

What does the log file says about the records being rejected? why loading from datastage EE jobs made them reject? there has to be a logical reason behind the rejection.

DIRECT=FALSE, PARALLEL=TRUE is a value for APT_ORACLE_LOAD_OPTIONS environment variable. which forces not to use the direct load method and use the general SQLLDR utility.

You can still try to force it to raise an error whenever the first record is rejected. but first try this option and let us know the results.

[chulett]

Make sure you understand that setting DIRECT=FALSE means you are no longer doing a bulk load but rather conventional inserts. This may or may not be acceptable to you.

[alow]

Should have also mentioned that I don't have any other options set in my .ctl file (apart from the last test run where I had OPTIONS(DIRECT=FALSE, PARALLEL=TRUE)...

For eg.

Code: Select all

OPTIONS(DIRECT=FALSE, PARALLEL=TRUE)
LOAD DATA  length semantics byte  INFILE 'ora.23244.637488.fifo.0' "FIX 9463"
APPEND INTO TABLE ...

[alow]

I have received a response from IBM support in regards to this issue.

After more investigation at my end, I have discovered that the issue is occuring in jobs when using a target Oracle Enterprise stage with the "Index Mode" setting. When we do not use this setting (ie. rebuild indexes after the load), the jobs behave the way I would expect them to (they abort when a .bad file exists).

This issue appears to be a bug, and IBM support have provided us with a patch. We are currently using DataStage 7.5.3.

If anyone else has a similar problem, our IBM case number was 81112215616.

For anyone looking for info on the parallel rountine I wrote to strip non ascii chars from a string, please look here (viewtopic.php?p=357664