SQL -999 INVALID INTERNAL DATE

Products

• Datacom
• DATACOM - AD
• CIS
• COMMON SERVICES FOR Z/OS
• 90S SERVICES
• DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS
• COMMON PRODUCT SERVICES COMPONENT
• Common Services
• CA ECOMETER SERVER COMPONENT FOC
• Easytrieve Report Generator for Common Services
• INFOCAI MAINTENANCE
• IPC
• UNICENTER JCLCHECK COMMON COMPONENT
• Mainframe VM Product Manager
• CHORUS SOFTWARE MANAGER
• CA ON DEMAND PORTAL
• CA Service Desk Manager - Unified Self Service
• PAM CLIENT FOR LINUX ON MAINFRAME
• MAINFRAME CONNECTOR FOR LINUX ON MAINFRAME
• GRAPHICAL MANAGEMENT INTERFACE
• WEB ADMINISTRATOR FOR TOP SECRET
• Xpertware

Issue/Introduction

How to determine what data is causing the Datacom SQL request to get INVALID INTERNAL DATE error:

SQLCODE = -999, SQLSTATE=56S19
MESSAGE = INTERNAL ERROR (charDate LINE 1149): INVALID INTERNAL DATE FOUND

Environment

Release: DATABB00200-14.0-Datacom/AD
Component:

Resolution

This SELECT should list any rows with invalid date values. 

Substitute DATECHK with the name of the Table and COL1 with the name of the date column to check.
Add additional columns such as the key(s) to the request to further identify and isolate the bad rows..
 
SELECT substr(hex(COL1),1,2) AS CC, 
substr(hex(COL1),3,2) AS YY, 
substr(hex(COL1),5,2) AS MM, 
substr(hex(COL1),7,2) AS DD 
FROM DATECHK 
WHERE NOT ( 
substr(hex(COL1),1,2) BETWEEN '01' AND '14' 
AND substr(hex(COL1),3,2) BETWEEN '00' AND '63' 
AND (substr(hex(COL1),5,2) BETWEEN '01' AND '09' 
OR substr(hex(COL1),5,2) IN ('0A','0B','0C') ) 
AND (substr(hex(COL1),7,1) BETWEEN '0' AND '3' 
OR substr(hex(COL1),8,1) BETWEEN '0' AND '9' 
OR substr(hex(COL1),8,1) IN ('A','B','C','D','E','F') ) ) 
; 

Additional Information

In this example, the SELECT request was run via the DBSQLPR utility.
Five rows with bad value in the specified date column were the result:
 CC           YY           MM           DD             
 CHAR(2) N.N. CHAR(2) N.N. CHAR(2) N.N. CHAR(2) N.N.   
 ____________ ____________ ____________ ____________   
 65           26           E8           D5             
 F0           F1           F1           F1             
 E3           E8           D5           C8             
 F3           F0           F7           14             
 E8           D5           C8           D2             
 ___ 5 rows returned ___     

Adding key(s) to the SELECT will help identify the bad rows.