Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Earlier today I had two failed followup posts.  I'll just provide the answers as I researched them.

1. As Art and Mike responded, bigserial column type is 309
2. I found through experimentation, that serial8 is column type 274.  What'sa difference?! (Except that a table cannot have both a bigserial and serial8 column.)
3. The next value for  serial column is kept in systabinfo.ti_serialv corresponding to the partition number of the table. But what about the next serial8 and bigserial values?  Well the current for all three is in sysptnhdr, columns serialv, cur_bigserial, and cur_serial8.

Here's a mushed version of the query as I ran it, lookin for high serial values in a database, here named "yahoo". Run in database sysmaster

select u.database || ":" || u.tabname, u.colname,
       ptn.serialv, ptn.cur_bigserial, ptn.cur_serial8
  from sysptnhdr ptn,
       (select "yahoo" database, t.partnum, t.tabname, c.colname
          from yahoo:systables t, yahoo:syscolumns c
         where t.tabid = c.tabid
           and coltype in (262, 309, 274)    -- Serial, bigserial, serial8
         ) u 
  where ptn.partnum = u.partnum
    and ptn.serialv > 100000000
  order by ptn.serialv desc
 ;

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Earlier today I had two failed followup posts.  I'll just provide the answers as I researched them.

1. As Art and Mike responded, bigserial column type is 309
2. I found through experimentation, that serial8 is column type 274.  What'sa difference?! (Except that a table cannot have both a bigserial and serial8 column.)
3. The next value for  serial column is kept in systabinfo.ti_serialv corresponding to the partition number of the table. But what about the next serial8 and bigserial values?  Well the current for all three is in sysptnhdr, columns serialv, cur_bigserial, and cur_serial8.

Here's a mushed version of the query as I ran it, lookin for high serial values in a database, here named "yahoo". Run in database sysmaster

select u.database || ":" || u.tabname, u.colname,
       ptn.serialv, ptn.cur_bigserial, ptn.cur_serial8
  from sysptnhdr ptn,
       (select "yahoo" database, t.partnum, t.tabname, c.colname
          from yahoo:systables t, yahoo:syscolumns c
         where t.tabid = c.tabid
           and coltype in (262, 309, 274)    -- Serial, bigserial, serial8
         ) u 
  where ptn.partnum = u.partnum
    and ptn.serialv > 100000000
  order by ptn.serialv desc
 ;

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

I don't understand your second follow-up question, namely:
"I found through experimentation, that serial8 is column type 274.  What'sa difference?! (Except that a table cannot have both a bigserial and serial8 column.)"

Difference from what? From BIGSERIAL? BIGSERIAL (and BIGINT) are 64bit binary integers (ie a "C" long long) while a SERIAL8 (and INT8) is a proprietary 10 byte structure that has the same data range as BIGSERIAL. Essentially, if it were not for the fact that several system tables were modified in v11 to include INT8 types, INT8 and SERIAL8 would have been deprecated and dropped. IBM recommends no longer using INT8 or SERIAL8 in favor ot BIGINT and BIGSERIAL.

If that is not the question, please rephrase.

Art

Earlier today I had two failed followup posts.  I'll just provide the answers as I researched them.

1. As Art and Mike responded, bigserial column type is 309
2. I found through experimentation, that serial8 is column type 274.  What'sa difference?! (Except that a table cannot have both a bigserial and serial8 column.)
3. The next value for  serial column is kept in systabinfo.ti_serialv corresponding to the partition number of the table. But what about the next serial8 and bigserial values?  Well the current for all three is in sysptnhdr, columns serialv, cur_bigserial, and cur_serial8.

Here's a mushed version of the query as I ran it, lookin for high serial values in a database, here named "yahoo". Run in database sysmaster

select u.database || ":" || u.tabname, u.colname,
       ptn.serialv, ptn.cur_bigserial, ptn.cur_serial8
  from sysptnhdr ptn,
       (select "yahoo" database, t.partnum, t.tabname, c.colname
          from yahoo:systables t, yahoo:syscolumns c
         where t.tabid = c.tabid
           and coltype in (262, 309, 274)    -- Serial, bigserial, serial8
         ) u 
  where ptn.partnum = u.partnum
    and ptn.serialv > 100000000
  order by ptn.serialv desc
 ;

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

I don't understand your second follow-up question, namely:
"I found through experimentation, that serial8 is column type 274.  What'sa difference?! (Except that a table cannot have both a bigserial and serial8 column.)"

