MS SQL server 2005 sequences for DD? big issues

in this past w/ oracle it was easy to use DD w/ oracle sequences as a primary key, but w/ sql server we r having the following issues:

Our iwov DD table has an IDENTITY field called ID as the primary key

1. This is maintained and inserted by the DB itself, we cannot pass it from the DCT.
2. Since this is a primary key, DD won’t work unless it gets a value for this field from DCR.
3. Now this primary key column is used as a Foreign key to establish a join between the events and their location [in another table]. DD needs us to specify this foreign key relation in the config file for deployments to happen in both tables.

The reasons we chose to request the removal of identity field are:

1. The identity fields happens to be the primary key as well OD doesn’t support it. [as per the release notes].
2. The Values of the Identity field would not be available to us until the insert to the main table is complete. We will need a script to get this value from database. Then we will be forced to update the secondary table externally using another Perl script through a separate external task in the workflow.
3. Also, if we update the main table using OD and later update the location records through another IPL , there is no transactional deployment, we will have to think a logic for the rollbacks, which are usually automated and managed by DD itself

Find more posts tagged with

Comments

Migrateduser

I would be inclined to add path or some other unique field to the DCR and have DD treat that as the PK, even if the database continues to use the identity as the true PK.

Alternatively, you could potentially get the next value that the identity will return in a tuple pre-processor and put that into your tuple. I would worry about a race condition though, where two deployments might both get to the point of requesting the next value for the identity and then you end up trying to insert duplicate values.

Adam Stoller

I would be inclined to add path or some other unique field to the DCR and have DD treat that as the PK, even if the database continues to use the identity as the true PK.

We just recently ran into this on my current project (they're using MySQL) and although the DB has an auto_insert'ed column as the primary key, we specified a different (though still containing unique data per row) column as the primary key in the DD config file - we are not using 'path', so we have delete-tracker="yes" and real-update="yes".

Note, however, that ours is a very simple single-table schema - no joins, foreign keys, etc. So your mileage may vary.

Migrateduser

thanks guys - here is some additional feedback from our developers:

If we set up another field to let DD think was the primary key and set that field up to be the foreign key to the other table.

That really puts us where we would be with changing the PK ID field anyway. The only difference is that we could still query and return News items (in this case) by an ID that is not a huge number.

I agree about the tuple idea that is mentioned, also. Not a good idea.

However, I was thinking... what if we create a SP that acts like an Oracle sequence? It could INSERT a blank record into the NewsAndEvents table and return the ID that is created. That ID could be used for the actual work that DD does, then, as long as DD could do an UPDATE of the master table and not just an INSERT. As long as the SP could be queried in the DCT, then it should operate just like an Oracle sequence does.

If DD couldn't do the UPDATE mentioned above, we *could* create another table that just holds an Identity ID field in it and use that in combination with the SP mentioned above. That would be more like the true Oracle sequence, but it would cause us to have more tables... So, for instance, we would add a NewsAndEventsID table.

Migrateduser

Your stored procedure idea should work, but you would have to execute the SP as part of a tuple pre-processor. That would solve the race condition, though.

jmennen

You mention that this is easy to do with Oracle sequences. Please elaborate. I am having issues with DataDeploy and my Oracle primary key ids.

Keep getting the following error:

DBD: ERROR:Values for PK column [SUBCATEGORY_ID] missing in [templatedata\external_site\product_category\data\product].
DBD: ERROR:Exception occured: Values for PK column [SUBCATEGORY_ID] missing in [templatedata\external_site\product_category\data\product].
DBD: ERROR:
com.interwoven.dd100.dd.InvalidSourceTupleException: Values for PK column [SUBCATEGORY_ID] missing in

However, all necessary fields are in the config xml file.

Any ideas are appreciated.