Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2011
    Posts
    10

    Unanswered: ADMIN_DROP_SCHEMA error

    Dear All,

    I got error from ADMIN_DROP_SCHEMA as follow:


    db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('CUS_TEST', NULL, 'ERRORSCHEMA', 'ERRORTABLE')"
    SQL0443N Routine "*P_SCHEMA" (specific name "") has returned an error
    SQLSTATE with diagnostic text "sqlcode -964:insert into
    "ERRORSCHEMA"."ERRORTABLE" values ". SQLSTATE=38000

    How to fix?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Code:
    SQL0964C  The transaction log for the database is full.
    
    Explanation:
    
    All space in the transaction log is being used.
    
    If a circular log with secondary log files is being used, an attempt has
    been made to allocate and use them. When the file system has no more
    space, secondary logs cannot be used.
    
    If an archive log is used, then the file system has not provided space
    to contain a new log file.
    
    This message can also be returned when the database has used almost all
    of the possible log sequence numbers. The database manager identifies
    database log records using a unique identifier, called a log sequence
    number (LSN). Log sequence numbers are 6-byte hexadecimal numbers that
    range from 0x0000 0000 0000 to 0xFFFF FFFF FFFF. When transactions cause
    database logs to be written, the database uses new LSN values.
    
    The application cannot perform any transactions that cause the database
    to write log records.
    
    The statement cannot be processed.
    
    User response:
    
    Execute a COMMIT or ROLLBACK on receipt of this message (SQLCODE) or
    retry the operation.
    
    If the database is being updated by concurrent applications, retry the
    operation. Log space may be freed up when another application finishes a
    transaction.
    
    Issue more frequent commit operations. If your transactions are not
    committed, log space may be freed up when the transactions are
    committed. When designing an application, consider when to commit the
    update transactions to prevent a log full condition.
    
    If deadlocks are occurring, check for them more frequently. This can be
    done by decreasing the database configuration parameter DLCHKTIME. This
    will cause deadlocks to be detected and resolved sooner (by ROLLBACK)
    which will then free log space.
    
    If the condition occurs often, increase the database configuration
    parameter to allow a larger log file. A larger log file requires more
    space but reduces the need for applications to retry the operation.
    Transaction configuration parameters which may need to be adjusted are
    LOGFILSIZ, LOGPRIMARY, LOGSECOND. For more information about the
    transaction log search the DB2 Information Center using phrases such as
    "transaction log".
    
    If installing the sample database, drop it and install the sample
    database again.
    
    If this message was returned because the database used nearly all of the
    possible LSN values, you can reset the LSN values to zero by doing the
    following:
    
    1. unload all data from the database
    2. drop and recreate the database
    3. load all the data
    
    sqlcode: -964

    You ran out of log space. What DB2 version and OS are you using? What are the logging parameters for your database?

    Andy

  3. #3
    Join Date
    Dec 2011
    Posts
    10
    Thanks for reply

    DB2 version 9.7 on Ubuntu 10.4
    but I can dropped some schema

    Log parameter
    Code:
     Log retain for recovery status                          = NO
     User exit for logging status                            = NO
     Catalog cache size (4KB)              (CATALOGCACHE_SZ) = (MAXAPPLS*5)
     Log buffer size (4KB)                        (LOGBUFSZ) = 256
     Log file size (4KB)                         (LOGFILSIZ) = 1000
     Number of primary log files                (LOGPRIMARY) = 3
     Number of secondary log files               (LOGSECOND) = 2
     Changed path to log files                  (NEWLOGPATH) = 
     Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
     Overflow log path                     (OVERFLOWLOGPATH) = 
     Mirror log path                         (MIRRORLOGPATH) = 
     First active log file                                   = 
     Block log on disk full                (BLK_LOG_DSK_FUL) = NO
     Block non logged operations            (BLOCKNONLOGGED) = NO
     Percent max primary log space by transaction  (MAX_LOG) = 0
     Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
     Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
     Log retain for recovery enabled             (LOGRETAIN) = OFF
     User exit for logging enabled                (USEREXIT) = OFF
     HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
     First log archive method                 (LOGARCHMETH1) = OFF
     Options for logarchmeth1                  (LOGARCHOPT1) = 
     Second log archive method                (LOGARCHMETH2) = OFF
     Options for logarchmeth2                  (LOGARCHOPT2) = 
     Failover log archive path                (FAILARCHPATH) = 
     Number of log archive retries on error   (NUMARCHRETRY) = 5
     Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
     Log pages during index build            (LOGINDEXBUILD) = OF

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You have a very small log size: 1000 * 3 * 2 * 4096 = 48 K. You need to increase it to be able to perform this operation. The other drop schemas were small enough, but not this one.

    Andy

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ARWinner View Post
    1000 * 3 * 2 * 4096 = 48 K
    You have probably meant to say 1000 * (3+2) * 4096 ~ 20M, which is still too little.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by n_i View Post
    You have probably meant to say 1000 * (3+2) * 4096 ~ 20M, which is still too little.
    Yes, it should be (3+2), but it is not 20 MB, just 2MB. Which is still too little.

    Andy

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ARWinner View Post
    t it is not 20 MB, just 2MB
    Hm. Must be Friday...
    Attached Thumbnails Attached Thumbnails 20M.png  

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by n_i View Post
    Hm. Must be Friday...
    That is going like a Monday. Yes, that is 20MB total log space.

    Andy

Posting Permissions

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