5 Replies Latest reply: Jul 9, 2014 10:44 AM by rp0428 RSS

    Migrating db2 LOBs to Oracle using Offline Method

    user530848-Oracle

      We are using SQL developer for this activity. In SQL developer, we were using offline data migration method to unload/load data from DB2 to Oracle. As part of this when we unload the data from DB2, LOBs are getting created as separate files (in the db export command lobsinsepfiles option is used). If we use lobsinfile option instead of lobsinsepfiles withdb2 export command, all the LOBs get unloaded as a single file and data will be loaded as one file, so that we've only 2 files to move across database servers& easy to manage& migrate the data. In this case, how can we write controlfile for the sql loader to load data from single file.

       

      In our case, we have records more than 100 millions and it'll generate 100 million of LOB files. Will it be a good solution to load 100 million files. I hope Operating System will not allow those many number of files get created under one directory.

      Can you suggest us any other option for this.

        • 1. Re: Migrating db2 LOBs to Oracle using Offline Method
          rp0428

          I haven't use offline migration before. What is the process for that? Can you post the actual steps you are using?

          If we use lobsinfile option instead of lobsinsepfiles withdb2 export command, all the LOBs get unloaded as a single file and data will be loaded as one file,

          If all of the LOBs are in one file how do you know where the first one ends and the next one begins? There must be some metadata somewhere that has the lengths of each LOB or the LOBs must be prefixed with a length.

          so that we've only 2 files to move across database servers& easy to manage& migrate the data.

          What two files are those? You just said all LOBs are in one file.

           

          I'm trying to figure out how to reproduce your problem the simplest way possible.

          • 2. Re: Migrating db2 LOBs to Oracle using Offline Method
            1567201

            Thank you.

             

            Here two files means, when we use "db2 export command with lobsinfile option" data get unloaded as 2 files - one is .dat file& another is .lob file where all the lobs from each row get extracted as a single file. If in case of "db2 export command with lobsinsepfiles option" data get unloaded as multiple files - one is .dat file& rest of all are .lob files, each one of them referencing each row in the table. I hope i am explained the scenario.

            In prior scenario, how can i load data from single lob file using SQL loader.

             

            Thanks,

            • 3. Re: Migrating db2 LOBs to Oracle using Offline Method
              rp0428

              That '.dat' file is going to have the metadata that has the length of each of the LOBs in that one file.

               

              Those files are likely in an IBM proprietary format.

               

              Someone from the Sql Dev team will need to respond if they somehow support that file format but I rather doubt it.

               

              Depending on the size of the LOBs storing them all in one file is NOT a good idea to begin with:

              1. You now have a single, probably binary file. If that file gets corrupted you could lose ALL of the data

              2. You can not easily locate and load/extract the LOB for a particular record unless an IBM utility provides that functionality

              3. Unless they added the functionality in Sql Dev 4 that app would not extract BLOB data


              This link supports what I said about the file being a proprietary format.

              http://www-01.ibm.com/support/knowledgecenter/#!/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html?cp=SSEPGG_9.7.0%2F3-6-2-4-36

              • 4. Re: Migrating db2 LOBs to Oracle using Offline Method
                Barry Mcgillin-Oracle

                What we need to do is figure out whether we can generate the loader or external table definitions with the lobsinfile format. We have not done that yet.  We need to do a bit of work and figure out what changes we would need to make to support this way.  Ramesh will take a look and come back when he has an answer.  We;re doing work in this area at the moment so it is appropriate.

                • 5. Re: Migrating db2 LOBs to Oracle using Offline Method
                  rp0428
                  What we need to do is figure out whether we can generate the loader or external table definitions with the lobsinfile format. We have not done that yet.  We need to do a bit of work and figure out what changes we would need to make to support this way.  Ramesh will take a look and come back when he has an answer.  We;re doing work in this area at the moment so it is appropriate.

                  Both sql*loader and external tables can certainly be used to load LOB data. For example - here is an oracle-base article that shows how external tables can be used.

                  http://www.oracle-base.com/articles/10g/external-tables-containing-lob-data.php

                   

                  I don't see how those can be used for inline data since blob data, by definition, can contain any legal binary characters but control files are text files. And even CLOB data can have character set issues and use a character set different than the control file character set.

                   

                  The example in the link above has the LOBs in a separate file for each entry with the control file providing the appropriate file name. That would roughly correspond to the 'lobsinsepfiles' parameter.

                   

                  Although external tables support the preprocessor directive the result of that still needs to feed the data properly and that is separate from the actual control file/external table parameters that need to be supplied.

                   

                  Also, with external tables, the data needs to be on the server, not the client.

                   

                  Doesn't Sql developer still lack support for even exporting LOB data? And still limit exported CLOB data to 4k? If so then that functionality 'hole' should be plugged first.

                   

                  IMHO the best 'general purpose' solution for sql developer would be to add support would be the EXPORT/UNLOAD of table data that includes LOB columns. Then in conjuntion with that add support for the LOAD of that same data.

                   

                  If I were prototyping such functionality I would follow the pattern already established by others. The data file produced would be non-LOB data with the LOB columns specifying the file name of the LOB for that row and column. The LOB data itself would go into a separate file. That would correspond to the 'lobsinsepfiles' option.

                   

                  Separate files - at least for the first iteration of the functionality.I see no need to deal with multiple LOBs in the same file. Adding multiple LOBs to the same file is trivial from a functional point of view (just concatenate the individual files) but unnecessarily complicates the development and testing of the raw functionality needed.