Solved

is it possible to do a REORG Table or REORGCHK update statistics  or a RUNSTATS UPDATE in an SP?

Posted on 2003-12-05
6
1,593 Views
Last Modified: 2010-05-18
DB2 UDB 7.2  on Unix..

using an SQL store procedure
is it possible to run any of the above sorts of DB2 Command  from within a
stored procedure...?

0
Comment
Question by:Lowfatspread
  • 3
  • 3
6 Comments
 
LVL 18

Expert Comment

by:BigSchmuh
ID: 9888203
It is possible to use Administration APIs from a C stored procedure (or C++) but I do not know how to simulate a reorgcheck.

Please look the url below for the "db2Reorg" API
    http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/r0009147.htm
and a C example for it
    http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/samples/c/s-tbreorg-sqc.htm
and the db2RunStats API
    http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/r0008570.htm
==> If you want me to help you to set up a dev environment, I'll be pleased to help you

Hope this helps.
0
 
LVL 50

Author Comment

by:Lowfatspread
ID: 9888535
thanks ,
but i'm presently forced to only use SQL stored procedures...
(crazy I know)
its looking like i 'll need to do this from a shell script...

i'll leave it open in the hope of further replies...  
0
 
LVL 18

Expert Comment

by:BigSchmuh
ID: 9891494
After creating a MyReorgProc from C, it becomes a standard stored procedure that you can call from any languages including Java or SQL.

By the way, did you notice that SQL stored procedures are converted to C to be compiled ? You will find sqc files for your SQL package below SqlLib\Function\....

Hope this helps.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 50

Author Comment

by:Lowfatspread
ID: 9891549
1) I thought i'd read that an SQL procedure could only call SQL procedures ....
      or have i got that mixed up with the access levels , NO SQL , READ SQL , MODIFIES etc...

2 ) oh yes and and i 've tried that argument several times...

0
 
LVL 18

Accepted Solution

by:
BigSchmuh earned 500 total points
ID: 9893095
Stored procedures are FENCED or NOT FENCED which means they run OutOfProcess (detached process) or InProcess (same adress space, means faster but more dangerous for your db) and I think that is where you may have troubles calling each others but I did not try that specific scenario.

NO SQL, READ SQL or MODIFY SQL are just for the dbms to know if the stored procedure uses the locking subsystem...

I do not see any reason why an SQL procedure can not call a C or Java one as soon as this proc is registered using the CREATE FUNCTION statement (and that you take care of paremeters type of course).

Hope this helps.
0
 
LVL 50

Author Comment

by:Lowfatspread
ID: 9893495
i 've got
some re-reading for tomorrow then...

thanks
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
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…

756 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