Hashed File reading records as if in an infinite loop

bikramg · Post by **bikramg** » Mon Sep 01, 2014 9:33 pm

Recently I started getting this odd problem with hashed files in one of my existing jobs (used to run fine without any issues) - where the hashed file is being populated from a SQL \ ODBC connection and then being looked up by a transformer.

The SQL only fetches 1 row from the DB - however the hashed file then keeps on throwing out millions of rows in output and the jobs never ends (have to kill it from the shell). When I do view data on the hashed file stage it only shows the desired one row. I have no idea why this is happening? I have even changed the options to delete the file before creating it assuming that pre-existing file is causing the problem (although clear file in update action was set).

Any ideas how do I rescue out of this problem

Thanks,

chulett · Post by **chulett** » Mon Sep 01, 2014 10:10 pm

First, off to the Server forum for you! Second, can you detail out your job design for us, please?

bikramg · Post by **bikramg** » Mon Sep 01, 2014 11:01 pm

Oops. Sorry Craig.

The design is to create a reconciliation starting point for the overall batch where

I'm checking the last batch details batch id, status, count etc (coming from a SQL conection) that goes in to the hashed file (causing the problem). This is then being referenced in a transformer by the latest batch to see if the batch has already been run etc.

So it looks like this (trying my best to draw it in word):

Code: Select all

                                    Output
                                       |
Last_Batch -> Hf_Last_Batch---->Lkp_Last_Batch  <-Tfm_Latest_Batch_Validation <---- Latest_Batch

