Count records of hashed file in a routine

[rodre]

Can the records of a hashed file be counted from a routine into a variable?
Thanks,
Rodre

[kris007]

Code: Select all

Command = "COUNT ":Arg1
Call DSExecute('TCL', Command, Output, ReturnCode)
Ans = ReturnCode

where Arg1 is the name of the hashed file.

HTH

[jdmiceli]

Hi Kris,

I like your solution - seems simple enough. However, being new to DataStage, I don't know how I would implement this code. Does it get put into a particular stage or is it run from a console somewhere? I would appreciate your educating me on this.

Bestest!

[kcbland]

If the hashed file is externally pathed, you'll need to write a function to open the file and count the records or use the SETFILE command to created a pointer to it and then use kris007's method. If the hashed file is in the project, you can use kris007's method.

To read the hashed file, you'll write a function to use OPENPATH on the file, then a SELECT statement to select the keys from the file, then there's about 3 different ways to get the list of keys. One method is a READNEXT loop to read each key from the selection stack, incrementing a counter until the READNEXT fails. Another is to to read the stack into an array from the SELECT statement using RTNLIST, then a simple COUNT(array, @AM) will give you the answer.

I'm sure Kim or Ray will cheat and post the code, but here's a quick chance to learn a little DS BASIC.

[kris007]

You will have to create this routine in the Manager first. And then you can use it in a Transformer Stage or in a Jobsequence within a RoutineActivity or Uservariables stage provided you pass the name of the hashed file as a parameter depending on your requirement.How exactly would you want to use the number of records in the hashed file with in your Job?

[ray.wurlod]

Why a routine? Can't you use a job? What do you want to do with the result? Clearly you are unfamiliar with DataStage routines. You can use the supplied before/after routine ExecUV to execute COUNT #HashedFile# command, but what do you want to do with the result?

[rodre]

Thank you all for the response. :D
One question:
What is the syntax for the pointer SETFILE command? I don't have the Universe manuals. SETFILE /my/hashed/file accessible. I am in a windows enviroment, do I use the g:\work\files\hashfile path?

Ray,
We have a custom logfile that we use to track the status of a sequencer. We currently use a routine to write into the logfile along the way of the sequencer but we want to change this process and write it into a hashfile instead via a routine from the sequncer. The hashfile count gives us the sequence number to use for the next line.
Let me know if you think of a better way to do this...

[prabu]

Thank you all for the response. :D
One question:
What is the syntax for the pointer SETFILE command?

Code: Select all

HELP SETFILE

[kris007]

Code: Select all

SETFILE /hash_file_path/hash_filename hash_filename OVERWRITING

[ray.wurlod]

OVERWRITING is an optional keyword. I counsel caution using it.

[kvsudheer]

We have a requirement to write a after job routine which will capture the job level log details along with warning and error messages and insert into a Control Table. I would like to know how we can insert the values obtained from routine into a table.

Thanks in advance for your help.

[chulett]

Welcome aboard, but you really need to start your own thread on this subject, not jump on the end of this one. We don't know details like Server v. PX, operating system or version of DS that a new post would require.

When you do start your thread, clarify what you mean by 'table'... database? Universe?

[rameshrr3]

OVERWRITING is an optional keyword. I counsel caution using it. ...

He seems to have used overwriting as he possibly is calling this via Before/After job sub routine using ExecTCL , hence the pointer is being recreated repeatedly, once whenever the job runs.
The only advantage of using OVERWRITING this way is to suppress a warning message in the job log file that would otherwise drive a customer nuts.

Thanks