IBM Db2 is a family of data management products, including the Db2 relational database. The products feature AI-powered capabilities to help you modernize the management of both structured and unstructured data across on-premises and multicloud environments.
DB2 in general is a family of data management products. It is developed by IBM. The AI-powered family of data management tools helps us in the management of both structured as well as unstructured data. The DB2 relational database is a relational database that is part of the DB2 family of data management tools.
The DELETE * FROM table-name query deletes all the rows from the table but, there is still an empty table with no rows and no columns in the database. However, the DROP TABLE table-name query deletes all the rows and columns and along with that, the table gets deleted from the database too i.e. the table is completely removed (deleted) from the database.
The union command is used to merge 2 or more SELECT statements and the select statements can be applied on a single table as well as on multiple tables. The major difference between UNION and UNION ALL is that UNION removes the duplicate rows when it is applied on the tables whereas UNION ALL retains the duplicates.
Concurrency means that more than one application process of DB2 can access the data at the same time. Concurrency can cause some issues like lost updates, access to unrepeatable reads and uncommitted data.
DCLGEN stands for Declarations generator. The programs that we write should declare the tables and views that it accesses. The declarations generator i.e. DCLGEN produces these DECLARE statements for C, Cobol and PL/I programs so that one does not need to code the statements manually. It also generates corresponding host variable structures. When DCLGEN is used to generate a table declaration, the DB2 uses the DB2 catalog to get the relevant information from it. DCLGEN, then uses this information to create an SQL DECLARE TABLE statement for the table/view.
The full form of SQLCA is the SQL communication area or Structured Query Language Communication Area. It is a structure of variables that is updated after every execution of an SQL statement.
DSNDB07 is a database where the DB2 performs its sorting operations. It includes the sort work-area of DB2 and external storage.
RUN STATS is a DB2 utility used to collect statistics about the data value in TABLES. It is used by the optimizer to decide the access path. RUN STATS also collects statistics that are used for space management. These statistics are stored in the DB2 catalog tables.
Usually, the RUN STATS is run after the following:
Lock escalation is the process of promoting page lock sizes to table or table space lock size when the transaction has acquired more locks than the ones specified in NUMLKTS. Locks have to be taken on objects in a single table space for escalations to take place.
When used, PACKAGES help avoid binding of large numbers of DBRM members in one plan. They also dispense with the cost of large BIND and avoid the entire transactions making them unavailable during BIND and automatic REBIND of the plan. Another advantage is that they minimize the fallback complexities when changes result in an error.
DBRM denotes Database Request Module. It has the SQL statements that are extracted from the host language program obtained by the pre-compiler.
PLAN is the result of the BIND process and has executable code for SQL statements in DBRM.
CS would release the lock on the page after its use. RR would retain all the locks acquired till the end of a transaction.
SELECT is not preferred in Embedded SQL programs for three reasons. First, if the table structure is changed by the addition or deletion of a field and the program is modified, then using SELECT might retrieve the columns that the user may not use. This would lead to Input-Output overhead and the chances of index-only scan are also eliminated.
The RCT is a table that is defined to a DB2/CICS region. It contains control characteristics that are assembled via the DSN CRCT macros. The RCT matches the CICS transaction ID to its associated DB2 authorization ID and plan ID ( CICS attachment facility).
A STOGROUP is a named collection of DASD volumes to be used by table spaces and index spaces of databases. The volumes of STOGROUP must be of the same device type.
It is a data structure that must be included in any host-language program using SQL. It is used to pass feedback about the SQL operations to the program. Fields are return codes, error messages, handling codes, and warnings.