Found 150 Articles for DB2

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.

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 More

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.

The foreign key of a DB2 table can be found using SYSIBM.SYSFOREIGNKEYS table and SYSIBM.SYSRELS table. The SYSFOREIGNKEYS is a DB2 system table which contains one row for every column of every foreign key. The SYSRELS table contains details about the referential constraints. In order to find out the foreign key(s) of any table, we can use the below SQL query.SELECT B.REFTBNAME AS PARENTTABLE, COLNAME FROM SYSIBM.SYSFOREIGNKEYS A, SYSIBM.SYSRELS B WHERE A.RELNAME = B.RELNAME AND B.TBNAME = 'TAB1' AND B.REFTBCREATOR = A.CREATORWe will join SYSFOREIGNKEYS and SYSRELS table for the columns RELNAME which stores the details regarding constraint name for ... Read More

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.

The ALIAS is basically a different name given to a particular object in a database. An ALIAS can be defined for a table, view or another alias and the existence of the object is not verified during the creation of the alias. The ALIAS is used to hide the location qualifier and we need SYSADM authority to create a new ALIAS. When the object is dropped, its corresponding ALIAS is not dropped.We can use the below command in order to create a new ALIAS for a table.CREATE ALIAS T1    FOR DBSET1.TAB1The CREATE ALIAS reserved words are followed by the ... Read More

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

A segmented table space is divided in multiple segments. A segment is defined as a contiguous set of fixed number of pages. This fixed number is defined during the table space definition using the SEGSIZE parameter. The SEGSIZE serves multiple purposes - It defines the tablespace as segmented and also defines the size of the segment.In a segmented tablespace, the rows of a table are stored in one or more segments and a particular segment can only have rows from one table.The segmented tablespace can be created by using the CREATE TABLESPACE command with SEQSIZE parameter as below.CREATE TABLESPACE TABSPA1 ... Read More

A tablespace is a collection of data files which resides inside a DB2 database. It is used to organize the data logically. Any DB2 database contains at least one tablespace and in the real world scenario there are multiple tablespaces within a database which are allocated for different business units. In order to create a new tablespace, we have to provide the command as below−CREATE TABLESPACE TABSPA1    IN DBSPAC1    USING STOGROUP STG1    PRIQTY 50    SECQTY 50    BUFFERPOOL BP02    ERASE NOThe CREATE AND TABLESPACE are the reserved words which are followed by the name of ... Read More