DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
him121



Group memberships:
Premium Members

Joined: 07 Aug 2004
Posts: 55

Points: 563

Post Posted: Tue Dec 12, 2006 10:07 pm Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Parallel
OS: Unix
Additional info: DS PX 7.5.1, IBM AIX, DB2
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 51753
Location: Sydney, Australia
Points: 281026

Post Posted: Tue Dec 12, 2006 10:15 pm Reply with quote    Back to top    

Permissions?

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

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Sydney and Melbourne
Rate this response:  
Not yet rated
kumar_s

Premium Poster


since February 2006

Group memberships:
Premium Members, Heartland Usergroup

Joined: 16 Jun 2005
Posts: 5233

Points: 26292

Post Posted: Tue Dec 12, 2006 10:42 pm Reply with quote    Back to top    

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

_________________
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Rate this response:  
Not yet rated
DSguru2B

Premium Poster


since February 2006

Group memberships:
Premium Members, Heartland Usergroup

Joined: 09 Feb 2005
Posts: 6854
Location: Houston, TX
Points: 35666

Post Posted: Tue Dec 12, 2006 10:45 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
kumar_s

Premium Poster


since February 2006

Group memberships:
Premium Members, Heartland Usergroup

Joined: 16 Jun 2005
Posts: 5233

Points: 26292

Post Posted: Tue Dec 12, 2006 10:54 pm Reply with quote    Back to top    

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'
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 51753
Location: Sydney, Australia
Points: 281026

Post Posted: Wed Dec 13, 2006 1:16 am Reply with quote    Back to top    

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

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Sydney and Melbourne
Rate this response:  
Not yet rated
DSguru2B

Premium Poster


since February 2006

Group memberships:
Premium Members, Heartland Usergroup

Joined: 09 Feb 2005
Posts: 6854
Location: Houston, TX
Points: 35666

Post Posted: Wed Dec 13, 2006 7:05 am Reply with quote    Back to top    

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

_________________
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Rate this response:  
Not yet rated
shamshad



Group memberships:
Premium Members

Joined: 25 Aug 2004
Posts: 145
Location: Detroit,MI
Points: 1359

Post Posted: Wed Dec 13, 2006 10:34 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
bcarlson



Group memberships:
Premium Members

Joined: 01 Oct 2004
Posts: 772
Location: Minnesota
Points: 7799

Post Posted: Thu Jan 25, 2007 12:59 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 51753
Location: Sydney, Australia
Points: 281026

Post Posted: Thu Jan 25, 2007 3:58 pm Reply with quote    Back to top    

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

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Sydney and Melbourne
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours