Slow Compilation

milind · Post by **milind** » Mon Aug 23, 2004 1:50 am

Hi,

We have set up a Sun Solaris server 400 x 4 Mhz, 4Gb ram, with Solaris 2.9 (with latest patches) and DataStage server 7.1.
We have cases where the jobs have 1000 to 2000 columns in the source stage.There is no complex logic in the job.But the compile time goes to 40-50 minutes for such a job (this being the primary reason for procuring a high end machine).Same job takes 10 minutes to compile on NT.The 4 kernel parameters are already set as mentioned in the tuning guide.

Are there other parameters to be tweaked in the uvconfig file?

One more question:
Is there a limit on number of records that can be sorted in the hash file stage in the selection tab(for eg 1 million)?
which is better option: hash sort or unix sort?

rasi · Post by **rasi** » Mon Aug 23, 2004 6:36 am

There isn't any limitation in sorting. But it does take long time in sort stage. You can use unix sort which is better without buying any third party sort software (ie cosort etc.).

Consider putting the records into a staging table and use your database to do the sort. Much faster with adding proper index to it.

Cheers
Siva

ray.wurlod · Post by **ray.wurlod** » Mon Aug 23, 2004 4:39 pm

There is no sorting in the hashed file stage, unless you specify it as a "selection" phrase. Can you advise what you had in mind here?

Investigate the CoSort plug-in. It absolutely flies, even compared to the UNIX sort. DataStage sort stage comes a bad last.

milind · Post by **milind** » Mon Aug 23, 2004 9:52 pm

ray.wurlod wrote:There is no sorting in the hashed file stage, unless you specify it as a "selection" phrase. Can you advise what you had in mind here?

Investigate the CoSort plug-in. It absolutely flies, even compared to the UNIX sort. DataStage sort stage comes a bad last.

Thanks Ray..How do I get hold of the CoSort plugin, does it come in the Datastage cd?..And yes I am sorting in the selection tab(since its a a/c hash and not a lookup)

But can you please advise on the slow compile issue? (This has nothing to do with the sort stage by the way.)

ray.wurlod · Post by **ray.wurlod** » Tue Aug 24, 2004 12:52 am

CoSort plug-in is not part of DataStage; it's a third-party product (that is, more budget). You can find out more and request an evaluation copy from Tools4DataStage. It's particularly valuable for large sorts. I'm just back from a project with an Indian telco; imagine the sheer volume of CDRs they get!

The slow compilation, particularly compared to the Windows platform, is a bit of a mystery. That you have 2000 columns ought not to be a major influence, though it will take time during the preparatory (pre-compile, or "sanity check", phase). How complex are the transformation rules, and how complex is the job design?

When you compile, how long does the "precompiling" message stay in the dialog box before "compiling job" is displayed (approximately)?

There's no tunable in uvconfig or anywhere else that would affect the overall time taken to compile a job, unless you're running out of space in the directory pointed to by UVTEMP. On Solaris the default location (/tmp) is notoriously small by default, and this is compounded by having swap there too. What's the current setting of UVTEMP? I'd definitely try pointing it elsewhere if it's pointing to /tmp.

Apart from that, open a support call and see whether "they" can reproduce what you're seeing.