ksenthilraj
asked on
Databse query using ADODB... Heap problem
Dear Experts,
I am using ADODB for fetching records from db2.
>>set con = server.CreateObject(ADODB.
>>con.Open....
>>set rs = server.createObject(ADODB.
>>rs.open select count(*) from tablename,con
This web page is used to get the report according to database datas.
A TIMESTAMP field... which having date and time of that event.. In my report ....select count(*) from table where timestampfield between starttime and endtime...
And in the report i need to give 2 hours slot.. that is 6am - 8 am.. 8 am - 10 am... like this 12 time slots come..
To achieve the above report i need 12 queries to be executed..
Here is the problem starts..
>>rs.open select count(*) from table where timestampfield between starttime and endtime,con
for the 12 slots i have created 12 recordset and above statement for 12 query excuted and reported...
It works fine..
But some times it gives heap storage exceeds the limit..this error come.. in some other application which uses the same database also got the same error..
REQUIREMENT:
What is my thinking in this is... while creating 12 individual instance and executing the 12 queries seperately.. the heap level exceeds at this time...
I am expecting your EXPERTS.. give some solution to do efficient way of doing this....
Is there any other method to do this query..???
Any suggestion are appreciated..
Thanks in advance.
Cheers.
I am using ADODB for fetching records from db2.
>>set con = server.CreateObject(ADODB.
>>con.Open....
>>set rs = server.createObject(ADODB.
>>rs.open select count(*) from tablename,con
This web page is used to get the report according to database datas.
A TIMESTAMP field... which having date and time of that event.. In my report ....select count(*) from table where timestampfield between starttime and endtime...
And in the report i need to give 2 hours slot.. that is 6am - 8 am.. 8 am - 10 am... like this 12 time slots come..
To achieve the above report i need 12 queries to be executed..
Here is the problem starts..
>>rs.open select count(*) from table where timestampfield between starttime and endtime,con
for the 12 slots i have created 12 recordset and above statement for 12 query excuted and reported...
It works fine..
But some times it gives heap storage exceeds the limit..this error come.. in some other application which uses the same database also got the same error..
REQUIREMENT:
What is my thinking in this is... while creating 12 individual instance and executing the 12 queries seperately.. the heap level exceeds at this time...
I am expecting your EXPERTS.. give some solution to do efficient way of doing this....
Is there any other method to do this query..???
Any suggestion are appreciated..
Thanks in advance.
Cheers.
i don't know exactly the sql function, that DB2 provides, but this should also be solvable with one sql staatement...
something like this(pseudocode):
select int(datediff("h",timestamp
start and end includes all 12 slots...
ASPGuru
something like this(pseudocode):
select int(datediff("h",timestamp
start and end includes all 12 slots...
ASPGuru
Check if your hard disks are not full where the sql is swapping memory.. I think the query is using all the memory resources and exceeds swap files..
ASKER
Dear Experts,
Great responses..
To robbert:
-----------
>>SQL0437W Performance of this complex query may be sub->>optimal. Reason code: 1. SQLSTATE=01602
When i tried with your query type .. the above error comes out.. And i heard that .. this is a very tough and complex query.. which needs more resource...
Expecting your reply.. soon.
To aspGuru:
-----------
I seached the document.. But i could not find such a code..
Can you give me some links..
To Silver5:
-----------
The system have enough hard disk space.. with that it also have 512 MB RAM..
We checked those things..
To Experts:
-----------
>>[IBM][CLI Driver][DB2/NT] Not enough storage is >>available in the application heap to process the >>statement. SQLSTATE=57011
This is the error i got..
FYI: This error not coming directly to my program.. When i view webreports.. this error come in our other service which uses some simple query..
Hope i explained the problem...
Expecting your suggestion..
Cheers..
Great responses..
To robbert:
-----------
>>SQL0437W Performance of this complex query may be sub->>optimal. Reason code: 1. SQLSTATE=01602
When i tried with your query type .. the above error comes out.. And i heard that .. this is a very tough and complex query.. which needs more resource...
Expecting your reply.. soon.
To aspGuru:
-----------
I seached the document.. But i could not find such a code..
Can you give me some links..
To Silver5:
-----------
The system have enough hard disk space.. with that it also have 512 MB RAM..
We checked those things..
To Experts:
-----------
>>[IBM][CLI Driver][DB2/NT] Not enough storage is >>available in the application heap to process the >>statement. SQLSTATE=57011
This is the error i got..
FYI: This error not coming directly to my program.. When i view webreports.. this error come in our other service which uses some simple query..
Hope i explained the problem...
Expecting your suggestion..
Cheers..
> I seached the document..
> But i could not find such a code..
> Can you give me some links..
here's a DB2 sql reference:
http://plutonium.cs.umanitoba.ca/DB2Docs/db2s0/frame3.htm#index
the function that i named datediff() is actually called timestampdiff()
and the int() function is called integer()...
you probably have to play around a little with the sql statement to get it right...
ASPGuru
> But i could not find such a code..
> Can you give me some links..
here's a DB2 sql reference:
http://plutonium.cs.umanitoba.ca/DB2Docs/db2s0/frame3.htm#index
the function that i named datediff() is actually called timestampdiff()
and the int() function is called integer()...
you probably have to play around a little with the sql statement to get it right...
ASPGuru
ASKER
ASPGuru,
I will try... and let you know the feedback ..ASAP..
Cheers..
I will try... and let you know the feedback ..ASAP..
Cheers..
ASKER
ASPGuru,
>>you probably have to play around a little with the sql statement to get it right...
I think it is not very easy... That datetimediff function just returns the difference between two stamps.. with that..i do know how come i can calculate my required query..
I am novice to use db2functions... hope you understand the problem..
Expecting reply from all,
Cheers.
>>you probably have to play around a little with the sql statement to get it right...
I think it is not very easy... That datetimediff function just returns the difference between two stamps.. with that..i do know how come i can calculate my required query..
I am novice to use db2functions... hope you understand the problem..
Expecting reply from all,
Cheers.
>>[IBM][CLI Driver][DB2/NT] Not enough storage is >>available in the application heap to process the
>>statement. SQLSTATE=57011
The default value in DB2 for the Extended Search configuration
database is 8MB, which should be sufficient for most operations. If
you receive this message, however, you should increase the value of
the application heap size.
1. Enter the following commands on the console command line:
db2cmd
db2
2. When the DB2 command window is displayed, enter the following
command:
update db cfg for des using applheapsz value
where "value" is the new size for the heap size. Be aware that
the value you specify will be multiplied by four.
>>statement. SQLSTATE=57011
The default value in DB2 for the Extended Search configuration
database is 8MB, which should be sufficient for most operations. If
you receive this message, however, you should increase the value of
the application heap size.
1. Enter the following commands on the console command line:
db2cmd
db2
2. When the DB2 command window is displayed, enter the following
command:
update db cfg for des using applheapsz value
where "value" is the new size for the heap size. Be aware that
the value you specify will be multiplied by four.
you need to use timestampdiff to calculate the difference between the two timestamps in hours.
this leads to a number between 0 and 24 hours
then you divide by two and take just the integer part...
this gives you a number between 0 and 11, which are your twelve timeslots
ASPGuru
this leads to a number between 0 and 24 hours
then you divide by two and take just the integer part...
this gives you a number between 0 and 11, which are your twelve timeslots
ASPGuru
ASKER
ASPGuru,
After spliting the time slots into twelve parts.. how we can effiecietly form the query.. to avoid the above said problem..
Robbert
*******
We increased the size to 30MB.. even then the problem exists..
>>update db cfg for des using applheapsz value
I have a doubt.. after increasing the value
How i can check the value of applicationheap size...
select db cfg for des....like query.. to cross check whether heap size increased or not..
Cheers.
After spliting the time slots into twelve parts.. how we can effiecietly form the query.. to avoid the above said problem..
Robbert
*******
We increased the size to 30MB.. even then the problem exists..
>>update db cfg for des using applheapsz value
I have a doubt.. after increasing the value
How i can check the value of applicationheap size...
select db cfg for des....like query.. to cross check whether heap size increased or not..
Cheers.
well, have you even tried the query?
it should be very efficient... it's just one statement...
ASPGuru
it should be very efficient... it's just one statement...
ASPGuru
Sorry, I don't have DB2 installed, and cannot test it. Here's the documentation: http://taurus.vcu.edu/db2/
My personal opinion is that DB2 is an outdated database system, and doesn't scale. I would upgrade to any other system.
My personal opinion is that DB2 is an outdated database system, and doesn't scale. I would upgrade to any other system.
ASKER
ASPGuru,
I donot know how to build such a query..
Expecting your input in this regard.
Cheers.
I donot know how to build such a query..
Expecting your input in this regard.
Cheers.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
btw...
50 points is no more appropriate here...
ASPGuru
50 points is no more appropriate here...
ASPGuru
ASKER
ASPGuru,
Sorry for the late reply.. I will check as you said.. and let you know..
If it needs more time.. i will increase the question points.
I will give feedback ASAP.
Cheers.
Sorry for the late reply.. I will check as you said.. and let you know..
If it needs more time.. i will increase the question points.
I will give feedback ASAP.
Cheers.
ASKER
Dear Guru,
Among all, your comments are good. Still this problem persists. I am not able to solve the problem or otherway i am not able to follow your guidelines..
Any way thanks for your comments..
Cheers.
Among all, your comments are good. Still this problem persists. I am not able to solve the problem or otherway i am not able to follow your guidelines..
Any way thanks for your comments..
Cheers.
i'm sorry.... try to learn some more DB2 SQL....
maybe you solve the problem yourself then....
ASPGuru
maybe you solve the problem yourself then....
ASPGuru
(select count(*) from table where timestampfield between starttime1 and endtime1
) as count1,
(select count(*) from table where timestampfield between starttime2 and endtime2
) as count2
from table