Found 150 Articles for DB2

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 More

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”.

We will make use of NULL indicator in order to store a NULL value in any column of a DB2 table. Firstly, we should move a value -1 in the NULL indicator in our COBOL-DB2 program. After that we execute UPDATE or INSERT query to store the NULL value.For example, if we have to update a NULL value in the ORDER_DESCRIPTION column of the ORDER table where ORDER_ID is 3345612.A020-UPDATE-ORDERS.    MOVE -1 TO ORDER-DESCRIPTION-N    MOVE SPACES TO ORDER-DESCRIPTION-DATA    EXEC SQL       UPDATE ORDERS          SET ORDER_DESCRIPTION =           ... Read More

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 More

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 More

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.

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.

The DB2 column can store NULL value if it is explicitly not defined with the ‘NOT NULL’ option. However, COBOL does not have any NULL concept. In order to handle these NULL values, COBOL programs use spaces for character columns and zeros for integer columns having NULL value.However, the main challenge is how to detect that particular column is having NULL value and how to move spaces/zeros into corresponding host variables. In order to overcome this, the DCLGEN utility generates a NULL indicator for each DB2 column which can hold a null value. The NULL indicator is a 2 byte ... Read More

The formula for converting DECIMAL DB2 data type to COBOL equivalent is−DECIMAL(p,q) = PIC S9(p-q)V(q). Where V indicates implied decimal.The DECIMAL(7,3) can take a sample value as 7861.237 and this can be converted to COBOL equivalent as PIC S9(7-3)V(3) = PIC S9(4)V(3).

The host variables are used to transfer the data from DB2 to program and vice versa.For each DB2 table column we have a COBOL equivalent host variable defined. The host variables can be generated automatically using the DCLGEN utility or we can explicitly give the host variables in the working storage section of the COBOL-DB2 program.The COBOL equivalent of various DB2 data types is mentioned in the table below.DB2 data typesCOBOL equivalentSMALLINT - 2 bytesPIC S9(4) COMPINTEGER - 4 bytesPIC S9(9) COMPTIMESTAMP - 10 bytesPIC X(26)CHAR - 5 bytesPIC X(5)