Create Table in DB2 Mainframe

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”.

Note When you create a variable block, make sure to give record format as VB(variable Block).

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(–).

Follow @tutorial_brain

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.

Note Table and view are important in DB2 mainframe synonyms are rarely used

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.

Note DROP is different from DELETE. In DELETE we can give WHERE clause to delete a particular row in a particular record but in case of DROP, it will delete the table entirely.

Syntax: DROP TABLE NAME;
                DROP COLUMN NAME;
Example, DROP TABLE STORE;
                 DROP TABLE ITEM;

DB2 Create Table

How to create views in DB2

Example

ALTER TABLE in DB2

DB2 DROP A TABLE

Tutorials for all brains!