DSXchange

Hi All,

Incase of low number of records, Jobs are aborting while writing zero records thro' one of the node.

Present job is writing 21 records for 2 nodes, its working fine if rows are divided as 10 or 11 between the nodes.

But its aborting quite often in case rows are divided as 21 and 0 between the nodes...giving following datastage Warning....

SG_CICW_DISTRICTS,0: Export complete; 0 records exported successfully, 0 rejected.
SG_CICW_DISTRICTS,0: SQL*Loader-500: Unable to open file (\\.\pipe\ora.11552.557000.fifo.0)
SG_CICW_DISTRICTS,0: SQL*Loader-554: error opening file
SG_CICW_DISTRICTS,0: SQL*Loader-509: System error: The operation completed successfully.
SG_CICW_DISTRICTS,0: SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SG_CICW_DISTRICTS,1: Export complete; 21 records exported successfully, 0 rejected.
SG_CICW_DISTRICTS,0: The call to sqlldr failed; the return code = 256;
please see the loader logfile: E:/IBM/InformationServer/Server/Scratch/ora.11552.557000.0.log for details.
SG_CICW_DISTRICTS,0: Log file contents:
SG_CICW_DISTRICTS,0: The runLocally() of the operator failed.
SG_CICW_DISTRICTS,0: Input 0 consumed 1 records.
SG_CICW_DISTRICTS,0: Output 0 produced 0 records.
SG_CICW_DISTRICTS,0: Operator terminated abnormally: runLocally did not return APT_StatusOk
SG_CICW_DISTRICTS,1: Load completed - logical record count 21.
main_program: Step execution finished with status = FAILED.
main_program: Startup time, 0:17; production run time, 0:04.
Job SG_ELL_DIM_CICW_DISTRICTS aborted.
(SG_Ellipse_Dimension_Tables) <- SG_ELL_DIM_CICW_DISTRICTS: Job under control finished.

Also posting the Sql Loader log..


SQL*Loader: Release 10.2.0.1.0 - Production on Tue May 20 09:26:32 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: ora.11552.557000.0.ctl
Data File: \\.\pipe\ora.11552.557000.fifo.0
File processing option string: "FIX 120"
Bad File: ora.11552.557000.0.log.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct - with parallel option.

Table CICW_DISTRICTS, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DISTRICT_SK_ID 1:20 20 PACKED DECIMAL (38, 0)
DISTRICT_CODE 21:24 4 CHARACTER
DISTRICT_NAME 25:64 40 CHARACTER
CURRENT_IND 65:68 4 INTEGER
EFFECTIVE_TIMESTAMP 69:94 26 DATETIME YYYY-MM-DD HH24:MI:SS.FF6
EXPIRATION_TIMESTAMP 95:120 26 DATETIME YYYY-MM-DD HH24:MI:SS.FF6

SQL*Loader-500: Unable to open file (\\.\pipe\ora.11552.557000.fifo.0)
SQL*Loader-554: error opening file
SQL*Loader-509: System error: The operation completed successfully.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Table CICW_DISTRICTS:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 0
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 0
Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Tue May 20 09:26:32 2008
Run ended on Tue May 20 09:26:35 2008

Elapsed time was: 00:00:02.43
CPU time was: 00:00:00.15


Sql Loader Control File log...

OPTIONS(DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=YES)

LOAD DATA INFILE '\\.\pipe\ora.11552.557000.fifo.0' "FIX 120"
APPEND INTO TABLE CICW_DISTRICTS
(
DISTRICT_SK_ID POSITION(1:20) DECIMAL(38,0) ,
DISTRICT_CODE POSITION(21:24) ,
DISTRICT_NAME POSITION(25:64) ,
CURRENT_IND POSITION(65:68) INTEGER ,
EFFECTIVE_TIMESTAMP POSITION(69:94) timestamp "YYYY-MM-DD HH24:MI:SS.FF6" ,
EXPIRATION_TIMESTAMP POSITION(95:120) timestamp "YYYY-MM-DD HH24:MI:SS.FF6"

)

That has to be a bug. Advise your support provider.

A patch is available to fix this.

PATCH REPORT FOR ECASE: 130688
PRODUCT: Information Server
VERSION: 8.0.1.1 (FixPack1)
PATCH NAME: patch_e130688
COMPONENT: PXEngine

PROBLEM REPORTED/Implementation Details:
PX job run with Oracle EE stage throws error.

CAUSE OF PROBLEM:

FIX SUMMARY:
The fix introduces an environment variable APT_EXPORT_DELAY_ON_ZERO_RECS.

USER INTERFACE FIX:

ECASE 130688
============
APT_EXPORT_DELAY_ON_ZERO_RECS should be set.
The customer should set this variable in jobs where they expect to have 0 record partitions.
The value of this variable indicated the seconds to delay waiting for the sqlldr to open the fifo for reading.
A value between 5 and 10 should be adequate