[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3767
  • Last Modified:

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?
0
sashadelenn
Asked:
sashadelenn
  • 5
  • 4
1 Solution
 
sashadelennAuthor Commented:
i found that my db2 v9.4 was one lvl to low for websphere portal 6.1.1.0 so i upgraded to db2 v9.5.

After this I get this error:

trying again: error:
action-process-constraints:
Sat Apr 18 23:31:03 CEST 2009
     [java] [04/18/09 23:31:04.091 CEST] Attempting to create a new Instance of com.ibm.db2.jcc.DB2Driver
     [java] [04/18/09 23:31:04.174 CEST] Instance of com.ibm.db2.jcc.DB2Driver created successfully
     [java] [04/18/09 23:31:04.179 CEST] Attempting to make connection using: jdbc:db2://mollytest.sio.no:60000/release:returnAlias=0; :: db2admin :: PASSWORD_REMOVED
     [java] [04/18/09 23:31:07.910 CEST] Connection successfully made
     [java] [04/18/09 23:31:08.031 CEST] ERROR: Error occurred gathering data from the source database
     [java] com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -443, SQLSTATE: 38553, SQLERRMC: SYSIBM.SQLTABLES;TABLES;SYSIBM:CLI:-805
     [java]     at com.ibm.db2.jcc.b.zc.d(zc.java:1351)
     [java]     at com.ibm.db2.jcc.a.db.l(db.java:366)
     [java]     at com.ibm.db2.jcc.a.db.e(db.java:102)
     [java]     at com.ibm.db2.jcc.a.r.e(r.java:72)
     [java]     at com.ibm.db2.jcc.a.sb.i(sb.java:194)
     [java]     at com.ibm.db2.jcc.b.zc.q(zc.java:1319)
     [java]     at com.ibm.db2.jcc.b.ad.d(ad.java:2281)
     [java]     at com.ibm.db2.jcc.b.bd.U(bd.java:188)
     [java]     at com.ibm.db2.jcc.b.s.a(s.java:7833)
     [java]     at com.ibm.db2.jcc.b.s.a(s.java:6337)
     [java]     at com.ibm.db2.jcc.b.s.getTables(s.java:6247)
     [java]     at com.ibm.wps.config.db.Database.getTablesList(Database.java:284)
     [java]     at com.ibm.wps.config.db.transfer.ConstraintHandler.process(ConstraintHandler.java:175)
     [java]     at com.ibm.wps.config.db.transfer.ConstraintHandler.main(ConstraintHandler.java:284)
     [java] Exception in thread "main" java.lang.Error: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -443, SQLSTATE: 38553, SQLERRMC: SYSIBM.SQLTABLES;TABLES;SYSIBM:CLI:-805
     [java]     at com.ibm.wps.config.db.transfer.ConstraintHandler.process(ConstraintHandler.java:236)
     [java]     at com.ibm.wps.config.db.transfer.ConstraintHandler.main(ConstraintHandler.java:284)
     [java] Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -443, SQLSTATE: 38553, SQLERRMC: SYSIBM.SQLTABLES;TABLES;SYSIBM:CLI:-805
     [java]     at com.ibm.db2.jcc.b.zc.d(zc.java:1351)
     [java]     at com.ibm.db2.jcc.a.db.l(db.java:366)
     [java]     at com.ibm.db2.jcc.a.db.e(db.java:102)
     [java]     at com.ibm.db2.jcc.a.r.e(r.java:72)
     [java]     at com.ibm.db2.jcc.a.sb.i(sb.java:194)
     [java]     at com.ibm.db2.jcc.b.zc.q(zc.java:1319)
     [java]     at com.ibm.db2.jcc.b.ad.d(ad.java:2281)
     [java]     at com.ibm.db2.jcc.b.bd.U(bd.java:188)
     [java]     at com.ibm.db2.jcc.b.s.a(s.java:7833)
     [java]     at com.ibm.db2.jcc.b.s.a(s.java:6337)
     [java]     at com.ibm.db2.jcc.b.s.getTables(s.java:6247)
     [java]     at com.ibm.wps.config.db.Database.getTablesList(Database.java:284)
     [java]     at com.ibm.wps.config.db.transfer.ConstraintHandler.process(ConstraintHandler.java:175)
     [java]     ... 1 more

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:53: The following error occurred while executing this line:
/apps/prog/NewPortal/wp_profile/ConfigEngine/config/actions/dbt_dbcfg.xml:145: Java returned: 1

Total time: 30 minutes 21 seconds
isIseries currently set to: null
uploading registry
0
 
sashadelennAuthor Commented:
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.
0
 
jmoreno8238Commented:
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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sashadelennAuthor Commented:
Wow.. thanks. i will try this solution! :)
0
 
jmoreno8238Commented:
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.
0
 
sashadelennAuthor Commented:
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 :)
0
 
jmoreno8238Commented:
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.
0
 
sashadelennAuthor Commented:
i followed to scripts, but found a site ages ago about increasing logsecond. I regret I can not find the webpage anymore.

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

Glad that you got everything worked out.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now