1.  Program Usage
2.  When should movetab be used ?
3.  When should movetab not be used ?
4.  What is moved/copied ?
5.  What happens if you start movetab ?
6.  What does the delay option (-d) mean ?
7.  What does the batchjob option (-b) mean ?
8.  What happens if a movetab-job is interrupted ?
9.  What means job_status "F" (failed) ?
10. Description of table "movetab_status"
11. Description of isql-form "movetab"

1.  Program Usage

Usage..: movetab -s db@db_srv:tab -t db@db_srv:tab -f dbspace
                 [-c commit_rate] [-F ] [ [-E] | [-n] [-g] [-d] ]
         -s source table
         -t target table
         -f target dbspace
         -c number of rows before commit
         -F take fragmentation scheme from source to target table
         -E Enterprise Replication
         -n no drop table/create synonym
         -g grant privileges
         -d delay flag (job is only stored, not executed)

or.....: movetab -b
         -b batch mode

Example: movetab -s host@NO_PROD:inf20 -t host@no_arc_pip:inf20 -f inf1dbs
                 -c 20000

2.  When should movetab be used ?

Normally you will use movetab to move tables between two informix-
online-instances when it is not possible to turn logging off.
Movetab splits the whole operation in a smaller set of transaction
groups (size can be configured).
So you don't have to worry about a long transaction, you don't need
any filesystem-space for an unload and if the job fails in the middle
of execution, movetab will start from this error point instead of
repeating the whole operation.
Movetab can be used to transfer data between different machines if
there is a sqlhosts-connection between them.

Movetab can also be useful when using informix enterprise-replication.
it is suitable to be used as initial synchronization method, trans-
fering the data the first time to the target tables.
The special flag "-E" can be used for enterprise replication.

3. When should movetab not be used ?

Movetab can only be used with static tables. That means that no
application should update data (only select) from the source
table if movetab runs.
The exception to this is when using movetab as initial synchronization
method in an ER-environment.
Movetab can and should only be used in databases with transactions,
otherwise it makes no sense to use movetab.
Movetab cannot be used to transfer tables with big blobs, because
the blobs will be stored in memory.
Blobs are stored with the table, not in seperated blobspaces
you cannot use movetab between two Shared-Memory-Connections.

4. What is moved/copied ?

- table structure
- indices
- constraints
- permissions
- fragmentation scheme (if option "-F" is given)
- data

5. What happens if you start movetab ?

1)  The table "movetab_status" will be created in the "db@dbsrv" where
    the environment varialbe "DBSRV_MOVETAB" points to.
    If this variable is not set, table "movetab_status" will be created
    in the source-database.
2)  A new job will be inserted into table "movetab_status" (job_status="N" ->New)
3)  If delay_flag "-d" is given, then movetab stops execution here
4)  Movetab created the target database if it not exists and grants
    the permissions according to dictionary-table "sysusers" (if option "-g" given)
4)  Movetab creates target table
5)  Movetab grants permissons on target-table (if option "-g" given)
6)  Movetab creates unique indices (if there are any)
7)  Movetab reads rows from source table and insert rows into target table
8)  Every 5000 rows (default, if  -c commit parameter is not given) a
    transaction is committed and a new one is started.
    The column "nrows_target" in table "movetab_status" is updated with the
    number of rows already transferred
9)  After copy of rows the following is done
    - additional indexes will be created
    - column defaults will be created
    - constraints will be created (if possible, otherwhise error message)
    - update statistics (low) is executed
10) If "-n" (no drop table/create synonym) is used than:
    - nothing happens, movetab skips to point 11)
    Otherwise ("-n" option is NOT given)
    - drop source table
    - create synonym from source table to target table
11) Mark job as completed in table "movetab_status" (job_status "C")

6. What does the delay option (-d) mean ?

If option "-d" is given a new job will be stored in table
"movetab_status", but is not executed.
Later you can execute the stored job with "movetab -b".

7. What does the batchjob option (-b) mean ?

If movetab is started with "movetab -b", then the first uncompleted
job (job_status "N"->New or "P"->Partial) will be executed
Only one job will be executed, if there are remaining uncompleted jobs,
you have to start additional "movetab -b" jobs
The output will be automatically redirected to "tabname.out", so you
can start several "movetab -b" jobs in parallel and you don't need
to redirect the output by yourself.

