To truncate a DB2 table without REDO logs

db2 load client from /dev/null of DEL replace into SCHEMA.TABLE nonrecoverable DISK_PARALLELISM 1

NOLOGGING In DB2

ALTER TABLE ACTIVATE NOT LOGGED INITIALLY
-- In the same unit of work logging will be disabled.

DB Query Syntax - OUTER JOIN

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0000875.htm

Procedures in DB2

http://www.informit.com/articles/article.aspx?p=31963

Temporary Tables

DECLARE GLOBAL TEMPORARY TABLE SESSION.ESM_EXTRAS
(
ESMP CHARACTER(30)
)
ON COMMIT PRESERVE ROWS;

To convert rows to columns

WITH n(rownum, chain) AS
(
SELECT rownum, PROCESS_DATE
FROM (SELECT row_number() over (), CAST(PROCESS_DATE AS VARCHAR(10000)) FROM PROCESS ) AS t(rownum, PROCESS_DATE)
WHERE T.rownum = 1
UNION ALL
SELECT t1.rownum, n.chain ':' t1.PROCESS_DATE
FROM (SELECT row_number() over (), CAST(PROCESS_DATE AS VARCHAR(10000)) FROM PROCESS ) AS t1(rownum, PROCESS_DATE), n
WHERE n.rownum + 1 = t1.rownum
)
SELECT chain FROM n x, (select max(rownum) maxrow from n) y where x.rownum = y.maxrow

Interesting articles

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0510rielau/
http://www.devx.com/getHelpOn/10MinuteSolution/16573/1954

SELECT row_number() over (), PROCESS_DATE FROM BATCH_PARAMETER