DBMS: Update Statistics

A statistic (on a table, a column / a set of columns or on an index) provides information on:
– the table (number of lines, average size of a line, number of blocks/page, density),
– the distribution of values ​​for the column(s) (number of distinct values, number of duplicates, existence of null values, histogram of distribution of values),
– the composition of the index (depth, number of blocks, cluster ratio).

These statistics are used by the database optimizer to study the different execution plans that can be used for a query.
The optimizer (based on costs / CBO) chooses the best execution plan (least expensive) from the data calculated from these statistics on, in particular, the number of rows and blocks of data to be shuffled at each stage of these plans (access strategies).

Statistics are essential for the proper calculation of query execution plans. It is therefore essential that they are up to date to take into account changes to the data and therefore to the indexes, columns and tables.
Thanks to update statistics, the optimizer has reliable data to choose the best performing execution plan to serve data to applications.

DBMS: Reorganize

An optimized database improves query performance, reduces processing times and reduces resource consumption (CPU, RAM, Storage, etc.). This is why it is essential to maintain an efficient organization of databases.

Hence the need for reorganization when this is not the case.
This task consists of physically reorganizing indexes and/or tables with the aim of optimizing the use of disk space and improving access to data (through their organization), recovering unused disk space, left by deleted records and/or data updates.

The operation to be carried out depends on the degree of dispersion of the index (or table) information in the database (called fragmentation):
– compaction (REORGANIZE or COMPACT) is often necessary for fragmentation between 10 and 30%
– beyond that, a reconstruction (REBUILD) must be carried out.
A “side effect” of rebuilding a table is rebuilding all of its indexes.

In any case, a successful database is a database that is monitored and maintained.

V2022.04

dbSQWare version 2022.04 has just been released!

This latest version includes several major new features expected by our users:
– the possibility of managing repository instances by API:
         –> in consultation, creation and update
– the ability to generate indicators, by API, for:
         –> backups and storage.
The management of indicators for the Job part is being finalized and will be available in the next patch.

Constantly evolving, dbSQWare adapts to the demands of our users to make their daily life easier and to gain in efficiency and time, on the daily tasks of operating database engines.

V2022.03

dbSQWare version 2022.03 has just been released!

With a novelty that should please our users:
– the possibility of delegating the authentication, to connect to the software, to an external application, AD style.

Not to mention that dbSQWare is the only DBMS management and maintenance software capable of managing 12 types of database engines in a consistent manner.

DBMS: Environment copy

– “The environment copy, what is it for?”

The environment copy allows you to duplicate an environment and thus have 2 identical environments (same structure, same tables, same data) on 2 different servers.

The interest is to have, for example, a development environment identical to that of production (in the case where the prod environment is copied in dev) and to provide developers with a working base reliable and up-to-date before they start coding (Caution, it may be necessary to anonymize the data!).
In other cases, the environment copy can make it possible to put a test environment into production, if it has successfully passed all the validation stages, or to set up a DRP (Disaster Recovery Plan) on D-1.

Finally, if the duplication is done through backups, this makes it possible to validate their viability.

DBMS

dbSQWare, our multi-DBMS operation and maintenance software supports 11 types of database engines.

On RDBMS side (Relational Database Management System):
– Oracle, the most popular on UNIX / Linux
– SQLServer, developed by Microsoft and mainly deployed on Windows
– Teradata, a historic RDBMS with colossal storage capacities
– Sybase, acquired by SAP and former market leader
– DB2 LUW, the distributed system version of the Mainframe engine
– Ingres, dating from the 80s and in Open Source since 2004
– MySQL, the most popular open source RDBMS
– PostgreSQL, the most advanced open source RDBMS

On NoSQL side:
– Cassandra and her column-oriented organization
– MongoDB, the most popular of NoSQL, with a document-oriented organization
– Adabas Linux version, accessible through the natural language.

Despite the diversity of these database engines and their heterogeneity, with dbSQWare, their operation and maintenance are completely homogeneous.

DBMS : Restoration

– “Restoring a database, what does it consist of? »

To be able to restore a database, you must already have made a backup (full, incremental, log, hot or cold).
Then, restore consists of recovering the backed up files that make up the database.

A well-planned backup and restore strategy helps protect databases from data loss.
It is therefore essential to regularly check your strategy, restoring a set of backups to recover your database.
 

V2022.01

dbSQWare version 2022.01 is released!

This version includes new features designed to make life easier for our users:
– the option on percentages in the legend of pie charts
– management of bar graphs
– updated support dates for Cassandra (4.0), MongoDB (5.1), Oracle (21c) and Teradata (15.x, 16.x, 17.x).

Not to mention that dbSQWare is the only DBMS management and maintenance software capable of managing 12 types of database engines in a consistent manner.

DBMS: Backups

– “By the way, what’s the point of making database backups? »

As its name suggests, a database is a structured collection of data. It consists of files in which the data is stored.
It is created and managed by a database management system (DBMS) which, among other things, allows data to be recorded, searched for, sorted and modified.

Data is at the heart of business activity. It is extremely valuable and its loss can be devastating when there is no copy.
This is why it is essential to make backups of the databases.

There are several types of backup:
– the “full backup”: all the files of a database are copied
– the “incremental backup”: only the blocks of files that have been modified since the last backup are copied
– the “log backup” (redo log, transaction log, wal or xlog): transaction log files are copied. These files are used to memorize all the modifications carried out on a database.

Backups can be made:
– cold: when the database is stopped
– or hot: when the database is active.

Once the backups have been made, it is then possible to recover the data if necessary.

And since trust does not exclude control, it is a good idea to check compliance with the backup policy on a daily basis across the entire database.

V2021.07

We couldn’t end the year without releasing a new version of our DBMS management and maintenance software.

With new features like:
– the user access report to databases for administrators, by API
– the management of inclusion and exclusion users
– the management of Oracle clusters configuration

And other changes to quickly discover in V2021.07 from dbSQWare!