8. What happens if a movetab-job is interrupted ?

Normally the job will be marked as "P" (partially completed), so if
you execute "movetab -b" the next time, the job will be executed.
The movetab-program skips the number of rows ("nrows_target") already
If you kill the job or the connection is lost (maybe because the database
server goes down), then the job_status is not changed, so the job_status
remains "R"->Running.
In this case you must update table "movetab_status" by hand (thru isql-form
"movetab(.per)") and change job_status from "R"->Running to "P"->Partial.
then restart job with "movetab -b".

9. What means job_status "F" (failed) ?

If a job is marked as failed (job_status="F") then take a look at the
column "job_comment" in table "movetab_status".
Normally you should see an sql-/isam error code.
there is a also a "retries" column in table "movetab_status".
If the number of restarts exceeds 3, than the job is automatically
marked as "F"->Failed and will not be restarted anymore.
Maybe there is not enough free disk space or some other sort of error.
you shoud decide for yourself how to go further.

10. Description of table "movetab_status"

(1)  job_id         serial not null primary key,
(2)  job_created    datetime year to second default current year to second
                    not null,
(3)  job_status     char(1) not null
                    check (job_status in ("N","R","P","F","C")),
(4)  job_comment    varchar(255, 20),
(5)  start_time     datetime year to second,
(6)  finish_time    datetime year to second,
(7)  max_commit     int not null,
(8)  retries        int default 0 not null,
(9)  drop_table     char(1) default "Y" not null
                    check (drop_table in ("Y","N")),
(10) grant_priv     char(1) default "N" not null
                    check (drop_table in ("Y","N")),
(11) er_flag        char(1) default "N" not null
                    check (drop_table in ("Y","N")),
(12) frag_flag      char(1) default "N" not null
                    check (frag_flag in ("Y","N")),
(13) dbsrv_source   varchar(18,5) not null,
(14) db_source      varchar(18,5) not null,
(15) tabn_source    varchar(18,5) not null,
(16) nrows_source   int not null,
(17) dbsrv_target   varchar(18,5) not null,
(18) db_target      varchar(18,5) not null,
(19) tabn_target    varchar(18,5) not null,
(20) dbspace_target varchar(18,5) not null,
(21) nrows_target   int default 0 not null

Colno Purpose
(1)   Unique identification number of this job
(2)   Time when job was created
(3)   Current status of the job
      "N" -> New
      "C" -> Complete
      "R" -> Running
      "P" -> Partial
      "F" -> Failed
(4)   Job comment ("job FAILED", "job completed SUCCESSFULLY", error code)
(5)   Time when job started
(6)   Time when job finished
(7)   Max number of inserts in one transaction group
(8)   Number of restarts
(9)   Table should be dropped or not
      "Y" -> source table will be dropped, synonym will be created
      "N" -> source table will not be dropped, no synonym will be created
(10)  Table-privileges should be granted or not
(11)  Enterprise-replication flag
(12)  Fragmentation flag
      "Y" -> fragmentation schema from source table will be used for the
      creation of the target table
      "N" -> unfragmented target-table will be created
(13)  Name of source database server
(14)  Name of source database
(15)  Name of source table
(16)  Number of rows in source table
(17)  Name of target database server
(18)  Name of target database
(19)  Name of target table
(20)  Dbspace where target table will be created
(21)  Number of rows in target table

11. Description of isql-form "movetab"

                          M O V E T A B _ S T A T U S
Job_id.........: (181   )                Job_created....: (1997-03-04 12:13:18)

Job_status.....: (C)    Job_comment: (job finished SUCCESSFULLY !             )
                                     (                                        )
                                     (                                        )
Start_time.....: (1997-03-04 12:15:09)   Finish_time....: (1997-03-04 12:23:25)
Max_commit.....: (5000       )           Retries........: (0          )
Drop_table.....: (N) Grant_priv: (Y)  Er_flag: (Y)  Frag_flag: (N)
Dbsrv_source...: (NO_PROD           )    Db_source......: (host               )
Tabn_source....: (unrated035842     )    Nrows_source...: (30000      )
Dbsrv_target...: (no_rate2          )    Db_target......: (hostr1             )
Tabn_target....: (unrated035842     )    Nrows_target...: (30000      )
Dbspace_target.: (inf1dbs           )