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------------------------------
Original Message:
Sent: Fri May 14, 2021 12:22 PM
From: Sergio Peres
Subject: dbexport alternative
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
Original Message:
Sent: Fri May 14, 2021 06:39 AM
From: Doug Lawry
Subject: dbexport alternative
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
Original Message:
Sent: Thu May 13, 2021 05:25 PM
From: Sergio Peres
Subject: 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