About the guide:
Creating usage summaries, which require creating summary table and stored procedure execution on a live database, can potentially degrade database performance. To mitigate this, coordinate with your database administrators (DBAs) to schedule these operations during off-peak hours.
This procedure provides an estimate of total usage based on available data. The results may be lower than the actual metered usage recorded by the application.
Some scenarios where the estimate may be lower than actual usage include (but are not limited to):
1. Customer disabling bizdoc persist.
2. Customer disabling payload extraction for AS2.
3. This query does not take attachments into account.
Please treat this as a ballpark estimate rather than an exact usage metric.
Introduction
Subscription based pricing:
When moving to a subscription-based pricing plan it’s helpful to have a baseline or an estimated number of transactions that will be counted as chargeable. The following solution will help guide you in determining total usage over a period by executing a query against the IBM Trading Networks database.
Definition of a B2B transaction:
A B2B Transaction is the exchange of any electronic document (e.g., purchase order, purchase order acknowledgment, invoice, ASN) with another entity in any EDI or non-EDI standard format (e.g., EDI, XML, csv).
- A single document sent or received by the platform is counted as a B2B Transaction
- For batch transactions, each single transaction contained within the batch is counted as a B2B Transaction.
- A document more than 5 kilobytes will be counted as an additional B2B Transaction for each additional 5 kilobytes rounded up to the nearest increment
Solution description: We have an optimized stored procedure that summarizes data hourly, reducing the risk of database locking.
Stored Procedure considerations:
Access Your Database Interface – Ensure you have the necessary permissions to execute queries.
2. Set the Date Range – Adjust the provided query parameters to reflect the desired reporting period.
3. Execute the Procedure – Follow steps provided for your DB instance.
4. Example Results: Below are sample results which show the transaction and data volume of those transactions by hour. Simply do a sum of all results to get a total for the entire time frame.
Note
In a simulated 1TB MySQL environment, this procedure generated a daily summary in 120 seconds (1 day report). However, performance is influenced by transaction volume, database throughput, and overall system load.
The procedure's timeframe can be configured for daily or shorter intervals.
Please consult your DBA as this involves creating a summary table and executing stored procedure on a live database. This may impact DB performance.
Steps to generate usage metrics for different DB
MySQL
-- =========================================
-- STEP 1: Create Summary table
-- =========================================
CREATE TABLE b2b_measures_summary (
Hour_Bucket VARCHAR(19),
B2bTransactionsCount BIGINT,
B2BDataTransfer BIGINT
);
-- =========================================
-- STEP 2: Create Stored procedure
-- =========================================
-- DELIMITER $$
CREATE PROCEDURE generateb2bmeasuressummary(IN p_start_timestamp DATETIME,
IN p_end_timestamp DATETIME)
begin
DECLARE v_current_hour DATETIME;
DECLARE v_next_hour DATETIME;
DECLARE v_error_message VARCHAR(255);
SET v_current_hour = date_format(p_start_timestamp, '%Y-%m-%d %H:00:00');
SET v_next_hour = date_add(v_current_hour, INTERVAL 1 hour);
WHILE v_current_hour < p_end_timestamp do
-- Process data for the current hour
INSERT INTO b2b_measures_summary
(hour_bucket,
b2btransactionscount,
b2bdatatransfer)
SELECT Date_format(v_current_hour, '%Y-%m-%d %H:%i:%s') AS Hour_Bucket,
Sum(Ceil(bc.contentlength / ( 1024 * 5 ))) AS
B2bTransactionsCount,
Sum(bc.contentlength) AS B2BDataTransfer
FROM bizdoc b
JOIN bizdoccontent bc
ON b.docid = bc.docid
JOIN bizdoctypedef btd
ON b.doctypeid = btd.typeid
WHERE b.doctimestamp >= v_current_hour
AND b.doctimestamp < v_next_hour
AND ( btd.bdtype IN ( 0, 1, 2 )
OR ( btd.bdtype = 3
AND btd.typeid IN ( 'WMUNED1-----ENVELOPE----',
'WMEANC1-----ENVELOPE----',
'WMVDA-1-----ENVELOPE----',
'WMVICS1-----ENVELOPE----',
'WMODET1-----ENVELOPE----',
'WMX12-1-----ENVELOPE----',
'WMUCS-1-----ENVELOPE----',
'WMTRAD1-----STX---------' ) ) )
AND bc.partindex = 0
GROUP BY Date_format(v_current_hour, '%Y-%m-%d %H:%i:%s');
SET v_current_hour = v_next_hour;
SET v_next_hour = date_add(v_current_hour, INTERVAL 1 hour);
end WHILE;
end;
-- DELIMITER ;
-- =========================================
-- STEP 3: Execute the stored procedure
-- =========================================
-- Adjust the "from" and "to" dates as needed.
-- The example below calculates usage metrics for the period from March 3rd to March 5th.
CALL generateB2BMeasuresSummary('2025-03-03 00:00:00', '2025-03-05 23:59:59');
-- =========================================
-- STEP 4: Fetch result summary
-- =========================================
select * from b2b_measures_summary;
-- =========================================
-- STEP 5: Cleanup
-- =========================================
-- 🔔 NOTE FOR CUSTOMERS:
-- Once you have retrieved the usage data you need,
-- please drop the stored procedure and summary table
-- to keep your database clean and avoid future conflicts.
DROP PROCEDURE generateB2BMeasuresSummary;
DROP TABLE b2b_measures_summary;
MS SQL
-- =========================================
-- STEP 1: Create Summary table
-- =========================================
CREATE TABLE b2b_measures_summary (
Hour_Bucket VARCHAR(19),
B2bTransactionsCount BIGINT,
B2BDataTransfer BIGINT
);
-- =========================================
-- STEP 2: Create Stored procedure
-- =========================================
CREATE PROCEDURE Generateb2bmeasuressummary @p_start_timestamp DATETIME,
@p_end_timestamp DATETIME
AS
BEGIN
SET nocount ON;
DECLARE @v_current_hour DATETIME;
DECLARE @v_next_hour DATETIME;
SET @v_current_hour = Dateadd(hour, Datediff(hour, 0, @p_start_timestamp),
0
);
SET @v_next_hour = Dateadd(hour, 1, @v_current_hour);
WHILE @v_current_hour < @p_end_timestamp
BEGIN
-- Insert aggregated data for the current hour
INSERT INTO b2b_measures_summary
(hour_bucket,
b2btransactionscount,
b2bdatatransfer)
SELECT hour_bucket,
b2btransactionscount,
b2bdatatransfer
FROM (SELECT Format(@v_current_hour, 'yyyy-MM-dd HH:mm:ss') AS
Hour_Bucket,
Sum(Ceiling(bc.contentlength / ( 1024.0 * 5 ))) AS
B2bTransactionsCount,
Sum(bc.contentlength) AS
B2BDataTransfer
FROM bizdoc b
JOIN bizdoccontent bc
ON b.docid = bc.docid
JOIN bizdoctypedef btd
ON b.doctypeid = btd.typeid
WHERE b.doctimestamp >= @v_current_hour
AND b.doctimestamp < @v_next_hour
AND ( btd.bdtype IN ( 0, 1, 2 )
OR ( btd.bdtype = 3
AND btd.typeid IN (
'WMUNED1-----ENVELOPE----',
'WMEANC1-----ENVELOPE----',
'WMVDA-1-----ENVELOPE----',
'WMVICS1-----ENVELOPE----',
'WMODET1-----ENVELOPE----',
'WMX12-1-----ENVELOPE----',
'WMUCS-1-----ENVELOPE----',
'WMTRAD1-----STX---------' )
) )
AND bc.partindex = 0) AS DerivedTable;
SET @v_current_hour = @v_next_hour;
SET @v_next_hour = Dateadd(hour, 1, @v_current_hour);
END;
END;
-- =========================================
-- STEP 3: Execute the stored procedure
-- =========================================
-- Adjust the "from" and "to" dates as needed.
-- The example below calculates usage metrics for the period from March 3rd to March 5th.
exec generateB2BMeasuresSummary '2025-03-03 00:00:00', '2025-03-05 23:59:59';
-- =========================================
-- STEP 4: Fetch result summary
-- =========================================
select * from b2b_measures_summary;
-- =========================================
-- STEP 5: Cleanup
-- =========================================
-- 🔔 NOTE FOR CUSTOMERS:
-- Once you have retrieved the usage data you need,
-- please drop the stored procedure and summary table
-- to keep your database clean and avoid future conflicts.
DROP PROCEDURE generateB2BMeasuresSummary;
DROP TABLE b2b_measures_summary;
Oracle DB
-- =========================================
-- STEP 1: Create Summary table
-- =========================================
CREATE TABLE b2b_measures_summary (
Hour_Bucket VARCHAR2(19),
B2bTransactionsCount NUMBER,
B2BDataTransfer NUMBER
);
-- =========================================
-- STEP 2: Create Stored procedure
-- =========================================
CREATE OR replace PROCEDURE Generateb2bmeasuressummary(
p_start_timestamp TIMESTAMP,
p_end_timestamp TIMESTAMP)
AS
v_proc_start_time NUMBER;
v_proc_end_time NUMBER;
v_proc_exec_time NUMBER;
v_current_hour TIMESTAMP;
v_next_hour TIMESTAMP;
BEGIN
v_current_hour := Trunc(p_start_timestamp, 'HH');
-- Start at the beginning of the first hour
v_next_hour := v_current_hour + INTERVAL '1' HOUR;
v_proc_start_time := dbms_utility.get_time;
WHILE v_current_hour < p_end_timestamp LOOP
-- Process data for the current hour
dbms_output.Put_line('Processing hour: '
|| To_char(v_current_hour, 'YYYY- MM-DD HH24:MI:SS'
));
INSERT INTO b2b_measures_summary
(hour_bucket,
b2btransactionscount,
b2bdatatransfer)
SELECT To_char(v_current_hour, 'YYYY-MM-DD HH24:MI:SS') AS Hour_Bucket,
SUM(Ceil(bc.contentlength / ( 1024 * 5 ))) AS
B2bTransactionsCount,
SUM(bc.contentlength) AS
B2BDataTransfer
FROM bizdoc b
join bizdoccontent bc
ON b.docid = bc.docid
join bizdoctypedef btd
ON b.doctypeid = btd.typeid
WHERE b.doctimestamp >= v_current_hour
AND b.doctimestamp < v_next_hour
AND ( btd.bdtype IN ( 0, 1, 2 )
OR ( btd.bdtype = 3
AND btd.typeid IN ( 'WMUNED1-----ENVELOPE----',
'WMEANC1-----ENVELOPE----',
'WMVDA-1-----ENVELOPE----',
'WMVICS1-----ENVELOPE----',
'WMODET1-----ENVELOPE----',
'WMX12-1-----ENVELOPE----',
'WMUCS-1-----ENVELOPE----',
'WMTRAD1-----STX---------' )
) )
AND bc.partindex = 0
GROUP BY Trunc(v_current_hour, 'HH');
v_current_hour := v_next_hour;
-- Move to the next hour
v_next_hour := v_current_hour + INTERVAL '1' HOUR;
END LOOP;
v_proc_end_time := dbms_utility.get_time;
v_proc_exec_time := ( v_proc_end_time - v_proc_start_time ) / 100;
dbms_output.Put_line('Procedure execution time: '
|| v_proc_exec_time
|| ' seconds');
EXCEPTION
WHEN OTHERS THEN
dbms_output.Put_line('Error: '
|| SQLERRM);
END;
-- =========================================
-- STEP 3: Execute the stored procedure
-- =========================================
-- Adjust the "from" and "to" dates as needed.
-- The example below calculates usage metrics for the period from March 3rd to March 5th.
BEGIN generateB2BMeasuresSummary( TO_TIMESTAMP('2025-03-03 00:00:00.000', 'YYYY-MM-DD HH24:mi:SS.FF3'), TO_TIMESTAMP('2025-03-05 23:59:59.999', 'YYYY-MM-DD HH24:mi:SS.FF3') ); END;
-- =========================================
-- STEP 4: Fetch result summary
-- =========================================
select * from b2b_measures_summary;
-- =========================================
-- STEP 5: Cleanup
-- =========================================
-- 🔔 NOTE FOR CUSTOMERS:
-- Once you have retrieved the usage data you need,
-- please drop the stored procedure and summary table
-- to keep your database clean and avoid future conflicts.
DROP PROCEDURE generateB2BMeasuresSummary;
DROP TABLE b2b_measures_summary;