It is the Hf_Last_Batch causing me the trouble. The output is of Lkp_Last_Batch (alignment isn't quite coming right here).

ray.wurlod · Post by **ray.wurlod** » Mon Sep 01, 2014 11:40 pm

Use Code tags to hold the alignment.

The hashed file will be looked up for every row coming in from Latest_Batch. It should return its one row in each case. Is this not what you require?

Try changing the hashed file type from 30 to 2, with a separation and modulo of 1 each.

chulett · Post by **chulett** » Tue Sep 02, 2014 6:55 am

Since it worked fine once upon a time, it may have simply been corrupted in a way that the 'Clear' cannot fix. Try deleting the hashed file directory so it is recreated next run, see what happens.

bikramg · Post by **bikramg** » Tue Sep 02, 2014 7:43 pm

Craig \ Ray,

I have tried both the options you have suggested - deleting the files directories and changing those options as well. But none seems to have make any difference to the actual problem I'm getting.

However, a bit more information for you and that might help me to understand this issue better (I don't have much experience with server jobs so some questions can sound moronic - bear with me please)

In my previous post I depicted the area that is getting the phantom rows coming through. But the main job is far bigger that part I have shown, and they are not getting this issue of phantom rows in the hashed file \ lookups (there are a total of 5 in the job - so 4 other plus the one I have issue with).

After changing the type to 2 from 30 (for all 5) I noticed upon running the job it creates a file D_Tmp_Batch_Check_Prev_Batch and a directory Tmp_Batch_Check_Prev_Batch which has the data & OVER (type 30) files in it. What is interesting is none of the stages in the file is called that. My question is how is the directory coming up. It is this one that is causing the problem - pretty sure. As when I killed the job from shell & recompiled the job I noticed in job stat that (lookup) link was still processing rows

And when I tried to delete that directory it wouldn't let me to.

Also does the directory name simply match what the file name is? D_ files are delete files?

E.g. If a file name specified in the hashed file is Last_Batch we will end up with Last_Batch (file), D_Last_Batch (file) and Last_Batch (directory)?

Finally is there any other stage that can create these file structure, in server jobs?

chulett · Post by **chulett** » Tue Sep 02, 2014 8:29 pm

Hashed files are the heart and soul of Server jobs, nothing else really works like them and there shouldn't be a need to seek alternatives. Can you detail for us the options you have in the Hashed File stage for both the link writing to it and the reference link doing the lookup? I'm wondering specifically about the caching options (as it sounds like you have write caching on) but let us know everything please. I'd also be curious if these are "pathed" where you've specified both a directory for them to live and their name or if they are "account-based" and all you've specified is the hashed file's name and they end up in the Project directory.

When you create a Type 30 / Dynamic hashed file, you get a directory of the same with three files in it: DATA.30, OVER.30 and a hidden file that controls the type .Type30 with all names off the top of my head but should be pretty darn close. You can also get a file at the same level as the directory with the same name and a "D_" prefix, the D is for Dictionary.

When you create a Type 2 / Static hashed file, from what I recall you get a file with that name created. Sounds like you have remnants of both out there.

bikramg · Post by **bikramg** » Tue Sep 02, 2014 8:51 pm

Right. So I unraveled from where the type 30 was coming - didn't pay attention properly, my bad. One of the hashed file is taking two streams in - for the second link I hadn't changed the type to 2 from 30.

Anyway, changing that hasn't solved anything either. For all hashed file stages I have \ the job has -

Allow Stage write cache - 'Y' (not sure why - as I didn't implement the job)
Create File - 'Y'
Create file before writing - 'Y'
Backup existing file - 'N'

Within (Create File) Options - the latest changed version
Type - Type 2 (Hashed)
Modulus - 1
Separation - 1
Caching Attrbutes - 'NONE'
Delete file before create - 'Y'

All files are pathed - (passed in a as job parameter) and the files appear in the path specified.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 02, 2014 10:13 pm

D_Last_Batch will always be created, irrespective of the hashed file type. This is the "Dictionary" which contains the column definitions, etc.

If you've settled on using Type 2 as recommended, delete first the contents of Last_Batch directory (using rm -rf Last_Batch/*) then delete the directory itself (using rmdir Last_Batch).

Change your job design so that multiple links do not create the hashed file. This can cause corruption. Perhaps prefer a before-stage subroutine to create the hashed file (using ExecSH and the mkdbfile command). Something like:

Code: Select all

DEL X:\path\Last_Batch 2>.\NUL ; %DSHOME%\bin\mkdbfile X:\path\Last_Batch 2 1 1

chulett · Post by **chulett** » Wed Sep 03, 2014 7:24 am

ray.wurlod wrote:D_Last_Batch will always be created, irrespective of the hashed file type. This is the "Dictionary" which contains the column definitions, etc.

I never found that to be true. We would find the dictionary file... sometimes. Never really could quantify when it would be there and when it wouldn't be. Seem to recall that most of the time, for whatever reason, it wasn't there. Never seemed to matter. [shrug]

chulett · Post by **chulett** » Wed Sep 03, 2014 7:27 am

Yah, nuke the remnants of the Type30 and disable write caching. Also - involve support if you haven't already.

bikramg · Post by **bikramg** » Wed Sep 03, 2014 10:11 pm

Ok so I have found what was causing the problem of this infinite loop or whatever it was.

The primary feed into the transformer \ lookup following the hashed file was coming from another transformer which had only one link leading into it and that was a reference link from an ODBC stage

. I don't really know what was the rationale behind this when this job was created but it seemed a little weird to me when I first had a look. Once I changed the reference to be a stream the job seems to be running fine. The ODBC connector however was fetching one row only so I don't know if it was getting into a self tangle of looking up itself. Would be good to know that.

What really confused me \ diverted my attention towards the hashed file is that although the output link from hashed file was reflecting the same number of rows that the transfomer \ lookup I mentioned above was fetching in reality.

Anyway, was good one for the time being that allowed me to spend some time with server jobs \ hashed files etc. I'll leave the post open for another day - to get an answer on my question about self tangled situation just so that I'm not making up stories....

chulett · Post by **chulett** » Thu Sep 04, 2014 7:52 am

Excellent that you figured it out. I'm not really following the resolution but that's neither here nor there. However, not really sure what the 'self tangled situation' is that you're leaving this open for - can you clarify?

Mike · Post by **Mike** » Thu Sep 04, 2014 7:52 am

Transformer with no stream input link would explain your observation. A transformer in a server job can drive itself without an input link as long as there is a stage variable defined. Without an output constraint it would generate output indefinitely. Usually in this type of scenario, you would have an output constraint similar to @OUTROWNUM = 1 when you only wish to generate a single row.

In this way a transformer in a server job can perform the same function as a Row Generator in a parallel job.

Mike

chulett · Post by **chulett** » Thu Sep 04, 2014 7:55 am

That's true... and something I considered but from the job description I couldn't tell if that was in play or not. And I don't know how it could ever have worked before and then suddenly started shooting out endless rows... unless someone changed the job and removed the constraint that controls the output volume.