DB2 commands cheat book

Some DB2 commands in brief : 

1. Connecting to database 

    $ db2 'CONNECT TO [DATABASE NAME]'

2. Disconnect from database

    $ db2 'terminate'

3. Creating Schema 

    $ CREATE SCHEMA [SCHEMA_NAME] AUTHORIZATION [AUTHORIZATION NAME]

4. Creating table:

    $ db2 " CREATE TABLE  USERS (

              userid varchar(30) NOT NULL, 

              password varchar(40) NOT NULL,

              insertedat TIMESTAMP DEFAULT CURRENT TIMESTAMP,

              primary key(userid)

        ) IN USERSPACE 1 "

Here : 

VARCHAR is datatype of the column. primay key defines the primary key for the table. DEFAULT specifies the default value when nothing is specified while inserting row in table.

5.  Inserting values in table:

     $ db2 "insert in to users(userid, password) values ('pgadekar', 'mypassword')"

     Here the above value will be inserted as row in table with values for column userid and password 

and for INSERTEDAT will be assigned the default value i.e. current time stamp.

6. Select rows from table : 

    $ db2 'SELECT * FROM USERS'

7. Delete rows from table : 

   $db2 "delete from users where userid = 'pgadekar'"

8. Update rows in table 

   $db2 "update users set password = 'newpass' where userid='pgadekar'"

9. Inserting data from CSV file 

    assume content of csv file are as : 

     users.csv

    ---------------x------------x---------------

    'pgadekar','mypassword'

    'vijay','hispassword'

    ---------------x------------x---------------

    

    $ db2 'import from users.csv of del modified by coldel | commitcount 100  insert in to users (username, password)'

    This will upload the csv file data in to table users. Commit operation will be performed after every 100 rows. 

10. Running sql file : 

      sample sql  file content:

      schema.sql

    ---------------------------------x---------------------------x------------------------

     CREATE TABLE  USERS (

              userid varchar(30) NOT NULL, 

              password varchar(40) NOT NULL,

              insertedat TIMESTAMP DEFAULT CURRENT TIMESTAMP,

              primary key(userid)

        ) IN USERSPACE 1@

    ---------------------------------x---------------------------x------------------------

    $db2 -td@ -f schema.sql 

    here @ specifies the deliminator. You can use any.


11.  Dropping table: 
      $db2 'DROP TABLE [TABLE_NAME]'


12.  Getting table schema: 
      $db2 "DESCRIBE TABLE [TABLE_NAME]"