Attunity hash (.hsh) files

[Rich Jordan]

I just found two HASH##################.HSH files in the [navroot.tmp]
directory on a server; I don't know attunity/navigator so I'm not sure
if they're needed, but one of them is 1.2 million blocks, and neither
has a recent create/modify date.

What are they for and are they needed? Can they be deleted without
consequence given their age (> 1 year since last modification)?

Thanks...

[VAXman-]

Rich, I asked to people at Attunity if they'd answer this for you.

--
VAXman- A Bored Certified VMS Kernel Mode Hacker VAXman(at)TMESIS(dot)ORG

Well I speak to machines with the voice of humanity.

[Hein RMS van den Heuvel]

The HASH files are used to calculate join criteria in SQL and should go away one the query has been hashed and executed returning the results.

So you can safely delete any stale one.
They are no use except during active use in a complex query.
Considering the file is good sized I suspect someone got impatient about a (non-index join?) query and killed servers.

Hope this helps,
Hein
(now @ Attunity.com)

[Rich Jordan]

On Jun 7, 8:15 am, Hein RMS van den Heuvel

Thanks Hein (and Brian too). I'll add HSH files to the housekeeping
on that server; anything older than X days or previous boot time gets
cleaned off.

Rich

[Hein RMS van den Heuvel]

On Thursday, June 7, 2012 11:01:01 AM UTC-4, Rich Jordan wrote:
> On Jun 7, 8:15 am, Hein RMS van den Heuvel
> <heinvandenheu...@gmail.com> wrote:
> > On Wednesday, June 6, 2012 6:41:56 PM UTC-4, Rich Jordan wrote:
> > > I just found two HASH##################.HSH files in the [navroot.tmp]

serendipity?...
I just issued a long running non-scaling query under Attunity:

SQL> select count(distinct xxx) from yyy;

Work fine for a few hundred rows.
Try it against a few million rows and it just 'goes away' doing 500+ IO/sec.
Peek at it with SDA PROCIO and what file do I see?

reads writes file - Process PCB: 8A76D100
-------------------------------------------------------------
516748 515963 x:[NAVROOT.TMP]HASH120120607212907135228_21A009B3.HSH;1
18962 0 y:[TEMP]FULLLOAD.SEQ;1
1 19 x:[NAVROOT.TMP]CDC.LOG;13
13 0 x:[NAVROOT.DEF]SYS.NOS;1
9 0 x:[NAVROOT.DEF]CDC.NOS;1

I honestly had never seen that file in action before today!
I can also appreciate how someone would want to shoot the process running this.
Grins,
Hein.

btw... because I knew the records were in order, and sql did not know this, I could get the answer in a few minutes with a perl hack, including the time to write that program :-)

use VMS::Stdio qw( &vmsopen );
my $temp = shift or die "please provide a file";
# perl IO fails on fixed length records... too easy? Use system calls
my $fh = vmsopen($temp, "ctx=rec", "mbc=120", "mbf=4") or die "Could not open $temp for input. $!\n";
while (sysread ($fh,$_,34)) { # unfortunately need to know the record size
$a = substr($_,0,8); # key size
next if $a eq $o; # new = old/prior?
$i++; # count it
$o = $a; # what was new is now old.
}
print "uniq=$i\n"

[Rich Jordan]

On Jun 7, 11:20 pm, Hein RMS van den Heuvel

On behalf of myself, my company, and my customer, we're delighted to
have provided part of a serendipitous event to you, and thanks again
for the assist!

[Craig A. Berry]

In article <16b26474-e6b9-4ed3...@googlegroups.com>,

Hein RMS van den Heuvel <heinvand...@gmail.com> wrote:

> because I knew the records were in order, and sql did not know this, I
> could get the answer in a few minutes with a perl hack, including the time to
> write that program :-)
>
> use VMS::Stdio qw( &vmsopen );
> my $temp = shift or die "please provide a file";
> # perl IO fails on fixed length records... too easy? Use system calls
> my $fh = vmsopen($temp, "ctx=rec", "mbc=120", "mbf=4") or die "Could not open
> $temp for input. $!\n";
> while (sysread ($fh,$_,34)) { # unfortunately need to know the record size
> $a = substr($_,0,8); # key size
> next if $a eq $o; # new = old/prior?
> $i++; # count it
> $o = $a; # what was new is now old.
> }
> print "uniq=$i\n"

