DB2 List Tables, Sizing for Each Tables, DDL Import CSV, and Count Rows from Each Tables

---   ---   ---   ---   ---

GENERAL QUERY

SELECT

"TABNAME" AS "TABLE_NAME",

('db2 ' || '"IMPORT FROM ' || UPPER("TABNAME") || '.csv OF DEL MODIFIED BY DELPRIORITYCHAR DATEFORMAT=\"yyyy-mm-dd\" TIMEFORMAT=\"hh.mm.ss\" TIMESTAMPFORMAT=\"yyyy-mm-dd-hh.mm.ss.uuuuuu\" COLDEL; SKIPCOUNT 1 INSERT INTO ' || UPPER("TABNAME") || '"') AS "IMPORT_CSV"

FROM SYSCAT.TABLES

WHERE

"TYPE" = 'T' AND -- for table

UPPER(TABSCHEMA) = UPPER('YOUR_SCHEMA_NAME')

ORDER BY TABNAME ASC;

---   ---   ---   ---   ---

ADVANCED QUERY

SELECT

(ROW_NUMBER() OVER ()) AS NOURUT,

"dbTmp2"."TABLE_NAME",

"dbTmp2"."SIZE_MB",

"dbTmp2"."SIZE_GB",

('db2 ' || '"IMPORT FROM ' || UPPER("dbTmp2"."TABLE_NAME") || '.csv OF DEL MODIFIED BY DELPRIORITYCHAR DATEFORMAT=\"yyyy-mm-dd\" TIMEFORMAT=\"hh.mm.ss\" TIMESTAMPFORMAT=\"yyyy-mm-dd-hh.mm.ss.uuuuuu\" COLDEL; SKIPCOUNT 1 INSERT INTO ' || UPPER("dbTmp2"."TABLE_NAME") || '"') AS "IMPORT_CSV",

('SELECT ' || '''' || UPPER("dbTmp2"."TABLE_NAME") || '''' || ' AS "TABLE_NAME", COUNT(*) AS "COUNT_ITEMS" FROM ' || "dbTmp2"."TABLE_NAME" || CHR(10) || 'UNION ALL') AS "QUERY_COUNT",

(CASE WHEN "dbTmpCountEachTables"."COUNT_ITEMS" IS NULL = TRUE THEN 0 ELSE "dbTmpCountEachTables"."COUNT_ITEMS" END) AS "COUNT_ITEMS"

FROM (

SELECT

REPLACE(REPLACE(TRIM("dbTmp1"."TABLE_NAME"), ' ', ''), 'YOUR_SCHEMA.', '') AS TABLE_NAME,

CAST("dbTmp1"."USED_MB" AS BIGINT) AS SIZE_MB,

CAST(("dbTmp1"."USED_MB" / 1024) AS DECIMAL(18,2)) AS SIZE_GB

FROM (

SELECT

char(date(t.stats_time)) || ' ' || char(time(t.stats_time)) AS statstime,

substr(t.tabschema, 1, length(t.tabschema)) || '.' || substr(t.tabname, 1, length(t.tabname)) AS "TABLE_NAME",

decimal(float(t.npages) / (1024 / (b.pagesize / 1024)), 9, 2) AS used_mb,

decimal(float(t.fpages) / (1024 / (b.pagesize / 1024)), 9, 2) AS allocated_mb

FROM syscat.tables t, syscat.tablespaces b

WHERE t.tbspace = b.tbspace

ORDER BY 5 DESC WITH UR

) AS "dbTmp1"

WHERE LEFT("dbTmp1"."TABLE_NAME", LENGTH('YOUR_SCHEMA')) = 'YOUR_SCHEMA'

ORDER BY UPPER("dbTmp1"."TABLE_NAME") ASC

) AS "dbTmp2"

LEFT JOIN (

SELECT 'TABLE1' AS "TABLE_NAME", COUNT(*) AS "COUNT_ITEMS" FROM TABLE1

UNION ALL

SELECT 'TABLE2' AS "TABLE_NAME", COUNT(*) AS "COUNT_ITEMS" FROM TABLE2

UNION ALL

SELECT 'TABLE3' AS "TABLE_NAME", COUNT(*) AS "COUNT_ITEMS" FROM TABLE3

) AS "dbTmpCountEachTables" ON "dbTmpCountEachTables"."TABLE_NAME" = "dbTmp2"."TABLE_NAME"

ORDER BY

"dbTmp2"."TABLE_NAME" ASC;

---   ---   ---   ---   ---