Found 150 Articles for DB2

DB2 provides a facility to insert a default value in case the user does not give any value for the column. For each DB2 data type there is a fixed default value which will be assigned to the column (which was defined with ‘DEFAULT’ parameter during table creation) if the value is not given during insertion.Below table gives the DB2 default values for CHAR, VARCHAR, TIMESTAMP and INTEGER.Data typeDB2 Default valueCHARSpacesVarcharEmpty string having length 0TIMESTAMPCurrent timestampINTEGERZero

Null in DB2 is defined as nothing. It is an unknown value. If we want to restrict NULL value in any column then the column should be defined with the “NOT NULL” parameter in CREATE TABLE.The “NOT NULL” will force the user to enter a value for the column. However, if we do not want to give any value for this column we can also place a “WITH DEFAULT” parameter which will allow DB2 to place the default value in case the user has not provided any value for the “NOT NULL” column.For example, if we have a column INVOICE_ID ... Read More

The foreign key is used to establish a referential constraint between the child table(in which column is defined as foreign key) and parent table (in which foreign key of the child table becomes primary key). For example if we have an ORDER table in which foreign key is defined as TRANSACTION_ID. This foreign key will refer to the TRANSACTION_ID column of TRANSACTIONS table. In this TRANSACTIONS table, TRANSACTION_ID will be the primary key. The parent table here is TRANSACTIONS table while the child table here is ORDERS table.The CASCADE rule of the foreign key states that when any entry is ... Read More

The DB2 table contains a number of columns whose value will remain unique in the entire table. Among these multiple columns only one column is selected as the primary key and the remaining keys are known as candidate keys.We can declare any candidate key as an alternate key. Which means that the value of this key cannot take duplicate value, however unlike primary key the primary index is not built on the alternate key.We can define alternate key while defining any table using a UNIQUE keyword. For example, if we want to make TRANSACTION_ID as an alternate key then−CREATE TABLE ... Read More

The SQLWARN3 field in SQLCA is used to detect the condition wherein the number of the resultant columns is greater than the number of host variables given in the query of a COBOL-DB2 program. The SQLWARN3 is a 1 byte field, which contains the value ‘W’when there is mismatch in number of columns returned by the query and number of host variables used.We can enquire the status of SQLWARN3 using IF or EVALUATE statements as in the below exampleA010-CHECK-ORDER. EXEC SQL    SELECT ORDER_DATE,          ORDER_TOTAL       INTO :ORDER-DATE,       FROM ORDERS   ... Read More

There are situations in which DCLGEN members are not used and the host variables declarations are done explicitly in the working storage section. However, due to these explicit declarations there are chances of human errors. One such error is declaring incorrect data length of COBOL equivalent host variables.For example, the host variable should have been declared as PIC X(24) but it was declared as PIC X(14) by mistake. In this case when the data transfer takes place from DB2 to COBOL program, the column data might get truncated due to the shorter length of the receiving host variable.We can detect ... Read More

The DCLGEN member contains two important sets of data.The table structure containing definitions of all the columns present in the table.The host variable declaration in equivalent COBOL data types.Including the DCLGEN member is not mandatory until we explicitly give the host variables declaration in the working storage section. But it is always considered as a good coding practice to include a DCLGEN member because it also contains the table structure using which the pre-compiler can perform the query column validation. Although, the query column validation is optional for the pre-compiler but it gives us possible errors in the precompilation stage ... Read More

The SQLCODE field of SQLCA is used to get the return code for the last executed SQL query from DB2 to COBOL program. Below are the range of return codes which SQLCODE field can take along with their significance.SQLCODE = 0 → Query executed successfully without any issue.SQLCODE > 0 → There was a warning issued while executing the query.SQLCODE < 0 → There was an error occurred while executing the query.Below is the sample paragraph where the usage of SQLCODE is demonstrated.A010-CHECK-ORDER. EXEC SQL    SELECT ORDER_DATE       INTO :ORDER-DATE,       FROM ORDERS     ... Read More

The SQLCA helps in the communication between DB2 and COBOL-DB2 program. The SQLCA has multiple fields which gives us different information regarding the last executed SQL query.The SQLCA is mandatory in a COBOL-DB2 program. However, if we do not give the SQLCA using the INCLUDE statement then the program compilation will fail and we will get the below error in the logs−“SQLCA is not defined as a data-name”

The DATE, TIME and TIMESTAMP DB2 data types occupy 4, 3 and 10 bytes respectively. The memory occupied by the CHAR data types is occupied as per the size given. Below table shows the equivalent COBOL data types for CHAR, DATE, TIME and TIMESTAMP.DB2 data typeDB2 BytesCOBOL equivalentCOBOL bytesCHAR(z)zPIC X(z)zDATE4PIC X(10)10TIME3PIC X(8)8TIMESTAMP10PIC X(26)26