Persistence Datastore Sequence Customization

You want to create a new datastore sequence for use in Windchill.

Windchill uses datastore schema objects to implement a sequence. In Oracle, this is implemented directly as a sequence schema object. In SQLServer, this is implemented as a table and access stored procedure.

• This documentation assumes that you have access to the Windchill datastore directory, <WindchillHome>/db, and access rights to execute DDL scripts in the target datastore.

• For the modeled customization procedure, it is assumed that the Windchill Java Annotations support for sequences has been successfully installed and configured.

The end result of this solution is the creation of a datastore sequence.

Element	Type	Description
<MySequence>	Java class	The class for representing a datastore sequence in Windchill.

1. Create a class <MySequence> in <MyPackage> that extends <MySequence> .

2. Specify the “GenAsDatastoreSequence” annotation and sequence properties as the annotation’s arguements. Specify the initial seed and increment values if the defaults are not sufficient. For example, for seed value 1000 and increment 100, the resulting Java source file would be as follows:

import com.ptc.windchill.annotations.metadata.GenAsDatastoreSequence;

@GenAsDatastoreSequence (objectName="mySequence", seed=1000, increment=100)
public class MySequence extends _MySequence {
}

3. Compile the <MySequence>.java class in <MyPackage>.

4. Generate the DDL scripts.

5. Execute the sequence DDL. From a Windchill shell execute: <WindchillHome>/db/execute_sql_script.bat <MyPackage>/create_<MySequence>_ sequence.sql

Procedure — Creating a Non-modeled Datastore Sequence in Oracle

When a non-modeled sequence is created, the database schema objects are not included in the Windchill introspection information. Therefore, some tools that rely on this information (e.g.Windu, upgrade tools) will treat these objects as unrelated to Windchill.

1. Create the sequence DDL script "create_<MySequence>_sequence.sql" in "<WindchillHome>/db/sql" directory. For multi-byte systems, use directory, "<WindchillHome>/db/sql3".

2. Add the following lines to this script, substituting <MySequence>, <MySequenceSeed>, and <MySequenceIncrement>.

exec WTPK.createSequence('<MySequence>', <MySequenceSeed>,
<MySequenceIncrement>)

3. Execute the sequence DDL. From a Windchill shell, execute : <WindchillHome>/db/execute_sql_script.bat create_<MySequence>_sequence.sql

Procedure – Creating a Non-modeled Datastore Sequence in SQLServer

1. Create the sequence DDL script "create_<MySequence>_sequence.sql" in "<WindchillHome>/db/sqlServer" directory.

2. Add the following line to this script, substituting <MySequence>, <MySequenceSeed>, and <MySequenceIncrement>.

CREATE TABLE wt_sequence_<MySequence>(dummy
CHAR(1),
value BIGINT IDENTITY(<MySequenceSeed>, <MySequenceIncrement>))
go
CREATE PROCEDURE wt_get_next_sequence_<MySequence>
@returnValue BIGINT OUTPUT
AS
INSERT wt_sequence_<MySequence> (dummy) VALUES ('x')
SELECT @returnValue = SCOPE_IDENTITY()
go

3. Execute the sequence DDL. From a Windchill shell, execute <WindchillHome>/db/execute_sql_script.bat create_<MySequence>_sequence.sql <DB_USERNAME> <DB_PASSWORD>

In the primary procedure above, a sequence class was created. To obtain the next value in this sequence, use the PersistenceManager.getNextSequence(Class) method passing <MyPackage>.<MySequence>.class.

Procedure - Obtaining the Next Value in a Non-Modeled Sequence

In the non-modeled procedures above, a sequence was created directly in the datastore. To obtain the next value in this sequence, use the PersistenceManager.getNextSequence(String) method passing the string constant <MySequence>.

Sequence values are used in many areas within Windchill code. See sub-class of wt.fc.DatastoreSequence in Where-Used Tool and/or Javadoc.

The following is a sample script for a partNumber sequence that begins with 1 and has an interval of 20.

CREATE TABLE wt_sequence_partNumber (dummy
CHAR(1), value BIGINT IDENTITY(1,20))
go
CREATE PROCEDURE wt_get_next_sequence_partNumber @returnValue BIGINT OUTPUT
AS
INSERT wt_sequence_<MySequence> (dummy) VALUES ('x')
SELECT @returnValue = SCOPE_IDENTITY()
go