Linking DataStage Jobs Together

Once you have mastered the “navigation” and asset selection options of Data Lineage reporting, it’s time to look at how DataStage Jobs are automatically linked together. By now you should be comfortable with thinking about your “starting position” for a data lineage report — your initial “perspective” if you will (what object are you standing on when you begin). You should also be comfortable with thinking about the “direction” for your Data Lineage investigation — are you looking “upstream” for “Where did this come from?” or downstream for “Where does this go to?” [In 8.7 or higher, you no longer have to request “upstream” or “downstream”, but you should still give it some thought so that you have an idea of what you will be expecting or what you are looking for].

If you need a refresher on the basics, please see Getting Started with Data Lineage!.

A typical production site for DataStage/QualityStage has MANY Jobs — hundreds perhaps….even thousands. All integrated and working together to transform your data and move it from one place to another. Sometimes they are written by one very hard-working developer, who might have all the lineage in his or her head, but more often it is a larger scale endeavor, with lots of team members, often scattered around the globe, and with varied skill sets and possibly working on related albeit independent solutions. They may know each other, or may not. How are the DataStage Jobs sequenced from a data flow perspective? How does data flow between a Job developed to process data received via FTP from the mainframe and then ultimately to a datamart that supports a reporting system? How does one Job connect to another? Sometimes it may be one giant Job, but its not likely. Intermediate temporary tables are often created for everything from checkpoints to Operational Data Stores to “parking lots” where data can be restructured or delivered to another application along the way. Workbench can sort this out and provide you with lineage through all these Jobs.

Intra-Job Data Lineage (Data Lineage between Jobs) is largely automatic. You simply have to pay attention to a few “good sense” leading practices and understand the pattern. Note that this has “nothing” to do with Shared Tables or Table Definitions at all… it’s entirely done by merely parsing thru your Jobs [this is a key reason why you can get immediate insight on 7.x Jobs that are imported into the 8.x environment — even if you haven’t compiled a single one or started your formal testing and QA process!]

Automated Services is the “parsing” step at the Advanced Tab….when you say “run” with your Project(s) checked, Workbench combs through your Jobs, looking for similarities that will link Jobs together end-to-end. [Note: If you are using 8.7 or higher, this process is called “Detect Associations” …find it by first clicking on “Manage Lineage” at the Advanced Tab, and then clicking on the larger “arrow” icon on the right after selecting one of your DataStage Projects.] Here’s what it looks for among Jobs:

a) Common or “like” Stages between the Target of one Job and the Source of another. Two ODBC Stages are in common, but so is ODBC and say, Oracle OCI. Or DB2Load and DB2Connector. Or two Sequential Stages. Or two Dataset Stages.

b) At least one column in common. [Update Note… the research leading to this post was done originally in early 8.1 … I’ve since discovered in 8.5 and higher that the connection is more flexible now, and one column name in common is no longer required. The default values and Stage type are the major controlling factor in bringing Jobs together.]

c) Same hard coded values (yuk…who does that?) OR….same “default” values for Job Parameters for the critical common properties. For RDBMS type Stages, it’s ServerName, Schema, and Tablename. For Sequential type Stages, it’s the filename. The Automated Services will put together multiple Job Parameter default values if needed.

If your team follows good practices of having parameter sets or common values for things like an ODBC DSN, a high degree of lineage will often occur immediately after the first Automated Services (informally known as “stitching”) occurs. Expect that the “first” time you run Automated Services, it could take a long time and be very intense. Do it during off hours if you have hundreds or thousands of Jobs. After that first time it will recognize a delta and only parse thru the Jobs that are new or have been changed.

Now when you do your lineage reporting, and you start while “standing” on the target Stage of an application down-stream Job….when you ask for “Where does this come from?”, you can expect to see Stages through many Jobs back to the ultimate source. If it dead-ends surprisingly, it’s probably because one of the three rules above didn’t apply, or there’s an odd Stage that isn’t supported for lineage (Redbrick is one of the only ones I’m aware of at this point).

