dbexport alternative

Hi, I need to clone informix databases with some frequence, I know there is dbexport but it implies to have no connections. I would like to have some way to do it without downtime. I have tryed to clone with some old scripts I have, but I facing one problem with referencing constraints that give error on creation. output to "dbs_table" without headings select "dbschema –q –d <database name> -t " || tabname || ";" from systables where tabid > 99 ********************************* dbs_table > createtable.sql ******************************** output to "unload.sql" without headings select "unload to " || tabname || " select * from " || tabname || ";" from systables where tabid > 99 and tabtype = "T" ******************************** output to "load.sql" without headings select "load from " || tabname || " insert into " || tabname || ";" from systables where tabid > 99 and tabtype = "T" ******************************** dbaccess database create newdatabase dbaccess newdatabase createtable.sql ******************************************************** This return error on referencing constraints, there is any other option to clone one database on the same server with minimal or inexistant downtime? Thanks for any help, SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------ #Informix

If the data is being changed while you are unloading tables, then you are going to end up with inconsistent results. You would need to lock the database exclusively while unloading all the data. dbexport prevents access to the database while it runs so that referential integrity is preserved. Take a look at Art Kagel's myexport utility, which allows the data to be unloaded while it is still accessible to users. ------------------------------ Mike Walker ------------------------------

Sergio: Go to my web site ( My Utilities ASK Database Management remove preview My Utilities Included: myexport - Drop in replacement for dbexport dbping.ec - Tests connections and reports connection time as well as the actual host and servername connected as well as which alias was used for the connection. These reports are important for testing DBPATH, connection groups, and Connection Manager behavior as well as verifying server failover. View this on ASK Database Management > ) and download both the latest release of my utils2_ak package and the myexport package. The latter is a replacement for dbexport and dbimport that uses myschema from utils2_ak and external tables to perform the export. Myexport does not lock the database nor the tables but for busy servers it offers several ways to minimize getting inconsistent data including exporting the tables in reverse dependency order and loading the data (using myimport) with filtering enabled. For your purposes, I would use the -E option to export and import using external tables which is MUCH faster than dbexport/dbimport and if you use foreign keys include the -O option to export in reverse dependency order. If your use the -m or -M options loads will be faster because indexes etc. are created after the data load by myimport. If you do not use -m or -M then the export can be imported using myimport or even dbimport as the files are completely compatible. (If -m/-M is used the files are still compatible with dbimport but you will have to manually execute the schema files that create the indexes etc.) Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Sergio, play with : SET ENVIRONMENT NOVALIDATE ON ;at the beginning of your data-load session. And if you are afraid to run in inconsistency make a kind of compare row check afterward. Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 ------------------------------

If you have space, time, resources - clone your server (e.g. ontape -s -L 0 -F -t STDIO | ssh other_server ontape -r -t STDIO) and then do a dbexport on cloned instance. HTH Hrvoje ------------------------------ Hrvoje Zokovic ------------------------------

Henri: First Sergio is using dbimport, so no opportunity to set NOVALIDATE. There is also a problem with just loading the inconsistent data with NOVALIDATE set in the session. You end up with inconsistent data in your database if it would not load without that setting. The purpose of NOVALIDATE isn't to load up bad data but rather to speed up a load of known GOOD data. Exporting the database with myexport -O (with the latest versions of myexport and myschema) and reloading with myimport are really the only way to get consistent data without locking the database. That's why I bother to maintain and improve them! Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Art, Sergio likes not to use an exclusive lock with dbexport. This means he is searching for other solutions. Many alternatives but easy workarounds, too. And I agree, your my* tools is a very good solution - but it depends on some parameters, too (e.g. database size...). Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 ------------------------------

