DSXchange

Put less data into the hashed file, instead of one large hashed file create many smaller hashed files using a partitioning method, then use multiple job instances with the same partitioning method to reference the appropriate hashed file.

INSERTing is usually very fast, UPDATEs are notoriously slow. The best methods for loading are to partition align your data with the database, avoid global indexes on table structures, pre-determine inserts from updates, pure insert load your inserts, insert your updates into a runtime table and use...

By control, you must mean either Batch or Sequence. Both can run an OS command, which in turn can be your command line program (isql, sqlplus, dbaccess, etc), that also can then run a .sql file that contains whatever syntax is relevant to your database. That .sql file can do whatever needs be done, ...

Welcome to the forum! Did you do a ". ./dsenv" prior to starting the shell? If so, then is this a new installation of DataStage?

Are you sure you're looking at the job correctly? It's impossible to not have a WHERE clause on a lookup. With multi-row enabled, your result is that every primary input stream row will produce as many output rows as returned in the lookup. Without a where clause, you get a cartesian product. Withou...

Yeah, Ray's solution relies on 2 character values in the list, therefore, positional answers is a simple math expression. I try not to work my brain cells too much, it seems 5 years of college math used up my lifetimes supply. I opt for things that I can understand when looking at them in the middle...

Hmmm, I didn't quite follow Craigs and Rays conversation there. I don't know how my solution doesn't work for every situation. Even if your search text varies from row to row, and the lookup list of values varies also from row to row, the request was the position within the delimited list. You reque...

Yeah, STATEMENTS like LOCATE are a bummer, you need to wrapper it with a FUNCTION so that it's usable in a derivation, key expression, or constraint. Since your example shows that you are turning a CSV list into a MV array, why not just use INDEX combined with DCOUNT? I assumed your list of values a...

Use stage variables.

Unless you are referencing and writing to the same hashed file in ONE Transformer stage, buffering of any sort between the reference and write will cause inconsistencies in the result.

And disable read and write caching, as well as turn OFF row buffering or inter-process. If necessary, deselect Use Project defaults to force no buffering.

Craig, you're too fast. I turned my head to catch a replay on MNF and didn't get my answer in fast enough.

The update DML doesn't require a row to be present for the update to succeed. If the row WHERE criteria has any trim, date, or scale issue and won't match a corresponding row, you can't see the row isn't found.

The Create File check box only activates the Options button, instead of it being "greyed" out. If a link is writing to a hashed file, it will create it no matter what the Create File check box contains. Clearing a hashed file only drops it back to its Minimum Modulus setting from when it was created...

Changing all ExecSH to ExecDOS will be fun. If you're truly Parallel, then MKS toolkit will be available for you to use as it's part of the install. You won't have to rewrite/change much any scripts you're using. You'll have fun of course changing your Sequential stages to write DOS files, but you d...