Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
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
Problem: Explain the difference between Multi-index access and Index-only access paths used by DB2 Optimizer. Give an example for both.SolutionThe Index-only access and Multi-index access are the types of access path which the DB2 optimizer chooses in order to fetch the query results. The Index-only access paths are used when all the columns given in the SELECT query are present in the index. In this case, the optimizer does not have to go to the data page to fetch the result, all the data is available in the index page.For example, the ORDERS table has a primary key as ORDER_ID ... Read More
The UNION in DB2 is used to merge two or more SELECT statements. The SELECT statements can be on a single table or on multiple tables. Each SELECT statement is processed individually and their results are combined to give us the final result rows.The UNION statement will eliminate the duplicate rows appearing as a result of SELECT statements. If we want to retain the duplicate rows, then we can use the UNION ALL statement.For example, if we want to extract all ORDER_ID with ORDER_TOTAL greater than 1000 in ORDERS and ORDERS_HIST table, we can use the below query with a ... Read More
The EXECUTE IMMEDIATE and EXECUTE with PREPARE could not be used for SELECT query. For the SELECT query, we have a fixed list SELECT in which the column to be fetched remains fixed and it cannot be changed.For example, if we want to select the orders placed on 14-08-2020. Then we can use dynamic SQL as given below:ExampleMOVE ‘SELECT ORDER_ID FROM ORDERS WHERE ORDER_DATE=?’ TO WS-SQL-QUERY EXEC SQL DECLARE ORDER-CUR CURSOR FOR SELQUERY END-EXEC EXEC SQL PREPARE SELQUERY FROM :WS-SQL-QUERY END-EXEC MOVE ‘14-08-2020’ TO WS-ORDER-DATE EXEC SQL OPEN ORDER-CUR USING :WS-ORDER-DATE END-EXEC PERFORM UNTIL SQLCODE = +100 ... Read More
The EXECUTE IMMEDIATE and EXECUTE PREPARE are the forms of dynamic SQL. In case of EXECUTE immediate, we can give the SQL statement in the host variable and pass this host variable in EXECUTE IMMEDIATE.Following example demonstrates these forms.Example01 WS-SQL-DECLARE 05 WS-SQL-LEN PIC S9(04) COMP. 05 WS-SQL-QUERY PIC X(70). MOVE +80 TO WS-SQL-LEN MOVE “UPDATE ORDERS SET ORDER_PAID = ‘YES’ WHERE ORDER_DATE = ‘14-08-2020’” TO WS-SQL-QUERY EXEC SQL EXECUTE IMMEDIATE :WS-SQL-DECLARE END-EXECIn case of EXECUTE PREPARE, the SQL statement is first prepared and then executed. We can use this form of dynamic SQL ... Read More