Informix

 View Only
Expand all | Collapse all

How to compile dbdelete utility for utils2_ak file

  • 1.  How to compile dbdelete utility for utils2_ak file

    Posted Tue March 28, 2023 10:04 PM

    Hello, Anyone knows how to implement or compile dbdelete utility for utils2_ak file? My computer environment is windows and i have no idea how to compile and apply it into the informix.



    ------------------------------
    nasiha zailan
    ------------------------------


  • 2.  RE: How to compile dbdelete utility for utils2_ak file

    IBM Champion
    Posted Tue March 28, 2023 10:27 PM

    Nasiha:

    Just unpack the shell archive (ksh ./utils2_ak) then if you are running on Linux just type:

    make

    and the entire package will build itself. For other platforms, there is a text file, BUILDING, that describes how to modify the makefile (named Makefile) for other platforms and there are comments and notes inside the makefile itself.

    If you edit the makefile and set INSTALLDIR to where you want the executables installed, you can just run:

    make install

    If you only want to build dbdelete, just type:

    make dbdelete

    Witness:
    art@Elezar-II:~/GoogleDrive/iiug/utils2_ak/test$ make dbdelete
    INFORMIXC=gcc esql  -O -DHAVE_GNU_GETOPT=1 -DHAVE_GETOPT_H=1   -o dbdelete dbdelete.ec
    art@Elezar-II:~/GoogleDrive/iiug/utils2_ak/test$ ls -l ./dbdelete
    -rwxrwxr-x 1 art art 37392 Mar 28 22:25 ./dbdelete

    art@Elezar-II:~/GoogleDrive/iiug/utils2_ak/test$ ./dbdelete

    /dbdelete - Delete rows from an Informix table.

    Usage: /dbdelete
                    -d database
                    -b FetBufSize
                    -t table
                   <-h host>
                   <-S> <-V> <-R> <-I> <-v>  
                   <-w waittime>
                   <-u keylist>
                   <-o>
                   <-s where clause | -q select stmt>
                   <-B SQL bufsize>
                   <-p PDQRPIORITY>

           -d - Database from which to delete.
           -b - Size of a transaction buffer.  Controls the number of rows deleted
               in a single transaction. (Default: 32767. Range: 2048 -> 32767)
           -t - Table from which data is to be deleted.
           -h - Host to delete rows from (Default: $INFORMIXSERVER).
           -S - Silent mode.  Supress messages.
           -V - Print version information and exit.
           -v - Enable verbose output.
           -I - Use ifx_row_id instead of rowid. Supports partitioned tables
                that were not created WITH ROWIDS. Should be faster for other
                partitioned tables as well.
           -P - Pause in milli-seconds between deletes.
           -w - # of seconds to wait for locked rows to be released (if < 0 wait
                forever).  (Default 10.)
           -u - Comma separated list of columns forming a key which can be used to  
                identify rows to delete.  Separate -u options may be used.  
                (Default: ROWID.)
           -o - Do not close the fetch cursor between fetch rounds.  This is
               slower, however, it will prevent dbdelete from deleting rows that
               have been inserted since the original cursor was opened.  Note:
               this option does NOT eliminate the dangers of using dbdelete on
               a table that is being actively inserted to.  Rows inserted BEFORE
               the cursor is open are still at risk.  Caviat emptor applies.
           -s - Where clause for use in selecting ROWIDS or row keys to delete.
                Do not include the keyword "WHERE".
           -q - Full SELECT statement to select rowids (or keys) to delete.  If  
                keys are returned you must also include -u
           -B - Set the size of the SQL DELETE statements that are generated.
                (DEFAULT: 2048, MAXIMUM: 65500)  N.B. 2K seemed most efficient in
                testing.  This option is effective only for delete by rowid (ie no  
                '-u' options).
           -p - PDQPRIORITY to set for the task.
           -R - Do not replicate deletions.  Begins transactions with the WITHOUT  
               REPLICATION clause included.


    The standard Informix environment variable FET_BUF_SIZE will override the  
    default value of the (-b flag) Commit size if within the valid range.  Higher  
    values for -b will commit larger blocks of rows deleted and use more locks.  


    Finally, if you are still having issues, just email me or fill out the contact form on my website and I'll help out.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 3.  RE: How to compile dbdelete utility for utils2_ak file

    IBM Champion
    Posted Tue March 28, 2023 10:40 PM

    For what it's worth, the most likely way you will want to run dbdelete will be like this:

    dbdelete -d <database> -t <table> 

    If you will not be deleting all rows from the table, add a -s <filter> option.

    If the table is partitioned and you are using v14.10FC6 or later, include the -I option to tell dbdelete to use the undocumented ifx_row_id instead of ROWID. This will work in most earlier releases, but it will be slow in earlier releases because ifx_row_id was treated as a filter before 14.10.FC6 (instead of the physical address of the row) causing each row to trigger a sequential scan of the entire table.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 4.  RE: How to compile dbdelete utility for utils2_ak file

    Posted Wed March 29, 2023 03:13 AM

    Hi Nasiha.

    I think Art may have missed that you are on Windows where compiling dbdelete might be very challenging! You could use stored procedure sp_dbdelete which is named in deference to Art and provides the basics:

    https://www.oninitgroup.com/faq-items/informix-stored-procedure-for-mass-delete



    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------

    Original Message


  • 5.  RE: How to compile dbdelete utility for utils2_ak file

    IBM Champion
    Posted Wed March 29, 2023 06:38 AM

    Arg! Yes, I did miss that. Thanks Doug.

    OK, compiling on Windows is not covered in the BUILDING file. 

    Nasiha: OK, this presents a couple of problems. First, I do not know how to expand the shell archive that contains the source code. So, please email me directly (art.kagel@gmail.com) and I will get you a standard zip file that you can unpack on Windows. The makefile it contains is not compatible with the Microsoft make utility which is non-standard (of course it is). So, here's the compile line. You can compile dbdelete like this:

    esql -o dbdelete dbdelete.ec getopt.ec 



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 6.  RE: How to compile dbdelete utility for utils2_ak file

    IBM Champion
    Posted Wed March 29, 2023 09:41 AM
      |   view attached

    Got it compiled on Windows, with some minor modifications.

    Haven't tested it, though...



    ------------------------------
    Andreas Legner
    ------------------------------

    Attachment(s)

    zip
    dbdelete.zip   734 KB 1 version
    Original Message


  • 7.  RE: How to compile dbdelete utility for utils2_ak file

    Posted Thu March 30, 2023 12:36 AM

    Thanks Andreas! Really helpful, i've had hard times to compile on windows. 



    ------------------------------
    nasiha zailan
    ------------------------------

    Original Message


  • 8.  RE: How to compile dbdelete utility for utils2_ak file

    IBM Champion
    Posted Wed March 29, 2023 10:14 AM

    Oops, that compile line should be:

    esql -o dbdelete dbdelete.ec getopt.c

    Not getopt.ec.

    Thanks Andreas.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 9.  RE: How to compile dbdelete utility for utils2_ak file

    IBM Champion
    Posted Wed March 29, 2023 10:32 AM
    Edited by Andreas Legner Wed March 29, 2023 10:34 AM

    You're welcome!

    And yes, that was the most obvious 'modification' required, yes ;-)

    Others:

    • remove #include <unistd.h>
    • dbdelete.ec: extern char *optarg;  -  shouldn't this be required on non-Windows too, or are you relying on native getopt there?
    • dbdelete.ec: replaced nanosleep loop by Sleep(ms)
    • getopt.c: changed an #include <strings.h> into #include <string.h>
    • getopt.c: malloc instead of __alloca

    I also compiled with -static, so no CSDK should be required for running (increases size from 38kB to nearly 2MB).

    Andreas



    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 10.  RE: How to compile dbdelete utility for utils2_ak file

    IBM Champion
    Posted Wed March 29, 2023 11:37 AM

    Thanks. Making things more Windows friendly. This is the first time anyone has told be about trying to build any utils2_ak utilities on Windows. Always wondered.



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 11.  RE: How to compile dbdelete utility for utils2_ak file

    Posted Thu March 30, 2023 12:39 AM

    Hello Art, anyway do you have any idea why my execution got error? Did i missed something?

    Thanks.



    ------------------------------
    nasiha zailan
    ------------------------------

    Original Message


  • 12.  RE: How to compile dbdelete utility for utils2_ak file

    IBM Champion
    Posted Thu March 30, 2023 08:39 AM

    Nasiha:

    The error, -23101, is a locale error. Your current locale does not match the locale of the database:

    $ finderr 23101
    -23101  Unable to load locale categories.

    An invalid locale name was supplied for the locale initialization.
    The environment variable specifying the locale category has a wrong
    value.

    Check the value of the corresponding environment variable,
    CLIENT_LOCALE or DB_LOCALE. If this error was encountered when using
    the "SET COLLATION" statement, check that the value passed to the
    statement is a valid locale. If this error was encountered when using
    the "SET NO COLLATION" statement, check that the session's default
    locale file is still available. Also check if INFORMIXDIR points to
    a valid directory where IBM Informix products are installed.

    If a client application receives this error when it requests a database
    server connection, check that the client and database locales are
    compatible. They are compatible if a valid locale exists on the server
    computer with the name lg_tr.codeset@mod, where lg_tr (language and
    territory) and mod (locale modifier) are from the CLIENT_LOCALE and
    codeset is from the database locale (from the DB_LOCALE, if it is set,
    or as stored in the database).

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 13.  RE: How to compile dbdelete utility for utils2_ak file

    Posted Sun April 09, 2023 10:24 PM

    Hi, i stil got the same error, Below is my locale setup;

    What did i do wrong?



    ------------------------------
    nasiha zailan
    ------------------------------

    Original Message


  • 14.  RE: How to compile dbdelete utility for utils2_ak file

    IBM Champion
    Posted Mon April 10, 2023 03:54 AM
    The DB_LOCALE needs to be the same as the one the database was created with.
    Check in sysmaster database. There is a table of databases with the locale identifier  sydbslocale . The id in the table is a sortiert of internal identifier which might not be equal to the environment setting, but it will give you a hint.
    Marcus 





    Original Message


  • 15.  RE: How to compile dbdelete utility for utils2_ak file

    IBM Champion
    Posted Wed March 29, 2023 03:19 AM

    Just for clarification, by "my computer environment" do you mean your personal computer or the one hosting this database server?

    What OS is the Informix server running on?



    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message