DB2_Bulkloading_records not inserting

somu_june · Post by **somu_june** » Thu May 10, 2007 10:29 pm

Hi,

Iam using Db2/UDB load plugin to load data in to a Db2 table. The job finished ok with no warnings but when I check in table it is showing 0 records . Please can some body help me . Properties I changed in Load stage are

Load Method : Sequential File
Load Immediate : No
Load From Client : Yes
Directory for Data and Command Files : /Datastage/DS/
Remove Intermediate Data file : No
File type of the data format : IXF
Load Mode : Insert
Local Message File Name : /Datastage/DS/FFFF.txt
Directory for temporary files : /Datastage/DS/
Non Recoverable : No
Without Prompting : No

And all remaining options are set to default and they are not touched . Can some body tell whether Iam missing some thing or selecting wrong options

Thanks,
SomaRaju

ray.wurlod · Post by **ray.wurlod** » Thu May 10, 2007 11:16 pm

Load Immediate = No

This means that the control and data files are generated but the loader is not invoked.
It has done precisely what you asked.

rafik2k · Post by **rafik2k** » Fri May 11, 2007 1:56 am

Code: Select all

Load Immediate = No

This should work when Load Method : Sequential File

What is the File type of the data format? is it IXF or Delimited ASCII format?

When you run the job, is performance statistics showing any rows to the input link of DB2_UDB_Load

somu_june · Post by **somu_june** » Fri May 11, 2007 7:17 am

Hi Ray,

As you mentioned I changed Load Immediate to 'YES' but my job is failed by throwing this error

Tgt_Conf_Pricetype_Tbl,0: Warning: CopyOfPopulate_Conf_Price.Tgt_Price_Tbl: ERROR occurred : loading table, SQLCODE -3508

Tgt_Conf_Pricetype_Tbl,0: Warning: CopyOfPopulate_Conf_Pricetype.Tgt_Conf_Pricetype_Tbl: SQL3508N Error in accessing a file or path of type "TEMP_FILE" during load or
load query. Reason code: "1". Path:
"/DsProjects/MAP/DB200010.PID/DB20000F.OID/load".

Tgt_Conf_Pricetype_Tbl,0: Warning: CopyOfPopulate_Conf_Pricetype.Tgt_Conf_Pricetype_Tbl: Error occurred while loading table

What path should I provide for TEMP_FILE .

Thanks,
SomaRaju

DSguru2B · Post by **DSguru2B** » Fri May 11, 2007 7:22 am

Search on the error message. I remember going through this ordeal myself. It has been discussed in detail.

somu_june · Post by **somu_june** » Fri May 11, 2007 12:35 pm

Hi DSguru,

I searched in the form . I found in one of the post saying each node needs to have a local path to write to for the database's temp files . But Iam confused what temp directory path I have to provide.

In my env file I can find

DB2DIR = /usr/opt/db2_08_01 ; export DB2DIR
DB2INSTANCE = db2sos01; export DB2INSTANCE
INSTHOME = /db2sos01 ; export INSTHOME

Where can I find the Temp file that I can I load . Is it in INSTANCE home dorectory or DB2DIR

Thanks,
SomaRaju

DSguru2B · Post by **DSguru2B** » Fri May 11, 2007 1:43 pm

TEMP_FILE should be a file that is local to DB2 and not on your datastage server.

somu_june · Post by **somu_june** » Sat May 12, 2007 12:52 pm

Hi Dsguru,

In one of the post it was mentioned like this

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

but here u001/local is the Datastage directory, I mean Db2 client Instance path. MY question is can I go to Db2Client instance path in Datastage server and create a temperory file path as the above poster mentioned

Thanks,
SomaRaju