IDS Forum

RE: alter Table modify serial did not work

Posted By: John Miller iii
Date: Tuesday, 21 September 2010, at 2:08 p.m.

In Response To: RE: alter Table modify serial did not work (Everett Mills)

Here is a way of peaking at the serials columns current value.

select trim(dbsname)||'.'||trim(tabname) as tabname,
serialv as serial, cur_serial8 as serial8, cur_bigserial as big_serial
from sysptnhdr P, systabnames T
where P.partnum =3D T.partnum
and P.partnum =3D P.lockid

John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)

ids-bounces@iiug.org wrote on 09/21/2010 07:20:36 AM:

> [image removed]
>
> RE: alter Table modify serial did not work [21393]
>
> Everett Mills
>
> to:
>
> ids
>
> 09/21/2010 07:21 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> It's been that way since I started using serials about 10-12 years ag=
o.
Maybe
> you could log a feature request for something like this:
>
> TRUNCATE TABLE my_table WITH SERIAL RESET
>
> So that when a table is truncated the next serial values are reset to=
1.
It
> should be pretty easy to implement, if there's enough call for it. It=

would
> also be a way to provide compatibility with other databases, Squeal f=
or
one,
> that always reset serials when tables are truncated.
>
> --EEM
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf =
Of
> > Andre Koppel
> > Sent: Tuesday, September 21, 2010 9:00 AM
> > To: ids@iiug.org
> > Subject: Re: alter Table modify serial did not work [21391]
> >
> > Hi Art,
> > you are right, it works this way. But I have have some old code (20=
03
> > or
> > so) that has reset the serial value
> > after all of the rows in a table are deleted. I am absolutely sure =
that

> > the code has worked long time ago.
> > So something has changed within the past. For me it did not make se=
nse
> > that it is not alowed to reset a serial
> > even if the table is empty.
> > --
> >
> > Am 21.09.2010 15:42, schrieb Art Kagel:
> > > You cannot set the serial value down only up. To restart it from =
1
> > you
> > > have to set it to MAXINT (2147483647) then insert one row and del=
ete
> > it.
> > > The following row will start over at 1 assuming nothing else prev=
ents

> > that
> > > (like a unique index/constraint on the serial column and a '1'
> > already
> > > exists).
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > IIUG Board of Directors (art@iiug.org)
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions and
> > > do not reflect on my employer, Advanced DataTools, the IIUG, nor =
any
> > other
> > > organization with which I am associated either explicitly,
> > implicitly, or by
> > > inference. Neither do those opinions reflect those of other
> > individuals
> > > affiliated with any entity with which I am affiliated nor those o=
f
> > the
> > > entities themselves.
> > >
> > > On Tue, Sep 21, 2010 at 9:17 AM, Andre Koppel<akoppel@akso.de> wr=
ote:

> > >
> > >> Hi all,
> > >> I have a strange problem. Using IDS 11.5 the following command d=
id
> > not
> > >> work as expected:
> > >> alter table prj modify (snr serial(1));
> > >> The command was processed without any problem, but the next inse=
rt
> > did
> > >> not generate 1 (or even 2) for the next "snr", but it generates
> > >> 280091. I have completely deleted all the entries (rows) from th=
e
> > table,
> > >> so there should be no problem for informix starting with 1
> > >> for the next serial "snr". There is nothing special with snr (it=
s a
> > >> primary key and a serial, we often use a variable named snr)
> > >> Whats going wrong, I have not found any information, the modify =
...
> > >> serial is simply ignored in every case we hav tested.
> > >> Kind regards
> > >> Andre
> > >>
> > >> --
> > >> * Andre Koppel Software GmbH *
> > >> * Prinz-Handjery-Str. 38 *
> > >> * 14167 Berlin *
> > >> * Tel.: (+4930) 810 09 190 *
> > >> * Fax: (+4930) 326 01 046 *
> > >> * www.invep.de *
> > >> * www.akso.de *
> > >>
> > >> Eingetragen beim Amtsgericht
> > >> Berlin Charlottenburg HRB92600
> > >> Gesch=E4ftsf=FChrer Andre Koppel
> > >>
> > >>
> > >>
> > >>
> > >
> > *******************************************************************=
****

> > ********
> > >> Forum Note: Use "Reply" to post a response in the discussion for=
um.
> > >>
> > >>
> > > --0016e6509b5279a96c0490c53496
> > >
> > >
> > >
> > *******************************************************************=
****

> > ********
> > > Forum Note: Use "Reply" to post a response in the discussion foru=
m.
> > >
> >
> >
> > *******************************************************************=
****

> > ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.=

>
>
>
***********************************************************************=
********

> Forum Note: Use "Reply" to post a response in the discussion forum.=

>=

Messages In This Thread

• alter Table modify serial did not work
  Andre Koppel -- Tuesday, 21 September 2010, at 9:17 a.m.
  • Re: alter Table modify serial did not work
    Fernando Nunes -- Tuesday, 21 September 2010, at 9:26 a.m.
    • Re: alter Table modify serial did not work
      Art Kagel -- Tuesday, 21 September 2010, at 9:44 a.m.
      • Re: alter Table modify serial did not work
        Fernando Nunes -- Tuesday, 21 September 2010, at 9:56 a.m.
  • Re: alter Table modify serial did not work
    Art Kagel -- Tuesday, 21 September 2010, at 9:42 a.m.
    • Re: alter Table modify serial did not work
      Andre Koppel -- Tuesday, 21 September 2010, at 9:59 a.m.
      • Re: alter Table modify serial did not work
        Keith Simmons -- Tuesday, 21 September 2010, at 10:19 a.m.
      • RE: alter Table modify serial did not work
        Everett Mills -- Tuesday, 21 September 2010, at 10:20 a.m.
        • RE: alter Table modify serial did not work
          John Miller iii -- Tuesday, 21 September 2010, at 2:08 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.