Difference from what? From BIGSERIAL? BIGSERIAL (and BIGINT) are 64bit binary integers (ie a "C" long long) while a SERIAL8 (and INT8) is a proprietary 10 byte structure that has the same data range as BIGSERIAL. Essentially, if it were not for the fact that several system tables were modified in v11 to include INT8 types, INT8 and SERIAL8 would have been deprecated and dropped. IBM recommends no longer using INT8 or SERIAL8 in favor ot BIGINT and BIGSERIAL.

If that is not the question, please rephrase.

Art

Earlier today I had two failed followup posts.  I'll just provide the answers as I researched them.

1. As Art and Mike responded, bigserial column type is 309
2. I found through experimentation, that serial8 is column type 274.  What'sa difference?! (Except that a table cannot have both a bigserial and serial8 column.)
3. The next value for  serial column is kept in systabinfo.ti_serialv corresponding to the partition number of the table. But what about the next serial8 and bigserial values?  Well the current for all three is in sysptnhdr, columns serialv, cur_bigserial, and cur_serial8.

Here's a mushed version of the query as I ran it, lookin for high serial values in a database, here named "yahoo". Run in database sysmaster

select u.database || ":" || u.tabname, u.colname,
       ptn.serialv, ptn.cur_bigserial, ptn.cur_serial8
  from sysptnhdr ptn,
       (select "yahoo" database, t.partnum, t.tabname, c.colname
          from yahoo:systables t, yahoo:syscolumns c
         where t.tabid = c.tabid
           and coltype in (262, 309, 274)    -- Serial, bigserial, serial8
         ) u 
  where ptn.partnum = u.partnum
    and ptn.serialv > 100000000
  order by ptn.serialv desc
 ;

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

I posted a long, researched rejoinder earlier to Art and Mike's replies today but it seems to have vanished.  Rather than duplicate all that research I'll just start with the bottom line question... Sort of..

For serial column in a table, there is a corresponding column is sysmaster:systabinfo: ti_serialv, the next serial value to be used in the next insert to the tables.  All fine for serial data type.  But there seems to be no corresponding "next value" for a bigserial column.  So where is that kept?  An additional table linked with systabinfo?

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

I posted a long, researched rejoinder earlier to Art and Mike's replies today but it seems to have vanished.  Rather than duplicate all that research I'll just start with the bottom line question... Sort of..

For serial column in a table, there is a corresponding column is sysmaster:systabinfo: ti_serialv, the next serial value to be used in the next insert to the tables.  All fine for serial data type.  But there seems to be no corresponding "next value" for a bigserial column.  So where is that kept?  An additional table linked with systabinfo?

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Andreas said:

> changing a serial to a bigserial will not reset the value for the serial.

If I change the serial column to bigserial, I would hope it DOES inherit the current highest value from the current serial.  And since I'm not using the serial anymore, who cares if the cur_serial in sysptnhdr still has its high value? Nobody is looking at it anymore.  Dead yeast in the beer.  Just like a table with no bigserial column; the value in sysptnhdr is still 1 but nobody cares about it.

-- Jacob S

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Jacob:

I have to apologize, when you alter a SERIAL to a BUGSERIAL (and presumably to SERIAL8) the sysptnhdr record for the partnum is updated by copying the serialv value to cur_bigserial, so you do not have to specifically alter the column type with an initializer!

As far as resetting to '1', witness:

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Jacob:

I have to apologize, when you alter a SERIAL to a BUGSERIAL (and presumably to SERIAL8) the sysptnhdr record for the partnum is updated by copying the serialv value to cur_bigserial, so you do not have to specifically alter the column type with an initializer!

As far as resetting to '1', witness:

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Not so fast there, Jake!

I have discovered, to my great distress, that systabinfo.ti_serialv (and, of course, sysptnhdr.serialv) and NOTHING to to with the actual serial values. For example, for one particular table (based on partnum) I found a large negative number in systabinfo.ti_serialv. Specifically: 

ti_serialv   -1507688642

on the other hand, my target user, who requested the report, asked me to run select max(serial column) on the table, I got 639772559.  I KNOW they're the same table because I am specifying the same partnum.

It appears that I have been laboring under an illusion since I started this project. Anyone know how to tap-dance?  Or at least explain this discrepancy?My discovery goes totally against the comments in sysmaster.sql.

Feeling like quite the fool here!

Jacob:

I have to apologize, when you alter a SERIAL to a BUGSERIAL (and presumably to SERIAL8) the sysptnhdr record for the partnum is updated by copying the serialv value to cur_bigserial, so you do not have to specifically alter the column type with an initializer!

As far as resetting to '1', witness:

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Not so fast there, Jake!

