Persistence Datastore Sequence Customization
Objective
You want to create a new datastore sequence for use in Windchill.
Background
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.
Scope/Applicability/Assumptions
• 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.
Intended Outcome
The end result of this solution is the creation of a datastore sequence.
Solution
Model a sequence class usingWindchill Java Annotations.
Solution Elements
|
Element
|
Type
|
Description
|
|
<MySequence>
|
Java class
|
The class for representing a datastore sequence in Windchill.
|
Procedure - Creating a Modeled Datastore Sequence
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
Customization Points
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
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/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>
Procedure - Obtaining the Next Value in a Modeled Sequence
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>.
Limitations
None.
Sample Code
Examples of Usage in Windchill Code
Sequence values are used in many areas within Windchill code. See sub-class of wt.fc.DatastoreSequence in Where-Used Tool and/or Javadoc.
Packaged Samples
None.
Non-modeled Sample Scripts
Oracle
The following is a sample script for a partNumber sequence that begins with 1 and has an interval of 20.
exec WTPK.createSequence('partNumber', 1, 20)
SQL Server
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
Related Package/Class Javadoc
• Foundation module
wt.fc