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
KeyMgtGetNextValue
Moderators: chulett, rschirm, roy
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:
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.
Code: Select all
<key> Sequence name (usually your table name)
<1> value
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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 -
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 -
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
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#;
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
Regards,
Michael Hester
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
More help with max value of column
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
There is a reference to
Is this a reference to an example server job that can be found somewhere? I don't understand the hierarchy under OCI/ODBC.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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.