home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 8 Other
/
08-Other.zip
/
tblcp2.zip
/
tblcop2.INF
(
.txt
)
< prev
next >
Wrap
OS/2 Help File
|
1993-10-28
|
16KB
|
417 lines
ΓòÉΓòÉΓòÉ 1. About the Table Copier ΓòÉΓòÉΓòÉ
This utility is designed to copy the result of an SQL "Select" statement from
one database (source) to one or more target databases at one or more target LUs
(targets). Since this is a DB2/2 utility, you can copy a table from/to any
database that can be accessed via DB2/2 APIs (i.e. not only DB2/2 databases
per-se, but any database DB2/2 supports connects to, such as DRDA databases
(DB2,SQL/DS,etc.)). The utility consists of two programs, one of which
connects to the source database, the other of which connects to the target
database. This is necessary because DB2/2 does not allow a single process to
simultaneously connect to two or more databases.
The "exporter" (source) and "importer" (target) talk to each other using Appc.
This transport mechanism has the advantage of allowing the exporter and
importer to run on different OS/2 machines. The only necessary relationship
between two participating OS/2 machines is that they be "mutually Lu6.2
addressable." That is, the exporter machine must be able to bind an Lu6.2
session with the importer machine. Needless to say, this requires
Communications Manager in addition to Database Manager.
The exporter can "distribute" the table it is copying to multiple targets at
once (a separate instance of the importer is started at each target).
The process is always driven from the exporter side, because that is where the
SQL Select statement (which defines the table to be copied) is supplied. The
exporter is implemented as a Rexx external function. This was done because the
exporter requires many parameters and Rexx compound variables (stems) provide
an easy way to pass such structures into a compiled program. Besides, if it's
callable from Rexx, it can be used anywhere in OS/2.
Author: Dave Boll
(CompuServe = 74170,2016)
IBMMAIL(USFMCFKF)
Release Date: 28 October, 1993
Version: 1.2 (for DB2/2)
Copyright 1993 Dave Boll
ΓòÉΓòÉΓòÉ 2. New in this release ΓòÉΓòÉΓòÉ
This release contains a few fixes and some new features.
New Features:
- Ability to limit number of rows fetched and sent.
(Thanks to suggestion by Jean-Claude Marchier)
- Ability to specify fetch/send ratio
- Ability to supply an argument to the follow-up command string
sent to importer.
- Ability to specify a userid/password for APPC conversation
security which is independent of that used for target database
logon.
- Format of each target's result string changed to be more usable
Bug Fixes:
- Was not treating empty follow-up command string properly.
- Code to log SQL statement failures in the importer
ΓòÉΓòÉΓòÉ 3. How to Use It ΓòÉΓòÉΓòÉ
How to register the table copier as a Rexx function:
In order to use the function, you have to register it with Rexx like this:
call rxfuncadd yourfuncname,'tblcop2e','tblcop2e'
where:
yourfuncname = any name by which you want to refer to the function in
your Rexx programs
ΓòÉΓòÉΓòÉ 4. Tips, Restrictions, Recommendations ΓòÉΓòÉΓòÉ
Important notes:
- The DLL "tblcop2e.dll" must be in your "LIBPATH" for the Rexx
external function "tblcop2e" to work.
- The exporter and importer components of the utility will attempt to bind
themselves to the source and target databases (respectively) if necessary.
First, if the "tblcop2e.bnd" file is in the same directory as the
"tblcop2e.dll" DLL was loaded from, this will be used to bind. And if the
"tblcop2i.bnd" file is in the same directory as the "tblcop2i.exe" EXE was
loaded from, this will be used to bind.
In each case, if the corresponding .BND file is not found in the respective
directory, the "DPATH" will be searched for a bind file. If none is
found, the utility will fail.
- The function "tblcop2e" is NOT designed to be run on multiple threads
of the same process. If you try to run the function on multiple threads
of the same process, results will be unpredictable.
- The components of this utility are 32-bit code and therefore will
only run on OS/2 2.x versions.
ΓòÉΓòÉΓòÉ 5. Syntax ΓòÉΓòÉΓòÉ
The basic syntax is:
info = tablecopy(fromdb,selstmt,targstem[,fromid][,frompw][,cmtfreq]
[,nodata][,appcmode][,fetchlim][,fsratio])
where:
fromdb = name of source database, from the perspective of the
machine where the exporter is running
selstmt = actual text of an SQL Select statement whose result table
you want copied. This statement may not contain any host
variables or parameter markers. That is, it is restricted
in the same way as an SQL statement in QMF or SPUFI. In
addition, since the statement is prepared and executed
dynamically, the auth-id running the exporter must have
whatever privileges are necessary to perform the select.
It is recommended that you always specify the "For Fetch
Only" clause on your select statement as this will cause
block fetch at the source database, if it is remote from
the source machine.
targstem = the actual name of a Rexx stem (such as "st.") under
which are mapped the parameters for 1-n targets as such:
stem.n.1 = target Lu name
stem.n.2 = target database name
stem.n.3 = target table name
stem.n.4 = userid to be used for logon to target database
stem.n.5 = password to be used with "stem.n.4"
stem.n.6 = copy option which governs how table is copied
to target
stem.n.7 = alter option which tells importer how to
handle surplus columns
stem.n.8 = convert short-enough LongVarChar to VarChar
stem.n.9 = use Appc Security
stem.n.10 = altnerate column names for target table
stem.n.11 = follow-up command string to be executed at
target after table copy is complete (such
as DDL, etc.)
stem.n.12 = Argument to program specified in stem.n.11
stem.n.13 = Alternate Appc Security Userid
stem.n.14 = Alternate Appc Security Password
fromid = userid to be used for logon to source database
frompw = password to be used for logon to source database
cmtfreq = commit frequency, causes a commit at target(s) every time
this many rows have been processed (keeps log file size down).
nodata = causes source table's structure to be copied without
sending its rows (still allows follow-up command string),
allowing the DDL of a table to be distributed.
appcmode = alternate APPC Mode Name to be used in conversation(s)
between exporter and importer (default is "#BATCH").
fetchlim = integer specifying maximum number of rows to be fetched
and sent (default is all rows from SELECT statement).
fsratio = fetch/send ratio, which allows you to send every "nth"
row (default is every row (i.e. fsratio = 1)).
ΓòÉΓòÉΓòÉ 6. Target parameter info ΓòÉΓòÉΓòÉ
The target parameter structure defines where/how to distribute table.
Specific information about the elements of the target parameter structure:
stem.n.1 = target Lu name (REQUIRED)
- can be either an lu alias, a fully qualified luname
or an asterisk (*) to indicate same as originating lu
stem.n.2 = target database name (as catalogued at the target Lu) (REQUIRED)
- this name is the local alias of the target database
at the target lu. This allows you to "leapfrog"
databases by addressing a database at a remote lu
which is in turn remote from that lu.
stem.n.3 = target table name (REQUIRED)
stem.n.4 = userid to be used for logon to target database (REQUIRED)
- this userid is also used for Appc Security, if
such is requested with "stem.n.9" (see below)
stem.n.5 = password to be used for logon to target database (REQUIRED)
- this password is also used for Appc Security, if
such is requested with "stem.n.9" (see below)
stem.n.6 = copy option governs how table is copied to target (OPTIONAL)
Valid values are:
C - Create
(DEFAULT)
Table will be created at target database. This
will fail if table already exists at target.
R - Replace
Table will be created at target database, or droppoed
and then created if it already existed.
A - Append
Rows sent are added to existing rows in target
table.
O - Overlay
All rows in target table are deleted, then sent
rows are inserted in their place.
I - Insert_Update
Each sent row is inserted, or used to update if
row of same primary/sole-unique key already exists.
Should be used instead of "Update_Insert" if most
sent rows will be "inserts" because fewer SQL
statements will be executed.
U - Update_Insert
Each sent row updates row of same primary/sole-unique
key if it exists, or is inserted otherwise.
Should be used instead of "Insert_Update" if most
sent rows will be "updates" because fewer SQL
statements will be executed.
stem.n.7 = alter option indicates how to handle surplus columns (OPTIONAL)
To determine what are surplus columns, the leading columns of
the sent table are compared with the corresponding columns of
the target table (if it exists). If all leading columns match
exactly (same data type, except nullability, and same precision
and scale, and same column name), and copy option is APPEND,
OVERLAY, INSERT_UPDATE, UPDATE_INSERT, then alter option will
be honored. For instance, if the target table has 6 columns,
but the table being sent has 8 columns, do I want to alter
the target table to add the 2 new columns of the incoming
table, or ignore these new columns, or reject the copy?
Valid values are:
A - alter target table to add new columns before
inserting/updating rows
I - ignore surplus columns (that is, for each sent
row, discard the surplus columns)
R - reject the copy if structures do not match exactly
(DEFAULT)
stem.n.8 = convert short-enough LongVarChar to VarChar (OPTIONAL)
From DB2/2, a DB2 column which describes as having a
length > 255 bytes and < 4000 bytes appears as LongVarChar,
though the same column in DB2/2 could be just VarChar.
This option allows such columns to be created in DB2/2 as
just VarChar of the appropriate length.
Valid values:
N - Don't convert LongVarChar->VarChar
(DEFAULT)
Y - Do Convert LongVarChar->VarChar
stem.n.9 = use Appc Security (OPTIONAL)
when allocating conversation to importer at target,
use Appc conversation security.
Valid values:
N - Don't use conversation security
(DEFAULT)
Y - Do Use conversation security
stem.n.10 = alternate column names (OPTIONAL)
specification of alternate column names for target
table. This is a blank delimited list of the form:
colno.colname colno.colname colno.colname ...
where:
colno = the relative column number to be renamed
(for a table with "n" columns, valid
values are 1-n)
colname = new name for the column (1-18 characters)
an example of an alternate column list specification:
"3.new_col3 5.first_name 2.second_col"
stem.n.11 = follow-up command string (OPTIONAL)
- follow-up command string to be executed at target
after table copy is complete. This string is
interpreted directly by the Rexx interpreter at the
target machine as a Rexx program. Thus, whatever
you send must be legal Rexx program source code.
Typically, this feature could be used to execute DDL
after the copy has completed, but since you're talking
to the Rexx interpreter, you obviously could do lots
of other things. This includes deleting files and
the like, which is why you may want to secure the
importer at the target machine to require appc security.
stem.n.12 = Argument to program specified in stem.n.11
- this is a way to pass data to the program string
stem.n.13 = Alternate Appc Security Userid (OPTIONAL)
- this is used when Appc Security is requested, but
the userid to be used for Appc Security must be
different from that used for the target database
logon. If Appc Security is requested, but this
value is not supplied, then the target database
logon userid will be used in its place.
stem.n.14 = Alternate Appc Security Password (OPTIONAL)
- this is used when Appc Security is requested, but
the password to be used for Appc Security must be
different from that used for the target database
logon. If Appc Security is requested, but this
value is not supplied, then the target database
logon password will be used in its place.
ΓòÉΓòÉΓòÉ 7. Results ΓòÉΓòÉΓòÉ
The result of the copy to each target is returned under the stem used to supply
target parameters for that target as "stem.n" for target "n".
You can disect the result for each target as such:
do i=1 to stem.0
parse var stem.n copyresult ' ' commandresult
end
where:
copyresult = string which indicates copy statistics as such:
#rows:rcvd=#rcvd,isrt=#isrt,updt=#updt
commandresult = byte for byte result returned from your Rexx
command string as executed by the importer
The function itself returns a value which indicates its success ('0')
or failure (not '0')
ΓòÉΓòÉΓòÉ 8. How it works ΓòÉΓòÉΓòÉ
The exporter starts a conversation with an instance of the importer for each
target specified. The exporter can distribute the same copy of a table to
multiple targets in one invocation. The mechanisms are somewhat like this:
Sending LU Receiving LU
(tblcop2e) (tblcop2i)
ΓöîΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ ΓöîΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
Γöé Γöé Γöé Γöé
Γöé From Database Γöé APPC Γöé Target Database #1Γöé
Γöé Select Stmt. ΓöéΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇ>Γöé Target Table #1 Γöé
Γöé Γöé Γöé Γöé Γöé
Γöé Γöé<ΓöÇΓöéΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöé Γöé
Γöé Γöé Γöé ^ Γöé Γöé
Γöé Γöé Γöé Γöé Γöé Γöé
ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ Γöé Γöé ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
Γöé Γöé
Γöé Γöé Receiving LU
Γöé Γöé (tblcop2i)
Γöé Γöé ΓöîΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
Γöé Γöé Γöé Γöé
Γöé Γöé APPC Γöé Target Database #2 Γöé
ΓööΓöÇΓöÇΓöÇΓöéΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇ>Γöé Target Table #2 Γöé
Γöé Γöé Γöé
ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöé Γöé
Γöé Γöé
Γöé Γöé
ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
.
.
.
.
.
.
If you are copying a table from a database on one machine to a remote
database (another DB2/2), it is best to have the importer run on the
machine where the remote database resides because then its SQL will
execute locally on that machine. However, the exporter and importer
can each connect to a database which is remote from the machine on
which they are running.
ΓòÉΓòÉΓòÉ 9. Communications Manager Configuration ΓòÉΓòÉΓòÉ
Because the utility uses Appc for communication between the exporter and the
importer, certain features of the Comm. Mgr. SNA configuration must be set up.
Suggested configuration values in the .NDF of a target machine:
DEFINE_TP tp_name(TBLCOP2I)
filespec(c:\tblcop2i.exe)
conv_security_rqd(no) /* or this could be "yes" */
conversation_type(mapped) /* MUST be mapped */
program_type(vio_windowable)
sync_level(either) /* "either" or "confirm" */
tp_operation(nonqueued_am_started);
or, to avoid explicit definition of TP:
DEFINE_DEFAULTS IMPLICIT_INBOUND_PLU_SUPPORT(YES)
DEFAULT_MODE_NAME(#BATCH)
MAX_MC_LL_SEND_SIZE(32767)
DIRECTORY_FOR_INBOUND_ATTACHES(*)
DEFAULT_TP_OPERATION(NONQUEUED_AM_STARTED)
DEFAULT_TP_PROGRAM_TYPE(VIO_WINDOWABLE)
DEFAULT_TP_CONV_SECURITY_RQD(NO)
MAX_HELD_ALERTS(10);
ΓòÉΓòÉΓòÉ 10. Possible Uses ΓòÉΓòÉΓòÉ
The utility can be used to copy/merge tables within a single database on a
single machine, among databases on a single machine, or among databases on
several machines. The number of targets for a given copy operation is limited
only by available memory.
This utility can be used to efficiently download data from DB2 or SQL/DS into
DB2/2 databases if you have DDCS/2 available.