Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2012
    Posts
    15

    Unanswered: Call sysproc.admin_cmd reorg table

    I'm trying to use the command CALL SYSPROC.ADMIN_CMD('REORG TABLE LINK.AAAD INPLACE ALLOW WRITE ACCESS NOTRUNCATE TABLE START')@

    CALL SYSPROC.ADMIN_CMD('REORG TABLE LINK.AAAD INPLACE ALLOW WRITE ACCESS NOTRUNCATE TABLE START')@
    CALL SYSPROC.ADMIN_CMD('REORG INDEXES ALL FOR TABLE LINK.AAAD ALLOW WRITE ACCESS')@

    CALL SYSPROC.ADMIN_CMD starts to run than i get an hour glass / locks up for a while...

    but fails...

    Any help would be appreciated.
    ------------------------------------------------------------------------------------
    -- Logged 4/24/2012 1:37:31 PM by 77920 in database DB_LINK => DBLINK [LINK]
    ------------------------------------------------------------------------------------
    -- SQL Execute ver. 10.2.3.1
    ------------------------------------------------------------------------------------


    CALL SYSPROC.ADMIN_CMD('REORG TABLE LINK.AAAD INPLACE ALLOW WRITE ACCESS NOTRUNCATE TABLE START')@
    -- Failure!!! (at: 4/24/2012 1:38:31 PM - execution time: 1.00 minutes)
    -- [IBM][CLI Driver][DB2/NT64] SQL1131N DARI (Stored Procedure) process has been terminated abnormally. SQLSTATE=38503



    CALL SYSPROC.ADMIN_CMD('REORG INDEXES ALL FOR TABLE LINK.AAAD ALLOW WRITE ACCESS')@
    -- Failure!!! (at: 4/24/2012 1:39:31 PM - execution time: 1.00 minutes)
    -- [IBM][CLI Driver][DB2/NT64] SQL1131N DARI (Stored Procedure) process has been terminated abnormally. SQLSTATE=38503
    -----------------------------------------
    -- Results Summary - 4/24/2012 1:40:05 PM
    -----------------------------------------
    --
    -- Statements Failures Successes Warnings
    --
    -- CALL 2 2 0 0
    --
    -- Totals: 2 2 0 0
    Please note this is not just for an individual table its the command call sysproc.admin_cmd that is failing.
    Last edited by 77920; 04-24-12 at 14:43.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Always put your DB2-version+fixpack and operating-system in new posting.
    What authorities does the user that submits the admin_cmd have on the database?

  3. #3
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by db2mor View Post
    Always put your DB2-version+fixpack and operating-system in new posting.
    What authorities does the user that submits the admin_cmd have on the database?
    It's Windows Server 2008 Standard R2- DB2 version 9.5 fixpack 4 or 5... i cannot remember
    and as far as i remember its got full writes as far as premissions
    where would i edit the authorities of the user that submits the admin-cmd on the DB?
    Last edited by 77920; 04-24-12 at 15:48.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Use the db2level command on the server to get the version and fixpack of db2.

    Ask the DBA to look in the DB2 diagnostics file to see if there are any relevant messages.


    To find what authorities you have, refer to:
    AUTH_LIST_AUTHORITIES_FOR_AUTHID - IBM DB2 9.7 for Linux, UNIX, and Windows

    To see the authorities needed for reorg, refer to:
    REORG INDEXES/TABLE using ADMIN_CMD - IBM DB2 9.7 for Linux, UNIX, and Windows

  5. #5
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    This seems to be the same question asked at the start of March 2012.

    Please post if you have a support-contract with IBM.
    If you do not have a support contract, and this is a production system, then buy a support contract.

    If this is a production system, then you *must* find a test-system that is the same (as much as possible) to the production system. You want to make experiments and do problem-determination on the test system.

    You must try to re-create the symptom on a test-system that has the same database, same db cfg, same dbm cfg, same registry-settings as production.

    If you have a test system that's like production, and the problem symptom re-creates there, then you can make experiments to do the problem-determination. However, on the test-system if the problem *does not recreate* and the admin_cmd works fine then you *must* perform a detailed comparison of the working versus the failing system. The differences will lead to solving the problem.

    DARI procedures like admin_cmd will not crash unless something is wrong with the setup. The key tool here is elimination of each suspect. There might be permissions problems (at the operating system level) , there might be a partially broken installation (someone or something deleted a file), file and directory permissions might be incorrect.

    On the test-system, with diaglevel=4, rename your db2diag.log (so db2 will create a fresh, smaller one), and try to recreate the problem. If the symptom re-creates on the test-system then you are in luck . Patiently examine db2diag.log for clues.
    Check in eventvwr to see if there are any clues.

  6. #6
    Join Date
    Mar 2012
    Posts
    15
    No support contract and also no DBA.
    And we're going to create a mirror of our current server and create a new VM for testing.
    Yes its the same question from back in March however i think i asked the wrong questions then.

    I will try the above information you supplied.
    Thanks

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

  8. #8
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    No DBA and no support contract? wtf !

    Is this a for-profit corporation or is it a charity/not-for-profit?

    What is the application worth to the business?

    Anyway, you have an alternative to admin_cmd -
    just use a Windows batch script to run the
    reorgs scripted in CLP or scripted via Perl.
    So it just does not seem critical if
    your admin_cmd is crashing when there are
    alternative ways to get the job done.
    It's a question of *which* of many problems
    that you face deserve the most time to investigate and solve.

    But careful problem determination will expose the
    configuration detail that's causing the issue.

  9. #9
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by db2mor View Post
    No DBA and no support contract? wtf !

    Is this a for-profit corporation or is it a charity/not-for-profit?

    What is the application worth to the business?

    Anyway, you have an alternative to admin_cmd -
    just use a Windows batch script to run the
    reorgs scripted in CLP or scripted via Perl.
    So it just does not seem critical if
    your admin_cmd is crashing when there are
    alternative ways to get the job done.
    It's a question of *which* of many problems
    that you face deserve the most time to investigate and solve.

    But careful problem determination will expose the
    configuration detail that's causing the issue.
    Its definetly a profit organization we just dont have a DBA or support contract with IBM its expensive and alot of it we found the answers online on forums and through friends of friends.

    the system works fine just the admin_cmd doesnt i've granted every premission possible to all users in which i want to be able to run the admin_cmd i still think it has something to do with java... since that's a feature / function of java.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by 77920 View Post
    Its definetly a profit organization we just dont have a DBA or support contract with IBM its expensive and alot of it we found the answers online on forums and through friends of friends.
    Of course, you can find free help for technical questions, how-tos etc. You dont need IBM support for this.
    But if you hit on a software bug or more common, hardware issue, you are on your own. It is then you require IBM support contract .. Forums or friends cannot be or any help!
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Nov 2003
    Location
    Am Ende der Welt
    Posts
    12
    You've got a SQL1131N error (SQLSTATE=38503), so according to the manuals, the cause of this error may be:

    * There was a coding error (for example, segmentation violation) within
    the routine.
    * The process has been terminated by another process through the use of
    a signal.

    Check carefully the syntax of the reorg indexes/table command for your DB2 version.

    Regards,

    Cesar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •