Solved

Databse query using ADODB... Heap problem

Posted on 2002-04-11
18
290 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Read XML Response From ASP Classic 9 56
Set a string that can be access from the entire winform. 7 77
ASP VB... 7 93
API not working 33 47
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now