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

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...?

LVL 50
LowfatspreadAsked:
Who is Participating?
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.

BigSchmuhCommented:
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
LowfatspreadAuthor Commented:
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
BigSchmuhCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

LowfatspreadAuthor Commented:
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
BigSchmuhCommented:
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

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
LowfatspreadAuthor Commented:
i 've got
some re-reading for tomorrow then...

thanks
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.