SQL3508N Error in accessing a file or path of type "TEMP_FILE" during load

[contractsup]

Environment:

$ uname -a
AIX <withheld2 5 000100614C00

$ db2level
DB21085I Instance "<withheld>" uses "32" bits and DB2 code release
"SQL08024"
with level identifier "03050106".
Informational tokens are "DB2 v8.1.1.104", "s060120", "U805924", and
FixPak
"11".
Product is installed at "/usr/opt/db2_08_01".

Failing SQL synopsis:

DECLARE xyz CURSOR FOR SELECT <withheld>
;

....
DB20000I The SQL command completed successfully.
LOAD FROM xyz OF CURSOR MESSAGES /path/to/message/dir/xyz.log
TEMPFILES PATH /path/to/temp/dir
REPLACE INTO <schema>.<table>
STATISTICS YES AND DETAILED INDEXES ALL
;

Agent Type Node SQL Code Result
__________________________________________________ ____________________________
LOAD 001 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 002 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 003 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 004 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 005 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 006 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 007 +00000000 Success.
__________________________________________________ ____________________________
LOAD 008 +00000000 Success.
....
LOAD 028 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 029 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 030 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
RESULTS: 2 of 30 LOADs completed successfully.
__________________________________________________ ____________________________

Summary of LOAD Agents:
Number of rows read = 0
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 0

SQL3508N Error in accessing a file or path of type "TEMP_FILE" during
load or
load query. Reason code: "1". Path:
"/path/to/temp/dir/DB20049A.PID/DB200003.OID/load0024".

There is approximately 172 gig left on drive. This is a new directory,
so persistent historical data is not the problem. This is only
happening
on our development box - there is no problem in the production or user
acceptance
test environments.

The problem is intermittent. Sometimes you can just resubmit the job
and it works. Right now, it's just not working.

TIA for any help or advice.

Dan Mercer

[Asphalt Blazer]

Can be different reasons
1) check ulimit -a on your dev box to see if there is a limit on file
size
2) check the chmod values for the directory you are writing the load
file to..
maybe the user you are loading the data as does not have appropriate
privileges to write to the directory.

************************************************** *************************************************
contractsup wrote:

Environment:

$ uname -a
AIX <withheld2 5 000100614C00

$ db2level
DB21085I Instance "<withheld>" uses "32" bits and DB2 code release
"SQL08024"
with level identifier "03050106".
Informational tokens are "DB2 v8.1.1.104", "s060120", "U805924", and
FixPak
"11".
Product is installed at "/usr/opt/db2_08_01".

Failing SQL synopsis:

DECLARE xyz CURSOR FOR SELECT <withheld>
;

...
DB20000I The SQL command completed successfully.
LOAD FROM xyz OF CURSOR MESSAGES /path/to/message/dir/xyz.log
TEMPFILES PATH /path/to/temp/dir
REPLACE INTO <schema>.<table>
STATISTICS YES AND DETAILED INDEXES ALL
;

Agent Type Node SQL Code Result
__________________________________________________ ____________________________
LOAD 001 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 002 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 003 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 004 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 005 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 006 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 007 +00000000 Success.
__________________________________________________ ____________________________
LOAD 008 +00000000 Success.
...
LOAD 028 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 029 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 030 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
RESULTS: 2 of 30 LOADs completed successfully.
__________________________________________________ ____________________________

Summary of LOAD Agents:
Number of rows read = 0
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 0

SQL3508N Error in accessing a file or path of type "TEMP_FILE" during
load or
load query. Reason code: "1". Path:
"/path/to/temp/dir/DB20049A.PID/DB200003.OID/load0024".

There is approximately 172 gig left on drive. This is a new directory,
so persistent historical data is not the problem. This is only
happening
on our development box - there is no problem in the production or user
acceptance
test environments.

The problem is intermittent. Sometimes you can just resubmit the job
and it works. Right now, it's just not working.

TIA for any help or advice.

Dan Mercer

[contractsup]

************************************************** *************************************************

contractsup wrote:

Environment:

$ uname -a
AIX <withheld2 5 000100614C00

$ db2level
DB21085I Instance "<withheld>" uses "32" bits and DB2 code release
"SQL08024"
with level identifier "03050106".
Informational tokens are "DB2 v8.1.1.104", "s060120", "U805924", and
FixPak
"11".
Product is installed at "/usr/opt/db2_08_01".

Failing SQL synopsis:

DECLARE xyz CURSOR FOR SELECT <withheld>
;

...
DB20000I The SQL command completed successfully.
LOAD FROM xyz OF CURSOR MESSAGES /path/to/message/dir/xyz.log
TEMPFILES PATH /path/to/temp/dir
REPLACE INTO <schema>.<table>
STATISTICS YES AND DETAILED INDEXES ALL
;

Agent Type Node SQL Code Result
__________________________________________________ ____________________________
LOAD 001 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 002 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 003 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 004 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 005 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 006 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 007 +00000000 Success.
__________________________________________________ ____________________________
LOAD 008 +00000000 Success.
...
LOAD 028 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 029 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
LOAD 030 -00003508 Init error. Table unchanged.
__________________________________________________ ____________________________
RESULTS: 2 of 30 LOADs completed successfully.
__________________________________________________ ____________________________

Summary of LOAD Agents:
Number of rows read = 0
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 0

SQL3508N Error in accessing a file or path of type "TEMP_FILE" during
load or
load query. Reason code: "1". Path:
"/path/to/temp/dir/DB20049A.PID/DB200003.OID/load0024".

There is approximately 172 gig left on drive. This is a new directory,
so persistent historical data is not the problem. This is only
happening
on our development box - there is no problem in the production or user
acceptance
test environments.

The problem is intermittent. Sometimes you can just resubmit the job
and it works. Right now, it's just not working.

TIA for any help or advice.

Dan Mercer

Asphalt Blazer wrote:

Can be different reasons
1) check ulimit -a on your dev box to see if there is a limit on file
size

ulimit -a
....
file(blocks) unlimited
....

2) check the chmod values for the directory you are writing the load
file to..
maybe the user you are loading the data as does not have appropriate
privileges to write to the directory.

I created a brand new parent directory with permissions 777 - no luck.

However, we have had a similar problem with the MSGFILES keyword. In
both
cases, the error messages have contained truncated path names. The
major
difference between the production, uat and dev environments is the
path length
of the locations where we keep logs and tempfiles. To get around this
we
have symbolically linked our log dir and tempfile dir into /tmp which
seems
to obviate the problem.

Dan Mercer