Question:Do you know how to write a query in DB2 that would display records based on the last month eg. Take todays date and then display last 31 days ? In other words, I need to subtract some days from a timestamp
Answer: Without actual details I can’t supply a specific query, but I can share with you a pattern, allowing you to apply in your query.
The basic concept:
CURRENT_TIMESTAMP - 31 DAYS
This example query will return the date from 31 days ago
db2 "select date(current timestamp – 31 days) from sysibm.sysdummy1"
How you construct the statement will depend on the column used to store the dates
Question: We’re running an application which connects to a DB2 9.7 on Red Hat Linux 6. On the last batch job the following error message was reported.
Connection reset. ERRORCODE=-4499, SQLSTATE=08001
-- The associated messages in the db2diag.log were
ADM7009E An error was encountered in the "TCPIP" protocol support. A possible
cause is that the maximum number of agents has been exceeded.
ADM7519W DB2 could not allocate an agent. The SQLCODE is "-1225".
Answer: Some background information. The configuration parameter MAXAGENTS is set at the instance level , therefore an aggregate of all active databases. Each database has a setting for MAXAPPLS – defining the maximum number of connected applications.
It is possible to exceed the total number of agents if there are a large number of connections.
First step , get the dbm snapshot
db2 get snapshot for database manager > mydbm.out
Focus on these parameter values
High water mark for agents registered
High water mark for coordinating agents
Agents stolen from another application
Max agents overflow
One indicator of MAXAGENTS limits , is if the value of “Agents stolen from another application” is greater than zero. You have an option of increasing the amount of agents available by sizing MAXAGENTS or MAX_COORAGENTS (as appropriate)
You can update these parameters by issuing the following command:
db2 update db cfg for <database_name> using MAXAGENTS <increase_value>
db2 update dbm cfg using MAX_COORDAGENTS <increase_value>
Question: Why do some sql queries use a read-only clause? Is there any performance gain by using the read only clause?
Answer:Sometimes you will see a sql statement such as :
Select name from mytable for read only
The purpose of using the read only clause is to make the cursor unambiguous. When the read only clause is used , positional updates cannot occur
By unambiguous I mean : Normally the SELECT is turned into a cursor. If the cursor is explicitly defined as a READ ONLY – “blocking” is used. “Blocking” means the DB2 server sends groups of rows across to the client. Normally DB2 only shows this behaviour on non updatabable cursors e.g joins
The potential result in a multi user environment is to decrease throughput – as more users are allowed to take shared locks