I have discovered, to my great distress, that systabinfo.ti_serialv (and, of course, sysptnhdr.serialv) and NOTHING to to with the actual serial values. For example, for one particular table (based on partnum) I found a large negative number in systabinfo.ti_serialv. Specifically: 

ti_serialv   -1507688642

on the other hand, my target user, who requested the report, asked me to run select max(serial column) on the table, I got 639772559.  I KNOW they're the same table because I am specifying the same partnum.

It appears that I have been laboring under an illusion since I started this project. Anyone know how to tap-dance?  Or at least explain this discrepancy?My discovery goes totally against the comments in sysmaster.sql.

Feeling like quite the fool here!

Jacob:

I have to apologize, when you alter a SERIAL to a BUGSERIAL (and presumably to SERIAL8) the sysptnhdr record for the partnum is updated by copying the serialv value to cur_bigserial, so you do not have to specifically alter the column type with an initializer!

As far as resetting to '1', witness:

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Jacob:

As Mike noted, the three serial value holder fields in the table's partition header page will wrap to negative but the next serial value that is assigned when that happens is 1 calculated by adding 2^31 to the stored negative value. That's just the way it works until that wraps back to zero causing the serial values to start again from 1.

Art

Not so fast there, Jake!

I have discovered, to my great distress, that systabinfo.ti_serialv (and, of course, sysptnhdr.serialv) and NOTHING to to with the actual serial values. For example, for one particular table (based on partnum) I found a large negative number in systabinfo.ti_serialv. Specifically: 

ti_serialv   -1507688642

on the other hand, my target user, who requested the report, asked me to run select max(serial column) on the table, I got 639772559.  I KNOW they're the same table because I am specifying the same partnum.

It appears that I have been laboring under an illusion since I started this project. Anyone know how to tap-dance?  Or at least explain this discrepancy?My discovery goes totally against the comments in sysmaster.sql.

Feeling like quite the fool here!

Jacob:

I have to apologize, when you alter a SERIAL to a BUGSERIAL (and presumably to SERIAL8) the sysptnhdr record for the partnum is updated by copying the serialv value to cur_bigserial, so you do not have to specifically alter the column type with an initializer!

As far as resetting to '1', witness:

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

I used a bad example, Art.  But for that very same table I did this:

select * from <table>
 where <ser-column> = (select max(<ser-column>) from table);

and indeed, the serial column in the select * is 639,795,005 A pretty large number but a far cry from going negative. From Mike's note am I supposed to be able to map that negative number to the actual serial value I just demonstrated?  Some arcane arithmetic?

Jacob:

As Mike noted, the three serial value holder fields in the table's partition header page will wrap to negative but the next serial value that is assigned when that happens is 1 calculated by adding 2^31 to the stored negative value. That's just the way it works until that wraps back to zero causing the serial values to start again from 1.

Art

Not so fast there, Jake!

I have discovered, to my great distress, that systabinfo.ti_serialv (and, of course, sysptnhdr.serialv) and NOTHING to to with the actual serial values. For example, for one particular table (based on partnum) I found a large negative number in systabinfo.ti_serialv. Specifically: 

ti_serialv   -1507688642

on the other hand, my target user, who requested the report, asked me to run select max(serial column) on the table, I got 639772559.  I KNOW they're the same table because I am specifying the same partnum.

It appears that I have been laboring under an illusion since I started this project. Anyone know how to tap-dance?  Or at least explain this discrepancy?My discovery goes totally against the comments in sysmaster.sql.

Feeling like quite the fool here!

Jacob:

I have to apologize, when you alter a SERIAL to a BUGSERIAL (and presumably to SERIAL8) the sysptnhdr record for the partnum is updated by copying the serialv value to cur_bigserial, so you do not have to specifically alter the column type with an initializer!

As far as resetting to '1', witness:

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Jacob:

I would expect that the serialv stored value would be something like this:\

639795005 - 2^31
-1507688643

Art

I used a bad example, Art.  But for that very same table I did this:

select * from <table>
 where <ser-column> = (select max(<ser-column>) from table);

and indeed, the serial column in the select * is 639,795,005 A pretty large number but a far cry from going negative. From Mike's note am I supposed to be able to map that negative number to the actual serial value I just demonstrated?  Some arcane arithmetic?

Jacob:

As Mike noted, the three serial value holder fields in the table's partition header page will wrap to negative but the next serial value that is assigned when that happens is 1 calculated by adding 2^31 to the stored negative value. That's just the way it works until that wraps back to zero causing the serial values to start again from 1.

Art

Not so fast there, Jake!

