We have 2 Java applications and some Esql/c scripts inserting/updating
data into Informix 9.30 Dynamic server tables simultaneously. At
present the Primary keys in these tables are of Informix Serial data
type. Now we are planning to change Serial data type to Integer data
type. May I know the possible (options/changes/any issues which may
occur) which need to be done for achieving the same?

Thanks in advance
Sarat
saratr@gmail.com

On 6 Jan 2006 03:51:11 -0800, Sarat <saratr@gmail.com> wrote:
> We have 2 Java applications and some Esql/c scripts inserting/updating
> data into Informix 9.30 Dynamic server tables simultaneously. At
> present the Primary keys in these tables are of Informix Serial data
> type. Now we are planning to change Serial data type to Integer data
> type. May I know the possible (options/changes/any issues which may
> occur) which need to be done for achieving the same?

The only issue of note that I can think oof is that every application
that currently inserts zero to generate the new serial number will
have to be revised to determine the actual number to insert. Further,
I'd probably want to put a 'dummy' row with ID of zero into the table
(along with a unique index) to ensure that those inadvertantly
overlooked applications all fail.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

reposting: Earlier one did not go thru.Jonathan Leffler wrote:
> The only issue of note that I can think oof is that every application
> that currently inserts zero to generate the new serial number will
> have to be revised to determine the actual number to insert.

this can be avoided by using triggers and stored procedures.

create a new table with a serial column.

create an insert trigger on the table with the following clause

referencing new as n
for each row (execute procedure sp_getserial(n.serial_column) into
serial_column) ;

create procedure sp_getserial(p_in integer)
if (p_in <> 0 ) then
return p_in ;
end if ;
insert into new_table_with_serial values(0);
return dbinfo('sqlca.sqlerrd1') ;
end procedure

all existing applications which inserts 0 to the serial field can work
as it is.

> Further,
> I'd probably want to put a 'dummy' row with ID of zero into the table
> (along with a unique index) to ensure that those inadvertantly
> overlooked applications all fail.

Sarat wrote:

> We have 2 Java applications and some Esql/c scripts inserting/updating
> data into Informix 9.30 Dynamic server tables simultaneously. At
> present the Primary keys in these tables are of Informix Serial data
> type. Now we are planning to change Serial data type to Integer data
> type. May I know the possible (options/changes/any issues which may
> occur) which need to be done for achieving the same?
>
> Thanks in advance
> Sarat
> saratr@gmail.com
>

You sure you really want to do this, you are going to have to manage the
key yourself. That aside, the alter table will do want you want

Paul Watson

In message <1136548271.098176.319600@z14g2000cwz.googlegroups .com>,
Sarat <saratr@gmail.com> writes
>We have 2 Java applications and some Esql/c scripts inserting/updating
>data into Informix 9.30 Dynamic server tables simultaneously. At
>present the Primary keys in these tables are of Informix Serial data
>type. Now we are planning to change Serial data type to Integer data
>type. May I know the possible (options/changes/any issues which may
>occur) which need to be done for achieving the same?

Why do this? I can see you might want to change the primary key (though
the Informixes I have worked with don't care what it is, so long as
there is *a* key that helps the operation), but I cannot see the
advantage in getting rid of the serial column. I have seen a number of
cases where there is reasonably more than one unique key on a table.

--
Surfer!
Email to: ramwater at uk2 dot net

I am assuming that the new data type will be integer which is PKY or
unique
index.

The biggest change you have to do is to change the insert from 0 to a
unique
value. However this can be avoided by using triggers and stored
procedure.

create a new table as follows

create table serial_table
( serial_com serial not null
) ;

create an insert trigger on your table with this clause
for each row (execute sp_getser() into serial_col)

create procedure sp_getser(p_in integer)
if ( p_in <> 0 ) then
return p_in ;
end if ;
insert into serial_table values(0) ;
return (dbinfo('sqlca.sqlerrd1');
end procedure

what will happen is that the table will get 0 momentarily before it is
converted
into a unique value by the trigger. At the end it will have the same
effect as
a serial field, without declaring the field a serial and more
importantly no
change is required in the code.

Note: I just wrote the pseudo code. I did not test the syntax. I tested
it a few yrs
back on 9.21 and it worked fine.

Sarat wrote:
> We have 2 Java applications and some Esql/c scripts inserting/updating
> data into Informix 9.30 Dynamic server tables simultaneously. At
> present the Primary keys in these tables are of Informix Serial data
> type. Now we are planning to change Serial data type to Integer data
> type. May I know the possible (options/changes/any issues which may
> occur) which need to be done for achieving the same?
>
> Thanks in advance
> Sarat
> saratr@gmail.com

Surfer! wrote:
> In message <1136548271.098176.319600@z14g2000cwz.googlegroups .com>,
> Sarat <saratr@gmail.com> writes
>
>> We have 2 Java applications and some Esql/c scripts inserting/updating
>> data into Informix 9.30 Dynamic server tables simultaneously. At
>> present the Primary keys in these tables are of Informix Serial data
>> type. Now we are planning to change Serial data type to Integer data
>> type. May I know the possible (options/changes/any issues which may
>> occur) which need to be done for achieving the same?
>
>
> Why do this? I can see you might want to change the primary key (though
> the Informixes I have worked with don't care what it is, so long as
> there is *a* key that helps the operation), but I cannot see the
> advantage in getting rid of the serial column. I have seen a number of
> cases where there is reasonably more than one unique key on a table.
>

Well, there's nothing wrong with a table having more than one candidate key.

A serial column is a surrogate key - it tells you nothing about your
data except the order you inserted it. They are, of course, highly
efficient to index. And, apart from the way they treat zero, equivalent
to an integer in any case. I'd leave well alone.

Jonathan Leffler wrote:
> The only issue of note that I can think oof is that every application
> that currently inserts zero to generate the new serial number will
> have to be revised to determine the actual number to insert.

this can be avoided by using triggers and stored procedures.

create a new table with a serial column.

create an insert trigger on the table with the following clause

referencing new as n
for each row (execute procedure sp_getserial(n.serial_column) into
serial_column) ;

create procedure sp_getserial(p_in integer)
if (p_in <> 0 ) then
return p_in ;
end if ;
insert into new_table_with_serial values(0);
return dbinfo('sqlca.sqlerrd1') ;
end procedure

all existing applications which inserts 0 to the serial field can work
as it
is.

> Further,
> I'd probably want to put a 'dummy' row with ID of zero into the table
> (along with a unique index) to ensure that those inadvertantly
> overlooked applications all fail.

In message <1136648493.882571.16970@g47g2000cwa.googlegroups. com>,
rkusenet@yahoo.com writes
<snip>

>. At the end it will have the same
>effect as
>a serial field, without declaring the field a serial
<Snip>

So why do it? Why not stick to leaving the column as a serial
generating it's own keys?

--
Surfer!
Email to: ramwater at uk2 dot net

"Surfer!" <surfer@127.0.0.1> wrote in message newsybPZXbWHiwDFw1p@nevis-view.co.uk...
> In message <1136648493.882571.16970@g47g2000cwa.googlegroups. com>, rkusenet@yahoo.com writes
> <snip>
>
>>. At the end it will have the same
>>effect as
>>a serial field, without declaring the field a serial
> <Snip>
>
> So why do it? Why not stick to leaving the column as a serial generating it's own keys?

because you can not update a serial col, unlike integer col.
The one time I did it, we had a requirement wherein we had
to update the serial fld temporarily. We set it back once we
corrected the business requirement.