First off, I want to post that I am a developer and not a DBA, so I am trying to approach this from the standpoint of what is the best thing to do, rather than just assuming I can do everything efficiently "in code" without any issues.

Ok, now to the challenge. I am going to be updating data in two different DBMS's. The one is DB2 for i on our iSeries systems. The other is DB2 on AIX. I know there are some differences between those versions of DB2. Not sure if I will hit those differences or not. We have some data that could be considered "lookup data" (ie, data that could maybe populate a drop down box, etc. In other words, a user or program looks up the data and uses it, rather than this being "input" data from a business user). This lookup data is maintained by a particular department in our company. We are writing a new system to replace our legacy system. The legacy system resides on System i. The new system resides as a Java EE app on AIX. Until the new system completely replaces the legacy system we want to push this lookup data from its legacy format in our legacy tables, to its newer format in new tables for the new system (this will also help us with data cleansing too).

I am wondering what the best way to go after this data, transform it, and load it? We have purchased an ETL tool (IBM InfoSphere DataStage), but may not use it yet, because we have no one trained on the tool and we have never used an ETL tool before. We have always been a shop with developers who wore multiple "hats". And recently, we are trying to make the transition to having DBA's and doing things the proper way.

So I am responsible for coming up with a design, and since this only is applicable until we move away from our legacy system, the feeling I am getting from management is they want a custom code program(s) to take care of this ETL (at least until we get better at DataStage).

As a developer, I would have normally tried do a read of the data sorted a certain way, and then cycle through all my records one by one and transform them and then either write each record one at a time, or maybe "write off" the updates to a file that can do a mass insert at the end.

Being a developer, I have no idea if this is really the right/smart way to do it? Any thoughts? The data in the table I am extracting from will be between 40 and 60 thousand records, so nothing huge, but that is still a lot to process individually within a program when I know SQL and other DB tools and strategies can better handle things.

Any thoughts or suggestions? I appreciate any made.

I also started a similar topic that I labelled "What are the best practices for ETL of data from one OLTP system to another Part 2?"

First off, I want to post that I am a developer and not a DBA, so I am trying to approach this from the standpoint of what is the best thing to do, rather than just assuming I can do everything efficiently "in code" without any issues.

Ok, now to the challenge. I am going to be updating data in two different DBMS's. The one is DB2 for i on our iSeries systems. The other is DB2 on AIX. I know there are some differences between those versions of DB2. Not sure if I will hit those differences or not. We have some data that could be considered "lookup data" (ie, data that could maybe populate a drop down box, etc. In other words, a user or program looks up the data and uses it, rather than this being "input" data from a business user). This lookup data is maintained by a particular department in our company. We are writing a new system to replace our legacy system. The legacy system resides on System i. The new system resides as a Java EE app on AIX. Until the new system completely replaces the legacy system we want to push this lookup data from its legacy format in our legacy tables, to its newer format in new tables for the new system (this will also help us with data cleansing too).

I am wondering what the best way to go after this data, transform it, and load it? We have purchased an ETL tool (IBM InfoSphere DataStage), but may not use it yet, because we have no one trained on the tool and we have never used an ETL tool before. We have always been a shop with developers who wore multiple "hats". And recently, we are trying to make the transition to having DBA's and doing things the proper way.

So I am responsible for coming up with a design, and since this only is applicable until we move away from our legacy system, the feeling I am getting from management is they want a custom code program(s) to take care of this ETL (at least until we get better at DataStage).

As a developer, I would have normally tried do a read of the data sorted a certain way, and then cycle through all my records one by one and transform them and then either write each record one at a time, or maybe "write off" the updates to a file that can do a mass insert at the end.

Being a developer, I have no idea if this is really the right/smart way to do it? Any thoughts? The data in the table I am extracting from will be between 40 and 60 thousand records, so nothing huge, but that is still a lot to process individually within a program when I know SQL and other DB tools and strategies can better handle things.

Any thoughts or suggestions? I appreciate any made.

