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
------------------------------

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
------------------------------