I agree with everyone's comments. The only 100% solution is to export from a restored copy of the production instance. If that's not possible, you can't use "dbexport" as it would lock the source database. There are many alternatives to that, but my preference is to script around Art's "dbcopy" tool so that nothing needs writing to file systems. Foreign keys can be recreated with NOVALIDATE which is often fine for dev/test systems. If you need to check for any inconsistencies, the following SQL generator is due to go into a technical articles soon, but I have a backlog! Suppress the DELETE statements if you want to check only. DROP PROCEDURE IF EXISTS sp_revalidate; CREATE PROCEDURE sp_revalidate() RETURNING LVARCHAR AS sql;/* Generate SQL to check FOREIGN KEYS added with NOVALIDATE Doug Lawry, March 2021 Usage in "dbaccess": UNLOAD TO 'revalidate.sql' DELIMITER ';' SELECT * FROM TABLE (FUNCTION sp_revalidate());*/ DEFINE l_constrname VARCHAR(128); -- foreign key constraint name DEFINE l_dtabname VARCHAR(128); -- detail table name DEFINE l_ptabname VARCHAR(128); -- parent table name DEFINE l_dtabid LIKE systables.tabid; -- detail table ID DEFINE l_ptabid LIKE systables.tabid; -- parent table ID DEFINE l_dindexkeys LIKE sysindices.indexkeys; -- detail index column numbers DEFINE l_pindexkeys LIKE sysindices.indexkeys; -- parent index column numbers DEFINE l_dcolno LIKE syscolumns.colno; -- detail column number DEFINE l_pcolno LIKE syscolumns.colno; -- parent column number DEFINE l_dcolname VARCHAR(128); -- detail column name DEFINE l_pcolname VARCHAR(128); -- parent column name DEFINE l_keyid SMALLINT; -- index array pointer DEFINE l_sql LVARCHAR; -- generated SQL FOREACH SELECT dc.constrname, TRIM(dt.tabname), dt.tabid, di.indexkeys,TRIM(pt.tabname), pt.tabid, pi.indexkeys INTO l_constrname, l_dtabname, l_dtabid, l_dindexkeys, l_ptabname, l_ptabid, l_pindexkeys FROM sysconstraints AS dc JOIN sysobjstate AS do ON do.name = dc.constrname JOIN systables AS dt ON dt.tabid = dc.tabid JOIN sysindices AS di ON di.idxname = dc.idxname JOIN sysreferences AS dr ON dr.constrid = dc.constrid JOIN sysconstraints AS pc ON pc.constrid = dr.primary JOIN systables AS pt ON pt.tabid = pc.tabid JOIN sysindices AS pi ON pi.idxname = pc.idxname WHERE dc.constrtype = 'R' AND do.objtype = 'C' AND do.state = 'E' ORDER BY 2, 1 LET l_sql = 'SELECT d.ROWID AS row_id ' || 'FROM ' || l_dtabname || ' AS d ' || 'LEFT OUTER JOIN ' || l_ptabname || ' AS p '; FOR l_keyid = 0 TO 15 LET l_dcolno = ikeyextractcolno(l_dindexkeys, l_keyid); LET l_pcolno = ikeyextractcolno(l_pindexkeys, l_keyid); IF l_dcolno = 0 THEN EXIT FOR; END IF SELECT TRIM(colname) INTO l_dcolname FROM syscolumns WHERE tabid = l_dtabid AND colno = l_dcolno; SELECT TRIM(colname) INTO l_pcolname FROM syscolumns WHERE tabid = l_ptabid AND colno = l_pcolno; LET l_sql = l_sql || DECODE(l_keyid, 0, 'ON', 'AND') || ' d.' || l_dcolname || " =" || ' p.' || l_pcolname || " "; END FOR FOR l_keyid = 0 TO 15 LET l_dcolno = ikeyextractcolno(l_dindexkeys, l_keyid); IF l_dcolno = 0 THEN EXIT FOR; END IF SELECT TRIM(colname) INTO l_dcolname FROM syscolumns WHERE tabid = l_dtabid AND colno = l_dcolno; LET l_sql = l_sql || DECODE(l_keyid, 0, 'WHERE', 'AND') || ' d.' || l_dcolname || " IS NOT NULL "; END FOR LET l_sql = l_sql || 'AND p.ROWID IS NULL ' || 'INTO TEMP temp_rowid WITH NO LOG'; RETURN l_sql WITH RESUME; LET l_sql = 'UNLOAD TO "' || l_dtabname || '.' || l_constrname || '.unl" ' || 'SELECT * FROM ' || l_dtabname || ' ' || 'WHERE ROWID IN (SELECT * FROM temp_rowid)'; RETURN l_sql WITH RESUME; LET l_sql = 'DELETE FROM ' || l_dtabname || ' ' || 'WHERE ROWID IN (SELECT * FROM temp_rowid)'; RETURN l_sql WITH RESUME; LET l_sql = 'DROP TABLE temp_rowid'; RETURN l_sql WITH RESUME; END FOREACH; END PROCEDURE; ------------------------------ Doug Lawry Oninit Consulting ------------------------------

