load(DB2) doesn`t work

fmartinsferreira · Post by **fmartinsferreira** » Mon Jan 08, 2007 11:39 am

My source is a file and my target is a DB2/UDB(UNIX AIX).

When I use the write mode works, but when I use the load mode don`t work.

Does anyone can help me?

Regards,

Fernando

DSguru2B · Post by **DSguru2B** » Mon Jan 08, 2007 11:42 am

What are the error messages that you are getting?

kris007 · Post by **kris007** » Mon Jan 08, 2007 11:46 am

Did you meet the requirements for using a LOAD stage such as setting Non-Recoverable Transactions as TRUE and giving the path for a local message file.

fmartinsferreira · Post by **fmartinsferreira** » Tue Jan 09, 2007 6:58 am

DSguru2B wrote:What are the error messages that you are getting? ...

1 - warning
-> The enviroment variable DB2INSTANCE is currently set to db2inst1

2 - Fatal
-> Error Idx = 1; (...)

Good points:
a - the real name of the DB2 intance is "ipi03" but when was made the catalog in data stage server was named "db2inst1"

b - There is no enviroment variable named: "DB2INSTANCE"

c -There is one enviroment variable named:"$APT_DB2INSTANCE_HOME" and its default value is "/home/epi03". That is the DB2 instalation home directory(in DB2 Server not DataStage Server :D )

bcarlson · Post by **bcarlson** » Tue Jan 09, 2007 4:04 pm

The DB2INSTANCE variable contains the name of the DB2 instance you are connecting to. In the $DSHOME directory, there is a file called 'dsenv' that contains environmental settings for DataStage. When the DataStage server starts, this is one of the scripts called. Your DB2 settings may be in this file, at least it is in ours. The DB2INSTANCE variable is set here, along with INSTHOME which points to the instance home directory.

Check this file and make sure this is pointing to the correct database instance. If you end up changing it, you may need to stop and restart the DataStage server for the values to take effect.

In your point #2, you are missing the text of the error message (...). Can you post that? You can get it within DataStage Director - just double click on (...) and it will print the entire error message.

Brad.

kumar_s · Post by **kumar_s** » Tue Jan 09, 2007 4:13 pm

Was that the full fatal error message? Don't you get more info if you double click the log event?

fmartinsferreira · Post by **fmartinsferreira** » Wed Jan 10, 2007 10:45 am

kumar_s wrote:Was that the full fatal error message? Don't you get more info if you double click the log event? ...

Full error message:

-> DB2 Load Binary Error

-> SQLCODE = -27959; SQLSTATE= /datastage/scratch/ordb21880090bd8bd084_

-> SQL27959N The partitioned database configuration option "PARTITIONED DB CONFIG" is invalid. Reason Code = "1"

Question:

If I change from write to load mode, do I need to change anything in my job?

Regards,

Fernando

fmartinsferreira · Post by **fmartinsferreira** » Wed Jan 10, 2007 10:47 am

kris007 wrote:Did you meet the requirements for using a LOAD stage such as setting Non-Recoverable Transactions as TRUE and giving the path for a local message file.

I don't know if I understand, but I'm using a "DB2/UDB Enterprise Stage" with write method = "load"

bcarlson · Post by **bcarlson** » Wed Jan 10, 2007 11:16 am

I just converted a job yesterday from DB2 write to DB2 load. The only thing I had to change was the write option to load. Nothing else. Now, if you are using either the create or replace mode, then there are some DB Options you need to set but those just specify your partitioning key and target tablespace.

The exception table and message file options are exactly that - options. You shouldn't need to change anything switching from write to load.

Brad.

bcarlson · Post by **bcarlson** » Wed Jan 10, 2007 11:19 am

bcarlson wrote:The DB2INSTANCE variable contains the name of the DB2 instance you are connecting to. In the $DSHOME directory, there is a file called 'dsenv' that contains environmental settings for DataStage. When the DataStage server starts, this is one of the scripts called. Your DB2 settings may be in this file, at least it is in ours. The DB2INSTANCE variable is set here, along with INSTHOME which points to the instance home directory.

Check this file and make sure this is pointing to the correct database instance. If you end up changing it, you may need to stop and restart the DataStage server for the values to take effect.

Have you had a chance to check your dsenv file yet? You may need to work with whomever is your DataStage administrator (or whomever has admistrative rights to update this file).

I think the first step of fixing your issue is making sure the right instance is referenced. Let us know what happens when that is fixed.

Brad.

fmartinsferreira · Post by **fmartinsferreira** » Wed Jan 10, 2007 12:14 pm

bcarlson wrote: Have you had a chance to check your dsenv file yet? You may need to work with whomever is your DataStage administrator (or whomever has admistrative rights to update this file).
I think the first step of fixing your issue is making sure the right instance is referenced. Let us know what happens when that is fixed.

Brad.

I had been checked my dsenv file and I'm thing that is ok.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 10, 2007 2:34 pm

The stage goes to DB2 and checks the partitioning of the table whose name you supply via the tool to the right of the partitioning algorithm. Make sure that an appropriate table name is given (typically the table you're proposing to load).

fmartinsferreira · Post by **fmartinsferreira** » Thu Jan 11, 2007 5:46 am

ray.wurlod wrote:The stage goes to DB2 and checks the partitioning of the table whose name you supply via the tool to the right of the partitioning algorithm. Make sure that an appropriate table name is given (typica ...

Talking with the DBA he told me:

There isn't anything partitioned in the database, what exist is the "dpf" feature(DB2) installed.

Second Brad if I'm using

Write Method = Load
Write Mode = Create or Replace

I need to specify the partitioning key and target tablespace

Right Brad?

Well and if the target table isn't partitioned, I mean:

Is it possible to use "Write Method = Load" when the target table isn't partitioned?

Fernando

ray.wurlod · Post by **ray.wurlod** » Thu Jan 11, 2007 6:25 am

DPF used to be called DB2 EEE and is an environment that supports partitioned tables. Without it, none of the DB2 partitioning stuff is accessible, and DataStage certainly could not employ the DB2 partitioning algorithm.

Given that you can still select the DB2 partitioning algorithm when the write method is Load would, I assume, indicate that you can use Load in a DPF environment. But it may end up being a sequential load if the table itself is not partitioned, since it will show as "single partition" when DataStage queries the system table entries for the "partition table" given in the partitioning algorithm extended properties.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 11, 2007 2:35 pm

DataStage is a 32-bit application. The database can be either 32-bit or 64-bit, but DataStage must find the 32-bit libraries for accessing the database; that is, the 32-bit libraries must occur before the 64-bit libraries in the shared library search path (e.g. LD_LIBRARY_PATH) for DataStage users/processes.

I only assumed that Load could be used in a partitioned environment, for the reasons I gave. I am not in a position to test this assumption.