Truncate does not work on "Updatable (SDS)" for TE

Posted in 2009

Topics: High Availability & Replication, Server Administration, Platform-Specific Issues

Jeff Filippi — Tue, 24 Feb 2009 17:41:29 — source: IIUG Forums & Mailing Lists

Question, is the truncate command allowed on an "Updateable (SDS)" server for a TEMP table? When I run it the command executes but does not truncate the TEMP table. I could not find any documentation that says it cannot be ran on the "Updatable (SDS)" server. There are no error messages when it is ran on the "Updatable (SDS)" server. The same sql does truncate the TEMP table on the primary server. Environment: Informix 11.50FC3 HP-UX 11.23 Itanium Here is the test SQL that I ran in dbaccess. set isolation to committed read; begin work; create temp table tst ( col1 integer, col2 integer ) with no log ; insert into tst values (1,1); insert into tst values (2,1); insert into tst values (3,1); insert into tst values (4,1); insert into tst values (5,1); create index idx_tst on tst(col1); select * from tst where col1 = 3; select count(*) from tst; truncate table tst; commit work; select count(*) from tst; begin work; insert into tst values (1,1); insert into tst values (2,1); insert into tst values (3,1); insert into tst values (4,1); insert into tst values (5,1); select * from tst where col1 = 3; select count(*) from tst; truncate table tst; commit work; Thanks, Jeff

↪ replying to Jeff Filippi

Madison — Tue, 24 Feb 2009 18:04:23 — source: IIUG Forums & Mailing Lists

Updatable secondaries only support DML statements, not DDL. ------------------------------------- Madison Pruet, STSM IDS Replication Architect = "Jeff Filippi" = <iiug@itdataconsu = lting.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Truncate does not work on = 02/24/2009 11:41 "Updatable (SDS)" fo.... [15001= ] AM = = = Please respond to = ids@iiug.org = = = Question, is the truncate command allowed on an "Updateable (SDS)" serv= er for a TEMP table? When I run it the command executes but does not trunc= ate the TEMP table. I could not find any documentation that says it cannot be ran on the "Updatable (SDS)" server. There are no error messages when it is ran on the "Updatable (SDS)" ser= ver. The same sql does truncate the TEMP table on the primary server. Environment: Informix 11.50FC3 HP-UX 11.23 Itanium Here is the test SQL that I ran in dbaccess. set isolation to committed read; begin work; create temp table tst ( col1 integer, col2 integer ) with no log ; insert into tst values (1,1); insert into tst values (2,1); insert into tst values (3,1); insert into tst values (4,1); insert into tst values (5,1); create index idx_tst on tst(col1); select * from tst where col1 =3D 3; select count(*) from tst; truncate table tst; commit work; select count(*) from tst; begin work; insert into tst values (1,1); insert into tst values (2,1); insert into tst values (3,1); insert into tst values (4,1); insert into tst values (5,1); select * from tst where col1 =3D 3; select count(*) from tst; truncate table tst; commit work; Thanks, Jeff ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

↪ replying to Madison

Jeff Filippi — Tue, 24 Feb 2009 19:06:59 — source: IIUG Forums & Mailing Lists

Ok, they should change the documentation to show that the truncate command cannot be used on temp tables in an Updatable Secondary(should return an error when truncating temp tables), because you can do create table/drop table/create indexes for temp tables in an Updatable Secondary. Thanks, Jeff -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Madison Pruet Sent: Tuesday, February 24, 2009 12:04 PM To: ids@iiug.org Subject: Re: Truncate does not work on "Updatable (SDS).... [15002] Updatable secondaries only support DML statements, not DDL. ------------------------------------- Madison Pruet, STSM IDS Replication Architect = "Jeff Filippi" = <iiug@itdataconsu = lting.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Truncate does not work on = 02/24/2009 11:41 "Updatable (SDS)" fo.... [15001= ] AM = = = Please respond to = ids@iiug.org = = = Question, is the truncate command allowed on an "Updateable (SDS)" serv= er for a TEMP table? When I run it the command executes but does not trunc= ate the TEMP table. I could not find any documentation that says it cannot be ran on the "Updatable (SDS)" server. There are no error messages when it is ran on the "Updatable (SDS)" ser= ver. The same sql does truncate the TEMP table on the primary server. Environment: Informix 11.50FC3 HP-UX 11.23 Itanium Here is the test SQL that I ran in dbaccess. set isolation to committed read; begin work; create temp table tst ( col1 integer, col2 integer ) with no log ; insert into tst values (1,1); insert into tst values (2,1); insert into tst values (3,1); insert into tst values (4,1); insert into tst values (5,1); create index idx_tst on tst(col1); select * from tst where col1 =3D 3; select count(*) from tst; truncate table tst; commit work; select count(*) from tst; begin work; insert into tst values (1,1); insert into tst values (2,1); insert into tst values (3,1); insert into tst values (4,1); insert into tst values (5,1); select * from tst where col1 =3D 3; select count(*) from tst; truncate table tst; commit work; Thanks, Jeff ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. = **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Glossary

dbaccess