DB/2 – LUW :- DDL & DML – Schema, Table & Adding Data

Background

Now that we have a sample database, let us start creating database objects and manipulating data.

Data Definition Language ( DDL )

Schema

government

syntax


CREATE SCHEMA [schema];

Sample


CREATE SCHEMA "government";

Table

government.city

Sample


DROP TABLE government.city;

create table "government"."city"
(

	  "state"  varchar(60) not null
	, "city"   varchar(60) not null

	, constraint "PK_Government_City"
	  primary key
	  (
		  "state"
		, "city"
	  )

)
;

Data Manipulation Language ( DML )

Table

Add data

Add data using INSERT/VALUES

Sample

/*
	DELETE
	FROM government.city
	WHERE 1=1
	;

*/
TRUNCATE TABLE "government"."city"
	REUSE STORAGE
    IGNORE DELETE TRIGGERS
    IMMEDIATE
    ;

insert into "government"."city"

( "state", "city")

VALUES
	  ( 'CA', 'San Francisco' )
	, ( 'CA', 'Sacramento' )
	, ( 'CA', 'Santa Clarita' )

	, ( 'GA', 'Augusta' )
	, ( 'GA', 'Atlanta' )
	, ( 'GA', 'Macon' )
	, ( 'GA', 'Savanna' )

	, ( 'NY', 'New York' )
	, ( 'NY', 'Sacramento' )

	, ( 'TX', 'Austin' )
	, ( 'TX', 'Dallas' )
	, ( 'TX', 'Houston' )
	, ( 'TX', 'San Antonio' )				


Add data using Table Function

Sample
/*

	DELETE
	FROM "government"."city"
	WHERE 1=1;

*/

TRUNCATE TABLE "government"."city"
    REUSE STORAGE
    IGNORE DELETE TRIGGERS
    IMMEDIATE
    ;

insert into "government"."city"
( "state", "city")

SELECT *

FROM TABLE
(

      VALUES
          ( 'CA', 'San Francisco' )
        , ( 'CA', 'Sacramento' )
        , ( 'CA', 'Santa Clarita' )

        , ( 'GA', 'Augusta' )
        , ( 'GA', 'Atlanta' )
        , ( 'GA', 'Macon' )
        , ( 'GA', 'Savanna' )

        , ( 'NY', 'New York' )
        , ( 'NY', 'Sacramento' )

        , ( 'TX', 'Austin' )
        , ( 'TX', 'Dallas' )
        , ( 'TX', 'Houston' )
        , ( 'TX', 'San Antonio' )               

)
;

Crediting

Insert multiple rows into DB2 database
Crediting Hogan & Esperento57
Link

One thought on “DB/2 – LUW :- DDL & DML – Schema, Table & Adding Data

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s