Streaming Results to Export Large Datasets

[godonnell_ip]

Hi - this is more a question of clarification than a concern that things aren't working right. Apologies in advance if you feel I am re-treading old ground, but having read the documentation and forum posts I feel as though some if it is contradictory and I wonder if that's because of misinterpretation or because things have changed in the framework over time.

Using SmartGWT v6.0 Power (SmartClient Version: v11.0p_2016-10-21/PowerEdition Deployment (built 2016-10-21)) I am trying to validate the settings needed of a server-side DSRequest against a DB2 database running on IBM i to avoid out of memory conditions at the server when exporting large datasets. The export is being written to the server filesystem only.

I'd like to be able to export to both CSV or OOXML, but just one of these will be a good fallback position. I have read that exports to OOXML are automatically streamed, and indeed have observed while stepping the debugger that the output is indeed streamed and debug messages in the log report progress every 1000 records. However, prior to the output being written to disk it appears that a fetch for all matching records is made to the database server and this causes me concern since in earlier versions of our application (SmartGWT v3.1) such behavior has caused huge problems with out of memory conditions. Is this correct and expected?

I have tried setting streamResults on the request, and indeed using the debugger again it does seem as though the code is now trying to stream the results from the DB (I see it calling into SQLDataSource.streamNextRecordAsObject()), but the process terminates with the following exception and leaves me with a Results.xlsx file that contains only the file titles and no data:

=== 2017-08-08 08:02:53,110 [ec-6] WARN StreamingResponseIterator - Error calling nextRecordAsObject, abandoning iteration
com.isomorphic.datasource.StreamingResponseException: Exception trying to stream the next record in a SQLDataSource DSResponse
at com.isomorphic.sql.SQLDataSource._streamNextRecord(SQLDataSource.java:4477)
at com.isomorphic.sql.SQLDataSource.streamNextRecordAsObject(SQLDataSource.java:4444)
at com.isomorphic.datasource.DSResponse.nextRecordAsObject(DSResponse.java:1503)
at com.isomorphic.datasource.StreamingResponseIterator.next(StreamingResponseIterator.java:56)
at com.isomorphic.rpc.ExcelDataExport.getExportObject(ExcelDataExport.java:526)
at com.isomorphic.rpc.DataExport.exportResultSet(DataExport.java:630)
at com.isomorphic.rpc.DataExport.exportResultSet(DataExport.java:454)
at com.isomorphic.rpc.RPCManager.completeResponse(RPCManager.java:1257)
at com.isomorphic.rpc.RPCManager.send(RPCManager.java:694)
at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:187)
at com.islandpacific.gui.server.customDataSource.IpIDACall.processRPCTransaction(IpIDACall.java:67)
at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:152)
at com.isomorphic.servlet.IDACall._processRequest(IDACall.java:119)
at com.isomorphic.servlet.IDACall.doPost(IDACall.java:79)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:162)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at com.islandpacific.gui.server.GWTCacheControlFilter.doFilter(GWTCacheControlFilter.java:47)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:343)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:188)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at com.islandpacific.gui.security.IpConcurrentSessionFilter.doFilter(IpConcurrentSessionFilter.java:55)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:149)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at com.isomorphic.servlet.CompressionFilter._doFilter(CompressionFilter.java:247)
at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:88)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:474)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:495)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:767)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1347)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Cursor state not valid.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:405)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:377)
at com.ibm.as400.access.AS400JDBCResultSet.checkOpen(AS400JDBCResultSet.java:388)
at com.ibm.as400.access.AS400JDBCResultSet.getRow(AS400JDBCResultSet.java:1444)
at org.apache.commons.dbcp.DelegatingResultSet.getRow(DelegatingResultSet.java:370)
at org.apache.commons.dbcp.DelegatingResultSet.getRow(DelegatingResultSet.java:370)
at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:665)
at com.isomorphic.sql.SQLDataSource._streamNextRecord(SQLDataSource.java:4469)
... 70 more

I have tried with CSV exports and am not entirely clear based on the documentation and other forum posts I have read whether streaming is automatic for CSV exports or if I have to turn it on using streamResults. With streamResults=true I get similar results, although the failure occurs when the RPC manager is attempting to export the results. I am left with a zero-byte file named along the lines of Results.csv.tmp.6888@TinyStudio.88.

=== 2017-08-08 08:17:20,351 [ec-8] ERROR IpIDACall - com.islandpacific.gui.server.customDataSource.IpIDACall top-level exception
java.lang.NullPointerException
at com.isomorphic.rpc.DataExport.transformValues(DataExport.java:1021)
at com.isomorphic.rpc.DataExport.exportResultSet(DataExport.java:611)
at com.isomorphic.rpc.DataExport.exportResultSet(DataExport.java:454)
at com.isomorphic.rpc.RPCManager.completeResponse(RPCManager.java:1257)
at com.isomorphic.rpc.RPCManager.send(RPCManager.java:694)
at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:187)
at com.islandpacific.gui.server.customDataSource.IpIDACall.processRPCTransaction(IpIDACall.java:67)
at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:152)
at com.isomorphic.servlet.IDACall._processRequest(IDACall.java:119)
at com.isomorphic.servlet.IDACall.doPost(IDACall.java:79)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:162)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at com.islandpacific.gui.server.GWTCacheControlFilter.doFilter(GWTCacheControlFilter.java:47)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:343)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:188)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at com.islandpacific.gui.security.IpConcurrentSessionFilter.doFilter(IpConcurrentSessionFilter.java:55)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:149)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at com.isomorphic.servlet.CompressionFilter._doFilter(CompressionFilter.java:247)
at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:88)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:474)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:495)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:767)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1347)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)

Finally, I am unclear on the intended difference between streamResults and exportStreaming. Is the former intended to cause the results of the query to be streamed from the database server to the SmartGWT server, while the latter streams the formatted version of those results to the target? Are either of them automatically employed, or do one or both of them need to be explicitly specified?

Thanks in advance for any clarification you can provide.

Regards,

Gary O'Donnell

[Isomorphic]

dsRequest.streamResults can be used for streaming purposes that are not exports. exportStreaming can be used to turn streaming on and off for particular exports. If you've set exportStreaming, you do not have to also set streamResults (and it would be invalid to set it directly, which is why your latter attempt broke).

So the next question is: do you get this weird cursor error if you just set exportStreaming and don't try to directly set streamResults?

[godonnell_ip]

Great - thanks for the response. I wasn't clear that the two were mutually exclusive, and in fact your advice above conflicts with that given in this thread which says:

Setting streamResults only affects the built-in export modes...

Yes, that comment does go on to say that applies to 9.x and that additional APIs had been added in 10.x, but doesn't explicitly reference exportStreaming. So as I said, thanks for clarifying.

I do not get the cursor error when just setting exportStreaming, which is good, and after I posted my question this morning I ran some tests where I first successfully exported 32,000 records and then kicked off an export of 290,000 (to an OOXML file). This latter only just finished an hour ago after running for 7.5hrs, but that negative performance is quite possibly due to me being remote (in AZ) from the DB server (in CA). I'll be repeating the test shortly on a server that is located on the same network as the DB server to get a better idea of performance.

More importantly, though, Tomcat on my development server has access to 16GB. During the test the Old Gen Perm Space consumed up to 4GB. While still being well below the 10GB max space for Old Gen, bear in mind this was a server that had only one user on it with only one such long running request. If three such requests had been issued, would I have seen my old "OutOfMemoryError: PermGen" nemesis in the log?

That aside, the server appeared to continue to respond to interactive use without any major difficulties during the entire export process. Unfortunately, I had forgotten to turn off sending a response to the client and so I got a "Host closed connection" message that I think prevented the stream to disk from occurring (I seem to recall that when both are requested you output the results via a TeeOutputStream, so I suspect an exception in one would also prevent the other from completing successfully). Either way my only artifact was an 11K temporary results file. Hence repeating the test again tonight, although I'll be curious to see what happens since the test instance will have considerably less memory allocated to it.

I'll keep you posted.

By the way, here's a screen capture of the monitoring I was during the export (taken about 20 mins after the server started writing out the results file and then threw the exception I mentioned above). You can see how the PS Old Gen usage gradually increased over time:

[godonnell_ip]

Perhaps I should add that (as you can see) I started the export at around 0940 this morning, yet the export file did not start being written until 1629. This, along with the growth in PS Old Gen space, leads me to question whether or not the records are actually being streamed direct to disk as (I believe) exportStreaming should achieve for me. I think the first 7 hours was spent on extracted the data from the database and transferring it to my server which, based on my observations during debug yesterday, seems to confirm that a full data extract to the SmartClient server is performed before anything is written to disk. Is that expected?

[Isomorphic]

Previous post: there doesn't seem to be any conflicting statements here. Even in that post we indicated that the next version was different, because now streamResults can be used outside of the built-in exports.

Current post: no, fully loading the dataset is of course not expected, as that wouldn't be streaming or solve any problem at all..

We have various automated tests, all passing, that verify the streaming functionality by setting limits on total JVM memory. However, as you have an unusual database, it's always possible that streaming is either not possible, or takes steps not required for other databases. A good check here would be to use streamResults *without* an export, just to minimize the number of layers (Java file system behavior could differ on your platform as well, for example).

[godonnell_ip]

Thanks again for the additional response. I updated one of our test servers located on the same network as the iSeries DB server and the performance was much better - I exported over 750K records to a ~27.5MB OOXML file in 18 minutes. That's way better than my earlier test which is as I expected.

However, I first ran that test with a max of 6GB allocated to Tomcat and I ran out of memory. When I upped the memory to 12GB the export completed successfully as described above. I'm assuming, though, this is way more than is anticipated if streaming is working properly since just a few of these requests would bog down even the biggest server (we have seen these OOM errors on a production server that has a generous 96GB RAM but which anticipates around 500 concurrent users, so just doing some SWAG math gives one pause for thought...).

I have to confess I did wonder if DB2 itself was tripping us up. It's certainly possible, although I was under the impression that DB2 supported results streaming. I'll go with your suggestion for some further tests and will let you know how I get on.

[godonnell_ip]

OK - we may be onto something. I went with your suggestion of trying to simply streamResults and have a little more information which points to it being our fault.

In my first post I'd rather lazily bypassed the fact that the stack trace above is initially caused by a cursor state exception in the AS400 driver. So what does the code look like that throws that exception? Like this:

/*      */   void checkOpen()
/*      */     throws SQLException
/*      */   {
/*  387 */     if (this.closed_)
/*  388 */       JDError.throwSQLException("24000");
/*      */   }

So clearly, based on the stack trace at the top of this thread, checkOpen() is failing while attempting to call getRow(), which implies that streaming is in play, and indeed in my most recent test the debugger confirmed that we are dropping into streaming logic although a similar stack trace is generated:

=== 2017-08-09 08:55:16,043 [c-15] WARN StreamingResponseIterator - Error calling nextRecordAsObject, abandoning iteration
com.isomorphic.datasource.StreamingResponseException: Exception trying to stream the next record in a SQLDataSource DSResponse
at com.isomorphic.sql.SQLDataSource._streamNextRecord(SQLDataSource.java:4477)
at com.isomorphic.sql.SQLDataSource.streamNextRecordAsObject(SQLDataSource.java:4444)
at com.isomorphic.datasource.DSResponse.nextRecordAsObject(DSResponse.java:1503)
at com.isomorphic.datasource.StreamingResponseIterator.next(StreamingResponseIterator.java:56)
at com.isomorphic.js.JSTranslater.convertIterator(JSTranslater.java:1554)
at com.isomorphic.js.JSTranslater.convertIterator(JSTranslater.java:1527)
at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:852)
at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:697)
at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:688)
at com.isomorphic.js.JSTranslater.convertMap(JSTranslater.java:1407)
at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:843)
at com.isomorphic.js.JSTranslater.convertIterator(JSTranslater.java:1574)
at com.isomorphic.js.JSTranslater.convertCollection(JSTranslater.java:1520)
at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:845)
at com.isomorphic.js.JSTranslater.convert(JSTranslater.java:697)
at com.isomorphic.js.JSTranslater.toJS(JSTranslater.java:659)
at com.isomorphic.rpc.RPCManager.completeResponse(RPCManager.java:1683)
at com.isomorphic.rpc.RPCManager.send(RPCManager.java:694)
at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:187)
at com.islandpacific.gui.server.customDataSource.IpIDACall.processRPCTransaction(IpIDACall.java:67)
at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:152)
at com.isomorphic.servlet.IDACall._processRequest(IDACall.java:119)
at com.isomorphic.servlet.IDACall.doPost(IDACall.java:79)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:162)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at com.islandpacific.gui.server.GWTCacheControlFilter.doFilter(GWTCacheControlFilter.java:47)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:343)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:188)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at com.islandpacific.gui.security.IpConcurrentSessionFilter.doFilter(IpConcurrentSessionFilter.java:55)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:149)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at com.isomorphic.servlet.CompressionFilter._doFilter(CompressionFilter.java:260)
at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:88)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:474)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:495)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:767)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1347)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Cursor state not valid.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:405)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:377)
at com.ibm.as400.access.AS400JDBCResultSet.checkOpen(AS400JDBCResultSet.java:388)
at com.ibm.as400.access.AS400JDBCResultSet.getRow(AS400JDBCResultSet.java:1444)
at org.apache.commons.dbcp.DelegatingResultSet.getRow(DelegatingResultSet.java:370)
at org.apache.commons.dbcp.DelegatingResultSet.getRow(DelegatingResultSet.java:370)
at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:665)
at com.isomorphic.sql.SQLDataSource._streamNextRecord(SQLDataSource.java:4469)
... 79 more

So, given that the cursor state exception is caused by the result set being closed, the next question is what closes it? A breakpoint in the close() method allows us to identify that the cursor is closed when the datasource is released. Below is the stack trace where we can see that the instigator is a call to freeDataSource(). This is a method in our dynamic datasource that helps support our internal datasource pooling mechanism. At this point you are probably quite justifiably throwing darts at the screen. Don't worry - I'll be turning off our version of pooling to see if we see the same problem with the native implementation.

I'll let you know.

Daemon Thread [http-nio-8080-exec-19] (Suspended (breakpoint at line 487 in AS400JDBCResultSet))    
    owns: Object  (id=244)    
    owns: PoolableConnection  (id=245)    
    owns: DB2iSeriesDriver  (id=246)    
    owns: PoolingDataSource$PoolGuardConnectionWrapper  (id=247)    
    owns: Collections$SynchronizedRandomAccessList<E>  (id=248)    
    owns: NioEndpoint$NioSocketWrapper  (id=144)    
    AS400JDBCResultSet.close() line: 487 [local variables unavailable]    
    DelegatingResultSet.close() line: 187    
    DelegatingStatement.close() line: 163    
    PoolableConnection(DelegatingConnection).passivate() line: 426    
    PoolableConnectionFactory.passivateObject(Object) line: 693    
    GenericObjectPool<T>.addObjectToPool(T, boolean) line: 1430    
    GenericObjectPool<T>.returnObject(T) line: 1393    
    PoolableConnection.close() line: 90    
    PoolingDataSource$PoolGuardConnectionWrapper.close() line: 191    
    SQLConnectionManager.free(Connection, boolean) line: 344    
    SQLConnectionManager.free(Connection) line: 291    
    DB2iSeriesDriver(SQLDriver).freeConnection() line: 419    
    DB2iSeriesDriver(SQLDriver).clearState() line: 1572    
    ItemDS(SQLDataSource).clearState() line: 646    
    IpDynamicDataSource.freeDataSources() line: 271    
    ItemDS(IpDataSource).execute(DSRequest) line: 247    
    ItemDS.execute(DSRequest) line: 274    
    AppBase.executeDefaultDSOperation() line: 735    
    AppBase.executeAppOperation() line: 652    
    AppBase.execute(DSRequest, RequestContext) line: 493    
    DSRequest.execute() line: 2725    
    IpIDACall(IDACall).handleDSRequest(DSRequest, RPCManager, RequestContext) line: 230    
    IpIDACall.handleDSRequest(DSRequest, RPCManager, RequestContext) line: 128    
    IpIDACall(IDACall).processRPCTransaction(RPCManager, RequestContext) line: 187    
    IpIDACall.processRPCTransaction(RPCManager, RequestContext) line: 67    
    IpIDACall(IDACall).processRequest(HttpServletRequest, HttpServletResponse) line: 152    
    IpIDACall(IDACall)._processRequest(HttpServletRequest, HttpServletResponse) line: 119    
    IpIDACall(IDACall).doPost(HttpServletRequest, HttpServletResponse) line: 79    
    IpIDACall(HttpServlet).service(HttpServletRequest, HttpServletResponse) line: 648    
    IpIDACall(BaseServlet).service(HttpServletRequest, HttpServletResponse) line: 162    
    IpIDACall(HttpServlet).service(ServletRequest, ServletResponse) line: 729    
    ApplicationFilterChain.internalDoFilter(ServletRequest, ServletResponse) line: 230    
    ApplicationFilterChain.doFilter(ServletRequest, ServletResponse) line: 165    
    WsFilter.doFilter(ServletRequest, ServletResponse, FilterChain) line: 53    
    ApplicationFilterChain.internalDoFilter(ServletRequest, ServletResponse) line: 192    
    ApplicationFilterChain.doFilter(ServletRequest, ServletResponse) line: 165    
    GWTCacheControlFilter.doFilter(ServletRequest, ServletResponse, FilterChain) line: 47    
    ApplicationFilterChain.internalDoFilter(ServletRequest, ServletResponse) line: 192    
    ApplicationFilterChain.doFilter(ServletRequest, ServletResponse) line: 165    
    FilterChainProxy$VirtualFilterChain.doFilter(ServletRequest, ServletResponse) line: 343    
    FilterSecurityInterceptor.invoke(FilterInvocation) line: 109    
    FilterSecurityInterceptor.doFilter(ServletRequest, ServletResponse, FilterChain) line: 83    
    FilterChainProxy$VirtualFilterChain.doFilter(ServletRequest, ServletResponse) line: 355    
    ExceptionTranslationFilter.doFilter(ServletRequest, ServletResponse, FilterChain) line: 97    
    FilterChainProxy$VirtualFilterChain.doFilter(ServletRequest, ServletResponse) line: 355    
    SessionManagementFilter.doFilter(ServletRequest, ServletResponse, FilterChain) line: 100    
    FilterChainProxy$VirtualFilterChain.doFilter(ServletRequest, ServletResponse) line: 355    
    SecurityContextHolderAwareRequestFilter.doFilter(ServletRequest, ServletResponse, FilterChain) line: 54    
    FilterChainProxy$VirtualFilterChain.doFilter(ServletRequest, ServletResponse) line: 355    
    RequestCacheAwareFilter.doFilter(ServletRequest, ServletResponse, FilterChain) line: 35    
    FilterChainProxy$VirtualFilterChain.doFilter(ServletRequest, ServletResponse) line: 355    
    UsernamePasswordAuthenticationFilter(AbstractAuthenticationProcessingFilter).doFilter(ServletRequest, ServletResponse, FilterChain) line: 188    
    FilterChainProxy$VirtualFilterChain.doFilter(ServletRequest, ServletResponse) line: 355    
    LogoutFilter.doFilter(ServletRequest, ServletResponse, FilterChain) line: 105    
    FilterChainProxy$VirtualFilterChain.doFilter(ServletRequest, ServletResponse) line: 355    
    SecurityContextPersistenceFilter.doFilter(ServletRequest, ServletResponse, FilterChain) line: 79    
    FilterChainProxy$VirtualFilterChain.doFilter(ServletRequest, ServletResponse) line: 355    
    IpConcurrentSessionFilter.doFilter(ServletRequest, ServletResponse, FilterChain) line: 55    
    FilterChainProxy$VirtualFilterChain.doFilter(ServletRequest, ServletResponse) line: 355    
    FilterChainProxy.doFilter(ServletRequest, ServletResponse, FilterChain) line: 149    
    DelegatingFilterProxy.invokeDelegate(Filter, ServletRequest, ServletResponse, FilterChain) line: 237    
    DelegatingFilterProxy.doFilter(ServletRequest, ServletResponse, FilterChain) line: 167    
    ApplicationFilterChain.internalDoFilter(ServletRequest, ServletResponse) line: 192    
    ApplicationFilterChain.doFilter(ServletRequest, ServletResponse) line: 165    
    CompressionFilter._doFilter(ServletRequest, ServletResponse, FilterChain) line: 260    
    CompressionFilter(BaseFilter).doFilter(ServletRequest, ServletResponse, FilterChain) line: 88    
    ApplicationFilterChain.internalDoFilter(ServletRequest, ServletResponse) line: 192    
    ApplicationFilterChain.doFilter(ServletRequest, ServletResponse) line: 165    
    StandardWrapperValve.invoke(Request, Response) line: 199    
    StandardContextValve.invoke(Request, Response) line: 96    
    NonLoginAuthenticator(AuthenticatorBase).invoke(Request, Response) line: 474    
    StandardHostValve.invoke(Request, Response) line: 140    
    ErrorReportValve.invoke(Request, Response) line: 79    
    AccessLogValve(AbstractAccessLogValve).invoke(Request, Response) line: 624    
    StandardEngineValve.invoke(Request, Response) line: 87    
    CoyoteAdapter.service(Request, Response) line: 349    
    Http11Processor.service(SocketWrapperBase<?>) line: 495    
    Http11Processor(AbstractProcessorLight).process(SocketWrapperBase<?>, SocketEvent) line: 66    
    AbstractProtocol$ConnectionHandler<S>.process(SocketWrapperBase<S>, SocketEvent) line: 767    
    NioEndpoint$SocketProcessor.doRun() line: 1347    
    NioEndpoint$SocketProcessor(SocketProcessorBase<S>).run() line: 49    
    ThreadPoolExecutor(ThreadPoolExecutor).runWorker(ThreadPoolExecutor$Worker) line: not available    
    ThreadPoolExecutor$Worker.run() line: not available    
    TaskThread$WrappingRunnable.run() line: 61    
    TaskThread(Thread).run() line: not available

[godonnell_ip]

Indeed - turning off our pooling mechanism fixes the straight streaming of results. Now to check up on how that affects the export.

Sorry to have distracted you with this non-issue, but I very much appreciate your responses as they prompted me to dig deeper.

UPDATE: it turns out that the test server did not have our version of datasource pooling active and yet the exports I did last night consumed way more memory than I would have expected. It seems I have even more digging to do. Watch this space...

[godonnell_ip]

OK, so here's the final batch of questions. Why final? Because now I have it working.

Having turned off our perhaps ill-considered implementation of datasource pooling I have found that I can turn on streamResults and use it in combination with exportData without any problems which is contrary to your initial advice above (I know - if we hadn't had our datasource pooling solution in the mix you may not have said that streamResults is only used for non-export requests). I had previously turned it off when experiencing the exceptions in my development environment, and it was this implementation I tested on our test server last night, which of course meant that streamResults was false, thus causing the amount of consumed memory to increase.

What I believe is the case is that "streaming" is an overloaded term that refers to the following:

1. Streaming of results from the DB server to the SmartGWT server: Partial results are returned iteratively from the DB Server to the SmartGWT server in order to avoid having to load the entire result set into memory at once. Achieved by setting streamResults=true.
2. Streaming of results from the SmartGWT server to the output stream(s): Partial results are written to the output stream(s) iteratively from the SmartGWT server. This avoids having to construct the response/document entirely in memory. Achieved by setting exportStreaming=true, which is the default for OOXML.

Do I understand this correctly now and is it true (as it seems to be in my tests) that I should have both settings at true in order to keep my memory overhead to a minimum? Also, is exportStreaming also the default for CSV output, or does (as the documentation implies) this setting only affect exports to OOXML? I can see that the CSV export observes streamResults=true.

Finally - as part of my non-export testing that you suggested, I attempted to set streamResults=true for response to a ListGrid's fetch operation. Since the ListGrid is set to use paged mode the first thing that happens under the covers is to determine the total number of records corresponding to the request. This resulted in a NullPointerException stack trace. Is this expected? I could see how they might be considered mutually exclusive, but there is nothing I saw in the documentation to indicate this. If it's not expected I will recreate and post the stack trace.

Thanks again for hanging in there with me on this.

[Isomorphic]

As far as usage, if you are doing one of the built-in exports, set exportStreaming if you want streaming, as that implies streaming both from the DB and to disk (where possible). When doing a built-in export, do not try to set streamResults directly. Any attempt to directly set streamResults in that case would at best be a no-op, at worst, mess up the export by turning it on when it's known to not work, or off when we need it to be on, or just applying the setting at an unexpected moment. This is the same thing we said in less detail before; still true.

In particular, if you want a streamed CSV export, set exportStreaming and you should not have to set streamResults (and again should not do so). If this doesn't seem to be working, let us know.

We'll clarify in the docs which modes are *expected* to work or not.

If you are not doing a built-in export, set streamResults if you want streaming.

Since a ListGrid is requesting a certain range of rows, streamResults would be a useless and unexpected setting. However, if you could show us the NPE, we might want to change how the failure is reported to make it clearer (or even have the setting ignored).

[godonnell_ip]

Ah, this kind of definitive response is great, thanks. I can confirm that I'm not seeing the behavior you describe for either CSV or OOXML exports (yes, I'm using built-in exports only). Below are the debug logs for CSV and OOXML exports respectively. There are two logs for each - one where I explicitly set both exportStreaming and streamResults and the other where I only set exportStreaming.

The key is to look at the entries that follow the SQL statement. Where I set streamResults you can see an entry like:

=== 2017-08-09 20:02:36,141 [c-41] DEBUG SQLDriver - [builtinApplication.substitutePrimaryItems] Streaming the response

...whereas where I do not set streamResults we see something more like:

=== 2017-08-09 19:56:51,876 [c-36] INFO DSResponse - DSResponse: List with 1879 items

...with no reference to streaming.

With the OOXML export we see the same results, but there's more debug logging during the streaming of the output file so while we can see the DB the results were not streamed we DO see that streaming is in play during creation of the file. This is why I asserted in my prior post that I thought I needed to explicitly set streamResults=true to get the memory-conserving behavior that I want.

There's just one more thing I want to eliminate before you spend too much time on this, and that is to take our subclass of SQLDataSource out of play. However, I wanted to document my findings in case you were able to see something obvious.

Here are the results I mentioned:

CSV Export (no streamResults)

=== 2017-08-09 19:54:02,823 [c-36] INFO RequestContext - URL: '/ipgui/ipgui/sc/IDACall/Results.csv', User-Agent: 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36': Safari with Accept-Encoding header
=== 2017-08-09 19:54:02,861 [c-36] DEBUG XML - Parsed XML from (in memory stream): 37ms
=== 2017-08-09 19:54:02,863 [c-36] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'transaction' but null was returned
=== 2017-08-09 19:54:02,864 [c-36] DEBUG PoolableDataSourceFactory - Created DataSource 3981 of type 'Object' and assigned it to thread http-nio-8080-exec-36
=== 2017-08-09 19:54:02,866 [c-36] DEBUG PoolableDataSourceFactory - Created DataSource 3982 of type 'List' and assigned it to thread http-nio-8080-exec-36
=== 2017-08-09 19:54:02,866 [c-36] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'elem' but null was returned
=== 2017-08-09 19:54:02,867 [c-36] DEBUG RPCManager - Processing 1 requests.
=== 2017-08-09 19:54:02,870 [c-36] DEBUG XML - Parsed XML from (in memory stream): 2ms
=== 2017-08-09 19:54:02,872 [c-36] DEBUG PoolableDataSourceFactory - Created DataSource 3983 of type 'DataSource' and assigned it to thread http-nio-8080-exec-36
=== 2017-08-09 19:54:02,876 [c-36] DEBUG PoolableDataSourceFactory - Created DataSource 3984 of type 'DataSourceField' and assigned it to thread http-nio-8080-exec-36
=== 2017-08-09 19:54:02,878 [c-36] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'field' but null was returned
=== 2017-08-09 19:54:02,879 [c-36] DEBUG PoolableDataSourceFactory - Created DataSource 3985 of type 'JS' and assigned it to thread http-nio-8080-exec-36
=== 2017-08-09 19:54:02,879 [c-36] DEBUG PoolableDataSourceFactory - Created DataSource 3986 of type 'Object' and assigned it to thread http-nio-8080-exec-36
=== 2017-08-09 19:54:02,882 [c-36] DEBUG PoolableDataSourceFactory - Created DataSource 3987 of type 'ListGridField' and assigned it to thread http-nio-8080-exec-36
=== 2017-08-09 19:54:02,885 [c-36] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'validator' but null was returned
=== 2017-08-09 19:54:02,894 [c-36] DEBUG PoolableDataSourceFactory - Created DataSource 3988 of type 'Validator' and assigned it to thread http-nio-8080-exec-36
=== 2017-08-09 19:54:02,897 [c-36] DEBUG PoolableDataSourceFactory - Created DataSource 3989 of type 'ServerObject' and assigned it to thread http-nio-8080-exec-36
=== 2017-08-09 19:54:02,898 [c-36] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'serverObject' but null was returned
=== 2017-08-09 19:54:02,900 [c-36] DEBUG PoolableDataSourceFactory - Created DataSource 3990 of type 'ValueMap' and assigned it to thread http-nio-8080-exec-36
=== 2017-08-09 19:54:02,903 [c-36] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'SDC' but null was returned
=== 2017-08-09 19:54:02,907 [c-36] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'pickListCriteria' but null was returned
=== 2017-08-09 19:54:02,923 [c-36] DEBUG PoolableDataSourceFactory - Created DataSource 3991 of type 'OperationBinding' and assigned it to thread http-nio-8080-exec-36
=== 2017-08-09 19:54:02,926 [c-36] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'operationBinding' but null was returned
=== 2017-08-09 19:54:02,934 [c-36] DEBUG PoolableDataSourceFactory - Created DataSource 3992 of type 'ReplenAvailableItems' and assigned it to thread http-nio-8080-exec-36
=== 2017-08-09 19:54:02,936 [c-36] DEBUG DSRequest - Caching instance 3992 of DS 'ReplenAvailableItems' from DSRequest.getDataSource()
=== 2017-08-09 19:54:02,936 [c-36] DEBUG DSRequest - Caching instance 3992 of DS ReplenAvailableItems
=== 2017-08-09 19:54:02,938 [c-36] DEBUG RPCManager - Request #1 (DSRequest) payload: {
criteria:{
operator:"iContains",
fieldName:"itemDescription",
value:"A",
_constructor:"AdvancedCriteria"
},
operationConfig:{
dataSource:"ReplenAvailableItems",
repo:null,
operationType:"fetch",
textMatchStyle:"exact"
},
exportResults:true,
exportAs:"csv",
exportDelimiter:",",
exportTitleSeparatorChar:"",
exportFilename:"Results.csv",
exportPath:null,
exportDisplay:"download",
lineBreakStyle:"default",
exportFields:[
"itemClass",
"itemVendor",
"itemStyle",
"itemColor",
"itemSize",
"substituteItemType",
"primaryItemNumber",
"itemNumber",
"skuNumber",
"isbn",
"gtin",
"itemDescription",
"shortDescription",
"vendorStyle",
"coordinateGroup",
"lastVendorCost",
"bookRetail"
],
exportHeader:null,
exportHeaderless:null,
exportFooter:null,
exportFieldTitles:{
itemClass:"Class",
itemVendor:"Vendor",
itemStyle:"Style",
itemColor:"Color",
itemSize:"Size",
substituteItemType:"Substitute Type",
primaryItemNumber:"Primary Item Number",
itemNumber:"Item Number",
skuNumber:"SKU Number",
isbn:"ISBN",
gtin:"GTIN",
itemDescription:"Item Description",
shortDescription:"Short Description",
vendorStyle:"Vendor Style",
coordinateGroup:"Coordinate Group",
lastVendorCost:"Vendor Cost",
bookRetail:"Book Retail"
},
exportDatesAsFormattedString:null,
exportRawValues:true,
exportCurrencySymbol:"$",
exportHeaderSpans:null,
exportOtherFields:{
itemClass:"Class",
itemVendor:"Vendor",
itemStyle:"Style",
itemColor:"Color",
itemSize:"Size",
substituteItemType:"Substitute Type",
primaryItemNumber:"Primary Item Number",
itemNumber:"Item Number",
skuNumber:"SKU Number",
isbn:"ISBN",
gtin:"GTIN",
itemDescription:"Item Description",
shortDescription:"Short Description",
vendorStyle:"Vendor Style",
coordinateGroup:"Coordinate Group",
lastVendorCost:"Vendor Cost",
bookRetail:"Book Retail"
},
exportWrapHeaderTitles:null,
exportStreaming:true,
exportPropertyIdentifier:"name",
appID:"builtinApplication",
operation:"substitutePrimaryItems",
oldValues:{
operator:"iContains",
fieldName:"itemDescription",
value:"A",
_constructor:"AdvancedCriteria"
},
exportToFilesystem:true,
exportToClient:false
}
=== 2017-08-09 19:54:02,940 [c-36] WARN IpAuthenticationManager - Property charSetNormalize not found in server.properties
=== 2017-08-09 19:54:02,940 [c-36] WARN IpAuthenticationManager - Property charSetNormalize not found in server.properties
=== 2017-08-09 19:54:02,941 [c-36] INFO IpIDACall - Performing 1 operation(s)
=== 2017-08-09 19:54:02,941 [c-36] INFO IpIDACall - Processing DataSource ReplenAvailableItems
=== 2017-08-09 19:54:02,941 [c-36] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2017-08-09 19:54:02,941 [c-36] DEBUG DeclarativeSecurity - DataSource ReplenAvailableItems is not in the pre-checked list, processing...
=== 2017-08-09 19:54:02,942 [c-36] DEBUG AppBase - [builtinApplication.substitutePrimaryItems] No userTypes defined, allowing anyone access to all operations for this application
=== 2017-08-09 19:54:02,942 [c-36] DEBUG AppBase - [builtinApplication.substitutePrimaryItems] No public zero-argument method named '_substitutePrimaryItems' found, performing generic datasource operation
=== 2017-08-09 19:54:02,943 [c-36] INFO SQLDataSource - [builtinApplication.substitutePrimaryItems] Performing fetch operation with
criteria: {operator:"iContains",fieldName:"itemDescription",value:"A",_constructor:"AdvancedCriteria"} values: {operator:"iContains",fieldName:"itemDescription",value:"A",_constructor:"AdvancedCriteria"}
=== 2017-08-09 19:54:03,018 [c-36] INFO SQLDataSource - [builtinApplication.substitutePrimaryItems] derived query: SELECT $defaultSelectClause FROM $defaultTableClause
join ipdivsn on ddiv < 2

