Found this on a board I lurk at sometimes. Comments?

http://www.kitcomm.com/cgi-bin/comments/gold/display_short.cgi#start

Date: Sat Oct 02 1999 18:31 Silverbear (Y2K - THE REAL DEAL) ID#144226: Copyright ) 1999 Silverbear/Kitco Inc. All rights reserved Forget the code and embedded chips, let's talk files and databases. For every line of computer code there's millions of records stored on files and databases. Now assume that your shop has the forsight during say the last 10-15 years to move your "old" data to one of the "newer" mainframe databases like DB2. The dates on the old files may or may not have contained century but no problem, you "new" database does, so there's nothing to worry about with your data come Y2K. Right? WRONG!

See, when the old data was moved to the new databases, alot of shops restructured the new databases to get around a problem that has come about now that shops want their databases up and running 24-7. What these shops did was add "effective" dates on every record they put on these new databases, in otherwords, a "begin" and an "end" date that said when that record was 'effective".

With these "effective" dates, they could now load data this month that wouldn't become "effective" for months to come. Now this was great, massive database updates could be done incrementally over weeks or even months and the "updates" would all show up on some future effective date.

The problem is, guess what "termination" date the programmers loaded on all the records they put on those "new" databases during the last 10-15 years. That's right, they plugged the termination dates on every record with '1999-12-31'.

Now why would they have done that? You see, they knew that most of the programs at that time were not Y2K compliant, they knew that most of the programs that would read these new databases, would just drop the century and they would have caused problems if they had used a termination date of say "2100-01-01'. So, they plugged the termination date with '1999-12-31'.

Now, as shops all over the country are starting to do full system tests on their newly "Y2K compliant" systems, they are suddenly finding that when the system date is rolled to '2000-01-01' that the data on their databases has ceased to be "effective".

You see, a large company can have thousands of files ( actually we call them tables these days ) , each of which can contain millons if not hundreds of millons of records ( now called rows ) , and updating everyone of them makes the code changes shops just spent the last few years working on see like a joke.

Then to make matters worse, the original records on the files which have '1999-12-31' termination dates are now mixed in with records added this year that really should terminate '1999-12-31' and in some cases, it's now impossible to tell them apart.

Ah, but it get's worse, "just make the termination dates on all records open ended" you say. But you can't do that, see these records that really should terminate '1999-12-31' share the files with other records for the same person, item, etc, that become effective the very next day. Open up the termination dates on all the records with '1999-12-31' termination dates and you'll end up with overlapping dates. In otherwords, you'll get multiple records where you expect just one or none and this is a ABEND condition. An ABEND stands for Abnormal Termination" which basically means the program stops executing.

Hmmmm. But this can't be true you say. Something this bad would be on the Drudge Report by now, it would have leaked out by now! Well, the fact is, many shops are just finding the problem NOW. Do you really think any major corporation is now going to announce "Those Y2K ready letters we mailed to all our shared holders last month were printed in error, you see we have found another problem, a problem so serious, it can't possibly be fixed in time".

Besides, the few programmers who know about this now have surely had the screws put to them to keep quiet about it "until a solution can be found".

-- Helium (Heliumavid@yahoo.com), October 02, 1999

Answers

I think ol' Gary should hear about this.

-- cody (cody@y2ksurvive.com), October 02, 1999.

AAAARRRRGGGHHHHH!!!!! NO!!! Not the blue screen of Death!!

-- Billy-Boy (Rakka...LIB@TOTAL.LIB:FAILURE), October 02, 1999.

I may be wrong, but I vaguely remember reading this piece about a year ago.

dave

-- dave (wootendave@hotmail.com), October 02, 1999.

Any competent database manager can repair this. If there is no flag in the database to identify these records than have the incorrect expiration date of 12/31/99, simply compare a backup copy of the database before the update to determine which records do not actually expire on 12/31/99. Modify the records accordingly.

-- Steve-Oh (sron1234@aol.com), October 02, 1999.

...any competent data-base manager..

Aye, there's the rub...there are just 26 work days 'till Turkeyday (no heavy lifting in December) and the managers with intimate knowledge of the database, the institutional memory, were canned in the '80's and '90's to increase the bottom line.

