Preserving the spaces while bulk load(OracleEnterprisStage)

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

Post Reply
panchusrao2656
Charter Member
Charter Member
Posts: 64
Joined: Sat Sep 17, 2005 10:42 am

Preserving the spaces while bulk load(OracleEnterprisStage)

Post by panchusrao2656 »

I am trying load data with load options( Write Method : Load, Write Mode: Truncate) using Oracle Enterprise stage in a parallel job.

Field Name CUST_NAME Varchar2(20):

Values in the source dataset : 1."Randy" 2." "(one space)

Case1:
$APT_ORACLE_PRESERVE_BLANKS = True, data in the target is having Randy and 15 more spaces("Randy ") second one with 20 spaces. Instead of preserving it is padding spaces.

Case2:
$APT_ORACLE_PRESERVE_BLANKS = False
target data is like 1."Randy" 2.NULL (Oracle Null)
It simply trims the spaces.

But i would like my Data to be 1."Randy" 2." "

How to achive this, is there any other option that preserve the spaces( i cannot use Upsert as i need to Truncate the table each time).
Top
kris007
Charter Member
Charter Member
Posts: 1102
Joined: Tue Jan 24, 2006 5:38 pm
Location: Riverside, RI

Post by kris007 »

I can't recollect the environment variable we used to resolve it from the top of my head but I know there is one. Wanted to share this information so that you know there is an environment variable. I'll try to get back once I get hold of that.
Kris

Where's the "Any" key?-Homer Simpson
Top
panchusrao2656
Charter Member
Charter Member
Posts: 64
Joined: Sat Sep 17, 2005 10:42 am

Post by panchusrao2656 »

Hi Kris007,

Do you know how the control file gets created(ora.24480.24940.1.ctl). I noticed that control file is getting created only once for a particular job and log file is getting created each time we execute the job with an extension ".log".

I donot have any clue how the control file is getting created.

Do you have any idea what the following variable is for $APT_ORACLE_LOAD_OPTIONS =
Top
kris007
Charter Member
Charter Member
Posts: 1102
Joined: Tue Jan 24, 2006 5:38 pm
Location: Riverside, RI

Post by kris007 »

Code: Select all

APT_STRING_PAD_CHAR.
That is the environment variable I used to get rid of spaces while using Load stage. Set it to ASCII null value. You should be good.
Kris

Where's the "Any" key?-Homer Simpson
Top
panchusrao2656
Charter Member
Charter Member
Posts: 64
Joined: Sat Sep 17, 2005 10:42 am

Post by panchusrao2656 »

I believe this variable APT_STRING_PAD_CHAR is to pad default characters when a field is CHAR right.

Can anyone explore something about the control files and log files.

As per my understanding, while bulk loading DataStage works as follows.

DataStage will prepare a control file, a datafile and the log file and then the sqlloader will load the data with the help of those files.

Control file will be something like :

