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,590 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how can i import or extract data from Progressor db? 6 279
DB2, how do I FETCH 1 ROW from a table inside a subselect ? 22 810
SQL UPDATE QUERY PROBLEM 5 187
iSeries DB2 Query 2 90
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

16 Experts available now in Live!

Get 1:1 Help Now