Profiling Data in Database Column for DB2 (KBA6995)

How many rows in the table?

This is important to know to understand the statistical distribution of the profile result below. 

-- Replace [table]
--  
SELECT COUNT(*) cnt_rows FROM [table];

 CNT_ROWS
----------
  4534824

How many duplicates (is the data unique)?

Use this query when the uniqueness of the values in a column is important. This query should be executed on the column before and after masking. Especially investigate columns that are referential keys (i.e. PK and FK) or have unique constraints. These might be composite and require multiple columns to be investigated. Also, values might need to be formatted using a cast. 

This query shows if there are duplicated values in a column. The expected result is one row with 'dups' = 1.

Note:

No values from the columns are shown. This query only shows statistics (number of duplicates).

-- Replace [table]
-- Replace [column]
--
-- dups should be 1 for no duplicates.
--
SELECT s1.dups, count(*) as cnts
FROM (
   SELECT [column], count(*) as dups
   FROM [table]
   GROUP BY [column]) s1
GROUP BY dups;

Concatenate columns (using | as a separator as it is not frequently used):

column1 || '|' || column2

      DUPS      CNTS
---------- ----------
         1   4534822
         2         1

How many rows with a specific length (characters and bytes)?

This query shows the number of rows with a specific length. There are two queries - one for characters and one for bytes. The first 1,000 rows are counted. For memory-related issues use bytes (LENGTHB). For masking results, use characters (LENGTH). 

-- Replace [table]
-- Replace [mask_col]
-- 
SELECT s1.len_char, s1.len_bytes, COUNT(*) as cnt 
FROM 
(SELECT LENGTH([mask_col]) as len_char, LENGTHB([mask_col]) as len_bytes 
 FROM [table] FETCH FIRST 1000 ROWS ONLY) s1
GROUP BY s1.len_char, s1.len_bytes 
ORDER BY cnt DESC;

 len_char   len_bytes       CNT
---------- ----------- ---------
       10          20       604
       11          22        65
       12          24        10
...
       24          48         2
       26          52         1

-- Create Function Below
-- Replace [table]
-- Replace [mask_col]
-- 
SELECT s1.profile, count(*) cnt from 
(SELECT 
 TRANSLATE(TRANSLATE(TRANSLATE([mask_col],
  'LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL',
   'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),
  'NNNNNNNNNN',
   '1234567890'),
  'Z',
   ' ') as profile
 FROM [table] FETCH FIRST 1000 ROWS ONLY) s1
GROUP BY profile
ORDER BY cnt DESC;

 profile       cnt
--------- ---------
 LL            376
 LL.           206
 LLL.           34
 LLLL           31
 ...

How many rows with special characters in the cell?

This query has specifically been created to extract special characters in data that need to be ignored in masking algorithms. The query shows the number of rows with special characters. The query has been limited to only count the first 1,000 rows. 

-- Create Function Below
-- Replace [table]
-- Replace [mask_col]
-- 
SELECT s1.profile, count(*) cnt from
(SELECT TRANSLATE([mask_col], '', 
 ' 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') as profile
 FROM [table] FETCH FIRST 1000 ROWS ONLY) s1
GROUP BY profile
ORDER BY cnt DESC;

 special       cnt
--------- ---------
 _             205
 _&             98
 _-             72
 _++            24
... 

What rows have Special Characters (how to find them)?

This query can be used to show the first records in the database with Special Characters. The number of rows selected might need to be changed.

-- Replace [table]
-- Replace [mask_col]
-- Include [key] to include an identifier so the record can be found.
-- Modify 1000 if more sample data is needed.

SELECT * FROM 
(SELECT [key], [mask_col] 
 FROM [table]
 FETCH FIRST 1000 ROWS ONLY)
WHERE regexp_like([mask_col],'[^a-zA-Z0-9]');

EMPNO  FIRSTNME    
------ ------------
000200 DAVID_123     
1 record(s) selected.