DSXchange

Yep, the lookup functions translate a list of strings into a specified set of integer values, you are better off with Ray's list of conversion functions.

Try running a View Data from your job, when it fails you should be given a prompt to look at the parallel log for that job, this will have a more informative error message. Your DB2Instance settings are in the Install and Upgrade Guide. You need to add them to the dsenv file or if you have multiple ...

I don't think I've ever delivered a job to production that didn't have parameters. Since every job needs a source and target all my jobs have as a minimum either sequential file parameters such as file location or database parameters such as user id and password. Typically most of my jobs have 8-12 ...

Many stages have an optional sort function via the partition tab. This means if you are partitioning your data in a stage you can define the sort at the same time. The sort stage is for use when you don't have any stage doing partitioning in your job but you still want to sort your data, or if you w...

Since XML is a hierarchy structure when you choose a field low down in the hierarchy as key the flattening process will make the tree of the hierarchy key fields as well. So you just choose the bottom most field as key.

You don't need to do anything special for database input stages, they accurately read the metadata of the table via table import and the nullubility settings are taken from the table definition. This ensures all data can be read from the table including nulls. You then need to handle nulls in a modi...

Remote DB2 connections are trickier. There is a DB2 Remote Configuration setting document that you can request from IBM/Ascential support or download it from devnet. You need to run through the db2 setup and grant scripts in your $PXHOME/bin directory. The normal db2grant and db2setup only work on d...

You need to switch your job around making Oracle the reference link and SQL Server the primary link. Server jobs do not let your use right outer joins though parallel jobs do.

A normal join in a DataStage job is an outer join. The transformer simply outputs nulls onto the join fields where a lookup match is not found, you can add null handling to your output derivation fields to default values where a join is not found or leave them as null.

It's got some amazing numbers for common topics such as databases and operating systems. For example the answer to " I want an SQL query - select top 10 records except for the top 10 records." has had 11989 views. The most view thread on dsxchange has just topped 1000. One top database poster has 74...

In Enterprise database stages you will often find transaction size as an optional option, click on the stage options and look for it in the bottom right properties window. By default it gets set to 2000 (I think!). This may destroy your database, you will get multiple instances trying to build and s...

If you are on 7.5.1 you will need to call your routine via the Set Variables stage rather then a routine stage. You could also put the BASIC code directly into the Set Variables stage and not use a routine but that would not be as re-usable.

There is no other way. Job parameters are the method for setting variables in a job. If you set the value of your environment variable job parameters to $PROJDEF you ensure it picks up the latest value from your project settings each time the job is run so you don't have to change any settings in a ...

This is an extremely fast dedupe method if the data is sorted. But remember sorting is one of the slowest functions in server jobs, so a Unix sort will be faster, and you may as well use the dedupe Unix sort option while you are at it! The hash file method doesn't require sorting so may be a faster ...

Try the Parallel Job Advanced Developer's Guide for the chapter on buildops. Also have a look at the server folder $PXHOME/examples for, you guessed it, examples! There is a Custom_Ops example folder. Also do a search on this forum as a couple pieces of code have been posted.