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

  1. 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
    )
  2. 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
    )
  3. Create new table?                     CRTTBL(N)   // (N=No, Y=Yes)
  4. Replace/Add records?             RCDADD(R)  // (A=Add, R=Replace, N=None)
  5. 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

Created: February 29, 2008 | Modified: December 23, 2011