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,595 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

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 (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…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

726 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