## Question

3

Replies

145

Views

Posted: 5 years ago

Last activity: 5 years 4 months ago

## SQL error when access Garbage Collector And Log Usage from SMA

Re-posting this same question from the PDN forum. While trying to reproduce, I am running into the same problem. PRPC ML6, Tomcat 7, Postgres 9.1. When I try to view the Log Usage Statistics on a node, I get a SQL syntax error:

The failing query is below. The parameters are node ID, start/end date.

SELECT MAX(pxProcessCPU) as "pxProcessCPU", MAX(pxRequestorType) as "RequestorType", MAX(pxSystemNodeID) as "pxSystemNodeID", MAX(pxSystemNode) as "pxSystemNode", MAX(pxSystemName) as "pxSystemName", COUNT(DISTINCT(pxRequestorID)) as "UniqueRequestors", COUNT(DISTINCT(pyUserIdentifier)) as "UniqueUsers", SUM(pxActivationDataTimeCPU) as "pxActivationDataTimeCPU", SUM(pxActivationDataTimeElapsed) as "pxActivationDataTimeElapsed", SUM(pxActivityCount) as "pxActivityCount", SUM(pxAlertCount) as "pxAlertCount", SUM(pxCommitCount) as "pxCommitCount", SUM(pxCommitElapsed) as "pxCommitElapsed", SUM(pxCommitRowCount) as "pxCommitRowCount", SUM(pxConnectCount) as "pxConnectCount", SUM(pxConnectElapsed) as "pxConnectElapsed", SUM(pxDBInputBytes) as "pxDBInputBytes", SUM(pxDBOpExceedingThresholdCount) as "pxDBOpExceedingThresholdCount", SUM(pxDBOutputBytes) as "pxDBOutputBytes", SUM(pxRDBIOElapsed) as "pxRDBIOElapsed", SUM(pxRDBIOCount) as "pxRDBIOCount", SUM(pxDeclarativeRuleReadCount) as "pxDeclarativeRuleReadCount", SUM(pxDeclarativeRulesInvokedCount) as "pxDeclarativeRulesInvokedCount", SUM(pxDeclarativeRulesInvokedCPU) as "pxDeclarativeRulesInvokedCPU", SUM(pxDeclarativeRulesLookupCount) as "pxDeclarativeRulesLookupCount", SUM(pxDeclarativeRulesLookupCPU) as "pxDeclarativeRulesLookupCPU", SUM(pxDeclExprCtxFreeUseCount) as "pxDeclExprCtxFreeUseCount", SUM(pxDeclExprCtxSensUseCount) as "pxDeclExprCtxSensUseCount", SUM(pxDeclNtwksBuildConstCPU) as "pxDeclNtwksBuildConstCPU", SUM(pxDeclNtwksBuildConstElapsed) as "pxDeclNtwksBuildConstElapsed", SUM(pxDeclNtwksBuildHLCPU) as "pxDeclNtwksBuildHLCPU", SUM(pxDeclNtwksBuildHLElap) as "pxDeclNtwksBuildHLElap", SUM(pxDeclRulesInvokedBckGrdCnt) as "pxDeclRulesInvokedBckGrdCnt", SUM(pxDeclRulesInvokedElapsed) as "pxDeclRulesInvokedElapsed", SUM(pxDeclRulesLookupElapsed) as "pxDeclRulesLookupElapsed", SUM(pxDecryptCount) as "pxDecryptCount", SUM(pxDecryptCPU) as "pxDecryptCPU", SUM(pxDecryptElapsed) as "pxDecryptElapsed", SUM(pxEncryptCount) as "pxEncryptCount", SUM(pxEncryptCPU) as "pxEncryptCPU", SUM(pxEncryptElapsed) as "pxEncryptElapsed", SUM(pxFlowCount) as "pxFlowCount", SUM(pxIndexCount) as "pxIndexCount", SUM(pxInferGeneratedJavaCount) as "pxInferGeneratedJavaCount", SUM(pxInferGeneratedJavaCPU) as "pxInferGeneratedJavaCPU", SUM(pxInferGeneratedJavaElapsed) as "pxInferGeneratedJavaElapsed", SUM(pxInferGeneratedJavaHLElapsed) as "pxInferGeneratedJavaHLElapsed", SUM(pxInputBytes) as "pxInputBytes", SUM(pxInteractions) as "pxInteractions", SUM(pxJavaAssembleCount) as "pxJavaAssembleCount", SUM(pxJavaAssembleCPU) as "pxJavaAssembleCPU", SUM(pxJavaAssembleElapsed) as "pxJavaAssembleElapsed", SUM(pxJavaAssembleHLElapsed) as "pxJavaAssembleHLElapsed", SUM(pxJavaCompileCount) as "pxJavaCompileCount", SUM(pxJavaCompileCPU) as "pxJavaCompileCPU", SUM(pxJavaCompileElapsed) as "pxJavaCompileElapsed", SUM(pxJavaGenerateCount) as "pxJavaGenerateCount", SUM(pxJavaGenerateCPU) as "pxJavaGenerateCPU", SUM(pxJavaGenerateElapsed) as "pxJavaGenerateElapsed", SUM(pxJavaStepCount) as "pxJavaStepCount", SUM(pxJavaSyntaxCount) as "pxJavaSyntaxCount", SUM(pxJavaSyntaxCPU) as "pxJavaSyntaxCPU", SUM(pxJavaSyntaxElapsed) as "pxJavaSyntaxElapsed", SUM(pxLegacyRuleAPIUsedCount) as "pxLegacyRuleAPIUsedCount", SUM(pxListRowWithFilteredStrmCnt) as "pxListRowWithFilteredStrmCnt", SUM(pxListRowWithoutStreamCount) as "pxListRowWithoutStreamCount", SUM(pxListRowWithUnfilteredStrmCnt) as "pxListRowWithUnfilteredStrmCnt", SUM(pxListWithFilteredStreamCount) as "pxListWithFilteredStreamCount", SUM(pxListWithoutStreamCount) as "pxListWithoutStreamCount", SUM(pxListWithUnfilteredStrmCnt) as "pxListWithUnfilteredStrmCnt", SUM(pxLookupListDBFetches) as "pxLookupListDBFetches", SUM(pxNewFUAInstances) as "pxNewFUAInstances", SUM(pxOtherBrowseCPU) as "pxOtherBrowseCPU", SUM(pxOtherBrowseElapsed) as "pxOtherBrowseElapsed", SUM(pxOtherBrowseFilterCnt) as "pxOtherBrowseFilterCnt", SUM(pxOtherBrowseFilterCPU) as "pxOtherBrowseFilterCPU", SUM(pxOtherBrowseFilterElapsed) as "pxOtherBrowseFilterElapsed", SUM(pxOtherBrowseReturned) as "pxOtherBrowseReturned", SUM(pxOtherCount) as "pxOtherCount", SUM(pxOtherFromCacheCount) as "pxOtherFromCacheCount", SUM(pxOtherIOCount) as "pxOtherIOCount", SUM(pxOtherIOCPU) as "pxOtherIOCPU", SUM(pxOtherIOElapsed) as "pxOtherIOElapsed", SUM(pxOutputBytes) as "pxOutputBytes", SUM(pxParseRuleCount) as "pxParseRuleCount", SUM(pxParseRuleTime) as "pxParseRuleTime", SUM(pxParseRuleTimeCPU) as "pxParseRuleTimeCPU", SUM(pxPassivationCount) as "pxPassivationCount", SUM(pxPassivationDataTimeCPU) as "pxPassivationDataTimeCPU", SUM(pxPassivationDataTimeElapsed) as "pxPassivationDataTimeElapsed", SUM(pxPassivationIdleTimeElapsed) as "pxPassivationIdleTimeElapsed", SUM(pxPassivationSizeAverage) as "pxPassivationSizeAverage", SUM(pxPassivationSizeLast) as "pxPassivationSizeLast", SUM(pxPassivationSizePeak) as "pxPassivationSizePeak", SUM(pxProceduralRuleReadCount) as "pxProceduralRuleReadCount", SUM(pxPropertyReadCount) as "pxPropertyReadCount", SUM(pxRDBRowWithoutStreamCount) as "pxRDBRowWithoutStreamCount", SUM(pxRDBRowWithStreamCount) as "pxRDBRowWithStreamCount", SUM(pxRDBWithoutStreamCount) as "pxRDBWithoutStreamCount", SUM(pxRDBWithStreamCount) as "pxRDBWithStreamCount", SUM(pxRuleBrowseCPU) as "pxRuleBrowseCPU", SUM(pxRuleBrowseElapsed) as "pxRuleBrowseElapsed", SUM(pxRuleBrowseFilterCnt) as "pxRuleBrowseFilterCnt", SUM(pxRuleBrowseFilterCPU) as "pxRuleBrowseFilterCPU", SUM(pxRuleBrowseFilterElapsed) as "pxRuleBrowseFilterElapsed", SUM(pxRuleBrowseReturned) as "pxRuleBrowseReturned", SUM(pxRuleCount) as "pxRuleCount", SUM(pxRuleCPU) as "pxRuleCPU", SUM(pxRuleFromCacheCount) as "pxRuleFromCacheCount", SUM(pxRuleIOElapsed) as "pxRuleIOElapsed", SUM(pxRulesExecuted) as "pxRulesExecuted", SUM(pxRulesUsed) as "pxRulesUsed", SUM(pxRunModelCount) as "pxRunModelCount", SUM(pxRunOtherRuleCount) as "pxRunOtherRuleCount", SUM(pxRunStreamCount) as "pxRunStreamCount", SUM(pxRunWhenCount) as "pxRunWhenCount", SUM(pxSavedCbAfterIntCount) as "pxSavedCbAfterIntCount", SUM(pxSavedCbAfterIntCPU) as "pxSavedCbAfterIntCPU", SUM(pxSavedCbAfterIntElapsed) as "pxSavedCbAfterIntElapsed", SUM(pxSavedCxtAfterIntCount) as "pxSavedCxtAfterIntCount", SUM(pxSavedCxtAfterIntCPU) as "pxSavedCxtAfterIntCPU", SUM(pxSavedCxtAfterIntElapsed) as "pxSavedCxtAfterIntElapsed", SUM(pxServiceActivityCPU) as "pxServiceActivityCPU", SUM(pxServiceActivityTime) as "pxServiceActivityTime", SUM(pxServiceCount) as "pxServiceCount", SUM(pxServiceDataVolume) as "pxServiceDataVolume", SUM(pxServiceInMapReqCPU) as "pxServiceInMapReqCPU", SUM(pxServiceInMapReqTime) as "pxServiceInMapReqTime", SUM(pxServiceNumFileRecords) as "pxServiceNumFileRecords", SUM(pxServiceOutMapReqCPU) as "pxServiceOutMapReqCPU", SUM(pxServiceOutMapReqTime) as "pxServiceOutMapReqTime", SUM(pxTotalReqCPU) as "pxTotalReqCPU", SUM(pxTotalReqTime) as "pxTotalReqTime", SUM(pxTrackedPropertyChangesCount) as "pxTrackedPropertyChangesCount", SUM(pxTransientJavaAssembleCount) as "pxTransientJavaAssembleCount", SUM(pxTransientJavaAssembleCPU) as "pxTransientJavaAssembleCPU", SUM(pxTransientJavaAssembleElapsed) as "pxTransientJavaAssembleElapsed", SUM(pxTransientJavaCompileCount) as "pxTransientJavaCompileCount", SUM(pxTransientJavaCompileCPU) as "pxTransientJavaCompileCPU", SUM(pxTransientJavaCompileElapsed) as "pxTransientJavaCompileElapsed" FROM pegadata.pr_perf_statsWHERE (pxSystemNodeID = ?) AND (pyUserIdentifier IS NULL OR pyUserIdentifier <> 'Guest Content') AND (pxRequestorType <> 'BATCH') AND (pxSnapshotTime BETWEEN ? AND ?)

In addition, when trying to view the Garbage Collector Statistics, I get the error "GarbageCollectorView: null

*****Updated by moderator: Marissa to close post*****

**This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.**

For the first issue (SQL Error) I'm noticing that a space is missing near the end of the query:

FROM

pegadata.pr_perf_statsWHERE(pxSystemNodeID = ?)AND (pyUserIdentifier ISNULLOR pyUserIdentifier <> 'Guest Content')

AND (pxRequestorType <> 'BATCH')

AND (pxSnapshotTime BETWEEN ? AND ?)

I can replicate this issue on my own Pega7.1.6 on Postgres9.1 test machine, but it runs normally on my Pega 7.1.7 on Postgres test machine.