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"
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.
- 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
transfered.
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
)
-------------------------------------------------------------------------------