Hi,
1)I am loading a file which contains 50 million of records in to target table. If i use job design it will take atleast 1 or 2 days to load. I have written a unix script and loading data through load utility from sequential file to target table. it is getting hanged because if there is any null values in the records. Is there any way to handle null or change it to blank before using load utility.
2) In unix, how can we find is there any null values in file(Command).
Handling Null in Load Utility
Moderators: chulett, rschirm, roy
-
dilip.datastage
- Participant
- Posts: 22
- Joined: Wed Aug 15, 2007 10:59 pm
- Location: Bangalore
-
rleishman
- Premium Member
- Posts: 252
- Joined: Mon Sep 19, 2005 10:28 pm
- Location: Melbourne, Australia
- Contact:
Datastage should not be that much slower than native RDBMS tools. What database are you using?
If you were using Oracle (for example) you could load using SQL*Loader, directing it to trim trailing spaces. Since Oracle does not support the ANSI empty-string-different-to-null concept, the resultant empty strings are loaded as NULLs. If you used Datastage, I don't know if there is an automated way to trim trailing spaces; you may need to add a transformer and perform an expression on each column to trim spaces.
As for finding null columns in Unix, what type of file is it? CSV? Fixed length records?
Unix does not have much in the way of native file parsing, you are much better off using a programming language. My favourite is Perl because it is so fast to develop complex scripts. Perl has some nice downloadable packages like Text::CSV_XS and Parse::FixedLength. But I don't see these as being any more functional or efficient than DS for a simple file with one record format.
Before you give up on DS, find out why you are not getting the performance you expect. Make sure you are NOT using the ODBC stage. Whichever database you are using, make sure you are using a stage that is compatible with that DBs bulk loader (eg. OCI Load stage for Oracle). Make sure that your table is ready for a large bulk load: drop all indexes, triggers and constraints.
If you need help getting the load to perform quickly, post your job design , current performance, and your performance expectations here and see if we can help.
If you were using Oracle (for example) you could load using SQL*Loader, directing it to trim trailing spaces. Since Oracle does not support the ANSI empty-string-different-to-null concept, the resultant empty strings are loaded as NULLs. If you used Datastage, I don't know if there is an automated way to trim trailing spaces; you may need to add a transformer and perform an expression on each column to trim spaces.
As for finding null columns in Unix, what type of file is it? CSV? Fixed length records?
Unix does not have much in the way of native file parsing, you are much better off using a programming language. My favourite is Perl because it is so fast to develop complex scripts. Perl has some nice downloadable packages like Text::CSV_XS and Parse::FixedLength. But I don't see these as being any more functional or efficient than DS for a simple file with one record format.
Before you give up on DS, find out why you are not getting the performance you expect. Make sure you are NOT using the ODBC stage. Whichever database you are using, make sure you are using a stage that is compatible with that DBs bulk loader (eg. OCI Load stage for Oracle). Make sure that your table is ready for a large bulk load: drop all indexes, triggers and constraints.
If you need help getting the load to perform quickly, post your job design , current performance, and your performance expectations here and see if we can help.
Ross Leishman
-
dilip.datastage
- Participant
- Posts: 22
- Joined: Wed Aug 15, 2007 10:59 pm
- Location: Bangalore
Hi
I am using DB2 database. In the JOb i am using ODBC stages to trigger Stored Procedure.