connecting sybase database using routines

tom · Post by **tom** » Mon Apr 30, 2007 12:53 am

Hi Folks,

I have a requirement to connect sybase database using DS routine. From there I need to get values from a table. which are passed to a job activity as parameters.

So can anybody tell me what is the option to connect to sybase database using DS routine.

Thanks in advance.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 30, 2007 1:13 am

The correct solution is to get a better requirement.

You have DataStage, with the ability to connect to Sybase and move data to and fro, built in.

Why do you want to write in code what you've spent lots of dollars to buy? It makes no sense whatsoever. Connect via a job. The job can capture the Sybase data wherever you like, including its own user status area, which you can interrogate subsequently.

It CAN be done, using BCI functions (search) but you'll need to spend the same amount of money again to buy the drivers from Data Direct, and will have yet another code base to maintain.

tom · Post by **tom** » Mon Apr 30, 2007 2:01 am

Hi,

My requirement is to pass control table information(in sybase) which are updated regularly as job parameters at sequencer level. Parameters are fileformat, server name, pathnames, etc.

I tried by creating hash file by using directory path lookup and using utilityhashlookup transform to get these values. I couldn't get it. I used as utilityhashlookup('hashfilename',keyvalue,column position). Error I got is 'table not found'.

That's why I went for routine to get the parameter values at sequencer level. Could you please suggest some methods to do the above requirement.

Thanks in advance.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 30, 2007 4:45 am

Create a job that retrieves them from Sybase into a hashed file. Use key names that map to the parameter names.
Use UtilityHashLookup() or Trans() - note that the name is case sensitive - to retrieve the parameter values from the hashed file. Use them directly in the job sequence's expressions if you're using version 7.5 or later, or in a Routine if earlier than version 7.5.

tom · Post by **tom** » Mon Apr 30, 2007 6:29 am

Ray,

Thanks for your response.

I changed my hash file path to my own path and modify code in UtilityHashLookup to OpenPath rather than Open in the copy routine.But still i couldnt read value from hash file.Now error is 'record not found'.I couldnt find Trans() function in my repository.Please advice.

Thanks once again.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 30, 2007 3:08 pm

The Trans() function is intrinsic to the DataStage BASIC language - you will find it in the DataStage BASIC manual. To use it you will need a VOC pointer to the hashed file - search the forum for SETFILE.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 30, 2007 3:10 pm

"Record not found" is a problem with your logic - you are not searching for the same key values that you wrote to the hashed file.

Describe fully the job that writes data from Sybase into the hashed file. What is the key column name, and what values do you assign to it? What is the non-key column name, and what values do you assign to it?