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 5 of 13
Explain 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 MoreUsage and syntax of INNER and OUTER JOIN in DB2
Problem: How to explain INNER JOIN and OUTER JOIN with the help of an example on ORDERS and TRANSACTION DB2 table.SolutionThe JOIN is used to combine data from one or more tables in DB2. There are two main types of JOIN — INNER JOIN and OUTER JOIN. The basic difference between them is, INNER JOIN is an intersection of two or more tables while outer join is union of two or more tables. Basically, INNER JOIN is used to combine the data from multiple tables using equal column value and on the other hand, in case of OUTER JOIN, if ...
Read MoreHow can you revert all the DB2 table changes done in a COBOL-DB2 program?
We can revert all the changes done on a COBOL-DB2 program using the ROLLBACK statement. However, ROLLBACK is only applicable until COMMIT has not been issued. If we have used a COMMIT statement, then ROLLBACK will revert all the changes made in DB2 tables after the last COMMIT point.For example, after the commit statement, we execute an UPDATE statement to modify the ORDER_PAID column of ORDERS table. After that if we fire ROLLBACK then the UPDATE on the ORDERS table will be reverted.ExampleEXEC SQL COMMIT END-EXEC EXEC SQL UPDATE ORDERS SET ORDERS_PAID = ‘YES’ WHERE ORDER_DATE = :WS-CURRENT-DATE ...
Read MoreWhat is the use of the VALUE function in a DB2? Explain with the help of an example
The purpose of VALUE function in DB2 is to check for NULL values and it can be used in place of NULL indicator or COALESCE function. The VALUE function replaces the column value with the given argument if it contains a NULL value.For example, if we have an ORDER table and we have to extract ORDER_ID and ORDER_DESCRIPTION from this table. The ORDER_DECRIPTION column can have NULL values.If this is the case, we have to replace ORDER_DESCRIPTION with SPACES, then we can use the below query:ExampleEXEC SQL SELECT ORDER_ID, VALUE(ORDER_DESCRIPTION, ‘ ‘) INTO :ORDER-ID, :ORDER-DESCRIPTION FROM ORDERS ...
Read MoreWhat is the usage of scrollable cursor for absolute positioning?
The SCROLLABLE CURSOR can be used to directly point the cursor position to the mentioned absolute position. The absolute position is the position of a particular row in the result table from the first row.We can fetch the absolute position by using ABSOLUTE parameter in the FETCH statement. For example, we have to declare a scrollable cursor as below.EXEC SQL DECLARE ORDER_CURR SCROLL CURSOR FOR SELECT ORDER_ID, ORDER_DATE FROM ORDERS WHERE ORDER_DATE = ‘2020-07-29’ END-SQLNow if we want to fetch the absolute 9th row then we will ...
Read MoreWhat is the difference between SENSITIVE and INSENSITIVE scrollable CURSOR with syntax
The INSENSITIVE SCROLLABLE CURSOR are sort of read only cursors in which the result table cannot change once the cursor is opened. The other applications also cannot update the INSENSITIVE SCROLLABLE CURSOR once it is opened. The SENSITIVE SCROLLABLE CURSOR, unlike INSENSITIVE are sensitive to changes made in the result table. The changes made by other applications will be reflected in the result table.We can declare SENSITIVE and INSENSITIVE SCROLLABLE CURSOR like below.EXEC SQL DECLARE ORDER_CURR SENSITIVE SCROLL CURSOR FOR SELECT ORDER_ID, ORDER_DATE FROM ORDERS WHERE ORDER_DATE ...
Read MoreWrite the syntax to declare a scrollable cursor on the ORDERS DB2 table.
A SCROLLABLE CURSOR can move in both forward and backward direction. In other words, it can fetch next as well as previous rows. A SCROLLABLE CURSOR is declared using the “SCROLL” clause in the DECLARE CURSOR.For example, if we want to declare a SCROLLABLE CURSOR on the ORDERS table then we have to declare the cursor like below.EXEC SQL DECLARE ORDER_CURR SCROLL CURSOR FOR SELECT ORDER_ID, ORDER_DATE FROM ORDERS WHERE ORDER_DATE = ‘2020-07-29’ END-SQL
Read More