Hash file issue

[raju_chvr]

I have 4 coulmns in Hash file:

Phys_Street_Name, Phys_City_Name, Phys_State_Cd, Phys_Country_Cd
Wilson, North Wilson, NE, US

For the above I see data from DataStage data browser for all the four columns. But when I run the job, DS is coming back with warning saying that it cannot insert null value into Phys_State_Cd

Why is this happening? In my Hash-file outputs metadata I have position field empty for all columns.

thanks for ur time.

[ray.wurlod]

Which column is the key column?

It is illegal to try to insert NULL into a primary key column. This is true in all databases. Hashed files are the method that the UniVerse database implements its tables. For UniVerse you can also read DataStage Engine.

[raju_chvr]

None of them is a primary key column. I gave a part of metadata information for the hash file, there are many other coulmns in the hash file.

[ray.wurlod]

You can not insert rows into a hashed file without supplying a value for the key column.

Exception:
If the hashed file was created as a UV table with automatically generated key column, you can. I expect that this is not the case here. How?

Code: Select all

CREATE TABLE tablename (
  PKCOL INTEGER NOT NULL DEFAULT NEXT AVAILABLE,
  other column definitions,
  CONSTRAINT tablename_PKC PRIMARY KEY ( PKCOL )
);

It's the default value NEXT AVAILABLE that does the trick.

[raju_chvr]

Let me make myself more clear. I have some 40 coumns in the hash file and the above 4, I mentioned happened to be 4 out of 40. My Hash file is fine and it has key. Everything is fine except for one row which has a space in its 'Phys_City_Name' -columns and so the next column 'Phys_State_Cd' is getting a null value. But this is not the case when I view the data from DS data browser.

Some of column names:
Phys_Street_Name, Phys_City_Name, Phys_State_Cd, Phys_Country_Cd

Data I have a problem with:
Wilson, North Wilson, NE, US

Again I have a key and the hash file is formed for 42,000 rows. There is no problem with writing into hash-file. The issue is reading from hash-file for a column which has a space in its data.

I hope I made myself clear. Phew! I still need to come in terms with this jargon.

[ray.wurlod]

The space character in data is just another character. It should not generate any problem in retrieving data.
Your data browser never tries to insert anything. The error message is not occurring when you're browsing the data. It's occurring when you run the job, when the job is trying, somewhere, to insert NULL into the Phys_State_CD column.
What exactly is your job doing?

[raju_chvr]

Hi Ray,

It is simple job that reads from the previously built Hash-file and writing into the ORACLE table using ORAOCI stage.

There is also a look-up but that hs nothing to do with the derivation of this column. This column in derived from the hash-file which has the data.

it is still weird that the Data browser shows the data and when actually running the job ORACLE is getting a null value for that particular column.

[raju_chvr]

Never mind some problem on my end !! It is now fixed !! Thanks and Sorry Ray for your time.