I have discovered, to my great distress, that systabinfo.ti_serialv (and, of course, sysptnhdr.serialv) and NOTHING to to with the actual serial values. For example, for one particular table (based on partnum) I found a large negative number in systabinfo.ti_serialv. Specifically: 

ti_serialv   -1507688642

on the other hand, my target user, who requested the report, asked me to run select max(serial column) on the table, I got 639772559.  I KNOW they're the same table because I am specifying the same partnum.

It appears that I have been laboring under an illusion since I started this project. Anyone know how to tap-dance?  Or at least explain this discrepancy?My discovery goes totally against the comments in sysmaster.sql.

Feeling like quite the fool here!

Jacob:

I have to apologize, when you alter a SERIAL to a BUGSERIAL (and presumably to SERIAL8) the sysptnhdr record for the partnum is updated by copying the serialv value to cur_bigserial, so you do not have to specifically alter the column type with an initializer!

As far as resetting to '1', witness:

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

I used a bad example, Art.  But for that very same table I did this:

select * from <table>
 where <ser-column> = (select max(<ser-column>) from table);

and indeed, the serial column in the select * is 639,795,005 A pretty large number but a far cry from going negative. From Mike's note am I supposed to be able to map that negative number to the actual serial value I just demonstrated?  Some arcane arithmetic?

Jacob:

As Mike noted, the three serial value holder fields in the table's partition header page will wrap to negative but the next serial value that is assigned when that happens is 1 calculated by adding 2^31 to the stored negative value. That's just the way it works until that wraps back to zero causing the serial values to start again from 1.

Art

Not so fast there, Jake!

I have discovered, to my great distress, that systabinfo.ti_serialv (and, of course, sysptnhdr.serialv) and NOTHING to to with the actual serial values. For example, for one particular table (based on partnum) I found a large negative number in systabinfo.ti_serialv. Specifically: 

ti_serialv   -1507688642

on the other hand, my target user, who requested the report, asked me to run select max(serial column) on the table, I got 639772559.  I KNOW they're the same table because I am specifying the same partnum.

It appears that I have been laboring under an illusion since I started this project. Anyone know how to tap-dance?  Or at least explain this discrepancy?My discovery goes totally against the comments in sysmaster.sql.

Feeling like quite the fool here!

Jacob:

I have to apologize, when you alter a SERIAL to a BUGSERIAL (and presumably to SERIAL8) the sysptnhdr record for the partnum is updated by copying the serialv value to cur_bigserial, so you do not have to specifically alter the column type with an initializer!

As far as resetting to '1', witness:

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Not so fast there, Jake!

I have discovered, to my great distress, that systabinfo.ti_serialv (and, of course, sysptnhdr.serialv) and NOTHING to to with the actual serial values. For example, for one particular table (based on partnum) I found a large negative number in systabinfo.ti_serialv. Specifically: 

ti_serialv   -1507688642

on the other hand, my target user, who requested the report, asked me to run select max(serial column) on the table, I got 639772559.  I KNOW they're the same table because I am specifying the same partnum.

It appears that I have been laboring under an illusion since I started this project. Anyone know how to tap-dance?  Or at least explain this discrepancy?My discovery goes totally against the comments in sysmaster.sql.

Feeling like quite the fool here!

Jacob:

I have to apologize, when you alter a SERIAL to a BUGSERIAL (and presumably to SERIAL8) the sysptnhdr record for the partnum is updated by copying the serialv value to cur_bigserial, so you do not have to specifically alter the column type with an initializer!

As far as resetting to '1', witness:

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Isn't this just the case of Informix internally treating the serial values as unsigned integers ( in the case of SERIAL, values from 0 to 4294967295 ), but the clients that we use cannot "print" unsigned integers, so they just "print" then as signed integers and we see negative numbers?

More of "limitation" of what the Informix clients can "print"/represent ?

Best regards,

Luis Marques

Not so fast there, Jake!

I have discovered, to my great distress, that systabinfo.ti_serialv (and, of course, sysptnhdr.serialv) and NOTHING to to with the actual serial values. For example, for one particular table (based on partnum) I found a large negative number in systabinfo.ti_serialv. Specifically: 

ti_serialv   -1507688642

on the other hand, my target user, who requested the report, asked me to run select max(serial column) on the table, I got 639772559.  I KNOW they're the same table because I am specifying the same partnum.

It appears that I have been laboring under an illusion since I started this project. Anyone know how to tap-dance?  Or at least explain this discrepancy?My discovery goes totally against the comments in sysmaster.sql.

Feeling like quite the fool here!

Jacob:

