0

I am very new in iseries/DB2.

We use V7R3. We have table that is generated every day by RPG program as physical file. For accessing the table data from java we use jt400.jar jdbc driver.

Most of the table queries work fine but some complex queries that are using "DENSE_RANK() OVER(ORDER BY" and "ROW_NUMBER() OVER(PARTITION BY" time to time hanging and causing CPU 100%. only killing the job on AS400 side is resolving the issue.

in the AS400 log I see:

Job 969954/QUSER/QZDASOINIT started on 02/21/19 at 09:36:46 in subsystem 
QUSRWRK in QSYS. Job entered system on 02/21/19 at 09:36:46. 
User USERXX from client X.X.X.X connected to server. 
Use of function TIMESTAMP_FORMAT in QSYS2 not valid. 
Use of function TIMESTAMP_FORMAT in QSYS2 not valid. 
Data mapping error on member TABLE_NAME. 
Data mapping error on member TABLE_NAME. 
Data mapping error on member TABLE_NAME. 
Data mapping error on member TABLE_NAME. 
Value in date, time, or timestamp string not valid.

It looks similar as problem described in the Why am I getting a "[SQL0802] Data conversion of data mapping error" exception? and probably the problem is related to invalid data stored to DATE type columns.

Looking to the DATE columns I see that some records displayed as <null> in SQuirrel SQL Client. Interesting that here are 2 different <null>'s returned by distinct query. enter image description here

If I run 

 select  distinct VARCHAR_FORMAT(DATE_COLUMN, 'YYYY/MM/DD')  from TABLE_NAME

I get 0001/01/01 and 9999/12/31 for these <null>'s rows.

if I run Select * from TABLE where DATE_COLUMN is null I don't get any results. So I am not sure what kind of <null>'s is that.

Not sure if these records can cause an issue.

UPD: when I run 

Select * from TABLE

I see errors in JDBC client log:

Warning:   [SQL0181] Value in date, time, or timestamp string not valid.
SQLState:  01534
ErrorCode: 181
Warning:   [SQL0181] Value in date, time, or timestamp string not valid.
SQLState:  01534
ErrorCode: 181Warning:   [SQL0181] Value in date, time, or timestamp string not valid.
SQLState:  01534
ErrorCode: 181
Warning:   [SQL0181] Value in date, time, or timestamp string not valid.
SQLState:  01534
ErrorCode: 181
Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.252, SQL query: 0.005, Reading results: 0.247

based on https://www.consolut.com/en/s/sap-ides-access/d/s/p/40/doc/XH-SQL0181/ it should be incorrect date somewhere in the table

The question is there any way to find and filter records that have "invalid" data (causing above exception in the log) from SQL side?

David Abragimov
  • 524
  • 2
  • 6
  • 24
  • timestamp_format converts character data to a timestamp. Bad input data. the varchar_format converts from date to char it is stable doesn't fail for me. – danny117 Mar 19 '19 at 13:39
  • @danny117 interesting that we are not using TIMESTAMP_FORMAT function in the query. not sure why this error appears in the log – David Abragimov Mar 19 '19 at 14:33
  • Did you check that the data type of the column is DATE and not some type of string? – WarrenT Mar 19 '19 at 23:20
  • @WarrenT it displayed as getColumnTypeName = DATE, getColumnClassName = java.sql.Date , getColumnType = 91 for the DATE columns metadata in jdbc client(SQuirrel SQL Client). Not sure how to check it from AS400 side. – David Abragimov Mar 20 '19 at 02:35
  • The JDBC column type is probably fine. The CL command would be [DSPFFD](https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/cl/dspffd.htm), display file field descriptions. Or you could look in the system catalog view [SYSCOLUMNS](https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcatsyscol.htm) – WarrenT Mar 22 '19 at 19:00
  • So the type is DATE. What is the data for date_column in those two records? What happens if you set your connection's date format to *ISO, and just SELECT date_column, what shows up for those two records? – WarrenT Mar 22 '19 at 19:08
  • @WarrenT if I set dateformat=ISO in connection properties than I see 0001-01-01, 9999-12-31 in Select distinct date_column from table_name query output. Also after adding dateformat=ISO I don't see Warning: [SQL0181] Value in date, time, or timestamp string not valid.SQLState: 01534 in the SQL client log when run Select * from table_name – David Abragimov Mar 22 '19 at 19:17

0 Answers0