Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

------------------------------
Jacob Curtis

Original Message:
Sent: Thu October 24, 2024 06:04 AM
From: Rudi Van Helvoirt
Subject: Memory consumption via SQL

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Hello Rich,

For looking at the memory usage iDoctor is no longer needed, IBM i comes out of the box with a graph with which you can view the Memory Usage by Pool:

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Hi Rudi

Its more of my preference, I prefer the idoctor tools - especially since I spend a lot of time using it for Job Watcher anyway. Thanks

Hello Rich,

For looking at the memory usage iDoctor is no longer needed, IBM i comes out of the box with a graph with which you can view the Memory Usage by Pool:

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Hello Rich,

Please do not understand me wrong, iDoctor is a brilliant tool. It is a pity it is Windows only.
Regarding Job Watcher iDoctor outperforms Navigator for i, but for the average IBM i System Administrator the Navigator for i graphs will do fine. 

It is a matter of taste for sure, but the Graphs in Navigator for i look much better ;-)

As I am Dutch I do like things which come for free, iDoctor does require your wallet.

As always feel 100 % free to disagree with me. According to my kids I am always wrong. 

Greetings, 

Hi Rudi

Its more of my preference, I prefer the idoctor tools - especially since I spend a lot of time using it for Job Watcher anyway. Thanks

Hello Rich,

For looking at the memory usage iDoctor is no longer needed, IBM i comes out of the box with a graph with which you can view the Memory Usage by Pool:

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Hello Rich,

For looking at the memory usage iDoctor is no longer needed, IBM i comes out of the box with a graph with which you can view the Memory Usage by Pool:

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

There is no historic chart for Memory Usage by Pool, is there? 

------------------------------
Alejandro Insfran Beloqui
IBM i System Admin
Kyndryl

Original Message:
Sent: Wed October 30, 2024 11:45 AM
From: Rudi Van Helvoirt
Subject: Memory consumption via SQL

Hello Rich,

For looking at the memory usage iDoctor is no longer needed, IBM i comes out of the box with a graph with which you can view the Memory Usage by Pool:

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Wed October 30, 2024 08:49 AM
From: Rich Malloy
Subject: Memory consumption via SQL

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

------------------------------
Rich Malloy

Original Message:
Sent: Tue October 29, 2024 04:40 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

------------------------------
Jacob Curtis

Original Message:
Sent: Thu October 24, 2024 06:04 AM
From: Rudi Van Helvoirt
Subject: Memory consumption via SQL

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Dear Alejandro

>>>> There is no historic chart for Memory Usage by Pool, is there?  <<<<

Yes, there is. It's in the IBM i PDI chart named Memory Available By Pool and it is described in this article I published in 2022 on Itjungle.com: Guru: IBM i Experience Sharing, Case 5 – Using IBM i PDI Charts To Answer Performance Questions at https://www.itjungle.com/2022/07/18/guru-ibm-i-experience-sharing-case-5-using-ibm-i-pdi-charts-to-answer-performance-questions/     

------------------------------
Satid S

Original Message:
Sent: Mon March 23, 2026 11:08 AM
From: Alejandro Insfran Beloqui
Subject: Memory consumption via SQL

There is no historic chart for Memory Usage by Pool, is there? 

------------------------------
Alejandro Insfran Beloqui
IBM i System Admin
Kyndryl

Original Message:
Sent: Wed October 30, 2024 11:45 AM
From: Rudi Van Helvoirt
Subject: Memory consumption via SQL

Hello Rich,

For looking at the memory usage iDoctor is no longer needed, IBM i comes out of the box with a graph with which you can view the Memory Usage by Pool:

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Wed October 30, 2024 08:49 AM
From: Rich Malloy
Subject: Memory consumption via SQL

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

------------------------------
Rich Malloy

Original Message:
Sent: Tue October 29, 2024 04:40 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

------------------------------
Jacob Curtis

Original Message:
Sent: Thu October 24, 2024 06:04 AM
From: Rudi Van Helvoirt
Subject: Memory consumption via SQL

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

But that's the daily data, isn't it? I was wondering if there was a Memory Usage by Pool for the month, like the ones under Graph History (either Summary or Detail). I cannot find one for this item. I don't think there is.

------------------------------
Alejandro Insfran Beloqui
IBM i System Admin
Kyndryl

Original Message:
Sent: Mon March 23, 2026 09:18 PM
From: Satid S
Subject: Memory consumption via SQL

Dear Alejandro

>>>> There is no historic chart for Memory Usage by Pool, is there?  <<<<

