From routine to db table

laiko · Post by **laiko** » Fri Jan 16, 2009 12:57 pm

I need to list the start and end timestamps of all the jobs under a Project. I am able to do that using a Routine (function) and LogMsg. However, I need to save the info to database. I have a table with the following columns: job_name, start_time, end_time.

I am sure this requirement is not new to evryone, so am hoping someone can show me an example how to accomplish this, or some kind of detailed tips (since I am but a beginner). Thanks in advance.

chulett · Post by **chulett** » Fri Jan 16, 2009 2:52 pm

You really don't want to be writing to a database from a routine, while it can be done I don't see the need. Simplest solution is to take your information and write it out to a flat file, then have a 'normal' DataStage job that reads that file (or files) and loads the information into your database on a regular basis.

laiko · Post by **laiko** » Fri Jan 16, 2009 3:10 pm

Thanks for your reply. That is one option I was actually thinking. It may be difficult for me as a beginner to get the info from routine to database directly - but 'routine to file to db' may be easier. However, how do i do that?? My problem is how to get the info passed from routine to either file or database table. If possible, can u please show me an example or step-by-step instruction... thanks again.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 16, 2009 3:13 pm

Let's begin with a question - how does the routine generate the data in the first place?

There are a few ways to get data from a routine into a file, the most usual is to use OpenSeq, Seek, WriteSeq and CloseSeq statements. For a single line of data, though, it may be enough to assemble the line of data and use the echo command to write to the file, with appropriate redirection operators.

laiko · Post by **laiko** » Sun Jan 18, 2009 5:47 am

ray.wurlod wrote:Let's begin with a question - how does the routine generate the data in the first place?

I use a For loop to parse all jobs in the Project. Then one by one, I generate a line which has the jobname, starttimestamp and endtimestamp delimited by a space. I use LogMsg to display them to the logs.

chulett · Post by **chulett** » Sun Jan 18, 2009 7:49 am

Ok, then why not give the various 'Seq' functions a try? As Ray noted, OpenSeq opens / creates a sequential file, Seek can be used to position the pointer to the end to begin appending to any existing records, followed by WriteSeq and lastly CloseSeq which do exactly what you'd think they do.

Pick one function (say, OpenSeq) and search the forums, there should be a plethora of examples out there of their use. I know Ray has posted some code, an example of all functions including error handling, a search should turn it up.