My IBM DB2 Cheat Sheet

This is a set of DB2 snippets I often used at work. I’m glad to share them with you.

Generate GRANT statements for a new DB2 user with name MYUSER user on all tables of a schema named MYSCHEMA:

select 'GRANT SELECT ON TABLE ' || TRIM(tabschema) || '.' || TRIM(tabname) || ' TO USER MYUSER @' from syscat.tables where tabschema = 'MYSCHEMA'
UNION
select 'GRANT INSERT ON TABLE ' || TRIM(tabschema) || '.' || TRIM(tabname) || ' TO USER MYUSER @' from syscat.tables where tabschema = 'MYSCHEMA'
UNION
select 'GRANT UPDATE ON TABLE ' || TRIM(tabschema) || '.' || TRIM(tabname) || ' TO USER MYUSER @' from syscat.tables where tabschema = 'MYSCHEMA' @

The following stored procedure will automatically generate and execute the grant statements

CREATE OR REPLACE PROCEDURE MYSCHEMA.GRANT_ME_SOME (IN SCHEMANAME VARCHAR(128), IN USRNME VARCHAR(128))
       DYNAMIC RESULT SETS 1
       MODIFIES SQL DATA
P1: BEGIN
		DECLARE q VARCHAR(1024);
		DECLARE AT_END SMALLINT DEFAULT 0 ;
		DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000' ;
		
		DECLARE CUR CURSOR WITH HOLD FOR 
		select 'GRANT SELECT ON TABLE ' || TRIM(tabschema) || '.' || TRIM(tabname) || ' TO USER ' || USRNME AS KWERIE from syscat.tables where tabschema = SCHEMANAME
		UNION
		select 'GRANT INSERT ON TABLE ' || TRIM(tabschema) || '.' || TRIM(tabname) || ' TO USER ' || USRNME  AS KWERIE from syscat.tables where tabschema = SCHEMANAME
		UNION
		select 'GRANT UPDATE ON TABLE ' || TRIM(tabschema) || '.' || TRIM(tabname) || ' TO USER ' || USRNME  AS KWERIE from syscat.tables where tabschema = SCHEMANAME; 
	
		DECLARE CONTINUE HANDLER FOR NOT_FOUND SET AT_END = 1 ;
		OPEN CUR;
		
		UPDATE_LOOP : LOOP
		
		FETCH CUR INTO q;
		IF AT_END <> 0 THEN
	        LEAVE UPDATE_LOOP ;
	    END IF ;
		
		EXECUTE IMMEDIATE q;
		
		END LOOP;
END P1;

Generate ‘reorg index’ and and ‘reorg columns’ statements for all tables of a schema MYSCHEMA

select 'CALL SYSPROC.ADMIN_CMD(''RUNSTATS ON TABLE ' ||TRIM(tabschema)||'.'||TRIM(tabname)||' FOR DETAILED INDEX ALL '')@' from syscat.tables where tabschema='MYSCHEMA' and type='T' 
UNION
select 'CALL SYSPROC.ADMIN_CMD(''RUNSTATS ON TABLE ' ||TRIM(tabschema)||'.'||TRIM(tabname)||' ON ALL COLUMNS'')@' from syscat.tables where tabschema='MYSCHEMA' and type='T' @

Generate reorg table statements for all tables of a schema MYSCHEMA

select 'CALL SYSPROC.ADMIN_CMD(''REORG TABLE '||TRIM(tabschema)||'.'||TRIM(tabname)||''') @' from syscat.tables where tabschema='MYSCHEMA' and type='T'

Find all inactive tables on your database

SELECT COALESCE(
 'SET INTEGRITY FOR  "' || TRIM(TABSCHEMA) || '"."' || TRIM(TABNAME) || 
  '"  IMMEDIATE CHECKED @' , '')
FROM

 TABLE
 ( SELECT TABSCHEMA, TABNAME
 FROM SYSCAT.TABLES
 WHERE (CONST_CHECKED LIKE '%N%'
 OR STATUS = 'C')
 UNION

 SELECT a.REFTABSCHEMA,a.REFTABNAME
 FROM SYSCAT.REFERENCES a,SYSCAT.TABLES b
 WHERE (a.TABSCHEMA, a.TABNAME) IN
 (SELECT TABSCHEMA, TABNAME
 FROM SYSCAT.TABLES
 WHERE (CONST_CHECKED LIKE '%N%'
 OR STATUS = 'C'))
 AND a.REFTABSCHEMA = b.TABSCHEMA
 AND a.REFTABNAME = b.TABNAME
 AND (b.CONST_CHECKED LIKE '%N%'
 OR b.STATUS = 'C')

 ) AS TAB( TABSCHEMA, TABNAME )
ORDER BY TABSCHEMA
FETCH FIRST 50 ROWS ONLY
OPTIMIZE FOR 1 ROW

Quickly unload a table (sets your table in an invalid state). Works from CLI only

db2 LOAD FROM /dev/null of del REPLACE INTO MYSCHEMA.MYTABLE

An example of a DELETE WITH statement using the SELECT FROM OLD TABLE structure. I previously covered this statement.

WITH 
	DOCLINK(ID) AS ( SELECT ID FROM OLD TABLE (
			DELETE FROM MYSCHEMA.MYTABLE1 WHERE CODE = 'XYZ'
		)
	), 
	DOCU(ID) AS (SELECT ID FROM OLD TABLE (
			DELETE FROM MYSCHEMA.MYTABLE2 WHERE DOC_ID IN (SELECT ID FROM DOCLINK)
		)
	)	
	SELECT COUNT FROM MYSCHEMA.MYTABLE1 WHERE CODE = 'XYZ'
 @

An example of a MERGE statement. The field MYTABLE.JAAR will be updated with the value of the matching field in MYTABLE2.JAAR. It’s what you would do with UPDATE MYTABLE.JAAR = (SELECT JAAR FROM MYTABLE2 WHERE MYTABLE.VELD_ID = MYTABLE.ID) if it were a valid statement.

MERGE INTO MYSCHEMA.MYTABLE VELD 
	USING (
		select DISTINCT V.ID AS VELD_ID, T.JAAR AS JAAR 
		 from MYSCHEMA.MYTABLE2 T, MYSCHEMA.MYTABLE3 V
                 WHERE    V.ID = T.V_ID
			AND V.TYPE_ID IN (  100000,   100065,   100130)
			AND T.JAAR IS NOT NULL
	) AS VELD_TO_UPDATE
	ON VELD_TO_UPDATE.VELD_ID = VELD.ID
	WHEN MATCHED THEN UPDATE SET VELD.JAAR = VELD_TO_UPDATE.JAAR 
	@

A simple WITH statements

WITH IDS(FRST, SCND, CNT) AS (
	SELECT MIN(ID), MAX(ID), COUNT(ID) FROM MYSCHEMA.MYTABLE
		WHERE ID > 105220
		GROUP BY EXT_ID, INT_ID
		HAVING COUNT(ID) > 1
) SELECT ID, REF FROM MYSCHEMA.MYTABLE2 WHERE
	IN in (SELECT FRST FROM IDS) 
	OR ID IN (SELECT SCND FROM IDS)
	ORDER BY REF
	FOR READ ONLY @

List aggregation (LISTAGG)

SELECT LISTAGG(REF, ', ') WITHIN GROUP(ORDER BY ID) 
   FROM MYSCHEMA.MYTABLE GROUP BY REF_PREFIX

Drop a schema (CLI)

db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('MYSCHEMA', NULL, 
 'DELETESCHEMA', 'ERR_DEL')"

Copy a schema (CLI)

db2 "CALL SYSPROC.ADMIN_COPY_SCHEMA('MYSCHEMA', 'MYNEWSCHEMA', 
 'COPY', null,  null, null, 'ERRORSCHEMA', 'ERRORTAB') "

DB2Advisor on schema MYSCHEMA (CLI) using an input file YOUR_QUERIES (you can also pass a password separately with -x)

db2advis -d MY_DB -t 30 -m IMP -noxml -i YOUR_QUERIES.SQL 
 -a USERNAME/PASSWORD -o OUTPUT.OUT -n MYSCHEMA -q MYSCHEMA

Create explain tables in your scherma.

CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), 
 CAST ('MYUSER' AS VARCHAR(128)))
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s