Global Sterling Data Exchange

Here is something you can consider. Hopefully it will give you an idea of what you can try.

• Message Writing:
  • BPML (Business Process Modeling Language): You'll need to create or modify a Business Process in Sterling B2Bi using BPML. This process would write incoming messages or tasks to your chosen queueing system:
    • For database queueing, use an SQL Adapter to execute the insert SQL command.
    • For file-based, write to a file in a designated directory with a sequence or timestamp in the filename.
• Message Reading and Processing:
  • Design another BPML process that:
    • Reads from the queue (e.g., selects the oldest message from the database or picks the earliest file by name).
    • Processes this message according to your business logic.
    • Removes or marks the message as processed in the queue to avoid reprocessing.

• Retry Mechanism:
  • Implement a retry count in your process. If an error occurs while processing, log it, increment the retry counter, and requeue the message for another attempt later.
  • Use a separate "Error Queue" or table where failed messages are stored for manual or automatic retry.
• Dead Letter Queue:
  • For messages that fail repeatedly, move them to a "Dead Letter Queue" where they can be inspected or retried manually. This prevents endless retry loops and maintains FIFO for the rest of the queue.

• Database Locking:
  • With SQL Server or DB2, you might encounter locking issues if multiple processes try to read/write at the same time. Use transactions wisely, possibly with lower isolation levels for reading if data consistency allows.
• File System Operations:
  • For file-based queuing, ensure that file creation, renaming, and deletion operations are optimized. Use atomic file operations to prevent race conditions.
• Throughput and Scalability:
  • Ensure the chosen method can handle the expected message volume. For databases, indexes on InsertionTime would speed up retrieval. For files, manage directory structure to avoid OS limitations on file counts per directory.

• Concurrency Testing:
  • Simulate multiple concurrent writes and reads to your queue to check if FIFO order is maintained. Use tools or scripts to send messages at high rates.
• Failure Scenarios:
  • Test how the system handles errors, like network issues, database failures, or disk full situations. Ensure messages are not lost or processed out of order.
• Volume and Load Testing:
  • Test with large volumes of messages to see where bottlenecks occur and how the system scales, especially under peak loads.

-- Create the queue table
CREATE TABLE MessageQueue (
    MessageID INT IDENTITY(1,1) PRIMARY KEY,
    MessageContent VARCHAR(MAX),
    InsertionTime DATETIME DEFAULT CURRENT_TIMESTAMP,
    ProcessingAttempts INT DEFAULT 0,
    LastProcessingAttempt DATETIME
);

-- Insert a message
INSERT INTO MessageQueue (MessageContent) VALUES ('Your Message Here');

-- Select the oldest unprocessed message for processing
SELECT TOP 1 * FROM MessageQueue 
WHERE ProcessingAttempts < 3 -- Example retry limit
ORDER BY InsertionTime ASC;

-- Update after processing attempt
UPDATE MessageQueue 
SET ProcessingAttempts = ProcessingAttempts + 1, 
    LastProcessingAttempt = CURRENT_TIMESTAMP 
WHERE MessageID = @MessageID; -- @MessageID is the ID of the message being processed

-- Remove processed message or move to an archive table for history
DELETE FROM MessageQueue WHERE MessageID = @MessageID;
-- or 
INSERT INTO ArchiveQueue SELECT * FROM MessageQueue WHERE MessageID = @MessageID;
DELETE FROM MessageQueue WHERE MessageID = @MessageID;