DB2\UDB Enterprise Stage doubt

[fmartinsferreira]

Hi, I'm trying to use the DB2\UDB Enterprise Stage but I'm having some doubts:

Scenario:

- My DB2 is 8.1 ESE with the DPF(database partitioning feature) must be installed.
- The target table isn't partitioning.
- The database DB2 is 64 bits.
- The datastage server and database server are differents machine.
- I want to use the "Write Method=Load"

Questions:

1 - I already know that datastage work 32bits, then do I need to install a 32bits client or a 64bits client to connect to the database?

2 - "Write Mode=Replace" x "Write Mode=Truncate" what's the real difference? I understood that when I use "Replace" the process drop and recreate the table and when I use "Truncate", well that's is the point. The truncate from Oracle clear and insert the table without drop and recreate it but "Write Mode=Truncate" well I realy didn't understand it.

3 - In the Target yet: I need to put the table target, but I saw at the stuff that I have to indicate the table partition that will be loaded, how to indicate the partition if the table isn't partitioned?

4 - Let's assume that "owner=dw" and "table name = ft_churn" then I'm putting "Table=dw.ft_churn" where I need to put the partition?

Sorry by so many questions but I'm new in "DB2\UDB Enterprise Stage"

Regards,

Fernando

[ray.wurlod]

1. 32-bit.

2. It's quicker to truncate. And the permissions required are different.

3. When you choose DB2 partitioning algorithm there is a tool to the right of the drop-down list, which opens a dialog in which you can name the partition table.

4. You don't need to - the DB2 partitioning algorithm works all this out for you.

[fmartinsferreira]

1. 32-bit.

2. It's quicker to truncate. And the permissions required are different.

3. When you choose DB2 partitioning algorithm there is a tool to the right of the drop-down list, which opens ...

Thanks

[bcarlson]

I know this is a resolved topic, but can I throw something out there?

We were using truncate with DB2 EE writes and running into performance issues. The problem is that DataStage is not really doing a truncate (an efficient elimination of all data), but a SQL delete (a fully logged transaction). You may not see much issue with smaller tables, but large tables will present performance issues.

What we ended up doing is truncating data in the target table before the DataStage job runs. If the table is created with 'NOT LOGGED INITIALLY', then there is a truncate option that instantaneously truncates the data without using a SQL delete. Here's the syntax:

Code: Select all

alter table <schema>.<tablename> 
activate not logged initially with empty table
;

Another option is to load or import an empty file into the table with the replace option. For any of these options you could easily create a function to do that so you just pass a table name:

Code: Select all

trunc_table <schema>.<tablename>

Just my 2 cents...

Brad.