Can a local container be used as a reference input?

sbass1 · Post by **sbass1** » Thu Mar 05, 2009 5:42 pm

Glossary:
NK = Natural Key
SK = Surrogate Key

Job design:

Source Table containing NKs --> xfm with a bunch of reference links --> Target Table containing mix of NKs and SKs

One of the reference inputs is a hashed file containing nothing but SKs. So, I have to do another "bunch of reference inputs" to retrieve the NKs to match the input data.

I've set this up as a local container as follows:

Dragged a container onto the "outer job" canvas.
In the container tab, deleted the Input stage (no input to a reference link).
Built the container code.
Named the output link "Output".

In my "Outer" job:

Made the container one of the reference links.
That link is named "Lookup_GL4CodeSK".

I'm getting this error when I compile:

Link name mismatch on local container. No matching link "Lookup_GL4CodeSK" inside the local container.

Things I've tried:

1) Rename the output link in the container to "Lookup_GL4CodeSK". According to the doc, this is what I would need to do.

Result:
"The source stage does not support reference links."

2) Rename the reference link in the outer job to "Output"

Result:
"The destination stage cannot support any more stream input links."

3) Convert the output link in the container to a reference link (ok, that probably isn't the thing to do, but I thought I might get a "dotted line" reference pointing to the output of the container)

Result:
"The source stage does not support reference links."

Questions:

How do I get this to work?

Is there a limit to how low containers can be nested? Even if no limit, are there best practice considerations?

<rant, feel free to ignore>

Why should I CARE what the output link name is in the container / input link name is in the containing job? I've got an output from the container, which is an input to the containing job. Why can't DS link the two and work out how to compile the job? Why put the onus on the programmer to work this out?

</rant>

ray.wurlod · Post by **ray.wurlod** » Thu Mar 05, 2009 5:58 pm

"The source stage does not support reference links" is unrelated to link names. It's informing you that whatever is the producer putting rows onto the reference link is not capable of performing a "get row by key" function.

You need to specify what stage type is on the "Output" link inside the container before we can comment further.

sbass1 · Post by **sbass1** » Thu Mar 05, 2009 6:54 pm

"Outer" job:

DRS source --> xfm with hashed files for reference links --> DRS target
One of the reference links is the container

Container:
DRS source --> xfm with hashed files for reference links --> Output stage (container box, blue arrow going out)

I assume when I drag a container stage onto my job, and it puts an Input and Output stage in the container tab, that I'm supposed to "do something" with them, i.e. link to/from them??? Otherwise why would it put these objects in the container tab in the first place??? I do note that if I select objects and select "Construct Container", I don't get Input or Output stages.

If I'm meant to replace the output stage with something, is it possible to "pipe" the output of the container to the containing job (similar to xfm --> xfm processing), or do I need an intermediate physical data store? If so, I'm assuming a Universe stage would be good here per your recent post about #Temp SQL Server tables?

Thanks...

chulett · Post by **chulett** » Thu Mar 05, 2009 7:08 pm

I don't think I have ever moved a Container stage onto the canvas and built it 'from scratch' so can't really comment on some of your frustrations. What I do is build the job to completion and then create the container from the built stages.

To do this, simply select the stages that should be in the container and then 'Edit / Construct Container / Local' or shared. It will be built with whatever it needs in the way of Input and/or Output links automatically.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 05, 2009 8:30 pm

It is the Transformer stage inside the container that does not support reference links. You can not service a reference link with a Transformer stage.
To prove this, select the container and deconstruct it (under the Edit menu).

sbass1 · Post by **sbass1** » Thu Mar 05, 2009 10:20 pm

I think I've made progress on this topic...

I've created a simplified job to test my approach. When I had DS create the container, it looked something like:

Source DRS --> xfm --> Target DRS ==> Output

where ==> is a reference link (dotted line)

This setup allowed the job to compile.

Now my question is, since I can't use a temporary SQL Server table for the target table, I thought I'd use a UniVerse table per this link:

viewtopic.php?t=125757&highlight=

However, I don't know the different between UniData, UniData6, and UniVerse stages, or what I put in for Data Source name, User name, and Password for say the UniVerse stage.

Now I know the doc and/or search is my friend, but if you have any quick advice on the above, I'd really appreciate it! Or at least which stage type to use...

(Hitting Ch. 4 of the Server Job Developer's Guide now...)

ray.wurlod · Post by **ray.wurlod** » Thu Mar 05, 2009 10:38 pm

Only UniVerse is applicable here.

UniData is a different database product, and one that has never been used as the DataStage engine. Go here to contrast the two database products.

chulett · Post by **chulett** » Thu Mar 05, 2009 11:21 pm

And typically what you'd put for the Data Source Name would be localuv - the equivalent of the current Project. User Name and Password will automatically 'grey out' and be non-applicable.

sbass1 · Post by **sbass1** » Thu Mar 05, 2009 11:32 pm

Thanks Ray and Craig. I hit the doc, saw "localuv", success.

Question: can I specify where the hashed file gets written? We like to keep all our hashed files in one spot for file system cleanup, etc.

I tried #$HashFileLocation#/#DSJobname#_#DSStageName# for the table name, but no joy:

ScottTest008..Testing.Load: DSD.BCIOpenW call to SQLExecDirect failed.
CREATE TABLE "/opt2/dswork/DEV_GGS/HASH_FILES/fubar"

...

SQLSTATE=S1000, DBMS.CODE=950162
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: SQL Quoted Tablename contains illegal character.

chulett · Post by **chulett** » Thu Mar 05, 2009 11:47 pm

Hashed file yes, Universe table no... other than the account/project that is. So all that's valid there is the table name itself, no 'path' of any kind.

Now, you can create a hashed file wherever you like and then create a VOC pointer in the project to that location and reference it by that pointer name in the UV stage.

sbass1 · Post by **sbass1** » Fri Mar 06, 2009 12:39 am

chulett wrote:Now, you can create a hashed file wherever you like and then create a VOC pointer in the project to that location and reference it by that pointer name in the UV stage.

Sorry I don't know how to do that. I searched the doc set on "VOC" and got a lot of hits. It looks like the Basic Guide is my best bet, but there are a LOT of hits. A quick skim shows a lot of them irrelevant to my task at hand. I'll read them when I get time.

However, inferring from your comments, there doesn't appear to be a lot of difference, at the physical file level, from a hashed file and a Universe table. And since the object of my container is a reference link, and hashed files are valid input to a reference link, I assume I can just change my code from a Universe table to a hashed file.

I lose the ability to execute SQL against the "table", but since all I'm doing is a lookup against the keys, I assume this will be OK.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 06, 2009 2:11 am

Do a search on SETFILE.

I normally use ExecTCL as a before-job or before-stage subroutine. The command executed is:

Code: Select all

SETFILE pathname_of_hashed_file hashed_file_name OVERWRITING

sbass1 · Post by **sbass1** » Mon Mar 09, 2009 5:17 pm

So I did a PDF search as follows:

Acrobat Reader
Search all PDF documents in C:\Program Files\Ascential\DataStage7.5.1\Docs (53 documents in total)
"SETFILE"

Not one single hit.

DS Designer and Administrator help files.

Not one single hit.

Yes a search of DSXchange on SETFILE returns 194 hits, but:

1) I just don't have time to review 194 hits (nor I expect do most people)
2) Sometimes the hit is a single word in a thread that isn't relevant
3) I know the experts get sick of answering the same questions time and time again, but a lot of the answers are really really...ahem..."terse" - not really suitable as documentation level answers, esp. for beginners. And sometimes these terse answers assume a lot of ancillary knowledge that beginners may not have.

