Hi,

Our production database has 16GB memory but all the time its giving memory issue. Whenever I tried to execute the query

select * from table(snapshot_dyn_sql('NOVAPPRO',-1)) as x order by total_exec_time desc

its giving minimum 8000 records as result. I couldnt able to understand whether this many queries will execute at the same time. One thing is that the queries are repeated. For example, one of the query appears around 1500 times. Please let me know what will be the issue. I have attached the query_result.txt for your reference.

Thanks and Regards
Sholan

What DB2 version and OS?

Andy

Hi,

Version is 8.1 FP9 and OS is Solaris.

Thanks and Regards
Sholan

What you are essentially dumping is the package cache with that query. This is a cache of the dynamic SQL issued to your db. This is over time, not the instant you do the snapshot. The snapshot is how the cache appears at the time of the snapshot. I believe that DB2 uses a LRU (least recently used) algorithm to manage the package cache. The oldest unused statement is removed when space is needed. Depending on the environment, statements can be around for days and weeks. As for having the same statement in the cache 1500 times, you did not sort the dump you posted, so it is hard to see that. From what I checked, each one was unique in some way.

Andy

Thanks a lot. Could you please let me know how to overcome this issue. At any point of time the maximum application connections not exceed more than 500... but we had given MAXAGENT as 8000. Wether we need to reduce this value or as you told we need to do anything with package cache. Its a high priority issue for us. Please let me know the next action.

Thanks and Regards
Sholan

Which issue? So far you have not shown us any particular error or problem.

As for the MAXAGENTS value, you probably don't need to worry about it. Agent memory is only allocated when a connection is establishe, so if you are sure there are at most 500 concurrent connections then that's how much memory will be allocated.

When you post your errors, please also include all instance and database configuration parameters.

Hi,
There is only one instance and two database is available in our Prod system. I have attached the configuration parameters file as well as current monitoring graph.

Regards
Sholan

Please find the attachment.

The problem is that your application is not using host variables ("?") for all the literals in the predicate and you get many "duplicate" statements. The problem is that unless they are indentical, DB2 will use memory for each one and you will have a memory and/or SQL compile problem:

select * from cb5_vehicle_options_matrix_v where (from_equip_code = ? ) and GRADE_CODE= 102137 order by from_equip_code, to_equip_code

SELECT vgnAsStaticFileRef.id,vgnAsStaticFileRef.placement Path,vgnAsStaticFileRef.deploymentType FROM vgnAsStaticFileRef WHERE (vgnAsStaticFileRef.id IN ( '3ba65c993f0ab110VgnVCM2000008293e753STFL' ) )

As you can see, they sometimes have "?" so the statements are being prepared and they know how to use host variables, but not using it everywhere like they should.

Thanks a Lot. I will raise your query with my development team and get back to you asap. If possible could you please check the configuration parameter files and let me know any changes required for the parameter values.

Thanks and Regards
Sholan

The database configuration does not make any sense to me. Looks like you set every memory pool to some arbitrary large number.

You still haven't told us what is the problem you are trying to solve.

Also, it would be nice to see the output of db2level.

PS. Do not zip the files - attach them as text. Very few people will download potentially unsafe files from an unfamiliar source.

Hi,

The issue is the usage of memory. The system is using all 16GB memory and the database is getting hanged. Here is our environment

$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL08022"
with level identifier "03030106".
Informational tokens are "DB2 v8.1.0.89", "OD_14086", "U800790_14086", and
FixPak "9".
Product is installed at "/opt/IBM/db2/V8.1".

$ db2licm -l
Product Name = "DB2 Enterprise Server Edition"
Product Identifier = "DB2ESE"
Version Information = "8.2"
Expiry Date = "Permanent"
Registered Connect User Policy = "Disabled"
Number Of Entitled Connect Users = "5"
Enforcement Policy = "Soft Stop"
Number of processors = "8"
Number of licensed processors = "8"
Database partitioning feature = "Not entitled"
Annotation = ""
Other information = ""

Product Name = "DB2 High Availability Disaster Recovery Option"
Product Identifier = "DB2HADR"
Version Information = "8.2"
Expiry Date = "Permanent"
Annotation = ""
Other information = ""

Product Name = "DB2 Advanced Security Option"
Product Identifier = "DB2ASO"
Version Information = "8.2"
Expiry Date = "Permanent"
Annotation = ""
Other information = ""


Regards
Sholan

This being a 32-bit DB2 installation, a single instance cannot use all of 16 GB. There must be something else that is using memory. Besides, there may be no connection at all between "using all 16GB memory" and "database is getting hanged". How do you define "hanged"? Are there any errors in the db2diag.log at that time?

Please provide the output of:

db2ilist
vmstat
db2pd -inst -mempools
db2pd -db <yourdatabase> -mempools

I looked at your config parms and your db2level. Here are my comments, which are my best guess without knowing more about your databases or your application:

1. FP9 on version 8 is not very good (still lots of bugs). I consider FP10 to be the minimum FP that one should use with HADR. Since the latest fixpack is 17a, and there is no charge for fixpacks, you should upgrade to FP17a ASAP.

2. On the dbm config, you have all monitors turned on permanently. This is very dangerous on a heavily loaded system and can cause system to crash or hang. If you need to run snapshots with monitors, use the "update monitor switches using XXXXXX ON" to turn them on temporarily for a snapshot. You can leave the DFT_MON_TIMESTAMP monitor on in the DBM config.

3. On dbm config you have allowed for 8000 MAXAGENTS (usually the same as number of connections). Do you need that many? You also have NUM_POOLAGENTS set to 8000. I would lower the NUM_POOLAGENTS to about 100, especially if you have a application server that does its own connection pooling like Jboss, Websphere, etc. Do this even if you leave the MAXAGENTS at 8000.

4. Consider putting the databases in two different instances if you have 32 bit DB2 on a 16 GB machine. There is a 2 GB max per instance. This will allow you to use more memory for each. You don't even have to move the databases, but just uncatalog it and recatalog on the new instance.

5. On the db config, your dbheap of 70000 is ridiculously large. 3000 is more than enough.

6. Lower the LOGBUFSZ to 512. Anything more than that you get diminishing returns (slower response time in accessing and flushing the DB2 transaction log).

7. Change LOCKTIMEOUT to 30 (seconds). -1 is infinite lockwait time. If an application is waiting more than 30 seconds on a lock to be released by a different application, you need to know that and fix the problem.

8. Increase NUM_IOCLEANERS to number of CPU cores or 3 whichever is lower.

9. Change your LOGPRIMARY to 5-10 max, add any reduction in primary to the number of secondary logs. 5 is usually fine for Primary.

10. One of your db's has MINCOMMIT = 2. I would revisit that.

11. Check the size of your bufferpools. The total size of all bufferpools in an instance (all databases combined) can be somewhere between 1.1 GB - 1.5 GB depending on the OS. Make sure they are not too small or too large. Moving one database to a different instance will allow you to make the bufferpools larger.

These are just for starters, and I am sure there are other problems.

Thanks. Actually, I have told "hanged" from the Applications side. The pages are not getting loaded and lot many complaints from the customers. At the time when I "TOPAS" the db server, the usuage memory, cpu are full. I have attached the results for
db2ilist
vmstat
db2pd -inst -mempools
db2pd -db NOVAPPRO -mempools