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)))
P_W999