Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
Articles on Trending Technologies
Technical articles with clear explanations and examples
How LOST UPDATE and DIRTY READ impact the processing of a COBOL-DB2 program?
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 MoreImplementation of restart logic in a COBOL-DB2 program
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 MoreUsage and example of Multi-index and Index-only access path in DB2
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 MoreDifference between UNION and UNION ALL in DB2
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 MoreWhat is Fixed-list SELECT? Give the syntax and explain with help of an example
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 MoreWhat is the difference between EXECUTE IMMEDIATE and EXECUTE WITH PREPARE in DB2?
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 MoreExplain the concept of DYNAMIC SQL in DB2 with the help of an example
A static SQL is hardcoded in COBOL-DB2 program and the SQL query cannot change during the program execution. We can only change the value of the host variables. In the case of DYNAMIC SQL, we can change the columns, tables and predicates in the COBOL-DB2 program in run time.For example, based on the current date, we can update ORDERS or ORDERS_HIST table. This query can be built using DYNAMIC SQL which includes ORDERS table or ORDER_HIST table.The main advantage of DYNAMIC SQL is its flexibility. We can add columns or change tables/predicates as per our business logic. On the other ...
Read MoreWhat is the significance of the QBLOCK_TYPE and JOIN_TYPE column of a PLAN table?
The QBLOCK_TYPE column of the PLAN_TABLE mainly gives the information about the type of query. It can have the value as CORSUB which indicates that the query is Correlated subquery and NCOSUB indicates that the query is Non-correlated subquery.The JOIN_TYPE column indicates the type of join used in the query. This column can take the value as per the below table.Column valueInterpretationBLANKINNER JOIN/NO JOINFFULL OUTER JOINLLEFT OUTER JOINRRIGHT OUTER JOIN
Read MoreWhat is the significance of ACCESSTYPE and INDEXONLY column of a PLAN table in DB2?
The PLAN_TABLE in DB2 contains details of the access paths used by the optimizer from the EXPLAIN statement. This table gives much information about the PLAN having DB2 SQL statements.ACCESSTYPE is one of the columns of PLAN_TABLE which gives details regarding the access type. The values which can be present in this column and its interpretation is given below.Column valueInterpretationIIndexed accessRTablespace scanMMultiple index scanNIndex access present in predicateThe INDEXONLY column can take the value as ‘Y’ which indicates that the optimizer just needs to read indexspace in order to fetch the data for the query.
Read MoreDifference between CORRELATED and UNCORRELATED subqueries in DB2
The subquery is a nested query. When this subquery is executed only once and the result of this subquery is used to extract the data in the main query, then this type of subquery is known as UNCORRELATED subquery. On the other hand, when a subquery refers to the main query for each execution, then the subquery is known as CORRELATED subquery.For example, if we want to extract all the orders from the ORDERS table which have ORDER_TOTAL more than overall average, then we can use the below UNCORRELATED subquery.ExampleSELECT ORDER_ID FROM ORDERS WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM ORDERS)There ...
Read More