How to prevent truncating before data extraction?

Palermo · Post by **Palermo** » Thu May 07, 2015 3:25 pm

Hi,

I have a issue. SQL query performs for a long time from DB2 Connector stage and the target table is truncated before data extraction. During this time the target table is empty. How to prevent truncating the target table before data extraction from source table by using only one parallel job?

The job:
Source_DB2_Connector----->Target_DB2_Connector

Thanks!

chulett · Post by **chulett** » Thu May 07, 2015 5:42 pm

So... you are looking for a way to truncate the target table only once the data is ready to be loaded so it doesn't spend so long empty? There really isn't a way to do that 'using only one parallel job' as to do that you'd need to extract your source data elsewhere, say to a dataset and then once complete, kick off the truncate and load. But that means two jobs.

ray.wurlod · Post by **ray.wurlod** » Thu May 07, 2015 6:54 pm

You can perform these "blocking operations" in server jobs, but not in single parallel jobs. But even there persistent storage is involved.

chulett · Post by **chulett** » Fri May 08, 2015 7:12 am

True. Took advantage of that fact many times.

Palermo · Post by **Palermo** » Fri May 08, 2015 9:54 am

I think that it is impossible too. But I know one way but it impacts on performance. That is to use Change Capture stage and create 4 additional DB2 connector stage(READ, INSERT, UPDATE, DELETE) and Transformer stage. I will be forced to use them. Maybe you know another way?

chulett · Post by **chulett** » Fri May 08, 2015 10:23 am

Use two jobs.

ShaneMuir · Post by **ShaneMuir** » Fri May 08, 2015 11:19 am

Once the query has executed - how long is it taking for data transfer?

If all you are after is not having the table empty for so long, is it silly to ask if your query is optimised?

Something also that I have wanted to try is, if you have an output reject link from a DB stage which feeds as a reference link into a lookup with entire as the partitioning, will a Parallel job 'wait' for the data before outputting anything? If that was to work, then you could write the data to temp table, then maybe write a routine that that calls the table truncation from the OS only once data has got to that point? Not convinced it would work though, but you could try.

EDIT:
The more I think about it the question arises: why can you not have 2 jobs?
Can you have a temporary table in your target DB? If so - run your job into the temporary table, and then once the job is finished, execute a stored procedure as either after SQL or as an ofter job routine, to copy the data from the temp table to you target table? That would get it done in one job.

Palermo · Post by **Palermo** » Fri May 08, 2015 12:21 pm

ShaneMuir wrote:Once the query has executed - how long is it taking for data transfer?

If all you are after is not having the table empty for so long, is it silly to ask if your query is optimised?

The original SQL executed for minimum 20 minutes and I optimized it and now it executes for minimum 10 minutes. But it is minimum time and varies according to workload.

ShaneMuir wrote:The more I think about it the question arises: why can you not have 2 jobs?
Can you have a temporary table in your target DB? If so - run your job into the temporary table, and then once the job is finished, execute a stored procedure as either after SQL or as an ofter job routine, to copy the data from the temp table to you target table? That would get it done in one job.

Yes, of course I know that it is easy to solve the issue by using 2 jobs or a temporary staging table but I can change only one parallel job. I've
implemented the job by using Change Capture stage and it seems that the job works fine.

Thanks to everyone for the tips.

chulett · Post by **chulett** » Fri May 08, 2015 12:27 pm

Palermo wrote:I can change only one parallel job

That seems like a very... odd... restriction. And the mechanism you were 'forced' to use (Change Capture with INSERT/UPDATE/DELETE) is pretty much the polar opposite of a simple truncate and load. But if that is working for you without 'impacts on performance' then yay.

Palermo · Post by **Palermo** » Fri May 08, 2015 12:54 pm

chulett wrote:But if that is working for you without 'impacts on performance' then yay.

The SQL query returns about 60 000 rows and the change capture stage don't impact on performance in this case.

ray.wurlod · Post by **ray.wurlod** » Fri May 08, 2015 4:01 pm

Your proof of this assertion?

Palermo · Post by **Palermo** » Sat May 09, 2015 1:42 am

truncate

change capture stage (read, insert and delete)

ray.wurlod · Post by **ray.wurlod** » Sat May 09, 2015 2:01 am

It would be interesting to see the %CPU for these operators.

Palermo · Post by **Palermo** » Sat May 09, 2015 2:43 am

You are right, %CPU increases from 1% to 28% but the loading time is not long.
running

finished

truncate

chulett · Post by **chulett** » Sat May 09, 2015 7:56 am

Ah... I can see why your performance seems to be fine with this change, you are working with what basically is an insignificant number of rows. I was assuming the number was... much larger.