DSXchange

I agree with the foregoing with the exception that you can use Oracle sequences. The trick is, you use them in a reference lookup when loading the file for sqlldr to process subsequently. Point an OCI, DRS or ODBC stage at Oracle, and have it execute do something like SELECT ROWNUM, SEQUENCE_NAME.NE...

The IsNull() function can take any data type as its argument, and returns 1 (= true) if the argument is null, or 0 (= false) if the argument is not null. The only time you can get an error - which is actually a warning - is if you try to use an unassigned variable as the argument. This can happen be...

With 120 million records it's almost certain that 64-bit addressing would be needed - divide 2GB by 120 million and you don't get very large records (17 bytes). There is approximately 14 bytes per record storage overhead with 32-bit addressing, so that would leave only three bytes per record for dat...

Almost certainly PX will be giving you "pipeline parallelism", in which a downstream stage can be processing row #1 while an upstream stage has already begun processing row #2 (even though row #1 is still somewhere in the job). You can code to prevent this, but that's defeating one of the things tha...

You need to specify the search columns as "Key". This means that in the SQL there will be a parameter marker in a WHERE clause for each such column. For example

FUNCTION GetFileNames(FilePath) Command = "ls -1rt " : FilePath Ans = "" * Capture a list of file names (note option "1" in ls command, so that * output contains ONLY a list of file names, one per line). Call DSExecute("UNIX", Command, Out...

Check that the library search path (SHLIB_PATH, LIBPATH or LD_LIBRARY_PATH, depending on your Unix) is correctly specified in the dsenv script in the DataStage Engine directory. This script defines environment variables for all DataStage processes, including agent processes of connected clients.

Only to observe that that message is generated by DB2, not by DataStage. View the SQL that DataStage is generating. Use the tracing facilities that allow you to see the SQL that DB2 is processing. Figure out why they might be different. Ensure that the SQL you view in the DataStage job does actually...

Having checked the "first line is column names" in the Sequential File stage output link properties (Format tab), you don't need to worry about the first line; you'll never see it in the data processed by your DataStage job. Effectively the Sequential File stage discards the first line as part of re...

Create an earlier Routine Activity to return the comma-separated list of filenames. When supplying the job parameter value, click on Use Job Parameter and choose the return value from the Routine Activity. You could also use an Execute Command Activity, but you'd have to create a script to turn the ...

The project directory is on the server machine. Log in on the server machine and cd to the project directory. The log is a text file; you can view it with any text viewer, such as more, cat, vi and so on.

will work.

Note, however, if you are feeding this from a Sequential File stage with "first line is column names" set, then the first line has already been removed by the time the data flow reaches the Transformer stage.

The default settings for a hashed file will quite happily handle 1 million records totalling 1GB. Minimum modulus will allow you to pre-allocate disk space. If you set that accurately then split load is extraneous; it's the threshold at which the hashed file expands to hold more data. Merge load is ...

I have to split data from a TEXT field in SQL Server into strings of 72 chars breaking the string at a "Space" or "Enter". I am doing this in a Routine by passing in the TEXT filed. OK, you don't need SQL in the routine for this. FUNCTION Split72(TheText, FileName) SplitText = Fmt(TheTe...

There is a library of functions collectively called the BASIC SQL Client Interface that mimics the ODBC 2.0 API. If you use this you would not (probably) be limited to 72 characters per line, but would need to have an ODBC data source name configured. Why can't you use DataStage components to perfor...