Solved

Get DB2 tablespace during runtime

Posted on 2003-11-21
5
834 Views
Last Modified: 2011-10-03
Dear all,

Is there any method to know the DB2 tablespace during runtime? I am using Java application and SQLJ to access IBM DB2 V7.  My table is partitioned and I want my program to know if one partition is full, it can insert record to the next partition.  Does SQLJ provide any function to do so?

Thanks all.

0
Comment
Question by:helloyip
  • 2
  • 2
5 Comments
 
LVL 2

Expert Comment

by:askanivg
ID: 9812742
try using db2 list tablespaces. I dont know about SQLJ.

Basically paritioning key if defined, (feature of V8) distributes the load across various nodes. So it does not mean that the first tablespace is filled and it starts filling up the different nodes/tablespaces. If you wish to do it manually then you can think doing on these lines. ie.. checking the range of the partitioning key and inserting into the respective node/tablespace that houses that range of partitioning key values. This would help your performance also.

HtH
0
 

Author Comment

by:helloyip
ID: 9816207
Thanks for your reply.  However, what I am seeking is the way to know the tablespace utilization at runtime using Java, so that I can avoid inserting record to the partition which is already or nearly out of disk space.  

Any other suggestion?


0
 
LVL 2

Expert Comment

by:askanivg
ID: 9818158
I suppose you cannot do this manually. It should be done by the database.
Or
if you wish to do it manually you should have access to use 'list tablespaces show detail'. Then looking at the upper water level mark you should decide whether the tablespace is filled. Then use some commands like use node1, export node1 and then load to that node.
0
 
LVL 18

Expert Comment

by:BigSchmuh
ID: 9862267
Did you try using SYSCAT.TABLESPACES catalog table to retrieve those informations ?
0
 
LVL 18

Accepted Solution

by:
BigSchmuh earned 500 total points
ID: 9888295
I apologize for my last post as SYSCAT.TABLESPACES will definitely not do the job.

But there is a way to do it using C (or C++) to build a stored procedure which you can call from SQLJ.

First, I will show you what the Administration API can do for you. Please find below the API to a C example of reading tablespaces informations (Free, Used, High watermark...)
    http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/samples/c/s-tsinfo-sqc.htm
and a link to the first API to use to read tbs info
    http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/r0001596.htm

Second, I will be pleased to show you how to build those those stored procedures if you want me to.
Third, I may although show you how to use those stored procedures from SQLJ but you probably know more than me about it.

Hope this helps.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
db2 - u 3 243
Call a stored proc from a VIEW 5 102
Leveraging AD Groups in DB2 1 85
Monitor SQL Insert 8 99
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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