Recovering a workflow

I've discovered a powerful option within Informatica's Workflow Manager. This might be old hat to some of you, but it was new to me, so I thought I'd share it.

In general, restarting a down workflow is kind of a pain, especially if you have one as complex as the main one I maintain:


In this workflow, I've identified a couple of "merge points". These are sessions that run all by themselves. The beauty of these specific sessions is that if the workflow fails at some point before these merge points, I can "Restart workflow from task" and once it gets up to the "merge point", I can then "restart workflow from task" at the merge point, and then let it run from there.

There's a better way - suspense.

In the Workflow General properties (Workflow -> edit -> general tab) you'll see a checkbox called "Suspend on Error". Honestly, why this isn't checked by default is kind of a mystery, because it's a beautiful, powerful thing.

When a session fails, the workflow will go into "Suspend" mode. When in suspense mode, you have the option to "recover" the workflow. Once chosen, the workflow will restart whatever down job there is, and then finish the workflow from there. The beauty of this option is that you don't have to intervene at the merge point. So, if a job that would normally run in parallel to other sessions fails, you don't have to then restart the workflow from the merge point forward. Informatica retains session status and can continue on the workflow as needed.




When recovered, the failed job gets this weird red/green hybrid line to indicate that it was failed, but then succeeded.

I wonder if they use Informatica?

Saw this article on CNN today:

http://www.cnn.com/2009/US/07/15/quadrillion.dollar.glitch/index.html?iref=mpstoryview

Haha! It seems likely to me that someone probably had the wrong arrow pointed to a float field. Was probably some sort of insanely large transaction key field - just a huge integer. Notice there was no decimal value. So, they probably ended up sticking some gigantic numeric field into a credit float field.

Not that first time I've seen this kind of thing. Seems like it happens every couple of months, actually.

Edit checks are a good thing. I'm thinking if you code a system that handles credit card data, and a single credit comes across that's larger than the national debt, you might want to throw an error and abend.

You know - just a suggestion.

Backup your repository

When was the last time you backed up your production repository server? Too long? That's what I figured.

It's easy.

Log onto your production server, and sign in with pmrep

pmrep will want your domain name and repository service name. This is why it's helpful to have the admin console loaded so you can reference those names easily. If you're like me, you don't play admin very often, so I tend to forget what the actual names are.

pmrep is kind of an odd command, in that it's an interactive command line program. So, when you run pmrep, you'll have to then run a "connect" command. Once you're connected, then you can run a "backup" command. Type help for a list of possible commands.

For backup, just use -o and give it a filename. Easy.

Another useful command here is "truncatelog" - pass it something like a -t 90 option to remove all logs older than 90 days.

Mystery solved

Well, sort of. I don't know why I couldn't see the "advanced transformations". I re-installed my client, and I have them now.

Apparently, Union and a bunch of other transformations are considered "advanced" and show up in a separate toolbar. I didn't even have an option to show the advanced toolbar. The re-install seems to have fixed it.

I know you were all so worried about it. :)

In case anyone cares, this is what Informatica considers an "Advanced Transformation":

Data Masking
Flexible Target Key
HTTP
Web Services
SQL
Union
Java
XML Parser
XML Generator
Unstructured Data
Salesforce Lookup, Picklist & Merge

Union Transformations?

In a comment to my previous entry, Divya asked me to check out an Informatica quiz here: http://tinyurl.com/infaquiz

Tough quiz. Some of the questions were pretty tough. One in particular threw me for a loop - one about Union transformations.

While trying to answer it, it occurred to me that I had no idea what a Union transformation was. After the quiz, I pulled up Informatica's help on this topic, and found Union Transformation in the help, but couldn't actually add one to any mapping.

I found this:
To create a Union transformation:
1. In the Mapping Designer, click Transformations > Create.
2. Select Union Transformation and enter the name of the transformation.
...

Except that when I try step 2, I fail - because Union Transformation isn't a choice for me in my drop-down. Anyone have any idea what is going on here? Is this a transformation that doesn't come with all default installations of Informatica?

When not to cleanse data

We've had a couple of interesting discussions this week regarding data quality. One of the major areas that we've been dealing with lately has to do with email addresses. We had a problem last Friday pulling a list of email addresses for a marketing campaign, and a couple of the records were obviously bogus (something like notvalidemail@acme-hackme.com) and then as an added bonus, whoever created them on the website added a carriage return to it. So, not only was the email bogus, but it ended up crashing my job because it threw the line count off.

I made the suggestion to the web team that they filter this kind of stuff out - turns out they do, but this particular record was loaded as part of a mass import of data from our 3rd party vendor which used to manage that stuff.

In investigating these records, it occurred to me that there could be a whole level of data quality "corrections" that could be made to the data that would make it better. For example, not only do we want to exclude the bad ones, but we also want to encourage users to correct the good ones - because if you're specifically signing up for an email alert, then it doesn't do you any good if the email address isn't valid at all.

One of the examples I'd discovered in our database were things like joe2yahoo.com or joe@yahoo.co, - where they fingered the comma instead of the m - or even cp, where their hand was off just slightly (conversely, cim or cin were also present). The joe2yahoo example resulted when someone didn't hold the shift key down, and got a 2 instead of an @.

I suggested that these patterns could be matched on, and then replaced, fixing the email address, but the web developers were all very hesitant - "What if joe@yahoo.com isn't even their real email address?", they asked, "You'd be turning an intentionally bogus email address into a valid one, and then we'd be spamming poor Joe who never even asked for our emails.

In fairness, I suspect if joe@yahoo.com is a valid email address, he probably gets a ton of spam anyway.

But their argument had some merit. So yes, it would seem that sometimes, data cleansing can sometimes have unforeseen side-effects.

I'm ready to kill someone

Apparently,

NULL != a number

Doesn't evaluate to anything in Informatica.

So, if you have two variables "existing_x" and "new_x" and you're trying to route off whether they are different or not, and you do something like this:

"existing_x != new_x" in your router condition, it doesn't satisfy the condition if one of the values is NULL, and the other one is not null.

What a pile of garbage.

I had to force the NULL value to be zero before it would work.

I'm also ready to kill someone. Whoever wrote the code to handle that would be a good candidate.