Community Tip - Learn all about PTC Community Badges. Engage with PTC and see how many you can earn! X
Hi,
I'm busy setting up windchill and the numbering is automatic:
0000000001 for the first one, 000000000x... counting up.
However, I would like to change the first number: 700000000x
So my first part generated is 7000000001.
How to implement, I how no knowledge of XML 😕
Thanks!
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.
Theoretically you could leave the sequence (and database) alone, use a sequence that is one digit shorter, and then just add a "7" prefix to the front provided sequence via XML.
Hello, Mike! My name is Oleksiy, im from Kyiv, Ukraine. Here is war, but those who not on war try to working
Here my problem.
When the power is off and our server not turning off correctly we have jump up our counter plus 10000.
For example at 8:00 number was 0000201158, at 15:00 when we starting our server wnc it have 0000211201.
I believe that exist some place in database where i can manually push it back by entering value "0000201160" and it
continues to plus 1 from it value.
Do u know something about it and maybe you can help me?
thx anywhere)
In SQLplus you can modify the sequence up or down by whatever increment you want, so you could go minus 10000 after a power outage, the commands are in the Oracle documentation. Depending on your situation, moving your db to the cloud may be a good option for the long run.
Its about Microsoft SQL2012
Are you using Oracle or SQLServer?
On Oracle, read the above thread for setting your initial number and setting the no_cache to prevent some skipping of numbers. I think there is also a setting for the skip increment number when Oracle restarts. My system only jumps by 10, not 10,000.
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.
Hi,
Thanks!
I got it working with the prefix 7... As I didn't find how to do the other option in XML
However, how and where do I insert the nocache line?
Follow this article to set the nocache option in Oracle.
https://www.ptc.com/en/support/article?n=CS113612
This is not relevant to SQL Server.
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
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
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"
Thanks, let me check the article as I'm on Oracle