The DB2 STORED PROCEDURE are the programs which are directly managed by DBMS. The STORED PROCEDURE generally contains SQLs and they can be called by application programs. The STORED PROCEDURE processes the query and returns the result to the application program. The STORED PROCEDURES can be used for the SQLs which are very often used, so instead of using the same SQL query again and again, we can simply use STORED PROCEDURE.The other benefit of STORED PROCEDURE is that they are fast and have good performance as compared to static SQLs used in application programs. The STORED PROCEDURE can be ... Read More

The DB2 indexes are used to increase the query performance and speed up the query result. There can be multiple indexes built on a single table and DB2 optimizer chooses different indexes based on the predicate used in the WHERE clause to fetch the query result.In order to find out all the indexes which are built on a particular table we will use the DB2 system table SYSIBM.SYSINDEXES. This table records all the details related to indexes. Following SQL query can be used on this table to get the desired result.ExampleSELECT NAME, TBNAME FROM SYSIBM.SYSINDEXES    WHERE TBNAME = ‘’The ... Read More

A DB2 PLAN contains all the SQL statements in executable form. To execute any COBOL-DB2 program we need to provide its corresponding PLAN. The DB2 optimizer will evaluate the access path for the SQLs present in the PLAN to fetch the result for the COBOL-DB2 program. The SQLs in PLAN may use a number of indexes. Indexes facilitate the table search based on a key value and speed up the query processing time.If any of the index is dropped, it may negatively impact the PLANS which contain the SQLs using that dropped index. Therefore, as part of impact analysis, it ... Read More

The DB2 SCALAR functions take a single column value and returns a single result. The COLUMN function takes the column value from multiple rows of a DB2 table and returns a single result. In case of SCALAR function only one row is involved.SCALAR FUNCTIONDESCRIPTIONLENGTHGives the length of the column valueREPLACEUsed to replace a string with another stringCONCATUsed to combine two or more column valuesINTEGERGives the integer equivalent of the column valueCHARGives the character equivalent of the column valueFor example, if we have an ORDERS DB2 table and we want to return only the integer value of the ORDER_TOTAL for all ... Read More

The DB2 COLUMN functions are also known as aggregate functions. These functions take the values from a particular column of multiple rows and return a single value.Below is the list of some most commonly used COLUMN functions.COLUMN FUNCTIONDESCRIPTIONSUMFinds the sum of column valuesCOUNTCount the number of column valuesAVGFinds the average of column valuesMAXFinds the maximum value in the columnMINFinds the minimum value in the columnThe COLUMN function can only be used in the SELECT query. For example, if we have to find the average of all the ORDER_TOTAL for the orders placed on 15-08-2020 from the ORDERS table, then we ... Read More

A DEADLOCK condition occurs when two applications lock the data that is needed by each other. Both the applications wait for the other one to release the lock and hence deadlock occurs.Let us see an example to understand this better:There are two COBOL-DB2 programs in execution: PROG A and PROG B.The PROG A is holding an UPDATE LOCK in ORDERS table while PROG B is holding a UPDATE LOCK in TRANSACTION table. At some point of time, PROG A requires an UPDATE LOCK on TRANSACTION TABLE and PROG B requires an UPDATE LOCK on ORDERS table but two application programs ... Read More

The SHARED, UPDATE and EXCLUSIVE are the types of locks which are used in concurrent application processing. This means that these locks are used when the two or more applications try to access the same row or page. Below is the description for each type of lock.SHARED LOCKIf any application acquires SHARED LOCK on a page, then it can read that page but cannot update it. Other concurrent applications can acquire the SHARED or UPDATE lock on the same page.UPDATE LOCKIf any application acquires UPDATE LOCK on a page, then it can read that page but cannot update it. In ... Read More

The NON-REPEATABLE READ conditions occur when the COBOL-DB2 program executes the same query multiple times, but the result of the query is different each time. This generally happens when two COBOL-DB2 programs access the same row of the DB2 table.The first program reads the row, then the second program reads the same row, updates it and commits the changes. The first program again reads the same row but now the data does not match with the previous fetch.The PHANTOM condition occurs when the number of rows in the query result does not match when the query is executed multiple times.For ... Read More

The LOST UPDATE and DIRTY read issues are related to concurrency. The concurrency is defined as the ability of two or more applications to access the same table data.The LOST UPDATE impacts the processing of COBOL-DB2 programs in the following way.Suppose there are 2 application programs — PROG A and PROG B which are trying to access the same row of ORDERS DB2 table. PROG A and PROG B read a row from the ORDERS table with ORDER_ID = ‘Z87661’ at the same time. PROG A updates some data in this row and commits the changes. PROG B now updates ... Read More

Problem: A COBOL-DB2 program takes the data from an input file having 1000 records and inserts the data in a DB2 table. The program failed after 432nd record. How will you implement restart logic?SolutionThe restart logic can be implemented in a COBOL-DB2 program by fixing a commit frequency. If we choose a commit frequency of 100, then the following steps need to be performed:Declare a variable for a counter, say WS-COUNT.Place a loop in which we will read the record from the file and insert it in a database. Increment the counter WS-COUNT by one each time a record is ... Read More