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

What is the OS and DB2 level? Has this load worked before? If yes, what has changed?

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.

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

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

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.

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

Are you saying you are loading from a compressed file? Can you post your load command?

/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"`

Shouldn't that be "uncompress -c"?

'-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.

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...

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

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.