DSXchange

So, just to be clear... Because you are using a routine that reads the entire file into memory, this allows you to write everything back to the same filename when you are done - something that is not possible when using Sequential File stages. Ray, you mention 'ReadU' but don't use it in the posted ...

ray.wurlod wrote:Iconv() and Oconv() functions can perform multiple conversions; the list of conversion specifications is presented as a multi-valued field (that is, conversion specifications separated from each other by value marks). It's a handy shortcut sometimes.

Zowie. I had no clue you could do that.

ray.wurlod wrote:You must then inform the Aggregator stage - on its input link - that the data are sorted by the grouping column(s).

And don't even think about lying to it - the stage will bust you for that. Big time.

You learn pretty quickly that 'it runs from the command line' doesn't mean it will run when executed from a DataStage job. The environment they run under is pretty restricted, even when using 'the same userid'. Make no assumptions about what environment variables are available. Explicitly set them i...

Ok, you lost me a little on that one Ray. Maybe more than a little.

Not someplace where I can just plug in the syntax, so brain only at this point. It's similar to using the DIGITS transform, but I don't get the use of the value mark. Have to VPN in and check it out...

You can't use a Sequential File stage to read a spreadsheet unless you've saved it in .csv format first. To read the .xls directly you need to use the ODBC stage instead.

Try stripping the date separators from the field. For example:

No. You need to write to a new (or at least different) name, then decide what you want to do in the way of post processing. Rename new back to old, perhaps. Save off original file somewhere first, perhaps. Your call.

Glad I could help. Feels good every now and then to channel Dr Evil.

And yes, I they only knew...

Hard work, mad skillz and an intimate knowledge of the internal structures of PX jobs.

And it can be all yours for only... [puts little finger in mouth]... one million dollars.

(sorry)

As noted, there is a definite limit on the number of rows the aggregator can handle. That number, of course, isn't a fixed number as the total size of the rows being aggregated is more of the issue. The only way I've found to successfully agg millions of rows is by presorting them. Then it only need...

I'm not really sure how you would safely do this from job control. If it thinks it is running, it won't touch it. For those kind of situations, that's why the Clear Status option asks you "Are you really sure you want to do this?". Do you then go grepping for pids as a double-check? Perhaps a recomp...

The problem is that it not possible (itseems so for me) to implement update and insert in two separated links. Umm... this is pretty much ETL 101. Check the incoming data against the existing data so you know which rows should be inserted and which should be updated. How? Read on... Hash up existin...

However, if what you are doing is using a single link with an update action of the "Insert then Update" (or the opposite) type, you can never know which ones ended up being inserts and which ones ended up being updates. Except, perhaps, by querying the results if you are timestamping records. They ...

Go to his website and download the job, it's very well documented. You feed an export (.dsx file) in one end and and a .csv file comes out the other end.

We used to keep the output in a spreadsheet, but pushing it up into a table made things easier.