Describe the result of a query: Change nullability:

Db2 Cheat Sheet for development DESCRIBE SELECT * FROM tbl1 ALTER TABLE tbl1 ALTER COLUMN col3 SET NOT
Get help for a Db2 command: NULL
Created by: ? command Drop nullability:
Andres Gomez Casanova
(@angoca) Get help for a SQL code (SQLXXXX) or ALTER TABLE tbl1 ALTER COLUMN col3 DROP NOT
Version: SQLstate (YYYYY): NULL
2021-02-23 ? SQLXXXX Rename a column:
? YYYYY ALTER TABLE tbl1 RENAME COLUMN col3 TO new3
Get the most recent version at
https://angoca.github.com/db2-cheat-sheet Drop column:
Execution of a file in the console (db2clp).
DDL ALTER TABLE tbl1 DROP COLUMN new3
Create a schema: Create a primary key constraint:
• Semi-colon separated sentences: ALTER TABLE tbl5 ADD CONSTRAINT pkt5
db2 -t CREATE SCHEMA sch1
PRIMARY KEY (col1)
• At sign separated sentences (when there is Create a table specifying primary key:
Drop primary key:
CREATE TABLE tbl1 (col1 CHAR(1) NOT NULL
SQL PL code): PRIMARY KEY) ALTER TABLE tbl5 DROP PRIMARY KEY
db2 -td@ CREATE TABLE tbl2 (col1 INT NOT NULL, col2 Add identity:
Define a terminator character: DATE NOT NULL, PRIMARY KEY (col1, col2)) ALTER TABLE tbl2 ALTER col1 SET GENERATED
--#SET TERMINATOR @ Create a table specifying tablespaces: ALWAYS AS IDENTITY
List all databases (aliases): CREATE TABLE tbl3 (col1 INT NOT NULL, col2 Restart identity:
LIST DB DIRECTORY CHAR(1)) IN ts1 INDEX IN ts2 ALTER TABLE tbl2 ALTER col1 RESTART WITH 1
Connect to a database (alias): Create a table specifying schema: Drop identity:
CONNECT TO mydb CREATE TABLE sch1.tbl4 (col1 INT) ALTER TABLE tbl2 ALTER col1 DROP IDENTITY
Disconnect from a database: Create a table with auto incremental column: Create a foreign key:
CONNECT RESET CREATE TABLE tbl5 (col1 INT NOT NULL ALTER TABLE tbl5 ADD CONSTRAINT fkt5
TERMINATE GENERATED AS IDENTITY) FOREIGN KEY (col1) REFERENCES tbl11 (col1)
Get values from the environment (registry Create a table like another one: Create a check constraint:
values). CREATE TABLE tbl6 LIKE tbl1 IN ts1 INDEX IN ALTER TABLE tbl1 ADD CONSTRAINT chk CHECK
• Current timestamp: ts2 (col1 in ('a', 'b', 'c'))
VALUES CURRENT TIMESTAMP Comment on table and column: Enforce a constraint:
• Connected user: COMMENT ON TABLE tbl1 IS 'Comment in table' ALTER TABLE tbl1 ALTER CHECK chk ENFORCED
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: ENFORCED
Declare a temporary table (session schema):
VALUES CURRENT SERVER Change the granularity of the locks:
DECLARE GLOBAL TEMPORARY TABLE tmp1 (col1
List all tables: INT, col2 DATE) ON COMMIT PRESERVE ROWS ALTER TABLE tbl1 LOCKSIZE TABLE
LIST TABLES Drop a table:
Create a global temporary tablespace:
LIST TABLES FOR SCHEMA myuser DROP TABLE tbl1
LIST TABLES FOR ALL CREATE GLOBAL TEMPORARY TABLE tmp2 (col1
INT) Rename a table:
Change current schema: RENAME TABLE tbl2 TO table2
Create an index:
SET CURRENT SCHEMA otherschema Truncate a table:
CREATE INDEX idx1 ON tbl2 (col2)
Change the isolation level (RR, RS, CS, UR): TRUNCATE TABLE tbl1 IMMEDIATE
Create a unique index:
SET ISOLATION RR Create a sequence:
CREATE UNIQUE INDEX idx2 ON tbl5 (col1)
List all tablespaces with their status: CREATE SEQUENCE seq AS INTEGER
Drop an index:
LIST TABLESPACES Restart sequence:
DROP INDEX idx1
Describe the structure of the table: ALTER SEQUENCE seq RESTART WITH 15
Add a column (requires Reorg table):
DESCRIBE TABLE tbl1
ALTER TABLE tbl1 ADD COLUMN col3 timestamp
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 Call a stored procedure with an IN and an
Revoke on a table: FOR seq) OUTPUT parameter:
REVOKE DELETE ON TABLE mytable FROM recur CALL prc1(5, ?)
TCL Perform a reorg via ADMIN_CMD (Sometimes
DML Commit changes: required after “alter table”):
Insert values on a table: COMMIT CALL SYSPROC.ADMIN_CMD ('REORG TABLE tbl1')
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.