Migration of blob data
Aix 7.1 Informix v12.10fc7 Oracle 12c We are in the process of moving our PeopleSoft HR from Informix to Oracle .. not my choice...lost that battle .. anyway, currently in informix one of the tables contains an employee which is a byte column. The migration of that data to oracle with the PS Data mover script works successfully, and the photo ends up in the oracle DB as a blob. So,here is the question, we still have a database outside of the HR database that needs those pictures. Prior to the move this was handled by a synonym and all worked great. Now we have to change that to a table and get the data moved from Oracle back to Informix. The plan was to use Data Stage. I'm in no way an expert on DS, and those who are tell me that even though DS claims it can be done...well it can't... So that was a long winded way to ask if anyone had any suggestions as to how to get the data back over to Informix without a ton of effort. Incidentally we did try changing the informix side to also be a blob..but DS still won't do it ... thanks for any suggestions .... Peter Logan Database Administrator SpartanNash INC. Office: 616/878-8309 Mobile: 616/304-9672 E-Mail: peter.logan@spartannash.com
Informix Clients accessing heterogeneous data sources - Informix Enterprise Gateway Manager with ODBC or DRDA. http://www.ibm.com/developerworks/data/library/techarticle/dm-0810nadagi/ Oracle servers accessing an Informix database - Database Gateway for Informix + a view http://docs.oracle.com/cd/B28359_01/gateways.111/b31047/features.htm http://docs.oracle.com/cd/B28359_01/nav/portal_15.htm For an Informix server doing an equivalent of a synonym to an Oracle database....contact IBM! Regards, David. > On 11 August 2016 at 14:13 "Peter.Logan@spartannash.com" > <Peter.Logan@spartannash.com> wrote: > > > Aix 7.1 > Informix v12.10fc7 > Oracle 12c > > We are in the process of moving our PeopleSoft HR from Informix to Oracle > ... not my choice...lost that battle .. anyway, currently in informix one > of the tables contains an employee which is a byte column. The migration > of that data to oracle with the PS Data mover script works successfully, > and the photo ends up in the oracle DB as a blob. > > So,here is the question, we still have a database outside of the HR > database that needs those pictures. Prior to the move this was handled by > a synonym and all worked great. Now we have to change that to a table and > get the data moved from Oracle back to Informix. The plan was to use Data > Stage. I'm in no way an expert on DS, and those who are tell me that even > though DS claims it can be done...well it can't... So that was a long > winded way to ask if anyone had any suggestions as to how to get the data > back over to Informix without a ton of effort. Incidentally we did try > changing the informix side to also be a blob..but DS still won't do it ... > > thanks for any suggestions .... > > Peter Logan > Database Administrator > SpartanNash INC. > Office: 616/878-8309 > Mobile: 616/304-9672 > E-Mail: peter.logan@spartannash.com > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. >
It would help to know which version of DataStage you're using, which stage you tried and what errors you got (or if the stages simply don't support LOB data). You can offcours export the BLOBs into files and load them using simple SQL.... Hard to say the sort of performance you'd get... but again, what is the worload? How many BLOBS and what size are we discussing here? Also not sure if CDC (or CDD since you already have DataStage) would help. Regards. On Thu, Aug 11, 2016 at 2:13 PM, Peter.Logan@spartannash.com < Peter.Logan@spartannash.com> wrote: > Aix 7.1 > Informix v12.10fc7 > Oracle 12c > > We are in the process of moving our PeopleSoft HR from Informix to Oracle > ... not my choice...lost that battle .. anyway, currently in informix one > of the tables contains an employee which is a byte column. The migration > of that data to oracle with the PS Data mover script works successfully, > and the photo ends up in the oracle DB as a blob. > > So,here is the question, we still have a database outside of the HR > database that needs those pictures. Prior to the move this was handled by > a synonym and all worked great. Now we have to change that to a table and > get the data moved from Oracle back to Informix. The plan was to use Data > Stage. I'm in no way an expert on DS, and those who are tell me that even > though DS claims it can be done...well it can't... So that was a long > winded way to ask if anyone had any suggestions as to how to get the data > back over to Informix without a ton of effort. Incidentally we did try > changing the informix side to also be a blob..but DS still won't do it ... > > thanks for any suggestions .... > > Peter Logan > Database Administrator > SpartanNash INC. > Office: 616/878-8309 > Mobile: 616/304-9672 > E-Mail: peter.logan@spartannash.com > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f37cc81fd090539cca7c5
A quick lookup on the documentation for version 11.5 suggests using the Oracle connector for reading and ODBC (informix) fro writing could be a way... On Thu, Aug 11, 2016 at 3:34 PM, Fernando Nunes <domusonline@gmail.com> wrote: > It would help to know which version of DataStage you're using, which stage > you tried and what errors you got (or if the stages simply don't support > LOB data). > You can offcours export the BLOBs into files and load them using simple > SQL.... Hard to say the sort of performance you'd get... but again, what is > the worload? How many BLOBS and what size are we discussing here? > Also not sure if CDC (or CDD since you already have DataStage) would help. > > Regards. > > On Thu, Aug 11, 2016 at 2:13 PM, Peter.Logan@spartannash.com < > Peter.Logan@spartannash.com> wrote: > > > Aix 7.1 > > Informix v12.10fc7 > > Oracle 12c > > > > We are in the process of moving our PeopleSoft HR from Informix to Oracle > > ... not my choice...lost that battle .. anyway, currently in informix one > > of the tables contains an employee which is a byte column. The migration > > of that data to oracle with the PS Data mover script works successfully, > > and the photo ends up in the oracle DB as a blob. > > > > So,here is the question, we still have a database outside of the HR > > database that needs those pictures. Prior to the move this was handled by > > a synonym and all worked great. Now we have to change that to a table and > > get the data moved from Oracle back to Informix. The plan was to use Data > > Stage. I'm in no way an expert on DS, and those who are tell me that even > > though DS claims it can be done...well it can't... So that was a long > > winded way to ask if anyone had any suggestions as to how to get the data > > back over to Informix without a ton of effort. Incidentally we did try > > changing the informix side to also be a blob..but DS still won't do it > ... > > > > thanks for any suggestions .... > > > > Peter Logan > > Database Administrator > > SpartanNash INC. > > Office: 616/878-8309 > > Mobile: 616/304-9672 > > E-Mail: peter.logan@spartannash.com > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a113f37cc81fd090539cca7c5 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f37cc167c860539ccdc61
Version 9 of DS ... Seems that the informix data type isn't supported ..they have tried all of the informix related stages ... Peter Logan Database Administrator SpartanNash INC. Office: 616/878-8309 Mobile: 616/304-9672 E-Mail: peter.logan@spartannash.com From: "Fernando Nunes" <domusonline@gmail.com> To: ids@iiug.org Date: 08/11/2016 10:49 AM Subject: Re: Migration of blob data [37575] Sent by: ids-bounces@iiug.org A quick lookup on the documentation for version 11.5 suggests using the Oracle connector for reading and ODBC (informix) fro writing could be a way... On Thu, Aug 11, 2016 at 3:34 PM, Fernando Nunes <domusonline@gmail.com> wrote: > It would help to know which version of DataStage you're using, which stage > you tried and what errors you got (or if the stages simply don't support > LOB data). > You can offcours export the BLOBs into files and load them using simple > SQL.... Hard to say the sort of performance you'd get... but again, what is > the worload? How many BLOBS and what size are we discussing here? > Also not sure if CDC (or CDD since you already have DataStage) would help. > > Regards. > > On Thu, Aug 11, 2016 at 2:13 PM, Peter.Logan@spartannash.com < > Peter.Logan@spartannash.com> wrote: > > > Aix 7.1 > > Informix v12.10fc7 > > Oracle 12c > > > > We are in the process of moving our PeopleSoft HR from Informix to Oracle > > ... not my choice...lost that battle .. anyway, currently in informix one > > of the tables contains an employee which is a byte column. The migration > > of that data to oracle with the PS Data mover script works successfully, > > and the photo ends up in the oracle DB as a blob. > > > > So,here is the question, we still have a database outside of the HR > > database that needs those pictures. Prior to the move this was handled by > > a synonym and all worked great. Now we have to change that to a table and > > get the data moved from Oracle back to Informix. The plan was to use Data > > Stage. I'm in no way an expert on DS, and those who are tell me that even > > though DS claims it can be done...well it can't... So that was a long > > winded way to ask if anyone had any suggestions as to how to get the data > > back over to Informix without a ton of effort. Incidentally we did try > > changing the informix side to also be a blob..but DS still won't do it > ... > > > > thanks for any suggestions .... > > > > Peter Logan > > Database Administrator > > SpartanNash INC. > > Office: 616/878-8309 > > Mobile: 616/304-9672 > > E-Mail: peter.logan@spartannash.com > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a113f37cc81fd090539cca7c5 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f37cc167c860539ccdc61 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.