I don't know if IBM monitor this forum (do they monitor http://www.ibm.com/developerworks/forum ... orumID=825?) but I wish they wouldn't use these forums as an excuse for poor documentation. I really would have expected a hit on SETFILE in the doc set.

I'll mark this resolved, and hack around with the SETFILE command until I get it to work.

Thanks for the help!

ray.wurlod · Post by **ray.wurlod** » Mon Mar 09, 2009 5:39 pm

SETFILE is in the UniVerse doc set.

sbass1 · Post by **sbass1** » Mon Mar 09, 2009 8:05 pm

ray.wurlod wrote:SETFILE is in the UniVerse doc set.

Hmmm, the questions just keep coming

1. Here is the list of locally installed doc on my machine:

Code: Select all

C:\Program Files\Ascential\DataStage7.5.1\Docs>dir

 Directory of C:\Program Files\Ascential\DataStage7.5.1\Docs

23-Dec-04  10:28         1,669,527 advpx.pdf
23-Jun-04  12:16         5,254,479 Basic.pdf
19-Aug-03  10:11           231,220 cffstage.pdf
17-Jul-03  09:44           107,044 cmdstage.pdf
23-Dec-04  09:15         2,570,314 coredevgde.pdf
11-Jan-05  15:13           647,749 drsstage.pdf
07-Jan-05  04:41           716,868 dsadmgde.pdf
28-Jan-05  09:15            37,755 dsbooks.pdf
11-Jan-05  15:13           237,056 dsdb2.pdf
22-Jun-04  10:48           205,008 dsdskche.pdf
07-Jan-05  04:41           782,207 dsopsgde.pdf
23-Dec-04  06:09         1,334,820 dsupgde.pdf
19-Aug-03  04:00           133,436 ftp.pdf
29-Jul-03  07:57            84,504 infmxbl.pdf
29-Jul-03  10:20           249,485 infmxcli.pdf
11-Jan-05  15:13           224,686 instconf.pdf
11-Aug-03  09:53           114,185 iqbulk12.pdf
27-Jan-05  13:24           335,813 LegalNotices.pdf
17-Dec-04  06:47         3,781,175 Mainframe.pdf
23-Dec-04  05:59         2,295,334 man_gde.pdf
11-Jan-05  15:13           222,873 merge.pdf
11-Jan-05  15:13           401,855 mq.pdf
21-Aug-03  16:31           280,530 msoledb.pdf
23-Dec-04  05:48           841,303 NLS.PDF
27-Jan-05  12:43           303,515 odbcref.pdf
27-Jan-05  12:43           406,354 odbcuser.pdf
10-Dec-04  10:12           206,667 oraexp.pdf
11-Jan-05  15:13           510,889 oraoci8.pdf
11-Jan-05  15:13           497,479 oraoci9.pdf
11-Jan-05  15:13           142,145 oraocibl.pdf
23-Dec-04  10:01         7,731,999 parjdev.pdf
21-Jul-03  08:41            96,877 pivot.pdf
03-Feb-05  10:17            38,317 PlugDoc.pdf
29-Jul-03  08:21            74,686 redbrick.pdf
01-Dec-03  09:26            92,503 rowmerge.pdf
01-Dec-03  10:02            94,798 rowsplit.pdf
21-Jan-05  09:12           596,305 SASStage.pdf
23-Dec-04  07:59         4,018,741 servjdev.pdf
21-Jul-03  09:43            73,431 sort.pdf
19-Aug-03  09:34            79,490 sql7blk.pdf
11-Jan-05  15:13           406,091 stpstage.pdf
05-Aug-03  08:05           219,546 sybaseoc.pdf
27-Jan-05  12:43           223,045 sybaseop.pdf
27-Jan-05  11:11           235,930 sybref.pdf
27-Jan-05  12:43           223,045 sybuser.pdf
11-Jan-05  15:13           266,084 tdatabl.pdf
04-Dec-03  11:08           322,126 tdmload.pdf
23-Dec-04  09:21           229,312 techbull.pdf
18-Nov-03  10:05           212,544 teradata.pdf
27-Jan-05  16:44           341,689 txmapstg.pdf
02-Feb-05  16:41           123,753 udbload.pdf
03-Feb-05  18:13         1,151,177 XMLPACK_20_Designer.pdf
29-Jul-03  08:11           102,554 xpsload.pdf
              53 File(s)     41,780,318 bytes

C:\Program Files\Ascential\DataStage7.5.1\Docs>

So, is this the complete doc set for DataStage?

2. I'm happy to copy additional relevant PDF files to my local doc respository. Can you give me a hint as to which of the 25 docs in the UniVerse doc set are relevant to DS 7.5.x under Unix? Or should I just download them all?

I did type "SETFILE" in the search box on that site, but the hits didn't help determine which docs I should download.