3 Replies Latest reply: Nov 8, 2012 7:30 AM by 970752 RSS

    Using sqlldr to load CLOB data from DB2

    970752
      I am stuck trying to resolve this problem. I am migrating data from DB2 to Oracle. I used DB2 export to extract the data specifying lobsinfile clause. This created all the CLOB data in one file. So a typical record has a column with a reference to the CLOB data. "OUTFILE.001.lob.0.2880/". where OUTFILE.001.lob is the name specified in the export command and 0 is the starting position in the file and 2880 is the length of the first CLOB.

      When I try to load this data using sqlldr I'm getting a file not found.

      The control file looks something like this:
      ....
      ...
      clob_1 FILLER char(100),
      "DETAILS" LOBFILE(clob_1) TERMINATED BY EOF,
      ...
      ...

      I'm using Oracle 11gR2 and DB2 9.7.5

      Your help is appreciated.
        • 1. Re: Using sqlldr to load CLOB data from DB2
          damorgan
          File not found is a permissions issue but we've another problem with your post ... that is the actual error stack not your impression of it. Please post the full and complete stack. Thanks.
          • 2. Re: Using sqlldr to load CLOB data from DB2
            970752
            OK..here are additional details. Some names have changed but the idea is the same. Also the sqlldr is executing in the same directory as the data files and the control file

            Primary data file is VOIPCACHE.dat Secondary datafile (file with lob data) is VOIPCACHE.001.lob

            Control Fileload data
            infile 'VOIPCACHE.dat'
            badfile 'VOIPCACHE.bad'
            discardfile 'VOIPCACHE.dsc'
            replace into table VOIPCACHE
            fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS
            (KEY1 "rtrim(:KEY1)",
            .....
            .....
            FIELD8,
            clob_1 FILLER char (100),
            "DATA" LOBFILE(clob_1) TERMINATED BY EOF)

            Snippet from Log file

            IELD7 NEXT * , O(") CHARACTER
            FIELD8 NEXT * , O(") CHARACTER
            CLOB_1 NEXT 100 , O(") CHARACTER
            (FILLER FIELD)
            "DATA" DERIVED * EOF CHARACTER
            Dynamic LOBFILE. Filename in field CLOB_1

            SQL*Loader-502: unable to open data file 'VOIPCACHE.001.lob.0.0/' for field "DATA" table VOIPCACHE
            SQL*Loader-553: file not found
            SQL*Loader-509: System error: No such file or directory
            SQL*Loader-502: unable to open data file 'VOIPCACHE.001.lob.0.47/' for field "DATA" table VOIPCACHE
            SQL*Loader-553: file not found
            SQL*Loader-509: System error: No such file or directory
            SQL*Loader-502: unable to open data file 'VOIPCACHE.001.lob.47.47/' for field "DATA" table VOIPCACHE
            SQL*Loader-553: file not found
            SQL*Loader-509: System error: No such file or directory
            SQL*Loader-502: unable to open data file 'VOIPCACHE.001.lob.94.58/' for field "DATA" table VOIPCACHE
            SQL*Loader-553: file not found
            SQL*Loader-509: System error: No such file or directory
            SQL*Loader-502: unable to open data file 'VOIPCACHE.001.lob.152.58/' for field "DATA" table VOIPCACHE
            SQL*Loader-553: file not found
            SQL*Loader-509: System error: No such file or directory
            SQL*Loader-502: unable to open data file 'VOIPCACHE.001.lob.210.206/' for field "DATA" table VOIPCACHE
            ......
            ......
            This is repeated for each record

            sqlldr command

            sqlldr userid=${SCHEMA}/${PASSWD}@$ORACLE_SID control=${CTLDIR}/${tbl}.ctl log=${LOGDIR}/${tbl}.log direct=true errors=50
            I dont think the variables are important here

            -EC
            • 3. Re: Using sqlldr to load CLOB data from DB2
              970752
              Any help is appreciated.