Found 150 Articles for DB2

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.

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.

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 More

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 More

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 More

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

We can add a new column in an existing table as per the business requirements. Similarly, we can also remove a column from the table. This could be done using the ALTER table command as below.ALTER TABLE TAB1 ADD COLUMN ADDRESS VARCHAR(100);The ALTER TABLE reserved words are followed by the name of the table which we want to amend. In this case it is TAB1.To add a new column we will use ADD COLUMN and to remove the column we will use the REMOVE COLUMN parameter. This is followed by the name of the column. If this is an addition ... Read More

Constraints are used to restrict the data inserted at a particular column. Constraints can be used in such a way that a value can only be inserted if it satisfies the condition given in constraints. We can give the below parameter during CREATE TABLE command to add a constraint.CREATE TABLE DBSET1.TAB1    (STUDENT_ID CHAR(10) NOT NULL,    ENROLLMENT_ID CHAR(20) NOT NULL,    NAME VARCHAR(50),    AGE SMALLINT CONSTRAINT NUMBER CHECK    (AGE >=3 AND AGE

An index is a lookup table that optimizes the searching of data. An index defined on any table can increase the query speed. The index can be built up on any column of the table and DB2 will generate a logical structure at backend. This will facilitate the search on the table using indexed columns effectively.An index can be unique and non-unique. To create a new unique index at any table we can use below command.CREATE UNIQUE INDEX ENROTAB1    ON TAB1 (ENROLLMENT_ID) ASC;The CREATE UNIQUE INDEX reserved words are followed by the name of the unique index which we ... Read More

A table is a logical structure of a data in a DB2. A table consists of a column which represents the attribute and rows represents the entity. Below command can be issued in order to create a new DB2 table.CREATE TABLE DBSET1.TAB1    (STUDENT_ID CHAR(10) NOT NULL,    ENROLLMENT_ID CHAR(20) NOT NULL,    NAME VARCHAR(50),    AGE SMALLINT    PRIMARY KEY (STUDENT_ID));The CREATE TABLE reserved words are followed by the name of the table which we have to create in the format . Here DBSET1 is the database and TAB1 is the table.The name of the column has to be ... Read More