Db2 Bulk Load (TEMP_FILE) Error

him121 · Posted: Tue Dec 12, 2006 10:07 pm

Hi All

I am getting below error... while loading Data through Db2 bulk load.
I have alredy created TEMP_FILE into below path.
I am not able to understand what is "/DB200002.PID/DB2" means?
is it runtime temp file creation? If anyother setting needs to be done then give me the idea.

Thanks a Lot

Himanshu

DB2_UDB_Load_277,0: Warning: DBLDBalances_R_D_BulkLoad.DB2_UDB_Load_277: SQL3508N Error in accessing a file or path of type "TEMP_FILE" during load or
load query. Reason code: "1". Path:
"/baselwb/ds_wdir/dataset/bcrswb/TEMP_FILE/DB200002.PID/DB2".

ray.wurlod · Posted: Tue Dec 12, 2006 10:15 pm

Permissions?

Reason code 1 is usually permissions, reason code 2 is usually file not found, and so on.

kumar_s · Posted: Tue Dec 12, 2006 10:42 pm

Search will help you

Code:

"If doing a load and the table space is not in load pending state, correct the problem and invoke the load utility again. If the table space is in load pending state, then invoke the load utility in RESTART or REPLACE mode, or restore a backup of the table space(s). The state of the table space can be determined using the LIST TABLESPACES command.

The following is a list of reason codes:

1
Unable to open the file.

This could be caused by an incorrect file name or insufficient authority to access the file/directory. Correct the problem and either restart or rerun the load.

The load temporary file could have been destroyed or the database could have been restored from an earlier backup. Load restart is not supported under these circumstances. Use load terminate to bring the table out of load pending state.
2
Unable to read/scan the file.

This could be the result of a hardware error. If the error is a hardware error, take the appropriate action and restart or rerun the load.
3
Unable to write to or change size of the file.

This could be the result of a disk full condition or a hardware error. Refer to the file type list below and either ensure there is enough space to run the load or specify a different location to be used. Restart or rerun the load. If the error is a hardware error, take the appropriate action an restart or rerun the load.
4
The file contains invalid data.

A file required by the load contains incorrect data. See the action described for TEMPFILES_PATH.
5
Unable to close the file.

If the load cannot be restarted or rerun, contact your IBM service representative.
6
Unable to delete the file.

If the load cannot be restarted or rerun, contact your IBM service representative.
7
Parameter specified incorrectly. Refer to the list of file types to determine the parameter in error and rerun the load with a valid parameter.

The following is a list of file types:

SORTDIRECTORY
Ensure that the workdirectory parameter is specified properly. There must be enough combined space in all the directories to hold twice the size of the index keys for the loaded data. For load insert and load restart there must also be room for twice the size of the index keys of the existing data in the table.
MSGFILE
Ensure that the messagefile parameter is specified properly. There must be enough disk space to write out the messages that occur during the load.

If this is a load query, ensure that the local message file parameter is NOT the same as the messagefile parameter used for the load whose status is being queried.
TEMPFILES_PATH
Ensure that the tempfiles path parameter is specified properly. See the Data Movement Utilities Guide and Reference for more details on this parameter. "

DSguru2B · Posted: Tue Dec 12, 2006 10:45 pm

Ray is right. It surely is permissions. Check out this on IBM's help .Scroll down untill you see explanation for SQL3508N .

kumar_s · Posted: Tue Dec 12, 2006 10:54 pm

Or the same can be found in the previous post. Wink

ray.wurlod · Posted: Wed Dec 13, 2006 1:16 am

All that shows is that you can copy/paste faster than DSGuru2B can type!
Laughing

DSguru2B · Posted: Wed Dec 13, 2006 7:05 am

The news channel caught my attention while i was typing Rolling Eyes

shamshad · Posted: Wed Dec 13, 2006 10:34 am

I had the same problem. Make sure you placed the temp file location of the db2 server where db2 is installed. Not were the jobs are.

It kind of looks weird but this is the correct way.

bcarlson · Posted: Thu Jan 25, 2007 12:59 pm

Just came across this posting.

If anyone cares a month+ later....

The TEMP_FILE error usually occurs when the database instance owner cannot write to the local server. If you are on a multi-node system, each node needs to have a local path to write to for the database's temp files.

For example, on our system we have a directory /u001/local on our ETL server (which has a DB2 client instance) and our database server. Each user creates their own directory structure under this - again, on all servers. The directory structure is <userid>/load/tmp.

The permissions on this need to be open so that the database instance owner can read/write/execute into it. Now, most sys admins or security groups don't like files or directories to be world-writeable (like 777). What we did is make sure the directory structure has a group ownership that is shared between user and database instance. In our case, we have a group called db2load. So when the user creates his directories, he makes sure his primary group is db2load so that the directories are created as that group owner. Or he uses chgrp to set the group owner. The user also sets the sticky bit for the permissions so that anything created by the database in that directory will have the same group ownership.

Code:

cd /u001/local
mkdir -p someuser/load/tmp
chgrp -R db2load someuser/
chown -R 4775 someuser

FYI - these same steps are used when setting up the directory that DataStage will use. In this case, it is only created once (not for each user) and the TMPDIR variable is set at the project level to reference the directory path.

Hope this helps. If not the original poster, maybe someone else. Smile

Brad.

ray.wurlod · Posted: Thu Jan 25, 2007 3:58 pm

Who knows, maybe the OP will return and mark the post as resolved.