I have to apologize, when you alter a SERIAL to a BUGSERIAL (and presumably to SERIAL8) the sysptnhdr record for the partnum is updated by copying the serialv value to cur_bigserial, so you do not have to specifically alter the column type with an initializer!

As far as resetting to '1', witness:

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)

Luis:

No. The maximum SERIAL value is 2^31-1 (2147483647) not 2^32 (4294967296). It is signed not unsigned, it's just that the algorithm for assigning the next value is a bit strange.

So, when the serial value hits 2147483647 the next assigned value is not 2147483648 but 1. However, as Jacob has discovered, the value stored in the table's partition header page (aka TABLESPACE page) in sysmaster:sysptnhdr when the value wraps is the negative value -2147483647 (yea, not -2147483648 as one might expect). That value continues to have one added to it every time a new row is inserted into the table and so counting up towards zero. The actual current serial value is found by adding 2147483648 to the stored value in sysmaster:sysptnhdr.serialv. Once the serialv wraps again up to zero the next serial value assigned is, once again, one (yea, again, not zero). It is weird, but that's how it was coded long ago. The SERIAL8 and BIGSERIAL values work the same way except with 2^63-1 and 2^64.

Art

Isn't this just the case of Informix internally treating the serial values as unsigned integers ( in the case of SERIAL, values from 0 to 4294967295 ), but the clients that we use cannot "print" unsigned integers, so they just "print" then as signed integers and we see negative numbers?

More of "limitation" of what the Informix clients can "print"/represent ?

Best regards,

Luis Marques

Not so fast there, Jake!

I have discovered, to my great distress, that systabinfo.ti_serialv (and, of course, sysptnhdr.serialv) and NOTHING to to with the actual serial values. For example, for one particular table (based on partnum) I found a large negative number in systabinfo.ti_serialv. Specifically: 

ti_serialv   -1507688642

on the other hand, my target user, who requested the report, asked me to run select max(serial column) on the table, I got 639772559.  I KNOW they're the same table because I am specifying the same partnum.

It appears that I have been laboring under an illusion since I started this project. Anyone know how to tap-dance?  Or at least explain this discrepancy?My discovery goes totally against the comments in sysmaster.sql.

Feeling like quite the fool here!

Jacob:

I have to apologize, when you alter a SERIAL to a BUGSERIAL (and presumably to SERIAL8) the sysptnhdr record for the partnum is updated by copying the serialv value to cur_bigserial, so you do not have to specifically alter the column type with an initializer!

As far as resetting to '1', witness:

I dealt with this recently.  You are correct that you cannot change the serial to anything lower than the current value, but you can alter the table and set it higher to the maximum value and then let it wrap by inserting a dummy row and then deleting it.  That's as Art described, and it there is an article somewhere that describes this as the recommended action.  It will then start over at 1.  Interestingly, the serialv in sysptnhdr will then show a negative value.  To find the next value of the serial once it has wrapped, then you need to add 2147483647 to it.

Hello Art,

Unfortunately, this does not work as documented. An ALTER TABLE modify with 0 gives a syntax error. A number > 0 for modify does not give an error, but the value for serialv in the sysptnhdr is only changed if the value in the ALTER TABLE MODIFY is greater than that in the sysptnhdr. If it is smaller, the larger value in the sysptnhdr remains unchanged.

Andreas:

You are correct about the sysptnhdr serial type values do not reset when the table is altered, 

You can reset them to zero by using ALTER TABLE to reset the serial/serial8/bigserial column to its maximum value that will cause the next row (or is it the second next row - don't remember) to wrap to zero. 

Art

What Art said in his last post is correct. The 'Next Values' of the three serial types can be checked and retrieved in the sysmaster:sysptnhdr. However, you must be aware that changing a serial to a bigserial will not reset the value for the serial. This also applies to dropping the column. Even creating a new serial column does not reset the value. A check of this value will still report a high value, even if the table no longer has a serial value.
Since IBM does not want to acknowledge this unpleasant behavior as an error, there is a feature request for this. To make matters worse, setting a new base value for a serial only works if you want to set a value greater than the existing value as the new base value. Unfortunately, this does not help here either and here too only a feature request can help to achieve changes to the behavior. So please vote for my request:

https://ideas.ibm.com/ideas/INFX-I-658

Hi y'all.

This is a quick question.

I have determined that for a serial column, the coltype in syscolumns is an integer: 262. We have not loaded ak_utils here so I can't check the code to see what the coltype value is for  bigserial column.  At this time we are not yet using bigserial but I have a feeling we're gonna need to go that way before too long.

So: What is the coltype value for a bigserial?

(Uncharacteristically short question for me.  Who am I and what have I done with me?  ;-)