PRESERVE BLANKS
LOAD DATA INFILE 'ora.24480.24940.fifo.1' "FIX 503"
APPEND INTO TABLE WS_FDW_RCPT_DIM
(
INSERT_PRCS_AUD_ID POSITION(1:11) DECIMAL(20,0) NULLIF INSERT_PRCS_AUD_ID = X'0000000000000000000000',
LST_UPDT_PRCS_AUD_ID POSITION(12:22) DECIMAL(20,0) NULLIF LST_UPDT_PRCS_AUD_ID = X'0000000000000000000000',
SCD_II POSITION(23:23) NULLIF SCD_II = BLANKS,
RCPT_ID POSITION(24:34) DECIMAL(20,0) ,
SRC_INVTRY_LOCTN_ID POSITION(35:45) DECIMAL(20,0) ,
DEST_INVTRY_LOCTN_ID POSITION(46:56) DECIMAL(20,0) ,
BIN_ID POSITION(57:67) DECIMAL(20,0) ,
PO_LN_ID POSITION(68:78) DECIMAL(20,0) ,
REQSTN_LN_ID POSITION(79:89) DECIMAL(20,0) ,
VNDR_ID POSITION(90:100) DECIMAL(20,0) ,
VNDR_LOCTN_ID POSITION(101:111) DECIMAL(20,0) ,
SC_BUYER_ID POSITION(112:122) DECIMAL(20,0) ,
SHPMNT_NBR POSITION(123:142) NULLIF SHPMNT_NBR = BLANKS,
ORD_DOC_TYP_CD POSITION(143:162) NULLIF ORD_DOC_TYP_CD = BLANKS,
ORD_DOC_TYP_DESCR POSITION(163:192) NULLIF ORD_DOC_TYP_DESCR = BLANKS,
TRANS_SYS_ORIG_CD POSITION(193:212) NULLIF TRANS_SYS_ORIG_CD = BLANKS,
COMPONENT_SEQ_NBR POSITION(213:222) DECIMAL(19,0) NULLIF COMPONENT_SEQ_NBR = X'00000000000000000000',
RCPT_NBR POSITION(223:242) NULLIF RCPT_NBR = BLANKS,
RCPT_LN_NBR POSITION(243:252) DECIMAL(19,0) NULLIF RCPT_LN_NBR = X'00000000000000000000',
RCPT_RSN_CD POSITION(253:272) NULLIF RCPT_RSN_CD = BLANKS,
RCPT_RSN_DESCR POSITION(273:302) NULLIF RCPT_RSN_DESCR = BLANKS,
RCPT_CMMT_TXT POSITION(303:342) NULLIF RCPT_CMMT_TXT = BLANKS,
RCPT_OPRTR_CD POSITION(343:362) NULLIF RCPT_OPRTR_CD = BLANKS,
DRPSHP_IND POSITION(363:363) NULLIF DRPSHP_IND = BLANKS,
ITEM_TYP_CD POSITION(364:383) NULLIF ITEM_TYP_CD = BLANKS,
ITEM_TYP_DESCR POSITION(384:413) NULLIF ITEM_TYP_DESCR = BLANKS,
START_ROW_DT POSITION(414:432) DATE "YYYY/MM/DD HH24:MI:SS" NULLIF START_ROW_DT = BLANKS,
END_ROW_DT POSITION(433:451) DATE "YYYY/MM/DD HH24:MI:SS" NULLIF END_ROW_DT = BLANKS,
RCPT_DT POSITION(452:470) DATE "YYYY/MM/DD HH24:MI:SS" NULLIF RCPT_DT = BLANKS,
VNDR_ITEM_NBR POSITION(471:502) NULLIF VNDR_ITEM_NBR = BLANKS

)

Log File :
SQL*Loader: Release 10.1.0.2.0 - Production on Wed Dec 20 16:35:16 2006

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

