We can find all the ORDER_ID which are having ORDER_TOTAL greater than the average value of all the ORDER_TOTAL present in the ORDERS table using the sub-query.For example, if we have below ORDERS table.ORDER_IDORDER_TOTALA223451867A629985634A569027615A5691187960A56915132A5691880363Below is the subquery to find out the desired data.ExampleSELECT ORDER_ID, ORDER_TOTAL FROM ORDERS    WHERE ORDER_TOTAL ... Read More

Problem: How will the COBOL-DB2 program behave once the number of locks placed on the table space exceeds the defined limit?SolutionThe number of locks which an application can place on a DB2 resource such as page, table row, etc., is defined in DSNZPARM. Once the number of page and row ... Read More

DB2 optimizer plays an important role in the overall performance of the database. The optimizer selects the optimal access path for each query through which data can be fetched from the database. It identifies the indexes to follow, query predicates, etc.The optimizer selects the access path automatically and we can ... Read More

Problem: How can you implement a logic to automatically generate a unique value in a DB2 column for every new row inserted?SolutionWe can implement a logic in a DB2 table through which we can have one column which will have an automatically generated value for every new row inserted. This ... Read More

Problem: What are DB2 subqueries and what is the use of subqueries? Explain subqueries with the help of an example on the ORDERS table.SolutionA subquery in DB2 is a query within a query, i.e., nested query. A subquery is executed first followed by its parent query. We can have a ... Read More

The result of OUTER JOIN includes matched and unmatched rows in the WHERE clause. There are two main limitations of OUTER JOINS in DB2.The WHERE clause of OUTER JOIN can only have ‘=’ relational operator. , etc are not allowed in case of OUTER JOIN of two or more tables. ... Read More

DB2 has a concept of DCL, through which we can control the access to DB2 objects like table, plan, etc. DCL stands for Data Control Language and using this we can provide and remove user access to the DB2 objects.GRANT command will give the user access to the mentioned object ... Read More

DB2 places locks on the resources like table, tablespace, etc., when any application is using that resource. In a COBOL-DB2 program, if we are modifying or deleting data from a table then DB2 places locks. When we give the ROLLBACK statement to revert the changes made in the database, all ... Read More

Problem: How to use SAVEPOINT in a DB2? What is the purpose of SAVE-POINT in DB2? Explain with the help of an example.SolutionThe SAVEPOINT is used as a marker or tag to save the changes without committing in the database. After making the changes in the database, we can give ... Read More

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 ... Read More