Topic
  • 12 replies
  • Latest Post - ‏2012-07-12T12:07:49Z by SystemAdmin
SPaul
SPaul
15 Posts

Pinned topic Current value of Sequence

‏2012-07-11T09:50:53Z |
How can we update current sequence value ?

For example my current sequence value is 5 & sequence is incremented by 1. Now I want to set the sequence value as 10.

Please help......
Updated on 2012-07-12T12:07:49Z at 2012-07-12T12:07:49Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    648 Posts

    Re: Current value of Sequence

    ‏2012-07-11T10:26:58Z  
    For example, changing a record in SEQ table
  • SystemAdmin
    SystemAdmin
    648 Posts

    Re: Current value of Sequence

    ‏2012-07-11T10:27:26Z  
    call to 'Sequence::getSequenceNextValue' would get and set the sequence to the next value.
  • SPaul
    SPaul
    15 Posts

    Re: Current value of Sequence

    ‏2012-07-11T11:48:31Z  
    For example, changing a record in SEQ table
    Thanks for your reply .
    Yes I have checked the SEQ table . But it has no column that is showing latest value of that sequence .

    SEQ table has columns like

    SEQ_COMPANY_ID
    SEQ_OBJECT_ID
    SEQ_OBJECT_TYPE
    SEQ_NAME
    SEQ_INCREMENT
    SEQ_VALUE

    As I have already mentioned I want to change the current value of that sequence to some other value.
    Is there any other table that used to stote latest value of sequence?
  • SystemAdmin
    SystemAdmin
    648 Posts

    Re: Current value of Sequence

    ‏2012-07-11T12:00:23Z  
    • SPaul
    • ‏2012-07-11T11:48:31Z
    Thanks for your reply .
    Yes I have checked the SEQ table . But it has no column that is showing latest value of that sequence .

    SEQ table has columns like

    SEQ_COMPANY_ID
    SEQ_OBJECT_ID
    SEQ_OBJECT_TYPE
    SEQ_NAME
    SEQ_INCREMENT
    SEQ_VALUE

    As I have already mentioned I want to change the current value of that sequence to some other value.
    Is there any other table that used to stote latest value of sequence?
    A value which will be used as a start point for calculating sequence numbers after a start of a server is stored in the SEQ_VALUE column. The current value of a sequence, imho, is storing in the memory.

    What is a reason you want to change a sequence number?
  • SPaul
    SPaul
    15 Posts

    Re: Current value of Sequence

    ‏2012-07-11T12:29:50Z  
    A value which will be used as a start point for calculating sequence numbers after a start of a server is stored in the SEQ_VALUE column. The current value of a sequence, imho, is storing in the memory.

    What is a reason you want to change a sequence number?
    Lets consider the scenerio below why i want to update the sequence value...

    while creating an item in catalog say Test Catalog I have 2 option

    1. Through PIM UI (the primery Key in the catalog is a TYPE Sequence)
    2. Thorugh an Import script (ITEM belongs to same catalog in one ENV can be imported in other ENV.)

    Now take the example..

    I created an ITEM in Test Catalog in Server1 through PIM UI. Item primery KEY is 1 now..
    NOW I trying to import some items from other ENV to Server1 and ITEM imported with primary key 2,3,4 say for example.....

    now again while creating a new item in Server1 through PIM UI,I am expecting the item Primery key as 5...

    but since its still holding the Primery key sequence as 1 (earlier created)..its trying to generate primery key 2 as per sequence and getting Primery Key violation error as 2 is already been created through Import Script i mentioned above....

    So I want to update the last sequence number at the end of Import Process so that next time when i am creating a new ITEM through PIM UI it will start the sequence after this updated value...

    Hope you got the issue....

    Thanks in advance for your quick response.....
  • SystemAdmin
    SystemAdmin
    648 Posts

    Re: Current value of Sequence

    ‏2012-07-11T12:54:19Z  
    • SPaul
    • ‏2012-07-11T12:29:50Z
    Lets consider the scenerio below why i want to update the sequence value...

    while creating an item in catalog say Test Catalog I have 2 option

    1. Through PIM UI (the primery Key in the catalog is a TYPE Sequence)
    2. Thorugh an Import script (ITEM belongs to same catalog in one ENV can be imported in other ENV.)

    Now take the example..

    I created an ITEM in Test Catalog in Server1 through PIM UI. Item primery KEY is 1 now..
    NOW I trying to import some items from other ENV to Server1 and ITEM imported with primary key 2,3,4 say for example.....

    now again while creating a new item in Server1 through PIM UI,I am expecting the item Primery key as 5...

    but since its still holding the Primery key sequence as 1 (earlier created)..its trying to generate primery key 2 as per sequence and getting Primery Key violation error as 2 is already been created through Import Script i mentioned above....

    So I want to update the last sequence number at the end of Import Process so that next time when i am creating a new ITEM through PIM UI it will start the sequence after this updated value...

    Hope you got the issue....

    Thanks in advance for your quick response.....
    1) Did you face with the issue or just thinking about?
    2) When you are importing items from the other environment are you importing also the PKs (2,3,4)? Because only in that case you will face with the issue.
  • SPaul
    SPaul
    15 Posts

    Re: Current value of Sequence

    ‏2012-07-11T13:02:58Z  
    1) Did you face with the issue or just thinking about?
    2) When you are importing items from the other environment are you importing also the PKs (2,3,4)? Because only in that case you will face with the issue.
    1) Yes I am facing the problem not only thinking of it..
    2) yes,when i am importing the items from the other environment I am also importing the PKs (2,3,4).

    and at the end of import script i want to update the last sequence number in Test Catalog with 4(last items sequence in the import)...which i am unable to...

    Do you have any workaround for this?

    thanks once again for your quick response...
  • SystemAdmin
    SystemAdmin
    648 Posts

    Re: Current value of Sequence

    ‏2012-07-11T14:22:40Z  
    • SPaul
    • ‏2012-07-11T13:02:58Z
    1) Yes I am facing the problem not only thinking of it..
    2) yes,when i am importing the items from the other environment I am also importing the PKs (2,3,4).

    and at the end of import script i want to update the last sequence number in Test Catalog with 4(last items sequence in the import)...which i am unable to...

    Do you have any workaround for this?

    thanks once again for your quick response...
    Every time when you try save item sequence increase on 1. You can save item while you will get need pk. You can do it use script.
  • SystemAdmin
    SystemAdmin
    648 Posts

    Re: Current value of Sequence

    ‏2012-07-12T04:26:34Z  
    • SPaul
    • ‏2012-07-11T13:02:58Z
    1) Yes I am facing the problem not only thinking of it..
    2) yes,when i am importing the items from the other environment I am also importing the PKs (2,3,4).

    and at the end of import script i want to update the last sequence number in Test Catalog with 4(last items sequence in the import)...which i am unable to...

    Do you have any workaround for this?

    thanks once again for your quick response...
    It is a bad idea managing a PK for two independent catalogs, because if you solve the problem you need also think about a resolution the following issues you may face with.

    1) What will you do if in the destination catalog there is already an item with PK=3 while you are importing an item with the same PK?
    2) You must restrict access of users to UI during the import to prevent creating items with the same PK. How will you achieve that?
    2a) You also must guarantee that an user is not in a process of creating a new item (there is no an opened not saved form in UI) before starting the import, otherwise see 2)
  • SPaul
    SPaul
    15 Posts

    Re: Current value of Sequence

    ‏2012-07-12T11:31:22Z  
    It is a bad idea managing a PK for two independent catalogs, because if you solve the problem you need also think about a resolution the following issues you may face with.

    1) What will you do if in the destination catalog there is already an item with PK=3 while you are importing an item with the same PK?
    2) You must restrict access of users to UI during the import to prevent creating items with the same PK. How will you achieve that?
    2a) You also must guarantee that an user is not in a process of creating a new item (there is no an opened not saved form in UI) before starting the import, otherwise see 2)
    Hi Alexander ,

    Please find below answers

    1) As per our logic if same primery key exists say PK=3 we are taking next sequence value and creating the item.But i hope you understood that at the end of import job we want to update the last sequence ID to existing sequence so that next time we create a item manually in the catalog we do not encounter duplicate primery key error.

    2) If we get a way to update the sequence in the database then we dont need to restrict access the users to PIM UI.As its a one time process , so while importing items user will not create any item from UI

    2a) Yes sure we are maintaining that. we wish we could update the last sequence to database so any time user going to create a new item through PIM UI it will take the next sequence based on last update in database.

    Hope you understad our scenario ... If you have any idea on updating latest sequence value please share with us...

    Thanks in advance...
  • XH18_Remi_Serra
    XH18_Remi_Serra
    3 Posts

    Re: Current value of Sequence

    ‏2012-07-12T11:55:16Z  
    • SPaul
    • ‏2012-07-12T11:31:22Z
    Hi Alexander ,

    Please find below answers

    1) As per our logic if same primery key exists say PK=3 we are taking next sequence value and creating the item.But i hope you understood that at the end of import job we want to update the last sequence ID to existing sequence so that next time we create a item manually in the catalog we do not encounter duplicate primery key error.

    2) If we get a way to update the sequence in the database then we dont need to restrict access the users to PIM UI.As its a one time process , so while importing items user will not create any item from UI

    2a) Yes sure we are maintaining that. we wish we could update the last sequence to database so any time user going to create a new item through PIM UI it will take the next sequence based on last update in database.

    Hope you understad our scenario ... If you have any idea on updating latest sequence value please share with us...

    Thanks in advance...
    Hi Paul,

    I've seen it done this way :
    • Run your import, get the highest key (keep track during your import or use WQL)
    • Update SEQ table with SEQ_VALUE = highest key + 1 (via SQL, use SEQ_NAME to identify your sequence, it will look like %companyid%_%catalog%_%spec%_%attribute%)
    • Restart ALL jvms to ensure they read SEQ when asked for a new key (and dont use their cached sequence value)
    For initial load.

    Note : if you don't see your sequence in SEQ, create a few items first.

    Regards
  • SystemAdmin
    SystemAdmin
    648 Posts

    Re: Current value of Sequence

    ‏2012-07-12T12:07:49Z  
    • SPaul
    • ‏2012-07-12T11:31:22Z
    Hi Alexander ,

    Please find below answers

    1) As per our logic if same primery key exists say PK=3 we are taking next sequence value and creating the item.But i hope you understood that at the end of import job we want to update the last sequence ID to existing sequence so that next time we create a item manually in the catalog we do not encounter duplicate primery key error.

    2) If we get a way to update the sequence in the database then we dont need to restrict access the users to PIM UI.As its a one time process , so while importing items user will not create any item from UI

    2a) Yes sure we are maintaining that. we wish we could update the last sequence to database so any time user going to create a new item through PIM UI it will take the next sequence based on last update in database.

    Hope you understad our scenario ... If you have any idea on updating latest sequence value please share with us...

    Thanks in advance...
    Hi Paul,

    OK. I am not known any way to update a current sequence number. I think it doesn't exist.

    So if it is a one time import you should use the method described by Remi Serra
    If not, you have to refuse to importing of PKs (I understood from answer #1 that you can change a importing PK if there is an item in the destination catalog with the same PK. You can spread this rule on an entire set of importing items)