The translate function is pretty amazing. You pass in the variable, a string of characters you want the character to be replaced with followed by a string of characters you want to search for.  In the example below all numeric characters become N and all N’s become X.  This makes all your Digits the character N which you can then compare to ‘NNNNN’ in this instance of a char(5) datatype column to select your numeric only fields, you can then do a numeric comparison such as greater than <10000 without running into a SQL exception with casting nonnumeric characters.

SELECT *
FROM MYTABLE
WHERE TRANSLATE (MYTABLE.MYFIELD, 'NNNNNNNNNNX', '1234567890N') = 'NNNNN' AND MYTABLE.MYFIELD<10000
—MYFIELD is a CHAR(5) field and is being compared against 5 'N's

view raw
DetectNumbericOnlyField.sql
hosted with ❤ by GitHub

SQL Reference for IBM i – to learn more about TRANSLATE

ftp://ftp.software.ibm.com/ps/products/db2/info/vr7/pdf/letter/db2s0e70.pdf