DB2 Create Table
1. CREATE ====== Tables: STORE Table store id integer store name char(20) item id small int item receiving date date ITEM Table item id small int item name char(20) item price decimal(10,2) ITEM_QNTY
Views:
Extract important details from both the tables and keep it in a virtual table
called ‘STOREITEM’
store id
item id
item name
After deciding column and entering all fields and data types for your DB2 create table, go to option 15, it can differ in your product, you might not get option 15 directly, you might have option DB2 and inside that DB2 you might have option DB2 V10. So, please check your product properly. Then go to SPUFI, you can also use QMF for creating the table.
Here, we will get to SPUFI option 1, we have to give input filename “INPUT.FILE.HERE”, where we have to write SQL operation and then get the output of the SQL operation from “OUTPUT.FILE.HERE”.
The syntax for creating a table, write CREATE TABLE NAME(COLUMN1 DATA TYPE, COLUMN2 DATA TYPE, COLUMN3 DATA TYPE, COLUMN4 DATA TYPE). For Example, CREATE TABLE STORE(STORE_ID INTEGER, STORE NAME CHAR(20), ITEM_ID SMALLINT, ITEM_RECV_DATE DATE);
And save your table.
For giving comment in SPOFI we have to use two hyphens(–).
How to create views in DB2
Views are created as virtual table they are not a physical table if you delete the main table from which the view is created the view also gets deleted. Based on the situation we have to create the views and we have to analyze whether views are needed or not.
For creating view select the list of columns from the table from which you want to retrieve these fields.
Example
CREATE VIEW STOREITEM AS SELECT STORE_ID, ITEM.ITEM_ID, ITEM_NAME FROM STORE, ITEM;
Give semicolon for execution in COMMAND.
In place of view, we can create synonyms and alias as well.
ALTER TABLE in DB2
To add a new column, rename a column, changing the property of a new column like length or data type of a particular column in a table we use ALTER TABLE in DB2.
Syntax: ALTER TABLE NAME ADD COLUMN NAME DATATYPE; Example, ALTER TABLE STORE ADD STORE LOCATION VARCHAR(50);
For details go to DDL operation.txt.
The syntax for changing the name of a column: ALTER TABLE STORE RENAME COLUMN OLD COLUMN NAME TO NEW COLUMN NAME;
Example, ALTER TABLE STORE RENAME COLUMN STORE LOCATION TO STORE_LOC;
DB2 DROP A TABLE
The DB2 DROP A TABLE is used to delete a table permanently. When we use to drop all the views synonyms or indexes are automatically dropped.
Syntax: DROP TABLE NAME; DROP COLUMN NAME; Example, DROP TABLE STORE; DROP TABLE ITEM;