This discussion is archived
2 Replies Latest reply: Nov 16, 2012 10:58 PM by MikeN RSS

XML message - JMS message

user13010474 Newbie
Currently Being Moderated
I have a gg process which extracts data from a table and puts it in a extract trail at remote host. And there is a extract process which reads from this remote extract trail and puts the message onto a jms queue in XML format.

When I update a column in the source table I expect the key column and the updated column in the XML message, but what is happening is the XML is having data values for primary key and updated column along with no values for the other columns I.e empty tags for non updated columns. What parameter should be used to suppress these empty tags inthe XML in the jms queue message.

Thanks
  • 1. Re: XML message - JMS message
    MikeN Journeyer
    Currently Being Moderated
    user13010474 wrote:
    I have a gg process which extracts data from a table and puts it in a extract trail at remote host. And there is a extract process which reads from this remote extract trail and puts the message onto a jms queue in XML format.

    When I update a column in the source table I expect the key column and the updated column in the XML message, but what is happening is the XML is having data values for primary key and updated column along with no values for the other columns I.e empty tags for non updated columns. What parameter should be used to suppress these empty tags inthe XML in the jms queue message.
    That default xml format is mostly for demonstration purposes. There's another default xml format simply called "xml2", also for demo's, which is a little less verbose. For any "real" implementation, the xml format should be considered, designed, signed off by all involved parties (by the JMS consumers -- what format do they need?), and properly versioned to allow for changes over time.

    Rather than generating the default format and transforming it to meet the requirements, the desired format should be generated in the first place.

    To customize the message format, you can either write a Java class or quickly prototype something using a velocity template.

    For example, a simple csv velocity template:

    <pre>
    ${op.OpType.charID()},"${op.TableName}"#{foreach}($c in $op),"${c.Name}","${c.BeforeValue}","${c.AfterValue}"#{end}
    </pre>


    produces (for example):
    <pre>
    I,"MYSCHEMA.TCUSTMER","CUST_CODE","","JANE","NAME","","ROCKY FLYER INC.","CITY","","DENVER","STATE","","CO"
    </pre>
  • 2. Re: XML message - JMS message
    MikeN Journeyer
    Currently Being Moderated
    MikeN wrote:
    To customize the message format, you can either write a Java class or quickly prototype something using a velocity template.
    For example, a simple csv velocity template:

    <pre>
    ${op.OpType.charID()},"${op.TableName}"#{foreach}($c in $op),"${c.Name}","${c.BeforeValue}","${c.AfterValue}"#{end}
    </pre>

    produces (for example):
    <pre>
    I,"MYSCHEMA.TCUSTMER","CUST_CODE","","JANE","NAME","","ROCKY FLYER INC.","CITY","","DENVER","STATE","","CO"
    </pre>
    Here's an example using XML - it will still print "missing" columns, but you can see the if/else that checks to see if the column is "missing".

    (Note on "missing" columns, as defined by GoldenGate: when you have a table with 10 columns and you just update two of them, then GG is only by default going to replicat the two changed columns and the primary key. So the trail will have 3 columns, and the rest are "missing". If a column is updated to SQL "null", then it's not "missing", but it is "null". If you want columns to be "captured" that haven't changed, then add them to "add trandata" on the source (which force-logs the columns) or configure GG to "fetch" the column.)

    The formatter:
    <pre>
    <operation table="$op.tableName" sqlType="$op.sqlType" opType="$op.opType" txState="${op.txState.char}" seq="$op.seqno" rba="$op.rba">
    #foreach( $col in $op.iterator() )
    <col name="$col.meta.columnName" isKey="$col.meta.keyCol">
    #if($col.hasBeforeValue() && ! $col.isBeforeNull())
    <before exist="$col.hasBeforeValue()"><![CDATA[$col.beforeValue]]></before>
    #else
    #{if}($col.hasBeforeValue() && $col.isNull())<before><isNull/></before>#{end}
    #end
    #if($col.hasAfterValue() && ! $col.isNull())
    <after exist="$col.hasAfterValue()"><![CDATA[$col.afterValue]]></after>
    #else
    #{if}($col.hasAfterValue() && $col.isNull())<after><isNull/></after>#{end}
    #end
    </col>
    #end
    </operation>
    </pre>

    The output (for example):
    <pre>
    <operation table="TABLE_FOO" sqlType="UPDATE" opType="UPDATE_FIELDCOMP" txState="M" seq="17" rba="1220">
    <col name="COL_ID" isKey="true">
    <before exist="true"><![CDATA[2145]]></before>
    <after exist="true"><![CDATA[2145]]></after>
    </col>
    <col name="COL_STAMP" isKey="true">
    <before exist="true"><![CDATA[2009-01-12:15:14:43.946476000]]></before>
    <after exist="true"><![CDATA[2009-01-12:15:14:43.946476000]]></after>
    </col>
    <col name="COL_DESC" isKey="false">
    </col>
    <col name="COL_DATE" isKey="false">
    <before exist="true"><![CDATA[2134-11-14:00:00:00]]></before>
    <after exist="true"><![CDATA[2134-11-15:00:00:00]]></after>
    </col>
    <col name="COL_BUF" isKey="false">
    <before exist="true"><![CDATA[8589674788]]></before>
    <after exist="true"><![CDATA[8590045514]]></after>
    </col>
    </operation>
    </pre>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points