Require Info about Create Table

Hello, I have 2 databases( db1 & db2 )in same instance. db1 has a table "archive" with 10000 rows, i want to create a table in db2 as a exact replica of table "archive" from db1 with all the 10000 rows. What syntax should i use ? Pls guide ! Thanking you all, vikas.

I suppose that this will depend on how often you need to copy the data across Select the database Create the table (import the table schema using dbschema -d db1 -t archive -ss) Edit the schema for things like dbspaces and fragmentation strategies Load the data E.g. Database db2; Create table archive ( ); insert into archive Select * from db1:archive; -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of VIKAS HIVARKAR Sent: 10 April 2008 02:47 PM To: ids@iiug.org Subject: Require Info about Create Table [11821] Hello, I have 2 databases( db1 & db2 )in same instance. db1 has a table "archive" with 10000 rows, i want to create a table in db2 as a exact replica of table "archive" from db1 with all the 10000 rows. What syntax should i use ? Pls guide ! Thanking you all, vikas. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

VIKAS HIVARKAR wrote: > Hello, > > I have 2 databases( db1 & db2 )in same instance. > > db1 has a table "archive" with 10000 rows, i want to create a table in db2 as > a exact replica of table "archive" from db1 with all the 10000 rows. > > What syntax should i use ? > First, note that you can create a synonym in the second database that will connect the table in db1 to db2 directly. Now, assuming that you still want a copy and not access to the original table, use the dbschema utility to get the tables DDL: dbschema -d db1 -t archive -ss >db1_archive.sqlYou may want to edit the file to change the tables location if it was specifically placed in a particular dbspace and not in the db1 database's home dbspace. Otherwise just run that script while attached to db2 and voila! you'll have an exact duplicate of the table's structure in db2. Now for the data, you have many options: * Copy the data using pure SQL: o INSERT INTO db2:archive SELECT * FROM db1:archive; * Export the data to disk and reimport it into db2: o DATABASE db1; o UNLOAD TO 'archive.unl' SELECT * FROM archive; o DATABASE db2; o LOAD FROM 'archive.unl' INSERT INTO ARCHIVE; Both of these methods have a few problems. First, they will require a very large number of locks (10000 * (1 + #indexes)) unless you lock the table before loading. Second, if you do not have enough logical log space to hold the entire transaction and all other update activity on the server during the duration of the transaction, you will get a long transaction error and the whole thing will roll back. Third, if db1 and db2 have different logging statuses (ie one is logged and the other is not) you will not be able to use the direct copy option as you cannot run distributed queries between databases with differing logging statuses. There are two solutions: * Instead of using the dbaccess LOAD FROM verb to reload the extracted data, you can use the dbload utilility which will make partial commits every <N> rows eliminating both problems. See the Migration Guide Informix manual for details on setting up a dbload command file. It's not hard. * Get my dbcopy utility which will copy the data directly from one table to the other with partial commits. Dbcopy uses separate connections to the two databases (you must use a non-shared memory connection for at least one of those connections - dbcopy -? for details) so it avoids the third problem also. Dbcopy tends to be faster than a distributed SQL copy. Dbcopy is included in the package utils2_ak which you can download from the IIUG Software Repository or from the Oninit web site. Art S. Kagel Oninit > Pls guide ! > > Thanking you all, > > vikas. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > > >

The synonym idea is great Mr Kagel ! I never got a real chance to work with synonyms .Pls tell me how does it works. This is what i did. create synonym "informix".archive for db1:"informix".archive; and got a error saying that the db2 is not in logging mode so i changed its loggin mode to Buffered as that of db1 & again executed the above create statement . the synonym was created and select count(*) from archive on db2 showed 10000 rows. but when i did dbschema -d db2 -t archive there was no schema in the output. correct me if i am wrong on this . synonym created only points to the real table on db1 and no physical table is created on db2. Now if this is the case then please tell me what will happen if there are DML operations performed on the actuall archive table in db1 . Does any changed done to archive table on db1 will be seen on its synonym ie archive table on db2.if yes then its great for my cause. Am i correct on this ,if not please expalin me how does synonyms work . Also what if the 2 databases are on different machines can we still use these sysnonym option ( considering the fact that these two databases have entry of each other in sqlhosts and /etc/services).

VIKAS HIVARKAR wrote: > The synonym idea is great Mr Kagel ! > > I never got a real chance to work with synonyms .Pls tell me how does it > works. > > This is what i did. > create synonym "informix".archive for db1:"informix".archive; > and got a error saying that the db2 is not in logging mode so i changed its > Correct, that goes back to my point that you cannot perform distributed queries between databases with different logging modes. > loggin mode to Buffered as that of db1 & again executed the above create > statement . > the synonym was created and select count(*) from archive on db2 showed 10000 > rows. > but when i did dbschema -d db2 -t archive there was no schema in the output. > Because archive is not a table in db2 it is only a synonym for a remote table. If you run dbschema -d db2 on the whole database, synonyms are printed out, just not when you run it at the table level. > correct me if i am wrong on this . synonym created only points to the real > table on db1 and no physical table is created on db2. > Correct. It is just an entry in the syssynonyms table. > Now if this is the case then please tell me what will happen if there are DML > operations performed on the actuall archive table in db1 . > Does any changed done to archive table on db1 will be seen on its synonym ie > archive table on db2.if yes then its great for my cause. > > Am i correct on this ,if not please expalin me how does synonyms work . > Yes, users in db2 who query db2:archive - the synonym - will immediately see any changes in db1:archive - the real table. > Also what if the 2 databases are on different machines can we still use these > sysnonym option ( considering the fact that these two databases have entry of > each other in sqlhosts and /etc/services). > > You can define a synonym on a table in a database on a remote instance. No problem . The syntax is almost identical, you just have to specify the servername which you do not have to do if db1 & db2 are colocated on the same instance: create synonym "informix".archive for db1@some_instance_name:"informix".archive; Art S. Kagel Oninit