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

Hi Sebastien. I'm not sure you can: "dbschema" doesn't show it either. However, the current largest value can be determined with either: SELECT MAX( column-name ) FROM table-name ; SELECT colmax + 1 FROM systables AS t JOIN syscolumns AS c ON c.tabid = t.tabid WHERE t.tabname = ' table-name ' AND c.colname = ' column-name '; The second might require UPDATE STATISTICS beforehand. ------------------------------ Doug Lawry Oninit Consulting ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

Hello Mike, To clarify: I am writing a tool that extracts the definition of and Informix database, to get the original start values of serials, in order to produce a fresh new set of SQL statements to copy a database, WITHOUT the data. Similarly to SEQUENCE definitions, where you can specify a start value and get it from syssequences.start_val, it should be possible to get the starting SERIAL/BIGSERIAL. I will have a look at the "myschema" utility, but if this tool generates CREATE TABLE statement with serials starting at the current max+1, it's not useful. If Informix lacks the information of the starting value of serials, it should be accepted as a feature request. Seb ------------------------------ Sebastien FLAESCH ------------------------------

Okay, so to the best of my knowledge the original value for the serial is not stored anywhere. It's normally 1, and if you plan to follow up the creation of the empty database with a data load, then you would probably want to leave it unspecified (so that it will be set to the next highest value), or specify it as the next highest value. I am just questioning the usefulness of the ORIGINAL start value. Good luck with your script though. I checked, and myschema does have a "-I" (big "i") option to NOT include the next value of serial columns. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------