Solved

Databse query using ADODB... Heap problem

Posted on 2002-04-11
18
304 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
[X]
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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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 informatio…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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