Caught exception "PegaBulkMover:: SQLException loading database" while running migrate.sh
while running the migrat.sh scripts to migrate rule tables to the new schema, we got the below error while inserting data in the last table. All other tables have been migrated successfully in the new schema and have all the data.
Thu Apr 21 04:27:33 EDT 2016 (INFO): Column Index: 44 Name: PZINSKEY 12 VARCHAR2 Not Nullable 255
Thu Apr 21 04:27:33 EDT 2016 (INFO): Column Index: 45 Name: PZPVSTREAM 2004 BLOB Nullable 4000
Thu Apr 21 04:27:33 EDT 2016 (INFO): PegamoveData: SQL Statement Generated: Insert into PEGA_RULES.PR4_RULE_FILE (PXCREATEDATETIME, PXCREATEOPNAME, PXCREATEOPERATOR, PXCREATESYSTEMID, PXHOSTID, PXINSID, PXINSNAME, PXINSTANCELOCKEDBY, PXINSTANCELOCKEDDATETIME, PXINSTANCELOCKEDKEY, PXOBJCLASS, PXUPDATEDATETIME, PXUPDATEOPNAME, PXUPDATEOPERATOR, PXUPDATESYSTEMID, PYAPPLICATIONNAME, PYBASERULE, PYBUNDLETYPE, PYBUNDLENAME, PYCIRCUMSTANCEDATEPROP, PYCIRCUMSTANCEDATE, PYCIRCUMSTANCEPROP, PYCIRCUMSTANCETYPE, PYCIRCUMSTANCEVAL, PYCLASSNAME, PYFILENAME, PYFILETYPE, PYFILECHECKSUM, PYINTERFACE, PYLABEL, PYMETHODSTATUS, PYRELATIVEPATH, PYRULEAVAILABLE, PYRULEENDS, PYRULENAME, PYRULESET, PYRULESETVERSION, PZRULESETVERSIONMAJOR, PZRULESETVERSIONMINOR, PZRULESETVERSIONPATCH, PYRULESTARTS, PYSORTDATECIRCUMWITHINRSMAJOR, PZIMPLEMENTATIONKEY, PZINSKEY, PZPVSTREAM) Values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Thu Apr 21 04:27:36 EDT 2016 (INFO): PegaBulkMover: Loaded 1000 out of 12132 Rows
Thu Apr 21 04:27:39 EDT 2016 (INFO): PegaBulkMover: Loaded 2000 out of 12132 Rows
Thu Apr 21 04:27:43 EDT 2016 (INFO): PegaBulkMover: Loaded 3000 out of 12132 Rows
Thu Apr 21 04:27:47 EDT 2016 (INFO): PegaBulkMover: Loaded 4000 out of 12132 Rows
Thu Apr 21 04:27:52 EDT 2016 (INFO): PegaBulkMover: Loaded 5000 out of 12132 Rows
Thu Apr 21 04:27:57 EDT 2016 (INFO): PegaBulkMover: Loaded 6000 out of 12132 Rows
Thu Apr 21 04:28:01 EDT 2016 (INFO): PegaBulkMover: Loaded 7000 out of 12132 Rows
Thu Apr 21 04:28:07 EDT 2016 (INFO): PegaBulkMover: Loaded 8000 out of 12132 Rows
Thu Apr 21 04:28:12 EDT 2016 (INFO): PegaBulkMover: Loaded 9000 out of 12132 Rows
Thu Apr 21 04:28:19 EDT 2016 (INFO): PegaBulkMover: Loaded 10000 out of 12132 Rows
Thu Apr 21 04:28:21 EDT 2016 (INFO): Encountered exception during processing!
null
at java.nio.Buffer.nextPutIndex(Buffer.java:513)
at java.nio.DirectByteBuffer.put(DirectByteBuffer.java:291)
at com.pega.pegarules.util.deploy.PegaBulkMover.loadDatabaseTable(PegaBulkMover.java:921)
at com.pega.pegarules.util.deploy.PegaBulkMover.process(PegaBulkMover.java:567)
at com.pega.pegarules.util.deploy.PegaBulkMover.main(PegaBulkMover.java:222)
PegaBulkMover:: SQLException loading Database.
at com.pega.pegarules.util.deploy.PegaBulkMover.loadDatabaseTable(PegaBulkMover.java:1080)
at com.pega.pegarules.util.deploy.PegaBulkMover.process(PegaBulkMover.java:567)
at com.pega.pegarules.util.deploy.PegaBulkMover.main(PegaBulkMover.java:222)
We are upgrading from 6.2 sp1 to 7.1.9 in tomcat and oracle 11g
Scotiabank
CA
Hello Ranesh
Did you have enough space available in the 'BulkMover' directory when the issue occurred? It is recommended that size of the 'BulkMover' directory to be twice of Oracle data files.
Regards,
Sandeep
Capgemini India
IN
Hi Sandeep,
By bulkmover directory u mean the temp directory in the filessystem that we created for this purpose? Or the space available in the tablespace of the DB where the schema is created?
We have checked the space in the DB, there are enough free space available.
Thanks,Ranesh
Cognizant
IN
Bulkmover directory had plenty of space; all *.dmp files were created as expected.
We were running the migrate.sh for the first step of the upgrade that is "migrating rules to the new empty schema"
Its only when the script tries to load the rules into the empty PR4_RULE_FILE we get the error.
We noticed that pr4_rule_file.dmp in the bulkmover directory is almost 1.9 Gigs and its the biggest dmp file there.
All other tables were created properly expect this one PR4_RULE_FILE.
We also ensured enough tablespace in the database.
We have tired twice so far and at both times we got stuck at the exact same point with the same error message.
Please post you suggestions
Cognizant
IN
We verified all the 45 columns in V6 PR4_RULE_FILE and none of them were customized.
For example the column PYLABEL VARCHAR2 (64 BYTE) which is customized sometimes was not touched.
Also there were no ORA-NNNNN errors in the CLI-Migrate / PegaBulkMover logs
FTB
US
Hi Vignesh/ Ranesh,
Please try the if any of the below options resolve the issue.
1) PR4_RULE_FILE table contains RULE-FILE-BINARY, RULE-FILE-TEXT, RULE-FILE-FORM etc type of class instances. Check if there is any huge number of same files are there in multiple rulesets and try to skim those rulesets and clean up the lower version RULE-FILE instances from this table.
2) Since it failed exactly while after importing 10K records, looks like there might be some threshold. Please verify if there is any application specific RULE-FILE-* instances created and see how to minimize them below 10K during migration .
3) Try clean up the static content cache
Regards,
Hi Vignesh/ Ranesh,
Please try the if any of the below options resolve the issue.
1) PR4_RULE_FILE table contains RULE-FILE-BINARY, RULE-FILE-TEXT, RULE-FILE-FORM etc type of class instances. Check if there is any huge number of same files are there in multiple rulesets and try to skim those rulesets and clean up the lower version RULE-FILE instances from this table.
2) Since it failed exactly while after importing 10K records, looks like there might be some threshold. Please verify if there is any application specific RULE-FILE-* instances created and see how to minimize them below 10K during migration .
3) Try clean up the static content cache
Regards,
Rushi kesh
Cognizant
IN
Thanks Rushikesh.
There were large number of instances of Rule-File-Binary in the application.
As a result we got a 1.9 GB pr4_rule_file.dmp in the bulk mover dir while performing the rules migration to an empty schema.
We are working on an approach to purge all unwanted instances of Rule-File-Binary.
Pegasystems Inc.
IN
There are two options you can try moving this big table.
1) Rerun the migrate.bat by removing all the tables from tables.txt file and keep only table which has issues. Make sure that you set only load flag to true in migrateystems.propeties file.
2) Just take a table dump of pr4_rule_file table from old schema using oracle utilities and import into new schema.
Note: Make sure that all other tables are loaded sucessfully.
Cognizant
IN
Thanks for the suggestions Dhev.
Meanwhile our team deleted many unnecessary instances of Rule-File-Binary and ran the migrate.sh again.
At this attempt it loaded all the rule tables successfully. Thanks everyone for your support.
Please find the log for loading PR4_RULE_FILE below.
[java] Mon Apr 25 03:53:45 EDT 2016 (INFO): Column Index: 44 Name: PZINSKEY 12 VARCHAR2 Not Nullable 255
[java] Mon Apr 25 03:53:45 EDT 2016 (INFO): Column Index: 45 Name: PZPVSTREAM 2004 BLOB Nullable 4000
Thanks for the suggestions Dhev.
Meanwhile our team deleted many unnecessary instances of Rule-File-Binary and ran the migrate.sh again.
At this attempt it loaded all the rule tables successfully. Thanks everyone for your support.
Please find the log for loading PR4_RULE_FILE below.
[java] Mon Apr 25 03:53:45 EDT 2016 (INFO): Column Index: 44 Name: PZINSKEY 12 VARCHAR2 Not Nullable 255
[java] Mon Apr 25 03:53:45 EDT 2016 (INFO): Column Index: 45 Name: PZPVSTREAM 2004 BLOB Nullable 4000
[java] Mon Apr 25 03:53:45 EDT 2016 (INFO): PegamoveData: SQL Statement Generated: Insert into PEGA_RULES.PR4_RULE_FILE (PXCREATEDATETIME, PXCREATEOPNAME, PXCREATEOPERATOR, PXCREATESYSTEMID, PXHOSTID, PXINSID, PXINSNAME, PXINSTANCELOCKEDBY, PXINSTANCELOCKEDDATETIME, PXINSTANCELOCKEDKEY, PXOBJCLASS, PXUPDATEDATETIME, PXUPDATEOPNAME, PXUPDATEOPERATOR, PXUPDATESYSTEMID, PYAPPLICATIONNAME, PYBASERULE, PYBUNDLETYPE, PYBUNDLENAME, PYCIRCUMSTANCEDATEPROP, PYCIRCUMSTANCEDATE, PYCIRCUMSTANCEPROP, PYCIRCUMSTANCETYPE, PYCIRCUMSTANCEVAL, PYCLASSNAME, PYFILENAME, PYFILETYPE, PYFILECHECKSUM, PYINTERFACE, PYLABEL, PYMETHODSTATUS, PYRELATIVEPATH, PYRULEAVAILABLE, PYRULEENDS, PYRULENAME, PYRULESET, PYRULESETVERSION, PZRULESETVERSIONMAJOR, PZRULESETVERSIONMINOR, PZRULESETVERSIONPATCH, PYRULESTARTS, PYSORTDATECIRCUMWITHINRSMAJOR, PZIMPLEMENTATIONKEY, PZINSKEY, PZPVSTREAM) Values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[java] Mon Apr 25 03:53:48 EDT 2016 (INFO): PegaBulkMover: Loaded 1000 out of 10387 Rows
[java] Mon Apr 25 03:53:50 EDT 2016 (INFO): PegaBulkMover: Loaded 2000 out of 10387 Rows
[java] Mon Apr 25 03:53:53 EDT 2016 (INFO): PegaBulkMover: Loaded 3000 out of 10387 Rows
[java] Mon Apr 25 03:53:56 EDT 2016 (INFO): PegaBulkMover: Loaded 4000 out of 10387 Rows
[java] Mon Apr 25 03:53:59 EDT 2016 (INFO): PegaBulkMover: Loaded 5000 out of 10387 Rows
[java] Mon Apr 25 03:54:04 EDT 2016 (INFO): PegaBulkMover: Loaded 6000 out of 10387 Rows
[java] Mon Apr 25 03:54:07 EDT 2016 (INFO): PegaBulkMover: Loaded 7000 out of 10387 Rows
[java] Mon Apr 25 03:54:12 EDT 2016 (INFO): PegaBulkMover: Loaded 8000 out of 10387 Rows
[java] Mon Apr 25 03:54:15 EDT 2016 (INFO): PegaBulkMover: Loaded 9000 out of 10387 Rows
[java] Mon Apr 25 03:54:21 EDT 2016 (INFO): PegaBulkMover: Loaded 10000 out of 10387 Rows
[java] Mon Apr 25 03:54:31 EDT 2016 (INFO): PegaBulkMover: Loaded 10387 out of 10387 Rows
[java] Mon Apr 25 03:54:31 EDT 2016 (INFO):
[java] Mon Apr 25 03:54:31 EDT 2016 (INFO): PegaBulkMover: Table + PR4_RULE_FILE Load completed 10387 Rows loaded
[java] Mon Apr 25 03:54:31 EDT 2016 (INFO):
[java] Mon Apr 25 03:54:31 EDT 2016 (INFO): Processed 20 of 22 table(s)
AIG
US
Dhev, do we have limitation while loading "pr4_rule_file"? after removing unwanted stuff from table, it has been migrated. also i feel migrate script need few more logging details, non-db exception like above is difficult troubleshoot.
Pegasystems Inc.
IN
As per I know there is no such limitation on any tables, but rule_file_binary table contains all files including images which makes BLOB bigger. That could have caused the issue with file IO.
Yes I agree that it needs more logging than just showing some error. I will provide this feedback to Data Management team.
Capgemini India
IN
I don't think there is any limitation in the number of rows of the table. We had a project with more than 120K rows successfully migrate the pr4_rule_file table by the scripts without any issues. The limitation may be in the BLOB size as we had plenty of large documents attached in the Application, Use Cases, Requirement rule instances.
USU
DE
got the same error but with pr_rule_file_binary table. In case someone is facing the same error - Resolution is to delete all big custom added rule-file-binary objects.