Usage Of Universe Stage

[suresh.narasimha]

Hi,

I have a Source Table in remote on which i fire a SQL and put all the result into a staging table residing locally

From the staging table i do the aggregation or some selection based on where clauses etc and put into next table.

Now i wanted to know instead of a staging table can i use a Universe table?

Considering performance and storage which is a better option to use.

Please let me if my question is not clear enough or you need more information.

Thanks & Regards,
Suresh N

[gateleys]

Now i wanted to know instead of a staging table can i use a Universe table?

Use a hashed file instead of the staging table.

Considering performance and storage which is a better option to use.

Although you can access the just created hashed file using a Universe stage, my experiences have shown that the performance is really bad as compared to use of other enterprise database to host your staging tables.

On the other hand, you could stage them in sequential files, and perform the necessary aggregation downstream and restrict the rows based on trasformer constraints.

[ray.wurlod]

A UniVerse table IS a hashed file. Whether you use a UV stage or a Hashed File stage to access it is immaterial unless your access is via the primary key, in which case a Hashed File stage is definitely to be preferred.

UniVerse IS a database. So your sub heading "Performance of Universe Compared to Database" is moot.

If your other database is local (that is, on the same machine as the DataStage server), then you may as well use that. If it is not, good gains can be had from transferring data to a local staging area. This might be hashed files, it might be text files - it depends precisely what you want to do with them.