Informix

Expand all | Collapse all

dbexport alternative

  • 1.  dbexport alternative

    Posted Thu May 13, 2021 05:25 PM
    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
    ------------------------------


  • 2.  RE: dbexport alternative

    Posted Thu May 13, 2021 05:55 PM
    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
    ------------------------------



  • 3.  RE: dbexport alternative

    Posted Thu May 13, 2021 07:24 PM
    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
    ------------------------------



  • 4.  RE: dbexport alternative

    Posted Fri May 14, 2021 07:25 AM
    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
    ------------------------------



  • 5.  RE: dbexport alternative

    Posted Fri May 14, 2021 01:44 PM
    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
    ------------------------------



  • 6.  RE: dbexport alternative

    Posted Fri May 14, 2021 01:46 AM
    Edited by Henri Cujass Fri May 14, 2021 01:49 AM
    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
    ------------------------------



  • 7.  RE: dbexport alternative

    Posted Fri May 14, 2021 05:51 AM
    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
    ------------------------------



  • 8.  RE: dbexport alternative

    Posted Fri May 14, 2021 06:03 AM
    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
    ------------------------------



  • 9.  RE: dbexport alternative

    Posted Fri May 14, 2021 03:47 AM
    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
    ------------------------------



  • 10.  RE: dbexport alternative

    Posted Fri May 14, 2021 06:40 AM

    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
    ------------------------------



  • 11.  RE: dbexport alternative

    Posted Fri May 14, 2021 12:23 PM
    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
    ------------------------------



  • 12.  RE: dbexport alternative

    Posted Fri May 14, 2021 01:42 PM
    Edited by Art Kagel Fri May 14, 2021 01:46 PM
    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
    ------------------------------



  • 13.  RE: dbexport alternative

    Posted Tue May 18, 2021 05:01 AM
    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
    ------------------------------



  • 14.  RE: dbexport alternative

    Posted Wed May 19, 2021 07:54 AM
    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 that
    might 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
    ------------------------------



  • 15.  RE: dbexport alternative

    Posted Wed May 19, 2021 05:44 PM
    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
    ------------------------------



  • 16.  RE: dbexport alternative

    Posted Tue June 01, 2021 10:34 AM
    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
    ------------------------------