Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Copy Table Schema ?

261 views
Skip to first unread message

david...@gmail.com

unread,
Mar 2, 2010, 9:09:45 AM3/2/10
to
Hello,

I am running IDS 11.50.FC4 and have a monthly process that requires
copying the structure of a table to a new table. Is there any way to
copy a table structure in Informix via script without using
dbschema ? Also the original table has millions of records and is not
fragmented so I would not want to fragment the table and then detach a
fragment as it would take too long.

Thank You,

Dave

Obnoxio The Clown

unread,
Mar 2, 2010, 9:12:27 AM3/2/10
to IIUG Informix List

I don't understand: what is wrong with dbschema?

--
Cheers,
Obnoxio The Clown

http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

Informix DBA

unread,
Mar 2, 2010, 9:20:33 AM3/2/10
to Obnoxio The Clown, IIUG Informix List
There is nothing wrong with dbschema - just requires a little bit of parsing. 
 
I was wondering if there was any built in database SQL command.  I know there is the create table SAMEAS clause however that only applies to external tables and I know there is CREATE DUPLICATE statement to create a duplicate copy of an existing table though that is only for XPS. 
 
Anything for IDS?
 
 
--Dave

On Tue, Mar 2, 2010 at 9:12 AM, Obnoxio The Clown <obn...@serendipita.com> wrote:
I don't understand: what is wrong with dbschema?

--
Cheers,
Obnoxio The Clown

http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Art Kagel

unread,
Mar 2, 2010, 9:30:41 AM3/2/10
to Informix DBA, IIUG Informix List
No.  Nothing.  You could probably take the myschema source from my utils2_ak package and cobble something up that adds a feature to get a table's structure and output a different name.  You'd have to figure out what to do about indexes and other objects like constraints and triggers or to just shortcut out of creating them.  Not a feature I'd want to put in for general use though.  Too many different ways to want it done.  All of the mainline table name code is in the main file myschema.ec.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

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 of the entities themselves.



On Tue, Mar 2, 2010 at 9:20 AM, Informix DBA <in4m...@gmail.com> wrote:
There is nothing wrong with dbschema - just requires a little bit of parsing. 
 
I was wondering if there was any built in database SQL command.  I know there is the create table SAMEAS clause however that only applies to external tables and I know there is CREATE DUPLICATE statement to create a duplicate copy of an existing table though that is only for XPS. 
 
Anything for IDS?
 
 
--Dave

On Tue, Mar 2, 2010 at 9:12 AM, Obnoxio The Clown <obn...@serendipita.com> wrote:
I don't understand: what is wrong with dbschema?

--
Cheers,
Obnoxio The Clown

http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Informix DBA

unread,
Mar 2, 2010, 9:49:46 AM3/2/10
to Art Kagel, IIUG Informix List
Thank you.  I will take a look at your myschema scripts.  I have had a few occassions where having a built in feature to at least copy a table structure (minus indexes, constraints, triggers etc.. ) would really be useful.

On Tue, Mar 2, 2010 at 9:30 AM, Art Kagel <art....@gmail.com> wrote:
No.  Nothing.  You could probably take the myschema source from my utils2_ak package and cobble something up that adds a feature to get a table's structure and output a different name.  You'd have to figure out what to do about indexes and other objects like constraints and triggers or to just shortcut out of creating them.  Not a feature I'd want to put in for general use though.  Too many different ways to want it done.  All of the mainline table name code is in the main file myschema.ec.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

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 of the entities themselves.
On Tue, Mar 2, 2010 at 9:20 AM, Informix DBA <in4m...@gmail.com> wrote:
There is nothing wrong with dbschema - just requires a little bit of parsing. 
 
I was wondering if there was any built in database SQL command.  I know there is the create table SAMEAS clause however that only applies to external tables and I know there is CREATE DUPLICATE statement to create a duplicate copy of an existing table though that is only for XPS. 
 
Anything for IDS?
 
 
--Dave

On Tue, Mar 2, 2010 at 9:12 AM, Obnoxio The Clown <obn...@serendipita.com> wrote:
I don't understand: what is wrong with dbschema?

--
Cheers,
Obnoxio The Clown

http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Ian Michael Gumby

unread,
Mar 2, 2010, 9:57:08 AM3/2/10
to in4m...@gmail.com, art....@gmail.com, inform...@iiug.org

You should be able to do this as a stored procedure.
Essentially you look up in systables the table name, get the columns and their types and then create a copy...

Ok, so this has to either be a java stored procedure or an esql/c stored procedure, but shouldn't be too terribly difficult.


