Results 1 to 14 of 14

Thread: Load Hangs

  1. #1
    Join Date
    Jun 2003
    Posts
    127

    Unanswered: Load Hangs

    Load which is supposed to take a couple of minutes hangs. Since this is a test system could afford downtime today. Except for a generic system error.
    =========
    db2diag -rc 0x8012006D gives a SQL0902C A system error (reason code = "") occurred. Subsequent SQL
    statements cannot be processed.

    Number of sqlca tokens : 0
    Diaglog message number: 1ssage, don't see anything
    =========

    diag.log:
    2009-09-05-04.18.42.565749-240 I1580969A427 LEVEL: Warning
    PID : 274576 TID : 1 PROC : db2lrid 0
    INSTANCE: pajzgrr NODE : 000 DB : DZGRR
    APPHDL : 0-493 APPID: *LOCAL.pajzgrr.090905081841
    FUNCTION: DB2 UDB, database utilities, DIAG_NOTE, probe:0
    DATA #1 : String, 80 bytes
    LOADID: 327768.2009-09-05-04.18.42.498793.0 (7;11)
    Load CPU parallelism is: 4, 0

    2009-09-05-09.30.03.535307-240 I1581397A425 LEVEL: Error
    PID : 1589334 TID : 1 PROC : db2agent (DZGRR) 0
    INSTANCE: pajzgrr NODE : 000 DB : DZGRR
    APPHDL : 0-888 APPID: GA654454.HCDA.01B585133003
    FUNCTION: DB2 UDB, relation data serv, sqlrr_appl_init, probe:180
    RETCODE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
    "SQLCA has already been built"

    2009-09-05-09.30.03.535679-240 I1581823A695 LEVEL: Error
    PID : 1589334 TID : 1 PROC : db2agent (DZGRR) 0
    INSTANCE: pajzgrr NODE : 000 DB : DZGRR
    APPHDL : 0-888 APPID: GA654454.HCDA.01B585133003
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -20157 sqlerrml: 8
    sqlerrmc: PZGRRPTS
    sqlerrp : sqlrrait
    sqlerrd : (1) 0x8012006D (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate:
    =================
    ID = 55656
    Type = LOAD
    Database Name = DZGRR
    Partition Number = 0
    Description = OFFLINE LOAD Unknown file type AUTOMATIC INDEXING REPLACE NON-RECOVERABLE PZGRRTP .ASSET_LIST
    Start Time = 09/05/2009 04:18:42.524074
    Progress Monitoring:
    Phase Number = 1
    Description = SETUP
    Total Work = 0 bytes
    Completed Work = 0 bytes
    Start Time = 09/05/2009 04:18:42.524078
    Phase Number [Current] = 2
    Description = LOAD
    Total Work = 0 rows
    Completed Work = 0 rows
    Start Time = 09/05/2009 04:18:42.552619
    Phase Number = 3
    Description = BUILD
    Total Work = 1 indexes
    Completed Work = 0 indexes
    Start Time = Not Started
    ----------------------------
    db2 list applications show detail

    CONNECT Auth Id Application Name Appl. Application Id Seq# Number of Coordinating DB Coordinator Status Status Change Time DB Name DB Path
    Handle Agents partition number pid/thread
    ------------------------------ -------------------- ---------- ------------------------------ ---- ---------- ---------------- --------------- ------------------------------ -------------------------- -------- --------------------
    UZGR0UCD db2bp 493 *LOCAL.pajzgrr.090905081841 0006 1 0 327768 Performing a Load 09/05/2009 04:18:42.550265 DZGRR /pajzgrr/dzgrr0101/pajzgrr/NODE0000/SQL00001/
    ---------------------

    No loadmessage has been created so far. The data is being read from a NAS device. I'm using PIPES to do the load.


    Thanks
    Srini

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    What is the OS and DB2 level? Has this load worked before? If yes, what has changed?

  3. #3
    Join Date
    Jun 2003
    Posts
    127
    This is v8.2 on AIX 5.3. No change has been done. Initially thought that its NAS not responding but their teamm says its fine.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    The reason I asked about the OS is because load can hang when the message queues are exhausted on some platforms. This is not an issue with DB2 on AIX.

    Some things to try/check:

    - start the load again. If it appears hung, check if it's waiting to acquire a lock. I frequently see load conflicting with an online backup.

    - if possible, remove NAS from the picture to verify it's not causing problems

  5. #5
    Join Date
    Jun 2003
    Posts
    127
    Thanks for the response. I already looked at the things you mentioned. I took snapshots to check for locks. To make sure that I was not missing anything, the application was brought down and database put in quiesce mode. Also online TSM backup has been rescheduled. Unfortunately I have to use NAS. The exported data from another server is placed on the NAS.

    Srini

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Try testing the load from /dev/null to see if it works.

    Also, stop the instance and check for any leftover processes / IPC resources owned by the instance owner. Perform the following:

    - db2stop
    - ipclean
    - ps -ef | grep <instance name>
    If you see any processes owned by the instance owner, remove them using the kill command

    - ipcs | grep <instance name>"
    If you see any IPC resources owned by the instance owner, remove them using the ipcrm command


    Once everything has been cleaned up, start the instance and rerun the load command. If it's still hanging, then additional information should be collected. It's almost impossible to troubleshoot a true hang condition without collecting additional diagnostic info, but this info won't be helpful without contacting DB2 support (v8 is out of support unless you have a service extension).

    You can try increasing diaglevel to 4 to see if it will log any additional information, but most likely it won't if the load is hanging.

  7. #7
    Join Date
    Jun 2003
    Posts
    127
    Thanks. I already did all those.

    I did try /dev/null, removed semaphores, recycled db2. Also, checked if the compressed ixf file is valid. Since I'm using pipes, it creates .ixf.Z file. AIX recognizes the file as compressed file when I do a 'file a.ixf.Z'. I also exported the data with 'modified by codepage' option to make sure there is no code page issue (though both sides have the same codepage). I remember encountering an issue which got resolved using the 'modified' option sometime in the past (though there was an error thrown at that time). This didn't work either.

    The only two things I want to do are ask NAS guys to take a closer look or as you pointed out, up the diaglevel. I might have to run db2support.

    P.S: As you pointed out, its difficult to troubleshoot hangs.

    Srini

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Are you saying you are loading from a compressed file? Can you post your load command?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Jun 2003
    Posts
    127
    /usr/sbin/mknod $DATADIR/$TABLE.LOADPIPE p
    chmod 777 $DATADIR/$TABLE.LOADPIPE
    uncompress < $DATADIR/$TABLE.ixf.Z > $DATADIR/$TABLE.LOADPIPE &
    chmod 777 $DATADIR/*
    echo "db2 -ec load from $DATADIR/$TABLE.LOADPIPE of ixf messages $LOGDIR/$TABLE.
    load_msg replace into $SCHEMA_TABLE nonrecoverable" >> $LOGFILE
    x1=`db2 -ec "load from $DATADIR/$TABLE.LOADPIPE of ixf messages $LOGDIR/$TABLE.load_msg replace into $SCHEMA_TABLE nonrecoverable"`

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Shouldn't that be "uncompress -c"?
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Jun 2003
    Posts
    127
    '-c' writes to standard output. Just uncompress (without any options), worked on another server. I'm not sure if its NAS the issue or something in DB2 specific to this server.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Kota
    '-c' writes to standard output. Just uncompress (without any options), worked on another server.
    You don't seem to understand what the script you've posted is supposed to do. May be you should google for Unix pipes or something...
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Jun 2003
    Posts
    127
    Well, I'm the one who wrote it and it works on other servers very well. This is part of the script where the load is being done. I looked into LOAD as well (before writing it). Thank You

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Kota
    I'm not sure if its NAS the issue or something in DB2 specific to this server.
    You are free to troubleshoot NAS or DB2, of course, but I think the problem is in the way you are writing (or not writing) to the pipe.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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