LEFT JOIN(SELECT DISTINCT BCLS, BVEN, BSTY, BCLR, BSIZ, 'P' subType FROM IPBSSUB) primary
ON primary.BCLS=ICLS AND primary.BVEN=IVEN AND primary.BSTY=ISTY AND primary.BCLR=ICLR AND primary.BSIZ=ISIZ
LEFT JOIN (SELECT DISTINCT BSCL, BSVE, BSST, BSCR, BSSI, 'S' subType, BCLS, BVEN, BSTY, BCLR, BSIZ FROM IPBSSUB) secondary
ON secondary.BSCL=ICLS AND secondary.BSVE=IVEN AND secondary.BSST=ISTY AND secondary.BSCR=ICLR AND secondary.BSSI=ISIZ
WHERE $defaultWhereClause AND IPPK!='Y' AND ISET!='Y' AND ICSG!='Y'
=== 2017-08-09 19:54:03,019 [c-36] INFO SQLDataSource - [builtinApplication.substitutePrimaryItems] 3992: Executing SQL query on 'iptsfil5': SELECT COALESCE(primary.subType, secondary.subType, '') AS substituteItemType, DIGITS(COALESCE(primary.BCLS,secondary.BCLS))||'-'|| DIGITS(COALESCE(primary.BVEN,secondary.BVEN))||'-'|| DIGITS(COALESCE(primary.BSTY,secondary.BSTY))||'-'|| DIGITS(COALESCE(primary.BCLR,secondary.BCLR))||'-'|| DIGITS(COALESCE(primary.BSIZ,secondary.BSIZ)) AS primaryItemNumber, IPITHDR.ICNCPT AS concept, IPITHDR.IDIV AS division, IPITHDR.IDPT AS department, IPITHDR.ISBD AS subDepartment, IPITHDR.ICLS AS itemClass, IPITHDR.IVEN AS itemVendor, IPITHDR.ISTY AS itemStyle, IPITHDR.ICLR AS itemColor, IPITHDR.ISIZ AS itemSize, DIGITS(IPITHDR.ICLS)||'-'||DIGITS(IPITHDR.IVEN)||'-'||DIGITS(IPITHDR.ISTY)||'-'||DIGITS(IPITHDR.ICLR)||'-'||DIGITS(IPITHDR.ISIZ) AS itemNumber, IPITHDR.ISKU AS skuNumber, IPITHDR.ISBN AS isbn, IPITHDR.IGTIN AS gtin, IPITHDR.IGTINTP AS gtinType, CASE WHEN IPITHDR.IPPK='Y' THEN 'Y' ELSE 'N' END AS itemType, IPITHDR.IPKQTY AS prepackQuantity, IPITHDR.IPKTYPE AS prepackType, IPITHDR.IDES AS itemDescription, IPITHDR.ISDS AS shortDescription, IPITHDR.IVST AS vendorStyle, IPITHDR.IHLD AS holdStatus, IPITHDR.ICGP AS coordinateGroup, IPITHDR.IRTN AS vendorReturnPolicyCode, IPITHDR.ISTS AS status, IPITHDR.ISET AS setItem, IPITHDR.ILET AS leadTime, IPITHDR.IITR AS inTransitTime, IPITHDR.IMIN AS vendorMinimumPack, IPITHDR.IMLT AS minimumDistributionLot, IPITHDR.IUCD AS purchaseUnitCode, IPITHDR.IWT2 AS weightPerUnit, IPITHDR.IPKT AS packageType, IPITHDR.ISUQ AS salesPerStockingUnit, IPITHDR.ISUD AS stockingUnitDesc, IPITHDR.ICOR AS countryOfOrigin, IPITHDR.ICOD AS countryOfDelivery, IPITHDR.ICOM AS countryOfManufacture, IPITHDR.IDDC AS defaultDC, IPITHDR.ICCD AS commodityCode, IPITHDR.IDQT AS displayQuantity, IPITHDR.ISQF AS squareFootage, IPITHDR.IVCU AS vcpQuantity, IPITHDR.IVCH AS vcpHeight, IPITHDR.IVCW AS vcpWidth, IPITHDR.IVCL AS vcpLength, IPITHDR.IVCT AS vcpWeight, IPITHDR.IQCT AS qcType, IPITHDR.IBIN AS binLocation, IPITHDR.IMTP AS asnMultipack, IPITHDR.ICYC AS cycleCount, IPITHDR.ITKT AS ticketFormat, IPITHDR.IWTP AS weightTolerancePct, IPITHDR.ICTP AS costTolerancePct, IPITHDR.ILOCF AS lastOrderCost, IPITHDR.ILOCC AS lastOrderCostCurrency, IPITHDR.IVNC AS lastVendorCost, IPITHDR.IACC AS actualCost, IPITHDR.IVLC AS valuedCost, IPITHDR.ILNF-1 AS landingFactor, IPITHDR.INVC AS nextCost, IPITHDR.INCI AS nextCostStarts, IPITHDR.INCD AS nextCostStarts6D, IPITHDR.ILVC AS lowestCost, IPITHDR.IRET AS bookRetail, IPITHDR.ISUG AS suggestedRetail, IPITHDR.IORT AS originalRetail, IPITHDR.ISPR AS splitPriceRetail, IPITHDR.ISPQ AS splitPriceQuantity, IPITHDR.IPLU AS pluRetail, IPITHDR.IALT AS altPLURetail, IPITHDR.ITMP AS promoRetail, IPITHDR.ITFI AS promoStarts, IPITHDR.ITTI AS promoEnds, IPITHDR.ITFR AS promoStarts6D, IPITHDR.ITTO AS promoEnds6D, IPITHDR.IFTC AS pricingType, IPITHDR.IXDI AS creationDate, IPITHDR.IXD2 AS creationDate6D, IPITHDR.IDUN AS lastUpdateUser, IPITHDR.IBSS AS basicStockSubstitute, IPITHDR.ISRC AS entrySource, IPITHDR.ISAL AS temporarilyOnSale, IPITHDR.IMKD AS permanentMarkdown, IPITHDR.ILDI AS lastChainPLUDownload, IPITHDR.IFRI AS firstReceived, IPITHDR.ILRI AS lastReceived, IPITHDR.IFAI AS firstActivity, IPITHDR.ILAI AS lastActivity, IPITHDR.IFSI AS firstSale, IPITHDR.ILSI AS lastSale, IPITHDR.ILPI AS lastPhysical, IPITHDR.ILDL AS lastChainPLUDownload6D, IPITHDR.IFRD AS firstReceived6D, IPITHDR.ILRD AS lastReceived6D, IPITHDR.IFAD AS firstActivity6D, IPITHDR.ILAD AS lastActivity6D, IPITHDR.IFSD AS firstSale6D, IPITHDR.ILSD AS lastSale6D, IPITHDR.ILPD AS lastPhysical6D, IPITHDR.ISBY AS specialPurchase, IPITHDR.IADF AS adItem, IPITHDR.IPPC AS promoPriceCode, IPITHDR.IBAS AS basicItem, IPITHDR.IS#T AS serialNumberTracking, IPITHDR.IMMH AS maintainMonthlyHistory, IPITHDR.IWKH AS weeklyHistory, IPITHDR.ITRK AS weeklyHistoryLevel, IPITHDR.IWHB AS historyFrom, IPITHDR.IWHE AS historyTo, IPITHDR.IZPR AS storePricing, IPITHDR.ICSG AS consignment, IPITHDR.IVAT AS vatCode, IPITHDR.IGSK AS genericSku, IPITHDR.IREC AS reclassed, CASE WHEN IPITHDR.IREC='Y' THEN DIGITS(IPITHDR.IRCL)||'-'||DIGITS(IPITHDR.IRVN)||'-'||DIGITS(IPITHDR.IRST)||'-'||DIGITS(IPITHDR.IRCR)||'-'||DIGITS(IPITHDR.IRSZ) ELSE '' END AS reclassedTo, IPITHDR.IRCL AS reclassedToClass, IPITHDR.IRVN AS reclassedToVendor, IPITHDR.IRST AS reclassedToStyle, IPITHDR.IRCR AS reclassedToColor, IPITHDR.IRSZ AS reclassedToSize, IPITHDR.IMUC AS multipleGtins, IPITHDR.IMSKU AS multipleSkus, IPITHDR.IDSTPCT AS prepackReplenNeedPct, IPITHDR.IORDPCT AS prepackOrderPct, IPITHDR.IHPI1, IPITHDR.IHPI2, IPITHDR.IHPI3, IPITHDR.IPRI01, IPITHDR.IPRI02, IPITHDR.IPRI03, IPITHDR.IPRI04, IPITHDR.IPRI05, IPITHDR.IPRI06, IPITHDR.IPRI07, IPITHDR.IPRI08, IPITHDR.IPRI09, IPITHDR.IPRI10, IPITHDR.IBYR AS buyer FROM IPITHDR
join ipdivsn on ddiv < 2

