UniData 6 stage - need info

[gmorey]

Our DataStage ETL jobs were set up a couple of years ago by developers that no longer work here, so one of our big challenges is figuring out what they did and how they did it so we can make changes as necessary.

We have 2 UniData 6 database servers, development and production, and we are successfully doing a number of daily extracts. Some use the ODBC driver, and some the UniData 6 driver.

Our UniData guy and I are puzzled by the DataStage UniData 6 stage. On one job, for example, we're pulling data from table A, which contains a foreign key to table B. This works in both environments.

The SELECT statement contains a couple of fields from table B, but there is no explicit JOIN between the 2 tables. Like this:

SELECT ID, DESCRIPTION, TABLE_B_FUNKYDATE
FROM TABLE_A

TABLE_B_FUNKYDATE is a field in TABLE_B.

How does this work? Is the sql passed to UniData or is it translated to something else when it hits the UniData machine?

Basically, I want to know how I can run the SQL statement on the UniData side and FTP the file to another machine.

Thanks,
gregdmorey@yahoo.com

[kcbland]

The dictionary is what is used. Dictionaries can contain correlatives that point to other files. This is why Universe and Unidata are so complicated. If the dictionaries are not pure A or S types, then you can get into all kinds of trouble. If there are F, T, or I correlatives/descriptors then your table select could actually be reaching out and getting more data than you expected.

On an engagement like yours, the first thing I would have done is setup a new account with custom clean dictionaries and Q-pointers to the appropriate accounts.

[gmorey]

The dictionary is what is used. Dictionaries can contain correlatives that point to other files. This is why Universe and Unidata are so complicated. If the dictionaries are not pure A or S types, ...

Our dev environment's dictionary has the field listed as an I type, but the production environment's dictionary doesn't have the field, yet they both work in DataStage.

[kcbland]

Make sure which account the jobs are using, as that's the dictionary you need to verify. An I-descriptor is basically a compiled function that represents a column, which means you need to read the code and see what it's doing.

[gmorey]

Kenneth,

Do you know it the SQL is passed to UniData and run on UniData, or would it be translated to something else (i.e. another form of SQL?)?

Thanks,

Greg

[kcbland]

Honestly I don't know the nitty-gritty details, but my impression is that the SQL just becomes a "LIST ditem1, ditem2, ditem3, ditem4, etc." type statement with headings and paging suppressed. This appears to be the case because changing the dictionary items information (width, justification, data origination, etc) cause the data to return differently.