Nice to see Perl in action. Here are two alternative implementations
that will give you the same answer. The first one uses the input record
separator variable $/ to limit record reads to the size of the record.
Normally when set to a character string, this variable replaces \n with
something else that is considered the division between "records" in a
text file. But when set to a reference to an integer, it indicates the
maximum number of characters to read in a single read operation, which
on true record-oriented files gives you one record per read. This
method doesn't get you the buffer control you have from vmsopen, but
it's simpler:

$ type readhsh.pl
use strict;
$/=\34;
my $keycount;
binmode STDIN; # disable encodings
my $oldkey = '';
while (<>) {
my $key = substr($_,0,8);
$keycount++ if $key ne $oldkey;
$oldkey = $key;
}
print "uniq=$keycount\n";

Run it like:

$ perl readhsh.pl < foo.tmp

The second alternative goes the other direction. It's more complicated
and is overkill for this example, but could also be scaled up to more
complicated problems. It uses the VMS::IndexedFile extension, which
creates a hash tied to the indexes of an indexed file. Referencing a
key in the hash means doing an indexed read of the file. So, for
example, if the file were not in the same order as the key of interest,
you will still get the right answer because it would be reading down the
index you specify.

$ type readhsh2.pl
use strict;
use VMS::IndexedFile;

my $fdl = "FILE; ORGANIZATION indexed; RECORD; CARRIAGE_CONTROL none; "
. "FORMAT fixed; SIZE 34; KEY 0; CHANGES no; DUPLICATES yes; "
. "SEG0_POSITION 0; SEG0_LENGTH 8; TYPE string;";

my %h;
tie (%h, 'VMS::IndexedFile', $ARGV[0], 0, O_RDONLY, $fdl)
or die "failed to tie $ARGV[0]\n";

my $keycount = 0;
my $oldkey = '';

for my $key (keys %h) {
$keycount++ if $key ne $oldkey;
$oldkey = $key;
}

untie %h;

print "uniq=$keycount\n";


Run it like:

$ perl readhsh2.pl foo.tmp

If you have Perl 5.10.0 or later, write access to your Perl
installation, and an Internet connection, you can get VMS::IndexedFile
by doing:

$ cpanp install "VMS::IndexedFile"

[Hein RMS van den Heuvel]

On Friday, June 8, 2012 12:21:51 PM UTC-4, Craig A. Berry wrote:
> In article <16b26474-e6b9-4ed3...@googlegroups.com>,

> Hein RMS van den Heuvel wrote:
:

> > use VMS::Stdio qw( &vmsopen );
> > my $temp = shift or die "please provide a file";
> > # perl IO fails on fixed length records... too easy? Use system calls
> > my $fh = vmsopen($temp, "ctx=rec", "mbc=120", "mbf=4")

:

> Nice to see Perl in action.

All the time! Any excuse...

> The first one uses the input record
> separator variable $/ to limit record reads to the size of the record.

Almost works.
I still to get an additional record with just a record terminator.
Annoying. Same thing happened with sysread.
I had to or alter the original file to make it work, or add 'next unless length > 1' to the real code as well as an input record counter as $. was lying.

Besides that.... it is also annoying to have to know the records size, rms will happily report that for a 'normal file', but how abnormal is a fixed length record file? Couldn't be any easier!

RMS is perfectly fine reading a record at a time, which I tried to force with 'ctx=rec', but the C-rtl surely is trying to 'help' by interpreting the record attributes thereby needlessly muddling the waters.

$ conv/pad/tru/fdl="rec; form fix; size 20" sys$input fixed.tmp
aap
noot
mies
$ perl -v
This is perl, v5.10.0 built for VMS_AXP
$ perl -e "binmode STDIN; $/=\20; while (<>) { printf qq(%2d %2d %s\n), $., length, $_ }" fixed.tmp
1 20 aap
2 1

3 20 noot
4 1

5 20 mies
6 1
$ set file/att=rat=none fixed.tmp
$ perl -e "binmode STDIN; $/=\20; while (<>) { printf qq(%2d %2d %s\n),$.,length,$_}" fixed.tmp
1 20 aap
2 20 noot
3 20 mies

Oh well,
Hein