NAME

tx_split - Splitting big transactions into smaller TX-groups

SYNOPSIS

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

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 (ALLOWED STATEMENT-TYPES) in this file will be splitted into smaller transaction groups.

-c commitrate
Determines the sizes 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.

-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...", -> you have to check the sourcecode)

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

ENVIRONMENT-VARIABLES

DEBUG_TX_SPLIT

This environment variable could be set for debugging purposes (DIAGNOSTICS).

EXAMPLE

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*";
        )

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

DIAGNOSTICS

Debugging can be turned on by setting the environment variable DEBUG_TX_SPLIT before starting tx_split.

The debug-information could be saved with the use of I/O-redirection.

Example

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

RETURNCODES

tx_split will deliver the following returncodes:

0=RET_OK Program finished successfully

1=RET_ERR Program encountered erros

REQUIRES

AUTHOR

eric@herber-consulting.de