Control File: ora.34398.332121.1.ctl
Data File: ora.34398.332121.fifo.1
File processing option string: "FIX 503"
Bad File: ora.34398.332121.1.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 WS_FDW_RCPT_DIM, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
INSERT_PRCS_AUD_ID 1:11 11 PACKED DECIMAL (20, 0)
NULL if INSERT_PRCS_AUD_ID = 0X0000000000000000000000(character '')
LST_UPDT_PRCS_AUD_ID 12:22 11 PACKED DECIMAL (20, 0)
NULL if LST_UPDT_PRCS_AUD_ID = 0X0000000000000000000000(character '')
SCD_II 23:23 1 CHARACTER
NULL if SCD_II = BLANKS
RCPT_ID 24:34 11 PACKED DECIMAL (20, 0)
SRC_INVTRY_LOCTN_ID 35:45 11 PACKED DECIMAL (20, 0)
DEST_INVTRY_LOCTN_ID 46:56 11 PACKED DECIMAL (20, 0)
BIN_ID 57:67 11 PACKED DECIMAL (20, 0)
PO_LN_ID 68:78 11 PACKED DECIMAL (20, 0)
REQSTN_LN_ID 79:89 11 PACKED DECIMAL (20, 0)
VNDR_ID 90:100 11 PACKED DECIMAL (20, 0)
VNDR_LOCTN_ID 101:111 11 PACKED DECIMAL (20, 0)
SC_BUYER_ID 112:122 11 PACKED DECIMAL (20, 0)
SHPMNT_NBR 123:142 20 CHARACTER
NULL if SHPMNT_NBR = BLANKS
ORD_DOC_TYP_CD 143:162 20 CHARACTER
NULL if ORD_DOC_TYP_CD = BLANKS
ORD_DOC_TYP_DESCR 163:192 30 CHARACTER
NULL if ORD_DOC_TYP_DESCR = BLANKS
TRANS_SYS_ORIG_CD 193:212 20 CHARACTER
NULL if TRANS_SYS_ORIG_CD = BLANKS
COMPONENT_SEQ_NBR 213:222 10 PACKED DECIMAL (19, 0)
NULL if COMPONENT_SEQ_NBR = 0X00000000000000000000(character '')
RCPT_NBR 223:242 20 CHARACTER
NULL if RCPT_NBR = BLANKS
RCPT_LN_NBR 243:252 10 PACKED DECIMAL (19, 0)
NULL if RCPT_LN_NBR = 0X00000000000000000000(character '')
RCPT_RSN_CD 253:272 20 CHARACTER
NULL if RCPT_RSN_CD = BLANKS
RCPT_RSN_DESCR 273:302 30 CHARACTER
NULL if RCPT_RSN_DESCR = BLANKS
RCPT_CMMT_TXT 303:342 40 CHARACTER
NULL if RCPT_CMMT_TXT = BLANKS
RCPT_OPRTR_CD 343:362 20 CHARACTER
NULL if RCPT_OPRTR_CD = BLANKS
DRPSHP_IND 363:363 1 CHARACTER
NULL if DRPSHP_IND = BLANKS
ITEM_TYP_CD 364:383 20 CHARACTER
NULL if ITEM_TYP_CD = BLANKS
ITEM_TYP_DESCR 384:413 30 CHARACTER
NULL if ITEM_TYP_DESCR = BLANKS
START_ROW_DT 414:432 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if START_ROW_DT = BLANKS
END_ROW_DT 433:451 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if END_ROW_DT = BLANKS
RCPT_DT 452:470 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if RCPT_DT = BLANKS
VNDR_ITEM_NBR 471:502 32 CHARACTER
NULL if VNDR_ITEM_NBR = BLANKS


Table WS_FDW_RCPT_DIM:
15064 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.

Date cache:
Max Size: 1000
Entries : 68
Hits : 45124
Misses : 0

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: 15064
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 8
Total stream buffers loaded by SQL*Loader load thread: 7

Run began on Wed Dec 20 16:35:16 2006
Run ended on Wed Dec 20 16:35:17 2006

Elapsed time was: 00:00:01.07
CPU time was: 00:00:00.31
Top
Yuan_Edward
Participant
Posts: 73
Joined: Tue May 10, 2005 6:21 pm
Location: Sydney

Post by Yuan_Edward »

From what I can see, "NULLIF VNDR_ITEM_NBR = BLANKS" this kind of transformation will treat blank columns as NULL. This can explain why the second example columns is null.

"PRESERVE BLANKS" only preserves the blanks which exist in the input columns, I am quite sure it will not pad any blanks. I would suggest that you check the generated data file for your case the first example column.
panchusrao2656 wrote:I believe this variable APT_STRING_PAD_CHAR is to pad default characters when a field is CHAR right.

Can anyone explore something about the control files and log files.

As per my understanding, while bulk loading DataStage works as follows.

DataStage will prepare a control file, a datafile and the log file and then the sqlloader will load the data with the help of those files.

Control file will be something like :

