Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Databse query using ADODB... Heap problem

Posted on 2002-04-11
18
293 Views
Last Modified: 2007-12-19
Dear Experts,

I am using ADODB for fetching records from db2.

>>set con = server.CreateObject(ADODB.connection)
>>con.Open....
>>set rs = server.createObject(ADODB.recordset)
>>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.








0
Comment
Question by:ksenthilraj
  • 7
  • 7
  • 3
  • +1
18 Comments
 
LVL 15

Expert Comment

by:robbert
ID: 6933341
select
(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
0
 
LVL 11

Expert Comment

by:ASPGuru
ID: 6933345
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",timestampfield,start)/2) as slotnumber, count(*) from table where timestampfield between start and end group by slotnumber


start and end includes all 12 slots...


ASPGuru
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 6933541
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..
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:ksenthilraj
ID: 6933634
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..


 

0
 
LVL 11

Expert Comment

by:ASPGuru
ID: 6933655
> 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
0
 
LVL 1

Author Comment

by:ksenthilraj
ID: 6933674
ASPGuru,

I will try... and let you know the feedback ..ASAP..

Cheers..
0
 
LVL 1

Author Comment

by:ksenthilraj
ID: 6933758
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.
0
 
LVL 15

Expert Comment

by:robbert
ID: 6934373
>>[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.
0
 
LVL 11

Expert Comment

by:ASPGuru
ID: 6934694
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
0
 
LVL 1

Author Comment

by:ksenthilraj
ID: 6947179
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.



0
 
LVL 11

Expert Comment

by:ASPGuru
ID: 6947198
well, have you even tried the query?
it should be very efficient... it's just one statement...

ASPGuru
0
 
LVL 15

Expert Comment

by:robbert
ID: 6947210
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.
0
 
LVL 1

Author Comment

by:ksenthilraj
ID: 6949708
ASPGuru,

I donot know how to build such a query..

Expecting your input in this regard.

Cheers.
0
 
LVL 11

Accepted Solution

by:
ASPGuru earned 50 total points
ID: 6949791
try this:

-------
' yyyyxxddhhmmss, where yyyy is the year, xx is the month,
' dd is the day, hh is the hour, mm is the minute, and ss is the seconds.

start = "20020418000000"
end = "20020418235959"

sql = "select integer(timestampdiff(8,char(timestamp(" & start & ")-timestampfield))/2) as slotnumber, count(*) from table where timestampfield between timestamp(" & start & ") and timestamp(" & end & ") group by slotnumber"
-------
DB2 has probably some kind of console, where you can try sql statements.... try it there first...

as you see you have to construct a start and end timestamp string for the day that interests you... i did this by hand here... of course you need to do this automatic in ASP once this test works... hope you have data for the April 18. 2002 in your DB or you have to adjust that...

you may also have to change the "group by slotnumber" and insert the slotnumber expression instead of just the name slotnumber again...

ASPGuru
0
 
LVL 11

Expert Comment

by:ASPGuru
ID: 6949794
btw...

50 points is no more appropriate here...

ASPGuru
0
 
LVL 1

Author Comment

by:ksenthilraj
ID: 6952748
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.
0
 
LVL 1

Author Comment

by:ksenthilraj
ID: 6976344
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.
0
 
LVL 11

Expert Comment

by:ASPGuru
ID: 6976579
i'm sorry.... try to learn some more DB2 SQL....
maybe you solve the problem yourself then....

ASPGuru
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question