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
-
Economics & Finance
Articles by Mandalika
Page 18 of 41
How will you keep the CURSOR open after firing COMMIT in a COBOL-DB2 program?
Whenever we issue a COMMIT statement, all the open cursors will get closed. This is a very common case when we have to frequently use the commit statement after a UPDATE while working with a cursor. In this case we can use the “WITH HOLD” clause during the cursor declaration.The “WITH HOLD” clause will keep the cursor open even after firing the COMMIT statement. We can give the “WITH HOLD” clause in the following way.EXEC SQL DECLARE ORDER_CUR CURSOR WITH HOLD FOR SELECT ORDER_ID, TRANSACTION_ID FROM ORDERS WHERE ORDER_DATE = ‘2020-07-28’ END-EXEC
Read MoreWhat is the purpose and usage of "WHERE CURRENT OF" clause in a COBOL-DB2 program?
The “WHERE CURRENT OF” clause will place the exclusive lock on the row once the UPDATE statement is executed. The “WHERE CURRENT OF” clause will point to the most recently fetched row of the cursor.We can update the rows in cursor using “WHERE CURRENT OF” in the following way.CURSOR definition.EXEC SQL DECLARE ORDER_CUR CURSOR FOR SELECT ORDER_ID, TRANSACTION_ID FROM ORDERS WHERE ORDER_DATE = ‘2020-07-28’ END-EXECOPEN cursorEXEC SQL OPEN ORDER_CUR END-EXECFETCH cursor and Update rowSET WF-END-CURSOR-N TO TRUE PERFORM UNTIL WF-END-CURSOR-Y EXEC SQL FETCH ORDER_CUR INTO :ORDER-ID, :TRANSACTION-ID END-EXEC IF TRANSACTION-ID NOT = SPACES EXEC SQL ...
Read MoreWhat are the steps involved to use a CURSOR in any COBOL-DB2 program?
The CURSOR is used when we have to fetch multiple rows from a table. There are 4 steps involved in order to use a cursor in a COBOL-DB2 program.DECLARE cursor− In this step we will define the layout of the cursor. We will give the query we want to use. For example−EXEC SQL DECLARE ORDER_CUR CURSOR FOR SELECT ORDER_ID FROM ORDERS WHERE ORDER_DATE = ‘2020-07-28’ END-EXECOPEN cursor− Next we will open our cursor. This statement readies the cursor for data retrieval. For example−EXEC SQL OPEN ORDER_CUR END-EXECFETCH cursor− In this statement, we start fetching the data from DB2 and the ...
Read MoreExplain the scenario in which CURSOR should be used over a standalone SELECT statement?
The standalone SELECT statement is generally used when we use a primary or an alternate key in the WHERE clause. Therefore, In this case we are certain that the standalone select statement will return only one row since the primary key cannot have a duplicate value (multiple rows).If we want to query the database using a non-unique key which could return multiple rows from a DB2 table, we have to use a cursor to handle the multiple rows returned. We can access the cursor in a loop to read each row data one by one.For example, if we want to ...
Read MoreWhat we can conclude if the final value in the NULL indicator is -2?
A NULL indicator is a 2 bytes field which serves the multiple purpose. This indicator takes the value as -1 when any DB2 column has NULL value and it takes the value as 0 when DB2 column has a non NULL value.Although the main purpose of the NULL indicator is to check whether a column has a NULL value or not, this indicator can take a value of -2 as well. A -2 value in this indicator states that a NULL value was assigned to the host variable as a result of evaluating an expression with an arithmetic error, or ...
Read MoreWhat is the purpose of "NOT NULL WITH DEFAULT" clause used in DB2 table column?
When we define the DB2 table, we can declare any column as “NOT NULL” which means that in any case this column cannot store NULL value.Now if we try to store a NULL value in this column in our COBOL-DB2 program using -1 value in the NULL indicator then our query will fail. In this case the SQLCODE field of SQLCA will give the error code as -407. As per the IBM documentation -407 error code states that−“AN UPDATE, INSERT, OR SET VALUE IS NULL, BUT THE OBJECT COLUMN CANNOT CONTAIN NULL VALUES”.
Read MoreWhat is the result of count function in the NULL value present in DB2 table?
The COUNT function in DB2 is used to return the number of rows which satisfies the given criteria. The GROUP BY is used to divide the rows in the groups based on the criteria given in the query.If we perform the GROUP BY on INVOICE_ID and there are few rows having NULL value in INVOICE_ID then the null values form a separate group. For example, if we have below table.ORDER_IDINVOICE_IDA112343214A556113214A99867NULLA556713214A88907NULLA560126701On executing the query which performs GROUP BY on INVOICE_ID and counts the number of rows, we will get the result below.SELECT INVOICE_ID, COUNT(*) AS INVOICE COUNT FROM ORDERS GROUP BY ...
Read MoreWhat will happen if a NULL value is detected for a SQL statement in COBOL-DB2 program and the NULL indicator is not used?
There is no concept of NULL in COBOL language. Therefore, if any of the columns in DB2 table can hold NULL value then we need to give the NULL indicator in SELECT query in order to detect the NULL value.However, if we miss to give the null indicator in the SELECT query and any of the columns contains the NULL value then the query will fail and we will get a value of -305 in SQLCODE field of SQLCA. As per the IBM documentation -305 value states that.“THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number BECAUSE ...
Read MoreHow to verify NULL value in DB2 column data using COBOL paragraph?
In order to accomplish this, we will make use of NULL indicator after the SELECT query on the INVOICE_ID of the ORDERS table. If the NULL indicator has the value as -1 then we can conclude that INVOIVE_ID has a null value.Below is a COBOL paragraph for this−A010-CHECK-ORDER. EXEC SQL SELECT INVOICE_ID INTO :INVOICE_ID_DATA :INVOICE_ID_N FROM ORDERS WHERE ORDER_ID = ‘678542112’ END-EXEC IF INVOICE-ID-N = -1 MOVE SPACES TO INVOICE-ID-DATA END-IFINVOICE-ID-N is a null indicator here which is generated automatically by DCLGEN utility.
Read MoreHow will the COBOL-DB2 program behave when there is a mismatch between the host variable and number of columns in the SELECT statement?
In case there is a mismatch in the number of columns and number of host variables,the query will fail. There are two ways in which we can detect this condition.The SQLWARN3 field of SQLCA will get the value as ‘W’ in case there is a mismatch.In some installations the SQLCODE field for SQLCA gets the error code as -804 when there is a mismatch.We can use IF condition to check the value in SQLWARN3 or SQLCODE and direct the program processing accordingly.
Read More