asked on

DB2 database-transfer fails from derby

Plattform websphere: Solaris sun 9 on sparc
plattform DB2: windows server 2003 64 bits

DB2 level:
PTF number: WR21407
Build level: s080122
Prerequisite: V9.1 GA
Signature: 9.1.401.444
Release Date:14 Feb 2008

Websphere level:
WAS/WP 6.1.0.1.19 /6.1.0.1.1

I have set up the databases as the info center has told me to. I have not changed the db2cli.ini file as this is a type-4 driver in use. (i have tried with setting this up).

When i run the task ./ConfigEngine.bat database-transfer

I get this error every time:

[java] [04/17/09 14:28:26.718 CEST] Transferring table --{ RELEASE.PORT_WIRE}-- to table --{RELEASE.PORT_WIRE }--
[java] [04/17/09 14:28:26.829 CEST] Transferring table --{ RELEASE.WPS_SCDTASK}-- to table --{RELEASE.WPS_SCDTASK }--
[java] [04/17/09 14:28:27.255 CEST] error.executing.transfer
[java] err.dbtransfer.exception.labelclass com.ibm.db2.jcc.b.vd: Non-atomic batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements.
[java] com.ibm.db2.jcc.b.vd: Non-atomic batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements.
[java] at com.ibm.db2.jcc.b.i.a(i.java:390)
[java] at com.ibm.db2.jcc.b.ad.b(ad.java:2863)
[java] at com.ibm.db2.jcc.b.ad.a(ad.java:2632)
[java] at com.ibm.db2.jcc.b.ad.executeBatch(ad.java:2444)
[java] at com.ibm.db2.jcc.b.ad.executeBatch(ad.java:1329)
[java] at com.ibm.wps.config.db.transfer.DefaultTableHandler.execute(DefaultTableHandler.java:147)
[java] at com.ibm.wps.config.db.transfer.TransferEngine.execute(TransferEngine.java:57)
[java] at com.ibm.wps.config.db.transfer.CmdLineTransfer.execute(CmdLineTransfer.java:96)
[java] at com.ibm.wps.config.db.transfer.CmdLineTransfer.main(CmdLineTransfer.java:43)

BUILD FAILED
/apps/prog/NewPortal/wp_profile/ConfigEngine/base_dynamic.xml:828: The following error occurred while executing this line:
/apps/prog/NewPortal/wp_profile/ConfigEngine/config/actions/db_cfg_mgr.xml:303: The following error occurred while executing this line:
/apps/prog/NewPortal/PortalServer/base/wp.db.impl/config/includes/wp.db.impl_cfg.xml:105: The following error occurred while executing this line:
/apps/prog/NewPortal/wp_profile/ConfigEngine/config/actions/dbt_dbcfg.xml:79: Java returned: 1

Total time: 51 minutes 37 seconds
isIseries currently set to: null
uploading registry

I have checked all the properties files, and the validate-db2 and connection comes out with sucess.

Db2 type driver is Type-4

I dont have any possabilty to use type-2 driver as my old portal is on the same server using this to connect to a different db on another server.

What can i be doing wrong?

ASKER CERTIFIED SOLUTION

sashadelenn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sashadelenn

ASKER

well, as noone could help me with this I will close this case.

As a update I would like to say we decided to use type-2 driver as type-4 didnt work. The portal is now working against the DB2 server OK.

But for future ref. It would be nice to know if others experince also problems with the type-4 driver. And if there is a workaround, as this is where we would like to be when we have migrated to windows plattform.

jmoreno8238

Hi sorry that I didn't find your post soon enough.

I had the same problem the issue seems to be an error while transfering to BLOB columns on batch statements.

"If the universal JDBC driver is performing deferred prepares without the 1 statement retry logic, setBytes() against a BLOB column 1 would yield a -301 error. setObject() (with 1 Types.BLOB), setBinaryStream(), or 1 setBlob() should be used against BLOB columns when 1 deferPrepares is enabled to avoid the internal retry.

1 The statement retry logic is not performed for batched executes. 1 Therefore input data types must match the described column types for all 1 batched parameters. "

(http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0010273.htm)

