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,589 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 (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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

11 Experts available now in Live!

Get 1:1 Help Now