Topic
8 replies Latest Post - ‏2012-02-22T13:52:10Z by Henrik_Loeser
SystemAdmin
SystemAdmin
708 Posts
ACCEPTED ANSWER

Pinned topic Convert MS SQL varchar(max) to DB2 9.7 XML

‏2012-02-16T08:58:53Z |
Good day,

We are migrating from MS SQL to DB2 9.7 and need to migrate XML data stored
in MS SQL column varchar(max) to DB2 9.7 XML column.

How do we convert a column from MS SQL varchar(max) to XML column in DB2 ??
The tool generated the cloumn as CLOB and the load is failing.

Thank you and regards
Martin
Updated on 2012-02-22T13:52:10Z at 2012-02-22T13:52:10Z by Henrik_Loeser
  • Henrik_Loeser
    Henrik_Loeser
    5 Posts
    ACCEPTED ANSWER

    Re: Convert MS SQL varchar(max) to DB2 9.7 XML

    ‏2012-02-16T09:05:38Z  in response to SystemAdmin
    Hello Martin,

    you could change the data type to XML instead of CLOB. Why is load failing? Usually you can store all kinds of text data, including XML, in a LOB. If that is solved, loading into an XML-typed column should work, too.

    --
    Henrik Loeser
    Read more about DB2 in my blog at http://blog.4loeser.net
    • SystemAdmin
      SystemAdmin
      708 Posts
      ACCEPTED ANSWER

      Re: Convert MS SQL varchar(max) to DB2 9.7 XML

      ‏2012-02-16T13:29:31Z  in response to Henrik_Loeser
      Hi Henrik,

      I have updated the CustomDataMapping.properties to change the column to XML.
      I still get the error below when running the load.

      SQL3114W Some data following ""<soap:Envelope xmlns:soap="" in row "F1-1" and
      column "9" was not loaded.

      SQL3229W The field value in row "F1-1" and column "9" is invalid. The row was
      rejected. Reason code: "1".

      When I use vi editor to open the data file that is being loaded I see it has ^M characters,
      not sure if the data is not being transferred properly from SQL server to the AIX server,

      Thanks
      Martin
      • Henrik_Loeser
        Henrik_Loeser
        5 Posts
        ACCEPTED ANSWER

        Re: Convert MS SQL varchar(max) to DB2 9.7 XML

        ‏2012-02-16T13:54:16Z  in response to SystemAdmin
        The ^M probably cause it, try running dos2unix or a similar tool against your data.

        --
        Henrik Loeser
        Read more about DB2 in my blog at http://blog.4loeser.net
        • SystemAdmin
          SystemAdmin
          708 Posts
          ACCEPTED ANSWER

          Re: Convert MS SQL varchar(max) to DB2 9.7 XML

          ‏2012-02-16T14:32:17Z  in response to Henrik_Loeser
          Many Thanks,
          I will try data.
          Is there a way I could send the XML data to a separate output file(s) with IBM data movement tool?
          i.e. equivalent of export XML TO xml-path

          Regards
          Martin
          • SystemAdmin
            SystemAdmin
            708 Posts
            ACCEPTED ANSWER

            Re: Convert MS SQL varchar(max) to DB2 9.7 XML

            ‏2012-02-16T18:42:35Z  in response to SystemAdmin
            No luck after running dos2unix to fix the data file.
            Could it be that xml data has " " which is also used as character delimiter ?
            Please advise.How do I override the default character delimiter when generating the data.
            Also how do I send the XML to different output files.

            Thank you and regards
            Martin
            • Henrik_Loeser
              Henrik_Loeser
              5 Posts
              ACCEPTED ANSWER

              Re: Convert MS SQL varchar(max) to DB2 9.7 XML

              ‏2012-02-20T09:28:58Z  in response to SystemAdmin
              Hey,

              The IDMT tool creates several "Custom...Mapping" files in the migration directory. You could try specifying XML as data type as target and hopefully this should use the XML export.

              --
              Henrik Loeser
              Read more about DB2 in my blog at http://blog.4loeser.net
              • SystemAdmin
                SystemAdmin
                708 Posts
                ACCEPTED ANSWER

                Re: Convert MS SQL varchar(max) to DB2 9.7 XML

                ‏2012-02-20T14:49:46Z  in response to Henrik_Loeser
                Hi Henrik,

                Thanks for your assistance thus far.
                I have updated the CustomDataMapping.properties file and the ddl get generated correctly i.e column definition as XML.

                However when I run the load I get the Following error:

                SQL3114W Some data following "" <soap:Envelope" in row "F0-1"
                and column "9" was not loaded.

                SQL3229W The field value in row "F0-1" and column "9" is invalid. The row was
                rejected. Reason code: "1".

                Regards
                Martin
                • Henrik_Loeser
                  Henrik_Loeser
                  5 Posts
                  ACCEPTED ANSWER

                  Re: Convert MS SQL varchar(max) to DB2 9.7 XML

                  ‏2012-02-22T13:52:10Z  in response to SystemAdmin
                  Could you provide details on the load command and how the data is stored? Is the XML included in the delimited file or separate? How does the XML document causing the error look like?

                  --
                  Henrik Loeser
                  Read more about DB2 in my blog at http://blog.4loeser.net