RE: importing ids 7.20.UC5 bkups to ids 9.40.UC3

Ditto ;-) -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Christopher Coleman Sent: 20 February 2006 08:03 PM To: shahram@jlabs.de; informix-list@iiug.org Subject: RE: importing ids 7.20.UC5 bkups to ids 9.40.UC3 Dirk suggested: > One option would be to create the tables in version 9, but to create >them as raw tables, not standard (you will have to edit the .sql files >to add the word raw). This is good advice, but you have to do more than add "RAW" to the CREATE TABLE statement. A raw table cannot have indexes, referential constraints, or triggers. Therefore, you may have to do some significant editing of the XXX.sql file before you do the load, and may have to create a second sql file with alters, If, for example, you have a foo.sql file that looks like this: CREATE TABLE foo ( serial_col SERIAL PRIMARY KEY CONSTRAINT foo_pk, char_col CHAR NOT NULL CONSTRAINT char_col_nn, int_col INTEGER, string_col VARCHAR(255,13) DEFAULT 'I Love c.d.i!', CHECK (int_col > 0) CONSTRAINT int_col_positive ) EXTENT SIZE 248 NEXT SIZE 248 LOCK MODE ROW ; CREATE INDEX foo_comp_idx ON foo(char_col, int_col); You would want to modify it to look like this: CREATE RAW TABLE foo ( serial_col SERIAL ,-- PRIMARY KEY CONSTRAINT foo_pk, char_col CHAR NOT NULL CONSTRAINT char_col_nn, int_col INTEGER, string_col VARCHAR(255,13) DEFAULT 'I Love c.d.i!', CHECK (int_col > 0) CONSTRAINT int_col_positive ) EXTENT SIZE 248 NEXT SIZE 248 LOCK MODE ROW ; Note that constraints which do not require an index are acceptable, as are storage options, default values, etc. Then do the load, then run a second sql file which would look like this: ALTER TABLE foo TYPE (STANDARD); ALTER TABLE foo ADD CONSTRAINT PRIMARY KEY (serial_col) CONSTRAINT foo_pk; CREATE INDEX foo_comp_idx ON foo(char_col, int_col); While raw tables can significantly improve your load time, you may find that modifying all your files, especially if you have a large number, is not worth it. Also, when you turn logging back on for your tables, you really need to do a level 0 backup. Another option to consider is the High Performance Loader (HPL), which is much easier to use in 9.4 than in previous releases. It can be used, depending on your data and requirements, to bypass the logging without having to alter the schema files. Documentation on using HPL can be found at: http://publib.boulder.ibm.com/epubs/pdf/ct1t3na.pdf You can find more info RAW tables by searching the history of this group: http://groups.google.com/group/comp.databases.informix/search?group=comp .databases.informix&q=raw+table&qt_g=1 Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list > The information on this e-mail including any attachments relates to the official business of DigiCare (Pty) Ltd. The information is confidential and legally privileged and is intended solely for the addressee. Access to this e-mail by anyone else is unauthorised and as such any disclosure, copying, distribution or any action taken or omitted in reliance on it is unlawful. Please notify the sender immediately if it has inadvertently reached you and do not read, disclose or use the content in any way. > No responsibility whatsoever is accepted by DigiCare (Pty) Ltd if the information is, for whatever reason, corrupted or does not reach its intended destination. The views expressed in this e-mail are the views of the individual sender and should in no way be construed as the views of DigiCare (Pty) Ltd, except where the sender has specifically stated them to be the views of DigiCare (Pty) Ltd. >