Make Mine MAPPER #24 -------------------- by Rob Haeuser Julian-Date-Based Databases Curse A Search ----------------------------------------------------------------- You know it's getting bad when you find yourself counting the rings in the artificial wood grain of your work-station to determine how old it is. Can you believe it? Besides, three hundred and sixty-five seems awfully old for particle-board. Did I say bored? No, just another case of total, absolute, incredibly frustrating brain-lock. Like, how can I possibly say anything about this month's focus, Electronic Data Interchange (EDI), without being completely redundant? It's all been said, it's all being done, so, hey, let's just take advantage of it and leave it at that, okay? On second thought, I think not. I like being redundant. That way I get to say the same things over and over and over and over... Like how great and wonderful MAPPER is, how it can do just about anything, including that EDI focus thing, and how I always all-cap the MAPPER acro... Wait a minute! I think I figured out why people start sounding redundant. What happens is that their vocal chords get used to making a certain very finite set of sounds, eventually loosing the ability to generate anything remotely resembling intelligent thought outside the realm of their limited focus. So, in other words, if you don't know what you're talking about, shut up! You're probably psycho-babbling! Having said that, I'd like to shut up about the EDI thing and focus on what's really bothering me. I counted those blasted artificial growth rings a dozen times, and it seems like every fourth time or so I would count 366 rings, not 365. What's going on here? And to top it off, some kid named Julian carved his name right where the mouse pad goes. The only problem is, 3-inch-high letters carved a half-inch deep make the pad lumpy, and the mouse occasionally goes zinging off into never- never land. It has to be a curse of some kind. This is really starting to bother me. Maybe that dial-a- psychic was right, and that my stress is somehow work-related. That phone call was worth the $158.95 fer sher, dude. But must be a subliminal message. I've been doing these julian-date-based databases (say that ten times real fast for some true babble) for years now, and they seem to be doing okay. But, once in a while - leap year! Yeah! Perhaps the vagueness of this discussion is overcoming you by now. Your eyes are getting heavy... You're getting drowsy... My imaginary watch is dangling in front of you, spinning slowly, methodically catching flashes of brilliantly multi-colored hues from the klieg lights the road crew just set up behind you... Now, when I count to three, flip the page, browse through the want-ads, and buy everything advertised. What? Still here? Well, okay then, you must really have a need to know. I just wish I could tell ya. Julian-date-based databases (JDBDBs) bother the heck out of me, too. What is a JDBDB, you ask? It just so happens I have a couple of examples for you. But first, we must make an assumption: that a date is the driving event (you know, like when you were a teenager). In other words, all the inquiries go something like this: "How many widgets did we blah-blah on Wednesday?" Or "How many shoes did we mis-ship in September?" Clever, how these questions are phrased so as to make the date seem of lesser hierarchical importance than blah-blahing widgets and mis-shipped shoes. Users do that on purpose. Yeah, they do. The old "who, what where, when, and why" question may not have it in the right order. And, if you're not careful, you might organize the database on something silly, like widget types. Sheesh! What a moron! And when it comes time to answer the questions, you find yourself searching the entire database. Did I mention the fact that this was a nice sized database, say in the quarter-to-half million record range? With lots of daily updates? And all the requests ultimately have dates as subtle criteria... As you all know, MAPPER (all-caps at heart) has this rid (report identifier) mentality. A buncha rids is a database. A really good sized database can have thousands of rids. But there are physical limits, such as 2,000 rids maximum per draaawyer (that's drawer with a drawl, ya'll). In a JDBDB, the date of an event is converted into a julian date (1-365, or maybe 366...aaarrh), which is then used as a rid number. So, January 1st is report 1, February 1st is report 32, and so on, real stable-like, up to about February 28th, julian 59. After that, it's up for grabs. March 1st might be julian 60, but is it a leap-year? Try 61. What with another 305 or so days to go, you might be thinking that this julian thing sounds too flaky to be fun. You may be right. But wait! It is fun for the first year. You get to load up a bunch of reports: who cares if December 31st is 365 or 366? It's not until the year rolls over, and you need to start purging old data to make room for the new, that you start to worry. This is where the application's specific needs come into play. It might help to take a look at those examples now, eh? The first is a problem tracking system (PTS). When problems are logged, data is written into the pending area of the database (a small set of rids above 366), including the date the problem was entered. Once the problem is resolved, an over-night run converts the entry date to it's julian equivalent, anywhere from 1 to 366, and moves the data to that rid, deleting it from the pending area. So far, so good. And as I mentioned before, you're okay for the first year, since all rids start out empty. But once that first year rolls over, last year's stuff is history, and should be purged before any current data gets loaded. Yeah, so big deal. All I gotta do is run a "purger" just after midnight to pull the current julian date out of the machine, move the data in that rid to a history rid, and then gut the sucker. Voila! A clean rid, ready for the new data! Everything is fine with this arrangement as long as you are looking in the past and staying within the last 11 months. To pull a month, simply ask MAPPER for the julian equivalents for the 1st and last of the given month, and do a range search through that block of rids. The confusion begins if an inquiry is made against the current month. Obviously, our JDBDB cannot contain "future" data, so all rids representing the remainder of the month will actually contain last year's data. Not good. You must not allow your system to accidently look back to the future, so to speak. A simple edit on an entry screen dis-allowing an end date into the future will solve that problem. If it becomes just unbearably critical to pull the entire 13th month, take heart. Since I tend to use a "thousands compliment" technique, where history is the julian plus 1000, it is possible to get this data via a range search option, such as "R16-31,1001-1015" to pull all of last January on the 15th of the current January. Of course, past the 13th month, you are dealing purely with historical data, and all searches would be limited to the "thousands compliment". A recommendation: definitely write the date we've been talking about in the database (preferably in YYMMDD format), and include it (at least the YYMM part) when doing searches. This will guarantee that you don't accidently include the wrong year, month, or whatever. As for JDBDB example number 2, they're all the same, so what's there to say? Hah! Caught you watching the watch again. Sure, the idea of storing is similar, but that little "purger" in this system doesn't just clean out today's rid (so easy), but is awakened roughly 10 days prior to the end of the month to gut all rids representing the upcoming month. So on January 20th, I wake a run up to gut rids 32 through... what? 59 or 60? Doesn't matter. I let MAPPER tell me what the julian is for any end-of-month date with a simple code trick (see Figure 1). ------------------------------------------------------------------- Figure 1 -------- *Run Name: IMD14U... (are you still watching the watch?) .Description: converts any month into a julian rid range *================================================================== @CHG INPUT$ V1H6 . Get YYMM from somewhere, put it in V1 @LDV V1(5-2)=01 DC D3(V1) V3H4 \ get julian for the first IF V3 = **** .. ERROR .. bad YYMM .. how can anybody...? @LDV V2I2=31 . Guess which last day we'll try first? @1:LDV V1(5-2)=V2 DC D3(V1) V4H4 \ load last day and test IF V4 = **** DEC V2 IF V2 GE 28,(1) ; . . ERROR . . @LDV V3(1-1)=0,V4(1-1)=0 . @IF .. ya wanna look at history .. INC,1000 V3,V4 ;. ------------------------------------------------------------------- I gotta tell ya, though. Just to be absolutely, completely fail safe, after I gut what represents the upcoming month, I do a little "walk-about", as they say in Australia, checking the rid before and the rid after what I had figured was the beginning and ending of the gutted range. If there is data present from the prior year, I delete it too. This may sound a bit like overkill, but better to be safe than stupid, er, I mean, sorry. Besides, the ability to inquire against a JDBDB by dates at light speed can far outweigh the piddly effort required to keep it all straight. But I gotta tell ya: phooey! I hate leap-year. People born on February 29th should just give up the 4-to-1 myth, acknowledge their true age, and accept March 1st as their true birthday. That would clear the way for the rest of us to designate December 32nd as the new official leap-year day. It would make things infinitely easier, don't you think? ----------------------------------------------------------------- Rob Haeuser has over 17 years of Data Processing experience. He has been the MAPPER Coordinator for the Texas Department of Human Services since 1983. Rob has been writing a monthly column for Unisys World since June, 1990. This is article number 24.