Accessing Database through UNIX script

[tostay2003]

Hi everyone,

I am planning to have surrogate keys in the design fetched from the database. The database is DB2.

I know that we can call a shell script from job routine to fetch that. In that script how can i write sql commands for db2 to obtain the stored keys. Does DB2 have any CLI (command line interface)?

are there any ways to obtain the key value from database, apart from using shell script. Because i am fetching this key value as an intial value for my surrogate key generation.

Regards

[ray.wurlod]

What's wrong with using a DataStage job to do that? Even though it's only a one row job, you have everything using the same tool, which suggests easier long-term maintenance.

[tostay2003]

To be honest, i dont know how the maintainence issue effects by having surrogate fetched from database and having datastage tool do it? It would be helpful if you could shed light on it.

I was being told that the maintainence of these keys is easy on DB2 if the keys are maintained on database.

What does the Datastage do? I mean where does it store keys or how does it retrieve etc. and how does this vary from that of getting from database. What sort of effects each have on the maintainence.

Would appreciate your reply

Regards

[ray.wurlod]

As with everything, it depends. If no other users are going to be creating new keys in the database, then you're free to generate keys in DataStage.
You could create a simple job to retrieve the next key value, pick that up (from user status area or from a text file) and use that value as a job parameter for the job that actually generates the keys.

If DataStage needs to compete with other processes for allocation of keys, then it is better to allow the database to generate the keys (for example using a serial data type or a sequence). Or you could have the database allocate a block of key values that could be used exclusively by DataStage - no other process could use those keys.

It's up to you to design it how you will.