Finding non numeric data

Products

• CA Datacom
• CA DATACOM - AD
• CA CIS
• CA Common Services for z/OS
• CA 90s Services
• CA Database Management Solutions for DB2 for z/OS
• CA Common Product Services Component
• CA Common Services
• CA Datacom/AD
• CA ecoMeter Server Component FOC
• CA Easytrieve Report Generator for Common Services
• CA Infocai Maintenance
• CA IPC
• Unicenter CA-JCLCheck Common Component
• CA Mainframe VM Product Manager
• CA Chorus Software Manager
• CA On Demand Portal
• CA Service Desk Manager - Unified Self Service
• CA PAM Client for Linux for zSeries
• CA Mainframe Connector for Linux on System z
• CA Graphical Management Interface
• CA Web Administrator for Top Secret
• CA CA- Xpertware

Issue/Introduction

Is there anyway using Dataquery DQL or SQL to find if a numeric field contains non numeric data.

Environment

z/os or z/VSE, CA Datacom/DB

Resolution

You may do one of the following:

From DQL (Dataquery Language).
- Do a DISPLAY or PRINT command request on that table and find the output fields showing "***E1***".
        E1 : A numeric column named in a SET, PRINT, or DISPLAY command contains non-numeric data.
       So, a PRINT or DISPLAY will display error "E1" if the field does not contain numeric data.

- Do a DBUTLTY EXTRACT and via a COBOL program or other meaning identify the invalid non numeric  field.


From SQL
- Write a small SQL program using a DECLARE CURSOR/OPEN cursor/FETCH/CLOSE cursor. 
Declare the cursor for a select on this problem column CESS_CNT and all the columns from the master key. 
The FETCH fails on each row with invalid data in that column CESS_CNT with SQLCODE -161. 
So test on that SQLCODE and then display the values of the master key columns so that you know which row needs to be corrected. 
You test on that SQLCODE -161 during the FETCH , do the display and then FETCH the next row until all rows have been read. Then you close the cursor.