Hi Seb,
Maybe I am not fully understanding what you need or are expecting.
If I create a new table with a serial and I bother to specify a starting value, eg: id serial(100), then as soon as I start inserting records into the table, then the starting value if of little use anymore. Say I add 10 records to the table, the current value will be 110. Are you trying to the the "100" or the "110"...or something else? I expect that the
original starting value is not stored anywhere, only the
current value.
If you do a dbexport and then dbimport, then loading the rows back into the table with the serial will automatically increment the serial value, so that the starting point isn't really necessary, as the next insert will continue the serial value where the import left off - it won't be reset to 1 for example.
The dbinfo command will only work right after the insert to the table has happened. The query against sysptnhdr can be run anytime to get the current value.
BTW, if you are writing a schema extract tool, then you should look at Art Kagel's "myschema" utility. It may already do what you need, and it the "create table" output does specify the serial starting values to the next value for the serial.
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com------------------------------
Original Message:
Sent: Thu September 01, 2022 10:38 AM
From: Sebastien FLAESCH
Subject: Find start value of SERIAL/BIGSERIAL column
Hi all,
Maybe I was not clear enough, but I am not asking for the last generated serial / bigserial value, I know how to get it with DBINFO()
dbinfo('sqlca.sqlerrd1')
or
dbinfo('bigserial')
In fact I am writing a database schema extraction tool, so I need the starting value of a serial or bigserial from the system tables.
I expected dbexport to grab this info from somewhere, but it seems not to care about serial starting values.
So my conclusion so far is that Informix is missing to store that information somewhere in a database schema definition.
Seb
------------------------------
Sebastien FLAESCH
Original Message:
Sent: Wed August 31, 2022 08:27 AM
From: Mike Walker
Subject: Find start value of SERIAL/BIGSERIAL column
You can run oncheck -pt <database>:<tablename> and this will show you the serial/serial8/bigserial values:
For example: oncheck -pt stores_demo:orders
.
Current serial value 1024
Current SERIAL8 value 1
Current BIGSERIAL value 1
.
But I normally query sysmaster:sysptnhdr to get this info:
select serialv, cur_serial8, cur_bigserial
from sysmaster:sysptnhdr h, sysmaster:systabnames n
where h.partnum = n.partnum
and n.dbsname = "stores_demo"
and n.tabname = "orders"
serialv cur_serial8 cur_bigserial
1024 1 1
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
Original Message:
Sent: Tue August 30, 2022 10:57 AM
From: Sebastien FLAESCH
Subject: Find start value of SERIAL/BIGSERIAL column
Hello!
Where can I find the start value of a column defined as SERIAL(start-value) or BIGSERIAL(start-value) in the Informix system tables?
I did not find this in the syscolumns...
Seb
------------------------------
Sebastien FLAESCH
------------------------------
#Informix