KeyMgtGetNextValue

[Manny 1]

Guys

I have a job which uses the 'KeyMgtGetNextValue' built-In routine. It has been working fine for a few months until now.

The jobs appears to hang in a constant state of 'running'. I have pin-pointed the problem down to a particular sequence I am accessing via 'KeyMgtGetNextValue'.

If I change the sequence the job works fine.
However I noticed a very odd thing when I looked in the hashed file called SDKSequnces (which holds the actual sequences used by KeyMgtGetNextValue).

The bad sequence appears to be constantly incrementing even though there are NO jobs running. So, could there be a rogue process running in the background which is constantly incremanting it?

Has anybody experianced this type of thing before?

Also, if I create a new sequence (which KeyMgtGetNextValue does on the fly) is there anyway I can start the count at a number other than 1? Suppose I could update the actual SDKSequences hashed file....but whats the best way of doing this?

Thanks
Manny

[kcbland]

You should have been putting a seed value into this hash file prior to running any jobs that use it. I personally do not use this SDK routine for a lot of reasons I've covered before on this forum. However, you should at least design a job like OCI/ODBC --> xfm --> hash that selects max on the surrogate key column in your source table and writes the current value into the hash file SDKSequences in the project. The structure is:

Code: Select all

<key>  Sequence name (usually your table name)
<1>  value

You could design an octopus or centipede looking job that has a zillion OCI/ODBC stages each with a transformer doing a select max on separate tables. That way, one job gets all your max values. Another technique would be to use a shell script w/sql script to do this automatically, and then spool results to a text file you could load into the hash file.

[mhester]

Manny,

In addition, you could create one job to retrieve all max columns using just one OCI/ODBC, one transform and one output.

1) Ensure that the job is multi instance enabled
2) Define two parms, one for table, and one for column
3) Create generic metadata to represent column
4) User defined sql will work with the parms you have defined

Do something like the following in your user defined sql -

Code: Select all

SELECT MAX(#COLUMN#) FROM #TBL#;

Also define one column named MAXVAL or whatever to represent the real column.

Now you can use this job at any point you need to retrieve a max of a column and the nice thing is it is parameter driven in case someone changes the name of a column or table, which can happen.

Another option to solve your seed issue would be to create an additional parm for seed value. You could define it as integer and make the default 0, which would indicate to the job that you require the max column value from the table and if it is greater than 0, just use this value instead of what is returned from the max(column). (simple derivation).

The derivation would be something like -

Code: Select all

IF SEED > 0 THEN SEED ELSE outlink.MAXVALUE

I have been doing it this way for years and it works well every time. One job sufficiently parameterized will do exactly what you want.

Regards,

Michael Hester

[Manny 1]

Thanks for the help chaps.

[rdy]

I've been reading this thread and trying to make sense of it, but I can't find the example mentioned

There is a reference to

design a job like OCI/ODBC --> xfm --> hash that selects max on the surrogate key column in your source table and writes the current value into the hash file SDKSequences in the project.

Is this a reference to an example server job that can be found somewhere? I don't understand the hierarchy under OCI/ODBC.

[ray.wurlod]

There's no hierarchy. The ODBC/OCI stage (or any other SQL database stage types) selects a single column, derived as MAX(columnname), from the database table. This is loaded into a hashed file as not-the-key-column, the key column in the hashed file being the table name (probably a job parameter).