How many fields can DataStage handle on import? How about the overall length of the input record?
If there are hard limits set in DataStage, where are these documented? We have a file with 4300 fields and an overall record length of 32000 bytes that we are having a hard time importing. Are there any environment settings (like APT_* variables) that we need to tweak to accomodate such a large file format?
Any help would be most appreciated! Thanks!
Brad.
Overall limits of DataStage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
ODBC is limited to 400 columns and a row length of 16KB by default. These limits can be changed by setting MAXFETCHCOLS and MAXFETCHBUFF respectively in the uvodbc.config file.
There is no documented limit for the Sequential File import, other than the tedium of checking that each column definition has been guessed correctly!
There is no documented limit for the Sequential File import, other than the tedium of checking that each column definition has been guessed correctly!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
ODBC is limited to 400 columns and a row length of 16KB by default. These limits can be changed by setting MAXFETCHCOLS and MAXFETCHBUFF respectively in the uvodbc.config file.
There is no documented limit for the Sequential File import, other than the tedium of checking that each column definition has been guessed correctly!
There is no documented limit for the Sequential File import, other than the tedium of checking that each column definition has been guessed correctly!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
We will be using about 800 fields spread throughout the 4300, so I would imagine that we will be using the drop option in the input schema. That will allow us to define the whole schema, but not carry any fields that are not needed downstream.
In an ideal world, we would define each field so that later on, we can easily add dropped fields back in when the customer changes their mind. However, I guess if 4300 is too much we can always redefine sections as filler and reduce the number of fields.
Brad.
In an ideal world, we would define each field so that later on, we can easily add dropped fields back in when the customer changes their mind. However, I guess if 4300 is too much we can always redefine sections as filler and reduce the number of fields.
Brad.
What about splitting the file?
Hi all,
Similar to Klaus' suggesting of cutting out certain fields to work with:
Create multiple sequential files from the source putting only a hundred or so fields in each file along with the primary keys for those rows. Have one of the sequential files be the primary source for an insert to the target and then use the other files to update the target based on the keys. Alternatively, you write one job that brings all the source sequential files in at once, though I think that will drop you right back to your current problem of field count.
Bear in mind, this is just an off the wall theory and I have not actually done it this way. This is just a newbie trying to think outside the box. If this method makes those more experienced veterans grind their teeth, please counteract my message. I too have some large field count items that I'm looking for an efficient way of processing and your corrections to my thought processes would be greatly appreciated.
Bestest!
Similar to Klaus' suggesting of cutting out certain fields to work with:
Create multiple sequential files from the source putting only a hundred or so fields in each file along with the primary keys for those rows. Have one of the sequential files be the primary source for an insert to the target and then use the other files to update the target based on the keys. Alternatively, you write one job that brings all the source sequential files in at once, though I think that will drop you right back to your current problem of field count.
Bear in mind, this is just an off the wall theory and I have not actually done it this way. This is just a newbie trying to think outside the box. If this method makes those more experienced veterans grind their teeth, please counteract my message. I too have some large field count items that I'm looking for an efficient way of processing and your corrections to my thought processes would be greatly appreciated.
Bestest!
Bestest!
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"