PRESERVE BLANKS
LOAD DATA INFILE 'ora.24480.24940.fifo.1' "FIX 503"
APPEND INTO TABLE WS_FDW_RCPT_DIM
(
INSERT_PRCS_AUD_ID POSITION(1:11) DECIMAL(20,0) NULLIF INSERT_PRCS_AUD_ID = X'0000000000000000000000',
LST_UPDT_PRCS_AUD_ID POSITION(12:22) DECIMAL(20,0) NULLIF LST_UPDT_PRCS_AUD_ID = X'0000000000000000000000',
SCD_II POSITION(23:23) NULLIF SCD_II = BLANKS,
RCPT_ID POSITION(24:34) DECIMAL(20,0) ,
SRC_INVTRY_LOCTN_ID POSITION(35:45) DECIMAL(20,0) ,
DEST_INVTRY_LOCTN_ID POSITION(46:56) DECIMAL(20,0) ,
BIN_ID POSITION(57:67) DECIMAL(20,0) ,
PO_LN_ID POSITION(68:78) DECIMAL(20,0) ,
REQSTN_LN_ID POSITION(79:89) DECIMAL(20,0) ,
VNDR_ID POSITION(90:100) DECIMAL(20,0) ,
VNDR_LOCTN_ID POSITION(101:111) DECIMAL(20,0) ,
SC_BUYER_ID POSITION(112:122) DECIMAL(20,0) ,
SHPMNT_NBR POSITION(123:142) NULLIF SHPMNT_NBR = BLANKS,
ORD_DOC_TYP_CD POSITION(143:162) NULLIF ORD_DOC_TYP_CD = BLANKS,
ORD_DOC_TYP_DESCR POSITION(163:192) NULLIF ORD_DOC_TYP_DESCR = BLANKS,
TRANS_SYS_ORIG_CD POSITION(193:212) NULLIF TRANS_SYS_ORIG_CD = BLANKS,
COMPONENT_SEQ_NBR POSITION(213:222) DECIMAL(19,0) NULLIF COMPONENT_SEQ_NBR = X'00000000000000000000',
RCPT_NBR POSITION(223:242) NULLIF RCPT_NBR = BLANKS,
RCPT_LN_NBR POSITION(243:252) DECIMAL(19,0) NULLIF RCPT_LN_NBR = X'00000000000000000000',
RCPT_RSN_CD POSITION(253:272) NULLIF RCPT_RSN_CD = BLANKS,
RCPT_RSN_DESCR POSITION(273:302) NULLIF RCPT_RSN_DESCR = BLANKS,
RCPT_CMMT_TXT POSITION(303:342) NULLIF RCPT_CMMT_TXT = BLANKS,
RCPT_OPRTR_CD POSITION(343:362) NULLIF RCPT_OPRTR_CD = BLANKS,
DRPSHP_IND POSITION(363:363) NULLIF DRPSHP_IND = BLANKS,
ITEM_TYP_CD POSITION(364:383) NULLIF ITEM_TYP_CD = BLANKS,
ITEM_TYP_DESCR POSITION(384:413) NULLIF ITEM_TYP_DESCR = BLANKS,
START_ROW_DT POSITION(414:432) DATE "YYYY/MM/DD HH24:MI:SS" NULLIF START_ROW_DT = BLANKS,
END_ROW_DT POSITION(433:451) DATE "YYYY/MM/DD HH24:MI:SS" NULLIF END_ROW_DT = BLANKS,
RCPT_DT POSITION(452:470) DATE "YYYY/MM/DD HH24:MI:SS" NULLIF RCPT_DT = BLANKS,
VNDR_ITEM_NBR POSITION(471:502) NULLIF VNDR_ITEM_NBR = BLANKS

)

Log File :
SQL*Loader: Release 10.1.0.2.0 - Production on Wed Dec 20 16:35:16 2006

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

