DSXchange

Is there any possibility of using a hash file as table.
I have a set of entries in hash file and i would like to select data based on conditions 'between', 'where','having' etc.... But these can be done only in a table.

Is there any way convert a hash file to a table and query the hash file.
I have heard people saying registering in VOC makes a hash file behave like table? If so how o i do that?

Search the forum for discussions on SETFILE and the UV/ODBC stage.

You can use DS Universe tables for temporal tables to run sql queries.

Many databases, among them UniVerse and UniData (from IBM), D3 (from Raining Data) and jBase (from jBase), use hashed files to implement their tables. Since DataStage is a derivative of UniVerse, you can definitely use hashed files as tables. To do so, as others have intimated, you need the table name to be in the vocabulary of the product (that is, in the VOC file), which may be automatic (if the hashed file was created in the project) or may need to be emplaced (using a SETFILE command). Thereafter, access it via a UV stage, which uses standard SQL (or user-defined SQL if that's your thing). There are extensions in UniVerse SQL for nandling multi-valued data; I shall assume that you don't have any of those. As with other database tables, non-key constrained query performance is not very good unless you index the constraining columns.

You do not need SETFILE if you use the local account. You do this by leaving the Account name blank or the path name blank.

UV stage treats hash files as tables and you use SQL to extract the columns. I can't of any advantage to update a hash file with a UV stage. They are sort of interchangable. You need to create the columns with a Hash file stage or do it manually. You can use CREATE TABLE to do it as well. Universe is a very forgiving flexible database.

I think all this is very confusing to newbies. You have to try it. It can be very powerful especially if you learn TCL commands. It is worth the effort. I do this a lot in EtlStats. Use telnet. I hate TCL in the DataStage Adminstrator. If you telnet in then use this command:

PTERM CASE NOINVERT

You can cut and paste SQL into a telnet session after this TCL command.

There is a directory called SqlScripts which most are SQL used against the repository tables in DataStage. Most are posted on this site. EtlStats is available free on my tips page. Take a look at my tools while your are there DwNav, ParameterNavigator and DsWebMon. Save you lots of time.