TRUNCATE not occurring

[cccschip]

Somewhat new to Data Manager...but I think we're seeing a problem...

We have a standard (as far as we can tell) mapping that reads data from a source query and writes it to a target table.  The Load mode for the target table is set to TRUNCATE.

Before running the jobstream, the target table has 3.7M records in it and the source query returns no records.

After running the jobstream, the target table still has 3.7M records in it!

The log output is:

Data Manager v8.3.85.4 -- Copyright (c) 2007 Cognos Incorporated
Licensed to Colorado Community College System/Denver,CO(1)
databuild -- start run (03-Apr-2012 10:57:29)
Run context:
JobStream 'STUDENT_MARTS'
   JobStream 'ENROLLMENT_FACT'
      Build 'ENROLLMENT_FEX1'
[PROGRESS   - 10:57:32] Build 'ENROLLMENT_FEX1' - run id 216, audit id 83604
[PROGRESS   - 10:57:32] Validating Build Specification
[PROGRESS   - 10:57:33] Checking Fact Data existence
[DETAIL     - 10:57:33] No Fact Data found
[PROGRESS   - 10:57:33] Processing Fact Data
[DETAIL     - 10:57:33] 0 input (in all), 0 accepted, 0 delivered
[PROGRESS   - 10:57:33] Delivery 'ENROLLMENT_FEX1': no rows delivered
[DETAIL     - 10:57:33] Data Stream row limits: sample rate 1, max rows 0
[DETAIL     - 10:57:33] Data Source 'SFRSTCR': 0 physical (0 logical) rows read, 0 used
[PROGRESS   - 10:57:33] Acquisition: 0 accepted, 0 rejected
[PROGRESS   - 10:57:33] Delivery   : 0 direct, 0 summary/merge, 0 total
[PROGRESS   - 10:57:33] Done - 0 00:00:04 elapsed
databuild -- completed (03-Apr-2012 10:57:33)

Is it the case that, because there is no source data, the target table processing (including the TRUNCATE) is skipped altogether?

We expected the TRUNCATE to clean out the old records from the target table.

[MFGF]

This is the log from the jobstream, and only shows the major steps being executed. I assume that the piece you describe is a fact build, which is being called as one of the steps of the jobstream?

We really need to see the log file from this fact build to ascertain what it is doing - ideally with the build having the Detail, SQL and Executed SQL options enabled in the logging tab of the build properties before it is run.

Edit. I am a muppet! This IS the fact build log! Can you run again with the above logging options enabled?

Sent from my iPad using Tapatalk HD

[cccschip]

Actually, I did run with SQL and ExecutedSQL...which produced the expected output before the "No Fact Data found" message...a long SQL statement...which indeed returns no data.  I carved it out only because it made for a very lengthy post.

To restate, the SQL is correct and correctly returns no rows.  So the "No Fact Data found" output is correct.

But we'd expect the target table processing to still occur after that...with the hope that the TRUNCATE would be executed.  There were no other messages in the output, other than those shown, after the "No Fact Data found" message.

I'm thinking the SQL syntax would be of no help in determining why the target table processing appears to have been skipped.  Let me know if I'm wrong there and I'll try to recreate.

[MFGF]

SQL logging will show not only the SQL executed to collect the source data, but also the SQL executed to address the target table (if any is being attempted). I would expect to see a Truncate statement or at the very least a delete statement if the build was attempting to truncate.  Assuming the target table processing is skipped altogether in the event of a null source result set, your best bet will be to precede the fact build node in your jobstream with a SQL node, which issues the relevant Truncate statement for the table. Alternatively, if you want to run the build standalone outside of the jobstream and still have the truncate issued, define a variable in the properties of the fact build with an initial expression utilising a sql() function which issues the Truncate statement to the target table.

Regards,

MF.

[cccschip]

There was no SQL output for the target table...which is what led us to believe its processing was skipped in the first place.

MFGF, I think your suggestions are reasonable workarounds, but we are first trying to determine if this is expected behavior.  (I hate to workaround bugs that should, instead, be solved with a patch!)

Does anyone have insight as to whether this is working as designed or not?

Thanks.

[MFGF]

I would guess you will need to raise a PMR with IBM support to get a definitive answer to that question. There is no mention of it in the user guide from the searching I have done.

Regards,

MF.

[cccschip]

Yeah, I haven't found anything in the doc either.  Thanks for being a sounding board.  I'm off to IBM-land.

[cccschip]

IBM helped me understand the problem here...

The results depend on the type of target table delivery mechanism utilized:
•   When using "Relational Table Delivery" (with Refresh Type = TRUNCATE), the truncate is done.
•   When using "ORACLE SQL*Loader Delivery" (with Load Type = TRUNCATE), the truncate is not done.

This is because SQL*Loader embeds the TRUNCATE in the command that populates the target table which is, in turn, in the control (.ctl) file that it constructs.  But this control file is only constructed (and executed) if the source returns data.

The generic Relational Table method is SQL based and issues an explicit TRUNCATE...whether the source returns data or not.

Bottom line: Use of the SQL*Loader approach does not empty target tables (even if TRUNCATE is specified) when the source is empty.  If you have scenarios in which the source may, legitimately, be empty...you'll need to resort to the kind of work-around that MFGF suggests above.  Else downstream processing will find and use old data in the target table for this step.