I also started a similar topic that I labelled "What are the best practices for ETL of data from one OLTP system to another Part 2?"

First off, I want to post that I am a developer and not a DBA, so I am trying to approach this from the standpoint of what is the best thing to do, rather than just assuming I can do everything efficiently "in code" without any issues.

Ok, now to the challenge. I am going to be updating data in two different DBMS's. The one is DB2 for i on our iSeries systems. The other is DB2 on AIX. I know there are some differences between those versions of DB2. Not sure if I will hit those differences or not. We have some data that could be considered "lookup data" (ie, data that could maybe populate a drop down box, etc. In other words, a user or program looks up the data and uses it, rather than this being "input" data from a business user). This lookup data is maintained by a particular department in our company. We are writing a new system to replace our legacy system. The legacy system resides on System i. The new system resides as a Java EE app on AIX. Until the new system completely replaces the legacy system we want to push this lookup data from its legacy format in our legacy tables, to its newer format in new tables for the new system (this will also help us with data cleansing too).

I am wondering what the best way to go after this data, transform it, and load it? We have purchased an ETL tool (IBM InfoSphere DataStage), but may not use it yet, because we have no one trained on the tool and we have never used an ETL tool before. We have always been a shop with developers who wore multiple "hats". And recently, we are trying to make the transition to having DBA's and doing things the proper way.

So I am responsible for coming up with a design, and since this only is applicable until we move away from our legacy system, the feeling I am getting from management is they want a custom code program(s) to take care of this ETL (at least until we get better at DataStage).

As a developer, I would have normally tried do a read of the data sorted a certain way, and then cycle through all my records one by one and transform them and then either write each record one at a time, or maybe "write off" the updates to a file that can do a mass insert at the end.

Being a developer, I have no idea if this is really the right/smart way to do it? Any thoughts? The data in the table I am extracting from will be between 40 and 60 thousand records, so nothing huge, but that is still a lot to process individually within a program when I know SQL and other DB tools and strategies can better handle things.

Any thoughts or suggestions? I appreciate any made.

First off, I want to post that I am a developer and not a DBA, so I am trying to approach this from the standpoint of what is the best thing to do, rather than just assuming I can do everything efficiently "in code" without any issues.

Ok, now to the challenge. I am going to be updating data in two different DBMS's. The one is DB2 for i on our iSeries systems. The other is DB2 on AIX. I know there are some differences between those versions of DB2. Not sure if I will hit those differences or not. We have some data that could be considered "lookup data" (ie, data that could maybe populate a drop down box, etc. In other words, a user or program looks up the data and uses it, rather than this being "input" data from a business user). This lookup data is maintained by a particular department in our company. We are writing a new system to replace our legacy system. The legacy system resides on System i. The new system resides as a Java EE app on AIX. Until the new system completely replaces the legacy system we want to push this lookup data from its legacy format in our legacy tables, to its newer format in new tables for the new system (this will also help us with data cleansing too).

I am wondering what the best way to go after this data, transform it, and load it? We have purchased an ETL tool (IBM InfoSphere DataStage), but may not use it yet, because we have no one trained on the tool and we have never used an ETL tool before. We have always been a shop with developers who wore multiple "hats". And recently, we are trying to make the transition to having DBA's and doing things the proper way.

So I am responsible for coming up with a design, and since this only is applicable until we move away from our legacy system, the feeling I am getting from management is they want a custom code program(s) to take care of this ETL (at least until we get better at DataStage).

As a developer, I would have normally tried do a read of the data sorted a certain way, and then cycle through all my records one by one and transform them and then either write each record one at a time, or maybe "write off" the updates to a file that can do a mass insert at the end.

Being a developer, I have no idea if this is really the right/smart way to do it? Any thoughts? The data in the table I am extracting from will be between 40 and 60 thousand records, so nothing huge, but that is still a lot to process individually within a program when I know SQL and other DB tools and strategies can better handle things.

Any thoughts or suggestions? I appreciate any made.

I also started a similar topic that I labelled "What are the best practices for ETL of data from one OLTP system to another Part 2?"