There are a lot of concerns here. From the "it should be pretty simple,
everyone is making it so complicated" to "it really can be complicated".
Also, security on a staging table can be important also.
For example, let's say the outside source is sending you data like this:
Mother's maiden name
Social security number
Bank account number
Even though that's just 'staging' data, there is a slight possibility you
may want security on that.
Now, let's assume that the outside source is sending you data as a stream
file. We don't know if they aren't going to mess this up on occasion and
send you data with all sorts of exceptions, like:
- Name is bigger than our Name column.
- They put alphabetic characters into the SSN.
- They put currency symbols, thousand seperators, decimals, and negative
balances may have the sign leading or trailing.
You may want to read directly from the stream file (like using the Scott
Klement APIs) to get granular editing.
In this case, your staging data wouldn't even be in a library.
I happen to think that taking stream file data, putting it into yet
another staging area just for DB2 and they posting it is a waste of CPU,
disk and increases maintenance. However it's often done to pass data to
the 'accounting' programmer who looks at handling stream file data as a
tool of the devil. (Not picking on anyone I know - at my employer or
So, while someone really sharp with SQL could probably write something
that reads stream file data and posts it directly to DB2 (like Matt
Olsen's example) I suspect it wouldn't have the granularity of editing
that may be necessary.
Actually, this kind of intense editing was one of my first COBOL
assignments in college.