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
DB2 Articles
Page 3 of 13
How to find out the access path selected by optimizer for a particular query?
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 easily find the access path using EXPLAIN DB2 command. We have to SET the query number first and then give our SQL query to find out its access path in three simple steps.For example, We have a DB2 ORDERS table and we want to examine the SELECT query which has ...
Read MorePurpose and usage of ROW-ID and SEQUENCE in a DB2
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 column can serve as a primary key and hence it is very useful for the random access of the DB2 table. This logic can be implemented via ROW-ID and SEQUENCE.Any one column of the DB2 table can be defined as type ROW-ID following which DB2 will automatically assign a new ...
Read MorePurpose and usage of subqueries in DB with the help of an example
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 subquery up to 15 levels.For example, if we have below ORDERS table in DB2.ORDER_IDORDER_TOTALZ223451267Z629986734Z569028815Z5691178990Z56915432Z5691877453If 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 sub query.ExampleSELECT ORDER_ID FROM ORDERS WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM ORDERS)The ...
Read MoreWhat are the limitations of using OUTER JOIN on a DB2 table?
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. Also two or more conditions in WHERE clause can only be used with AND logical operator, other logical operators such as OR, NOT is not allowed.The functions to handle NULL operators such as VALUE and COALESCE could not be used with the OUTER JOINS.For example, if we have below 2 ...
Read MoreHow to provide & remove user access to/from DB2 object? Give the DB2 commands?
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 and REVOKE command will remove the user access.For example, if we have to provide SELECT and UPDATE access on the ORDERS table to user REL123X then we will fire below command.GRANT SELECT, UPDATE ON ORDERS TO REL123XIf we want to provide INSERT access, then we will use the below command.GRANT ...
Read MoreHow will you keep the locks on the resources even after a ROLLBACK?
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 the locks held by the DB2 are released by default.If we want to keep a lock on resources even after rollback, then we have to use ON ROLLBACK RETAIN LOCKS instead of ROLLBACK.For example, consider a cursor ORDER_CUR which is declared with FOR UPDATE OF clause. Therefore, as soon as ...
Read MorePurpose and usage of SAVEPOINT in COBOL-DB2 program
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 a named SAVEPOINT statement and after that at any point of time we can rollback the changes to this savepoint using ROLLBACK statement.Practically, we can have multiple SAVEPOINTS in our COBOL-DB2 program and we can jump back to any of these SAVEPOINTS using ROLLBACK. This will revert all changes made ...
Read MoreWhat is STORED PROCEDURE in a DB2? How will you create a new stored procedure?
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 MoreHow to find out the PLANS which will be impacted if a index is dropped?
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 MoreDifference between SCALAR and COLUMN function
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