Just as the Railroad's bottom line must have looked good the year they sold the Alexandria- Arlington Marshalling Yards to real estate developers.

-- K. Stevens (kstevens@ It's ALL going away in January.com), October 02, 1999.

Helium,

Basically the problem you are describing is that they have no way of knowing which is the REAL termination date. While a novice might make this mistake when designing a system I doubt it occurs very often in real life and I doubt it would go undiscovered for long.

Dates like that *always* appear in one report or another and someone would likely have noticed. Unlike a 'regular' Y2K problem where people looking at a report would assume the year 03 meant 2003 (in the context of the data they are dealing with), here they would be looking at a year of 99 and *not* the year they are interested in. How long do you think a sales rep or insurance agent would put up with not being able to see when a contract expires? Not long I assure you.

---------------

K. Stevens,

Aye, there's the rub...there are just 26 work days 'till Turkeyday (no heavy lifting in December) and the managers with intimate knowledge of the database, the institutional memory, were canned in the '80's and '90's to increase the bottom line.

No one is *just* discovering this now and I disagree with the "institutional memory" theory. I'd agree if you were talking about code where activity could be taking place in modules you've never, ever seen. But when talking about databases, understand that the people working with them know them intimately. They have to.

In fact, when I 'take over' a system the first thing I do is look at the database structures since it tells me volumes about what the code I'll be looking at later does. I don't think *this* problem poses any real threat. Could there be systems out there with this probem? Sure, but I think it's about a prevelant as the 9999 problem was... -TECH32-

-- TECH32 (TECH32@NOMAIL.COM), October 03, 1999.

Sorry, I don't buy the idea that migrations typically add "effective" dates that will now cause problems. I've worked with big databases for lotsa years, and this sounds like a pseudo-techie hallucination.

If you're adding effective dates, or any data field, you'll probably be recompiling the programs that read the data. Some databases wouldn't require the recompilation. Either way, the reading program doesn't have to HANDLE the data until it is programmed to. If you're adding date fields that you expect to use at some future date, it has no effect at all until the program that reads it has been PROGRAMMED TO READ IT.

There is absolutely no need to use a 4-digit year, such that if the year were expressed as two digits it would be understandable by the date logic used elsewhere in the program. I grant you that some brain-dead system designer might DO that, just as some put non-date data in date fields. (There are some horrible designs out there.)

Chucking in a new data field doesn't somehow make the reading program go nuts, just by being there. This is a data field, not a flu virus. It's ludicrous to say that we have some huge unexpected problem caused by the common use of this fairly unlikely logic, in widespread database migration situations. Like the 9/9/99 problem, this is an overstated danger of something that is probably extremely rare.

Ok, that said, there IS a problem with 12/31/99 generally, and the remainder of the cited article is a good discription of the problem. Programmers started talking about this as an inevitable part of the whole Y2k problem - it's not some hidden bug just now being discovered.

12/31/99 has been widely used in thousands of databases and programs for 20 years. I've seen the false-end-of-table situation hit. (In one case, the error was running for years, and no one knew it. It finally turned up in machine-usage stats, because the transaction with the problem was bogging the machine thru excess I/O.)

It really will hit us just like the 9/9/99 problem was SAID to work. It will cause EOF conditions, faulty purges, you name it. But it will hit in the middle of the 1/1/2000 problems, and no one will bother to separate the two. The symptoms and the fix are different - the 99/00 problem is usually repairable by logic changes (though 4-digit expansion is better) but the 12/31/99 problem is almost always an expansion-and-cleanup situation. That means database unloads and reloads, and it is the main reason the quick fix will often fail. It is a very different problem from the year rollover, but only the programmers will know or care.

(Oh, yeah. What an ABEND condition is depends on how you code it. Multiple records returned do not necessarily give abort situation - it might be a perfectly good result. I got some real problems with the technical advice this writer was using.)

-- bw (home@puget.sound), October 04, 1999.

And the 12/31/99 would be converted to 12/31/1999, as the writer said. And it a 12/31/99 value intended as "keep forever" would be mistaken for a 12/31/99 value meaning "actual expiration" (or whatever the field was intended to convey). Big problem, you bet.

-- bw (home@puget.sound), October 04, 1999.