Archive for the ‘B2B’ Category

Shrink b2b lobs

Sites that use Oracle B2B 10 with reasonable volumes will eventually find that the B2B_LOB tablespace keeps on growing even if the built-in purge job is used. Why? The Oracle database doesn’t reuse the space from old (deleted) lobs. This is a known peculiarity, check other sources for details.

The fix is straightforward. Shrink space for the two lobs that store payload and wirepayload:

alter table tip_datastorage_rt modify lob (clobvalue_clob) (shrink space);
alter table tip_datastorage_rt modify lob (blobvalue) (shrink space);

This can be scheduled as a recurring job or executed manually by a DBA and it can be done online without stopping B2B. To check the amount of storage used before and after, execute as b2b:

select round(sum(us.bytes/1024/1024)) total_mb
  from user_lobs ul, user_segments us
 where ul.segment_name = us.segment_name
   and ul.table_name = 'TIP_DATASTORAGE_RT';

For one system the storage used went from 5648M to 16M, quite a respectable saving.

According to Oracle Support (3-1865217281) it is safe to do this. It has intentionally been left out of the purge job, as it is considered to be a task for a DBA.

Note! Apparently one excellent reason to leave the shrink out of the purge job is that there is a bug in some old Oracle versions that may cause data corruption in lobs after a shrink. Check that you have or better, or alternatively that you have a one-off patch for the relevant bugs fixed in that release.

Categories: B2B, Database, Oracle

Tweaking Oracle B2B CONTRL

Oracle B2B supports EDIFACT and can of course generate CONTRL messages out of the box. However, by default in some versions a generated CONTRL message will not contain routing address or reverse routing address. These fields are also known as sub-addresses and are part of the interchange sender and recipient, as shown in the figure.

Interchange header with routing address in SpecBuilder

Interchange Header in SpecBuilder

For example, if an inbound message has the interchange sender 9990022:ZZ:XYZ the generated CONTRL contains 9990022:ZZ, both in the UNB and UCI segments. That is clearly a problem. Luckily it is easy to fix.

First of all set oracle.tip.adapter.b2b.edi.FAInternalProperties=true in $ORACLE_HOME/ip/config/ Second, edit $ORACLE_HOME/ip/oem/edifecs/XEngine/config/AckCode-CONTRL.xml. Backup the original file and add:

<CodeList Name="InternalPropertyList">
 <Code Key="InterchangeReceiverID" Value="InterchangeSenderID"/>
 <Code Key="InterchangeReceiverQual" Value="InterchangeSenderQual"/>
 <Code Key="InterchangeSenderID" Value="InterchangeReceiverID"/>
 <Code Key="InterchangeSenderQual" Value="InterchangeReceiverQual"/>
 <Code Key="InterchangeSenderInternalID" Value="InterchangeSenderInternalID"/>
 <Code Key="InterchangeReceiverInternalID" Value="InterchangeReceiverInternalID"/>
 <Code Key="GroupReceiverID" Value="GroupSenderID"/>
 <Code Key="GroupSenderID" Value="GroupReceiverID"/>
 <Code Key="InterchangeReceiverAddress" Value="InterchangeSenderAddress"/>
 <Code Key="InterchangeSenderAddress" Value="InterchangeReceiverAddress"/>

The lines with InterchangeSenderInternalID and InterchangeReceiverInternalID will do the trick. Restart B2BServer and everything should work.

Categories: B2B

Oracle B2B bulk resubmit with SQL

In 11g it will be possible to resubmit messages from the user interface, but in 10g that feature was missing. In early 2008 some resubmit support was added,  see It included automatic retries and manual resubmit using a command line tool and it made it possible to write more sophisticated tools supporting both inbound and outbound resubmission.

However, if thousands of messages are to be resubmitted based on some criteria nothing beats SQL. Luckily it is not that difficult to write a SQL script that resubmits messages that match custom search criteria.

For outbound messages it has always been fairly easy. All the information needed in order to create a new identical message and put it on the internal delivery channel for processing can be found in the b2b_instancemessage view. See Tech Note #004 for details.

For inbound messages it is harder, but there the resubmit support in the product helps. We can do what the command line tool does. Note that this is in no way supported by me or by Oracle; be sure to try it out on a test system before using it in production! It will probably not work as is with 11g either.

The script below finds matching messages, flags them for resubmit and alerts B2B to process them using the event queue. The condition can be quite complex, selecting exactly the inbound messages that are to be resubmitted.

set serveroutput on;
  msg                 b2b_event_type;
  enqueue_options     dbms_aq.enqueue_options_t;
  message_properties  dbms_aq.message_properties_t;
  msg_handle          raw(16);
  n                   number(10);
  dbms_output.put_line ('Resending messages...');
    subscribers(1) :=$_agent('b2buser', null, null);
    message_properties.recipient_list := subscribers;
    n := 0;
    for rec in (select b2bwiremessageid from b2b_instancemessage
      where direction = 'Inbound'
      -- ENTER CONDITION HERE ------------------------------------
      and b2btimestamp between sysdate - 20 and sysdate - 1
      and documenttype like 'INVOIC%'
      -- END OF CONDITION ----------------------------------------
    ) loop
      update tip_wiremessage_rt
        set protocoltransportbinding = 'B2BRetry=true
' || protocoltransportbinding
        where protocoltransportbinding not like '%B2BRetry=%'
        and b2bwiremessageid = rec.b2bwiremessageid;
      msg := b2b_event_type(event_type => 3, event_id => 24,
                            id => rec.b2bwiremessageid);
      dbms_aq.enqueue(queue_name  => 'B2B_EVENT_QUEUE',
                      enqueue_options => enqueue_options,
                      message_properties => message_properties,
                      payload => msg,
                      msgid => msg_handle);
      n := n + 1;
    end loop;
    dbms_output.put_line ('Processed ' || n || ' messages');
    dbms_output.put_line ('Commit or rollback now!');
    when others then
      dbms_output.put_line ('SQL Error: ' || SQLCODE ||
                            ' - ' || SQLERRM);

Note that the newline after B2BRetry=true is intentional and required!

Again, this is not officially supported in any way, but perhaps it can save your skin if something has gone wrong somewhere and you need to get those messages in fast. Just remember not to run it in production before trying it out in a safe environment.

Categories: B2B, Database, Oracle