Db2 Bulk Load (TEMP_FILE) Error

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
him121
Premium Member
Premium Member
Posts: 55
Joined: Sat Aug 07, 2004 1:50 am

Db2 Bulk Load (TEMP_FILE) Error

Post by him121 »

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
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Permissions?

Reason code 1 is usually permissions, reason code 2 is usually file not found, and so on.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
kumar_s
Charter Member
Charter Member
Posts: 5245
Joined: Thu Jun 16, 2005 11:00 pm

Post by kumar_s »

Search will help you

Code: Select all

"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. "
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

Ray is right. It surely is permissions. Check out this on IBM's help.Scroll down untill you see explanation for SQL3508N.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
kumar_s
Charter Member
Charter Member
Posts: 5245
Joined: Thu Jun 16, 2005 11:00 pm

Post by kumar_s »

Or the same can be found in the previous post. :wink:
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

All that shows is that you can copy/paste faster than DSGuru2B can type!
:lol:
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

The news channel caught my attention while i was typing :roll:
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
shamshad
Premium Member
Premium Member
Posts: 147
Joined: Wed Aug 25, 2004 1:39 pm
Location: Detroit,MI

Post by shamshad »

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
Premium Member
Premium Member
Posts: 772
Joined: Fri Oct 01, 2004 3:06 pm
Location: Minnesota

Post by bcarlson »

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: Select all

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

Brad.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Who knows, maybe the OP will return and mark the post as resolved.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply