Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Private Memory on Db2 results in -930 errors at load #1527

Closed
prb112 opened this issue Sep 28, 2020 · 2 comments · Fixed by #1677
Closed

Private Memory on Db2 results in -930 errors at load #1527

prb112 opened this issue Sep 28, 2020 · 2 comments · Fixed by #1677
Assignees
@prb112 @punktilious
Labels
bug Something isn't working persistence
Milestone
Sprint 20

Comments

@prb112
Copy link
Contributor

prb112 commented Sep 28, 2020 •
edited
Loading

Describe the bug
Private Memory on Db2 results in -930 errors at load

Further, the package cache and catalog cache runs out of space in a shared memory environment.

To Reproduce
Steps to reproduce the behavior:

  1. Go to create an instance of db2 in a shared environment such as IBM Cloud
  2. Run a synthetic load with multiple bundles
[err] com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-930, SQLSTATE=57011, SQLERRMC=null, DRIVER=4.26.14
[err] 	at com.ibm.db2.jcc.am.b7.a(b7.java:815)
[err] 	at com.ibm.db2.jcc.am.b7.a(b7.java:66)
[err] 	at com.ibm.db2.jcc.am.b7.a(b7.java:140)
[err] 	at com.ibm.db2.jcc.am.k4.b(k4.java:2471)
[err] 	at com.ibm.db2.jcc.am.k4.c(k4.java:2452)
[err] 	at com.ibm.db2.jcc.t4.ab.n(ab.java:914)
[err] 	at com.ibm.db2.jcc.t4.ab.f(ab.java:156)
[err] 	at com.ibm.db2.jcc.t4.p.e(p.java:81)
[err] 	at com.ibm.db2.jcc.t4.av.k(av.java:175)
[err] 	at com.ibm.db2.jcc.am.k4.ao(k4.java:2413)
[err] 	at com.ibm.db2.jcc.am.k5.a(k5.java:4686)
[err] 	at com.ibm.db2.jcc.am.CallableStatement.b(CallableStatement.java:134)
[err] 	at com.ibm.db2.jcc.am.CallableStatement.a(CallableStatement.java:103)
[err] 	at com.ibm.db2.jcc.am.CallableStatement.execute(CallableStatement.java:93)
[err] 	at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.execute(WSJdbcPreparedStatement.java:390)
[err] 	at com.ibm.fhir.persistence.jdbc.dao.impl.ResourceDAOImpl.insert(ResourceDAOImpl.java:528)
[err] 	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.create(FHIRPersistenceJDBCImpl.java:332)
[err] 	at com.ibm.fhir.server.util.FHIRRestHelper.doCreate(FHIRRestHelper.java:218)
[err] 	at com.ibm.fhir.server.util.FHIRRestHelper.processEntriesForMethod(FHIRRestHelper.java:1781)
[err] 	at com.ibm.fhir.server.util.FHIRRestHelper.processBundleEntries(FHIRRestHelper.java:1475)
[err] 	at com.ibm.fhir.server.util.FHIRRestHelper.doBundle(FHIRRestHelper.java:1068)
[err] 	at com.ibm.fhir.server.resources.Batch.bundle(Batch.java:77)
[err] 	at com.ibm.fhir.server.resources.Batch$Proxy$_$$_WeldClientProxy.bundle(Unknown Source)
[err] 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[err] 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
[err] 	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[err] 	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
[err] 	at com.ibm.ws.jaxrs20.cdi.component.JaxRsFactoryImplicitBeanCDICustomizer.serviceInvoke(JaxRsFactoryImplicitBeanCDICustomizer.java:339)
[err] 	at [internal classes]
[err] 	at com.ibm.fhir.server.filter.rest.FHIRRestServletFilter.doFilter(FHIRRestServletFilter.java:139)
[err] 	at javax.servlet.http.HttpFilter.doFilter(HttpFilter.java:127)
[err] 	at [internal classes]
[err] 	at com.ibm.fhir.server.filter.rest.FHIRRestAuthorizationServletFilter.doFilter(FHIRRestAuthorizationServletFilter.java:86)
[err] 	at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:201)
[err] 	at [internal classes]
[err] 	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
[err] 	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
[err] 	at java.base/java.lang.Thread.run(Thread.java:836)
[ERROR   ] SQLException encountered while inserting Resource.
DB2 SQL Error: SQLCODE=-930, SQLSTATE=57011, SQLERRMC=null, DRIVER=4.26.14