Thanks for reply Art, I have tried your tools but I am facing errors, maybe I am not doing the right steps... I have run myexport -M database and it returns Errno: 24 and Myschema failed, code: 3 Best regards, SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Thanks all for replies, Concerning I have tested the suggestions but I have faced some troubles, I have done some tests with this procedure: DROP FUNCTION IF EXISTS export_airc;CREATE FUNCTION "informix".export_airc(airc_dir VARCHAR(50) ) RETURNING INTEGER, INTEGER DEFINE feitos INTEGER; DEFINE erros INTEGER; DEFINE create_ext_tab LVARCHAR(8192); DEFINE ins LVARCHAR(512); DEFINE drop_ext_tab LVARCHAR(512); DEFINE tname VARCHAR(250); DEFINE tabela VARCHAR(250); DEFINE dbschema LVARCHAR(1024); DEFINE dbname VARCHAR(250); DEFINE comando VARCHAR(250); DEFINE numrows INTEGER; DEFINE mystring VARCHAR(13); LET mystring = " "; LET feitos=0; LET erros=0; LET dbname = DBINFO("dbname"); LET dbschema = "dbschema -q -d "||TRIM(dbname)||" -it DR -ss "|| TRIM(airc_dir)||"/"||TRIM(dbname)||".sql"; SYSTEM dbschema; SET ISOLATION TO DIRTY READ;SET DEBUG FILE TO TRIM(airc_dir)||"/errlogspl"; TRACE ON; FOREACH SELECT TRIM(tabname) INTO tabela FROM systables WHERE tabid >99 AND tabtype = 'T' --LET comando = "echo "||mystring||" "||tabela||" load from "||tabela||"; >> "||TRIM(airc_dir)||"/load.sql"; LET comando = "load from "||tabela||".unl insert into "||tabela; SYSTEM "echo '"||comando||";' >> "||airc_dir||"/load.sql"; END FOREACH FOREACH SELECT TRIM(tabname) ,nrows INTO tname,numrows FROM systables WHERE tabid >99 AND tabtype = "T" LET create_ext_tab = "CREATE EXTERNAL TABLE "||tname||"_ext "|| " SAMEAS "||tname||" USING (" || "DATAFILES('DISK:"||airc_dir||"/"||tname||".unl'),"|| "FORMAT 'DELIMITED', "|| "DELIMITER '|', "|| "RECORDEND '', "|| "DELUXE, ESCAPE, "|| "NUMROWS "|| numrows+1||", "|| "MAXERRORS 100, "|| "REJECTFILE '"||airc_dir||"/"||tname||".reject' " || " )"; LET ins = "INSERT INTO "||tname||"_ext SELECT * FROM "||tname; LET drop_ext_tab = "DROP TABLE "||tname||"_ext"; EXECUTE IMMEDIATE create_ext_tab; EXECUTE IMMEDIATE ins; EXECUTE IMMEDIATE drop_ext_tab; LET feitos = feitos + 1; END FOREACH RETURN feitos, erros; END FUNCTION; But the problem on import is similar, I have to split sql file and only after loading all tables I can create constraints... SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Sergio: Myschema can split the schema for you, that is how myexport does it with you specify -m, -a, or -M. This is equivalent to specifying -a to myexport as far as how the schema are generated: myschema -d database --index-file=indexes.sql --procedure-file=procs.sql --view-file=views.sql --constraint-file=constraints.sql --synonym-file=synonyms.sql --grant-file=privileges.sql tables.sql Or, just let myexport take care of it all: myexport database -a -E -o $PWD/exports -O -n ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

See my latest for a working command line. I think you need to put the database name first in line and you need a -o <dir_path> option just like for dbexport. Except for the myexport specific options, the command line is the same as for dbexport. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Art, Thanks for help, myschema works perfect and help me to solve my problem. I tried to use myexport but it returns errors: Error building hierarchy1! Error: -229. Myschema failed, code: 1 Best regards, SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Sergio: You ran out of sort space for the temporary tables created during processing of the table hierarchy in the DBSPACETEMP dbspaces, or the filesystems listed in PSORT_DBTEMP filled or privileges on them were wrong, or there were too many sort-work files and the engine ran out of file handles. Something like that (ignore the notes about DBTEMP): $ finderr 229-229 Could not open or create a temporary file. The database server cannot create a temporary disk file. The file should be created in the directory that the DBTEMP environment variable specifies (or /tmp by default on UNIX systems). Check the accompanying ISAM error code and look for operating-system error messages thatmight give more information. Possibly your account does not have write permission in that directory, or the disk is full. Since Version 5.01, the IBM Informix SE database server uses the DBTEMP environment variable, but IBM Informix Dynamic Server and IBM Informix OnLine Dynamic Server do not. On a UNIX system, try increasing the maximum number of open files (NFILES parameter on some systems) to approximately 400. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks for reply Art, Sorry for my lazyness but when I see myschema results, I get satisfied and dont have searched for error meaning. I will take a look over the system to see about the required resources and test myexport. Best regards, SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Just discovered a bug causing the -229 errors when using myexport -O with databases that have many tables. I'm uploading a new version today. Look for the June 1 package on my web site. www.askdbmgt.com/my-utilities ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------