Control File: ora.34398.332121.1.ctl
Data File: ora.34398.332121.fifo.1
File processing option string: "FIX 503"
Bad File: ora.34398.332121.1.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 WS_FDW_RCPT_DIM, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
INSERT_PRCS_AUD_ID 1:11 11 PACKED DECIMAL (20, 0)
NULL if INSERT_PRCS_AUD_ID = 0X0000000000000000000000(character '')
LST_UPDT_PRCS_AUD_ID 12:22 11 PACKED DECIMAL (20, 0)
NULL if LST_UPDT_PRCS_AUD_ID = 0X0000000000000000000000(character '')
SCD_II 23:23 1 CHARACTER
NULL if SCD_II = BLANKS
RCPT_ID 24:34 11 PACKED DECIMAL (20, 0)
SRC_INVTRY_LOCTN_ID 35:45 11 PACKED DECIMAL (20, 0)
DEST_INVTRY_LOCTN_ID 46:56 11 PACKED DECIMAL (20, 0)
BIN_ID 57:67 11 PACKED DECIMAL (20, 0)
PO_LN_ID 68:78 11 PACKED DECIMAL (20, 0)
REQSTN_LN_ID 79:89 11 PACKED DECIMAL (20, 0)
VNDR_ID 90:100 11 PACKED DECIMAL (20, 0)
VNDR_LOCTN_ID 101:111 11 PACKED DECIMAL (20, 0)
SC_BUYER_ID 112:122 11 PACKED DECIMAL (20, 0)
SHPMNT_NBR 123:142 20 CHARACTER
NULL if SHPMNT_NBR = BLANKS
ORD_DOC_TYP_CD 143:162 20 CHARACTER
NULL if ORD_DOC_TYP_CD = BLANKS
ORD_DOC_TYP_DESCR 163:192 30 CHARACTER
NULL if ORD_DOC_TYP_DESCR = BLANKS
TRANS_SYS_ORIG_CD 193:212 20 CHARACTER
NULL if TRANS_SYS_ORIG_CD = BLANKS
COMPONENT_SEQ_NBR 213:222 10 PACKED DECIMAL (19, 0)
NULL if COMPONENT_SEQ_NBR = 0X00000000000000000000(character '')
RCPT_NBR 223:242 20 CHARACTER
NULL if RCPT_NBR = BLANKS
RCPT_LN_NBR 243:252 10 PACKED DECIMAL (19, 0)
NULL if RCPT_LN_NBR = 0X00000000000000000000(character '')
RCPT_RSN_CD 253:272 20 CHARACTER
NULL if RCPT_RSN_CD = BLANKS
RCPT_RSN_DESCR 273:302 30 CHARACTER
NULL if RCPT_RSN_DESCR = BLANKS
RCPT_CMMT_TXT 303:342 40 CHARACTER
NULL if RCPT_CMMT_TXT = BLANKS
RCPT_OPRTR_CD 343:362 20 CHARACTER
NULL if RCPT_OPRTR_CD = BLANKS
DRPSHP_IND 363:363 1 CHARACTER
NULL if DRPSHP_IND = BLANKS
ITEM_TYP_CD 364:383 20 CHARACTER
NULL if ITEM_TYP_CD = BLANKS
ITEM_TYP_DESCR 384:413 30 CHARACTER
NULL if ITEM_TYP_DESCR = BLANKS
START_ROW_DT 414:432 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if START_ROW_DT = BLANKS
END_ROW_DT 433:451 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if END_ROW_DT = BLANKS
RCPT_DT 452:470 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if RCPT_DT = BLANKS
VNDR_ITEM_NBR 471:502 32 CHARACTER
NULL if VNDR_ITEM_NBR = BLANKS


Table WS_FDW_RCPT_DIM:
15064 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.

Date cache:
Max Size: 1000
Entries : 68
Hits : 45124
Misses : 0

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: 15064
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 8
Total stream buffers loaded by SQL*Loader load thread: 7

Run began on Wed Dec 20 16:35:16 2006
Run ended on Wed Dec 20 16:35:17 2006

Elapsed time was: 00:00:01.07
CPU time was: 00:00:00.31
Edward Yuan
Top
panchusrao2656
Charter Member
Charter Member
Posts: 64
Joined: Sat Sep 17, 2005 10:42 am

Post by panchusrao2656 »

When i set $APT_ORA_PRESERVE_BLANKS to true, it is padding characters eventhough the target column is varchar. Data file that is getting created ora.*.fifo.o.* is getting deleted once the file has been loaded.

can any of the DataStage experts or Premium posters put some light on this issue.

The issue is as follows.

Source Data : 1."Randy" 2." "(one space)
Target metadata 1.Varchar2(20), 2.Varchar2(20)

Case1: $APT_ORA_PRESERVE_BLANKS True

Target Data : 1."Randy "(padding spaces)
2.NULL (Oracle Null)

Case2: $APT_ORA_PRESERVE_BLANKS False

Target Data : 1."Randy"(padding spaces)
2.NULL (Oracle Null)

Desired Data :
Target Data : 1."Randy"
2." "(one space)