If that fails, use the “Stage Binding” at the Advanced Services tab. This is one of the options for “manual” binding of metadata — a sort of “toolbox” of wrenches and bolts for when you need ’em. The Stage Binding is designed to be used, only when absolutely necessary, to “force” two Stages together for lineage purposes. It is fairly easy to use…it prompts you first for the name of a Job, and then when it presents you with a list of Stages, slide your cursor over to the right so that you can “add” a Stage from another Job. I have used it effectively for unsupported Stages, as noted, but also when the rules above don’t apply. In one case I was sending the output of an XML stage into a Sequential File…and in the next Job, I was reading that with an “External Source” Stage. There is nothing in common between those stage types, and the columns were entirely different (the Sequential File Stage contains a column called “myXML” and the External Source merely carries the output of a unix list command (a set of filenames). I was able to establish perfect lineage however, by using a Manual “Stage Binding”, forcing the Sequential Stage of the first Job and the External Source Stage of the second Job to be “bolted” together.

Good reporting! Next topic — Connecting Database Table objects to your Jobs

Ernie

Getting started with Data Lineage!

Last night I was reminded about a series of blog entries I’ve wanted to make concerning the InfoSphere Metadata Workbench and how to get the most out of its Data Lineage capabilities. The Workbench is very powerful — it illustrates relationships between processes, business concepts, people, databases, columns, data files, and much, much more. Combined with Business Glossary, it gives you reporting capabilities for the casual business user as well as the (often) more technical dedicated metadata researcher.

I’ve had a variety of entries about Workbench in the past two years (see the table of contents link in the top right, and find the metadata section), but nothing on “getting started”. As Metadata Workbench starts to support more and more objects, knowing certain skills and techniques becomes that much more important. This is especially true when trying to gain the most from Metadata Workbench when it is being used to illustrate Business Terms, Stewards, FastTrack Mappings, DataStage Jobs, Tables and Files, External ETL Tools, scripts and processes, operational metadata data and a vast list of other data integration artifacts.

Many of you who start with Metadata Workbench begin with DataStage/QualityStage Jobs.

[Note ..this particular post applies mostly to pre-8.7 Metadata Workbench, but it is still worth reading initially, even if you are on a newer release of the workbench…the remaining posts in the series discuss the critical things needed for linking jobs together and linking jobs to their source and target table or file objects]

So I will start there.

Once you have mastered lineage with DataStage, and its combination with other objects, you can then easily move on to other concepts for non-DataStage metadata, which I will also cover in this series of blog entries. If you are using Metadata Workbench and are not a DataStage user, stay tuned. As we progress I will take a tour through Extensions, Extension Mappings, Extended Data Sources and all other such concepts.

MAKE SURE YOU START learning about lineage and Metadata Workbench using a small number of Jobs. NO MORE THAN 10 – 15. Any more and the results will be overwhelming or confusing, and will prevent you from understanding some very important and critical rules. Find 10 or so Jobs, probably in one application, and in one folder or related folders, that have things in common. Jobs that share datasets or are part of an overall flow. Get to know these Jobs before ever opening the Workbench to review them. Eventually you will be comfortable using the Workbench to review metadata on Jobs you have never seen — but that’s a poor way to learn the power of the tooling.

From those Jobs, pick a reasonably complex one. Maybe one with a lookup or a Join, a reasonable sequence of Stages (8 to 10 or so) and preferably a single “major” target. Since you are learning about the Workbench, you should be familiar, even intimate, with this Job. That will help as you learn the various ways to navigate through the user interface, because you will know what to expect at each particular dialog, report or screen.

[this first “getting started” assumes that you have NEVER performed Automated Services against your DataStage Project….if you have, it’s ok, but you might not get the same results as I am outlining below — you may get more metadata than I am describing in this initial learning step. …and if you don’t know what I’m talking about (yet), that’s ok too…]

Log into the Metadata Workbench and notice the “Engine” pull down at the left. This is the list of your DataStage Servers and their Projects. Open up the project, it’s folders, and find your Job. Click directly on it. Scroll up and down in the detailed page that appears. there is the main page with the picture of the Job (click on it and you will get an expanded view in a new window of what the Job looks like). The metadata you are viewing is up-to-date from the last moment you or a developer saved the Job in the DS Designer. Also there is a very important listing of the Stage types in the Job, along with their icon. Note below you have many “expandable” sections for things like Job Operational metadata…..investigate the options.

Now click on the “main” target Stage of this Job. This brings you to a similar looking detail page, this one for the “Stage.” Look around, but don’t click anything — when you are ready, select “Data Lineage” at the upper right. As you do so, consider “where you are standing” (you are on a “Stage”) and what sort of lineage you would like to see. As you will discover, knowing “where you are” when you start your lineage is very important.

[If you are using 8.7 or higher, at this point you should soon see a single graphic, probably just one big icon in the middle of the page. This is the lineage for THIS Job — all by itself (unless you’ve already done some other work in lineage, in which case you will get other things linked to it). Look around and then click on the “Expand” link that is on the Job itself. This brings up a detailed page “for that Job”. Look around… “grab” an empty part of the screen with your left mouse button and move the picture around; zoom up and down (there is a little bar at the top left for this). Click on the other buttons that show you both a “mini” edition of your lineage as well as a key for the kinds of lineage that are displayed. Then move to the next post in this series (Linking Jobs) ]

The default option at the next dialog is “Where did this come from”. Ignore the three checked boxes for now and click “Create Report”. This will comb through ALL the possible resources for “where” data for the “stage you started on” came from. Look thru the list. Note also the highlighted line. Move it up and down. This highlight bar lets you select EXACTLY which resource you’d like to see for your actual report. The “total” collection of lineage resources is in front of you right now — you will select which one you want for a detailed source-to-target report. This is often a point of confusion because the highlight bar is not always obvious. Data lineage doesn’t show you “ALL” the sources — just the path to/from the ones that you select [we’ll contrast this in a later entry with Business Lineage, which DOES provide a summary of ALL sources or ALL target from a particular resource].

Look at the bottom of the page. Find the button labeled “Display Final Assets”. Click it. The list of objects above should get much smaller. Most likely, it should just show the source stage for this Job, or maybe its ultimate source as well as a lookup source stage or a source for a Join. Pick the primary source stage for the Job and then click “Show Textual” Report.

Review the result. The textual report isn’t as pretty, but it tends to be more scalable. Scroll up and down, and note what you see on the left, and the Job details you see on the right. Everything is hyperlinked. Now find the little triangle towards the top left of this center pane where your report is (it’s called Report Selection or similar) and click on it. That should expose again the “assets” page. Now you can try “Show Graphical”. When you get there, play with it. Grab some white space around the diagram and move the whole thing around…..try the zoom bar in the upper left. Click on the various icons in the lineage and then right mouse on one of the stages and find “open details in new window”. That will bring you back to a detailed viewing page and the process starts again.

What happens if you choose the target stage of your original Job (the first stage you selected earlier) and ask for “Data Lineage” and select “Where does this go to”? If you haven’t done Automated Services as I’ve noted above, you should likely receive “No assets found” or “No data for the report”. This is because it’s the “final” target — there isn’t anything else. “Where did this come from” will yield a similar result if you happen to be “sitting” on a source when you start your lineage exercise.

If you practice this, you should become very familiar with the lineage report user interface, and will have a strong base for moving forward with more complex, and deeper, scenarios.

Next entry: Linking Jobs together……

(link to next post in this series: Linking Jobs )

Ernie

Incorporating Java classes into your DataStage Jobs

Java comes up a lot when we talk about “real time.”   Not that Java in particular has any special dibbs on the term, but frequently when a site is interested in things like Service Oriented Architecture (SOA), Web Services, messaging, and XML, they are often also interested in Java, J2EE, Application Servers and other things related to Sun’s language standard. 

Integrating Java with your ETL processing becomes the next logical discussion, whether “real time” even applies.   There may be some functionality, some existing algorithms worth re-using, some remote java-oriented or java managed system or message queue that contains valuable source data (or would be a valuable target), that you’d like to integrate into a data integration flow.   DataStage can easily be extended to include your Java functionality or take advantage of your Java experience.

There are two Stages that used to be referred to as JavaPack that are included with DataStage:  JavaClient and JavaTransformer.   Both allow you to integrate the functionality of a java class into the flow of a DataStage Job.   JavaClient is used for a sources or targets (only an output link or only an input link), and the JavaTransformer is used for row-by-row processing where you have something you’d like to invoke for each row that passes through.

DataStage provides a simple API for including java classes into your Jobs.  This API allows your class to directly interact with the DataStage engine at run-time — to obtain meta data about the columns and links that exist in the current executing job, and to read and write rows from and to those links when called upon to do so.   You define several special methods in your class, such as Process(), that the engine calls whenever it needs a row, or is giving your class control because it’s ready to give you a row.  Within that method you have various calls to make, such as readRow [from an input link] and writeRow [to an output link].    You can control what comes in and goes out, and also process rejections based on logic in your class.  Other than that, your class can do whatever it wants……read messages from JMS queues, invoke remote EJBs….whatever.  

The JavaPack is very well documented, with examples and descriptions of all the API calls.    However, I’ve included an additional example here for anyone who is interested, including java class, source, .dsx and usage notes.    Have fun!

-ernie

btw…I haven’t exactly figured out yet how to best get the names of the files below represented here on this blog, but if you save them from here, each file except the Readme begins with “ExamineRows” and should be ExamineRows.dsx (for the export), ExamineRows.java (for the Source) and ExamineRows.class for the actual compiled class.   I haven’t had a chance to re-try it after downloading from here, so worst case, you’ll need to recompile the class yourself in your environment.  Otherwise, it should run in v8 “as is”.  See the file at the Readme link for details on the expected classpath in the Job, etc., and read the annotations in the Job itself after you import it.  -e

Examine Rows Class, Examine Rows Java Source, Examine Rows Readme, Examine Rows DataStage Export

Simulating end-of-file for Real Time ETL Transformations

In my initial entry for this blog (https://dsrealtime.wordpress.com/2007/11/23/what-is-real-time-etl-anyway/), I wrote about some of the issues facing “always on” transformations that continously read data from a real-time source (MQSeries, sockets, named-pipes, etc.).   Tools that provide a graphical metaphor for transformation design, and find their origin in high volume batch functionality (classic ETL tools fit this description), are often challenged by the need for a signal that terminates processing.

If you are just doing one-to-one loads of messages to an rdbms, this issue might not matter.  But if you are concerned about individual units of work, have multiple rows “inside” a given message (like an XML document with repeating elements), or are processing individual service requests for a myriad of SOA clients, then something needs to be done to recognize these logical groupings of rows.  Transactional tools that fire up tiny, entirely compiled modules (WebSphere TX being one example) were designed for this, but classic ETL tools, often with interpreted connecivity and performance features that require some ramp-up time, need to stay “always on” for maximum efficiency.   Blocking functions, those that have to “wait” on all rows for completion, are particularly sensitive.  These include Aggregations, Sorts, Pattern Matching, XML document creation, and others.

DataStage and QualityStage manage this by supporting a concept known as end-of-wave.  Driven automatically by the receipt of a SOAP envelope, or on developer control by the reading of “n” messages or other factors, end-of-wave is a “signal” that is sent thru the Job, following all rows in a group, along every possible path.  The end-of-wave signal tells makes all the downstream Stages “think” that processing is complete.   The Stages who block by design (Aggregator, QualityStage matching, XMLOutput, etc.), are notified to go about their business of clean-up or processing “as though” they’ve drained an entire source and hit end-of-file.  However, in the real-time pattern, they haven’t.   End-of-wave is merely the signal that separates two requests from entirely independent users, or the related contents of one MQSeries message from another.   The Job, as noted before, is “always on.”  It simply continues running and immediately receives data for the next “wave.”  This behavior is inherent in the Information Services Director, as it manages traffic from incoming SOA clients via SOAP or other bindings, and is directly available in Stages like the MQSeries Connector.

The following diagram pictorially represents the end-of-wave concept.  Moving from left to right through the Job, end-of-wave follows each of the co row groupings.

  

This is one way of handling the need for high performance parallel transformations in a real-time scenario where volume is defined as lots of concurrent, yet independent sets of rows……while the same transformations and tooling is to be re-used for massive volumes (read: 100’s of gigabytes or multiple terabytes) in batch.  There are other approaches I’m sure, but be certain that the tool you are using has a way to deal with it.

Tips for using Web Services Pack with DataStage Part I: Getting Started

Thought I’d start sharing the notes I’ve collected over the years of working with the Web Services Pack.  This is the ability for DataStage to be a SOAP Client; for a DataStage Job to reach out and invoke a Web Service located “out there in the ether.”  This is a reference to the benefits of Web Services and the fact that you don’t have to know where the Service is located, what machine its on, who wrote it, or what language it’s written in — doesn’t mean it’s not a good idea, especially if your business is relying on it, but I think you get the point.   Give me a WSDL document, and that should be enough to invoke some type of remote function [this is what you might call the opposite of WISD, or RTI, which allow you to publish a DataStage Job or other Information Server asset “as” a Web Service].

Before you get started with it, ask yourself how comfortable you are with Web Services technology.   I speaking here about SOAP over HTTP in particular, the protocol supported by WS Pack, but it would be wise to get yourself a good intro to all of SOA.    There are good books out there, but also 1000’s of great resources here on the web.   Just search in your favorite browser for something like “Web Services Introduction XML,” and poke around.  You’ll find something for everyone.

Then you need the software.  If you are a release 8 user, it’s just “there.”   If 7.5, you have to speak with your account team about downloading it, but it won’t cost you anything.   The installation is simple, although be reminded that there is a client side and server side component.

Once installed, you’ll have two new Stages, the WSClient Stage and the WSTransformer Stage.  WSClient is for Web Services that will be a source or target to your Job, and the WSTransformer is for those Services that you expect to invoke on a row-by-row basis.

I have to put in a plug for the documentation.  The WSPack documentation is very thorough.  It has some very easy to understand graphics that do their own nice job of introducing Web Services in general, as well how WSDL, SOAP, SOAP Envelopes and SOAP Bodies come into play.  What I’d like to add in these entries are some other points to consider as you prepare to include Web Services in your jobs to help you be more successful.

A stand alone Web Services testing tool is a good idea too, unless you are already comfortable using Web Services from a common IDE for Java, C#, .NET, etc.

Finally, find yourself a good Web Service to start with, just to learn the mechanics.  There are some great ones out at www.xmethods.net .  You can test them there to see how the function, and get a solid idea of what their input and output requirements are.   There’s a nice mix of publicly available services there; some are free, others are part of a business.  The free ones are sponsored by folks who are making known their expertise.  Some very solid consulting firms and companies with Web Services expertise are represented there.  I’ve seen many of the services hosted at that site continue to function and be supported for more than five years.  For your first foray into Web Services pack, pick one that has very few input and output arguments, accepts and returns one row (a perfect candidate for WSTransformer) and doesn’t require you to pay anything or come up with an access code.

This entry is getting long.  Next time I’ll walk you thru access to the Service and then discuss how to prepare for a more complex one that you might find inside your enterprise.

Why use ETL for Real Time? …for Metadata support!

ETL tools were designed for back-room, nightly batch processing, right? Yes…maybe….I suppose. If you look at their history, with most ETL tooling born in the decision support and data warehousing world, the biggest challenges were for point-in-time refreshes and loading of vast amounts of information. However, requirements have evolved, missions have changed, and ETL is no longer used only for decision support. Indeed, a certain percentage of sites never have used ETL for data warehousing, even if that is admittedly still a large segment of the application for such tools and technologies. Today, ETL is a great choice for real-time, and it’s safe to say that the tools are now being designed for top notch real-time functionality. I’d like to just stop using the term “ETL” (or ELT, ETML and some of the other acronyms that have been floating around for years)! It’s not your father’s ETL anymore……..[but terms stick, so for now we’ll go with it unless any of you have better suggestions for us and our friends at the analysts 🙂 ].

If not ETL for Real Time, what else? A lot has already been written on ETL (Extract Transform Load) vs EAI (Enterprise Application Integration), with ETL generally being credited with better high volume abilities, and EAI better at complex, multi-construct (occurs, record types) sources and targets, and other pros and cons for either. As I learn more about how to manage this site I’ll create a page with my favorite links on this subject. In many of these comparisons, real-time often defaults to the EAI category.

However, one area that is often overlooked in this comparison are what you might call two “soft” issues — the user community, your teammates who will actually be doing the development, and the requirements for meta data management. While there are exceptions, ETL tools “tend” to be used by what I like to refer to as “data professionals.” These are folks who may have formal programming backgrounds, but gravitated to their role in the enterprise because they understand the business and they know the data. With their initial focus on business intelligence, ETL tools (I know, beauty is in the eye of the beholder) are often more inviting to this type of user. Not an “end-user” by any means, but also not the user who is typically comfortable with C header files, java types and code snippets. ETL vendors have competed for years on the usability issue. Their success with DBAs and more technical end users is a testament to their appeal.

The other “soft” issue worth noting as ETL moves into “real time” is the support for meta data. No longer is meta data something that people merely pay lip service to. Data lineage and impact analysis — the abilities to link a column name to a real-time Service, its rdbms target, its ERwin model AND its business intelligence report are unique to ETL tools. Most EAI type tools, until recently, could hardly spell metadata, let alone provide impact analysis and data lineage reporting from soup to nuts. This is changing, but deep metadata reporting has been a key component in the data warehousing space (and thus receiving massive investment from ETL vendors) for ten years or more.

Data Governance, regulatory compliance, and metadata management are on everyone’s minds. We can’t pay lip service to metadata and data lineage for any kind of data integration. SOA and real-time data integration need the deep metadata support provided by ETL tooling, as much as business intelligence applications do.

Increasingly, ETL tools, and the platforms they operate in are being chosen for real time data integration because of their support for meta data, and the preference of “data professionals” for these tools over their “closer-to-the-code” IDE tool cousins for programming development.

Ernie

What is Real Time ETL anyway?

What is Real Time ETL? What does it mean? This question keeps coming up in discussions with customers and prospects, for enterprises large and small, and with tool jockeys and home grown coders. It surfaces in debates about EAI vs ETL (subject for another blog), Changed Data Capture, transactional vs batch processing, and more. I won’t debate the definitions of real-time, right-time, real-time data warehousing, active data warehousing, just-in-time or near-real-time — a lot of really smart people have already been there. I just want to look at what people are actually doing, and calling, Real Time ETL.

Trying to formally define real time isn’t easy — there are so many points of view, and critical differences based on industry segment. Those of us in the commercial “data world” spend lots of time discussing the finer points of “real time”….however, I stopped trying to come up with a single definition after reading pure academic and engineering definitions of “real-time computing” that talked about robotic arms in an assembly line reacting in microsecond “real time” to things like minute temperature changes!

I’d like to reflect here instead on the technical aspects of common patterns that those of us in the data integration space run into regarding Real-Time ETL, and mention some of the gotchas that often go overlooked. I see four basic “patterns” that, depending on your point of view and problem you are trying to solve, qualify as Real Time ETL:

• Frequently executed ETL processes (ie. every 5 minutes, one minute, or every 10 seconds). Really a “batch” pattern, but run in small windows with tiny (by comparison to large batch loads) quantities of data.
• Messaging or other “continually live” medium as a Source.
• Messaging or other “continually live medium as a Target.
• Request/Response with a continually live medium on either end (Source and Target).

The second one above interests me right now, as I’ve had numerous questions on this subject in the past few days. I want to speak here about the technical definition for jobs, maps, procedures (or whatever you call your ETL processes) that need to “read” data from a commonly accepted “real time” technology. Real time sources may be popular messaging engines, such as MQSeries, TIBCO Rendevous, or MSMQ, or java based standards such as JMS, or more custom based solutions such as sockets or even named pipes. Most ETL tools can access these, or provide extensions that make it possible to utilize some of the lesser known APIs.

This is the most commonly requested pattern. When someone says “I need Real-Time ETL,” it generally turns out that they want to “read” from such a source. Reasons for needing it vary. Some sites desire immediate updates to decision support systems or portals, while others are merely “dipping” into an available source that is passing through for other purposes. An already built MQ Series infrastructure, shipping messages between applications, are often the perfect source of data for ETL, whether the objective is immediate updates or not. It’s just “there” and available…and simpler to get than trying to wrestle with security folks for access to source legacy systems. Of course there are hundreds of variants, whether the target is decision support oriented (data warehouse or datamart), or ERP (such as SAP). Either way I’m talking about a persistent target.

Regardless of the reasons, such ETL processes have to deal with issues like the following:

• Always On. Typically an initialization issue. ETL tools do a lot of preparation when they start…they validate connections, formally “PREPARE” their SQL, load data into memory, establish parallel processes, etc. Twenty seconds of initialization may be acceptable in a 45 minute batch job that processes ½ gigabyte. In a real time scenario, that’s unacceptable. You can’t afford to perform all of that initialization for every message or packet….it needs to be done once, then leave the process “always on” and waiting for new data. I like to think of it “floating” while it waits. Of course, this invites other problems…
• End-of-file processing for “blocking” functionality. If you have an “always on” job, what do you do if someone wants to use an aggregation or sum() function? How does the process know when it’s finished and can flush rows thru such an operation? This is particularly critical when we move on to Web Services in the request/response pattern, but equally important when reading messages that contain multiple rows, such as when the message payload is a complex XML document.
• Live vs buffered or in-memory lookups. A common technique for performance in large volume batch processes is to bring values into memory. Same issues for performance in “always on” jobs, but consider that “always on” means needing a strategy to refresh that in-memory copy. Or else ensure that a constant connection to the original source is feasible and performs well….and that the DBA who owns the real time source won’t kill your long running database connection in an “always on” scenario.

These aren’t the only issues, and there are numerous ways of dealing with them. Make sure the tool or techniques you choose give you ways to deal with these problems. Next time I’ll share my notes on these issues and the other real-time patterns in more detail.