CREATE TABLE & INDEX in one transaction
Hi,
I want to create table and its index in one transaction.
That is because if CREATE INDEX failed, I can rollback
the CREATE TABLE.
I used the following script in IDS7.30,
begin work;
create table tblname (....);
create index xtblname on tblname (...); commit work;
I always got an #206 error. I do not know if I have to
commit the "CREATE TABLE mytable" before
I can create index on "mytable".
Thanks a lot.
Harry Sheng wrote:
>
> Hi,
>
> I want to create table and its index in one transaction.
> That is because if CREATE INDEX failed, I can rollback
> the CREATE TABLE.
>
> I used the following script in IDS7.30,
>
> begin work;
> create table tblname (....);
> create index xtblname on tblname (...);> commit work;
>
> I always got an #206 error. I do not know if I have to
> commit the "CREATE TABLE mytable" before
> I can create index on "mytable".
Yes you do since the table mytable does not actually exist yet if you
do not commit its creation.
Art S. Kagel
Harry Sheng wrote:
> Hi,
>
> I want to create table and its index in one transaction.
> That is because if CREATE INDEX failed, I can rollback
> the CREATE TABLE.
>
> I used the following script in IDS7.30,
>
> begin work;
> create table tblname (....);
> create index xtblname on tblname (...);> commit work;
>
> I always got an #206 error. I do not know if I have to
> commit the "CREATE TABLE mytable" before
> I can create index on "mytable".
>
> Thanks a lot.
You can not create an index on a table which does not exit, so you have
to commit the "CREATE TABLE" before.
In a transactional mode (database) all transactional sql commands should
be committed before they take effect. While you may use just one commit
work for many transactions such as Insert Delete Update, in other cases
you can not. Your example above is one of these cases.
--
Compliments of QueriX
--------------------------------------------------------------------------------------------------
QueriX 4GL Compilers are Informix 4GL Compatible. Some features are:
True Windows GUI Clients.
HTML Report Generation.
More rigorous error handling.
Connection to all versions of Informix 4GL with no need to change
compiler.
Connection to other RDBMS such as Oracle.
For more details visit: http://www.querix.com/
---------------------------------------------------------------------------------------------------
That's odd. Here's some code which worked perfectly for me.
create database test with log;begin work;
create table test (
prkey integer,
prdata char(6000)
);
create index i1_test on test (prkey);
load from seed.dat
insert into test; -- 2 rows inserted
create table test_update (
prkey integer,
prdata char(6000)
);
load from seed.dat
insert into test_update;
delete from test_update
where prkey = 2; -- Row # 2 deleted.
-- Makes test_update contain 'different' data from test
-- for the update test
update test_update
set prdata = '-' || prdata
where 1=1;
rollback work;
All commands succeeded. At the end, database test existed with no user tables in it.
Rudy
Mehdi wrote:
> You can not create an index on a table which does not exit, so you have
> to commit the "CREATE TABLE" before.
>
> In a transactional mode (database) all transactional sql commands should
> be committed before they take effect. While you may use just one commit
> work for many transactions such as Insert Delete Update, in other cases
> you can not. Your example above is one of these cases.
>
Well done, Mehdi . . . .
John Carlson
Informix DBA
WHSmith USA
Mehdi wrote:
>
> Harry Sheng wrote:
>
> > Hi,
> >
> > I want to create table and its index in one transaction.
> > That is because if CREATE INDEX failed, I can rollback
> > the CREATE TABLE.
> >
> > I used the following script in IDS7.30,
> >
> > begin work;
> > create table tblname (....);
> > create index xtblname on tblname (...);> > commit work;
> >
> > I always got an #206 error. I do not know if I have to
> > commit the "CREATE TABLE mytable" before
> > I can create index on "mytable".
> >
> > Thanks a lot.
>
> You can not create an index on a table which does not exit, so you have
> to commit the "CREATE TABLE" before.
>
> In a transactional mode (database) all transactional sql commands should
> be committed before they take effect. While you may use just one commit
> work for many transactions such as Insert Delete Update, in other cases
> you can not. Your example above is one of these cases.
>
> --
> Compliments of QueriX
> --------------------------------------------------------------------------------------------------
>
> QueriX 4GL Compilers are Informix 4GL Compatible. Some features are:
>
> True Windows GUI Clients.
> HTML Report Generation.
> More rigorous error handling.
> Connection to all versions of Informix 4GL with no need to change
> compiler.
> Connection to other RDBMS such as Oracle.
> For more details visit: http://www.querix.com/
> ---------------------------------------------------------------------------------------------------