DESCRIBE SELECT * FROM tbl1 ALTER TABLE tbl1 ALTER COLUMN col3 SET NOT

Db2 Cheat Sheet for development Get help for a Db2 command NULL
? command Drop nullability
Created by: Get help for a SQL code (SQLXXXX) or ALTER TABLE tbl1 ALTER COLUMN col3 DROP NOT
Andres Gomez Casanova NULL
(@angoca) SQLstate (YYYYY)
Version: ? SQLXXX Rename a column
2019-10-12 ? YYYYY ALTER TABLE tbl1 RENAME COLUMN col3 TO new3
Drop column
Get the most recent version at
https://github.com/angoca/db2-cheat-sheet/Db2CheatSheetForDev.pdf DDL ALTER TABLE tbl1 DROP COLUMN new3
Create a schema Create a primary key constraint
Execution of a file in the console (db2clp) ALTER TABLE tbl5 ADD CONSTRAINT pkt5
• Semi-colon separated sentences: CREATE SCHEMA sch1
PRIMARY KEY (col1)
db2 -t Create a table specifying primary key
Drop primary key
• At sign separated sentences (when there is CREATE TABLE tbl1 (col1 CHAR(1) NOT NULL
PRIMARY KEY) ALTER TABLE tbl5 DROP PRIMARY KEY
SQL PL code): Add identity
CREATE TABLE tbl2 (col1 INT NOT NULL, col2
db2 -td@ DATE NOT NULL, PRIMARY KEY (col1, col2)) ALTER TABLE tbl2 ALTER col1 SET GENERATED
Define a terminator character Create a table specifying tablespaces ALWAYS AS IDENTITY
--#SET TERMINATOR @ CREATE TABLE tbl3 (col1 INT NOT NULL, col2 Restart identity
List all databases (aliases) CHAR(1)) IN ts1 INDEX IN ts2 ALTER TABLE tbl2 ALTER col1 RESTART WITH 1
LIST DB DIRECTORY Create a table specifying schema Drop identity
Connect to a database (alias) CREATE TABLE sch1.tbl4 (col1 INT) ALTER TABLE tbl2 ALTER col1 DROP IDENTITY
CONNECT TO mydb Create a table with auto incremental column Create a foreign key
Disconnect from a database CREATE TABLE tbl5 (col1 INT NOT NULL ALTER TABLE tbl5 ADD CONSTRAINT fkt5
CONNECT RESET GENERATED AS IDENTITY) FOREIGN KEY (col1) REFERENCES tbl11 (col1)
TERMINATE Create a table like another one Create a check constraint
Get values from the environment (registry CREATE TABLE tbl6 LIKE tbl1 IN ts1 INDEX IN ALTER TABLE tbl1 ADD CONSTRAINT chk CHECK
values) ts2 (col1 in ('a', 'b', 'c'))
• Current timestamp Comment on table and column Enforce a constraint
VALUES CURRENT TIMESTAMP COMMENT ON TABLE tbl1 IS 'Comment in table' ALTER TABLE tbl1 ALTER CHECK chk ENFORCED
• Connected user COMMENT ON COLUMN tbl1.col1 IS 'Description Not enforce a constraint
VALUES CURRENT USER of the field' ALTER TABLE tbl5 ALTER FOREIGN KEY fkt5 NOT
• Current database Declare a temporary table (session schema) ENFORCED
VALUES CURRENT SERVER DECLARE GLOBAL TEMPORARY TABLE tmp1 (col1 Change the granularity of the locks
List all tables INT, col2 DATE) ON COMMIT PRESERVE ROWS ALTER TABLE tbl1 LOCKSIZE TABLE
LIST TABLES Create a global temporary tablespace Drop a table
LIST TABLES FOR SCHEMA myuser CREATE GLOBAL TEMPORARY TABLE tmp2 (col1 DROP TABLE tbl1
LIST TABLES FOR ALL INT) Rename a table
Change current schema Create an index RENAME TABLE tbl2 TO table2
SET CURRENT SCHEMA otherschema CREATE INDEX idx1 ON tbl2 (col2) Truncate a table
Change the isolation level (RR, RS, CS, UR) Create a unique index TRUNCATE TABLE tbl1 IMMEDIATE
SET ISOLATION RR CREATE UNIQUE INDEX idx2 ON tbl5 (col1) Create a sequence
List all tablespaces with their status Drop an index CREATE SEQUENCE seq AS INTEGER
LIST TABLESPACES DROP INDEX idx1 Restart sequence
Describe the structure of the table Add a column (requires Reorg table) ALTER SEQUENCE seq RESTART WITH 15
DESCRIBE TABLE tbl1 ALTER TABLE tbl1 ADD COLUMN col3 timestamp
Describe the result of a query Change nullability
This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
Create a stored procedure Export Perform a cast
CREATE OR REPLACE PROCEDURE prc1 (IN val EXPORT TO myfile OF DEL SELECT * FROM tbl1 VALUES CAST('123' AS INTEGER)
INT, OUT ret DATE) SPECIFIC mypr BEGIN SET Import Concatenate
ret = (SELECT col2 FROM tbl2 WHERE col1 = IMPORT FROM myfile OF DEL INSERT INTO VALUES 'AnyText' || 5
val); END @ mytable1 VALUES 'AnyText' concat 5
Create a trigger Cursor Escape a single quote in a text field
CREATE TRIGGER cp_val AFTER INSERT ON tbl1 DECLARE cur1 CURSOR FOR SELECT * FROM tbl1 VALUES 'Sinead o''Connor'
REFERENCING NEW AS n FOR EACH ROW INSERT Load Query the database catalog
INTO tbl2 VALUES (n.col1, n.col2) LOAD FROM myfile OF DEL INSERT INTO tbl1 SELECT * FROM SYSCAT.TABLES
Create a view LOAD FROM cur1 OF CURSOR INSERT INTO tbl1 SELECT * FROM SYSCAT.TABAUTH
CREATE VIEW vw1 AS SELECT col2 FROM tbl1 Query the status of the load in a table SELECT * FROM SYSCAT.ROUTINES
LOAD QUERY TABLE tbl1
DCL Set integrity SQL PL
Grant on a table SET INTEGRITY FOR tbl1 IMMEDIATE CHECKED Create a compound statement – Anonymous
GRANT SELECT, INSERT ON TABLE tbl1 TO user Ingest block
Grant execution on a stored procedure INGEST FROM FILE myfile FORMAT DELIMITED BEGIN DECLARE val SMALLINT; SET val = 1;
GRANT EXECUTE ON PROCEDURE prc1(INT, DATE) INSERT INTO tbl1 WHILE (val <= 5) DO INSERT INTO tbl5
TO USER jdoe Get the next value from a sequence VALUES (val, val); SET val = val + 1; END
GRANT EXECUTE ON SPECIFIC PROCEDURE mypr TO VALUES NEXT VALUE FOR seq WHILE; END @
GROUP admins INSERT INTO tbl3 (col1) VALUES (NEXT VALUE Perform a reorg via ADMIN_CMD (Sometimes
Revoke on a table FOR seq) required after “alter table”)
REVOKE DELETE ON TABLE mytable FROM recur CALL SYSPROC.ADMIN_CMD('REORG TABLE tbl1')
TCL Call a stored procedure with an IN and an
DML Commit changes OUTPUT parameter
Insert values on a table COMMIT CALL prc1(5, ?)
INSERT INTO tbl3 VALUES (2, 'b') Create a savepoint
INSERT INTO tbl3 VALUES (3, 'c'), (4, 'd'), SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS
(5, 'e') --Atomic Undo changes until savepoint
Insert certain columns ROLLBACK TO SAVEPOINT sp1
INSERT INTO tbl1 (col1) VALUES (6) Undo changes
Insert values from a select ROLLBACK
INSERT INTO tbl6 SELECT col1 FROM tbl1
Insert in temporary table Queries
INSERT INTO session.tmp1 VALUES (1) Put a lock at table level
Update fields LOCK TABLE tbl1 IN EXCLUSIVE MODE
UPDATE tbl3 SET col1 = 5, mycol2 = 'e' -– Execute a query without regard of commit rows
all table SELECT * FROM tbl1 WITH UR --RR,RS,CS
UPDATE tbl3 SET col2 = 'd' WHERE col1 = 7
Execute a query with only 5 rows
Merge (upsert)
SELECT * FROM tbl1 FETCH FIRST 5 ROWS ONLY
MERGE INTO tbl3 AS t USING (SELECT col1
FROM tbl1) s ON (t.col1 = s.col1) WHEN Perform a query to a dummy table (dual)
MATCHED THEN UPDATE SET col2 = 'X' WHEN SELECT 'Any string' FROM SYSIBM.SYSDUMMY1
NOT MATCHED THEN INSERT VALUES (10, 'X') Perform a query calling a function
Delete rows SELECT HEX(col2) FROM tbl5
DELETE FROM tbl1 -–all table Call a function
DELETE FROM tbl1 WHERE col1 > 5 VALUES HEX('AnyText')
This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.