Thursday, April 28, 2022

HCL Connections 7.0 : how fix possible issue on side by side database migration with dbt.jar for Wikis,Files and Metrics db

In this period I'm working on HCL Connections migrations from 6.0 to 7.0 and I'm using the side-by-side approach. While I'm during this job, I prefer to use the dbt.jar for database migrations because this could help me identify and fix issues at db schema level.

In this migrations, I had some issues with Wikis, Files (the same for both db), and  Metrics,


Wikis and Files
For these 2 databases I got this error while the copy of the tables was running:

ERROR: Error occurred gathering data from the source database
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=LCUSER;SELECT;FILES.EVENT_STORE, DRIVER=4.29.24

after some investigation, I saw these 3 tables were involved

EVENT_PROCESSING
EVENT_STORE
FILE_LOCATION

LCUSER wasn't able to read these tables because grant issue.
These tables are new from CR5 and weren't granted to LCUSER inside the scripts (but the grants were present inside appGrants.sql from version 7.0)

I've fixed the issue running the following configurations on source and destinations WIKIS and FILES database:

GRANT DELETE,INSERT,SELECT,UPDATE ON "FILES"."EVENT_STORE" TO USER [email protected]
GRANT DELETE,INSERT,SELECT,UPDATE ON "FILES"."EVENT_PROCESSING" TO USER [email protected]
GRANT DELETE,INSERT,SELECT,UPDATE ON "FILES"."FILE_LOCATION" TO USER [email protected]



GRANT DELETE,INSERT,SELECT,UPDATE ON "WIKIS"."EVENT_STORE" TO USER [email protected]
GRANT DELETE,INSERT,SELECT,UPDATE ON "WIKIS"."EVENT_PROCESSING" TO USER [email protected]
GRANT DELETE,INSERT,SELECT,UPDATE ON "WIKIS"."FILE_LOCATION" TO USER [email protected]


Metrics
I was getting the following error while tables data copy was running

com.ibm.db2.jcc.am.SqlTransactionRollbackException: Error for batch element #1: DB2 SQL Error: SQLCODE=-1476, SQLSTATE=40506, SQLERRMC=-964, DRIVER=4.29.24 ERRORCODE=-4225, SQLSTATE=null mber of the batch..755 CEST] error.executing.transfer Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null com.ibm.db2.jcc.am.BatchUpdateException: [jcc][t4][102][10040][4.29.24] 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. ERRORCODE=-4229, SQLSTATE=null

After some investigation inside the db2diag.log of the destination instance database I found this errors:


FUNCTION: DB2 UDB, data protection services, sqlpgResSpace, probe:1660 MESSAGE : ADM1822W The active transaction log is being held by dirty pages. Database performance may be impacted. MESSAGE : ZRC=0x870F0035=-2029060043=SQLO_NOTAVAIL "Cannot honor the conditional request" DIA8531C A memory access error has occurred. MESSAGE : ZRC=0x85100009=-2062548983=SQLP_NOSPACE "Log File has reached its saturation point" DIA8309C Log file was full.

These errors pointed me to the database log config, so after having recreated the destination Metrics DB, I run this configuration command :

db2 update db cfg for METRICS using logfilsiz 16000 logprimary 26 logsecond 6

after this , also METRICS migration was able to complete



No comments:

Post a Comment