As we need to load the data by truncating old data everytime, please let us know whether there is any option like Pre_sql in server jobs that we can truncate the table and then upserts the data.

There are open command & close commands only for bulk load not for upserts.

The simple option we have is Truncate the table in the prior job and upsert the data in the next job, we would like to avoid this.
Top
kris007
Charter Member
Charter Member
Posts: 1102
Joined: Tue Jan 24, 2006 5:38 pm
Location: Riverside, RI

Post by kris007 »

From what I can recollect, it took care of the varchar's too. You might want to give it a shot.
Kris

Where's the "Any" key?-Homer Simpson
Top
Yuan_Edward
Participant
Posts: 73
Joined: Tue May 10, 2005 6:21 pm
Location: Sydney

Post by Yuan_Edward »

POSITION(1:11)

I just realized this is a control file for fixed-width format...I believe APT_STRING_PAD_CHAR is used when the Enterprise Stage generates the data file. That's why additional spaces appears in both fields if PRESERVE BLANKS is set to true.

Set APT_STRING_PAD_CHAR to NULL (0x00) and PRESERVE BLANKS to True will do the job.
panchusrao2656 wrote:I believe this variable APT_STRING_PAD_CHAR is to pad default characters when a field is CHAR right.

Can anyone explore something about the control files and log files.

As per my understanding, while bulk loading DataStage works as follows.

DataStage will prepare a control file, a datafile and the log file and then the sqlloader will load the data with the help of those files.

Control file will be something like :

PRESERVE BLANKS
LOAD DATA INFILE 'ora.24480.24940.fifo.1' "FIX 503"
APPEND INTO TABLE WS_FDW_RCPT_DIM
(
INSERT_PRCS_AUD_ID POSITION(1:11) DECIMAL(20,0) NULLIF INSERT_PRCS_AUD_ID = X'0000000000000000000000',
LST_UPDT_PRCS_AUD_ID POSITION(12:22) DECIMAL(20,0) NULLIF LST_UPDT_PRCS_AUD_ID = X'0000000000000000000000',
SCD_II POSITION(23:23) NULLIF SCD_II = BLANKS,
RCPT_ID POSITION(24:34) DECIMAL(20,0) ,
SRC_INVTRY_LOCTN_ID POSITION(35:45) DECIMAL(20,0) ,
DEST_INVTRY_LOCTN_ID POSITION(46:56) DECIMAL(20,0) ,
BIN_ID POSITION(57:67) DECIMAL(20,0) ,
PO_LN_ID POSITION(68:78) DECIMAL(20,0) ,
REQSTN_LN_ID POSITION(79:89) DECIMAL(20,0) ,
VNDR_ID POSITION(90:100) DECIMAL(20,0) ,
VNDR_LOCTN_ID POSITION(101:111) DECIMAL(20,0) ,
SC_BUYER_ID POSITION(112:122) DECIMAL(20,0) ,
SHPMNT_NBR POSITION(123:142) NULLIF SHPMNT_NBR = BLANKS,
ORD_DOC_TYP_CD POSITION(143:162) NULLIF ORD_DOC_TYP_CD = BLANKS,
ORD_DOC_TYP_DESCR POSITION(163:192) NULLIF ORD_DOC_TYP_DESCR = BLANKS,
TRANS_SYS_ORIG_CD POSITION(193:212) NULLIF TRANS_SYS_ORIG_CD = BLANKS,
COMPONENT_SEQ_NBR POSITION(213:222) DECIMAL(19,0) NULLIF COMPONENT_SEQ_NBR = X'00000000000000000000',
RCPT_NBR POSITION(223:242) NULLIF RCPT_NBR = BLANKS,
RCPT_LN_NBR POSITION(243:252) DECIMAL(19,0) NULLIF RCPT_LN_NBR = X'00000000000000000000',
RCPT_RSN_CD POSITION(253:272) NULLIF RCPT_RSN_CD = BLANKS,
RCPT_RSN_DESCR POSITION(273:302) NULLIF RCPT_RSN_DESCR = BLANKS,
RCPT_CMMT_TXT POSITION(303:342) NULLIF RCPT_CMMT_TXT = BLANKS,
RCPT_OPRTR_CD POSITION(343:362) NULLIF RCPT_OPRTR_CD = BLANKS,
DRPSHP_IND POSITION(363:363) NULLIF DRPSHP_IND = BLANKS,
ITEM_TYP_CD POSITION(364:383) NULLIF ITEM_TYP_CD = BLANKS,
ITEM_TYP_DESCR POSITION(384:413) NULLIF ITEM_TYP_DESCR = BLANKS,
START_ROW_DT POSITION(414:432) DATE "YYYY/MM/DD HH24:MI:SS" NULLIF START_ROW_DT = BLANKS,
END_ROW_DT POSITION(433:451) DATE "YYYY/MM/DD HH24:MI:SS" NULLIF END_ROW_DT = BLANKS,
RCPT_DT POSITION(452:470) DATE "YYYY/MM/DD HH24:MI:SS" NULLIF RCPT_DT = BLANKS,
VNDR_ITEM_NBR POSITION(471:502) NULLIF VNDR_ITEM_NBR = BLANKS

)

