Need a Help on Remove some data in Hash file

dr.murthy · Post by **dr.murthy** » Tue Jul 27, 2010 6:13 am

Hi ,

am working on Datstage 7.5 server jobs, my requirement is need to remove some records (let say storenum = 123) in a 30 hashfiles.
i design a job like

Code: Select all

hash-->Trn-->Seq--->Trns--->Hash

the first transformer have some filter condition to remove storenum and then write into a seq file and then write into the same hash file. but it takes more time to design a job for 30 hash fiels.
can you just tel me any other way to implement this change??

Thanks in Advance

chulett · Post by **chulett** » Tue Jul 27, 2010 6:15 am

If the hashed files are in an account (or otherwise have a VOC record) you could use the UV stage and SQL to delete records from them.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jul 27, 2010 6:16 am

Use ODBC or Universe stage to do SQL delete

dr.murthy · Post by **dr.murthy** » Tue Jul 27, 2010 6:30 am

How can i use Universe or ODBC to delete records in hash fiels.

chulett · Post by **chulett** » Tue Jul 27, 2010 6:36 am

Seems like my post is invisible. [scratches head]

'SQL delete' means exactly what it sounds like it means, you would use sql to delete the records just like you would from a relational table.

dr.murthy · Post by **dr.murthy** » Tue Jul 27, 2010 6:44 am

is it possible to read hash file data through UV stage in datastage canvas?

ray.wurlod · Post by **ray.wurlod** » Tue Jul 27, 2010 6:48 am

Yes, provided that a VOC pointer exists to the hashed file.

Note - it's hashed file, not hash file.

chulett · Post by **chulett** » Tue Jul 27, 2010 6:49 am

Still invisible. [sigh]

dr.murthy · Post by **dr.murthy** » Tue Jul 27, 2010 6:55 am

Am really sorry i never worked with hashed file bu i have few knowledge and am not sure where to write SQL in Hashed stage

chulett · Post by **chulett** » Tue Jul 27, 2010 7:13 am

You don't, you write it in the UV stage. As noted. And searching the forums for SETFILE which should help with the VOC issue.

dr.murthy · Post by **dr.murthy** » Tue Jul 27, 2010 8:07 am

Yes i got that but i have an issue when i view the data i got a error message like Datastage/SQl : Syntax Error . unexpected symbol. can you help me to resolve this [/code]

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jul 27, 2010 8:18 am

I think you may need to post more information on what you did, where you did, full error message etc....etc....etc...

dr.murthy · Post by **dr.murthy** » Tue Jul 27, 2010 8:22 am

i set the properties as Datasourcename as localuv and the tablename i specifie the hash file name , when i view the data i got this error message

DSBrowser..UniVerse_0.DSLink2: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:SELECT AcctIDHash.BANK_NUMBER, AcctIDHash.APPL_CODE, AcctIDHash.ACCOUNT_NUMBER, AcctIDHash.SUB_ACCOUNT_NUMBER, AcctIDHash.CLOSED_DATE, AcctIDHash.ACCT_ID, AcctIDHash.OPEN_DATE, AcctIDHash.OPEN_CLOSED_IND, AcctIDHash.ACCOUNT_BRANCH_ID, AcctIDHash.PROD_SRVC_RANK, AcctIDHash.ACCOUNT_RET_CML_IND FROM "AcctIDHash"
SQLSTATE=S1000, DBMS.CODE=950088
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: syntax error. Unexpected symbol. Token was "AcctIDHash.BANK_NUMBER". Scanned command was FROM "AcctIDHash" SELECT AcctIDHash.BANK_NUMBER

dr.murthy · Post by **dr.murthy** » Tue Jul 27, 2010 8:31 am

My datastag mission installed in Windows server

ArndW · Post by **ArndW** » Tue Jul 27, 2010 8:33 am

Whether UNIX or Windows, the uvsh executable is in the same relative location.