Some DB2 commands and queries

Commands

Switch to db2inst1 user

su - db2inst1

Start and stop DB. All db2 commands should be between these two

db2start

db2 ...

db2stop

List all databases in the instance

db2 LIST DATABASE DIRECTORY

Connect to a certain database

db2 connect to MY_DATABASE_NAME

List all schemas

select schemaname from syscat.schemata

List all tables in all schemas

db2 list tables for all

List tables in a certain schema

db2 list tables for schema DB2INST1

Do a SQL Query

db2 "select * from MY_TABLE"

Queries

Datetime vs Timestamp. It looks timestamp is better, at least it was a better fit in all cases I have used it so far. Nir's Answer: Should I use field 'datetime' or 'timestamp'?

CREATE TABLE mytable (id INTEGER, made_on TIMESTAMP)
INSERT INTO mytable (id, made_on) VALUES ( 1, TIMESTAMP '2005-05-13 07:15:31.123456789')
INSERT INTO mytable (id, made_on) VALUES ( 2, TIMESTAMP '2005-05-13 07:15:31')
INSERT INTO mytable (id, made_on) VALUES ( 3, DATE '2005-05-13')
SELECT * FROM mytable;

ID          MADE_ON                   
----------- --------------------------
          1 2005-05-13-07.15.31.123456
          2 2005-05-13-07.15.31.000000
          3 2005-05-13-00.00.00.000000

Sequences

Drop and create a sequence

DROP SEQUENCE CUSTOMER_NUMBER_SEQ RESTRICT;
CREATE SEQUENCE CUSTOMER_NUMBER_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 0 NO MAXVALUE CYCLE CACHE 4;

Select the next one

SELECT NEXT VALUE FOR CUSTOMER_NUMBER_SEQ FROM SYSIBM.SYSDUMMY1;

1

Select next one with padding zeros

SELECT LPAD(NEXT VALUE FOR CUSTOMER_NUMBER_SEQ, 8, '0') FROM SYSIBM.SYSDUMMY1;

00000002

Select previous one (most recently generated)

SELECT PREVIOUS VALUE FOR CUSTOMER_NUMBER_SEQ from SYSIBM.SYSDUMMY1;

2

Primary keys and Foreign keys

Create my users table. Make primary from definition.

DROP TABLE "T_USER";
CREATE TABLE "T_USER"(
 "USER_ID" VARCHAR(16) NOT NULL PRIMARY KEY,
 "PASSWORD" VARCHAR(32) NOT NULL,
 "FULLNAME" VARCHAR(20) NULL,
 "PRIVILEGE" INT NOT NULL,
 "CREATE_TIME" TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 "UPDATE_TIME" TIMESTAMP NULL
);

CREATE UNIQUE INDEX "USERNAME_UNIQUE" ON "T_USER" ("USER_ID");
COMMENT ON COLUMN T_USER.PRIVILEGE IS 'PRIVILEGE 0:Admin, 1:Editor, 2:Regular';

Create projects table. Make primary key by altering the table.

CREATE TABLE "T_PROJECT" (
 "PROJECT_CODE" VARCHAR(5) NOT NULL,
 "PROJECT_NAME" VARCHAR(60)
);

CREATE UNIQUE INDEX "T_PROJECT_PK" ON "T_PROJECT" ("PROJECT_CODE");
ALTER TABLE "T_PROJECT" ADD CONSTRAINT "T_PROJECT_PK" PRIMARY KEY ("PROJECT_CODE");

Finally create a mapping table, with two foreign keys:

DROP TABLE "T_USER_PROJECT";
CREATE TABLE "T_USER_PROJECT" (
 "USER_ID" VARCHAR(16) NOT NULL,
 "PROJECT_CODE" VARCHAR(5) NOT NULL,
 CONSTRAINT "USER_ID_FK" FOREIGN KEY (USER_ID)
  REFERENCES T_USER (USER_ID) ON DELETE RESTRICT,
 CONSTRAINT "PROJECT_CODE_FK" FOREIGN KEY (PROJECT_CODE)
  REFERENCES T_PROJECT (PROJECT_CODE) ON DELETE RESTRICT
);

This is how the tables relate:

Insert some data:

INSERT INTO T_USER (USER_ID,PASSWORD,FULLNAME,PRIVILEGE) VALUES
 ('admin',  'admin',  'Admin',  0),
 ('editor', 'editor', 'Editor', 1),
 ('user',   'user',   'User',   2);

INSERT INTO T_PROJECT (PROJECT_CODE,PROJECT_NAME) VALUES
 ('58000','Project0'),
 ('58001','Project1');

INSERT INTO T_USER_PROJECT (USER_ID, PROJECT_CODE) VALUES
 ('admin',  '58000'),
 ('admin',  '58001'),
 ('editor', '58000'),
 ('user',   '58001');

Inner join:

SELECT 
 P.PROJECT_CODE AS projectCode,
 P.PROJECT_NAME as projectName,
 M.USER_ID AS userId,
 U.PRIVILEGE AS privilege,
 CASE WHEN (U.PRIVILEGE = 0) THEN 'Y' ELSE 'N' END as admin,
 CASE WHEN (U.PRIVILEGE = 1 OR U.PRIVILEGE = 0) THEN 'Y' ELSE 'N' END as editor
FROM T_PROJECT P
INNER JOIN T_USER_PROJECT M ON M.PROJECT_CODE = P.PROJECT_CODE
INNER JOIN T_USER U ON M.USER_ID = U.USER_ID
AND M.USER_ID = 'admin'
AND M.PROJECT_CODE = '58000';

See more:

• IBM Knowledge Center - Sequence reference
• Stackoverflow - SQL Inner-join with 3 tables?
• IBM Knowledge Center - Ways to join data from more than one table