Hello,
these are three quite different data sources. And you are asking quite a range of questions, each of which can be answered for each data source. I’ll try to answer MQTT and SQL database (my colleagues will have to answer REST API in D2000).
As for MQTT: the D2000 KOM process implements MQTT protocol (documentation here). One communication line = one connection to an MQTT server. So, should there be high data volumes, you can create multiple lines to the same MQTT server and thus process incoming data in parallel. Of course, each line would have a different “Topic Filter” parameter. This presumes that there are multiple topics/topic groups that can be expressed by several filters so that the data can be split into several data flows.
MQTT protocol can also take care of communication interruptions and buffer the data during communication outages. This is related to the “Subscribe QoS” parameter - see more in our documentation and MQTT protocol documentation.
As for securing data transmissions, MQTT supports encryption (MQTTS). With D2000, you can use external tools to ensure encryption - the usage of stunnel is described in a blog. An advantage of using a separate utility is that you don’t have to update D2000 should you need updated/newer encryption methods.
As for SQL, things like encryption can be often achieved via ODBC configuration (supported e.g. by database drivers for PostgreSQL ODBC, SQL Server, or Sybase SQL Anywhere). And, of course, within SQL there are transactions, so you can ensure “all-or-nothing” is written to the database. As for reading from an external data source, SQL actions in our ESL script will return error codes should there be a connection error. If you are within a transaction, you have to close and reopen the transaction. If you are using "auto commit’ mode (outside of transaction), the DbManager automatically reconnects the database, so you can just repeat reading (or writing).
As for performance, for one Database object, there can be multiple connections (so that users/scripts working with a specific Database don’ have to wait for one another) as is written in our Working with databases document. Also, actions working with the database usually support structured variables, so that you can use a single DBS_INSERT to insert e.g. 1000 rows… the same is true for reading with DBS_READ - just specify a local structured variable and the maximum number of rows to be read.
Finally, you can use multiple Events to process the database data, or you can pipeline the processing - you can have one Event that reads the data and then use an asynchronous RPC CALL to send the data to another Event (or one of a group of Events if the processing needs more time/CPU).
Again, see our Scripting howto for more detailed reading about events.
Btw, several of our customers use D2000 to read thousands of orders/bids/trades per second from Joule Trayport trading system, process them, and store the data in a PostgreSQL database. They use a pipeline architecture described above (one task for receiving, multiple tasks for processing and inserting into PostgreSQL database).
As for logging, you can log into the D2000 Logging database (using the LOG or LOGEX actions) or you can log to a generic SQL database. Or, if you are only interested in a few latest errors, you can store them in a structured variable.
