Only change was the source table was moved from Dictionary managed tablespaces to locally managed tablespaces.
Details.
PX 6.1., HP-UX, Oracle 8.1.7
We have a PX job "LoadETLDMSalePX" that writes to a table ETL_DM_SALE with the "Append" option. The other options are "Index Mode = Rebuild, Compute Statistics = True , NOLOGGING = true).
After this job has completed we have a PX job "LoadDMSaleWeekPX" that reads from this table and aggregates to DM_SALE_WEEK table. This was working fine until last week when the ETL_DM_SALE table was moved from Dictionary managed tablespaces to locally managed tablespaces. That is the only change that was made. No change to the job. But now "LoadDMSaleWeekPX" reads less rows than there are in the ETL_DM_SALE table.
Is there any know issue with "locally managed tablespaces" on PX 6.1 on Oracle 8.1.7?
I have pasted below the queries generated by the second PX job "LoadDMSaleWeekPX" during the select.
/* Formatted on 2004/04/29 13:56 (Formatter Plus v4.8.0) */
SELECT SUM (blocks)
FROM SYS.dba_extents
WHERE owner = UPPER ('ETLUSER') AND segment_name = UPPER ('ETL_DM_SALE')
/* Formatted on 2004/04/29 13:55 (Formatter Plus v4.8.0) */
SELECT a.file_id, a.block_id, a.blocks, c.data_object_id
FROM SYS.dba_extents a, SYS.dba_objects c
WHERE a.owner = UPPER ('ETLUSER')
AND a.segment_name = UPPER ('ETL_DM_SALE')
AND ( c.object_type = 'TABLE'
OR c.object_type = 'TABLE PARTITION'
OR c.object_type = 'VIEW'
)
AND NVL (a.partition_name, ' ') = NVL (c.subobject_name, ' ')
AND c.owner = a.owner
AND c.object_name = a.segment_name
ORDER BY data_object_id, file_id, block_id
/* Formatted on 2004/04/29 13:55 (Formatter Plus v4.8.0) */
SELECT /*+ROWID(ETL_DM_SALE)*/
week_id, store_id, product_id, pattern_slin_id, upc_id,
product_combination_id, recmd_flag, new_product_flag,
gross_profit_bus_rule_code, inner_pack_qty,
ROUND (bill_back_us_amount / conversion_rate,
4
) bill_back_canadian_amount,
bill_back_us_amount,
ROUND (us_amount / conversion_rate, 4) canadian_amount,
ROUND (face_us_amount / conversion_rate, 4) face_canadian_amount,
face_us_amount,
ROUND (gross_profit_adj_us_amount / conversion_rate,
4
) gross_profit_adj_can_amount,
gross_profit_adj_us_amount,
ROUND (gross_profit_base_us_amount / conversion_rate,
4
) gross_profit_base_can_amount,
gross_profit_base_us_amount, markdown_unit_qty,
ROUND (post_off_us_amount / conversion_rate,
4
) post_off_canadian_amount,
post_off_us_amount, promotion_unit_qty,
ROUND (purchase_cost_us_amount / conversion_rate,
4
) purchase_cost_canadian_amount,
purchase_cost_us_amount, transaction_count, unit_qty, us_amount
FROM etl_dm_sale
WHERE (etl_dm_sale.ROWID BETWEEN 'AAAq9bAAVAAAAAaAAA' AND 'AAAq9bAAYAAAAOXH//'
)
/* Formatted on 2004/04/29 13:55 (Formatter Plus v4.8.0) */
SELECT /*+ROWID(ETL_DM_SALE)*/
week_id, store_id, product_id, pattern_slin_id, upc_id,
product_combination_id, recmd_flag, new_product_flag,
gross_profit_bus_rule_code, inner_pack_qty,
ROUND (bill_back_us_amount / conversion_rate,
4
) bill_back_canadian_amount,
bill_back_us_amount,
ROUND (us_amount / conversion_rate, 4) canadian_amount,
ROUND (face_us_amount / conversion_rate, 4) face_canadian_amount,
face_us_amount,
ROUND (gross_profit_adj_us_amount / conversion_rate,
4
) gross_profit_adj_can_amount,
gross_profit_adj_us_amount,
ROUND (gross_profit_base_us_amount / conversion_rate,
4
) gross_profit_base_can_amount,
gross_profit_base_us_amount, markdown_unit_qty,
ROUND (post_off_us_amount / conversion_rate,
4
) post_off_canadian_amount,
post_off_us_amount, promotion_unit_qty,
ROUND (purchase_cost_us_amount / conversion_rate,
4
) purchase_cost_canadian_amount,
purchase_cost_us_amount, transaction_count, unit_qty, us_amount
FROM etl_dm_sale
WHERE (etl_dm_sale.ROWID BETWEEN 'AAAq9bAAeAAAHDuAAA' AND 'AAAq9bAAhAAAL9EH//'
)
SELECT /*+ROWID(ETL_DM_SALE)*/
week_id, store_id, product_id, pattern_slin_id, upc_id,
product_combination_id, recmd_flag, new_product_flag,
gross_profit_bus_rule_code, inner_pack_qty,
ROUND (bill_back_us_amount / conversion_rate,
4
) bill_back_canadian_amount,
bill_back_us_amount,
ROUND (us_amount / conversion_rate, 4) canadian_amount,
ROUND (face_us_amount / conversion_rate, 4) face_canadian_amount,
face_us_amount,
ROUND (gross_profit_adj_us_amount / conversion_rate,
4
) gross_profit_adj_can_amount,
gross_profit_adj_us_amount,
ROUND (gross_profit_base_us_amount / conversion_rate,
4
) gross_profit_base_can_amount,
gross_profit_base_us_amount, markdown_unit_qty,
ROUND (post_off_us_amount / conversion_rate,
4
) post_off_canadian_amount,
post_off_us_amount, promotion_unit_qty,
ROUND (purchase_cost_us_amount / conversion_rate,
4
) purchase_cost_canadian_amount,
purchase_cost_us_amount, transaction_count, unit_qty, us_amount
FROM etl_dm_sale
WHERE (etl_dm_sale.ROWID BETWEEN 'AAAq9bAAeAAACh7AAA' AND 'AAAq9bAAeAAAHDtH//'
)
/* Formatted on 2004/04/29 13:55 (Formatter Plus v4.8.0) */
SELECT /*+ROWID(ETL_DM_SALE)*/
week_id, store_id, product_id, pattern_slin_id, upc_id,
product_combination_id, recmd_flag, new_product_flag,
gross_profit_bus_rule_code, inner_pack_qty,
ROUND (bill_back_us_amount / conversion_rate,
4
) bill_back_canadian_amount,
bill_back_us_amount,
ROUND (us_amount / conversion_rate, 4) canadian_amount,
ROUND (face_us_amount / conversion_rate, 4) face_canadian_amount,
face_us_amount,
ROUND (gross_profit_adj_us_amount / conversion_rate,
4
) gross_profit_adj_can_amount,
gross_profit_adj_us_amount,
ROUND (gross_profit_base_us_amount / conversion_rate,
4
) gross_profit_base_can_amount,
gross_profit_base_us_amount, markdown_unit_qty,
ROUND (post_off_us_amount / conversion_rate,
4
) post_off_canadian_amount,
post_off_us_amount, promotion_unit_qty,
ROUND (purchase_cost_us_amount / conversion_rate,
4
) purchase_cost_canadian_amount,
purchase_cost_us_amount, transaction_count, unit_qty, us_amount
FROM etl_dm_sale
WHERE (etl_dm_sale.ROWID BETWEEN 'AAAq9bAAYAAAAOYAAA' AND 'AAAq9bAAeAAACh6H//'
)
PX job reading fewer rows from a table than present.
Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 19
- Joined: Fri Apr 02, 2004 10:13 am
Return to “IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)”
Jump to
- Moderators' Choice
- ↳ Editor's BLOG Corner
- ↳ Ask the Experts! - Dads and Grads
- ↳ DSXchange Testimonials
- ↳ Cognos (IBM BI)
- FAQs
- ↳ FAQs
- ↳ FAQ Discussion
- DataStage
- ↳ General
- ↳ IBM<sup>®</sup> Infosphere DataStage Server Edition
- ↳ IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)
- ↳ Archive of DataStage Users@Oliver.com
- IBM<sup>®</sup>Infosphere Products<sup></sup>
- ↳ Business Glossary
- Suggestions
- ↳ Site/Forum
- ↳ Enhancement Wish List
- Consulting
- ↳ Talent
- ↳ Looking for Talent
- Support
- ↳ Parameter Manager
- ↳ Compile All Plus
- Usergroup Forums
- ↳ Usergroup Central Forum
- ↳ Heartland Usergroup Forum
- The Written Word
- ↳ Articles, White Papers and Tips and Tricks
- ↳ Product Documentation
- Third Party Applications
- ↳ Third Party Applications
- Product Derivatives
- ↳ Functions
- ↳ Routines
- ↳ Jobs
- ↳ Logs
- Tools
- ↳ Tools Forum
- Category
- ↳ Infosphere Master Data Management
- ↳ Data Quality Best Practices
- ↳ IBM QualityStage
- ↳ Information Analyzer (formerly ProfileStage)
- ↳ IBM<sup>®</sup> SOA Editions (Formerly RTI Services)
- ↳ IBM<sup>®</sup> DataStage TX
- ↳ BI
- ↳ Data Integration