DB2 Database Basic Text Based Operation And Monitoring

We can connect to DB2 with graphical or text based. IBM DB2 Data Studio or DB2 Control Center are graphical tools to operate with database object like create database, table, insert/update/delete records and run queries using graphical command editor.

Also we can work with DB2 on text based using DB2 Command Window. Text based is good for Linux Server OS and remote administration using SSH because its usually use a lot of text based commands.

  1. Open DB2 Command Window.
  2. Connect to sample database using syntax: db2 connect to SAMPLE user <username> and press enter, input password.
  3. Do database operation
    • Create schema with name ‘express’: db2 create schema express authorization db2admin
    • Create table ‘testers’: db2 create table express.testers (t_id integer not null generated always as identity (start with 1, increment by 1, no cache ), t_fname varchar(50), t_lname varchar(50), constraint pk_t_id primary key (t_id))
    • Add new record: db2 insert into express.testers (t_fname, t_lname) values ('David','Chantal')
    • Multiple insert records db2 insert into express.testers (t_fname, t_lname) values ('Hannah', 'Gunner'), ('Chavi', 'Krauss')
    • Show records: db2 select * from express.testers
    • Edit record: db2 update express.testers set t_name='Alex' where t_id=1
    • Delete record: db2 delete from express.testers where t_id=2
    • Create second table ‘tester_emails’: db2 create table express.tester_emails (t_id integer, email varchar(150))
    • Insert record on ‘tester_emails’: db2 insert into express.tester_emails values (1, 'david@anywhere.com')
    • Create view ‘myview’: db2 create view express.myview as select t_fname, t_lname, email from express.testers left join express.tester_emails on express.testers.t_id = express.tester_emails.t_id
    • Show records from view: db2 select * from express.myview
    • Add new column to table ‘testers’: db2 alter table express.testers add column t_city varchar(50)
    • Show table structure: db2 describe table express.testers
    • List all schema in database: db2 select schemaname from syscat.schemata
    • List all table: db2 list tables
    • Drop a view: db2 drop view express.myview
    • Drop a table: db2 drop table express.testers
  4. To monitor db2 process that currently running run db2 list application then press enter you will snapshot like below:
    db2 list application
  5. Open another DB2 Command Window and connect to sample database. Run db2 list application and you will get like below pic:
    db2 list application
    There are two processes running.
  6. To terminate database session run: db2 terminate. After that Run a query db2 select * from express.testers. It occurs error, connection does not exist.
  7. Run db2 list application, it appears other connection from second db2 command window still exist. We can run a queries from second command window.
  8. To stop all connection session run: db2 force application all then no connection exist.

Cheers,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s