DSXchange

Don't invoke the function while the job is running (which adds unnecessary overhead) - invoke it after the job has finished. Of course, you will need to attach and detach the job and name the stage and link explicitly when you use this method. However, these names could be passed as arguments to a g...

Welcome aboard! :D DataStage captures this information. Link row counts are retrievable (typically once the job has finished) using the DSGetLinkInfo() function, or can be retrieved by interrogating the job log (for example DSGetLogEvent() function). In a job sequence you could create a Routine to b...

I don't believe there is a "count distinct" aggregate function in the server job Aggregator stage. You could, of course, group by all three and simply count col3 then pass those results through a second Aggregator stage to group by col1 and col2 and to sum the summedcol3 generated by the first Aggre...

All link row counts can be obtained from DSGetLinkInfo() function. If the job has not aborted, link row counts can also be obtained by interrogating the log "active stage finished" messages. If the job has aborted, and has been reset, the number of rows on the reject link may also be able to be capt...

5150811 is the number of times a lookup was successfully performed against the hashed file. It is unrelated to the number of rows in the hashed file. Your expectations need to be managed better! Indeed, it is possible that the hashed file contains fewer than the number of rows that were written to i...

Don't you need double quotes if there are spaces in the pathname?

Try putting a space between '%' and "||" to make the job of the parser easier. Make sure, also, that the table and column names are correctly cased. EVAL is a way to construct a virtual column "on the fly" using a BASIC expression rather than an SQL expression. The expression must be enclosed in dou...

The INSERT statement in the error message reports only 20 columns.

It all got a bit hairy when they introduced private, public and shared public cache, which - although described fully in dsdskche.pdf - I'm sure almost no-one really understands. One would think that a before-stage subroutine that logs a message would be one way to resolve this; does it appear in th...

For whatever reason, only columns up to an including CW_TELEPHONE (that is, the first 20 columns) are being used in the INSERT statement. Check that you have derivations for all 34 columns in the preceding Transformer stage. Check that all 34 columns appear on the input link of whatever stage type y...

What you're really after is a "contains" operation. This is doable, but will not perform all that fast. From TCL something like: SELECT country_name FROM table WHERE EVAL "INDEX(city_state, country_name, 1)" <> 0; SELECT country_name FROM table WHERE EVAL "FIELD(city_state...

That phrase "pre-load" seems to figure large in the documentation, which seems to indicate that the contents are read from disk into the memory cache before the job begins processing rows on its main stream. That would suggest that the add_to_heap() warning should occur before row #1 is processed. O...

Is the generated SQL taking the key columns out into the WHERE clause leaving a mismatch?

Really, though, without seeing the actual table definition from the Oracle stage and the corresponding generated SQL, it's not possible to comment one way or the other.

In a UV stage, let DataStage write the SQL for you (use generated SQL). Mark city_state as the only Key column, and inspect the generated SQL, which will look like SELECT col1,col2,col3 FROM table WHERE country_name = '?'; Now switch to user-defined SQL and change the "=" to "LIKE" and the "?" to "%...

Welcome aboard! :D Technically, all of them, but many have suitable default values. May I suggest that you construct a job containing this stage, then click on Property Help as you select each property in turn? As well, there exists a manual for this stage in PDF form; it's on the CD and should be i...