Questions regarding Hash files and hash file stage

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

That design operates as two jobs, even though it's one. For a sufficiently small reference data set the hashed file write cache and read cache can be used, so that lookups are performed in memory, which should be faster than performing them to disk.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Ray, Smay. Phooey.
-craig

"You can never have too many knives" -- Logan Nine Fingers
mavrick21
Premium Member
Premium Member
Posts: 335
Joined: Sun Apr 23, 2006 11:25 pm

Post by mavrick21 »

Craig & Ray,

You guys are awesome! :D

Thanks
mavrick21
Premium Member
Premium Member
Posts: 335
Joined: Sun Apr 23, 2006 11:25 pm

Post by mavrick21 »

I was about to ask - Can data in Hashed file be cleared without deleting DATA.30, OVER.30 and the VOC entry?

And I found out I can :wink:

DELETE FROM <HASHED FILE>

Posted here hoping this might be useful for others
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Only if there is a VOC entry for the hashed file, from what I recall. That is, only if it is in an 'account' rather than a 'directory', that or you've manually created one for it using the SET.FILE command.

There's also a 'Clear' option if you need to do that inside a job in the Hashed File stage but I'm sure you knew that. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
mavrick21
Premium Member
Premium Member
Posts: 335
Joined: Sun Apr 23, 2006 11:25 pm

Post by mavrick21 »

Craig,

In that job the hashed file was used as reference and I couldn't find the job which populated it. Didn't want to waste my time creating a new job to clear it ;)
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

mavrick21 wrote:I couldn't find the job which populated it.
Why not? Did you attempt a "where used" analysis" on the table definition for the hashed file?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
mavrick21
Premium Member
Premium Member
Posts: 335
Joined: Sun Apr 23, 2006 11:25 pm

Post by mavrick21 »

Ray,

Is "where used" analysis available on DataStage 7.5? If so where can I find it?

Thanks
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Manager. The icon looks like a chemistry beaker from what I recall.
-craig

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

mavrick21 wrote:In that job the hashed file was used as reference and I couldn't find the job which populated it. Didn't want to waste my time creating a new job to clear it ;)
Well, I've seen plenty of places that have jobs that do just that. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
mavrick21
Premium Member
Premium Member
Posts: 335
Joined: Sun Apr 23, 2006 11:25 pm

Post by mavrick21 »

Craig,

We follow the same. One Clear_Hash job for ever Server job :lol:
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

mavrick21 wrote:I was about to ask - Can data in Hashed file be cleared without deleting DATA.30, OVER.30 and the VOC entry?

And I found out I can :wink:

DELETE FROM <HASHED FILE>

Posted here hoping this might be useful for others

Code: Select all

CLEAR.FILE <hashedfile>
is way more efficient.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
mavrick21
Premium Member
Premium Member
Posts: 335
Joined: Sun Apr 23, 2006 11:25 pm

Post by mavrick21 »

Hello,

Below is the LIST.DICT of HASHED_STORE_DEL

Code: Select all

               Type &
Field......... Field. Field........ Conversion.. Column......... Output Depth &
Name.......... Number Definition... Code........ Heading........ Format Assoc..

@ID            D    0                            HASHED_STORE_DE 10L    S
                                                 L
Jobname        D    0                            Jobname         10L    S
CURR_DEL       D    1                            CURR_DEL        10L    S
@KEY           PH     Jobname

@              PH     Jobname
                      CURR_DEL
                      ID.SUP

5 records listed.



Below is a sample entry in the hashed file.

Code: Select all

Jobname...    CURR_DEL..

SFCO_CS9_E
PM9
J_Stage_PS    E
_VAR_DATA_
SFPA_CS9_E
PM9

265 records listed.
>
>COUNT HASHED_STORE_DEL

265 records counted.

My question- Can I manually insert a record into the hashed file? If so what's the syntax?

Thanks.
mavrick21
Premium Member
Premium Member
Posts: 335
Joined: Sun Apr 23, 2006 11:25 pm

Post by mavrick21 »

Hello Gurus,

When data is stored in Hashed files, is it indexed on key fields and stored?

Thanks
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

A hash value is computed for the key fields (thus the name) and that is used to store the value. I seem to recall a Ken Bland post here with all kinds of gory details about hashed files... and I'm sure Ray has a thing or two up his sleeve. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply