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
Articles by Mandalika
Page 22 of 41
How 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 MoreHow to create a DB2 segmented tablespace TABSPAC2 in storage group STOGRP1?
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 MoreHow to add a row compression to a DB2 table TAB1?
A compression is used to save the DB2 disk space. The compression can be used either at row level or at a page level. In order to add a row compression in a DB2 table, we can give the following command−ALTER TABLE DBSET1.TAB1 COMPRESS YES STATICWe have to use ALTER TABLE for the compression of the DB2 table. The ALTER TABLE reserved words are followed by the name of DB2 table qualified by database. COMPRESS YES STATIC will complete the row compression for the said table.
Read MoreHow to delete a DB2 table TAB1?
We can delete the unused tables in the DB2. However, we must keep in mind that if we delete a table then all the indexes associated with the table are also dropped. Moreover, the triggers and views for the deleted table will become inaccessible. To delete any table in DB2, we can issue below command.DROP TABLE DBSET1.TAB1The DROP TABLE reserved word will be followed by the name of the table qualified by the database. The mentioned table will be deleted from the DB2 permanently.
Read MoreHow to change the length of the Name column from CHAR(20) to CHAR(50)?
DB2 gives us an option of modifying the attribute of the existing column in a table. We have to use the ALTER COLUMN parameter with ALTER TABLE as below in order to achieve this.ALTER TABLE DBSET1.TAB1 ALTER COLUMN NAME SET DATATYPE CHAR(50);The ALTER TABLE reserved words are followed by the name of the table qualified by a database, which is DBSET1.TAB1 in this case. Similarly, ALTER COLUMN is followed by the name of the column which needs to be modified, which NAME (of the student) in this case.In the SET DATATYPE parameter, we can pass the ...
Read MoreHow to create a table TAB2 having same attributes & columns as for table TAB1
DB2 gives us an option to copy the structure of an existing table to a new table. To copy the attributes and column of table TAB1 to a new table TAB2 we can use the following command−CREATE TABLE DBSET1.TAB2 LIKE DBSET1.TAB1The CREATE TABLE reserved words are followed by table name. The table name needs to be qualified by a database in which it will reside. In this case this new table is TAB2 and its database is DBSET1.The LIKE parameter is used after that followed by the name of the original table qualified by its database i.e. DBSET1.TAB1The important ...
Read MoreHow to create a view on table TAB1 for column Name, age, enrollmentId & age > 10 years. >
A view is an alternative way of representing the data stored in a table. A view can be used to increase the performance of the query since the view contains very limited rows as compared to its source table. We can use the below command to create a view on an existing table TAB1.CREATE VIEW AGEVIEW (NAME, AGE, ENROLLMENT_ID) AS SELECT NAME, AGE, ENROLLMENT_ID FROM TAB1 WHERE AGE > 10;We have to use CREATE VIEW reserved words in order to create a new view. This will be followed by the name of the view (AGEVIEW).The columns ...
Read MoreForeign key referencing of two DB2 tables
A foreign key is a column in a table that establishes a referential link with another table. A foreign key can be defined during creation of table (CREATE TABLE command) or it can be defined by modifying the table (ALTER TABLE command). However, before defining any key as foreign key, make sure that an index is built up on that column. We can use the below command to define an existing column CLASS in table TAB1 as a foriegn key which links to table TAB2.ALTER TABLE TAB1 ADD FOREIGN KEY (CLASS) REFERENCES CLASSDATA (CLASS_ID);The ALTER TABLE reserved words are followed ...
Read More