Oracle Enterprise Stage silently dropping records.
Moderators: chulett, rschirm, roy
Oracle Enterprise Stage silently dropping records.
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?
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?
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.
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.
Rgrds,
Abhi
Abhi
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
munch9 wrote: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?
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
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?
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?
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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.alow wrote: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).
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.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
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.
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 ...
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
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