A command to describe the hash file

sumitgulati · Post by **sumitgulati** » Wed Jul 21, 2004 4:23 pm

Hi All,

Is there any command to describe a Hash File (like Describe of Oracle).

A command that expects the hash file name and when executed returns all the columns present in the hashfile with their datatypes.

Regards,
Sumit

ketfos · Post by **ketfos** » Wed Jul 21, 2004 4:33 pm

Hi,
Try using in Director
LIST DICT FILENAME

Ketfos

sumitgulati · Post by **sumitgulati** » Wed Jul 21, 2004 4:51 pm

Thanks a lot Ketfos. It works. I was just wondering why don't we see all the columns when we do a "Select * From" and hash file in Command Interface.

Eg. - Try running SELECT * FROM DS_JOBOBJECTS in Command Interface. It won't return all the columns

Regards,
Sumit

kduke · Post by **kduke** » Wed Jul 21, 2004 6:22 pm

Select * from file uses the default listing for a file. The default list of fields is stored in the DICT in @. @ is a phrase. It has PH in line 1 and a list of fields on line 2 separated by spaces. The @KEY is also displayed. It is a phrase which has all the fields which are part of the primary key.

You can select any field in the LIST DICT. Try it.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 22, 2004 2:44 pm

Time to be a little bit pedantic again.

The columns that are displayed by an SQL query are specified by a phrase in the dictionary called @SELECT (it only defaults to @ if there is no @SELECT phrase defined). Usually @SELECT is the same as @ but incldues the key column explicitly. But it doesn't have to be thus.

Secondly, Ascential made a deliberate decision not to document every column of the Repository tables in their metadata, so you can only look at a very limited subset. In particular, records in DS_JOBOBJECTS have different structures depending on OLETYPE; one record type has only 26 columns; another has over 300. And what's in each column is different for different record types (for example Annotations don't have (or need) column definitions).

sumitgulati · Post by **sumitgulati** » Thu Jul 22, 2004 3:07 pm

Thanks Wurlod.

-Sumit

kduke · Post by **kduke** » Fri Jul 23, 2004 2:32 am

Ray

I have not seen @SELECT in a while. I know how it works but does ASCL use it any where?

Explain @REVISE too. That will confuse them all.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 23, 2004 4:49 am

OK, here we go...

@ defines the default output fields for the RetrieVe query language; if there is no @ entry only the key field is displayed

@LPTR defines the default output fields for the RetrieVe query language if the LPTR keyword appears in the query; if there is no @LPTR entry @ entry is used.

@REVISE defines the default fields to be prompted for when data are being entered/updated using the dictionary-driven REVISE tool (part of the RetrieVe query language); if no @REVISE entry appears all fields are prompted for, multi-valued fields on separate screens per association

@SELECT defines the default display fields (columns) for SQL SELECT queries (and, by implication, the effect of the asterisk for "all records"); if there is no @SELECT entry, the @ entry is used

@INSERT defines the default columns used in an SQL INSERT statement if there is no column list specified; if there is no @INSERT entry a column list must be specified

@KEY lists the columns making up the primary key

@KEY_SEPARATOR defines the separator character used to separate columns in a multi-column primary key; if there is no @KEY_SEPARATOR entry a text mark (accessible via the @TM system variable) is used

(@SELECT, @KEY and, if necessary, @KEY_SEPARATOR are emplaced by the CREATE TABLE statement.)

@EMPTY.NULL, if present, enables presentation of "" as NULL in ODBC-initiated queries.

All of these, and others (!), are documented in appropriate UniVerse manuals.

kduke · Post by **kduke** » Fri Jul 23, 2004 8:32 am

Ray

I never knew about @KEY_SEPARATOR. Very good. Does that work with DataStage?

Thanks Kim.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 23, 2004 5:30 pm

Yep sure does! Probably one or two other things you didn't know about CREATE TABLE in the following example.

Code: Select all

CREATE TABLE MyTable
(
   TYPE 18, MODULO 4211, SEPARATION 4,
   keycol1 INTEGER NOT NULL DEFAULT NEXT AVAILABLE,
   keycol2 DATE NOT NULL,
   othercol1 INTEGER NOT NULL DEFAULT 0,
   othercol2 VARCHAR(254),
   CONSTRAINT MyTablePKC PRIMARY KEY '*' (keycol1, keycol2)
);

In this example the separator character (@KEY_SEPARATOR) in the physical primary key (@ID) is an asterisk. The I-descriptors that isolate keycol1 and keycol2 in the dictionary will make reference to this character as the second argument of their FIELD functions.

kduke · Post by **kduke** » Fri Jul 23, 2004 6:49 pm

Ray

Cool. Another full Wurlod answer. Thanks.