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

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

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

    ‏2012-02-16T09:05:38Z  
    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

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

    ‏2012-02-16T13:29:31Z  
    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
    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

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

    ‏2012-02-16T13:54:16Z  
    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
    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

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

    ‏2012-02-16T14:32:17Z  
    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
    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

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

    ‏2012-02-16T18:42:35Z  
    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
    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

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

    ‏2012-02-20T09:28:58Z  
    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
    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

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

    ‏2012-02-20T14:49:46Z  
    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
    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

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

    ‏2012-02-22T13:52:10Z  
    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
    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