D2000 and working with databases

Peter Humaj -

In today's article, we will talk about how D2000 can work with databases. This time we will not talk about its "built-in" databases (configuration, logging, and archive), but we will focus on access to application-specific databases, the structure and content of which are fully in the hands of application specialists and consultants who create SCADA, MES, EMS and other applications built on D2000 technology.

DbManager process

Similar to the specialized processes for communication (D2000 KOM), scripting (D2000 Event Handler) or archiving (D2000 Archive), there is also a specialized process for working with application databases in the D2000 system – D2000 DbManager. This process accesses the SQL databases and performs all necessary actions. Requests for DbManager come from D2000 Event Handler processes (scripts executed on the server) and from D2000 HI processes (direct user access to databases via the Browser displayer + active schemes with scripts).

The following figure shows part of the D2000 system architecture. Three users (D2000 HI) and one D2000 Event Handler process are connected to the core of the system (D2000 Server process). Their requests for SQL database are handled by two D2000 DbManager processes. We did not draw other types of processes for clarity.

Figure 1- D2000 system architecture

The D2000 DbManager process can access databases in two different ways:

  • Via ODBC interface to any database for which an ODBC driver is installed in the system and an ODBC DSN (Data Source Name) is created. We routinely work with PostgreSQL, MySql, MariaDB, Sybase SQL Anywhere, Microsoft SQL Server, Informix, Firebird, and other databases.
  • Via OCI interface (Oracle Client Interface) to the Oracle database. In the past, the Oracle database was widely used in large D2000 applications, and therefore a specialized version of the DbManager process using advanced OCI features was created. The DbManager process in the OCI version also existed on platforms such as OpenVMS or HP-UX.

There are other reasons why several DbManager processes can be running in a single application:

  • Some older ODBC drivers may only be available in a 32-bit version (so a 32-bit DbManager is required), while others are 64-bit (a 64-bit DbManager is required).
  • Due to the separation of networks, it may be necessary to place the DbManager process in the network with the database, or directly on the database server. This especially concerns the so-called interface databases, i.e. databases used to connect different systems (e.g. MES system and ERP system).

Within the DODM model (about which you can read a separate blog), the DbManager process is the parent of objects of type Database, and these are the parents of objects of type Table.

Figure 2 - Hierarchy of objects Process - Database - Table

Database object

The Database object represents the SQL database together with the access rights to it, as it also contains the configuration of the username and password. Therefore, it is necessary to create several objects of type Database if we need to access the SQL database with different access rights (e.g. to access different schemas).

The D2000 DbManager process is optimized for the parallel work of several users with a Database type object. Therefore, it is able to create multiple connections with one SQL database. Each such connection is serviced by its own task and can work in transactional mode (reserved for the specific ESL script that created the transaction) or in non-transactional mode (shared by several scripts or D2000 HI processes, with a COMMIT automatically performed after each operation, so we call such a connection also automatic). In the configuration dialog of the Database object, you can set the number of predefined connections (they are created after the start of the DbManager process), limit the maximum number of connections, the maximum number of non-transactional (automatic) connections, and even reserve automatic connections for the Browser displayer.

Figure 3 - Configuration of Database object

It is also possible to specify after what time unused connections (created beyond the scope of predefined connections) should be closed. DbManager allows the created connections to be recycled - since the creation of a connection can be a rather demanding operation with high overhead for some SQL databases (e.g. Oracle).

If there is a firewall on the network between the DbManager process and the SQL database, it can be useful to specify empty operations after some time of inactivity - sometimes it happens that the firewall "cancels" a TCP connection that has not been used for a long time, and when DbManager tries to use it again, an error occurs. Empty operations make it possible to continuously check the connection status with the SQL database and to recreate it in the event of a breakdown.

It is also important to be able to set the interpretation of time data in the database - times can be in local time (according to the time zone of the server on which DbManager is running), or in monotonic time with a specified offset from UTC.

The value of an object of Database type is equal to the current number of connections that the DbManager process has created for this object.

Table object

The Table object represents a table or view in an SQL database. Each table has its own structure - column names and data types, which are defined by an object of the Structure Definition type. It is possible to configure which columns make up the key, which are optional (i.e. exist in the structure definition in D2000, but not in the SQL database), and which are non-null.

On the Table object, it is also possible to define the type of access to the table (none, read, read+modify).

Figure 4 - Configuration of Table object

A useful feature is defining the time depth. DbManager can automatically delete data in the table whose defined column (of type Absolute Time) is older than the time depth. Alternatively, different time depths can be defined for different periods using a Data Purpose object.

Due to data protection laws (GDPR), the possibility of automatic anonymization of data in selected columns based on the set time column was also implemented.

Example of use: data in the Name, Surname, Date_of_Birth columns in the Contract table will be anonymized after 5 years based on the Contract_End_Time column. Anonymization will take place in such a way that the Name and Date_of_Birth are deleted, the Surname is changed to "Anonymized yyyy-mm-dd", where yyyy-mm-dd is replaced by the date on which the anonymization was performed.

