Windchill autonumbering customization

This is done in the database, not the OIR.  The OIR calls out the sequence name in the database.

If you are using Oracle for example, you need to alter the existing sequence used for EPMDOCUMENT to set the starting number other than zero.

Note: If using Oracle, also set nocache=true. This prevents losing a bunch of numbers in the sequence every time Oracle is restarted.

Similar but not exactly the same with SQL server.

Create a new sequenceID in Oracle and use that in your OIR.

You can set the beginning number of the sequence to 7000000000. There are articles on the web that explain how to do this. Maybe some articles in the PTC knowledge base, too.

You can modify the OIR by adding a prefix of 7 to your existing number sequence and shortening the sequence by 1 character.

The first method is better at keeping the length when the 7xxxxxxxxx numbers are used, it will roll over to 8000000000.

Like Mike said, use the no_cache to prevent jumps in numbering at Oracle restarts.

There are issues with things like save as and autonumbering where you may lose some numbers.

I didn't see any clarification of the database type.  Are you using Oracle or SQL Server?  If it is SQL Server, the sequence nocache isn't relevant.  This is my understanding after dealing with this question recently and a lot of reading.

The Oracle implementation uses database sequences.  Follow this article to set the nocache option in Oracle.

https://www.ptc.com/en/support/article?n=CS113612

PTC's SQL Server implementation of the number generator does not create a database sequence and the nocache configuration is not applicable.

Consider the example SQL from PTC for building a ‘sequence generator’ in SQL Server.

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

What this code does...

1. Create a table 'wt_sequene_{mysequence}' with two columns

• 'dummy' has a single character value of 'x'.  Functionally, we just need some value in this column.
• 'value' holds the IDENTITY value.  IDENTITY [( seed, increment)] is a unique integer or big integer starting with the seed and incrementing by the increment value.  For each row added to the table, the IDENTITY value is calculated by taking the value from the last row in the table and adds the increment to it.

Each row in an IDENTITY column is guaranteed to have a unique value.  It is usually sequentially incremented unless the database is inappropriately shut down or restarted.  For any number generator, you can review the consumed IDENTITY values.  For example:

-- Get the existing sequence name from the OIRs.

-- Select all rows from the WTDocument 'sequence'

select * from wtdocumentid_seq;

-- For large sequences, select last 100 rows

select * from (select top 100 * from wtdocumentid_seq order by  value desc) a order by value;

2. Create a procedure

• Insert a value in the dummy column. This will cause the value column to automatically populate with the next sequence number.
• Return the 'returnValue' parameter from the freshly inserted row.

To improve performance, Microsoft originally hard coded a cache value of 10 for IDENTITY.  It worked so well that they enhanced it in SQL Server 2012 to cache 1000 values for an integer and 10000 values for a big integer (which is how PTC defines sequence tables).  That is where skipping large blocks of numbers started in SQL Server.  If the database is not shut down properly, the cached values are skipped, and the database picks up 10,000 values forward of the last consumed value.

https://dba.stackexchange.com/questions/88930/identity-value-jumps-when-restarting-sql-server

This behavior can be disabled with a SQL Server startup trace flag.

https://www.ptc.com/en/support/article?n=CS208897

"If the gap at SQL Server restart is not acceptable then it can be suppressed by adding a -T272 parameter to the startup of SQL Server"