Db2 Cheat Sheet for Development Cheat Sheet by angoca

Execution of a file in the console (db2clp)
 Semi-colon separated sentences:
db2 -t
 At sign separated sentences (when there is SQL PL code):
db2 -td@
Define a terminator character
--#SET TERMINATOR @
List all databases (aliases)
LIST DB DIRECTORY
Connect to a database (alias)
CONNECT TO mydb
Disconnect from a database
CONNECT RESET
TERMI­NATE
Get values from the enviro­nment (registry values)
* Current timestamp
VALUES CURRENT TIMESTAMP
 Connected user
VALUES CURRENT USER
 Current database
VALUES CURRENT SERVER
List all tables
LIST TABLES
LIST TABLES FOR SCHEMA myuser
LIST TABLES FOR ALL
Change current schema
SET CURRENT SCHEMA others­chema
Change the isolation level
SET ISOLATION RR
List all tables­paces with their status
LIST TABLES­PACES
Describe the estructure of the table
DESCRIBE TABLE mytable
Describe the result of a query
DESCRIBE SELECT * FROM mytable
Get help for a Db2 command
? command
Get help for a SQL code (SQLXXXX) or SQLstate (YYYYY)
? SQLXXX
? YYYYY

Grant on a table
GRANT SELECT, INSERT ON TABLE tbl1 TO user
Grant execution on a stored procedure
GRANT EXECUTE ON PROCEDURE prc1(INT, DATE) TO USER jdoe
GRANT EXECUTE ON SPECIFIC PROCEDURE mypr TO GROUP admins
Revoke on a table
REVOKE DELETE ON TABLE mytable FROM recur

Created by: Andres Gomez Casanova (@angoca)
Version: 2019-08-04
Get the most recent version at
https://github.com/angoca/db2-cheat-sheet/

This work is licensed under a Creative Commons Attrib­uti­on-­Sha­reAlike 4.0 Intern­ational License.

Create a schema
CREATE SCHEMA sch1
Create a table specifying primary key
CREATE TABLE tbl1 (col1 CHAR(1) NOT NULL PRIMARY KEY)
CREATE TABLE tbl2 (col1 INT NOT NULL, col2 DATE NOT NULL, PRIMARY KEY (col1, col2))
Create a table specifying tables­paces
CREATE TABLE tbl3 (col1 INT NOT NULL, col2 CHAR(1)) IN ts1 INDEX IN ts2
Create a table specifying schema
CREATE TABLE sch1.tbl4 (col1 INT)
Create a table with auto increm­ental column
CREATE TABLE tbl5 (col1 INT NOT NULL GENERATED AS IDENTITY)
Create a table like another one
CREATE TABLE tbl6 LIKE tbl1 IN ts1 INDEX IN ts2
Comment on table and column
COMMENT ON TABLE tbl1 IS 'Comment in table'
COMMENT ON COLUMN tbl1.col1 IS 'Descr­iption of the field'
Declare a temporary table (session schema)
DECLARE GLOBAL TEMPORARY TABLE tmp1 (col1 INT, col2 DATE) ON COMMIT PRESERVE ROWS
Create a global temporary tablespace
CREATE GLOBAL TEMPORARY TABLE tmp2 (col1 INT)
Create an index
CREATE INDEX idx1 ON tbl2 (col2)
Create a unique index
CREATE UNIQUE INDEX idx2 ON tbl5 (col1)
Drop an index
DROP INDEX idx1
Add a column (requires Reorg table)
ALTER TABLE tbl1 ADD COLUMN col3 timestamp
Change nullab­ility
ALTER TABLE tbl1 ALTER COLUMN col3 SET NOT NULL
Drop nullab­ility
ALTER TABLE tbl1 ALTER COLUMN col3 DROP NOT NULL
Rename a column
ALTER TABLE tbl1 RENAME COLUMN col3 TO new3
Drop column
ALTER TABLE tbl1 DROP COLUMN new3
Create a primary key constraint
ALTER TABLE tbl5 ADD CONSTRAINT pkt5 PRIMARY KEY (col1)
Drop primary key
ALTER TABLE tbl5 DROP PRIMARY KEY
Add identity
ALTER TABLE tbl2 ALTER col1 SET GENERATED ALWAYS AS IDENTITY
Restart identity
ALTER TABLE tbl2 ALTER col1 RESTART WITH 1
Drop identity
ALTER TABLE tbl2 ALTER col1 DROP IDENTITY
Create a foreign key
ALTER TABLE tbl5 ADD CONSTRAINT fkt5 FOREIGN KEY (col1) REFERENCES tbl11 (col1)
Create a check constraint
ALTER TABLE tbl1 ADD CONSTRAINT chk CHECK (col1 in ('a', 'b', 'c'))
Enforce a constraint
ALTER TABLE tbl1 ALTER CHECK chk ENFORCED
Not enforce a constraint
ALTER TABLE tbl5 ALTER FOREIGN KEY fkt5 NOT ENFORCED
Change the granul­arity of the locks
ALTER TABLE tbl1 LOCKSIZE TABLE
Drop a table
DROP TABLE tbl1
Rename a table
RENAME TABLE tbl2 TO table2
Truncate a table
TRUNCATE TABLE tbl1 IMMEDIATE
Create a sequence
CREATE SEQUENCE seq AS INTEGER
Restart sequence
ALTER SEQUENCE seq RESTART WITH 15
Create a stored procedure
CREATE OR REPLACE PROCEDURE prc1 (IN val INT, OUT ret DATE) SPECIFIC mypr BEGIN SET ret = (SELECT col2 FROM tbl2 WHERE col1 = val); END @
Create a trigger
CREATE TRIGGER cp_val AFTER INSERT ON tbl1 REFERE­NCING NEW AS n FOR EACH ROW INSERT INTO tbl2 VALUES (n.col1, n.col2)
Create a view
CREATE VIEW vw1 AS SELECT col2 FROM tbl1