LEFT JOIN(SELECT DISTINCT BCLS, BVEN, BSTY, BCLR, BSIZ, 'P' subType FROM IPBSSUB) primary
ON primary.BCLS=ICLS AND primary.BVEN=IVEN AND primary.BSTY=ISTY AND primary.BCLR=ICLR AND primary.BSIZ=ISIZ
LEFT JOIN (SELECT DISTINCT BSCL, BSVE, BSST, BSCR, BSSI, 'S' subType, BCLS, BVEN, BSTY, BCLR, BSIZ FROM IPBSSUB) secondary
ON secondary.BSCL=ICLS AND secondary.BSVE=IVEN AND secondary.BSST=ISTY AND secondary.BSCR=ICLR AND secondary.BSSI=ISIZ
WHERE (LOWER(IPITHDR.IDES) LIKE LOWER('%a%') {ESCAPE '\'} AND IPITHDR.IDES IS NOT NULL) AND IPPK!='Y' AND ISET!='Y' AND ICSG!='Y'
=== 2017-08-09 19:54:03,021 [c-36] DEBUG PoolableSQLConnectionFactory - [builtinApplication.substitutePrimaryItems] makeObject() created an unpooled Connection '1632256840'
=== 2017-08-09 19:54:03,021 [c-36] DEBUG SQLConnectionManager - [builtinApplication.substitutePrimaryItems] Borrowed connection '1632256840'
=== 2017-08-09 19:54:03,021 [c-36] INFO SQLDriver - [builtinApplication.substitutePrimaryItems] Executing SQL query on 'iptsfil5' using connection '1632256840': SELECT COALESCE(primary.subType, secondary.subType, '') AS substituteItemType, DIGITS(COALESCE(primary.BCLS,secondary.BCLS))||'-'|| DIGITS(COALESCE(primary.BVEN,secondary.BVEN))||'-'|| DIGITS(COALESCE(primary.BSTY,secondary.BSTY))||'-'|| DIGITS(COALESCE(primary.BCLR,secondary.BCLR))||'-'|| DIGITS(COALESCE(primary.BSIZ,secondary.BSIZ)) AS primaryItemNumber, IPITHDR.ICNCPT AS concept, IPITHDR.IDIV AS division, IPITHDR.IDPT AS department, IPITHDR.ISBD AS subDepartment, IPITHDR.ICLS AS itemClass, IPITHDR.IVEN AS itemVendor, IPITHDR.ISTY AS itemStyle, IPITHDR.ICLR AS itemColor, IPITHDR.ISIZ AS itemSize, DIGITS(IPITHDR.ICLS)||'-'||DIGITS(IPITHDR.IVEN)||'-'||DIGITS(IPITHDR.ISTY)||'-'||DIGITS(IPITHDR.ICLR)||'-'||DIGITS(IPITHDR.ISIZ) AS itemNumber, IPITHDR.ISKU AS skuNumber, IPITHDR.ISBN AS isbn, IPITHDR.IGTIN AS gtin, IPITHDR.IGTINTP AS gtinType, CASE WHEN IPITHDR.IPPK='Y' THEN 'Y' ELSE 'N' END AS itemType, IPITHDR.IPKQTY AS prepackQuantity, IPITHDR.IPKTYPE AS prepackType, IPITHDR.IDES AS itemDescription, IPITHDR.ISDS AS shortDescription, IPITHDR.IVST AS vendorStyle, IPITHDR.IHLD AS holdStatus, IPITHDR.ICGP AS coordinateGroup, IPITHDR.IRTN AS vendorReturnPolicyCode, IPITHDR.ISTS AS status, IPITHDR.ISET AS setItem, IPITHDR.ILET AS leadTime, IPITHDR.IITR AS inTransitTime, IPITHDR.IMIN AS vendorMinimumPack, IPITHDR.IMLT AS minimumDistributionLot, IPITHDR.IUCD AS purchaseUnitCode, IPITHDR.IWT2 AS weightPerUnit, IPITHDR.IPKT AS packageType, IPITHDR.ISUQ AS salesPerStockingUnit, IPITHDR.ISUD AS stockingUnitDesc, IPITHDR.ICOR AS countryOfOrigin, IPITHDR.ICOD AS countryOfDelivery, IPITHDR.ICOM AS countryOfManufacture, IPITHDR.IDDC AS defaultDC, IPITHDR.ICCD AS commodityCode, IPITHDR.IDQT AS displayQuantity, IPITHDR.ISQF AS squareFootage, IPITHDR.IVCU AS vcpQuantity, IPITHDR.IVCH AS vcpHeight, IPITHDR.IVCW AS vcpWidth, IPITHDR.IVCL AS vcpLength, IPITHDR.IVCT AS vcpWeight, IPITHDR.IQCT AS qcType, IPITHDR.IBIN AS binLocation, IPITHDR.IMTP AS asnMultipack, IPITHDR.ICYC AS cycleCount, IPITHDR.ITKT AS ticketFormat, IPITHDR.IWTP AS weightTolerancePct, IPITHDR.ICTP AS costTolerancePct, IPITHDR.ILOCF AS lastOrderCost, IPITHDR.ILOCC AS lastOrderCostCurrency, IPITHDR.IVNC AS lastVendorCost, IPITHDR.IACC AS actualCost, IPITHDR.IVLC AS valuedCost, IPITHDR.ILNF-1 AS landingFactor, IPITHDR.INVC AS nextCost, IPITHDR.INCI AS nextCostStarts, IPITHDR.INCD AS nextCostStarts6D, IPITHDR.ILVC AS lowestCost, IPITHDR.IRET AS bookRetail, IPITHDR.ISUG AS suggestedRetail, IPITHDR.IORT AS originalRetail, IPITHDR.ISPR AS splitPriceRetail, IPITHDR.ISPQ AS splitPriceQuantity, IPITHDR.IPLU AS pluRetail, IPITHDR.IALT AS altPLURetail, IPITHDR.ITMP AS promoRetail, IPITHDR.ITFI AS promoStarts, IPITHDR.ITTI AS promoEnds, IPITHDR.ITFR AS promoStarts6D, IPITHDR.ITTO AS promoEnds6D, IPITHDR.IFTC AS pricingType, IPITHDR.IXDI AS creationDate, IPITHDR.IXD2 AS creationDate6D, IPITHDR.IDUN AS lastUpdateUser, IPITHDR.IBSS AS basicStockSubstitute, IPITHDR.ISRC AS entrySource, IPITHDR.ISAL AS temporarilyOnSale, IPITHDR.IMKD AS permanentMarkdown, IPITHDR.ILDI AS lastChainPLUDownload, IPITHDR.IFRI AS firstReceived, IPITHDR.ILRI AS lastReceived, IPITHDR.IFAI AS firstActivity, IPITHDR.ILAI AS lastActivity, IPITHDR.IFSI AS firstSale, IPITHDR.ILSI AS lastSale, IPITHDR.ILPI AS lastPhysical, IPITHDR.ILDL AS lastChainPLUDownload6D, IPITHDR.IFRD AS firstReceived6D, IPITHDR.ILRD AS lastReceived6D, IPITHDR.IFAD AS firstActivity6D, IPITHDR.ILAD AS lastActivity6D, IPITHDR.IFSD AS firstSale6D, IPITHDR.ILSD AS lastSale6D, IPITHDR.ILPD AS lastPhysical6D, IPITHDR.ISBY AS specialPurchase, IPITHDR.IADF AS adItem, IPITHDR.IPPC AS promoPriceCode, IPITHDR.IBAS AS basicItem, IPITHDR.IS#T AS serialNumberTracking, IPITHDR.IMMH AS maintainMonthlyHistory, IPITHDR.IWKH AS weeklyHistory, IPITHDR.ITRK AS weeklyHistoryLevel, IPITHDR.IWHB AS historyFrom, IPITHDR.IWHE AS historyTo, IPITHDR.IZPR AS storePricing, IPITHDR.ICSG AS consignment, IPITHDR.IVAT AS vatCode, IPITHDR.IGSK AS genericSku, IPITHDR.IREC AS reclassed, CASE WHEN IPITHDR.IREC='Y' THEN DIGITS(IPITHDR.IRCL)||'-'||DIGITS(IPITHDR.IRVN)||'-'||DIGITS(IPITHDR.IRST)||'-'||DIGITS(IPITHDR.IRCR)||'-'||DIGITS(IPITHDR.IRSZ) ELSE '' END AS reclassedTo, IPITHDR.IRCL AS reclassedToClass, IPITHDR.IRVN AS reclassedToVendor, IPITHDR.IRST AS reclassedToStyle, IPITHDR.IRCR AS reclassedToColor, IPITHDR.IRSZ AS reclassedToSize, IPITHDR.IMUC AS multipleGtins, IPITHDR.IMSKU AS multipleSkus, IPITHDR.IDSTPCT AS prepackReplenNeedPct, IPITHDR.IORDPCT AS prepackOrderPct, IPITHDR.IHPI1, IPITHDR.IHPI2, IPITHDR.IHPI3, IPITHDR.IPRI01, IPITHDR.IPRI02, IPITHDR.IPRI03, IPITHDR.IPRI04, IPITHDR.IPRI05, IPITHDR.IPRI06, IPITHDR.IPRI07, IPITHDR.IPRI08, IPITHDR.IPRI09, IPITHDR.IPRI10, IPITHDR.IBYR AS buyer FROM IPITHDR
join ipdivsn on ddiv < 2

LEFT JOIN(SELECT DISTINCT BCLS, BVEN, BSTY, BCLR, BSIZ, 'P' subType FROM IPBSSUB) primary
ON primary.BCLS=ICLS AND primary.BVEN=IVEN AND primary.BSTY=ISTY AND primary.BCLR=ICLR AND primary.BSIZ=ISIZ
LEFT JOIN (SELECT DISTINCT BSCL, BSVE, BSST, BSCR, BSSI, 'S' subType, BCLS, BVEN, BSTY, BCLR, BSIZ FROM IPBSSUB) secondary
ON secondary.BSCL=ICLS AND secondary.BSVE=IVEN AND secondary.BSST=ISTY AND secondary.BSCR=ICLR AND secondary.BSSI=ISIZ
WHERE (LOWER(IPITHDR.IDES) LIKE LOWER('%a%') {ESCAPE '\'} AND IPITHDR.IDES IS NOT NULL) AND IPPK!='Y' AND ISET!='Y' AND ICSG!='Y'
=== 2017-08-09 19:56:51,876 [c-36] INFO DSResponse - DSResponse: List with 1879 items
=== 2017-08-09 19:56:51,876 [c-36] DEBUG DSRequest - About to free up resources for request of type fetch on DataSource ReplenAvailableItems
=== 2017-08-09 19:56:51,940 [c-36] DEBUG DataExport - Export took 59ms
=== 2017-08-09 19:56:51,955 [c-36] DEBUG SQLDriver - Freeing SQLDriver dbConnection 1632256840 for SQLDriver instance 493197323
=== 2017-08-09 19:56:51,955 [c-36] DEBUG SQLConnectionManager - About to close PoolGuardConnectionWrapper with hashcode "1632256840"

CSV Export (streamResults=true)

=== 2017-08-09 20:02:35,432 [c-41] INFO RequestContext - URL: '/ipgui/ipgui/sc/IDACall/Results.csv', User-Agent: 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36': Safari with Accept-Encoding header
=== 2017-08-09 20:02:35,468 [c-41] DEBUG XML - Parsed XML from (in memory stream): 35ms
=== 2017-08-09 20:02:35,468 [c-41] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'transaction' but null was returned
=== 2017-08-09 20:02:35,471 [c-41] DEBUG PoolableDataSourceFactory - Created DataSource 3968 of type 'Object' and assigned it to thread http-nio-8080-exec-41
=== 2017-08-09 20:02:35,472 [c-41] DEBUG PoolableDataSourceFactory - Created DataSource 3969 of type 'List' and assigned it to thread http-nio-8080-exec-41
=== 2017-08-09 20:02:35,473 [c-41] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'elem' but null was returned
=== 2017-08-09 20:02:35,474 [c-41] DEBUG RPCManager - Processing 1 requests.
=== 2017-08-09 20:02:35,476 [c-41] DEBUG XML - Parsed XML from (in memory stream): 2ms
=== 2017-08-09 20:02:35,478 [c-41] DEBUG PoolableDataSourceFactory - Created DataSource 3970 of type 'DataSource' and assigned it to thread http-nio-8080-exec-41
=== 2017-08-09 20:02:35,484 [c-41] DEBUG PoolableDataSourceFactory - Created DataSource 3971 of type 'DataSourceField' and assigned it to thread http-nio-8080-exec-41
=== 2017-08-09 20:02:35,484 [c-41] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'field' but null was returned
=== 2017-08-09 20:02:35,485 [c-41] DEBUG PoolableDataSourceFactory - Created DataSource 3972 of type 'JS' and assigned it to thread http-nio-8080-exec-41
=== 2017-08-09 20:02:35,485 [c-41] DEBUG PoolableDataSourceFactory - Created DataSource 3973 of type 'Object' and assigned it to thread http-nio-8080-exec-41
=== 2017-08-09 20:02:35,486 [c-41] DEBUG PoolableDataSourceFactory - Created DataSource 3974 of type 'ListGridField' and assigned it to thread http-nio-8080-exec-41
=== 2017-08-09 20:02:35,487 [c-41] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'validator' but null was returned
=== 2017-08-09 20:02:35,488 [c-41] DEBUG PoolableDataSourceFactory - Created DataSource 3975 of type 'Validator' and assigned it to thread http-nio-8080-exec-41
=== 2017-08-09 20:02:35,492 [c-41] DEBUG PoolableDataSourceFactory - Created DataSource 3976 of type 'ServerObject' and assigned it to thread http-nio-8080-exec-41
=== 2017-08-09 20:02:35,499 [c-41] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'serverObject' but null was returned
=== 2017-08-09 20:02:35,501 [c-41] DEBUG PoolableDataSourceFactory - Created DataSource 3977 of type 'ValueMap' and assigned it to thread http-nio-8080-exec-41
=== 2017-08-09 20:02:35,504 [c-41] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'SDC' but null was returned
=== 2017-08-09 20:02:35,504 [c-41] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'pickListCriteria' but null was returned
=== 2017-08-09 20:02:35,515 [c-41] DEBUG PoolableDataSourceFactory - Created DataSource 3978 of type 'OperationBinding' and assigned it to thread http-nio-8080-exec-41
=== 2017-08-09 20:02:35,516 [c-41] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'operationBinding' but null was returned
=== 2017-08-09 20:02:35,519 [c-41] DEBUG PoolableDataSourceFactory - Created DataSource 3979 of type 'ReplenAvailableItems' and assigned it to thread http-nio-8080-exec-41
=== 2017-08-09 20:02:35,528 [c-41] DEBUG DSRequest - Caching instance 3979 of DS 'ReplenAvailableItems' from DSRequest.getDataSource()
=== 2017-08-09 20:02:35,531 [c-41] DEBUG DSRequest - Caching instance 3979 of DS ReplenAvailableItems
=== 2017-08-09 20:02:35,532 [c-41] DEBUG RPCManager - Request #1 (DSRequest) payload: {
criteria:{
operator:"iContains",
fieldName:"itemDescription",
value:"A",
_constructor:"AdvancedCriteria"
},
operationConfig:{
dataSource:"ReplenAvailableItems",
repo:null,
operationType:"fetch",
textMatchStyle:"exact"
},
exportResults:true,
exportAs:"csv",
exportDelimiter:",",
exportTitleSeparatorChar:"",
exportFilename:"Results.csv",
exportPath:null,
exportDisplay:"download",
lineBreakStyle:"default",
exportFields:[
"itemClass",
"itemVendor",
"itemStyle",
"itemColor",
"itemSize",
"substituteItemType",
"primaryItemNumber",
"itemNumber",
"skuNumber",
"isbn",
"gtin",
"itemDescription",
"shortDescription",
"vendorStyle",
"coordinateGroup",
"lastVendorCost",
"bookRetail"
],
exportHeader:null,
exportHeaderless:null,
exportFooter:null,
exportFieldTitles:{
itemClass:"Class",
itemVendor:"Vendor",
itemStyle:"Style",
itemColor:"Color",
itemSize:"Size",
substituteItemType:"Substitute Type",
primaryItemNumber:"Primary Item Number",
itemNumber:"Item Number",
skuNumber:"SKU Number",
isbn:"ISBN",
gtin:"GTIN",
itemDescription:"Item Description",
shortDescription:"Short Description",
vendorStyle:"Vendor Style",
coordinateGroup:"Coordinate Group",
lastVendorCost:"Vendor Cost",
bookRetail:"Book Retail"
},
exportDatesAsFormattedString:null,
exportRawValues:true,
exportCurrencySymbol:"$",
exportHeaderSpans:null,
exportOtherFields:{
itemClass:"Class",
itemVendor:"Vendor",
itemStyle:"Style",
itemColor:"Color",
itemSize:"Size",
substituteItemType:"Substitute Type",
primaryItemNumber:"Primary Item Number",
itemNumber:"Item Number",
skuNumber:"SKU Number",
isbn:"ISBN",
gtin:"GTIN",
itemDescription:"Item Description",
shortDescription:"Short Description",
vendorStyle:"Vendor Style",
coordinateGroup:"Coordinate Group",
lastVendorCost:"Vendor Cost",
bookRetail:"Book Retail"
},
exportWrapHeaderTitles:null,
exportStreaming:true,
exportPropertyIdentifier:"name",
appID:"builtinApplication",
operation:"substitutePrimaryItems",
oldValues:{
operator:"iContains",
fieldName:"itemDescription",
value:"A",
_constructor:"AdvancedCriteria"
},
streamResults:true,
exportToFilesystem:true,
exportToClient:false
}
=== 2017-08-09 20:02:35,534 [c-41] WARN IpAuthenticationManager - Property charSetNormalize not found in server.properties
=== 2017-08-09 20:02:35,535 [c-41] WARN IpAuthenticationManager - Property charSetNormalize not found in server.properties
=== 2017-08-09 20:02:35,535 [c-41] INFO IpIDACall - Performing 1 operation(s)
=== 2017-08-09 20:02:35,535 [c-41] INFO IpIDACall - Processing DataSource ReplenAvailableItems
=== 2017-08-09 20:02:35,535 [c-41] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2017-08-09 20:02:35,536 [c-41] DEBUG DeclarativeSecurity - DataSource ReplenAvailableItems is not in the pre-checked list, processing...
=== 2017-08-09 20:02:35,546 [c-41] DEBUG AppBase - [builtinApplication.substitutePrimaryItems] No userTypes defined, allowing anyone access to all operations for this application
=== 2017-08-09 20:02:35,546 [c-41] DEBUG AppBase - [builtinApplication.substitutePrimaryItems] No public zero-argument method named '_substitutePrimaryItems' found, performing generic datasource operation
=== 2017-08-09 20:02:35,547 [c-41] INFO SQLDataSource - [builtinApplication.substitutePrimaryItems] Performing fetch operation with
criteria: {operator:"iContains",fieldName:"itemDescription",value:"A",_constructor:"AdvancedCriteria"} values: {operator:"iContains",fieldName:"itemDescription",value:"A",_constructor:"AdvancedCriteria"}
=== 2017-08-09 20:02:35,602 [c-41] INFO SQLDataSource - [builtinApplication.substitutePrimaryItems] derived query: SELECT $defaultSelectClause FROM $defaultTableClause
join ipdivsn on ddiv < 2

LEFT JOIN(SELECT DISTINCT BCLS, BVEN, BSTY, BCLR, BSIZ, 'P' subType FROM IPBSSUB) primary
ON primary.BCLS=ICLS AND primary.BVEN=IVEN AND primary.BSTY=ISTY AND primary.BCLR=ICLR AND primary.BSIZ=ISIZ
LEFT JOIN (SELECT DISTINCT BSCL, BSVE, BSST, BSCR, BSSI, 'S' subType, BCLS, BVEN, BSTY, BCLR, BSIZ FROM IPBSSUB) secondary
ON secondary.BSCL=ICLS AND secondary.BSVE=IVEN AND secondary.BSST=ISTY AND secondary.BSCR=ICLR AND secondary.BSSI=ISIZ
WHERE $defaultWhereClause AND IPPK!='Y' AND ISET!='Y' AND ICSG!='Y'
=== 2017-08-09 20:02:35,602 [c-41] INFO SQLDataSource - [builtinApplication.substitutePrimaryItems] 3979: Executing SQL query on 'iptsfil5': SELECT COALESCE(primary.subType, secondary.subType, '') AS substituteItemType, DIGITS(COALESCE(primary.BCLS,secondary.BCLS))||'-'|| DIGITS(COALESCE(primary.BVEN,secondary.BVEN))||'-'|| DIGITS(COALESCE(primary.BSTY,secondary.BSTY))||'-'|| DIGITS(COALESCE(primary.BCLR,secondary.BCLR))||'-'|| DIGITS(COALESCE(primary.BSIZ,secondary.BSIZ)) AS primaryItemNumber, IPITHDR.ICNCPT AS concept, IPITHDR.IDIV AS division, IPITHDR.IDPT AS department, IPITHDR.ISBD AS subDepartment, IPITHDR.ICLS AS itemClass, IPITHDR.IVEN AS itemVendor, IPITHDR.ISTY AS itemStyle, IPITHDR.ICLR AS itemColor, IPITHDR.ISIZ AS itemSize, DIGITS(IPITHDR.ICLS)||'-'||DIGITS(IPITHDR.IVEN)||'-'||DIGITS(IPITHDR.ISTY)||'-'||DIGITS(IPITHDR.ICLR)||'-'||DIGITS(IPITHDR.ISIZ) AS itemNumber, IPITHDR.ISKU AS skuNumber, IPITHDR.ISBN AS isbn, IPITHDR.IGTIN AS gtin, IPITHDR.IGTINTP AS gtinType, CASE WHEN IPITHDR.IPPK='Y' THEN 'Y' ELSE 'N' END AS itemType, IPITHDR.IPKQTY AS prepackQuantity, IPITHDR.IPKTYPE AS prepackType, IPITHDR.IDES AS itemDescription, IPITHDR.ISDS AS shortDescription, IPITHDR.IVST AS vendorStyle, IPITHDR.IHLD AS holdStatus, IPITHDR.ICGP AS coordinateGroup, IPITHDR.IRTN AS vendorReturnPolicyCode, IPITHDR.ISTS AS status, IPITHDR.ISET AS setItem, IPITHDR.ILET AS leadTime, IPITHDR.IITR AS inTransitTime, IPITHDR.IMIN AS vendorMinimumPack, IPITHDR.IMLT AS minimumDistributionLot, IPITHDR.IUCD AS purchaseUnitCode, IPITHDR.IWT2 AS weightPerUnit, IPITHDR.IPKT AS packageType, IPITHDR.ISUQ AS salesPerStockingUnit, IPITHDR.ISUD AS stockingUnitDesc, IPITHDR.ICOR AS countryOfOrigin, IPITHDR.ICOD AS countryOfDelivery, IPITHDR.ICOM AS countryOfManufacture, IPITHDR.IDDC AS defaultDC, IPITHDR.ICCD AS commodityCode, IPITHDR.IDQT AS displayQuantity, IPITHDR.ISQF AS squareFootage, IPITHDR.IVCU AS vcpQuantity, IPITHDR.IVCH AS vcpHeight, IPITHDR.IVCW AS vcpWidth, IPITHDR.IVCL AS vcpLength, IPITHDR.IVCT AS vcpWeight, IPITHDR.IQCT AS qcType, IPITHDR.IBIN AS binLocation, IPITHDR.IMTP AS asnMultipack, IPITHDR.ICYC AS cycleCount, IPITHDR.ITKT AS ticketFormat, IPITHDR.IWTP AS weightTolerancePct, IPITHDR.ICTP AS costTolerancePct, IPITHDR.ILOCF AS lastOrderCost, IPITHDR.ILOCC AS lastOrderCostCurrency, IPITHDR.IVNC AS lastVendorCost, IPITHDR.IACC AS actualCost, IPITHDR.IVLC AS valuedCost, IPITHDR.ILNF-1 AS landingFactor, IPITHDR.INVC AS nextCost, IPITHDR.INCI AS nextCostStarts, IPITHDR.INCD AS nextCostStarts6D, IPITHDR.ILVC AS lowestCost, IPITHDR.IRET AS bookRetail, IPITHDR.ISUG AS suggestedRetail, IPITHDR.IORT AS originalRetail, IPITHDR.ISPR AS splitPriceRetail, IPITHDR.ISPQ AS splitPriceQuantity, IPITHDR.IPLU AS pluRetail, IPITHDR.IALT AS altPLURetail, IPITHDR.ITMP AS promoRetail, IPITHDR.ITFI AS promoStarts, IPITHDR.ITTI AS promoEnds, IPITHDR.ITFR AS promoStarts6D, IPITHDR.ITTO AS promoEnds6D, IPITHDR.IFTC AS pricingType, IPITHDR.IXDI AS creationDate, IPITHDR.IXD2 AS creationDate6D, IPITHDR.IDUN AS lastUpdateUser, IPITHDR.IBSS AS basicStockSubstitute, IPITHDR.ISRC AS entrySource, IPITHDR.ISAL AS temporarilyOnSale, IPITHDR.IMKD AS permanentMarkdown, IPITHDR.ILDI AS lastChainPLUDownload, IPITHDR.IFRI AS firstReceived, IPITHDR.ILRI AS lastReceived, IPITHDR.IFAI AS firstActivity, IPITHDR.ILAI AS lastActivity, IPITHDR.IFSI AS firstSale, IPITHDR.ILSI AS lastSale, IPITHDR.ILPI AS lastPhysical, IPITHDR.ILDL AS lastChainPLUDownload6D, IPITHDR.IFRD AS firstReceived6D, IPITHDR.ILRD AS lastReceived6D, IPITHDR.IFAD AS firstActivity6D, IPITHDR.ILAD AS lastActivity6D, IPITHDR.IFSD AS firstSale6D, IPITHDR.ILSD AS lastSale6D, IPITHDR.ILPD AS lastPhysical6D, IPITHDR.ISBY AS specialPurchase, IPITHDR.IADF AS adItem, IPITHDR.IPPC AS promoPriceCode, IPITHDR.IBAS AS basicItem, IPITHDR.IS#T AS serialNumberTracking, IPITHDR.IMMH AS maintainMonthlyHistory, IPITHDR.IWKH AS weeklyHistory, IPITHDR.ITRK AS weeklyHistoryLevel, IPITHDR.IWHB AS historyFrom, IPITHDR.IWHE AS historyTo, IPITHDR.IZPR AS storePricing, IPITHDR.ICSG AS consignment, IPITHDR.IVAT AS vatCode, IPITHDR.IGSK AS genericSku, IPITHDR.IREC AS reclassed, CASE WHEN IPITHDR.IREC='Y' THEN DIGITS(IPITHDR.IRCL)||'-'||DIGITS(IPITHDR.IRVN)||'-'||DIGITS(IPITHDR.IRST)||'-'||DIGITS(IPITHDR.IRCR)||'-'||DIGITS(IPITHDR.IRSZ) ELSE '' END AS reclassedTo, IPITHDR.IRCL AS reclassedToClass, IPITHDR.IRVN AS reclassedToVendor, IPITHDR.IRST AS reclassedToStyle, IPITHDR.IRCR AS reclassedToColor, IPITHDR.IRSZ AS reclassedToSize, IPITHDR.IMUC AS multipleGtins, IPITHDR.IMSKU AS multipleSkus, IPITHDR.IDSTPCT AS prepackReplenNeedPct, IPITHDR.IORDPCT AS prepackOrderPct, IPITHDR.IHPI1, IPITHDR.IHPI2, IPITHDR.IHPI3, IPITHDR.IPRI01, IPITHDR.IPRI02, IPITHDR.IPRI03, IPITHDR.IPRI04, IPITHDR.IPRI05, IPITHDR.IPRI06, IPITHDR.IPRI07, IPITHDR.IPRI08, IPITHDR.IPRI09, IPITHDR.IPRI10, IPITHDR.IBYR AS buyer FROM IPITHDR
join ipdivsn on ddiv < 2

LEFT JOIN(SELECT DISTINCT BCLS, BVEN, BSTY, BCLR, BSIZ, 'P' subType FROM IPBSSUB) primary
ON primary.BCLS=ICLS AND primary.BVEN=IVEN AND primary.BSTY=ISTY AND primary.BCLR=ICLR AND primary.BSIZ=ISIZ
LEFT JOIN (SELECT DISTINCT BSCL, BSVE, BSST, BSCR, BSSI, 'S' subType, BCLS, BVEN, BSTY, BCLR, BSIZ FROM IPBSSUB) secondary
ON secondary.BSCL=ICLS AND secondary.BSVE=IVEN AND secondary.BSST=ISTY AND secondary.BSCR=ICLR AND secondary.BSSI=ISIZ
WHERE (LOWER(IPITHDR.IDES) LIKE LOWER('%a%') {ESCAPE '\'} AND IPITHDR.IDES IS NOT NULL) AND IPPK!='Y' AND ISET!='Y' AND ICSG!='Y'
=== 2017-08-09 20:02:35,606 [c-41] DEBUG PoolableSQLConnectionFactory - [builtinApplication.substitutePrimaryItems] makeObject() created an unpooled Connection '798017322'
=== 2017-08-09 20:02:35,606 [c-41] DEBUG SQLConnectionManager - [builtinApplication.substitutePrimaryItems] Borrowed connection '798017322'
=== 2017-08-09 20:02:35,606 [c-41] INFO SQLDriver - [builtinApplication.substitutePrimaryItems] Executing SQL query on 'iptsfil5' using connection '798017322': SELECT COALESCE(primary.subType, secondary.subType, '') AS substituteItemType, DIGITS(COALESCE(primary.BCLS,secondary.BCLS))||'-'|| DIGITS(COALESCE(primary.BVEN,secondary.BVEN))||'-'|| DIGITS(COALESCE(primary.BSTY,secondary.BSTY))||'-'|| DIGITS(COALESCE(primary.BCLR,secondary.BCLR))||'-'|| DIGITS(COALESCE(primary.BSIZ,secondary.BSIZ)) AS primaryItemNumber, IPITHDR.ICNCPT AS concept, IPITHDR.IDIV AS division, IPITHDR.IDPT AS department, IPITHDR.ISBD AS subDepartment, IPITHDR.ICLS AS itemClass, IPITHDR.IVEN AS itemVendor, IPITHDR.ISTY AS itemStyle, IPITHDR.ICLR AS itemColor, IPITHDR.ISIZ AS itemSize, DIGITS(IPITHDR.ICLS)||'-'||DIGITS(IPITHDR.IVEN)||'-'||DIGITS(IPITHDR.ISTY)||'-'||DIGITS(IPITHDR.ICLR)||'-'||DIGITS(IPITHDR.ISIZ) AS itemNumber, IPITHDR.ISKU AS skuNumber, IPITHDR.ISBN AS isbn, IPITHDR.IGTIN AS gtin, IPITHDR.IGTINTP AS gtinType, CASE WHEN IPITHDR.IPPK='Y' THEN 'Y' ELSE 'N' END AS itemType, IPITHDR.IPKQTY AS prepackQuantity, IPITHDR.IPKTYPE AS prepackType, IPITHDR.IDES AS itemDescription, IPITHDR.ISDS AS shortDescription, IPITHDR.IVST AS vendorStyle, IPITHDR.IHLD AS holdStatus, IPITHDR.ICGP AS coordinateGroup, IPITHDR.IRTN AS vendorReturnPolicyCode, IPITHDR.ISTS AS status, IPITHDR.ISET AS setItem, IPITHDR.ILET AS leadTime, IPITHDR.IITR AS inTransitTime, IPITHDR.IMIN AS vendorMinimumPack, IPITHDR.IMLT AS minimumDistributionLot, IPITHDR.IUCD AS purchaseUnitCode, IPITHDR.IWT2 AS weightPerUnit, IPITHDR.IPKT AS packageType, IPITHDR.ISUQ AS salesPerStockingUnit, IPITHDR.ISUD AS stockingUnitDesc, IPITHDR.ICOR AS countryOfOrigin, IPITHDR.ICOD AS countryOfDelivery, IPITHDR.ICOM AS countryOfManufacture, IPITHDR.IDDC AS defaultDC, IPITHDR.ICCD AS commodityCode, IPITHDR.IDQT AS displayQuantity, IPITHDR.ISQF AS squareFootage, IPITHDR.IVCU AS vcpQuantity, IPITHDR.IVCH AS vcpHeight, IPITHDR.IVCW AS vcpWidth, IPITHDR.IVCL AS vcpLength, IPITHDR.IVCT AS vcpWeight, IPITHDR.IQCT AS qcType, IPITHDR.IBIN AS binLocation, IPITHDR.IMTP AS asnMultipack, IPITHDR.ICYC AS cycleCount, IPITHDR.ITKT AS ticketFormat, IPITHDR.IWTP AS weightTolerancePct, IPITHDR.ICTP AS costTolerancePct, IPITHDR.ILOCF AS lastOrderCost, IPITHDR.ILOCC AS lastOrderCostCurrency, IPITHDR.IVNC AS lastVendorCost, IPITHDR.IACC AS actualCost, IPITHDR.IVLC AS valuedCost, IPITHDR.ILNF-1 AS landingFactor, IPITHDR.INVC AS nextCost, IPITHDR.INCI AS nextCostStarts, IPITHDR.INCD AS nextCostStarts6D, IPITHDR.ILVC AS lowestCost, IPITHDR.IRET AS bookRetail, IPITHDR.ISUG AS suggestedRetail, IPITHDR.IORT AS originalRetail, IPITHDR.ISPR AS splitPriceRetail, IPITHDR.ISPQ AS splitPriceQuantity, IPITHDR.IPLU AS pluRetail, IPITHDR.IALT AS altPLURetail, IPITHDR.ITMP AS promoRetail, IPITHDR.ITFI AS promoStarts, IPITHDR.ITTI AS promoEnds, IPITHDR.ITFR AS promoStarts6D, IPITHDR.ITTO AS promoEnds6D, IPITHDR.IFTC AS pricingType, IPITHDR.IXDI AS creationDate, IPITHDR.IXD2 AS creationDate6D, IPITHDR.IDUN AS lastUpdateUser, IPITHDR.IBSS AS basicStockSubstitute, IPITHDR.ISRC AS entrySource, IPITHDR.ISAL AS temporarilyOnSale, IPITHDR.IMKD AS permanentMarkdown, IPITHDR.ILDI AS lastChainPLUDownload, IPITHDR.IFRI AS firstReceived, IPITHDR.ILRI AS lastReceived, IPITHDR.IFAI AS firstActivity, IPITHDR.ILAI AS lastActivity, IPITHDR.IFSI AS firstSale, IPITHDR.ILSI AS lastSale, IPITHDR.ILPI AS lastPhysical, IPITHDR.ILDL AS lastChainPLUDownload6D, IPITHDR.IFRD AS firstReceived6D, IPITHDR.ILRD AS lastReceived6D, IPITHDR.IFAD AS firstActivity6D, IPITHDR.ILAD AS lastActivity6D, IPITHDR.IFSD AS firstSale6D, IPITHDR.ILSD AS lastSale6D, IPITHDR.ILPD AS lastPhysical6D, IPITHDR.ISBY AS specialPurchase, IPITHDR.IADF AS adItem, IPITHDR.IPPC AS promoPriceCode, IPITHDR.IBAS AS basicItem, IPITHDR.IS#T AS serialNumberTracking, IPITHDR.IMMH AS maintainMonthlyHistory, IPITHDR.IWKH AS weeklyHistory, IPITHDR.ITRK AS weeklyHistoryLevel, IPITHDR.IWHB AS historyFrom, IPITHDR.IWHE AS historyTo, IPITHDR.IZPR AS storePricing, IPITHDR.ICSG AS consignment, IPITHDR.IVAT AS vatCode, IPITHDR.IGSK AS genericSku, IPITHDR.IREC AS reclassed, CASE WHEN IPITHDR.IREC='Y' THEN DIGITS(IPITHDR.IRCL)||'-'||DIGITS(IPITHDR.IRVN)||'-'||DIGITS(IPITHDR.IRST)||'-'||DIGITS(IPITHDR.IRCR)||'-'||DIGITS(IPITHDR.IRSZ) ELSE '' END AS reclassedTo, IPITHDR.IRCL AS reclassedToClass, IPITHDR.IRVN AS reclassedToVendor, IPITHDR.IRST AS reclassedToStyle, IPITHDR.IRCR AS reclassedToColor, IPITHDR.IRSZ AS reclassedToSize, IPITHDR.IMUC AS multipleGtins, IPITHDR.IMSKU AS multipleSkus, IPITHDR.IDSTPCT AS prepackReplenNeedPct, IPITHDR.IORDPCT AS prepackOrderPct, IPITHDR.IHPI1, IPITHDR.IHPI2, IPITHDR.IHPI3, IPITHDR.IPRI01, IPITHDR.IPRI02, IPITHDR.IPRI03, IPITHDR.IPRI04, IPITHDR.IPRI05, IPITHDR.IPRI06, IPITHDR.IPRI07, IPITHDR.IPRI08, IPITHDR.IPRI09, IPITHDR.IPRI10, IPITHDR.IBYR AS buyer FROM IPITHDR
join ipdivsn on ddiv < 2

LEFT JOIN(SELECT DISTINCT BCLS, BVEN, BSTY, BCLR, BSIZ, 'P' subType FROM IPBSSUB) primary
ON primary.BCLS=ICLS AND primary.BVEN=IVEN AND primary.BSTY=ISTY AND primary.BCLR=ICLR AND primary.BSIZ=ISIZ
LEFT JOIN (SELECT DISTINCT BSCL, BSVE, BSST, BSCR, BSSI, 'S' subType, BCLS, BVEN, BSTY, BCLR, BSIZ FROM IPBSSUB) secondary
ON secondary.BSCL=ICLS AND secondary.BSVE=IVEN AND secondary.BSST=ISTY AND secondary.BSCR=ICLR AND secondary.BSSI=ISIZ
WHERE (LOWER(IPITHDR.IDES) LIKE LOWER('%a%') {ESCAPE '\'} AND IPITHDR.IDES IS NOT NULL) AND IPPK!='Y' AND ISET!='Y' AND ICSG!='Y'
=== 2017-08-09 20:02:36,141 [c-41] DEBUG SQLDriver - [builtinApplication.substitutePrimaryItems] Streaming the response
=== 2017-08-09 20:02:36,156 [c-41] DEBUG DSRequest - freeOnExecute is false for request of type fetch on DataSource ReplenAvailableItems - not freeing resources!
=== 2017-08-09 20:05:34,775 [c-41] DEBUG DataExport - Export took 178612ms
=== 2017-08-09 20:05:34,788 [c-41] DEBUG SQLTransaction - getConnection() looked for transactional connection for iptsfil5: (connection is null)
=== 2017-08-09 20:05:34,789 [c-41] DEBUG SQLDriver - Freeing SQLDriver dbConnection 798017322 for SQLDriver instance 350872847
=== 2017-08-09 20:05:34,789 [c-41] DEBUG SQLConnectionManager - About to close PoolGuardConnectionWrapper with hashcode "798017322"

OOXML Export (no streamResults)

=== 2017-08-09 18:26:30,086 [c-12] INFO RequestContext - URL: '/ipgui/ipgui/sc/IDACall/Results.xlsx', User-Agent: 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36': Safari with Accept-Encoding header
=== 2017-08-09 18:26:30,091 [c-12] DEBUG XML - Parsed XML from (in memory stream): 2ms
=== 2017-08-09 18:26:30,091 [c-12] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'transaction' but null was returned
=== 2017-08-09 18:26:30,093 [c-12] DEBUG PoolableDataSourceFactory - Created DataSource 5178 of type 'Object' and assigned it to thread http-nio-8080-exec-12
=== 2017-08-09 18:26:30,098 [c-12] DEBUG PoolableDataSourceFactory - Created DataSource 5179 of type 'List' and assigned it to thread http-nio-8080-exec-12
=== 2017-08-09 18:26:30,104 [c-12] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'elem' but null was returned
=== 2017-08-09 18:26:30,105 [c-12] DEBUG RPCManager - Processing 1 requests.
=== 2017-08-09 18:26:30,109 [c-12] DEBUG XML - Parsed XML from (in memory stream): 4ms
=== 2017-08-09 18:26:30,126 [c-12] DEBUG PoolableDataSourceFactory - Created DataSource 5180 of type 'DataSource' and assigned it to thread http-nio-8080-exec-12
=== 2017-08-09 18:26:30,132 [c-12] DEBUG PoolableDataSourceFactory - Created DataSource 5181 of type 'DataSourceField' and assigned it to thread http-nio-8080-exec-12
=== 2017-08-09 18:26:30,133 [c-12] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'field' but null was returned
=== 2017-08-09 18:26:30,136 [c-12] DEBUG PoolableDataSourceFactory - Created DataSource 5182 of type 'JS' and assigned it to thread http-nio-8080-exec-12
=== 2017-08-09 18:26:30,138 [c-12] DEBUG PoolableDataSourceFactory - Created DataSource 5183 of type 'Object' and assigned it to thread http-nio-8080-exec-12
=== 2017-08-09 18:26:30,140 [c-12] DEBUG PoolableDataSourceFactory - Created DataSource 5184 of type 'ListGridField' and assigned it to thread http-nio-8080-exec-12
=== 2017-08-09 18:26:30,142 [c-12] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'validator' but null was returned
=== 2017-08-09 18:26:30,154 [c-12] DEBUG PoolableDataSourceFactory - Created DataSource 5185 of type 'Validator' and assigned it to thread http-nio-8080-exec-12
=== 2017-08-09 18:26:30,161 [c-12] DEBUG PoolableDataSourceFactory - Created DataSource 5186 of type 'ServerObject' and assigned it to thread http-nio-8080-exec-12
=== 2017-08-09 18:26:30,166 [c-12] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'serverObject' but null was returned
=== 2017-08-09 18:26:30,171 [c-12] DEBUG PoolableDataSourceFactory - Created DataSource 5187 of type 'ValueMap' and assigned it to thread http-nio-8080-exec-12
=== 2017-08-09 18:26:30,176 [c-12] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'SDC' but null was returned
=== 2017-08-09 18:26:30,177 [c-12] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'pickListCriteria' but null was returned
=== 2017-08-09 18:26:30,198 [c-12] DEBUG PoolableDataSourceFactory - Created DataSource 5188 of type 'OperationBinding' and assigned it to thread http-nio-8080-exec-12
=== 2017-08-09 18:26:30,199 [c-12] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'operationBinding' but null was returned
=== 2017-08-09 18:26:30,214 [c-12] DEBUG PoolableDataSourceFactory - Created DataSource 5189 of type 'ReplenAvailableItems' and assigned it to thread http-nio-8080-exec-12
=== 2017-08-09 18:26:30,218 [c-12] DEBUG DSRequest - Caching instance 5189 of DS 'ReplenAvailableItems' from DSRequest.getDataSource()
=== 2017-08-09 18:26:30,219 [c-12] DEBUG DSRequest - Caching instance 5189 of DS ReplenAvailableItems
=== 2017-08-09 18:26:30,221 [c-12] DEBUG RPCManager - Request #1 (DSRequest) payload: {
criteria:{
operator:"iContains",
fieldName:"itemDescription",
value:"A",
_constructor:"AdvancedCriteria"
},
operationConfig:{
dataSource:"ReplenAvailableItems",
repo:null,
operationType:"fetch",
textMatchStyle:"exact"
},
exportResults:true,
exportAs:"ooxml",
exportDelimiter:",",
exportTitleSeparatorChar:"",
exportFilename:"Results.xlsx",
exportPath:null,
exportDisplay:"download",
lineBreakStyle:"default",
exportFields:[
"substituteItemType",
"primaryItemNumber",
"itemNumber",
"skuNumber",
"isbn",
"gtin",
"itemDescription",
"shortDescription",
"vendorStyle",
"coordinateGroup",
"lastVendorCost",
"bookRetail"
],
exportHeader:null,
exportHeaderless:null,
exportFooter:null,
exportFieldTitles:{
substituteItemType:"Substitute Type",
primaryItemNumber:"Primary Item Number",
itemNumber:"Item Number",
skuNumber:"SKU Number",
isbn:"ISBN",
gtin:"GTIN",
itemDescription:"Item Description",
shortDescription:"Short Description",
vendorStyle:"Vendor Style",
coordinateGroup:"Coordinate Group",
lastVendorCost:"Vendor Cost",
bookRetail:"Book Retail"
},
exportDatesAsFormattedString:null,
exportRawValues:true,
exportCurrencySymbol:"$",
exportHeaderSpans:null,
exportOtherFields:{
substituteItemType:"Substitute Type",
primaryItemNumber:"Primary Item Number",
itemNumber:"Item Number",
skuNumber:"SKU Number",
isbn:"ISBN",
gtin:"GTIN",
itemDescription:"Item Description",
shortDescription:"Short Description",
vendorStyle:"Vendor Style",
coordinateGroup:"Coordinate Group",
lastVendorCost:"Vendor Cost",
bookRetail:"Book Retail"
},
exportWrapHeaderTitles:null,
exportStreaming:true,
exportPropertyIdentifier:"name",
appID:"builtinApplication",
operation:"substitutePrimaryItems",
oldValues:{
operator:"iContains",
fieldName:"itemDescription",
value:"A",
_constructor:"AdvancedCriteria"
},
exportToFilesystem:true,
exportToClient:false
}
=== 2017-08-09 18:26:30,222 [c-12] WARN IpAuthenticationManager - Property charSetNormalize not found in server.properties
=== 2017-08-09 18:26:30,223 [c-12] WARN IpAuthenticationManager - Property charSetNormalize not found in server.properties
=== 2017-08-09 18:26:30,223 [c-12] INFO IpIDACall - Performing 1 operation(s)
=== 2017-08-09 18:26:30,224 [c-12] INFO IpIDACall - Processing DataSource ReplenAvailableItems
=== 2017-08-09 18:26:30,224 [c-12] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2017-08-09 18:26:30,224 [c-12] DEBUG DeclarativeSecurity - DataSource ReplenAvailableItems is not in the pre-checked list, processing...
=== 2017-08-09 18:26:30,226 [c-12] DEBUG AppBase - [builtinApplication.substitutePrimaryItems] No userTypes defined, allowing anyone access to all operations for this application
=== 2017-08-09 18:26:30,226 [c-12] DEBUG AppBase - [builtinApplication.substitutePrimaryItems] No public zero-argument method named '_substitutePrimaryItems' found, performing generic datasource operation
=== 2017-08-09 18:26:30,238 [c-12] INFO SQLDataSource - [builtinApplication.substitutePrimaryItems] Performing fetch operation with
criteria: {operator:"iContains",fieldName:"itemDescription",value:"A",_constructor:"AdvancedCriteria"} values: {operator:"iContains",fieldName:"itemDescription",value:"A",_constructor:"AdvancedCriteria"}
=== 2017-08-09 18:26:30,348 [c-12] INFO SQLDataSource - [builtinApplication.substitutePrimaryItems] derived query: SELECT $defaultSelectClause FROM $defaultTableClause
join ipdivsn on ddiv < 2

LEFT JOIN(SELECT DISTINCT BCLS, BVEN, BSTY, BCLR, BSIZ, 'P' subType FROM IPBSSUB) primary
ON primary.BCLS=ICLS AND primary.BVEN=IVEN AND primary.BSTY=ISTY AND primary.BCLR=ICLR AND primary.BSIZ=ISIZ
LEFT JOIN (SELECT DISTINCT BSCL, BSVE, BSST, BSCR, BSSI, 'S' subType, BCLS, BVEN, BSTY, BCLR, BSIZ FROM IPBSSUB) secondary
ON secondary.BSCL=ICLS AND secondary.BSVE=IVEN AND secondary.BSST=ISTY AND secondary.BSCR=ICLR AND secondary.BSSI=ISIZ
WHERE $defaultWhereClause AND IPPK!='Y' AND ISET!='Y' AND ICSG!='Y'
=== 2017-08-09 18:26:30,349 [c-12] INFO SQLDataSource - [builtinApplication.substitutePrimaryItems] 5189: Executing SQL query on 'iptsfil5': SELECT COALESCE(primary.subType, secondary.subType, '') AS substituteItemType, DIGITS(COALESCE(primary.BCLS,secondary.BCLS))||'-'|| DIGITS(COALESCE(primary.BVEN,secondary.BVEN))||'-'|| DIGITS(COALESCE(primary.BSTY,secondary.BSTY))||'-'|| DIGITS(COALESCE(primary.BCLR,secondary.BCLR))||'-'|| DIGITS(COALESCE(primary.BSIZ,secondary.BSIZ)) AS primaryItemNumber, IPITHDR.ICNCPT AS concept, IPITHDR.IDIV AS division, IPITHDR.IDPT AS department, IPITHDR.ISBD AS subDepartment, IPITHDR.ICLS AS itemClass, IPITHDR.IVEN AS itemVendor, IPITHDR.ISTY AS itemStyle, IPITHDR.ICLR AS itemColor, IPITHDR.ISIZ AS itemSize, DIGITS(IPITHDR.ICLS)||'-'||DIGITS(IPITHDR.IVEN)||'-'||DIGITS(IPITHDR.ISTY)||'-'||DIGITS(IPITHDR.ICLR)||'-'||DIGITS(IPITHDR.ISIZ) AS itemNumber, IPITHDR.ISKU AS skuNumber, IPITHDR.ISBN AS isbn, IPITHDR.IGTIN AS gtin, IPITHDR.IGTINTP AS gtinType, CASE WHEN IPITHDR.IPPK='Y' THEN 'Y' ELSE 'N' END AS itemType, IPITHDR.IPKQTY AS prepackQuantity, IPITHDR.IPKTYPE AS prepackType, IPITHDR.IDES AS itemDescription, IPITHDR.ISDS AS shortDescription, IPITHDR.IVST AS vendorStyle, IPITHDR.IHLD AS holdStatus, IPITHDR.ICGP AS coordinateGroup, IPITHDR.IRTN AS vendorReturnPolicyCode, IPITHDR.ISTS AS status, IPITHDR.ISET AS setItem, IPITHDR.ILET AS leadTime, IPITHDR.IITR AS inTransitTime, IPITHDR.IMIN AS vendorMinimumPack, IPITHDR.IMLT AS minimumDistributionLot, IPITHDR.IUCD AS purchaseUnitCode, IPITHDR.IWT2 AS weightPerUnit, IPITHDR.IPKT AS packageType, IPITHDR.ISUQ AS salesPerStockingUnit, IPITHDR.ISUD AS stockingUnitDesc, IPITHDR.ICOR AS countryOfOrigin, IPITHDR.ICOD AS countryOfDelivery, IPITHDR.ICOM AS countryOfManufacture, IPITHDR.IDDC AS defaultDC, IPITHDR.ICCD AS commodityCode, IPITHDR.IDQT AS displayQuantity, IPITHDR.ISQF AS squareFootage, IPITHDR.IVCU AS vcpQuantity, IPITHDR.IVCH AS vcpHeight, IPITHDR.IVCW AS vcpWidth, IPITHDR.IVCL AS vcpLength, IPITHDR.IVCT AS vcpWeight, IPITHDR.IQCT AS qcType, IPITHDR.IBIN AS binLocation, IPITHDR.IMTP AS asnMultipack, IPITHDR.ICYC AS cycleCount, IPITHDR.ITKT AS ticketFormat, IPITHDR.IWTP AS weightTolerancePct, IPITHDR.ICTP AS costTolerancePct, IPITHDR.ILOCF AS lastOrderCost, IPITHDR.ILOCC AS lastOrderCostCurrency, IPITHDR.IVNC AS lastVendorCost, IPITHDR.IACC AS actualCost, IPITHDR.IVLC AS valuedCost, IPITHDR.ILNF-1 AS landingFactor, IPITHDR.INVC AS nextCost, IPITHDR.INCI AS nextCostStarts, IPITHDR.INCD AS nextCostStarts6D, IPITHDR.ILVC AS lowestCost, IPITHDR.IRET AS bookRetail, IPITHDR.ISUG AS suggestedRetail, IPITHDR.IORT AS originalRetail, IPITHDR.ISPR AS splitPriceRetail, IPITHDR.ISPQ AS splitPriceQuantity, IPITHDR.IPLU AS pluRetail, IPITHDR.IALT AS altPLURetail, IPITHDR.ITMP AS promoRetail, IPITHDR.ITFI AS promoStarts, IPITHDR.ITTI AS promoEnds, IPITHDR.ITFR AS promoStarts6D, IPITHDR.ITTO AS promoEnds6D, IPITHDR.IFTC AS pricingType, IPITHDR.IXDI AS creationDate, IPITHDR.IXD2 AS creationDate6D, IPITHDR.IDUN AS lastUpdateUser, IPITHDR.IBSS AS basicStockSubstitute, IPITHDR.ISRC AS entrySource, IPITHDR.ISAL AS temporarilyOnSale, IPITHDR.IMKD AS permanentMarkdown, IPITHDR.ILDI AS lastChainPLUDownload, IPITHDR.IFRI AS firstReceived, IPITHDR.ILRI AS lastReceived, IPITHDR.IFAI AS firstActivity, IPITHDR.ILAI AS lastActivity, IPITHDR.IFSI AS firstSale, IPITHDR.ILSI AS lastSale, IPITHDR.ILPI AS lastPhysical, IPITHDR.ILDL AS lastChainPLUDownload6D, IPITHDR.IFRD AS firstReceived6D, IPITHDR.ILRD AS lastReceived6D, IPITHDR.IFAD AS firstActivity6D, IPITHDR.ILAD AS lastActivity6D, IPITHDR.IFSD AS firstSale6D, IPITHDR.ILSD AS lastSale6D, IPITHDR.ILPD AS lastPhysical6D, IPITHDR.ISBY AS specialPurchase, IPITHDR.IADF AS adItem, IPITHDR.IPPC AS promoPriceCode, IPITHDR.IBAS AS basicItem, IPITHDR.IS#T AS serialNumberTracking, IPITHDR.IMMH AS maintainMonthlyHistory, IPITHDR.IWKH AS weeklyHistory, IPITHDR.ITRK AS weeklyHistoryLevel, IPITHDR.IWHB AS historyFrom, IPITHDR.IWHE AS historyTo, IPITHDR.IZPR AS storePricing, IPITHDR.ICSG AS consignment, IPITHDR.IVAT AS vatCode, IPITHDR.IGSK AS genericSku, IPITHDR.IREC AS reclassed, CASE WHEN IPITHDR.IREC='Y' THEN DIGITS(IPITHDR.IRCL)||'-'||DIGITS(IPITHDR.IRVN)||'-'||DIGITS(IPITHDR.IRST)||'-'||DIGITS(IPITHDR.IRCR)||'-'||DIGITS(IPITHDR.IRSZ) ELSE '' END AS reclassedTo, IPITHDR.IRCL AS reclassedToClass, IPITHDR.IRVN AS reclassedToVendor, IPITHDR.IRST AS reclassedToStyle, IPITHDR.IRCR AS reclassedToColor, IPITHDR.IRSZ AS reclassedToSize, IPITHDR.IMUC AS multipleGtins, IPITHDR.IMSKU AS multipleSkus, IPITHDR.IDSTPCT AS prepackReplenNeedPct, IPITHDR.IORDPCT AS prepackOrderPct, IPITHDR.IHPI1, IPITHDR.IHPI2, IPITHDR.IHPI3, IPITHDR.IPRI01, IPITHDR.IPRI02, IPITHDR.IPRI03, IPITHDR.IPRI04, IPITHDR.IPRI05, IPITHDR.IPRI06, IPITHDR.IPRI07, IPITHDR.IPRI08, IPITHDR.IPRI09, IPITHDR.IPRI10, IPITHDR.IBYR AS buyer FROM IPITHDR
join ipdivsn on ddiv < 2

LEFT JOIN(SELECT DISTINCT BCLS, BVEN, BSTY, BCLR, BSIZ, 'P' subType FROM IPBSSUB) primary
ON primary.BCLS=ICLS AND primary.BVEN=IVEN AND primary.BSTY=ISTY AND primary.BCLR=ICLR AND primary.BSIZ=ISIZ
LEFT JOIN (SELECT DISTINCT BSCL, BSVE, BSST, BSCR, BSSI, 'S' subType, BCLS, BVEN, BSTY, BCLR, BSIZ FROM IPBSSUB) secondary
ON secondary.BSCL=ICLS AND secondary.BSVE=IVEN AND secondary.BSST=ISTY AND secondary.BSCR=ICLR AND secondary.BSSI=ISIZ
WHERE (LOWER(IPITHDR.IDES) LIKE LOWER('%a%') {ESCAPE '\'} AND IPITHDR.IDES IS NOT NULL) AND IPPK!='Y' AND ISET!='Y' AND ICSG!='Y'
=== 2017-08-09 18:26:30,354 [c-12] DEBUG PoolableSQLConnectionFactory - [builtinApplication.substitutePrimaryItems] makeObject() created an unpooled Connection '406774776'
=== 2017-08-09 18:26:30,354 [c-12] DEBUG SQLConnectionManager - [builtinApplication.substitutePrimaryItems] Borrowed connection '406774776'
=== 2017-08-09 18:26:30,354 [c-12] INFO SQLDriver - [builtinApplication.substitutePrimaryItems] Executing SQL query on 'iptsfil5' using connection '406774776': SELECT COALESCE(primary.subType, secondary.subType, '') AS substituteItemType, DIGITS(COALESCE(primary.BCLS,secondary.BCLS))||'-'|| DIGITS(COALESCE(primary.BVEN,secondary.BVEN))||'-'|| DIGITS(COALESCE(primary.BSTY,secondary.BSTY))||'-'|| DIGITS(COALESCE(primary.BCLR,secondary.BCLR))||'-'|| DIGITS(COALESCE(primary.BSIZ,secondary.BSIZ)) AS primaryItemNumber, IPITHDR.ICNCPT AS concept, IPITHDR.IDIV AS division, IPITHDR.IDPT AS department, IPITHDR.ISBD AS subDepartment, IPITHDR.ICLS AS itemClass, IPITHDR.IVEN AS itemVendor, IPITHDR.ISTY AS itemStyle, IPITHDR.ICLR AS itemColor, IPITHDR.ISIZ AS itemSize, DIGITS(IPITHDR.ICLS)||'-'||DIGITS(IPITHDR.IVEN)||'-'||DIGITS(IPITHDR.ISTY)||'-'||DIGITS(IPITHDR.ICLR)||'-'||DIGITS(IPITHDR.ISIZ) AS itemNumber, IPITHDR.ISKU AS skuNumber, IPITHDR.ISBN AS isbn, IPITHDR.IGTIN AS gtin, IPITHDR.IGTINTP AS gtinType, CASE WHEN IPITHDR.IPPK='Y' THEN 'Y' ELSE 'N' END AS itemType, IPITHDR.IPKQTY AS prepackQuantity, IPITHDR.IPKTYPE AS prepackType, IPITHDR.IDES AS itemDescription, IPITHDR.ISDS AS shortDescription, IPITHDR.IVST AS vendorStyle, IPITHDR.IHLD AS holdStatus, IPITHDR.ICGP AS coordinateGroup, IPITHDR.IRTN AS vendorReturnPolicyCode, IPITHDR.ISTS AS status, IPITHDR.ISET AS setItem, IPITHDR.ILET AS leadTime, IPITHDR.IITR AS inTransitTime, IPITHDR.IMIN AS vendorMinimumPack, IPITHDR.IMLT AS minimumDistributionLot, IPITHDR.IUCD AS purchaseUnitCode, IPITHDR.IWT2 AS weightPerUnit, IPITHDR.IPKT AS packageType, IPITHDR.ISUQ AS salesPerStockingUnit, IPITHDR.ISUD AS stockingUnitDesc, IPITHDR.ICOR AS countryOfOrigin, IPITHDR.ICOD AS countryOfDelivery, IPITHDR.ICOM AS countryOfManufacture, IPITHDR.IDDC AS defaultDC, IPITHDR.ICCD AS commodityCode, IPITHDR.IDQT AS displayQuantity, IPITHDR.ISQF AS squareFootage, IPITHDR.IVCU AS vcpQuantity, IPITHDR.IVCH AS vcpHeight, IPITHDR.IVCW AS vcpWidth, IPITHDR.IVCL AS vcpLength, IPITHDR.IVCT AS vcpWeight, IPITHDR.IQCT AS qcType, IPITHDR.IBIN AS binLocation, IPITHDR.IMTP AS asnMultipack, IPITHDR.ICYC AS cycleCount, IPITHDR.ITKT AS ticketFormat, IPITHDR.IWTP AS weightTolerancePct, IPITHDR.ICTP AS costTolerancePct, IPITHDR.ILOCF AS lastOrderCost, IPITHDR.ILOCC AS lastOrderCostCurrency, IPITHDR.IVNC AS lastVendorCost, IPITHDR.IACC AS actualCost, IPITHDR.IVLC AS valuedCost, IPITHDR.ILNF-1 AS landingFactor, IPITHDR.INVC AS nextCost, IPITHDR.INCI AS nextCostStarts, IPITHDR.INCD AS nextCostStarts6D, IPITHDR.ILVC AS lowestCost, IPITHDR.IRET AS bookRetail, IPITHDR.ISUG AS suggestedRetail, IPITHDR.IORT AS originalRetail, IPITHDR.ISPR AS splitPriceRetail, IPITHDR.ISPQ AS splitPriceQuantity, IPITHDR.IPLU AS pluRetail, IPITHDR.IALT AS altPLURetail, IPITHDR.ITMP AS promoRetail, IPITHDR.ITFI AS promoStarts, IPITHDR.ITTI AS promoEnds, IPITHDR.ITFR AS promoStarts6D, IPITHDR.ITTO AS promoEnds6D, IPITHDR.IFTC AS pricingType, IPITHDR.IXDI AS creationDate, IPITHDR.IXD2 AS creationDate6D, IPITHDR.IDUN AS lastUpdateUser, IPITHDR.IBSS AS basicStockSubstitute, IPITHDR.ISRC AS entrySource, IPITHDR.ISAL AS temporarilyOnSale, IPITHDR.IMKD AS permanentMarkdown, IPITHDR.ILDI AS lastChainPLUDownload, IPITHDR.IFRI AS firstReceived, IPITHDR.ILRI AS lastReceived, IPITHDR.IFAI AS firstActivity, IPITHDR.ILAI AS lastActivity, IPITHDR.IFSI AS firstSale, IPITHDR.ILSI AS lastSale, IPITHDR.ILPI AS lastPhysical, IPITHDR.ILDL AS lastChainPLUDownload6D, IPITHDR.IFRD AS firstReceived6D, IPITHDR.ILRD AS lastReceived6D, IPITHDR.IFAD AS firstActivity6D, IPITHDR.ILAD AS lastActivity6D, IPITHDR.IFSD AS firstSale6D, IPITHDR.ILSD AS lastSale6D, IPITHDR.ILPD AS lastPhysical6D, IPITHDR.ISBY AS specialPurchase, IPITHDR.IADF AS adItem, IPITHDR.IPPC AS promoPriceCode, IPITHDR.IBAS AS basicItem, IPITHDR.IS#T AS serialNumberTracking, IPITHDR.IMMH AS maintainMonthlyHistory, IPITHDR.IWKH AS weeklyHistory, IPITHDR.ITRK AS weeklyHistoryLevel, IPITHDR.IWHB AS historyFrom, IPITHDR.IWHE AS historyTo, IPITHDR.IZPR AS storePricing, IPITHDR.ICSG AS consignment, IPITHDR.IVAT AS vatCode, IPITHDR.IGSK AS genericSku, IPITHDR.IREC AS reclassed, CASE WHEN IPITHDR.IREC='Y' THEN DIGITS(IPITHDR.IRCL)||'-'||DIGITS(IPITHDR.IRVN)||'-'||DIGITS(IPITHDR.IRST)||'-'||DIGITS(IPITHDR.IRCR)||'-'||DIGITS(IPITHDR.IRSZ) ELSE '' END AS reclassedTo, IPITHDR.IRCL AS reclassedToClass, IPITHDR.IRVN AS reclassedToVendor, IPITHDR.IRST AS reclassedToStyle, IPITHDR.IRCR AS reclassedToColor, IPITHDR.IRSZ AS reclassedToSize, IPITHDR.IMUC AS multipleGtins, IPITHDR.IMSKU AS multipleSkus, IPITHDR.IDSTPCT AS prepackReplenNeedPct, IPITHDR.IORDPCT AS prepackOrderPct, IPITHDR.IHPI1, IPITHDR.IHPI2, IPITHDR.IHPI3, IPITHDR.IPRI01, IPITHDR.IPRI02, IPITHDR.IPRI03, IPITHDR.IPRI04, IPITHDR.IPRI05, IPITHDR.IPRI06, IPITHDR.IPRI07, IPITHDR.IPRI08, IPITHDR.IPRI09, IPITHDR.IPRI10, IPITHDR.IBYR AS buyer FROM IPITHDR
join ipdivsn on ddiv < 2

LEFT JOIN(SELECT DISTINCT BCLS, BVEN, BSTY, BCLR, BSIZ, 'P' subType FROM IPBSSUB) primary
ON primary.BCLS=ICLS AND primary.BVEN=IVEN AND primary.BSTY=ISTY AND primary.BCLR=ICLR AND primary.BSIZ=ISIZ
LEFT JOIN (SELECT DISTINCT BSCL, BSVE, BSST, BSCR, BSSI, 'S' subType, BCLS, BVEN, BSTY, BCLR, BSIZ FROM IPBSSUB) secondary
ON secondary.BSCL=ICLS AND secondary.BSVE=IVEN AND secondary.BSST=ISTY AND secondary.BSCR=ICLR AND secondary.BSSI=ISIZ
WHERE (LOWER(IPITHDR.IDES) LIKE LOWER('%a%') {ESCAPE '\'} AND IPITHDR.IDES IS NOT NULL) AND IPPK!='Y' AND ISET!='Y' AND ICSG!='Y'
=== 2017-08-09 18:29:00,180 [c-12] INFO DSResponse - DSResponse: List with 1879 items
=== 2017-08-09 18:29:00,181 [c-12] DEBUG DSRequest - About to free up resources for request of type fetch on DataSource ReplenAvailableItems
=== 2017-08-09 18:29:00,645 [c-12] DEBUG ExcelDataExport - Export streaming mode: true
=== 2017-08-09 18:29:01,311 [c-12] DEBUG ExcelDataExport - Streaming row 1000: Used memory: 341.86MB; Free memory: 15838.52MB
=== 2017-08-09 18:29:01,510 [c-12] DEBUG SQLDriver - Freeing SQLDriver dbConnection 406774776 for SQLDriver instance 1161246187
=== 2017-08-09 18:29:01,510 [c-12] DEBUG SQLConnectionManager - About to close PoolGuardConnectionWrapper with hashcode "406774776"

OOXML Export (streamResults=true)

=== 2017-08-09 19:35:01,473 [c-21] INFO RequestContext - URL: '/ipgui/ipgui/sc/IDACall/Results.xlsx', User-Agent: 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36': Safari with Accept-Encoding header
=== 2017-08-09 19:35:01,507 [c-21] DEBUG XML - Parsed XML from (in memory stream): 33ms
=== 2017-08-09 19:35:01,507 [c-21] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'transaction' but null was returned
=== 2017-08-09 19:35:01,509 [c-21] DEBUG PoolableDataSourceFactory - Created DataSource 3983 of type 'Object' and assigned it to thread http-nio-8080-exec-21
=== 2017-08-09 19:35:01,510 [c-21] DEBUG PoolableDataSourceFactory - Created DataSource 3984 of type 'List' and assigned it to thread http-nio-8080-exec-21
=== 2017-08-09 19:35:01,510 [c-21] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'elem' but null was returned
=== 2017-08-09 19:35:01,511 [c-21] DEBUG RPCManager - Processing 1 requests.
=== 2017-08-09 19:35:01,513 [c-21] DEBUG XML - Parsed XML from (in memory stream): 2ms
=== 2017-08-09 19:35:01,515 [c-21] DEBUG PoolableDataSourceFactory - Created DataSource 3985 of type 'DataSource' and assigned it to thread http-nio-8080-exec-21
=== 2017-08-09 19:35:01,515 [c-21] DEBUG PoolableDataSourceFactory - Created DataSource 3986 of type 'DataSourceField' and assigned it to thread http-nio-8080-exec-21
=== 2017-08-09 19:35:01,516 [c-21] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'field' but null was returned
=== 2017-08-09 19:35:01,516 [c-21] DEBUG PoolableDataSourceFactory - Created DataSource 3987 of type 'JS' and assigned it to thread http-nio-8080-exec-21
=== 2017-08-09 19:35:01,517 [c-21] DEBUG PoolableDataSourceFactory - Created DataSource 3988 of type 'Object' and assigned it to thread http-nio-8080-exec-21
=== 2017-08-09 19:35:01,518 [c-21] DEBUG PoolableDataSourceFactory - Created DataSource 3989 of type 'ListGridField' and assigned it to thread http-nio-8080-exec-21
=== 2017-08-09 19:35:01,521 [c-21] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'validator' but null was returned
=== 2017-08-09 19:35:01,522 [c-21] DEBUG PoolableDataSourceFactory - Created DataSource 3990 of type 'Validator' and assigned it to thread http-nio-8080-exec-21
=== 2017-08-09 19:35:01,523 [c-21] DEBUG PoolableDataSourceFactory - Created DataSource 3991 of type 'ServerObject' and assigned it to thread http-nio-8080-exec-21
=== 2017-08-09 19:35:01,524 [c-21] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'serverObject' but null was returned
=== 2017-08-09 19:35:01,525 [c-21] DEBUG PoolableDataSourceFactory - Created DataSource 3992 of type 'ValueMap' and assigned it to thread http-nio-8080-exec-21
=== 2017-08-09 19:35:01,526 [c-21] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'SDC' but null was returned
=== 2017-08-09 19:35:01,526 [c-21] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'pickListCriteria' but null was returned
=== 2017-08-09 19:35:01,533 [c-21] DEBUG PoolableDataSourceFactory - Created DataSource 3993 of type 'OperationBinding' and assigned it to thread http-nio-8080-exec-21
=== 2017-08-09 19:35:01,535 [c-21] DEBUG PoolableDataSourceFactory - Tried to create DataSource of type 'operationBinding' but null was returned
=== 2017-08-09 19:35:01,538 [c-21] DEBUG PoolableDataSourceFactory - Created DataSource 3994 of type 'ReplenAvailableItems' and assigned it to thread http-nio-8080-exec-21
=== 2017-08-09 19:35:01,538 [c-21] DEBUG DSRequest - Caching instance 3994 of DS 'ReplenAvailableItems' from DSRequest.getDataSource()
=== 2017-08-09 19:35:01,538 [c-21] DEBUG DSRequest - Caching instance 3994 of DS ReplenAvailableItems
=== 2017-08-09 19:35:01,538 [c-21] DEBUG RPCManager - Request #1 (DSRequest) payload: {
criteria:{
operator:"iContains",
fieldName:"itemDescription",
value:"A",
_constructor:"AdvancedCriteria"
},
operationConfig:{
dataSource:"ReplenAvailableItems",
repo:null,
operationType:"fetch",
textMatchStyle:"exact"
},
exportResults:true,
exportAs:"ooxml",
exportDelimiter:",",
exportTitleSeparatorChar:"",
exportFilename:"Results.xlsx",
exportPath:null,
exportDisplay:"download",
lineBreakStyle:"default",
exportFields:[
"substituteItemType",
"primaryItemNumber",
"itemNumber",
"skuNumber",
"isbn",
"gtin",
"itemDescription",
"shortDescription",
"vendorStyle",
"coordinateGroup",
"lastVendorCost",
"bookRetail"
],
exportHeader:null,
exportHeaderless:null,
exportFooter:null,
exportFieldTitles:{
substituteItemType:"Substitute Type",
primaryItemNumber:"Primary Item Number",
itemNumber:"Item Number",
skuNumber:"SKU Number",
isbn:"ISBN",
gtin:"GTIN",
itemDescription:"Item Description",
shortDescription:"Short Description",
vendorStyle:"Vendor Style",
coordinateGroup:"Coordinate Group",
lastVendorCost:"Vendor Cost",
bookRetail:"Book Retail"
},
exportDatesAsFormattedString:null,
exportRawValues:true,
exportCurrencySymbol:"$",
exportHeaderSpans:null,
exportOtherFields:{
substituteItemType:"Substitute Type",
primaryItemNumber:"Primary Item Number",
itemNumber:"Item Number",
skuNumber:"SKU Number",
isbn:"ISBN",
gtin:"GTIN",
itemDescription:"Item Description",
shortDescription:"Short Description",
vendorStyle:"Vendor Style",
coordinateGroup:"Coordinate Group",
lastVendorCost:"Vendor Cost",
bookRetail:"Book Retail"
},
exportWrapHeaderTitles:null,
exportStreaming:true,
exportPropertyIdentifier:"name",
appID:"builtinApplication",
operation:"substitutePrimaryItems",
oldValues:{
operator:"iContains",
fieldName:"itemDescription",
value:"A",
_constructor:"AdvancedCriteria"
},
streamResults:true,
exportToFilesystem:true,
exportToClient:false
}
=== 2017-08-09 19:35:01,539 [c-21] WARN IpAuthenticationManager - Property charSetNormalize not found in server.properties
=== 2017-08-09 19:35:01,546 [c-21] WARN IpAuthenticationManager - Property charSetNormalize not found in server.properties
=== 2017-08-09 19:35:01,546 [c-21] INFO IpIDACall - Performing 1 operation(s)
=== 2017-08-09 19:35:01,546 [c-21] INFO IpIDACall - Processing DataSource ReplenAvailableItems
=== 2017-08-09 19:35:01,546 [c-21] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2017-08-09 19:35:01,546 [c-21] DEBUG DeclarativeSecurity - DataSource ReplenAvailableItems is not in the pre-checked list, processing...
=== 2017-08-09 19:35:01,547 [c-21] DEBUG AppBase - [builtinApplication.substitutePrimaryItems] No userTypes defined, allowing anyone access to all operations for this application
=== 2017-08-09 19:35:01,547 [c-21] DEBUG AppBase - [builtinApplication.substitutePrimaryItems] No public zero-argument method named '_substitutePrimaryItems' found, performing generic datasource operation
=== 2017-08-09 19:35:01,547 [c-21] INFO SQLDataSource - [builtinApplication.substitutePrimaryItems] Performing fetch operation with
criteria: {operator:"iContains",fieldName:"itemDescription",value:"A",_constructor:"AdvancedCriteria"} values: {operator:"iContains",fieldName:"itemDescription",value:"A",_constructor:"AdvancedCriteria"}
=== 2017-08-09 19:35:01,596 [c-21] INFO SQLDataSource - [builtinApplication.substitutePrimaryItems] derived query: SELECT $defaultSelectClause FROM $defaultTableClause
join ipdivsn on ddiv < 2

LEFT JOIN(SELECT DISTINCT BCLS, BVEN, BSTY, BCLR, BSIZ, 'P' subType FROM IPBSSUB) primary
ON primary.BCLS=ICLS AND primary.BVEN=IVEN AND primary.BSTY=ISTY AND primary.BCLR=ICLR AND primary.BSIZ=ISIZ
LEFT JOIN (SELECT DISTINCT BSCL, BSVE, BSST, BSCR, BSSI, 'S' subType, BCLS, BVEN, BSTY, BCLR, BSIZ FROM IPBSSUB) secondary
ON secondary.BSCL=ICLS AND secondary.BSVE=IVEN AND secondary.BSST=ISTY AND secondary.BSCR=ICLR AND secondary.BSSI=ISIZ
WHERE $defaultWhereClause AND IPPK!='Y' AND ISET!='Y' AND ICSG!='Y'
=== 2017-08-09 19:35:01,596 [c-21] INFO SQLDataSource - [builtinApplication.substitutePrimaryItems] 3994: Executing SQL query on 'iptsfil5': SELECT COALESCE(primary.subType, secondary.subType, '') AS substituteItemType, DIGITS(COALESCE(primary.BCLS,secondary.BCLS))||'-'|| DIGITS(COALESCE(primary.BVEN,secondary.BVEN))||'-'|| DIGITS(COALESCE(primary.BSTY,secondary.BSTY))||'-'|| DIGITS(COALESCE(primary.BCLR,secondary.BCLR))||'-'|| DIGITS(COALESCE(primary.BSIZ,secondary.BSIZ)) AS primaryItemNumber, IPITHDR.ICNCPT AS concept, IPITHDR.IDIV AS division, IPITHDR.IDPT AS department, IPITHDR.ISBD AS subDepartment, IPITHDR.ICLS AS itemClass, IPITHDR.IVEN AS itemVendor, IPITHDR.ISTY AS itemStyle, IPITHDR.ICLR AS itemColor, IPITHDR.ISIZ AS itemSize, DIGITS(IPITHDR.ICLS)||'-'||DIGITS(IPITHDR.IVEN)||'-'||DIGITS(IPITHDR.ISTY)||'-'||DIGITS(IPITHDR.ICLR)||'-'||DIGITS(IPITHDR.ISIZ) AS itemNumber, IPITHDR.ISKU AS skuNumber, IPITHDR.ISBN AS isbn, IPITHDR.IGTIN AS gtin, IPITHDR.IGTINTP AS gtinType, CASE WHEN IPITHDR.IPPK='Y' THEN 'Y' ELSE 'N' END AS itemType, IPITHDR.IPKQTY AS prepackQuantity, IPITHDR.IPKTYPE AS prepackType, IPITHDR.IDES AS itemDescription, IPITHDR.ISDS AS shortDescription, IPITHDR.IVST AS vendorStyle, IPITHDR.IHLD AS holdStatus, IPITHDR.ICGP AS coordinateGroup, IPITHDR.IRTN AS vendorReturnPolicyCode, IPITHDR.ISTS AS status, IPITHDR.ISET AS setItem, IPITHDR.ILET AS leadTime, IPITHDR.IITR AS inTransitTime, IPITHDR.IMIN AS vendorMinimumPack, IPITHDR.IMLT AS minimumDistributionLot, IPITHDR.IUCD AS purchaseUnitCode, IPITHDR.IWT2 AS weightPerUnit, IPITHDR.IPKT AS packageType, IPITHDR.ISUQ AS salesPerStockingUnit, IPITHDR.ISUD AS stockingUnitDesc, IPITHDR.ICOR AS countryOfOrigin, IPITHDR.ICOD AS countryOfDelivery, IPITHDR.ICOM AS countryOfManufacture, IPITHDR.IDDC AS defaultDC, IPITHDR.ICCD AS commodityCode, IPITHDR.IDQT AS displayQuantity, IPITHDR.ISQF AS squareFootage, IPITHDR.IVCU AS vcpQuantity, IPITHDR.IVCH AS vcpHeight, IPITHDR.IVCW AS vcpWidth, IPITHDR.IVCL AS vcpLength, IPITHDR.IVCT AS vcpWeight, IPITHDR.IQCT AS qcType, IPITHDR.IBIN AS binLocation, IPITHDR.IMTP AS asnMultipack, IPITHDR.ICYC AS cycleCount, IPITHDR.ITKT AS ticketFormat, IPITHDR.IWTP AS weightTolerancePct, IPITHDR.ICTP AS costTolerancePct, IPITHDR.ILOCF AS lastOrderCost, IPITHDR.ILOCC AS lastOrderCostCurrency, IPITHDR.IVNC AS lastVendorCost, IPITHDR.IACC AS actualCost, IPITHDR.IVLC AS valuedCost, IPITHDR.ILNF-1 AS landingFactor, IPITHDR.INVC AS nextCost, IPITHDR.INCI AS nextCostStarts, IPITHDR.INCD AS nextCostStarts6D, IPITHDR.ILVC AS lowestCost, IPITHDR.IRET AS bookRetail, IPITHDR.ISUG AS suggestedRetail, IPITHDR.IORT AS originalRetail, IPITHDR.ISPR AS splitPriceRetail, IPITHDR.ISPQ AS splitPriceQuantity, IPITHDR.IPLU AS pluRetail, IPITHDR.IALT AS altPLURetail, IPITHDR.ITMP AS promoRetail, IPITHDR.ITFI AS promoStarts, IPITHDR.ITTI AS promoEnds, IPITHDR.ITFR AS promoStarts6D, IPITHDR.ITTO AS promoEnds6D, IPITHDR.IFTC AS pricingType, IPITHDR.IXDI AS creationDate, IPITHDR.IXD2 AS creationDate6D, IPITHDR.IDUN AS lastUpdateUser, IPITHDR.IBSS AS basicStockSubstitute, IPITHDR.ISRC AS entrySource, IPITHDR.ISAL AS temporarilyOnSale, IPITHDR.IMKD AS permanentMarkdown, IPITHDR.ILDI AS lastChainPLUDownload, IPITHDR.IFRI AS firstReceived, IPITHDR.ILRI AS lastReceived, IPITHDR.IFAI AS firstActivity, IPITHDR.ILAI AS lastActivity, IPITHDR.IFSI AS firstSale, IPITHDR.ILSI AS lastSale, IPITHDR.ILPI AS lastPhysical, IPITHDR.ILDL AS lastChainPLUDownload6D, IPITHDR.IFRD AS firstReceived6D, IPITHDR.ILRD AS lastReceived6D, IPITHDR.IFAD AS firstActivity6D, IPITHDR.ILAD AS lastActivity6D, IPITHDR.IFSD AS firstSale6D, IPITHDR.ILSD AS lastSale6D, IPITHDR.ILPD AS lastPhysical6D, IPITHDR.ISBY AS specialPurchase, IPITHDR.IADF AS adItem, IPITHDR.IPPC AS promoPriceCode, IPITHDR.IBAS AS basicItem, IPITHDR.IS#T AS serialNumberTracking, IPITHDR.IMMH AS maintainMonthlyHistory, IPITHDR.IWKH AS weeklyHistory, IPITHDR.ITRK AS weeklyHistoryLevel, IPITHDR.IWHB AS historyFrom, IPITHDR.IWHE AS historyTo, IPITHDR.IZPR AS storePricing, IPITHDR.ICSG AS consignment, IPITHDR.IVAT AS vatCode, IPITHDR.IGSK AS genericSku, IPITHDR.IREC AS reclassed, CASE WHEN IPITHDR.IREC='Y' THEN DIGITS(IPITHDR.IRCL)||'-'||DIGITS(IPITHDR.IRVN)||'-'||DIGITS(IPITHDR.IRST)||'-'||DIGITS(IPITHDR.IRCR)||'-'||DIGITS(IPITHDR.IRSZ) ELSE '' END AS reclassedTo, IPITHDR.IRCL AS reclassedToClass, IPITHDR.IRVN AS reclassedToVendor, IPITHDR.IRST AS reclassedToStyle, IPITHDR.IRCR AS reclassedToColor, IPITHDR.IRSZ AS reclassedToSize, IPITHDR.IMUC AS multipleGtins, IPITHDR.IMSKU AS multipleSkus, IPITHDR.IDSTPCT AS prepackReplenNeedPct, IPITHDR.IORDPCT AS prepackOrderPct, IPITHDR.IHPI1, IPITHDR.IHPI2, IPITHDR.IHPI3, IPITHDR.IPRI01, IPITHDR.IPRI02, IPITHDR.IPRI03, IPITHDR.IPRI04, IPITHDR.IPRI05, IPITHDR.IPRI06, IPITHDR.IPRI07, IPITHDR.IPRI08, IPITHDR.IPRI09, IPITHDR.IPRI10, IPITHDR.IBYR AS buyer FROM IPITHDR
join ipdivsn on ddiv < 2

LEFT JOIN(SELECT DISTINCT BCLS, BVEN, BSTY, BCLR, BSIZ, 'P' subType FROM IPBSSUB) primary
ON primary.BCLS=ICLS AND primary.BVEN=IVEN AND primary.BSTY=ISTY AND primary.BCLR=ICLR AND primary.BSIZ=ISIZ
LEFT JOIN (SELECT DISTINCT BSCL, BSVE, BSST, BSCR, BSSI, 'S' subType, BCLS, BVEN, BSTY, BCLR, BSIZ FROM IPBSSUB) secondary
ON secondary.BSCL=ICLS AND secondary.BSVE=IVEN AND secondary.BSST=ISTY AND secondary.BSCR=ICLR AND secondary.BSSI=ISIZ
WHERE (LOWER(IPITHDR.IDES) LIKE LOWER('%a%') {ESCAPE '\'} AND IPITHDR.IDES IS NOT NULL) AND IPPK!='Y' AND ISET!='Y' AND ICSG!='Y'
=== 2017-08-09 19:35:01,598 [c-21] DEBUG PoolableSQLConnectionFactory - [builtinApplication.substitutePrimaryItems] makeObject() created an unpooled Connection '687855846'
=== 2017-08-09 19:35:01,598 [c-21] DEBUG SQLConnectionManager - [builtinApplication.substitutePrimaryItems] Borrowed connection '687855846'
=== 2017-08-09 19:35:01,598 [c-21] INFO SQLDriver - [builtinApplication.substitutePrimaryItems] Executing SQL query on 'iptsfil5' using connection '687855846': SELECT COALESCE(primary.subType, secondary.subType, '') AS substituteItemType, DIGITS(COALESCE(primary.BCLS,secondary.BCLS))||'-'|| DIGITS(COALESCE(primary.BVEN,secondary.BVEN))||'-'|| DIGITS(COALESCE(primary.BSTY,secondary.BSTY))||'-'|| DIGITS(COALESCE(primary.BCLR,secondary.BCLR))||'-'|| DIGITS(COALESCE(primary.BSIZ,secondary.BSIZ)) AS primaryItemNumber, IPITHDR.ICNCPT AS concept, IPITHDR.IDIV AS division, IPITHDR.IDPT AS department, IPITHDR.ISBD AS subDepartment, IPITHDR.ICLS AS itemClass, IPITHDR.IVEN AS itemVendor, IPITHDR.ISTY AS itemStyle, IPITHDR.ICLR AS itemColor, IPITHDR.ISIZ AS itemSize, DIGITS(IPITHDR.ICLS)||'-'||DIGITS(IPITHDR.IVEN)||'-'||DIGITS(IPITHDR.ISTY)||'-'||DIGITS(IPITHDR.ICLR)||'-'||DIGITS(IPITHDR.ISIZ) AS itemNumber, IPITHDR.ISKU AS skuNumber, IPITHDR.ISBN AS isbn, IPITHDR.IGTIN AS gtin, IPITHDR.IGTINTP AS gtinType, CASE WHEN IPITHDR.IPPK='Y' THEN 'Y' ELSE 'N' END AS itemType, IPITHDR.IPKQTY AS prepackQuantity, IPITHDR.IPKTYPE AS prepackType, IPITHDR.IDES AS itemDescription, IPITHDR.ISDS AS shortDescription, IPITHDR.IVST AS vendorStyle, IPITHDR.IHLD AS holdStatus, IPITHDR.ICGP AS coordinateGroup, IPITHDR.IRTN AS vendorReturnPolicyCode, IPITHDR.ISTS AS status, IPITHDR.ISET AS setItem, IPITHDR.ILET AS leadTime, IPITHDR.IITR AS inTransitTime, IPITHDR.IMIN AS vendorMinimumPack, IPITHDR.IMLT AS minimumDistributionLot, IPITHDR.IUCD AS purchaseUnitCode, IPITHDR.IWT2 AS weightPerUnit, IPITHDR.IPKT AS packageType, IPITHDR.ISUQ AS salesPerStockingUnit, IPITHDR.ISUD AS stockingUnitDesc, IPITHDR.ICOR AS countryOfOrigin, IPITHDR.ICOD AS countryOfDelivery, IPITHDR.ICOM AS countryOfManufacture, IPITHDR.IDDC AS defaultDC, IPITHDR.ICCD AS commodityCode, IPITHDR.IDQT AS displayQuantity, IPITHDR.ISQF AS squareFootage, IPITHDR.IVCU AS vcpQuantity, IPITHDR.IVCH AS vcpHeight, IPITHDR.IVCW AS vcpWidth, IPITHDR.IVCL AS vcpLength, IPITHDR.IVCT AS vcpWeight, IPITHDR.IQCT AS qcType, IPITHDR.IBIN AS binLocation, IPITHDR.IMTP AS asnMultipack, IPITHDR.ICYC AS cycleCount, IPITHDR.ITKT AS ticketFormat, IPITHDR.IWTP AS weightTolerancePct, IPITHDR.ICTP AS costTolerancePct, IPITHDR.ILOCF AS lastOrderCost, IPITHDR.ILOCC AS lastOrderCostCurrency, IPITHDR.IVNC AS lastVendorCost, IPITHDR.IACC AS actualCost, IPITHDR.IVLC AS valuedCost, IPITHDR.ILNF-1 AS landingFactor, IPITHDR.INVC AS nextCost, IPITHDR.INCI AS nextCostStarts, IPITHDR.INCD AS nextCostStarts6D, IPITHDR.ILVC AS lowestCost, IPITHDR.IRET AS bookRetail, IPITHDR.ISUG AS suggestedRetail, IPITHDR.IORT AS originalRetail, IPITHDR.ISPR AS splitPriceRetail, IPITHDR.ISPQ AS splitPriceQuantity, IPITHDR.IPLU AS pluRetail, IPITHDR.IALT AS altPLURetail, IPITHDR.ITMP AS promoRetail, IPITHDR.ITFI AS promoStarts, IPITHDR.ITTI AS promoEnds, IPITHDR.ITFR AS promoStarts6D, IPITHDR.ITTO AS promoEnds6D, IPITHDR.IFTC AS pricingType, IPITHDR.IXDI AS creationDate, IPITHDR.IXD2 AS creationDate6D, IPITHDR.IDUN AS lastUpdateUser, IPITHDR.IBSS AS basicStockSubstitute, IPITHDR.ISRC AS entrySource, IPITHDR.ISAL AS temporarilyOnSale, IPITHDR.IMKD AS permanentMarkdown, IPITHDR.ILDI AS lastChainPLUDownload, IPITHDR.IFRI AS firstReceived, IPITHDR.ILRI AS lastReceived, IPITHDR.IFAI AS firstActivity, IPITHDR.ILAI AS lastActivity, IPITHDR.IFSI AS firstSale, IPITHDR.ILSI AS lastSale, IPITHDR.ILPI AS lastPhysical, IPITHDR.ILDL AS lastChainPLUDownload6D, IPITHDR.IFRD AS firstReceived6D, IPITHDR.ILRD AS lastReceived6D, IPITHDR.IFAD AS firstActivity6D, IPITHDR.ILAD AS lastActivity6D, IPITHDR.IFSD AS firstSale6D, IPITHDR.ILSD AS lastSale6D, IPITHDR.ILPD AS lastPhysical6D, IPITHDR.ISBY AS specialPurchase, IPITHDR.IADF AS adItem, IPITHDR.IPPC AS promoPriceCode, IPITHDR.IBAS AS basicItem, IPITHDR.IS#T AS serialNumberTracking, IPITHDR.IMMH AS maintainMonthlyHistory, IPITHDR.IWKH AS weeklyHistory, IPITHDR.ITRK AS weeklyHistoryLevel, IPITHDR.IWHB AS historyFrom, IPITHDR.IWHE AS historyTo, IPITHDR.IZPR AS storePricing, IPITHDR.ICSG AS consignment, IPITHDR.IVAT AS vatCode, IPITHDR.IGSK AS genericSku, IPITHDR.IREC AS reclassed, CASE WHEN IPITHDR.IREC='Y' THEN DIGITS(IPITHDR.IRCL)||'-'||DIGITS(IPITHDR.IRVN)||'-'||DIGITS(IPITHDR.IRST)||'-'||DIGITS(IPITHDR.IRCR)||'-'||DIGITS(IPITHDR.IRSZ) ELSE '' END AS reclassedTo, IPITHDR.IRCL AS reclassedToClass, IPITHDR.IRVN AS reclassedToVendor, IPITHDR.IRST AS reclassedToStyle, IPITHDR.IRCR AS reclassedToColor, IPITHDR.IRSZ AS reclassedToSize, IPITHDR.IMUC AS multipleGtins, IPITHDR.IMSKU AS multipleSkus, IPITHDR.IDSTPCT AS prepackReplenNeedPct, IPITHDR.IORDPCT AS prepackOrderPct, IPITHDR.IHPI1, IPITHDR.IHPI2, IPITHDR.IHPI3, IPITHDR.IPRI01, IPITHDR.IPRI02, IPITHDR.IPRI03, IPITHDR.IPRI04, IPITHDR.IPRI05, IPITHDR.IPRI06, IPITHDR.IPRI07, IPITHDR.IPRI08, IPITHDR.IPRI09, IPITHDR.IPRI10, IPITHDR.IBYR AS buyer FROM IPITHDR
join ipdivsn on ddiv < 2

LEFT JOIN(SELECT DISTINCT BCLS, BVEN, BSTY, BCLR, BSIZ, 'P' subType FROM IPBSSUB) primary
ON primary.BCLS=ICLS AND primary.BVEN=IVEN AND primary.BSTY=ISTY AND primary.BCLR=ICLR AND primary.BSIZ=ISIZ
LEFT JOIN (SELECT DISTINCT BSCL, BSVE, BSST, BSCR, BSSI, 'S' subType, BCLS, BVEN, BSTY, BCLR, BSIZ FROM IPBSSUB) secondary
ON secondary.BSCL=ICLS AND secondary.BSVE=IVEN AND secondary.BSST=ISTY AND secondary.BSCR=ICLR AND secondary.BSSI=ISIZ
WHERE (LOWER(IPITHDR.IDES) LIKE LOWER('%a%') {ESCAPE '\'} AND IPITHDR.IDES IS NOT NULL) AND IPPK!='Y' AND ISET!='Y' AND ICSG!='Y'
=== 2017-08-09 19:35:01,735 [c-21] DEBUG SQLDriver - [builtinApplication.substitutePrimaryItems] Streaming the response
=== 2017-08-09 19:35:01,751 [c-21] DEBUG DSRequest - freeOnExecute is false for request of type fetch on DataSource ReplenAvailableItems - not freeing resources!
=== 2017-08-09 19:35:02,158 [c-21] DEBUG ExcelDataExport - Export streaming mode: true
=== 2017-08-09 19:36:27,983 [c-21] DEBUG ExcelDataExport - Streaming row 1000: Used memory: 413.01MB; Free memory: 15835.80MB
=== 2017-08-09 19:37:44,607 [c-21] DEBUG SQLTransaction - getConnection() looked for transactional connection for iptsfil5: (connection is null)
=== 2017-08-09 19:37:44,607 [c-21] DEBUG SQLDriver - Freeing SQLDriver dbConnection 687855846 for SQLDriver instance 2061823627
=== 2017-08-09 19:37:44,609 [c-21] DEBUG SQLConnectionManager - About to close PoolGuardConnectionWrapper with hashcode "687855846"

[godonnell_ip]

OK - I completed my tests with our dynamic datasource generator out of the way and the results are exactly as above.

• If I don't specify streamResults=true then all results are transferred from the DB before the export file is written and I see a message that tells me the total number of records included in the response.
• If I do specify streamResults=true then I see a message that the server is "Streaming the response", no count of the number of records and a notification that the datasource won't be released (yet).

This behavior doesn't seem to jive with your earlier advice above.