We have a problem in our hands out here.
We would be loading certain tables in Oracle Database from files coming from mainframes. Our job structure looks like:
Job 1:
Code: Select all
Infile -----Datastage-----> Staging Tables
!
!
V
Error Table
Code: Select all
Staging table -----Datastage-----> Tmp Tables
!
!
V
Error Table
Code: Select all
Write a Oracle merge and load the final table-wrapped in Unix shell
Now what error conditions we are tapping are like:
Invalid dates/Null in not null fields/Invalid Numbers/Non-numeric character in numeric fields
So the design of my Datastage job is:
Code: Select all
Infile -----Transformer-----> Staging Tables
!
!
V
Error Table
SO OUR PROBLEM:
- 1> We have tables with columns ranging from 20 to 200. Loading all of them in staging- and then sequentiually checking each row using the routines and transform would be very slow.
2> We would have load ranging from 5 to 30 million. Can Datastage server handle such a volume?
3> It would be nice if we can drop the invalid records before loading the staging table. Then work on those data with the routines to check and transform them to our required values (like Null to value / Invalid dates replaced by sysdate). Because we have seen if we are loading 20 million may be 10,000 records would be invalid.
Is there any other way? It would be great if someone can help