Expected behavior
Solution

  • SELECT * FROM SYSIBMADM.DBCFG WHERE NAME='pckcachesz';

  • CALL SYSPROC.ADMIN_CMD ('update db cfg for bludb using pckcachesz 25000 AUTOMATIC');

  • SELECT * FROM SYSIBMADM.DBCFG WHERE NAME='pckcachesz';

  • SELECT * FROM SYSIBMADM.DBCFG WHERE NAME='catalogcache_sz';

  • CALL SYSPROC.ADMIN_CMD ('update db cfg for bludb using catalogcache_sz 3000');

  • SELECT * FROM SYSIBMADM.DBCFG WHERE NAME='catalogcache_sz';

You must restart the appserver after the change.

Additional context
Debugged in IBM Cloud
@prb112 prb112 added the bug Something isn't working label Sep 28, 2020
@prb112 prb112 changed the title pckcachesz value results in -930 errors catalogcache_sz and pckcachesz value results in -930 errors Sep 28, 2020
@prb112 prb112 self-assigned this Oct 1, 2020
@prb112 prb112 modified the milestones: Sprint 18, Sprint 19 Oct 1, 2020
@prb112 prb112 changed the title catalogcache_sz and pckcachesz value results in -930 errors Private Memory on Db2 results in -930 errors at load Oct 7, 2020
@prb112 prb112 modified the milestones: Sprint 19, Sprint 20 Oct 27, 2020
@prb112
Copy link
Contributor Author

prb112 commented Nov 2, 2020

Solution defined, need to review approach with Teammembers.

prb112 added a commit that referenced this issue Nov 7, 2020
@prb112
Private Memory on Db2 results in -930 errors at load #1527
24f121f 
This error is only on the Db2 and isolated to the Db2 stored procedure - add_any_resource.

Scenario
- Load a Bundle with multiple if not hundreds of resources in a simultaneous way within a single Connection
- Note, the Bundle can be set as Transaction or Batch.
- Db2 with 8GB of memory without INSTANCE_MEMORY=Automatic
- The simultaneous load with ~10 to 20 threads.
- The result is *SQL0930N There is not enough storage available to process the statement*.

In the underlying Memory Allocation, the 2GB size cause a block of memory to be reserved, where it accumulates. So if a new large request comes up requiring substantial chunk of memory, this chunk can only be freshly acquired from so far unused range of addresses, or it may fail.  If the p_payload is lowered to 10M, then the code operates as expected and the same workload can function with 200 threads. Note, it's dependent on the number of CALL to add_any_resource.

This PR is opened to discuss in more detail on an approach.  I'd like to lower the 2GB to 10M.  If needed we can branch to a LARGE size Stored Procedure if the file is larger than 10M.

Reference: https://www.ibm.com/support/pages/db2-content-management-application-may-receive-sql0930n
Signed-off-by: Paul Bastide <pbastide@us.ibm.com>
@prb112 prb112 linked a pull request Nov 7, 2020 that will close this issue
Private Memory on Db2 results in -930 errors at load #1527 #1677
Merged
@lmsurpre
Copy link
Member

Copying robin's proposal from the PR discussion:

Proposed solution: Limit the stored procedure to 1MB (to keep the memory requirements low). If the data payload is small (<1MB) then pass it to the procedure. If the payload is large, pass NULL to the procedure, and simply execute an UPDATE to the resources right after. This way, we keep the benefit of fewer round-trips to the database for the majority of cases, but aren't limiting our payload size due to the stored proc memory restrictions.

prb112 added a commit that referenced this issue Nov 13, 2020
@prb112
Private Memory on Db2 results in -930 errors at load #1527
a9b353d 
- Implement recommendations from Code Review
- Add Tests
- Explicit setting for the BLOB size

Signed-off-by: Paul Bastide <pbastide@us.ibm.com>
prb112 added a commit that referenced this issue Nov 15, 2020
@prb112
Private Memory on Db2 results in -930 errors at load #1527
cb76e7e 
Signed-off-by: Paul Bastide <pbastide@us.ibm.com>
prb112 added a commit that referenced this issue Nov 16, 2020
@prb112
Private Memory on Db2 results in -930 errors at load #1527
7a691a9 
Signed-off-by: Paul Bastide <pbastide@us.ibm.com>
prb112 added a commit that referenced this issue Nov 17, 2020
@prb112
Private Memory on Db2 results in -930 errors at load #1527
e185382 
Signed-off-by: Paul Bastide <pbastide@us.ibm.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@prb112 prb112

@punktilious punktilious

Labels
bug Something isn't working persistence
Projects
None yet
Milestone
Sprint 20
Development

Successfully merging a pull request may close this issue.

Private Memory on Db2 results in -930 errors at load #1527 LinuxForHealth/FHIR
3 participants
@prb112 @lmsurpre @punktilious