DB V2
Discover more about the DB V2 component and how to use it on the Digibee Integration Platform.
DB V2 supports Select, Insert, Delete, and Update operations, as well as Procedure calls, returning the results in a JSON structure. For a complete list of supported databases, refer to the Supported Databases documentation. Explore practical use cases and applications of DB V2 to learn more.
Parameters
Take a look at the configuration parameters of the component. Parameters supported by Double Braces expressions are marked with (DB).
General tab
Use Dynamic Account
When the option is active, the component will use the account dynamically. When deactivated, it will use the account statically.
False
Boolean
Account Type
Defines the Account type to be used by the component. Options are Basic, AWS V4, and Kerberos.
N/A
String
Account
Account to be used by the component to connect.
N/A
String
Fail On Error
If the option is activated, the pipeline's execution with an error will be interrupted. Otherwise, the pipeline execution proceeds, but the result will show a false value for the "success" property.
False
Boolean
Operation tab
Type
The data type of the property that is declared in the SQL Statement.
VARCHAR
String
Database URL
Define a Database URL.
jdbc:mysql://35.223.175.97/db-training
String
SQL Statement (DB)
Accepts any SQL statement supported by the underlying database. Double braces expressions are allowed. E.g.: {{ message.id }}.
SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d') as DATA
String
Batch
If the option is enabled, batch processing of some statements is performed.
False
Boolean
Rollback On Error
If this option is enabled, the commits of operations are performed only if all of them are successful. Otherwise, a rollback of all batch operations is performed. This option is only available if the Batch parameter is enabled.
False
Boolean
Batch Items
If Batch is enabled, specify the batch items.
N/A
String
Blob As File
If the option is enabled, all the BLOB parameters for Query or Procedure operations are expected to receive the file path.
N/A
Boolean
Clob As File
If the option is enabled, all the CLOB parameters for Query or Procedure operations are expected to receive the file path.
False
Boolean
Charset
Name of the character code for the file reading (standard UTF-8).
UTF-8
String
Type Properties
Click on the Add button to activate the following additional parameters: Key, Type, Out Parameter Name, and Parameter Type.
N/A
Options of Type Properties
Custom Connection Properties
Specific connection and database properties defined by the user.
N/A
String
Advanced Settings tab
Pool Size By Actual Consumers
If the option is enabled, the number of pooled connections is equal to the number of consumers configured in the pipeline deployment. If the option is disabled, the pool size is determined by the pipeline deployment size, regardless of the number of consumers.
False
Boolean
Exclusive DB Pool
If the option is enabled, a new non-shared pool is always created for the sole use by this connectors. If the option is disabled, a pool might be shared between connectors if the URL, credentials, and custom connection properties are the same. Important: the custom connection properties must be defined in the exact same order between connectors for a pool to be shared, otherwise it will be considered as a different configuration and another pool will be created.
False
Boolean
Custom Pool
If the option is enabled, the base configuration for the connection pool will be set based on the Connection Maximum Lifetime, Minimum Idle Connections, and Idle Connection Timeout parameters. If the option is disabled, the pool will be configured based on the Keep Connection parameter. Important: this is an advanced feature and must be used carefully. See the dedicated section for the Connection Pool subject below.
False
Boolean
Connection Maximum Lifetime
Defines the maximum lifetime of a connection in the connection pool. An in-use connection will never be retired. It will be removed only when it is closed. The minimum value allowed is 30000 milliseconds (30 seconds). If a lower value is set, then the default of 1800000 milliseconds (30 minutes) will be used. This option is only available if the Custom Pool parameter is enabled.
N/A
Integer
Minimum Idle Connections
Defines the minimum number of idle connections to be maintained in the pool. The maximum value allowed is based on the deployment size, that is, 10, 20, or 40. If a higher value is set, the maximum for the current deployment size will be used. This option is only available if the Custom Pool parameter is enabled.
N/A
Integer
Idle Connection Timeout
Defines the maximum amount of time that a connection is allowed to sit idle in the pool. For this option to take effect: Its value must be lower than the one set in Connection Maximum Lifetime. The value configured in Minimum Idle Connections must be lower than the pool size (defined through Pool Size By Actual Consumers). This option is only available if the Custom Pool parameter is enabled.
N/A
Integer
Keep Connection
If the option is enabled, the connection pool will always maintain a minimum number of open connections ready to use. After 30 minutes, these connections will be renewed. The minimum number of open connections will be defined based on the Pool Size By Actual Consumers parameter. If the option is disabled, the pool will be created empty and connections will be opened on demand, being kept in the pool for no more than 5 minutes. In this case, the connections are not renewed. This option is only available if the Custom Pool parameter is disabled.
True
Boolean
Output Column From Label
For some databases, if your Select uses an alias, you must enable this flag so that the column name is displayed exactly like the alias.
False
Boolean
Connection Test Query
SQL statement to be used before each connection is established. This is an optional parameter and should be used with databases that do not provide reliable connection status information.
N/A
String
Raw SQL Statement (DB)
If the option is active, the SQL Statement parameter allows the use of dynamic queries through Double Braces statements. When using this functionality, you must ensure that the pipeline has security measures against unwanted SQL statements (SQL Injection). See more about this parameter in the section below.
False
Boolean
Documentation tab
Documentation
Section for documenting any necessary information about the connector configuration and business rules.
N/A
String
Parameters additional information
Key
The Key parameter refers to a property declared through Double Braces expression in the SQL Statement in case of Procedures and INSERT Queries that handles CLOB/BLOB data types. Every Double Braces declaration has an index and it must be used to set this parameter.
Example:
INSERT INTO TABLE (MY_CLOB, MY_BLOB, MY_STRING) VALUES ({{ message.clob }}, {{ message.blob }}, {{ message.string }})
In this case, we have index 0 for {{ message.clob }}, 1 for {{ message.blob }} and 2 for {{ message.string }} .
Raw SQL Statement
To bring more flexibility when using DB V2, we can activate the Raw SQL Statement option, previously configure a query, and reference it through Double Braces in the SQL Statement parameter as follows:
Query previously defined via Template Transformer
Raw SQL Statement activation
Query referenced in SQL Statement parameter
Important: as a good practice, we strongly recommend that, when activating the Raw SQL Statement option, the queries are previously defined using the Template Transformer component. The use of Template Transformer allows validating parameters through FreeMarker technology and also parameter declaration through Double Braces. These parameters are not resolved by Template Transformer but by the component DB V2, which by default configures and validates the parameters of the SQL statement beforehand (PreparedStatement). By applying these security measures, you reduce the risks of attacks like SQL Injection.
In the image below, we have an example of recommended use of the component on the left (with the Double Braces in the WHERE clause, highlighted in green); and another example of non-recommended use on the right (with the FreeMarker in the WHERE clause, highlighted in red), which may pose risks to pipeline safety:
DB V2 in Action
Batch mode
When it's necessary to make a batch processing of some instructions, you can make calls in batch mode in the queries.
Example
Let's say you need to inform an array of objects in the component, that will be used in the batch execution:
Items
[ { "name": "Mathews", "type":"A"},
{ "name": "Jules", "type":"A"},
{ "name": "Raphael", "type":"B"} ]In the SQL instructions, you must inform it in the following way:
SQL
INSERT INTO TABLE VALUES ( {{ item.name }}, {{ item.type }} )
When you use Double Braces expressions {{ item.name }}, an iteration is made inside the array (informed in items) and a corresponding property is searched inside the object. In that case, the property is "name".
After the execution, 3 registers are inserted. The expected return is:
{
"totalSucceeded":3,
"totalFailed":0
}If one of the executions fails, an object will be returned with the "error" property:
{
"totalSucceeded":1,
"totalFailed":1
}If one of the executions fails, an object will be returned with the "errors" property:
{
"totalSucceeded":1,
"totalFailed":1
"errors": ["error1", "error2"]
}Rollback On Error
If this option is activated, the commits of the operations will be made only if all of them are successful. Otherwise, the rollback of all the batch operations will be made.
If the option is inactive, then the commit and the successful changes by commit will be made even if there's an error between the executions.
If an error occurs, an object containing all the errors will be returned (inside the "errors" property) and the consolidated with the -1 value will also be returned:
{
"totalSucceeded":-1,
"totalFailed":-1,
"errors": ["erro1", "error2"],
"success": false
}For other databases, as Firebird, the occurrence of errors isn't informed. Therefore, an object with no error can be returned even if there was a failure:
{
"totalSucceeded":0,
"totalFailed":3
"errors": ["error1", "error2"],
"success": false
}For these error cases in Batch Mode, don't forget to check the "success" property. If it returns "false", it means at least one error occurred during the execution.
Connection pool
By standard, we use a pool based on the configurations of the deployed pipeline. For example, if the pipeline is SMALL, then the pool size will be 10. For MEDIUM the size would be 20 and for LARGE it would be 40.
It's possible to manage the pool size by the deployment time as well. For that, it's necessary to enable the Pool Size By Actual Consumers property in the component. With it, what's manually configured in the deployment screen is used.
See in the image below the configuration of a SMALL pipeline with 5 consumers. If you want the pool of the database components (DB V2 and Stream DB V3) to use this size, you'll have to enable the “Pool Size By Actual Consumers” property in every existing component:
Our pool is shared between the database components that access the same database inside the pipeline. If an exclusive pool for a determined component is necessary, enable the “Exclusive Pool” property.
Customizing the Connection Pool
By default, the connector defines how the connections in the pool are handled based on the Keep Connection parameter. This configuration aims to facilitate the connection pool configuration for scenarios in which, for example, high availability is necessary or the opposite is the case.
If enabled, the connections in the pool are kept open all the time and renewed after 30 minutes. This could be used for high availability scenarios reducing the cost of frequently opening new connections.
However, if we have the opposite scenario, where connections are needed between longer intervals of time, we can disable the parameter to configure the pool without open connections and only open it when it is really necessary. After the connection has been used, it is kept in the pool for no more than 5 minutes and is not renewed.
Although this means you don’t have to worry about configuring the connection pool, this default option may not be the best for some use cases.
For more flexibility on the pool configuration, you can enable the Custom Pool option to ignore the default configuration and set a custom configuration.
In this case, you need to set the three parameters below (which are implicitly defined in the default configuration):
Connection Maximum Lifetime
Minimum Idle Connections
Idle Connection Timeout
Customizing a connection pool can be a difficult task as it requires a deeper knowledge of the subject. Besides, when applied to the context of the Digibee Integration Platform, you have to consider the variables that can affect the pool performance.
Things like the deployment size and its replicas, access to the same databases using distinct credentials and properties on DB connectors inside the same and different pipelines, the exclusive DB pool option on DB connectors, directly affect how the connection pool should be configured.
Based on all the mentioned above, it is highly recommended that you only enable the Custom Pool option if you have some knowledge on the topic and if it is really necessary.
Technology
Authentication via Kerberos
It's possible to make an authentication via Kerberos in database components. For that, all you have to do is:
inform a KERBEROS-type account
configure a Kerberos principal
configure a keytab (that must be the base64 of the own keytab generated file)
More usage scenarios with DB V2
Was this helpful?