Insert values on a table
INSERT INTO tbl3 VALUES (2, 'b')
INSERT INTO tbl3 VALUES (3, 'c'), (4, 'd'), (5, 'e') --Atomic
Insert certain columns
INSERT INTO tbl1 (col1) VALUES (6)
Insert values from a select
INSERT INTO tbl6 SELECT col1 FROM tbl1
Insert in temporary table
INSERT INTO sessio­n.tmp1 VALUES (1)
Update fields
UPDATE tbl3 SET col1 = 5, mycol2 = 'e' -–all table
UPDATE tbl3 SET col2 = 'd' WHERE col1 = 7
Merge (upsert)
MERGE INTO tbl3 AS t USING (SELECT col1 FROM tbl1) s ON (t.col1 = s.col1) WHEN MATCHED THEN UPDATE SET col2 = 'X' WHEN NOT MATCHED THEN INSERT VALUES (10, 'X')
Delete rows
DELETE FROM tbl1 -–all table
DELETE FROM tbl1 WHERE col1 > 5
Export
EXPORT TO myfile OF DEL SELECT * FROM tbl1
Import
IMPORT FROM myfile OF DEL INSERT INTO mytable1
Cursor
DECLARE cur1 CURSOR FOR SELECT * FROM tbl1
Load
LOAD FROM myfile OF DEL INSERT INTO tbl1
LOAD FROM cur1 OF CURSOR INSERT INTO tbl1
Query the status of the load in a table
LOAD QUERY TABLE tbl1
Set integrity
SET INTEGRITY FOR tbl1 IMMEDIATE CHECKED
Ingest
INGEST FROM FILE myfile FORMAT DELIMITED INSERT INTO tbl1
Get the next value from a sequence
VALUES NEXT VALUE FOR seq
INSERT INTO tbl3 (col1) VALUES (NEXT VALUE FOR seq)

Put a lock at table level
LOCK TABLE tbl1 IN EXCLUSIVE MODE
Execute a query without regard of commit rows
SELECT * FROM tbl1 WITH UR --RR,R­S,CS
Execute a query with only 5 rows
SELECT * FROM tbl1 FETCH FIRST 5 ROWS ONLY
Perform a query to a dummy table (dual)
SELECT 'Any string' FROM SYSIBM.SY­SDU­MMY1
Perform a query calling a function
SELECT HEX(col2) FROM tbl5
Call a function
VALUES HEX('A­nyT­ext')
Perform a cast
VALUES CAST('123' AS INTEGER)
Concat­enate
VALUES 'AnyText' || 5
VALUES 'AnyText' concat 5
Escape a single quote in a text field
VALUES 'Sinead o''Con­nor'
Query the database catalog
SELECT * FROM SYSCAT.TA­BLES
SELECT * FROM SYSCAT.TA­BAUTH
SELECT * FROM SYSCAT.RO­UTINES

Commit changes
COMMIT
Create a savepoint
SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS
Undo changes until savepoint
ROLLBACK TO SAVEPOINT sp1
Undo changes
ROLLBACK