Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
DB2 Articles
Page 10 of 13
How to reorganize the DB2 tablespace TABSPAC1 to reclaim fragmented space?
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.
Read MoreUpdating and sampling of catalog statistics for DB2 tablespace
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.
Read MoreHow to start a DB2 database DSNDB001 using the command panel?
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.
Read MoreHow to recover a DB2 table space TABSPC1 using the image copy TOLASTCOPY?
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 MoreHow to do a full & incremental MERGECOPY for a DB2 table TAB1?
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 MorePurpose and table creation syntax of "Created temporary table
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 MoreHow to get the list of all COBOL-DB2 programs using a DB2 table TAB1?
SYSIBM.SYSTABAUTH is a DB2 system table which records the privileges that users/program hold on tables and views. We can use this table to find out the list of programs accessing a particular table and what action the program is performing on the table like SELECT, UPDATE, INSERT or DELETE. The below SQL query can be fired on SYSTABAUTH in order to get list of programs.SELECT GRANTEE, SELECTAUTH, UPDATEAUTH, INSERTAUTH, DELETEAUTH FROM SYSIBM.SYSABAUTH WHERE GRANTEETYPE = ‘P’ AND TNAME = ‘TAB1’The column SELECTAUTH, UPDATEAUTH, INSERTAUTH and DELETEAUTH represents SELECT, UPDATE, INSERT and DELETE authority respectively. In the WHERE clause we will ...
Read MoreHow to find out all the indexes for a DB2 table TAB1?
To find out all the indexes built on the DB2 table TAB1 we can use the DB2 system table SYSIBM.SYSINDEXES. The SYSINDEXES database has one row for every index present in DB2. We can find indexes built on a particular table using the below SQL query.SELECT NAME, UNIQUERULE, CLUSTERING FROM SYSIBM.SYSINDEXES WHERE TBNAME=’TAB1’The column UNIQUERULE in the SELECT statement returns ‘P’ for primary index and ‘U’ for alternate index. The CLUSTERING column will be returned as ‘YES’ for clustered index and ‘NO’ for non-clustered index.
Read MoreHow to find the primary key of a DB2 table TAB1?
We can find the primary key of any table using the SYSIBM.SYSCOLUMNS table. The SYSIBM.SYSCOLUMNS is a DB2 system table which contains one row for every column of each table. It also contains the data related to the views. Below SQL query can be fired in order to find the primary key of a particular table.SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = 'TAB1’ AND KEYSEQ > 0 ORDER BY KEYSEQ ASC;We will use our table name in the TBNAME column of SYSCOLUMNS table using the WHERE clause and KEYSEQ > 0 will return only primary keys.
Read MoreHow to image copy the entire DB2 table TAB1 into a dataset?
The image copy allows us to download or copy the DB2 table into a mainframe dataset. There are two types of Image copy i.e. Full image copy and Incremental image copy. The full image copy is used to take the backup of the entire table. The incremental image copy refers to the differential backup. In order to take the full image copy of the DB2 table we can use the below JCL step.//STEP1 EXEC DSNUPROC //SYSCOPY DD DSN=TEST.TAB1.COPY, UNIT=SYSDA, VOL=SER=CPY01I, // SPACE=(CYL, (15, 1)), DISP=(NEW, CATLG, CATLG) //SYSOUT DD SYSOUT=* //SYSIN DD * COPY TABLESPACE TAB1SPAC /*We can use the ...
Read More