SCD Stage

[venkatvelpula]

As far as best practices front,

Is State file better option than DB sequence for surrogate key generation?

If so, Why?

[SURA]

It will prefer DB rather than file, as i had the bad experience "file corrupted"

DB is safe.

DS User

[ray.wurlod]

If you don't do anything to corrupt the state file, such as opening it with a text editor, the state file is also perfectly safe - it does not corrupt itself.

[SURA]

You are right. But what i did is , referred the same file in different jobs to get the unique id for the whole project.

DS User

[venkatvelpula]

Is there any performance bottle neck for one vs other?

[ray.wurlod]

Is your database local or remote to the DataStage engine? If it's remote then a state file, which perforce must be local, will definitely perform better.

[venkatvelpula]

Database is remote. I can't understand the resposne perforce. Is that performance?

How can the state file perform well..could you explain in detail?

[ray.wurlod]

The word "perforce" is an adverb meaning "necessarily".

I claim only that getting a block of keys out of a local state file will be quicker than sending a request over a network to invoke a database and issue a query against a sequence in that database then wait for the response from that database.

I make no claim about the "performance" of a state file, because it makes no sense to do so. What are your criteria?

[SURA]

If a DB2 Sequence created, then it will be local right?

DS User

[ray.wurlod]

Not necessarily. It will be wherever the DB2 database is.

If you're thinking of using a sequence in XMETA, there's still no requirement for the database (metadata repository tier) to be co-resident with the engine tier.