DSXchange

Posted: **Tue Jul 27, 2004 4:11 am**

I create a hash file in UV:

create.file Time_D_Keys 30

That works fine. Then I add a field in the dictionary:

Code: Select all

INSERT INTO DICT Time_D_Keys(@ID, F1, LOC, FORMAT) VALUES ('TimeKey', 'D', 1, '8L');

Also works fine. When I import the UV file definition into DS, I get two fields, AT.ID (@ID) and TimeKey.

What I'd like to do is have a hash file with @ID hidden, and TimeKey only, but the TimeKey field must be the key, not @ID. How do I do that? With the REVISE command? If so, how?

Posted: **Tue Jul 27, 2004 6:40 am**

I ended up doing the above using a job. So I have a hash file now (Time_D_Keys) with only one column, which is a key (according to the imported DS UniVerse def.) BUT...

I populate this hash file with a routine:

Code: Select all

FileNameOnDisk = "Time_D_Keys"

open FileNameOnDisk to TimeDimHash else
  print 'ERROR: Unable to open ' : FileNameOnDisk : '!'
  stop
end

clearfile TimeDimHash
* StartDate is an arg with YYYY-MM-DD HH:MM:SS format.
DateInt = Iconv(MatchField(StartDate,"0X' '2N':'2N':'2N",1),"D-YMD[4,2,2]")
* NumCycles gets a + 1 at the end because the date range is inclusive
NumCycles = Iconv(MatchField(EndDate,"0X' '2N':'2N':'2N",1),"D-YMD[4,2,2]") - DateInt + 1

for i = 1 to NumCycles
  rec = ''
  rec = convert(" ", "", oconv(DateInt,'DYMD[4,2,2]'))
  write rec on TimeDimHash, i else
    print 'ERROR: Unable to write to ' : FileNameOnDisk : '!'
    stop
  end
  DateInt += 1
next i

close TimeDimHash
Ans = 0
return(Ans)

When this routine has run, the column in the hash file contains the record number. When I add another column into the UV dictionary like this...

Code: Select all

INSERT INTO DICT Time_D_Keys(@ID, F1, LOC, FORMAT) VALUES ('TestColumn', 'D', 2, '8L');

...then I see the date values in the second column (TestColumn), but that's not what I want. I need the column with the date values to be a key.

Posted: **Tue Jul 27, 2004 7:07 am**

My UniVerse skills are moderate, and there are some UniVerse gurus on this forum so I'll let them answer you in detail.

But if you didn't know, or the others didn't know, IBM provides on its website a 2-user free license for UniVerse 10 Personal Edition so you can have lots of fun on Saturday nights enhancing your UniVerse skills.

http://www-306.ibm.com/software/data/u2/universe/

Saturday night has never been this fun! And if you don't have anything to do Friday night there is the same for UniData 6.

Posted: **Tue Jul 27, 2004 7:20 am**

I love being married to a beautiful woman.

Now I never have to stare at a PC on a Saturday night anymore. Oh hang on, I never did, except in the hard-core gamer days...

Anyway, I downloaded all the UniVerse manuals from http://www-306.ibm.com/software/data/u2 ... ry/96univ/. That seems enough fun for now!

I tried modifying the routine so that the key (and recno) is the date key, but now I just sit with an empty hash file:

Code: Select all

FileNameOnDisk = "Time_D_Keys"

open FileNameOnDisk to TimeDimHash else
  print 'ERROR: Unable to open ' : FileNameOnDisk : '!'
  stop
end

clearfile TimeDimHash
* StartDate is an arg with YYYY-MM-DD HH:MM:SS format.
DateInt = Iconv(MatchField(StartDate,"0X' '2N':'2N':'2N",1),"D-YMD[4,2,2]")
* NumCycles gets a + 1 at the end because the date range is inclusive
EndDateInt = Iconv(MatchField(EndDate,"0X' '2N':'2N':'2N",1),"D-YMD[4,2,2]") - DateInt + 1

for DateInt = DateInt to EndDateInt
  rec = ''
  rec = convert(" ", "", oconv(DateInt,'DYMD[4,2,2]'))
  write rec on TimeDimHash, DateInt else
    print 'ERROR: Unable to write to ' : FileNameOnDisk : '!'
    stop
  end
next i

close TimeDimHash
Ans = 0
return(Ans)

Suggestions still welcome!

Posted: **Tue Jul 27, 2004 2:20 pm**

This should be close enough.

Code: Select all

FileNameOnDisk = "Time_D_Keys" 

