A COBOL-DB2 program can have multiple DB2 SQL statements. In order to implement the common error-trapping for all the SQL statements in a COBOL-DB2 program, we will use WHENEVER statement.The WHENEVER statement can direct the control to the error handling section or routine based on the value returned in SQLCODE field of SQLCA. For example, we can declare WHENEVER statement in COBOL-DB2 program as below−EXEC SQL    WHENEVER SQLERROR GOTO ERROR-ROUTINE END-EXECIn the above example, the SQLERROR parameter will be set only when the value of SQLCODE will be lesser than zero, which indicates that there was some error occurred ... Read More

SQLCA stands for SQL-Communication Area. It is a medium through which DB2 can communicate with the COBOL program. In a typical COBOL-DB2 program, there are many SQL statements used. The main purpose of SQLCA is to inform the COBOL program about the status and other details of the most recently executed SQL query.The SQLCA has a total length of 136 bytes and it is composed of various fields like SQLCODE, SQLERRD, SQLWARN, etc. Each of these fields give specific details of the last executed SQL query.For example, SQLCODE returns the DB2 error code (if any), SQLWARN returns the warning issued ... Read More

The non-clustered index is just the opposite of the clustered index. In a non-clustered index, it is not necessary that the data rows having similar index keys should reside in the same page. This index is suitable if we have to traverse through the table.For example, if we take the scenario where index keys of the table are the whole number - 2, 12, 14, etc. Then the non-clustered index structure would look like below−

In a CLUSTERED INDEX of a DB2 table, the data rows (table rows) with the similar index keys are stored in the same page. For example, If we have 4 index keys - T5623, T5611, Z9786 and Z9078. So the data rows with similar keys T5623 and T5611 will be stored in the same page and other similar keys Z9786 and Z9078 will be stored together on the other page.The Clustered index structure has 2 types of pages i.e., Index page and data page. The index page stores all the index key values and points to the data page so ... Read More

As per the standard DB2 definitions, timestamp holds 10 bytes in DB2 storage and 26 bytes in corresponding COBOL storage (PIC X(26)). It is in the format YYYY-MM-DDHH. MM.SS.NNNNNN. Where, YYYY:- Year | MM:- Month | DD:- Date | HH:- Hour | MM:- Minutes | SS:- Seconds | NNNNNN:- MillisecondsAs per the scenario given in the question, the timestamp is “2020-07-01 23:14”. Clearly, the format of the timestamp is incorrect as the correct format is YYYY-MM-DDHH. MM.SS.NNNNNN. In this case if we will try to insert this incorrectly formatted timestamp in a DB2 table column which is defined as timestamp ... Read More

To maintain the integrity of a DB2 table the primary key is always unique in the entire table. For example, if we have a DB2 table ORDERS which stores all the orders and the primary key of the table is column ORDER_ID. Then there can be only a single row having a particular order id. This will be useful to identify an order distinctly.If we try to insert or update a row in a DB2 table having duplicate primary key using a COBOL-DB2 program, we will get DB2 error code -803. As per the IBM documentation - 803 error code ... Read More

To start a specific tablespace within a DB2 database we can use below panel command.START DATABASE (DSNDB01) SPACENAM(TABSPAC1)Using the START DATABASE command, we can also start database and indexspace.

A database contains multiple components like tablespace, indexspace, index, tables, etc. We can find out all the components within the database using the below panel command.DIS DB(DSNDB01)

There are multiple ways in which we can estimate the size of the DB2 database. Few of them are listed below−By using inbuilt get_dbsize_info function.By using DB2 active transaction logs.The size of the dataset which was used with UNLOAD utility can be checked.Table/Index dataUsing STOSPACE utility in JCL as below//STEP1 EXEC DSNUPROC //SYSIN DD * STOSPACE DATABASE DSNDB01 //*

The pending state is set when the image copy is required for the table space or when the INDEX is in rebuild status. In this case a COPY PENDING/ REBUILD PENDING flag is set. We can repair this state by using the below JCL step.//STEP010 EXEC DSNUPROC REPAIR SET INDEX IDX1 NORBDPENDThis utility can also be used to repair the tablespace. For this we can use the REPAIR SET TABLESPACE statement. This is followed by the name of index or tablespace.