Get DB2 tablespace during runtime

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.

helloyipAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

askanivgCommented:
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
helloyipAuthor Commented:
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?


askanivgCommented:
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.
BigSchmuhCommented:
Did you try using SYSCAT.TABLESPACES catalog table to retrieve those informations ?
BigSchmuhCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.