You must send the deferPrepares=false; in the url (i.e. jdbc:db2://*:*/*:returnAlias=0;deferPrepares=false;)
This is in your wkplc_comp.properties file.

You'll see that the file advises you to use the returnAlias, but maybe you can remove it and only use deferPrepares. Try it.

If you take a look at your Database_Transfer.log.0 log file you might see something like this...

[05/12/09 01:04:33.123 AST] Nested Exceptions: 
[05/12/09 01:04:33.124 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #0: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.124 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #1: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.124 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #2: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.124 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #3: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.124 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #4: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.124 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #5: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.124 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #6: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.124 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #7: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.125 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #8: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.125 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #9: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.125 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #10: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.125 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #11: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.125 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #12: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.125 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #13: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.125 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #14: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.125 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #15: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.125 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #16: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.125 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #17: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.126 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #18: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.126 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #19: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.126 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #20: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.126 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #21: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.126 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #22: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.126 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #23: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.126 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #24: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.126 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #25: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.126 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #26: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.126 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #27: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.127 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #28: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.127 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #29: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.127 AST] com.ibm.db2.jcc.a.SqlException: Error for batch element #30: DB2 SQL error: SQLCODE: -301, SQLSTATE: 07006, SQLERRMC: 6
 
[05/12/09 01:04:33.127 AST] error.executing.transfer
err.dbtransfer.exception.labelclass com.ibm.db2.jcc.a.je: Non-atomic batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements.
com.ibm.db2.jcc.a.je: Non-atomic batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements.
	at com.ibm.db2.jcc.a.j.a(j.java:414)
	at com.ibm.db2.jcc.a.kd.b(kd.java:3103)
	at com.ibm.db2.jcc.a.kd.a(kd.java:2872)
	at com.ibm.db2.jcc.a.kd.executeBatch(kd.java:2684)
	at com.ibm.db2.jcc.a.kd.executeBatch(kd.java:1347)
	at com.ibm.wps.config.db.transfer.DefaultTableHandler.execute(DefaultTableHandler.java:147)
	at com.ibm.wps.config.db.transfer.TransferEngine.execute(TransferEngine.java:57)
	at com.ibm.wps.config.db.transfer.CmdLineTransfer.execute(CmdLineTransfer.java:96)
	at com.ibm.wps.config.db.transfer.CmdLineTransfer.main(CmdLineTransfer.java:43)

Open in new window

sashadelenn

ASKER

Wow.. thanks. i will try this solution! :)

jmoreno8238

Just an Update I tried it yesterday and if you were getting the same error than me then this definitely worked for me:

jdbc:db2://<YourDatabaseServer>:50001/wpsdb:deferPrepares=false;returnAlias=0;

I think that the type 2 driver works because your need a client running, and the client manages the connections, but with the type 4 driver you connect directly, so thats where the problems start.

If my post was useful maybe you can update the ranking so that this thread can be more usefull to others.

sashadelenn

ASKER

an update to this case. After failing alot I fixed this problem on the DB server.

By doing this:
editing for every db

db2 UPDATE DB CFG FOR release using LOGSECOND 20
db2 UPDATE DB CFG FOR comune using LOGSECOND 20
db2 UPDATE DB CFG FOR custome using LOGSECOND 20
db2 UPDATE DB CFG FOR jcrdb51 using LOGSECOND 20
db2 UPDATE DB CFG FOR wps51 using LOGSECOND 20
db2 UPDATE DB CFG FOR wmm51 using LOGSECOND 20
db2 UPDATE DB CFG FOR lmdb51 using LOGSECOND 20
db2 UPDATE DB CFG FOR fdbk51 using LOGSECOND 20
db2 UPDATE DB CFG FOR jcrdb51 using LOGSECOND 20

then database-transfer worked.

Forgot to post this ages ago :)

jmoreno8238

Good find, but I would have thought that everything that you needed was in the scripts for creating the databases that are in the info center.

Did you use those scripts? For the looks of your naming conventions it seams that you were re-using soe older dbs maybe.

Anyway just curious because the scripts worked for me, but maybe you needed to do some tweak because of your particular setup.

sashadelenn

ASKER

i followed to scripts, but found a site ages ago about increasing logsecond. I regret I can not find the webpage anymore.

jmoreno8238

Oh ok well good find then hopefully that'll help someone else.

Glad that you got everything worked out.