- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 1659 Articles for Big Data Analytics
2K+ Views
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 More
143 Views
We can use SCROLLABLE CURSOR to directly point the cursor to the mentioned relative position. The relative position is the position of the row in the result table from the current row. For example, consider the table below.ORDER_IDORDER_DATEA223672020-07-28A667562020-07-28A778902020-07-29A968322020-07-29If the cursor is currently pointing to the 2nd absolute row i.e, ORDER_ID A66756 then the relative +2 position will be ORDER_ID A96832 and relative -1 position will be ORDER_ID A22367.The syntax to use relative position in FETCH statement is−EXEC SQL FETCH RELATIVE +2 ORDER_CURR INTO :ORDER-ID, :ORDER-DATE END-SQLRead More
273 Views
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 More
1K+ Views
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 More
438 Views
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
364 Views
A cursor can move only in forward direction, which means that it can extract the next row after every fetch. It is not possible to extract the previous row using a cursor.For example, if our resultant cursor contains following rows−ORDER_IDORDER_DATEA223672020-07-28A667562020-07-28A778902020-07-29A968322020-07-29If our cursor is currently pointing to 3rd row i.e. order id A77890 then the next fetch will point the cursor to the next row i.e. order id A96832. It is not possible to point the cursor to the previous order id i.e. A66756.In order to achieve this, we use the concept of SCROLLABLE CURSOR. The SCROLLABLE CURSOR can move both ... Read More
663 Views
The ATOMIC and NON ATOMIC clauses are used with the multi-row insert. ATOMIC is always processed by default if any of the options is not given. The ATOMIC clause states that if there is a failure while inserting any one row during multi-row insertion then the entire query will be failed and all the inserts will be rolled back.The NON ATOMIC clause is just the opposite of the ATOMIC clause. It is used when we have to insert and process all the rows individually in a multi-row insert. For example, this option can be used like below.MOVE 50 TO MAX-ROWS ... Read More
552 Views
If we want to insert a multiple rows in a DB2 table using a single INSERT command then we have to define the host variable array and move the row data we want to insert in that array. We need to define another variable in the working storage section with configuration S9(4) COMP which will hold the number of rows to be inserted. We can insert the multiple rows as below−MOVE 50 TO MAX-ROWS EXEC SQL INSERT INTO ORDERS (ORDER_ID, ORDER_DATE) VALUES(:ORDER-ID, :ORDER-DATE) FOR :MAX-ROWS ROWS END-EXECThe ORDER-ID and ORDER-DATE are host variables which should be ... Read More
180 Views
The host variable needs to be declared as an array for the multi-row fetch. Also, we need to define another variable in the working storage section with the configuration S9(4) COMP, which will store the value of the number of rows to be fetched in a single fetch call.We can give any name to this variable, here we have used MAX-ROW. Below is an example of declaring a host variable array and MAX-ROW.01 ORDER-ID PIC X(25) OCCURS 25 TIMES. 01 MAX-ROW PIC S9(4) COMP VALUE 25.We will fetch the cursor like below.EXEC SQL FETCH NEXT ROWSET FROM ORDER_CUR FOR ... Read More
803 Views
We can extract multiple rows from a DB2 table in a single FETCH call using a concept of multi row fetch.In a multi row fetch, we have to declare the cursor with the clause “WITH ROWSET POSITIONING”. The host variable in this case should be declared as an array.Therefore, in a single fetch statement the host variable array will be populated with the multiple row data. We can traverse in the host variable array in order to access this row data.For example, we can declare a multi fetch cursor like below−EXEC SQL DECLARE ORDER_CUR WITH ROWSET POSITIONING FOR ... Read More