Database operations

The actions performed by DbManager can be divided into several groups.

Actions of the Browser displayer – this is a direct interactive viewing of data by the user within the scheme or directly in the D2000 HI. The user can navigate between individual pages and edit, insert new rows, and delete rows according to the configured access rights and properties of the Browser.

Figure 5 - Browser with navigation in the scheme

Script actions - actions performed in an ESL script (and their equivalents available from the Java language). These can be further divided into several categories:

Work with transactions: opening/closing transactions, their commit and rollback. These actions include:

The transaction handle can subsequently be used for actions belonging to other categories. If these actions do not use a transaction handle, COMMIT is performed automatically after each action.

Standard work with data – based on the condition (WHERE) or based on the key column(s) defined on the Table object, it is possible to read/insert/update rows (one or more) in the table. These actions include:

Accelerated work with data – DBS_* actions allow you to skip DB_CONNECT/DB_DISCONNECT, other functionality is identical:

Actions for working with BLOBs: BLOBs are used in databases to store large binary data (e.g. PDF invoices). These are represented as files on the D2000 side. Special actions are used for reading/writing:

Paged work with data: these are script actions that enable paged access to data. For large volumes of data, it is better to load and process the data in the script gradually, e.g. in batches of 100 rows - the equivalent of pagination in the Browser.

  • PG_CONNECT - "connecting" to the table and setting the page size
  • PG_DISCONNECT- "disconnecting" from the table
  • PG_READ – reading the specified page of data
  • PG_INSERT – inserting one row into the table
  • PG_DELETE – deleting one row in the table
  • PG_UPDATE – updating one row in the table

Actions for "variable" work with the database: if it is not known in advance from which table and which columns should be read, it is possible to use the SQL_SELECT command, which allows specifying the entire SQL command. This can also be parameterized  (SQL_PREPARE and others related to it). The advantage of parameterized commands is the possibility of recycling them in the DbManager (execution multiple times with different parameters) and in the SQL database (the SQL command is parsed and an execution plan is created for it only once). This also includes actions to execute an arbitrary SQL statement and run a stored procedure:

  • SQL_CONNECT- "connecting" to the database
  • SQL_DISCONNECT- "disconnecting" from the database
  • SQL_EXEC_DIRECT– execution of any SQL command (without returning data)
  • SQL_EXEC_PROC– execution of a stored procedure with a list of parameters
  • SQL_SELECT – execution of any SQL SELECT command (without parameterization)
  • SQL_PREPARE– preparation of any SQL SELECT command (with parameterization)
  • SQL_BINDIN – setting of parameter values for SQL_PREPARE
  • SQL_FETCH – fetch 1 or more rows returned after SQL_PREPARE
  • SQL_FREE – releasing resources and ending SQL_PREPARE

After the SQL_PREPARE action, it is possible to perform the sequence SQL_BINDIN (setting parameters - different each time) and repeated SQL_FETCH (reading the returned rows) several times. Finally, we end the work by calling SQL_FREE. This is how recycling of parameterized SQL command is implemented.

Special actions: other specialized actions:

  • DB_REFRESH_TABLE – forcing the data displayed in the Browser to be refreshed. By default, after changing data through a specific object of the Table type, the data is restored for the Browsers in which this Table is displayed (unless they have data refresh turned off in the Browser configuration). However, there may be a situation where the data changes in another way (e.g. through another Table or by the SQL_EXEC_DIRECT action) and it is necessary to force the refresh from the script.
  • DB_SET_PROCESS_PARAMS- set or clear "context". Context means to set named parameters (name-value pairs), which are stored in the global temporary table D2000_PROCESS_PARAMS. By default, the context is common to one D2000 HI or D2000 Event Handler process, or it can be limited by the start parameter --batch_mode to events started by the OPENEVENT action with the same instance number. The context is visible not only within D2000, but can also be used by views and stored procedures in the SQL database. An example of use can be the use of two named parameters for setting the period (PERIOD_FROM, PERIOD_TO), for which e.g. invoices will be displayed using database views.
  • ON DB_CHANGE– registration of handler for data change. The handler will be called when the content of the database table changes, or the DB_REFRESH_TABLE action is  called, the active instance of the DbManager process is switched over (or the redundant D2000 Server is switched over), or the table is deleted from the configuration.

Conclusion

The D2000 DbManager process has been part of D2000 for over two decades. During that time, it was optimized and refactored into a multithreaded application in order to handle the work for dozens of users not only in applications such as SCADA or MES, but also in purely database-oriented applications such as ETRM (Energy Trading & Risk Management), in which application databases play a primary role.

In the continuation of today's blog, we will talk about the options for debugging and tuning database actions in D2000.

May 2, 2024, Ing. Peter Humaj, www.ipesoft.com