Log File :
SQL*Loader: Release 10.1.0.2.0 - Production on Wed Dec 20 16:35:16 2006

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

Control File: ora.34398.332121.1.ctl
Data File: ora.34398.332121.fifo.1
File processing option string: "FIX 503"
Bad File: ora.34398.332121.1.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 WS_FDW_RCPT_DIM, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
INSERT_PRCS_AUD_ID 1:11 11 PACKED DECIMAL (20, 0)
NULL if INSERT_PRCS_AUD_ID = 0X0000000000000000000000(character '')
LST_UPDT_PRCS_AUD_ID 12:22 11 PACKED DECIMAL (20, 0)
NULL if LST_UPDT_PRCS_AUD_ID = 0X0000000000000000000000(character '')
SCD_II 23:23 1 CHARACTER
NULL if SCD_II = BLANKS
RCPT_ID 24:34 11 PACKED DECIMAL (20, 0)
SRC_INVTRY_LOCTN_ID 35:45 11 PACKED DECIMAL (20, 0)
DEST_INVTRY_LOCTN_ID 46:56 11 PACKED DECIMAL (20, 0)
BIN_ID 57:67 11 PACKED DECIMAL (20, 0)
PO_LN_ID 68:78 11 PACKED DECIMAL (20, 0)
REQSTN_LN_ID 79:89 11 PACKED DECIMAL (20, 0)
VNDR_ID 90:100 11 PACKED DECIMAL (20, 0)
VNDR_LOCTN_ID 101:111 11 PACKED DECIMAL (20, 0)
SC_BUYER_ID 112:122 11 PACKED DECIMAL (20, 0)
SHPMNT_NBR 123:142 20 CHARACTER
NULL if SHPMNT_NBR = BLANKS
ORD_DOC_TYP_CD 143:162 20 CHARACTER
NULL if ORD_DOC_TYP_CD = BLANKS
ORD_DOC_TYP_DESCR 163:192 30 CHARACTER
NULL if ORD_DOC_TYP_DESCR = BLANKS
TRANS_SYS_ORIG_CD 193:212 20 CHARACTER
NULL if TRANS_SYS_ORIG_CD = BLANKS
COMPONENT_SEQ_NBR 213:222 10 PACKED DECIMAL (19, 0)
NULL if COMPONENT_SEQ_NBR = 0X00000000000000000000(character '')
RCPT_NBR 223:242 20 CHARACTER
NULL if RCPT_NBR = BLANKS
RCPT_LN_NBR 243:252 10 PACKED DECIMAL (19, 0)
NULL if RCPT_LN_NBR = 0X00000000000000000000(character '')
RCPT_RSN_CD 253:272 20 CHARACTER
NULL if RCPT_RSN_CD = BLANKS
RCPT_RSN_DESCR 273:302 30 CHARACTER
NULL if RCPT_RSN_DESCR = BLANKS
RCPT_CMMT_TXT 303:342 40 CHARACTER
NULL if RCPT_CMMT_TXT = BLANKS
RCPT_OPRTR_CD 343:362 20 CHARACTER
NULL if RCPT_OPRTR_CD = BLANKS
DRPSHP_IND 363:363 1 CHARACTER
NULL if DRPSHP_IND = BLANKS
ITEM_TYP_CD 364:383 20 CHARACTER
NULL if ITEM_TYP_CD = BLANKS
ITEM_TYP_DESCR 384:413 30 CHARACTER
NULL if ITEM_TYP_DESCR = BLANKS
START_ROW_DT 414:432 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if START_ROW_DT = BLANKS
END_ROW_DT 433:451 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if END_ROW_DT = BLANKS
RCPT_DT 452:470 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if RCPT_DT = BLANKS
VNDR_ITEM_NBR 471:502 32 CHARACTER
NULL if VNDR_ITEM_NBR = BLANKS


