[SOLVED] dbtool reports "Date errors found: 2" - how to fix?

[Ryszard Musielak]

Hello,

I run the dbtool script on a daily basis and sometime we fix the SQLWidth errors. However, I cannot seem to find the option to fix this error:

"Date errors found: 2"

Can anyone offer a hint?

Progress documentation only seems to mention the DBTool in general, but not many specifics.

Here is the full screen:

                     DATABASE TOOLS MENU - 11.6
                     ---------------------------

                 1. SQL Width & Date Scan w/Report Option
                 2. SQL Width Scan w/Fix Option
                 3. Record Validation
                 4. Record Version Validation
                 5. Read or Validate Database Blocks
                 6. Record Fixup
                 7. Schema Validation
                 8. Disable Object Locking Protection
                 9. Enable/Disable File Logging
                10. Index Space Validation
                11. Index Space Fixup
                12. Schema Fixup

                Q. Quit

                Choice:
        <connect>:    (0=single-user 1=self-service >1=#threads)?
        <table>:      (Table number or all)?
        <area>:       (Area number or all)?
        <display>:    (verbose level 0-4)?
 The option will process all records including template records in the specified range.


 Total records read: 142620416

 SQLWidth errors found: 0, Date errors found: 2

Thanks,
Richard

 

[Rob Fitzpatrick]

Was that the entire output? It didn't provide recids or key values for the record(s) with the date validation errors?

Did you find this documentation? It is fairly detailed.

https://docs.progress.com/bundle/openedge-database-management/page/DBTOOL-utility.html#

 

[Ryszard Musielak]

Our third party told us to run the first two options:
1. SQL Width & Date Scan w/Report Option
2. SQL Width Scan w/Fix Option

Can you please elaborate on this?

Was that the entire output? It didn't provide recids or key values for the record(s) with the date validation errors?

What do I need to run?
Should I choose this?
<display>: (verbose level 0-4)? 1

Value 4 display a lot of information.....

Thanks,
Richard

 

[Ryszard Musielak]

Ok, running this with verbose 1, seems like more details, I will re-post later......

 

[Ryszard Musielak]

Hi,

so here is the result of the Scan option with "(verbose level 0-4)?" = 2, found a table with this reported:

Current max fieldLen for conlog (208):
Fld# SQLWidth Max Width ERROR NAME
---- -------- --------- ----- ----
2: 6 3 depot
3: 2 1 c-type
4: 12 7 con-group
5: 20 6 cust-code
6: 14 6 p-code
7: Date 0 p-date
8: 16 8 p-time
9: 24 11 user-id
10: Date 116 %%%% act-date
11: Date 37 %%%% deact-date

The Verbose option 3 revealed a lot of these:

Date error: Table: 208 Field 11 Ext 0 Recid 53599643
Date error: Table: 208 Field 10 Ext 0 Recid 53599644
Date error: Table: 208 Field 10 Ext 0 Recid 53599656
Date error: Table: 208 Field 11 Ext 0 Recid 53599663
Date error: Table: 208 Field 10 Ext 0 Recid 53599664
Date error: Table: 208 Field 10 Ext 0 Recid 53599673
Date error: Table: 208 Field 10 Ext 0 Recid 53599674
Date error: Table: 208 Field 11 Ext 0 Recid 53599675
Date error: Table: 208 Field 10 Ext 0 Recid 53599679

How should I interpret the 10 and 11 items? How can they be fixed?

Thanks,
Richard

 

[Rob Fitzpatrick]

Look up those records and display the date values.

E.g.:

find conlog where recid( conlog ) = 53599643.  /* repeat for the other recids */
display 
  act-date    format '99/99/99999'
  deact-date  format '99/99/99999'.

You might have some dates with five-digit years.

 

[Ryszard Musielak]

Thank you, that was very helpful, yes, I found dates with 5 digit year. Not sure this is a DB problem or is it a front-end problem.

Are these errors related to the records I have with this invalid date format?
10: Date 116 %%%% act-date
11: Date 37 %%%% deact-date

Thanks,
Richard

 

[Rob Fitzpatrick]

I don't think it's a database problem per se (in terms of root cause), aside from the fact that your database now contains bad data and you need to decide what to do about it. For example, if a date value is 12/31/20199, there is a pretty good chance that the user meant to enter 2019 as the year and fat-fingered it. In other cases might not all be that obvious what the correct value should have been.

Note: the "bad data" in this case is semantically wrong but is valid from a database perspective, even though dbtool is complaining about it. The ABL date data type permits year values in dates between -32768 and 32767. This doesn't sound like a case of database corruption so a utility isn't going to provide you with an automated fix.

How the data got there is a different matter, and of course it comes down to the code or ETL process that creates or modifies records in your conlog table. You may need to tighten constraints in the UI or add a date sanity-check in your business-validation logic to prevent such bad data from being accepted and stored in the future. Determining the scope of work for this is a task for someone who knows your application.

Some more info:

Progress Customer Community

knowledgebase.progress.com

 

[Ryszard Musielak]

Hi,

thank you, it does look like the frontend ABL is allowing people to enter bad data, so I will pass it to my third party.

The article you pointed to seems like a good explanation.

Kind Regards,
Richard