Informix

 View Only
  • 1.  Find start value of SERIAL/BIGSERIAL column

    Posted Tue August 30, 2022 10:58 AM
    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


  • 2.  RE: Find start value of SERIAL/BIGSERIAL column

    Posted Wed August 31, 2022 07:09 AM
    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
    ------------------------------



  • 3.  RE: Find start value of SERIAL/BIGSERIAL column

    IBM Champion
    Posted Wed August 31, 2022 08:28 AM
    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
    ------------------------------



  • 4.  RE: Find start value of SERIAL/BIGSERIAL column

    Posted Thu September 01, 2022 10:38 AM

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



  • 5.  RE: Find start value of SERIAL/BIGSERIAL column

    IBM Champion
    Posted Thu September 01, 2022 12:36 PM
    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
    ------------------------------



  • 6.  RE: Find start value of SERIAL/BIGSERIAL column

    Posted Mon September 05, 2022 05:06 AM
    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
    ------------------------------



  • 7.  RE: Find start value of SERIAL/BIGSERIAL column

    IBM Champion
    Posted Mon September 05, 2022 10:37 AM
    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
    ------------------------------