Yes, there is. It's in the IBM i PDI chart named Memory Available By Pool and it is described in this article I published in 2022 on Itjungle.com: Guru: IBM i Experience Sharing, Case 5 – Using IBM i PDI Charts To Answer Performance Questions at https://www.itjungle.com/2022/07/18/guru-ibm-i-experience-sharing-case-5-using-ibm-i-pdi-charts-to-answer-performance-questions/     

------------------------------
Satid S

Original Message:
Sent: Mon March 23, 2026 11:08 AM
From: Alejandro Insfran Beloqui
Subject: Memory consumption via SQL

There is no historic chart for Memory Usage by Pool, is there? 

------------------------------
Alejandro Insfran Beloqui
IBM i System Admin
Kyndryl

Original Message:
Sent: Wed October 30, 2024 11:45 AM
From: Rudi Van Helvoirt
Subject: Memory consumption via SQL

Hello Rich,

For looking at the memory usage iDoctor is no longer needed, IBM i comes out of the box with a graph with which you can view the Memory Usage by Pool:

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Wed October 30, 2024 08:49 AM
From: Rich Malloy
Subject: Memory consumption via SQL

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

------------------------------
Rich Malloy

Original Message:
Sent: Tue October 29, 2024 04:40 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

------------------------------
Jacob Curtis

Original Message:
Sent: Thu October 24, 2024 06:04 AM
From: Rudi Van Helvoirt
Subject: Memory consumption via SQL

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Dear Alejandro

In my 3-decade experience with IBM i customers, memory consumption is NOT an important matter as there is a feature of dynamic memory movement between memory pools that helps with this.  In UNIX/Linux/Windows, when memory consumption of a job reaches its exhaustion point, there is a performance penalty to that running job when memory faulting happens BUT there is no such big penalty in IBM i as it uses a different virtual memory implementation (single level storage).  (Frank Soltis's book "Inside AS/400" explains this.)  As long as overall disk response time is good in IBM i server (especially in the current time of SSD/Flash Disk), IBM i memory faulting does not suffer as much performance penalty as in other OSes.    

PDI chart on memory usage that I provided above displays data from a member of the QAMP.... file and normally each member contain 24-hour data. If you want to see a chart for the whole month, you just create a new relevant QAPM... file (in a different library) for memory usage that accumulates the data of the whole month and display it through PDI.  This can done programmatically that is not hard to implement.   

IBM i Graph History is still available in Navigator for i but I do not remember if there is a chart on memory consumption or not. You can try it: Graph History in IBM Navigator for i Overview at https://www.ibm.com/support/pages/article-graph-history-ibm-navigator-i-overview       If there is no such chart you are looking for, you can submit your request through IBM Idea web site and you will get a response if the Navigator for i development team would oblige your request or not. 

Lastly, from my experience, most customer' workload pattern and thus memory consumption pattern is quite repetitive day by day with little or no variations. In some cases, the customers may have a peak monthly workload for a few days in a month and we just focus on those few days.  So, in IBM i, there is hardly a practical reason why a need to see memory consumption data for the whole month.     

Some customers somehow want fixed allocation of memory pools and I use PDI chart on memory usage to give the customers information on which pool has too much allocation (as in the sample chart I provided) and reallocate the memory for optimal usage among different pools.  This is where the memory usage chart is useful but it tends to be a one time effort action and 24-hour data of a few days is practically sufficient for making proper memory allocation adjustment.     But in the end, I see that using QPFRADJ system value for online adjustment and WRKSHRPOOL to set proper MAX and MIX size of each memory pool is the most useful way for the matter. 

------------------------------
Satid S

Original Message:
Sent: Wed March 25, 2026 08:54 AM
From: Alejandro Insfran Beloqui
Subject: Memory consumption via SQL

But that's the daily data, isn't it? I was wondering if there was a Memory Usage by Pool for the month, like the ones under Graph History (either Summary or Detail). I cannot find one for this item. I don't think there is.

------------------------------
Alejandro Insfran Beloqui
IBM i System Admin
Kyndryl

Original Message:
Sent: Mon March 23, 2026 09:18 PM
From: Satid S
Subject: Memory consumption via SQL

Dear Alejandro

>>>> There is no historic chart for Memory Usage by Pool, is there?  <<<<

Yes, there is. It's in the IBM i PDI chart named Memory Available By Pool and it is described in this article I published in 2022 on Itjungle.com: Guru: IBM i Experience Sharing, Case 5 – Using IBM i PDI Charts To Answer Performance Questions at https://www.itjungle.com/2022/07/18/guru-ibm-i-experience-sharing-case-5-using-ibm-i-pdi-charts-to-answer-performance-questions/     

------------------------------
Satid S

Original Message:
Sent: Mon March 23, 2026 11:08 AM
From: Alejandro Insfran Beloqui
Subject: Memory consumption via SQL

There is no historic chart for Memory Usage by Pool, is there? 

------------------------------
Alejandro Insfran Beloqui
IBM i System Admin
Kyndryl

Original Message:
Sent: Wed October 30, 2024 11:45 AM
From: Rudi Van Helvoirt
Subject: Memory consumption via SQL

Hello Rich,

For looking at the memory usage iDoctor is no longer needed, IBM i comes out of the box with a graph with which you can view the Memory Usage by Pool:

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Wed October 30, 2024 08:49 AM
From: Rich Malloy
Subject: Memory consumption via SQL

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

------------------------------
Rich Malloy

Original Message:
Sent: Tue October 29, 2024 04:40 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

------------------------------
Jacob Curtis

Original Message:
Sent: Thu October 24, 2024 06:04 AM
From: Rudi Van Helvoirt
Subject: Memory consumption via SQL

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

There is no historic chart for Memory Usage by Pool, is there? 

Hello Rich,

For looking at the memory usage iDoctor is no longer needed, IBM i comes out of the box with a graph with which you can view the Memory Usage by Pool:

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Hola Ale!!! Tanto tiempo 😊

Yo uso Performance Navigator, creo que lo usábamos en el GDC para algunos clientes. Tiene histórico con datos reducidos y después tiene uno muy bueno en Current Day que te da el movimiento de todos los pooles junto al faulteo, después podés analizar pool por pool.

Es muy útil en los casos en los que el cliente toca el MAX de los pooles y se termina dando una situación en la que relativamente pocos fallos (comparados con otros pooles en otro momento del día, ponele) te retrasa alguna tarea que en ese momento hubiera necesitado más memoria mientras que esta o bien está "toda" en base o está asignada sin necesidad en pooles que tienen el mínimo muy alto.

Ejemplo de movimiento diario de pooles: 

Acá ves como el Pool3 se come la enorme mayoría de los faults de 00:00 a 04:00 porque tiene seteado un máximo, después de las 04:00 se dan niveles similares de faulteo en otros pooles pero no afectan a la performance del equipo.

Después hay histórico con detalle de 90 días.

Abrazo enorme!

There is no historic chart for Memory Usage by Pool, is there? 

Hello Rich,

For looking at the memory usage iDoctor is no longer needed, IBM i comes out of the box with a graph with which you can view the Memory Usage by Pool:

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

------------------------------
Rich Malloy

Original Message:
Sent: Tue October 29, 2024 04:40 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

------------------------------
Jacob Curtis

Original Message:
Sent: Thu October 24, 2024 06:04 AM
From: Rudi Van Helvoirt
Subject: Memory consumption via SQL

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Dear Rich

>>>> on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it.  <<<<

I'm wondering if you are aware that you can achieve your stated goals more conveniently by setting the system value QPFRADJ=2 as it would move memory from low-faulting pool to the high-faulting one for you automatically every minute - definitely better than looking at iDoctor data every minute manually!   And you have an option to set the max and min memory pool size (with WRKSHRPOOL) which is important for *MACHINE pool which should be fixed at around 2-2.5 times its Reserves Size as seen from WRKSYSSTS.    

------------------------------
Satid S

Original Message:
Sent: Wed October 30, 2024 08:49 AM
From: Rich Malloy
Subject: Memory consumption via SQL

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

------------------------------
Rich Malloy

Original Message:
Sent: Tue October 29, 2024 04:40 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

------------------------------
Jacob Curtis

Original Message:
Sent: Thu October 24, 2024 06:04 AM
From: Rudi Van Helvoirt
Subject: Memory consumption via SQL

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------

Hi Satid, 

Yes, we PFRADJ is turned on. We are very aggressive with our Mins and Max's for our pools and while we technically can let the system move the memory, our workloads and users are very sensitive to latency. We don't look at idoctor every minute..lol......after we setup the mins and max pool sizes, we then look at the CS data via iDOCTOR to see if we either guessed to high on the min or not high enough on the max.....and then make adjustments. knowing not only how much memory is unallocated but also knowing WHEN there is high demand or even 'low demand' has allowed us more flexibility in freeing memory up from one set of pools and making them more available to another set. 

Dear Rich

>>>> on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it.  <<<<

I'm wondering if you are aware that you can achieve your stated goals more conveniently by setting the system value QPFRADJ=2 as it would move memory from low-faulting pool to the high-faulting one for you automatically every minute - definitely better than looking at iDoctor data every minute manually!   And you have an option to set the max and min memory pool size (with WRKSHRPOOL) which is important for *MACHINE pool which should be fixed at around 2-2.5 times its Reserves Size as seen from WRKSYSSTS.    

------------------------------
Satid S

Original Message:
Sent: Wed October 30, 2024 08:49 AM
From: Rich Malloy
Subject: Memory consumption via SQL

Jacob

Since Rudi already mentioned that you need to be at TR 10 (7.4) to get the *LAST support on the create alias statement....you might want to have a front end pgm that creates the alias for you in a CL program. I do something similar to harvest some perf data as well - example of retrieving the last member and then creating the alias on CL:

RTVMBRD    FILE(QPFRDATA/QAPMSYSAFN) MBR(*LAST) +     
             RTNMBR(&MBRNAME)                         

QSYS/RUNSQL SQL('CREATE OR REPLACE ALIAS +          
             QTEMP.TODAY         FOR +              
             QPFRDATA.QAPMSYSAFN (' *CAT &MBRNAME + 
             *TCAT ')') COMMIT(*NONE) NAMING(*SQL)  

hope this helps - Rich

P.S - on the memory utilization front..I do find it useful to see what the actual consumption is....i.e - do I have memory sitting idle / not allocated etc. I use iDoctor over the CS data though to get me that info and have found it very useful in lowering the amount of faults I have as well as identifying where I have "too much" memory in pools that simply don't need it. 

------------------------------
Rich Malloy

Original Message:
Sent: Tue October 29, 2024 04:40 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hi Rudi,

Thanks for your response. I have a question about your solution, if you wouldn't mind elaborating.

I tried creating the alias for QAPMPOOLB, but the query failed. I ran: 

`create alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);`

and got the error

`Token * was not valid. Valid tokens: <IDENTIFIER>. SQLSTATE=42601 SQLCODE=-104`.

Did I use the wrong syntax, or am I missing something?

------------------------------
Jacob Curtis

Original Message:
Sent: Thu October 24, 2024 06:04 AM
From: Rudi Van Helvoirt
Subject: Memory consumption via SQL

Hello Jacob,

Let the Navigator for i (Nav4i) give a helping hand ;-)

Logon to Nav4i and go to => Performance => Investigate Date => Memory Usage by Pool.

When doing this a graph will show and in the top right corner of that graph you will see an SQL button.

• Please press that button and a window will appear showing you the  SQL statement used for the first graph.
• Copy and paste that SQL statement into Run SQL Script of IBM i Access Client Solutions.
• Add the line above that SQL statement shown below:
  create or replace alias qrplobj.qapmpoolb for QPFRDATA.QAPMPOOLB(*LAST);
• now replace the string "QTEMP.QPFRDATAQAPMPOOLBQ288000025" by QRPLOBJ.QAPMPOOLB ( please be aware that you have a different number in the "Q288000025")
• Based on you Collection Services Interval you could decide to run this command on a refresh rate based on that. The create or replace alias SQL statement needs only to be run when after Collection Services has cycled. For this information use Nav4i => Performance => Collection Services Configuration
• Since you are only interested in the last collection of data please add the following in the SQL statement just after the table name:
  where datetime >= current timestamp - 5 minutes (again the 5 minutes used here is base on the collection interval used in the Collection Services Configuration.
  

I can only hope this helps and if it does not, I enjoyed putting it together and learned a lot doing so. So thanks for asking.

Although I agree with what Satid wrote I nonetheless decided to try to help you.

We use the information provided in the graph to check if there is memory available not being used. As I am Dutch I do like things which are for free to use ;-)

Greetings,

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Mon October 21, 2024 04:16 PM
From: Jacob Curtis
Subject: Memory consumption via SQL

Hello. I am trying to setup an exporter for Prometheus to monitor system performance. I'm using an existing java-based exporter from github that uses SQL statements to get the metric values. Unfortunately it doesn't come with a metric for memory consumption. The query it's using for memory statistics is:

"SELECT POOL_NAME,CURRENT_SIZE,DEFINED_SIZE,MAXIMUM_ACTIVE_THREADS,CURRENT_THREADS,RESERVED_SIZE FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X"

This gives me some data, but not what I need to determine memory consumption.

I looked through the discussions in this group, and did quite a lot of googling, and the only method I've seen to get an actual value for memory consumption is via the Navigator (Old Interface > Performance > Investigate Data) to see usage per pool. I looked at the query used for these graphs, and it appears to be querying a temporary performance data table (QTEMP.QPFRDATAQAPMPOOLBQ295000002). I couldn't figure out how to use this for my query, though.

If anyone knows of a query that will give me the current consumption of a single pool, or the entire memory, I would greatly appreciate it.

------------------------------
Jacob Curtis
------------------------------