Date: Tue, 2 Mar 2010 09:49:46 -0500
Subject: Re: Copy Table Schema ?
From: in4m...@gmail.com
To: art....@gmail.com
CC: inform...@iiug.org


Thank you.  I will take a look at your myschema scripts.  I have had a few occassions where having a built in feature to at least copy a table structure (minus indexes, constraints, triggers etc.. ) would really be useful.

On Tue, Mar 2, 2010 at 9:30 AM, Art Kagel <art....@gmail.com> wrote:
No.  Nothing.  You could probably take the myschema source from my utils2_ak package and cobble something up that adds a feature to get a table's structure and output a different name.  You'd have to figure out what to do about indexes and other objects like constraints and triggers or to just shortcut out of creating them.  Not a feature I'd want to put in for general use though.  Too many different ways to want it done.  All of the mainline table name code is in the main file myschema.ec.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

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 of the entities themselves.
On Tue, Mar 2, 2010 at 9:20 AM, Informix DBA <in4m...@gmail.com> wrote:
There is nothing wrong with dbschema - just requires a little bit of parsing. 
 
I was wondering if there was any built in database SQL command.  I know there is the create table SAMEAS clause however that only applies to external tables and I know there is CREATE DUPLICATE statement to create a duplicate copy of an existing table though that is only for XPS. 
 
Anything for IDS?
 
 
--Dave

On Tue, Mar 2, 2010 at 9:12 AM, Obnoxio The Clown <obn...@serendipita.com> wrote:
I don't understand: what is wrong with dbschema?

--
Cheers,
Obnoxio The Clown

http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list





Hotmail: Free, trusted and rich email service. Get it now.

Superboer

unread,
Mar 3, 2010, 11:15:07 AM3/3/10
to
you could try:

create row type mybasetable( a int, b char(10), c char(20));
create table newtable of type mybasetable;
alter table newtable drop type ;

Superboer

On 2 mrt, 15:57, Ian Michael Gumby <im_gu...@hotmail.com> wrote:
> You should be able to do this as a stored procedure.
> Essentially you look up in systables the table name, get the columns and their types and then create a copy...
>
> Ok, so this has to either be a java stored procedure or an esql/c stored procedure, but shouldn't be too terribly difficult.
>

> Date: Tue, 2 Mar 2010 09:49:46 -0500
> Subject: Re: Copy Table Schema ?

> From: in4mix...@gmail.com
> To: art.ka...@gmail.com
> CC: informix-l...@iiug.org


>
> Thank you.  I will take a look at your myschema scripts.  I have had a few occassions where having a built in feature to at least copy a table structure (minus indexes, constraints, triggers etc.. ) would really be useful.
>

> On Tue, Mar 2, 2010 at 9:30 AM, Art Kagel <art.ka...@gmail.com> wrote:
>
> No.  Nothing.  You could probably take the myschema source from my utils2_ak package and cobble something up that adds a feature to get a table's structure and output a different name.  You'd have to figure out what to do about indexes and other objects like constraints and triggers or to just shortcut out of creating them.  Not a feature I'd want to put in for general use though.  Too many different ways to want it done.  All of the mainline table name code is in the main file myschema.ec.
>
> Art
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (a...@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KSwww.iiug.org/conf
>
> 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 of the entities themselves.
>

> On Tue, Mar 2, 2010 at 9:20 AM, Informix DBA <in4mix...@gmail.com> wrote:
>
> There is nothing wrong with dbschema - just requires a little bit of parsing.  
>
> I was wondering if there was any built in database SQL command.  I know there is the create table SAMEAS clause however that only applies to external tables and I know there is CREATE DUPLICATE statement to create a duplicate copy of an existing table though that is only for XPS.  
>
> Anything for IDS?
>
> --Dave
>

> On Tue, Mar 2, 2010 at 9:12 AM, Obnoxio The Clown <obno...@serendipita.com> wrote:


>
> davidan...@gmail.com wrote:
> > Hello,
>
> > I am running IDS 11.50.FC4 and have a monthly process that requires
> > copying the structure of a table to a new table.  Is there any way to
> > copy a table structure in Informix via script without using
> > dbschema ?  Also the original table has millions of records and is not
> > fragmented so I would not want to fragment the table and then detach a
> > fragment as it would take too long.
>
> I don't understand: what is wrong with dbschema?
>
> --
> Cheers,
> Obnoxio The Clown
>
> http://obotheclown.blogspot.com
>
> I will now proceed to pleasure myself with this fish.
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
>
> believed to be clean.
>
> _______________________________________________
> Informix-list mailing list

> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list
>
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list
>
> _________________________________________________________________
> Hotmail: Free, trusted and rich email service.http://clk.atdmt.com/GBL/go/201469228/direct/01/

0 new messages