Table WS_FDW_RCPT_DIM:
15064 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.

Date cache:
Max Size: 1000
Entries : 68
Hits : 45124
Misses : 0

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: 15064
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 8
Total stream buffers loaded by SQL*Loader load thread: 7

Run began on Wed Dec 20 16:35:16 2006
Run ended on Wed Dec 20 16:35:17 2006

Elapsed time was: 00:00:01.07
CPU time was: 00:00:00.31
Edward Yuan
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

Preserve blanks only preserves blanks that are already there. "Randy" does not have any blanks.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
Yuan_Edward
Participant
Posts: 73
Joined: Tue May 10, 2005 6:21 pm
Location: Sydney

Post by Yuan_Edward »

PRESERVE BLANKS Option
The PRESERVE BLANKS option:

1. Retains leading whitespace when optional enclosure delimiters are not present
2. Leaves trailing whitespace intact when fields are specified with a predetermined size
For example, consider the following field, where underscores represent blanks:
__aa__,

If this field is loaded with the following control clause, then both the leading whitespace and the trailing whitespace are retained if PRESERVE BLANKS is specified. Otherwise, the leading whitespace is trimmed.

TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'




I guess the default value for APT_STRING_PAD_CHAR is SPACE and the generated data file is fixed-width. That means DataStage Oracle Enterprise Stage produced additional SPACEs for both fields. So the data file generated looks like:
1."Randy "(The origional value "Randy" plus additional padding spaces)
2." " (one origional space plus additional padding spaces)

Desired Data :
Target Data : 1."Randy"
2." "(one space)

Case1: $APT_ORA_PRESERVE_BLANKS True

Target Data : 1."Randy "(padding spaces) (Correct, padding spaces are part of the value of this field)
2.NULL (Oracle Null) (the original one space plus padding spaces expected but I guess there is transformation on this field, NULLIF it is BLANKS, so the result is NULL)

Case2: $APT_ORA_PRESERVE_BLANKS False

Target Data : 1."Randy"(padding spaces) (Correct, padding spaces are part of the value of this field, no leading spaces are trimmed)
2.NULL (Oracle Null) (Correct BLANKS will not be preserved).

Needs to be confirmed with the Control File and Data Files.
panchusrao2656 wrote:When i set $APT_ORA_PRESERVE_BLANKS to true, it is padding characters eventhough the target column is varchar. Data file that is getting created ora.*.fifo.o.* is getting deleted once the file has been loaded.

can any of the DataStage experts or Premium posters put some light on this issue.

The issue is as follows.

Source Data : 1."Randy" 2." "(one space)
Target metadata 1.Varchar2(20), 2.Varchar2(20)

Case1: $APT_ORA_PRESERVE_BLANKS True

Target Data : 1."Randy "(padding spaces)
2.NULL (Oracle Null)

Case2: $APT_ORA_PRESERVE_BLANKS False

Target Data : 1."Randy"(padding spaces)
2.NULL (Oracle Null)

Desired Data :
Target Data : 1."Randy"
2." "(one space)

As we need to load the data by truncating old data everytime, please let us know whether there is any option like Pre_sql in server jobs that we can truncate the table and then upserts the data.

There are open command & close commands only for bulk load not for upserts.

The simple option we have is Truncate the table in the prior job and upsert the data in the next job, we would like to avoid this.
Edward Yuan
Top
Post Reply

Return to “IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)”