Uncategorized
DataStage Dates – An Easy Solution
Oct 10th
After spending several hours yesterday in the Transformer Stage of IBM’s DataStage 8.1, I was incredibly frustrated with the date functions. No matter what I did, I couldn’t seem to get the StringToDate function to work on my input file. The data was coming in a format of YYYYMMDD, but when I tried to use StringToDate([FieldName],”%yy%mm%dd”) it kept complaining of the data being in an invalid format. I was tasked with modifying three jobs in an hour and a half. It’s not a difficult task when you find something that works, but DataStage 8.1 seems to behave in a different manner than DataStage 7.5 and the old way was not allowing the job to run.
I went for a walk, picked my brain, and finally came up with a solution. Instead of using any date related functions in the stage variable portion of the transformer stage, I’d treat the data like a string. Since the data could be either a valid date (20091011) or 0, I used an if then else statement to handle the 0. If the record had a date of 0, it was set to 9999-12-31, otherwise I took the various pieces of the input string and concatenated the -’s in the date. The code for each staging variable date ended up being:
IF Len(FieldName) <> 8
THEN ‘9999-12-31′
ELSE FieldName[1,4]:’-':FieldName[5,2]:’-':FieldName[7,2]
Using DataStage’s concatenation character (:), I was able to pick apart the input string and convert it into a format of YYYY-MM-DD. At this point the data is still in string format. The len() function checks to see what the length of the input data is. Since we are dealing with data in YYYYMMDD format, any data having a length not matching 8 characters can be rejected as invalid. In the output link column, I then used the following code:
IF NOT(IsValid(”Date”,StageVariable))
THEN ‘9999-12-31′
ELSE StageVariable
This code seems to work perfectly. Say that ABCD1122 is passed. Going through the staging variable code would result in ABCD-11-22 which is an invalid date. The NOT(IsValid()) function takes care of that. Since ABCD-11-22 is not a valid date, it is converted to the default (9999-12-31) and loaded.
As you can see from the above method, we have a way to easily take the date and convert it to a proper format (YYYY-MM-DD). This can be done with any uniform input format. If the input is not uniform, it will be much more difficult if not impossible to validate the date. It is my very strong belief that you should ALWAYS be able to expect the same format for each row in the input file. If you have a record with a date in format YYYY-MM-DD, YYYYMMDD and YYMMDD, you need to go back to the developer of the input file and asked that it be cleaned up. There is no reason to pass a variety of different formats in the same column and doing so is only going to result in disaster.
Having said that, implementing this technique is rather easy. First, consult with the source file developer or take a look at the file yourself. Determine the possible values for the input and identify which format it is in. Next, validate the data and construct the input into the ideal format (YYYY-MM-DD). The IsValid function for dates ONLY accepts YYYY-MM-DD, therefore this is critical. Finally, implement the validation function in the output link. This ensures that you trap any garbage that might have been passed along.
Bad Dates Play at the Peterborough Players
Jun 17th
Last night, my grandmother and I went to “C&S night” at the Peterborough Players. We followed some directions and initially got lost. I paid for access to VZ Navigator for the day and plugged in our destination. The GPS on my phone, of course, gave us better results than the handwritten directions we had!
We arrived at about 7:45; the play was to start at 8. At the time we arrived, they hadn’t even started letting people into the playhouse. We arrived while a woman was explaining some things, and shortly thereafter we were allowed to enter. We were told the normal things like “no photography” and “please turn off your cell phones;” and since this was a dress rehearsal, the house manager (if you can call him that), explained that the set was completely finished (usually not the case for a dress rehearsal).
The play featured only one actress, a leggy brunette haired woman with blonde highlights. She was visually pleasing, and she was an exceptional actress! I was convinced only once that she had forgotten her line. Had she slipped up at any other point, she did a fantastic job hiding it. While her facial expressions, body language and overall appearance helped make the play more interesting, I did not find the play to be particularly good.
One of the things I disliked was that the actress was all by herself. While it allowed me to appreciate her exceptional acting even more, I felt that one or two more actors on the stage would have made it far more enjoyable. The entire play took place in her New York apartment, with her trying on various sexy shoes and telling a story the entire time. A few times, she even stripped down to her undergarments on stage to change. John Riley commented that this was to keep the men in the audience interested.
That’s essentially what the ENTIRE play consisted of, a story. The story of the woman’s life was interesting enough, but I just found the play a bit lacking. The women in the audience seemed to thoroughly enjoy the play. From the male perspective, John Riley said it perefectly: “You know how there are chick flicks? That was a chick play.” Had I not been with my grandmother, I would still have found the play entertaining enough to stay for the whole thing; several people, however, left at intermission.
Latest Comments