Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Hyderabad, India
    Posts
    14

    Red face Unanswered: Transaction Log Full in SP

    Hello Group,
    Im trying to insert/ update a table data with another table's data, say im updating the table tab1 with the data in tab2. The tab1 has got around 30k records and tab2 has got 115k records. I worte an sp which checks any record of tab2 present in tab1 if so then im asking to update else im inserting it.. Now after a certaing amount of time im getting Transaction log full error. When i try to commit the same in the code after the first insert and commit my sp getting exited... So how best can be solution be solved.. and also how can the Transaction log be cleared and size could be increased such that max of 2 to 4 lakh of records could be held?... Cause i have such sceneraios.
    Looking forward for Some help on this issue.
    Also i would appreciate if i could be provided help on how to increase the Sort area and other stuff.. with some links to some books.

    Regards,
    Krishna

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try the "Useful DB2 Stuff" thread for links to all the DB2 manuals which can be downloaded in PDF format from the IBM website.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2003
    Location
    kualaumpur
    Posts
    33

    Re: Transaction Log Full in SP

    can paste ur SP to here so that i can have look and tell u accordingly

    Originally posted by vaddadik
    Hello Group,
    Im trying to insert/ update a table data with another table's data, say im updating the table tab1 with the data in tab2. The tab1 has got around 30k records and tab2 has got 115k records. I worte an sp which checks any record of tab2 present in tab1 if so then im asking to update else im inserting it.. Now after a certaing amount of time im getting Transaction log full error. When i try to commit the same in the code after the first insert and commit my sp getting exited... So how best can be solution be solved.. and also how can the Transaction log be cleared and size could be increased such that max of 2 to 4 lakh of records could be held?... Cause i have such sceneraios.
    Looking forward for Some help on this issue.
    Also i would appreciate if i could be provided help on how to increase the Sort area and other stuff.. with some links to some books.

    Regards,
    Krishna

  4. #4
    Join Date
    Nov 2003
    Location
    Jakarta, Indonesia
    Posts
    32
    UPDATE DATABASE CONFIGURATION [FOR database-alias]
    USING {config-keyword value ...}

    config-keyword:
    APP_CTL_HEAP_SZ, APPGROUP_MEM_SZ, APPLHEAPSZ, AUTORESTART, AVG_APPLS,
    BLK_LOG_DSK_FUL, BUFFPAGE, CATALOGCACHE_SZ, CHNGPGS_THRESH, COPYPROTECT,
    DATABASE_MEMORY, DBHEAP, DFT_DEGREE, DFT_EXTENT_SZ, DFT_LOADREC_SES,
    DFT_PREFETCH_SZ, DFT_QUERYOPT, DFT_REFRESH_AGE, DFT_SQLMATHWARN, DIR_OBJ_NAME,
    DISCOVER_DB, DLCHKTIME, DL_EXPINT, DL_NUM_COPIES, DL_TIME_DROP, DL_TOKEN,
    DL_UPPER, DL_WT_IEXPINT, DYN_QUERY_MGMT, ESTORE_SEG_SZ, GROUPHEAP_RATIO,
    INDEXREC, INDEXSORT, LOCKLIST, LOCKTIMEOUT, LOGBUFSZ, LOGFILSIZ, LOGPRIMARY,
    LOGRETAIN, LOGSECOND, MAXAPPLS, MAXFILOP, MAXLOCKS, MAX_LOG, MINCOMMIT,
    MIRRORLOGPATH, MULTIPAGE_ALLOC, NEWLOGPATH, NUM_DB_BACKUP, NUM_ESTORE_SEGS,
    NUM_FREQVALUES, NUM_IOCLEANERS, NUM_IOSERVERS, NUM_LOG_SPAN, NUM_QUANTILES,
    OVERFLOWLOGPATH, PCKCACHESZ, REC_HIS_RETENTN, SEQDETECT, SHEAPTHRES_SHR,
    SOFTMAX, SORTHEAP, STAT_HEAP_SZ, STMTHEAP, TRACKMOD, TSM_MGMTCLASS,
    TSM_NODENAME, TSM_OWNER, TSM_PASSWORD, USEREXIT, UTIL_HEAP_SZ.

    NOTE: Some config-keywords are platform/node specific and may not be
    available on all platforms.


    EXAMPLE
    UPDATE DB CFG FOR mydatabase USING logretain OFF logprimary 5 logsecond 2 logfilsiz 4096;

    this will make your database log to 5 primary log, and 2 secondary log, with logsize of 4096 * 4KB

  5. #5
    Join Date
    Nov 2003
    Posts
    4
    Is your DB2 service still running when you try to commit your transaction? If not, try to increase the stack size of your DB2 instance, using

    UPDATE DBM CFG USING AGENT_STACK_SZ xxxx

    You can get the current value by using

    GET DBM CFG


    But be careful setting the value too high: look the DB2 doc...

    Hope that helps,

    Tux

Posting Permissions

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