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