Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Mar 2012
    Posts
    15

    Exclamation Unanswered: Looking for some help DB2 reorg calls

    CALL SYSPROC.ADMIN_CMD('REORG TABLE MYSCHEMA.AAAD INPLACE ALLOW WRITE ACCESS NOTRUNCATE TABLE START')@

    -- Failure!!! (at: 2/29/2012 2:58:10 PM - execution time: 1.00 minutes)
    -- [IBM][CLI Driver][DB2/NT64] SQL1131N Stored procedure process has been terminated abnormally. SQLSTATE=38503

    - At our company we currently run IBM DB2 Windows Enviroment
    Server SQL05055 06060107 DB2. V 9.5.500.784
    Build Level S091123
    PTF is WR21458
    Java is IBM Corp 1.5.0

    ----- Now running on my local / home pc / Test DB below same call / command to reorg a table for example. I only sellected one but within 2 minutes or less it ran successful at home.

    Is it because i have a new version at home?
    ------------------------------------------------------------------------------------
    -- Logged 2/29/2012 11:14:40 PM by 77920 in database DB_77920 => SEVENDATA [ADMINS]
    -------------------------------------------------------------------------------------
    -- SQL Execute ver. 10.2.3.1
    -------------------------------------------------------------------------------------


    CALL SYSPROC.ADMIN_CMD('REORG TABLE ADMINS.AAAD INPLACE ALLOW WRITE ACCESS NOTRUNCATE TABLE START')@
    -- Success!!! (at: 2/29/2012 11:14:41 PM - execution time: 641 millisecs)


    CALL SYSPROC.ADMIN_CMD('REORG INDEXES ALL FOR TABLE ADMINS.AAAD ALLOW WRITE ACCESS')@
    -- Success!!! (at: 2/29/2012 11:14:42 PM - execution time: 1.14 seconds)



    ------------------------------------------
    -- Results Summary - 2/29/2012 11:14:54 PM
    ------------------------------------------
    --
    -- Statements Failures Successes Warnings
    --
    -- CALL 2 0 2 0
    --
    -- Totals: 2 0 2 0


    ----------------------------------------
    ================================================== ==========
    About DB2 Administration Tools Environment
    ================================================== ==========
    DB2 administration tools level:
    Product identifier SQL09074
    Level identifier 08050107
    Level DB2 v9.7.400.501
    Build level s110330
    PTF IP23238
    ================================================== ==========
    Java development kit (JDK):
    Level IBM Corporation 1.6.0
    ================================================== ==========

    At one point this use to work before some updates. - Does this have anything to do with the Operating System / Registry , the fixpack , java version... I'm just digging to see where / what i can come up with.

    We will need to update to version 9.7 etc with upcoming releases of software that will only run on DB2 9.7+

    - Any help would much be appreciated.
    Last edited by 77920; 03-01-12 at 12:34.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    We need more information. What happens when you try to run the REORG straight from the command line?

    Andy

  3. #3
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by ARWinner View Post
    We need more information. What happens when you try to run the REORG straight from the command line?

    Andy
    I've ran the Reorg directly from Command Liner Processor same result only thing different is it says DARI in the error log.


    I wish i could give more information.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check db2diag.log

  5. #5
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by n_i View Post
    Check db2diag.log
    Its a HUGE file any in specific i'm looking directly for?
    Edit....

    2012-02-29-16.31.00.430000-300 E120069345F558 LEVEL: Warning
    PID : 5040 TID : 3124 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : MYSCHEMA
    APPHDL : 0-12794 APPID: 192.8.200.21.24998.120229212411
    AUTHID : MYSCHEMA
    EDUID : 3124 EDUNAME: db2agent (MYSCHEMA)
    FUNCTION: DB2 UDB, relation data serv, sqlrreorg_indexes, probe:400
    MESSAGE : ADM9501W BEGIN index reorganization on table "MYSCHEMA
    .AAAD" (ID
    "733") and table space "USERSPACE1" (ID "2").

    2012-02-29-16.31.00.462000-300 E120069905F646 LEVEL: Warning
    PID : 5040 TID : 3124 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : MYSCHEMA
    APPHDL : 0-12794 APPID: 192.8.200.21.24998.120229212411
    AUTHID : MYSCHEMA
    EDUID : 3124 EDUNAME: db2agent (MYSCHEMA)
    FUNCTION: DB2 UDB, relation data serv, sqlrreorg_index_obj, probe:550
    MESSAGE : ADM9503W Index reorganization proceeds on index IID "1" (OBJECTID
    "733") in table space "USERSPACE1" (ID "2") for table "MYSCHEMA .AAAD"
    (ID "733") in table space "USERSPACE1" (ID "2").

    2012-02-29-16.31.00.555000-300 E120070553F556 LEVEL: Warning
    PID : 5040 TID : 3124 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : MYSCHEMA
    APPHDL : 0-12794 APPID: 192.8.200.21.24998.120229212411
    AUTHID : MYSCHEMA
    EDUID : 3124 EDUNAME: db2agent (MYSCHEMA)
    FUNCTION: DB2 UDB, relation data serv, sqlrreorg_indexes, probe:600
    MESSAGE : ADM9502W END index reorganization on table "MYSCHEMA .AAAD" (ID
    "733") and table space "USERSPACE1" (ID "2").
    Last edited by 77920; 03-01-12 at 12:35.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by 77920 View Post
    I've ran the Reorg directly from Command Liner Processor same result only thing different is it says DARI in the error log.


    I wish i could give more information.
    If it is failing in the command line, you should get an error. Would you mind sharing that with us so we can help?

    Also you can rename the db2disg.log file before you do the reorg, and DB2 will create a new one that should be easier to read.

    Andy

  7. #7
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by ARWinner View Post
    If it is failing in the command line, you should get an error. Would you mind sharing that with us so we can help?

    Also you can rename the db2disg.log file before you do the reorg, and DB2 will create a new one that should be easier to read.

    Andy
    Let me clarify that its not the reorg that fails - ReOrg command runs fine its a call sys procedure for anything for any table or schema that is not working. It's Call SysProc ADMIN_CMD
    for anything that fails.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    OK, now we are getting somewhere.

    Code:
    SQL1131N  DARI (Stored Procedure) process has been terminated
          abnormally.
    
    Explanation:
    
    The cause of this error may be:
    *  There was a coding error (for example, segmentation violation) within
       the DARI routine.
    *  The DARI process has been terminated by another process through the
       use of a signal.
    
    User response:
    
    Reinitiate the DARI request after doing the following :
    *  Ensure that the DARI procedure is free from programming errors.
    *  Make sure that no user is sending a termination signal to the DARI
       process.
    
     sqlcode: -1131
    
     sqlstate: 38503
    You mentioned a fixpack was recently applied. Did you rebind all the packages?

    Andy

  9. #9
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by ARWinner View Post
    OK, now we are getting somewhere.

    Code:
    SQL1131N  DARI (Stored Procedure) process has been terminated
          abnormally.
    
    Explanation:
    
    The cause of this error may be:
    *  There was a coding error (for example, segmentation violation) within
       the DARI routine.
    *  The DARI process has been terminated by another process through the
       use of a signal.
    
    User response:
    
    Reinitiate the DARI request after doing the following :
    *  Ensure that the DARI procedure is free from programming errors.
    *  Make sure that no user is sending a termination signal to the DARI
       process.
    
     sqlcode: -1131
    
     sqlstate: 38503
    You mentioned a fixpack was recently applied. Did you rebind all the packages?

    Andy
    What i mentioned was that on my home PC i have the most recent fixpacks / version of DB2 9.7

    Here at work i tryed to run the db2level to find out what fixpack is applied but nothing recently has been changed. The problem is no Admin Call commands can be ran.

    And we're not sure of the cause. I was using the reorg example but forget get completely i need to find out why we cannot use CALL commands for the admin_cmd's.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try rebinding the packages (db2ubind.lst).

    Andy

  11. #11
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by ARWinner View Post
    Try rebinding the packages (db2ubind.lst).

    Andy
    Andy I appreciate you're efforts in helping solve this problem.
    What will occur by rebinding the packages. *Note* this is a live DB that is in use 24/7 i don't wish to mess anything up, as i said i'm just digging to get info at the end of the day the IT Manager which isn't me will be doing these things to see if it solves the problem.

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Rebinding the DB2 utilities (db2ubind.lst) will not hurt anything on the database. To prevent lock contention, it should be performed during a scheduled maintenance.

    Andy

  13. #13
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by ARWinner View Post
    Rebinding the DB2 utilities (db2ubind.lst) will not hurt anything on the database. To prevent lock contention, it should be performed during a scheduled maintenance.

    Andy
    Thanks man! - I will reccomend or ask if he has done this or not.
    My intention is to learn DB2 / get Certified as a DBA over the next 5 years.
    But i'm just asking questions not sure if they are the right ones or not.


    **** EDIT ****
    - System Administrator *IT Manager* says he has already done this and the problem still occurs.
    Anything else you could think of that would cause us not to be able to use CALL admin commands?
    he believes that it has something to do with Java any troubleshooting steps you could think of andy?
    *** *********
    Last edited by 77920; 03-01-12 at 14:47.

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Is there anything in db2diag.log that pertains to this (not the command that is run)?

    Andy

  15. #15
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by ARWinner View Post
    Is there anything in db2diag.log that pertains to this (not the command that is run)?

    Andy
    Not that I can see... it doesn't indicate any errors in this log for anything we try for calling admin commands.

    it says it trys it trys and it ends that's my basic understanding of it.

Posting Permissions

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