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.

The tablespace reorganization is used to reorganize the data present in the system in order to reclaim the free space. This free space can be utilized to store the new data and therefore reorganization is very useful from a memory utilization point of view. We can reorganize any tablespace using DB2 REORG utility in JCL step as below.//STEP1 EXEC DSNUPROC,UID='IUJLU101.REORG', //UTPRINT DD SYSOUT=* //SYSIN DD * REORG TABLESPACE (DBSET1.TABSPAC1) //*The REORG TABLESPACE statement is followed by the name of the tablespace which needs to be reorganized qualified by dabasebase.

The DB2 RUNSTAT utility records the details of tablespace, table, index, etc in the system catalog. The RUNSTAT generates the data like space available in table space, indexes, access paths, etc. We can use the below JCL step to update the catalog statistics of DB2 tablespace TABSPAC2 and to sample 25% of the rows.//STEP1 EXEC DSNUPROC //UTPRINT DD SYSOUT=* //SYSIN DD * RUNSTATS TABLESPACE DBSET1.TABSPAC1    TABLE(ALL) SAMPLE 25    INDEX(ALL)The SYSIN parameter can be used with RUNSTATS TABLESPACE statement followed by tablespace name qualified by database name.

We can start the DB2 database by giving below command in panelDB2 ACTIVATE DSNDB001The DB2 ACTIVATE is followed by the name of the database which needs to be started.

The DB2 provides an utility RECOVER which is used to restore the tablespace. This utility uses image copy and DB2 logs to restore the changes. The following command can be used to recover the tablespace to the last image copy that was taken.RECOVER TABLESPACE DBSET1.TABSPAC1 TOLASTCOPYThe RECOVER TABLESPACE is followed by the name of tablespace qualified by the database in which it resides. The TOLASTCOPY parameter will restore the image copy in the following way. If the most recent image copy is full image copy, then the full image copy is restored in the tablespace. If the most recent image ... Read More

The MERGECOPY is just opposite of IMAGECOPY. This DB2 utlility is used to combine the multiple image copy datasets into a new full or incremental image copy. This is mainly used to restore the backup in the DB2 table.The below JCL step can be used for the incremental MERGECOPY for DB2 table TAB1 which is residing in the database DBSET1−//STEP010 EXEC DSNUPROC //COPY1 DD DSN=TEST.DB2.COPY1, DISP=(MOD, CATLG, CATLG), // UNIT=SYSDA, SPACE=(4000, (20, 20), , , ROUND) //COPY2 DD DSN=TEST.DB2.COPY2, DISP=(MOD, CATLG, CATLG), // UNIT=SYSDA, SPACE=(4000, (20, 20), , , ROUND) //SYSOUT DD SYSOUT=* //SYSIN DD * MERGECOPY TABLESPACE DBSET1.TBSPAC01 COPYDDN ... Read More

The declared temporary tables are also non-permanent tables but unlike created temporary tables they can be defined in the application program. The existence of a declared temporary table is limited to program execution. Since the declared temporary tables are created and deleted at the runtime, their entries are not stored into DB2 system table SYSIBM.SYSTABLES.The declared temporary table can be defined in the program using DECLARE GLOBAL TEMPORARY TABLE statement. The advantage of a declared temporary table over a created temporary table is that we can add constraints and indexes in these tables.

The Created temporary tables are non-permanent DB2 tables that exist as long as the process using this table is active. For example any batch program. The created temporary tables are best suited for the sequential access and hence system performance is not a concern over here.The created temporary tables can be created using the DDL statement CREATE GLOBAL TEMPORARY TABLE followed by the name of the temporary table. This statement makes the entry of the new table in DB2 system table SYSIBM.SYSTABLES with type column as ‘G’.The created temporary table should always be created using the above DDL statement before ... Read More

To get a DB2 view definition we can use the SYSIBM.SYSVIEW DB2 system table. The SYSVIEW table stores the details about all the views that exist in DB2. We can give below SQL statement to find the view definition.SELECT NAME, SEQNO, TEXT    FROM SYSIBM.SYSVIEWS WHERE NAME = ‘VIEW1’       ORDER BY SEQNO;The VIEW can have definitions in multiple lines denoted by the SEQNO. That is the reason we need to use ORDER BY SEQNO so as to give the view definition in correct order. The exact definition is available in the TEXT column.