open FileNameOnDisk to TimeDimHash else 
  Cmd = "create.file Time_D_Keys 30"
  execute Cmd
   open FileNameOnDisk to TimeDimHash then
      open 'DICT', FileNameOnDisk to DictTimeDimHash then
         FieldName = "TimeKey"
         DictRec = ''
         DictRec<1> = 'D'
         DictRec<2> = 0
         DictRec<3> = ''
         DictRec<4> = FieldName
         DictRec<5> = "6R"
         DictRec<6> = "S"
         write DictRec on DictTimeDimHash, FieldName else stop
         DictRec = "PH"
         DictRec<1> = FieldName
         write DictRec on DictTimeDimHash, "@KEY" else stop
         FieldName = "DateYYYYMMDD"
         DictRec = ''
         DictRec<1> = 'D'
         DictRec<2> = 1
         DictRec<3> = ''
         DictRec<4> = FieldName
         DictRec<5> = "10R"
         DictRec<6> = "S"
         write DictRec on DictTimeDimHash, FieldName else stop
         DictRec = "PH"
         DictRec<1> = FieldName
         write DictRec on DictTimeDimHash, "@" else stop
      end else 
         print 'ERROR: Unable to open DICT ' : FileNameOnDisk : '!' 
         stop 
      end
   end else 
      print 'ERROR: Unable to open ' : FileNameOnDisk : '!' 
      stop 
   end 
end 
clearfile TimeDimHash 
* StartDate is an arg with YYYY-MM-DD HH:MM:SS format. 
DateInt = Iconv(StartDate, "D-YMD[4,2,2]")
EndDateInt = Iconv(EndDate, "D-YMD[4,2,2]")

for DateInt = DateInt to EndDateInt 
  rec = '' 
  rec = convert("-", "", oconv(DateInt,'D4-YMD[4,2,2]')) 
  write rec on TimeDimHash, DateInt else 
    print 'ERROR: Unable to write to ' : FileNameOnDisk : '!' 
    stop 
  end 
next DateInt

close TimeDimHash 
Ans = 0

Posted: **Tue Jul 27, 2004 2:51 pm**

I would have created the hashed file with CREATE TABLE, which builds the dictionary for you. (SO does creating the hashed file from a Hashed File stage!)

Code: Select all

CREATE TABLE Time_D_Keys
(
   TimeKey CHAR(8) NOT NULL PRIMARY KEY
);

Posted: **Mon May 26, 2008 1:26 pm**

Please excuse me for bringing up this old thread.

I am hoping that someone could explain some doubts I have about ray.wurlod post:

I would have created the hashed file with CREATE TABLE, which builds the dictionary for you.

Is it possible? When you create a table a hashed file is created?
It looks much more simple than kduke code, but are both methods equivalent?
Could I call the Create Table function from an After/Before routine or from the job's Job Control?
Is the hashed file created outiside the universe (in a file directory)?

I am using version 8 server.

Thank you,

yserrano

Posted: **Mon May 26, 2008 3:49 pm**

First, an alternative to the original question. What gets displayed by a query is governed by an entry called @SELECT or @ in the file dictionary.

Code: Select all

INSERT INTO DICT Time_D_Keys(@ID, F1, F2) VALUES ('@', 'PH', 'TimeKey ID.SUP');

Now to your more recent questions.

yserrano wrote:When you create a table a hashed file is created?

Yes

yserrano wrote:It looks much more simple than kduke code, but are both methods equivalent?

Yes, with one exception - a hashed file created with CREATE TABLE must be deleted with DROP TABLE.

yserrano wrote:Could I call the Create Table function from an After/Before routine or from the job's Job Control?

Yes, through ExecUV in the former case and DSExecute in the latter.

yserrano wrote:Is the hashed file created outiside the universe (in a file directory)?

By default no but this can be managed with DATA and DICT clauses in the CREATE TABLE statement itself.

Posted: **Tue May 27, 2008 1:28 pm**

Yes, through ExecUV in the former case and DSExecute in the latter.

Is it possible to run this routine to create the hashed file from outside a Job?, i mean, through an API or something like that?

Regards

Edit: I am currently reading the Universe System Description manual, so I will probably have an answer for tomorrow and will be sharing it here. But please feel free to help if you'd like to.

Edit: Many thanks to ray.wurlod and the other posters for their help.

Posted: **Wed May 28, 2008 1:14 pm**

Hello,
After several tests and reading I achieved what I needed.
Developing ray.wurlod's suggestion I am using the CREATE TABLE statement to generate the hashed files.
It was not easy because of my poor knowledge of the UniVerse interface. Here are some basic tips for those who may need them.

- From a command prompt I moved to my project directory.
- Used the UniVerse shell (uvsh) to run the CREATE TABLE command.
- In order to create the hashed files outside of the universe, I used the DATA and DICT options.
- Tested in Designer the hashed files using the View Data option.

It is worth mention that there are several ways to achieve this (see above suggestions).

Thank you all,

yserrano

DSXchange

Hash file created in UniVerse

Hash file created in UniVerse