cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! X

Windchill autonumbering customization

didriek
12-Amethyst

Windchill autonumbering customization

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!

 

11 REPLIES 11
MikeLockwood
22-Sapphire I
(To:didriek)

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.

TomU
23-Emerald IV
(To:MikeLockwood)

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.

olkatel78
5-Regular Member
(To:MikeLockwood)

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.

olkatel78
5-Regular Member
(To:khimani_mohiki)

Its about Microsoft SQL2012

BenLoosli
23-Emerald II
(To:olkatel78)

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.

BenLoosli
23-Emerald II
(To:didriek)

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.

 

didriek
12-Amethyst
(To:BenLoosli)

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

  • '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"

didriek
12-Amethyst
(To:mmeadows-3)

Thanks, let me check the article as I'm on Oracle

Announcements


Top Tags