A table space is a logical entity in DB2 architecture. It is basically a collection of data files. A tablespace contains tables, indexes, objects, etc. To create a new tablespace we have to issue the following command.CREATE TABLESPACE TABSPA1    IN DBSET1    USING STOGROUP STG1    PRIQTY 50    SECQTY 50    BUFFERPOOL BP03The CREATE AND TABLESPACE are the reserved words which are followed by the name of tablespace we want to create. In this case it is TABSPA1.IN is a reserved word which is followed by the name of the database in which we want to create this ... Read More

A database is a structured collection of data which can be organized, maintained and updated easily. There can be multiple databases in a DB2 installation. The DB2 database can be created by giving the name of the database, storage group and bufferpool, as below−CREATE DATABASE DBSET1       STOGROUP (STG1)    BUFFERPOOL BP3;The CREATE and DATABASE are the reserved words which are followed by the name of the database we want to create. The STOGROUP parameter should be given with the name of the storage group and finally BUFFERPOOL parameter followed by the name of bufferpool.

Storage groups are basically the collection of volumes which hold the data that is present in the DB2 table. Basically it is the responsibility of DBA to maintain, add and update the storage groups in DB2.To create a storage group in a DB2 installation we have to give below command.CREATE STOGROUP STG1 VOLUMES (VOL1);The CREATE and STOGROUP are the reserved words which are followed by the name of the storage group. The volume parameter should be populated with the name of the volume for the storage group.

The access path gives us the path selected by the optimizers in order to fetch the result of the SQL query. It gives us an idea about what all indexes and parameters will be used by the optimizer.To get the details of the access path used for the SQL statements within COBOL-DB2 program we can use the EXPLAIN option during the BIND step. Below is the JCL step which can be used.//BIND EXEC PGM=IKJEFT01 //STEPLIB DD DSN=DIS.TEST.LOADLIB, DISP=SHR //SYSOUT DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(TB3) BIND PLAN(PLANA) - PKLIST(PACKA) - ACQUIRE(ALLOCATE) - ISOLATION (RS) - EXPLAIN(YES) /*The EXPLAIN BIND ... Read More

Packages are DB2 database objects which hold the executable forms of SQLs which are used in COBOL-DB2 programs. The packages are stored in a catalog table and contain the strategy/tables/columns/predicate associated with the SQL statements.If there is a DB2 table ORDERS_TEST in test environment and ORDERS_PROD in production environment, then we need two versions of COBOL-DB2 program (which will access these tables) — one for test and the other for production.Although both programs will be carbon copy of each other, the only difference lies in the SQL statements. The test version of the program will use table ORDERS_TEST in SQL ... Read More

The isolation level defines the degree to which the DB2 data which is being accessed in the COBOL-DB2 program is isolated with another parallelly executing COBOL- DB2 program. There are 4 main types of Isolation levels in DB2.Cursor stability (CS) - The cursor stability isolation level locks only the current row which the program is accessing. As soon as the program shifts to the next row, the lock in the previous row gets released. The cursor stability fetches only committed rows for the program to access. This is a default isolation level.Read stability (RS) - This isolation level places a ... Read More

The COBOL-DB2 program can place the lock into a DB2 table in two ways.When the SQL statement using that table is executed within the program.When the program is loaded in the main memory and it is ready to be executed. It acquires a lock on all the DB2 tables which are used in the SQL statements within the program.To acquire the lock on all DB2 tables once the program is loaded in the main memory or allocated to a thread, we have to BIND the plan using appropriate options and parameters. Below is a JCL step which can be used.//BIND ... Read More

The locks in DB2 are acquired on table and tablespaces to avoid the issues arising due to LOST UPDATE, DIRTY READ and PHANTOM.We need to define the lock parameter during the BIND package/plan step using the ACQUIRE option.A COBOL-DB2 program PROGA is using SQL statements to access table TA. If we need to place a lock on the table only when that particular SQL statement is executed within the program, then we need to define the BIND JCL step as below−//BIND EXEC PGM=IKJEFT01 //STEPLIB DD DSN=DIS.TEST.LOADLIB, DISP=SHR //SYSOUT DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(TB3) BIND PLAN(PLANA) - PKLIST(PACKA) - ... Read More

To prepare a COBOL-DB2 program for execution, we have to go through certain steps. These steps include pre-compilation - compilation - binding - linkediting.The pre-compilation is done using the utility DSNHPC. In the pre-compilation step all the SQL statements which are present in the source program are replaced by corresponding COBOL calls. All the SQL statements are taken in an object known as database resource module (DBRM) and passed on to the BIND step. The modified source code is passed on to the compilation step.In the compilation step, the modified source is compiled and the object module is generated. The ... Read More

In each execution of a COBOL-DB2 program, the timestamp of load module and package/DBRM are compared. If there is a change in the length of the variable (and no SQL change) in the program and it is compiled, then the load module will have the newly generated timestamp and on the other hand if the BIND is not performed then the package/DBRM would have an old timestamp. When this program is executed, the JCL step calling this program will fail with SQL error code -818.If we have a COBOL-DB2 program for which SQL statement is never going to change in ... Read More