Informix Tools - Transaction Split

Submitted by eherber on Sat, 2006-12-30 16:53

NAME

tx_split - Splitting big transactions into smaller TX-groups

SYNOPSIS

tx_split -f filename [-c commitrate] [-s waitsec] [-p sleepsec] [-k] [-w] [-Y]]

Usage: ./tx_split  -f filename [-c commit_rate] [-s wait_sec] [-p sleep_sec] [-k] [-w] [-Y]

                   -f filename with SQL statements
                   -c max # of rows before commit
                   -s max # of seconds for lockwait
                   -p # of sleep seconds to avoid overrunning the ER system
                   -k lock table in exclusive mode
                   -w without replication
                   -Y Yes, perform the DB operations (default: "readonly")

DESCRIPTION

tx_split is an Esql/C-program that can be used to split SQL update/delete statements into several smaller transaction groups.
This avoids the danger of locktable overflow or a long transaction.

The SQL-statements which should be splitted will be read from the file specified with the -f option.

OPTIONS

  • -f filename

    • This parameter tells tx_split the filename with the SQL statements.
    All SQL update-/delete-statements in this file will be splitted into smaller transaction groups.

  • -c commitrate

    • Determines the size of the transaction groups. A transaction commit happens after the specified number of rows has been processed.

    The default is 1000 rows.

  • -s waitsec

    • Maximum number of seconds to wait for a lock to release.

  • -p sleepsec

    • Number of seconds to pause after each transaction group (i.e. commitrate).
    In an ER environment this could avoid overrunning the ER system with massive updates/deletes performed by tx_split.

  • -k

    • Lock table in exclusive mode if this flag is given.

  • -w

    • Open the transaction with the keywords without replication. This means that in an ER-environment these transactions will not be replicated.

  • -Y

    • Yes, perform the operations on the database (default: readonly)

ALLOWED STATEMENT-TYPES

tx_split can only execute the following types of SQL-statements:

  • database
  • update
  • delete
  • some other SQL-statements (i.e. "set isolation to...", "set constraints...")

For a full list of allowed statement types you have to check the sourcecode.

SQL-statements (like insert or select) will be skipped by tx_split.

EXAMPLES


 SQL-File (upd_stores.tx_split)
 ==============================

 # --------------------------------------------------------------
 # choose database (only database-, no connect-statement allowed)
 # --------------------------------------------------------------
 database stores7;

 # ----------------------------------
 # delete all rows from table "state"
 # ----------------------------------
 delete from state;

 # ---------------------------------------
 # update certain rows in table "customer"
 # ---------------------------------------
 update customer set fname = "Eric" 
         where customer_num between 100 and 110;

 # -------------------
 # choose new database
 # -------------------
 database stores_demo;

 # ----------------------------------------
 # update on table "orders" with sub-select
 # ----------------------------------------
 update orders set order_date = today
        where customer_num in
        (
                select customer_num
                        from  customer
                        where lname matches "H*"
        );

SAMPLE OUTPUT

 tx_split-Call
 ==============
 tx_split -f upd_stores.tx_split -c 20 -Y 

 Sample Output
 =============

 ------------------------------------------------------------------------------
 Time.....: 11.07.2000 - 14:32
 Action...: ["./tx_split" started on host "tbva03"]
 ------------------------------------------------------------------------------

 [2000-07-11 14:37:55]  < UPDATE mode , data will be modified... >
 [2000-07-11 14:37:55]  < DATABASE STORES7 >
 [2000-07-11 14:37:55]  < Current SQL(1): [DELETE FROM STATE] >
 [2000-07-11 14:37:55]  < 20 rows processed so far... >
 [2000-07-11 14:37:55]  < 40 rows processed so far... >
 [2000-07-11 14:37:55]  < 52 rows processed, READY  >
 [2000-07-11 14:37:55]  < Current SQL(2): [UPDATE CUSTOMER SET FNAME = "Eric"   WHERE CUSTOMER_NUM BETWEEN 100 AND 110] >
 [2000-07-11 14:37:55]  < 10 rows processed, READY  >
 [2000-07-11 14:37:55]  < DATABASE STORES_DEMO >
 [2000-07-11 14:37:55]  < Current SQL(4): [UPDATE ORDERS SET ORDER_DATE = TODAY WHERE CUSTOMER_NUM IN 
                         ( SELECT CUSTOMER_NUM FROM  CUSTOMER WHERE LNAME MATCHES "H*" )] >
 [2000-07-11 14:37:55]  < 5 rows processed, READY  >

 ------------------------------------------------------------------------------
 Time.....: 11.07.2000 - 14:32
 Action...: ["./tx_split" finished]
 ------------------------------------------------------------------------------

RETURNCODES

tx_split will deliver the following returncodes:

  • 0=RET_OK

    • Program finished successfully

  • 1=RET_ERR

    • Program encountered an error

DIAGNOSTICS

Debugging can be turned on by setting the environment variable DEBUG_TX_SPLIT before starting the program. The whole debug information can be saved to a file via i/o-redirection.

Example:
--------
export DEBUG_TX_SPLIT=1
 tx_split -f upd_stores.tx_split -c 20 -Y > tx_split.out 2>&1 &

REQUIRES

  1. C-Compiler plus Informix-ClientSDK to compile tx_split
  2. Informix-ClientSDK or -Connect to execute tx_split

AUTHOR

eric@herber-consulting.de - http://www.herber-consulting.de

DOWNLOAD tx_split.tar.gz