{"id":2005,"date":"2008-02-29T03:45:15","date_gmt":"2008-02-29T09:45:15","guid":{"rendered":"http:\/\/www.mrc-productivity.com\/legacy\/?page_id=2005"},"modified":"2011-12-23T12:13:15","modified_gmt":"2011-12-23T18:13:15","slug":"using-java-file-copy-to-copy-files-between-different-databases","status":"publish","type":"page","link":"https:\/\/www.mrc-productivity.com\/legacy\/mrc-productivity-series\/using-java-file-copy-to-copy-files-between-different-databases","title":{"rendered":"Java File Copy"},"content":{"rendered":"

<\/p>\n

Java File Copy<\/h1>\n

 <\/p>\n

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.<\/p>\n

Parameters<\/h3>\n
    \n
  1. From-DB Info:
    \nFROMDB(
    \nDBTYP[ 1 ]                           \/\/JDBC Driver index
    \nDBSYS[ 192.168.0.170 ]         \/\/JDBC url to system\/DBaseServer
    \nDBUSR[ iSeries_User ]            \/\/user ID for logon
    \nDBPWD[ iSeries_Password ]    \/\/password for logon
    \nDBLIB[ DATALIB ]                 \/\/library or schema name
    \nDBTBL[ CUSTOMERS ]                    \/\/table or file name
    \n)<\/li>\n
  2. To-DB Info:
    \nTODB(
    \nDBTYP[ 3 ]                                          \/\/JDBC Driver index
    \nDBSYS[ 192.168.0.120 ]                    \/\/JDBC url to system\/DBaseServer
    \nDBUSR[ SQL_User ]                          \/\/user ID for logon
    \nDBPWD[ SQL_Password ]                 \/\/password for logon
    \nDBLIB[ SQLSCHEMA ]                    \/\/schema or library name
    \nDBTBL[ CUSTOMERS ]                   \/\/table or file name
    \n)<\/li>\n
  3. Create new table?                     CRTTBL(N)   \/\/ (N=No, Y=Yes) <\/li>\n
  4. Replace\/Add records?             RCDADD(R)  \/\/ (A=Add, R=Replace, N=None)<\/li>\n
  5. Display debug output? DSPMSG(N)  \/\/ (N=No, Y=Yes)<\/li>\n<\/ol>\n

    Example Execution Syntax<\/h3>\n

    Example Execution Syntax (from Remote iSeries DB to MS SQL Server):<\/p>\n

    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)\"<\/code><\/p>\n

    Example Execution Syntax (from Local iSeries DB to MS SQL Server for m-Power):<\/p>\n

    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)\"<\/code><\/p>\n

    Classpath Requirements<\/h3>\n

    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.<\/p>\n

    JDBC Driver required for various databases:<\/p>\n

    (0) Local access iSeries DB2<\/p>\n

         db2_classes.jar<\/p>\n

    (1) Remote access iSeries DB2<\/p>\n

         jt400.jar<\/p>\n

    (3) MS SQL Server<\/p>\n

       Version 2003:<\/p>\n

    msbase.jar<\/p>\n

    mssqlserver.jar<\/p>\n

    msutil.jar<\/p>\n

      Version 2005:<\/p>\n

    sqljdbc.jar<\/p>\n

    (4) Oracle Server<\/p>\n

         ojdbc14.jar<\/p>\n

    (5) Windows DB2<\/p>\n

         db2java.jar<\/p>\n

         db2jcc.jar<\/p>\n

         db2jcc_license_cu.jar<\/p>\n

    (7) MySql Server<\/p>\n

    mysql-connector-java-3.0.11-stable-bin.jar<\/p>\n

    More Example Syntax<\/h3>\n

    More examples for calling Java CopyFile Feature for various databases:<\/p>\n

    <\/p>\n

    *LOCAL DB2 on iSeries <==>192.168.0.170 remote DB2 on iSeries
    \ndb2_classes.jar
    \njt400.jar
    \njava --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])\"
    \n\" TODB( DBTYP[1] DBSYS[192.168.0.170] DBUSR[] DBPWD[] DBLIB[DEVTESTLIB] DBTBL[LTZFILE3])\"
    \n\" CRTTBL( N)\" \"RCDADD(R)\" \"DSPMSG(YES)\"<\/p>\n

    <\/code><\/p>\n

    *LOCAL DB2 on iSeries <==>192.168.0.120 MS SQL Server<\/code> (turn Windows firewall off on port 1433)
    \ndb2_classes.jar
    \nmsbase.jar\/mssqlserver.jar\/msutil.jar
    \njava --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
    \n\"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)\"<\/code><\/p>\n

    <\/p>\n

    *LOCAL DB2 on iSeries <==>192.168.0.120:1521 :mrcora Oracle<\/code> (turn Windows firewall off on port 1521)
    \ndb2_classes.jar
    \nojdbc14.jar
    \njava --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
    \n\"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)\"<\/code><\/p>\n

    <\/p>\n

    *LOCAL DB2 on iSeries <==>192.168.0.120::50000\/mrcdb2 Oracle<\/code> (turn Windows firewall off on port 3306)
    \ndb2_classes.jar
    \ndb2java.jar\/db2jcc.jar\/db2jcc_license_cu.jar
    \njava --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
    \n\"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)\"<\/code><\/p>\n

    iSeries CL command example<\/h3>\n

    COPYFILEEX Source:<\/p>\n

    \/* EXAMPLE CL PROGRAM TO CALL MRC JAVA COPYFILE FEATURE *\/
    \nPGM
    \n            DCL VAR (&CCSID) TYPE(*DEC) LEN(5 0) VALUE(0)
    \n            DCL VAR (&CLASSPATH) TYPE(*CHAR) LEN(9000)
    \n            DCL       VAR (&QSH) TYPE(*CHAR) LEN(9000)
    \n            DCL       VAR (&FDBTYP) TYPE(*CHAR) LEN(25)
    \n            DCL       VAR (&FDBSYS) TYPE(*CHAR) LEN(25)
    \n            DCL       VAR (&FDBUSR) TYPE(*CHAR) LEN(25)
    \n            DCL       VAR (&FDBPWD) TYPE(*CHAR) LEN(25)
    \n            DCL       VAR (&FDBLIB) TYPE(*CHAR) LEN(25)
    \n            DCL       VAR (&FDBTBL) TYPE(*CHAR) LEN(25)
    \n            DCL       VAR (&TDBTYP) TYPE(*CHAR) LEN(25)
    \n            DCL       VAR (&TDBSYS) TYPE(*CHAR) LEN(25)
    \n            DCL       VAR (&TDBUSR) TYPE(*CHAR) LEN(25)
    \n            DCL       VAR (&TDBPWD) TYPE(*CHAR) LEN(25)
    \n            DCL       VAR (&TDBLIB) TYPE(*CHAR) LEN(25)
    \n            DCL       VAR (&TDBTBL) TYPE(*CHAR) LEN(25)
    \n\/* WITH V5R3, TOMCAT RUNNING UNDER CCSID 65535 IS NOT VALID, PER IBM *\/
    \n            RTVJOBA   CCSID( &CCSID)
    \n            IF        COND( &CCSID *EQ 65535) THEN(DO)
    \n            CHGJOB    CCSID( 37)
    \n            ENDDO
    \n\/* Set the base classpath *\/
    \n            CHGVAR    VAR( &CLASSPATH) +
    \nVALUE( '\/mrcjava\/WEB-INF\/lib\/mrcjs11.jar:\/mr+
    \ncjava\/WEB-INF\/servlet.jar ')
    \n\/* Append any jar files needed for specific DB connection*\/
    \n   CHGVAR    VAR( &CLASSPATH) VALUE(&CLASSPATH *TCAT +
    \nDBSYS[ *LOCAL] DBUSR[] DBPWD[] +
    \nDBLIB[ CROWLEY ] DBTBL[DMCMP100])\" +
    \n            \" TODB( DBTYP[1] DBSYS[XX.XX.XXX.XXX] +
    \nDBUSR[ sals ] DBPWD[sa1ls] +
    \nDBLIB[ MRCAPPLLIB] DBTBL[DMCMP100])\" +
    \n\"CRTTBL(Y)\" \"RCDADD(R)\" \"DSPMSG(N)\"')  *\/
    \nQSH        CMD( &QSH)
    \n     \/* WELL-FORMATTED EXAMPLE: +
    \n              QSH       CMD( 'java --cp +
    \n    \/mrcjava\/web-inf\/lib\/mrcjs11.jar:\/mrcjava\/w+
    \n    eb-inf\/servlet.jar:\/mrcjava\/web-inf\/lib\/jt4+
    \n    00 .jar :\/mrcjava\/web-inf\/lib\/msbase.jar:\/mrc+
    \n    java\/web- inf\/lib\/mssqlserver.jar:\/mrcjava\/w +
    \nweb-inf\/lib\/util.jar CopyFile +
    \n    \" FROMDB( DBTYP[0] DBSYS[*LOCAL] +
    \n    ':\/mrcjava\/WEB-INF\/lib\/jt400.jar')
    \n            CHGVAR    VAR( &FDBTYP) VALUE('0')
    \n            CHGVAR    VAR( &FDBSYS) VALUE('*LOCAL')
    \n            CHGVAR    VAR( &FDBUSR) VALUE(' ')
    \n            CHGVAR    VAR( &FDBPWD) VALUE(' ' )
    \n            CHGVAR    VAR( &FDBLIB) VALUE(' CROWLEY ')
    \n            CHGVAR    VAR( &FDBTBL) VALUE('DMCMP100')
    \n            CHGVAR    VAR( &TDBTYP) VALUE('1')
    \n            CHGVAR    VAR( &TDBSYS) VALUE('XX.XX.XXX.XXX')
    \n            CHGVAR    VAR( &TDBUSR) VALUE('SALS')
    \n            CHGVAR    VAR( &TDBPWD) VALUE('SA1LS')
    \n            CHGVAR    VAR( &TDBLIB) VALUE('MRCAPPLLIB')
    \n            CHGVAR    VAR( &TDBTBL) VALUE('DMCMP100')
    \n  CHGVAR    VAR( &QSH) VALUE('java -cp ' *CAT &CLASSPATH +
    \n                        *TCAT ' CopyFile +
    \n               \" FROMDB( DBTYP[' *TCAT &FDBTYP *TCAT '] +
    \nDBSYS[ ' *TCAT &FDBSYS *TCAT '] +
    \nDBUSR[ ' *TCAT &FDBUSR *TCAT '] +
    \nDBPWD[ ' *TCAT &FDBPWD *TCAT '] +
    \nDBLIB[ ' *TCAT &FDBLIB *TCAT '] +
    \nDBTBL[ ' *TCAT &FDBTBL *TCAT '])\" +
    \n               \" TODB( DBTYP[' *TCAT &TDBTYP *TCAT '] +
    \nDBSYS[ ' *TCAT &TDBSYS *TCAT '] +
    \nDBUSR[ ' *TCAT &TDBUSR *TCAT '] +
    \nDBPWD[ ' *TCAT &TDBPWD *TCAT '] +
    \nDBLIB[ ' *TCAT &TDBLIB *TCAT '] +
    \nDBTBL[ ' *TCAT &TDBTBL *TCAT '])\" +
    \n\"CRTTBL(Y)\" \"RCDADD(R)\" \"DSPMSG(N)\"')
    \n\/* CHGVAR   VAR( &QSH) VALUE('java -cp ' *CAT &CLASSPATH +
    \n                        *TCAT ' CopyFile \" FROMDB( DBTYP[0] +
    \nDBUSR[ ] DBPWD[] +
    \nDBLIB[ CROWLEY ] DBTBL[DMCMP100])\" +
    \n    \" TODB( DBTYP[1] DBSYS[XX.XX.XXX.XXX] +
    \nDBUSR[ sals ] DBPWD[sa1ls] DBLIB[MRCAPPLLIB] +
    \nDBTBL[ DMCMP100])\" \"CRTTBL(Y)\" \"RCDADD(R)\" +
    \n    \" DSPMSG( N)\"')      *\/
    \n ENDPGM: ENDPGM<\/code><\/p>\n

    <\/p>\n","protected":false},"excerpt":{"rendered":"

    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. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":2203,"menu_order":1,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-2005","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages\/2005","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/comments?post=2005"}],"version-history":[{"count":4,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages\/2005\/revisions"}],"predecessor-version":[{"id":3684,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages\/2005\/revisions\/3684"}],"up":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages\/2203"}],"wp:attachment":[{"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/media?parent=2005"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}