Java File Copy
The Java File Copy feature makes it simple to copy data tables from one DB to another. This feature works on any OS and any DB supported by m-Power. The JDBC connection property information is required for each execution of this feature and includes that as part of the parameters set.
Parameters
- From-DB Info:
FROMDB(
DBTYP[ 1 ] //JDBC Driver index
DBSYS[ 192.168.0.170 ] //JDBC url to system/DBaseServer
DBUSR[ iSeries_User ] //user ID for logon
DBPWD[ iSeries_Password ] //password for logon
DBLIB[ DATALIB ] //library or schema name
DBTBL[ CUSTOMERS ] //table or file name
) - To-DB Info:
TODB(
DBTYP[ 3 ] //JDBC Driver index
DBSYS[ 192.168.0.120 ] //JDBC url to system/DBaseServer
DBUSR[ SQL_User ] //user ID for logon
DBPWD[ SQL_Password ] //password for logon
DBLIB[ SQLSCHEMA ] //schema or library name
DBTBL[ CUSTOMERS ] //table or file name
) - Create new table? CRTTBL(N) // (N=No, Y=Yes)
- Replace/Add records? RCDADD(R) // (A=Add, R=Replace, N=None)
- Display debug output? DSPMSG(N) // (N=No, Y=Yes)
Example Execution Syntax
Example Execution Syntax (from Remote iSeries DB to MS SQL Server):
java --cp /mrcjava/web-inf/lib/mrcjs11.jar:/mrcjava/web-inf/servlet.jar:/mrcjava/web-inf/lib/jt400.jar:/mrcjava/web-inf/lib/msbase.jar:/mrcjava/web-inf/lib/mssqlserver.jar:/mrcjava/web-inf/lib/util.jar CopyFile "TODB(DBTYP[1] DBSYS[192.168.0.170] DBUSR[ iSeries_User ] DBPWD[ iSeries_Password ] DBLIB[DATALIB] DBTBL[CUSTOMERS])" "FROMDB(DBTYP[3] DBSYS[192.168.0.120] DBUSR[ SQL_User ] DBPWD[ SQL_Password ] DBLIB[SQLLIB] DBTBL[CUSTOMERS])" "CRTTBL(N)" "RCDADD(R)" "DSPMSG(N)"
Example Execution Syntax (from Local iSeries DB to MS SQL Server for m-Power):
java --cp /m-power/mrcjava/WEB-INF/lib/mrcjs11.jar:/m-power/proddata/lib/servlet.jar:/m-power/mrcjava/WEB-INF/lib/msbase.jar:/m-power/mrcjava/WEB-INF/lib/mssqlserver.jar:/m-power/mrcjava/WEB-INF/lib/msutil.jar CopyFile "FROMDB(DBTYP[0] DBSYS[*LOCAL] DBUSR[ iSeries_USER ] DBPWD[ iSeries_PASS ] DBLIB[DATA_LIB] DBTBL[DATA_TABLE])" "TODB(DBTYP[3] DBSYS[SQL_IP] DBUSR[SQL_USER] DBPWD[SQL_PASS] DBLIB[SQL_LIB] DBTBL[SQL_TABLE])" "CRTTBL(Y)" "RCDADD(R)" "DSPMSG(YES)"
Classpath Requirements
For each database, different jar files will be required for the classpath parameter. For best results, get the necessary jar files from the DB vendor and copy them to directory /mrcjava/WEB-INF/lib.
JDBC Driver required for various databases:
(0) Local access iSeries DB2
db2_classes.jar
(1) Remote access iSeries DB2
jt400.jar
(3) MS SQL Server
Version 2003:
msbase.jar
mssqlserver.jar
msutil.jar
Version 2005:
sqljdbc.jar
(4) Oracle Server
ojdbc14.jar
(5) Windows DB2
db2java.jar
db2jcc.jar
db2jcc_license_cu.jar
(7) MySql Server
mysql-connector-java-3.0.11-stable-bin.jar
More Example Syntax
More examples for calling Java CopyFile Feature for various databases:
*LOCAL DB2 on iSeries <==>192.168.0.170 remote DB2 on iSeries
db2_classes.jar
jt400.jar
java --cp /mrcjava/web-inf/lib/mrcjs11.jar:/mrcjava/web-inf/servlet.jar:/mrcjava/web-inf/lib/db2_classes.jar:/mrcjava/web-inf/lib/jt400.jar.jar:/mrcjava/web-inf/lib/jt400.jar:/mrcjava/ CopyFile "FROMDB(DBTYP[0] DBSYS[*LOCAL] DBUSR[] DBPWD[] DBLIB[DEVTESTLIB] DBTBL[LTZFILE])"
" TODB( DBTYP[1] DBSYS[192.168.0.170] DBUSR[] DBPWD[] DBLIB[DEVTESTLIB] DBTBL[LTZFILE3])"
" CRTTBL( N)" "RCDADD(R)" "DSPMSG(YES)"
*LOCAL DB2 on iSeries <==>192.168.0.120 MS SQL Server (turn Windows firewall off on port 1433)
db2_classes.jar
msbase.jar/mssqlserver.jar/msutil.jar
java --cp /mrcjava/web-inf/lib/mrcjs11.jar:/mrcjava/web-inf/servlet.jar:/mrcjava/web-inf/lib/db2_classes.jar:/mrcjava/web-inf/lib/msbase.jar:/mrcjava/web-inf/lib/mssqlserver.jar:/mrcjava/web-inf/lib/msutil.jar CopyFile
"FROMDB(DBTYP[0] DBSYS[*LOCAL] DBUSR[] DBPWD[] DBLIB[DEVTESTLIB] DBTBL[LTZFILE])" "TODB(DBTYP[3] DBSYS[192.168.0.120] DBUSR[ mrc ] DBPWD[ mrc ] DBLIB[MRCSQL] DBTBL[LTZFILE3])" "CRTTBL(N)" "RCDADD(R)" "DSPMSG(YES)"
*LOCAL DB2 on iSeries <==>192.168.0.120:1521 :mrcora Oracle (turn Windows firewall off on port 1521)
db2_classes.jar
ojdbc14.jar
java --cp /mrcjava/web-inf/lib/mrcjs11.jar:/mrcjava/web-inf/servlet.jar:/mrcjava/web-inf/lib/db2_classes.jar:/mrcjava/web-inf/lib/ojdbc14.jar CopyFile
"FROMDB(DBTYP[0] DBSYS[*LOCAL] DBUSR[] DBPWD[] DBLIB[DEVTESTLIB] DBTBL[LTZFILE])" "TODB(DBTYP[4] DBSYS[192.168.0.120:1521:mrcora] DBUSR[ mrc ] DBPWD[ mrc ] DBLIB[MRCORCL] DBTBL[LTZFILE3])" "CRTTBL(N)" "RCDADD(R)" "DSPMSG(YES)"
*LOCAL DB2 on iSeries <==>192.168.0.120::50000/mrcdb2 Oracle (turn Windows firewall off on port 3306)
db2_classes.jar
db2java.jar/db2jcc.jar/db2jcc_license_cu.jar
java --cp /mrcjava/web-inf/lib/mrcjs11.jar:/mrcjava/web-inf/servlet.jar:/mrcjava/web-inf/lib/db2_classes.jar:/mrcjava/web-inf/lib/db2java.jar:/mrcjava/web-inf/lib/db2jcc.jar:/mrcjava/web-inf/lib/db2jcc_license_cu.jar CopyFile
"FROMDB(DBTYP[0] DBSYS[*LOCAL] DBUSR[] DBPWD[] DBLIB[DEVTESTLIB] DBTBL[LTZFILE])" "TODB(DBTYP[5] DBSYS[192.168.0.120::50000/mrcdb2] DBUSR[ mrc ] DBPWD[ mrc ] DBLIB[MRCDB2] DBTBL[LTZFILE3])" "CRTTBL(N)" "RCDADD(R)" "DSPMSG(YES)"
iSeries CL command example
COPYFILEEX Source:
/* EXAMPLE CL PROGRAM TO CALL MRC JAVA COPYFILE FEATURE */
PGM
DCL VAR (&CCSID) TYPE(*DEC) LEN(5 0) VALUE(0)
DCL VAR (&CLASSPATH) TYPE(*CHAR) LEN(9000)
DCL VAR (&QSH) TYPE(*CHAR) LEN(9000)
DCL VAR (&FDBTYP) TYPE(*CHAR) LEN(25)
DCL VAR (&FDBSYS) TYPE(*CHAR) LEN(25)
DCL VAR (&FDBUSR) TYPE(*CHAR) LEN(25)
DCL VAR (&FDBPWD) TYPE(*CHAR) LEN(25)
DCL VAR (&FDBLIB) TYPE(*CHAR) LEN(25)
DCL VAR (&FDBTBL) TYPE(*CHAR) LEN(25)
DCL VAR (&TDBTYP) TYPE(*CHAR) LEN(25)
DCL VAR (&TDBSYS) TYPE(*CHAR) LEN(25)
DCL VAR (&TDBUSR) TYPE(*CHAR) LEN(25)
DCL VAR (&TDBPWD) TYPE(*CHAR) LEN(25)
DCL VAR (&TDBLIB) TYPE(*CHAR) LEN(25)
DCL VAR (&TDBTBL) TYPE(*CHAR) LEN(25)
/* WITH V5R3, TOMCAT RUNNING UNDER CCSID 65535 IS NOT VALID, PER IBM */
RTVJOBA CCSID( &CCSID)
IF COND( &CCSID *EQ 65535) THEN(DO)
CHGJOB CCSID( 37)
ENDDO
/* Set the base classpath */
CHGVAR VAR( &CLASSPATH) +
VALUE( '/mrcjava/WEB-INF/lib/mrcjs11.jar:/mr+
cjava/WEB-INF/servlet.jar ')
/* Append any jar files needed for specific DB connection*/
CHGVAR VAR( &CLASSPATH) VALUE(&CLASSPATH *TCAT +
DBSYS[ *LOCAL] DBUSR[] DBPWD[] +
DBLIB[ CROWLEY ] DBTBL[DMCMP100])" +
" TODB( DBTYP[1] DBSYS[XX.XX.XXX.XXX] +
DBUSR[ sals ] DBPWD[sa1ls] +
DBLIB[ MRCAPPLLIB] DBTBL[DMCMP100])" +
"CRTTBL(Y)" "RCDADD(R)" "DSPMSG(N)"') */
QSH CMD( &QSH)
/* WELL-FORMATTED EXAMPLE: +
QSH CMD( 'java --cp +
/mrcjava/web-inf/lib/mrcjs11.jar:/mrcjava/w+
eb-inf/servlet.jar:/mrcjava/web-inf/lib/jt4+
00 .jar :/mrcjava/web-inf/lib/msbase.jar:/mrc+
java/web- inf/lib/mssqlserver.jar:/mrcjava/w +
web-inf/lib/util.jar CopyFile +
" FROMDB( DBTYP[0] DBSYS[*LOCAL] +
':/mrcjava/WEB-INF/lib/jt400.jar')
CHGVAR VAR( &FDBTYP) VALUE('0')
CHGVAR VAR( &FDBSYS) VALUE('*LOCAL')
CHGVAR VAR( &FDBUSR) VALUE(' ')
CHGVAR VAR( &FDBPWD) VALUE(' ' )
CHGVAR VAR( &FDBLIB) VALUE(' CROWLEY ')
CHGVAR VAR( &FDBTBL) VALUE('DMCMP100')
CHGVAR VAR( &TDBTYP) VALUE('1')
CHGVAR VAR( &TDBSYS) VALUE('XX.XX.XXX.XXX')
CHGVAR VAR( &TDBUSR) VALUE('SALS')
CHGVAR VAR( &TDBPWD) VALUE('SA1LS')
CHGVAR VAR( &TDBLIB) VALUE('MRCAPPLLIB')
CHGVAR VAR( &TDBTBL) VALUE('DMCMP100')
CHGVAR VAR( &QSH) VALUE('java -cp ' *CAT &CLASSPATH +
*TCAT ' CopyFile +
" FROMDB( DBTYP[' *TCAT &FDBTYP *TCAT '] +
DBSYS[ ' *TCAT &FDBSYS *TCAT '] +
DBUSR[ ' *TCAT &FDBUSR *TCAT '] +
DBPWD[ ' *TCAT &FDBPWD *TCAT '] +
DBLIB[ ' *TCAT &FDBLIB *TCAT '] +
DBTBL[ ' *TCAT &FDBTBL *TCAT '])" +
" TODB( DBTYP[' *TCAT &TDBTYP *TCAT '] +
DBSYS[ ' *TCAT &TDBSYS *TCAT '] +
DBUSR[ ' *TCAT &TDBUSR *TCAT '] +
DBPWD[ ' *TCAT &TDBPWD *TCAT '] +
DBLIB[ ' *TCAT &TDBLIB *TCAT '] +
DBTBL[ ' *TCAT &TDBTBL *TCAT '])" +
"CRTTBL(Y)" "RCDADD(R)" "DSPMSG(N)"')
/* CHGVAR VAR( &QSH) VALUE('java -cp ' *CAT &CLASSPATH +
*TCAT ' CopyFile " FROMDB( DBTYP[0] +
DBUSR[ ] DBPWD[] +
DBLIB[ CROWLEY ] DBTBL[DMCMP100])" +
" TODB( DBTYP[1] DBSYS[XX.XX.XXX.XXX] +
DBUSR[ sals ] DBPWD[sa1ls] DBLIB[MRCAPPLLIB] +
DBTBL[ DMCMP100])" "CRTTBL(Y)" "RCDADD(R)" +
" DSPMSG( N)"') */
ENDPGM: ENDPGM