Home » RDBMS Server » Server Utilities » Convert DB2 load scripts to Oracle Load scripts (SQL Loader)
| Convert DB2 load scripts to Oracle Load scripts [message #647930] |
Thu, 11 February 2016 19:22  |
 |
drevalla
Messages: 44
Registered: February 2016
|
Member |
|
|
We have DB2 load scripts which load data from .DAT (EBCDIC) files which are un-delimited.
Now, we need to convert these DB2 load scripts to SQL Loader scripts.
Below is one of the DB2 load script. Could you provide equivalent SQL loader script for this ?
Is there any tool or utility to convert DB2 load scripts to SQL loader scripts ?
DB2 Load Script :
load client from /export/alsdfbch/dfBatch/scripts/linux/db2/@region@/Notes.dat
of asc
modified by codepage=37
reclen=916
striptblanks
dateformat="YYYYMMDD"
timestampformat="YYYYMMDD"
dumpfile = @loaddumppath@_Notes.bad
dumpfileaccessall
method L
( 1 32, 33 44, 45 50, 51 54, 55 58, 59 101, 102 103,
104 104, 105 112, 113 120, 121 128, 129 136, 137 916 )
savecount 150000
messages /export/alsdfbch/dfBatch/scripts/linux/db2/@region@_Notes.log
insert into Notes(ID, CONTROLS, DEALERNUMBER, LOCATION, TYPE, NOTEKEY, NOTETYPECODE,
NOTEPRIORITYCODE, ORIGINALAUTHOR, ORIGINALDATE, STARTDATE, STOPDATE, NOTETEXT)
for exception X_Notes
copy yes to @loadcopypath@;
|
|
|
|
|
|
|
|
|
|
| Re: Convert DB2 load scripts to Oracle Load scripts [message #647955 is a reply to message #647952] |
Fri, 12 February 2016 07:52  |
|
drevalla
Messages: 44
Registered: February 2016
|
Member |
|
|
I converted to the following code. However, I'm getting syntax error.
Load Data infile 'C:\Users\lc7582\Desktop\Fortify\DAT files\DLSNotes.dat' "RECSIZE 916"
CHARACTERSET WE8EBCDIC500
badfile 'C:\Users\lc7582\Desktop\Fortify\DAT files\DLSNotes.bad'
discardfile 'C:\Users\lc7582\Desktop\Fortify\DAT files\DLSNotes.dsc'
ROWS=150000
append into table UNTK48.Notes
TRAILING NULLCOLS
(ID POSITION(01:32),
CONTROLS POSITION(33:44),
DEALERNUMBER POSITION(45:50),
LOCATION POSITION(51:54),
TYPE POSITION(55:58),
NOTEKEY POSITION(59:101),
NOTETYPECODE POSITION(102:103),
NOTEPRIORITYCODE POSITION(104:104),
ORIGINALAUTHOR POSITION(105:112),
ORIGINALDATE POSITION(113:120),
STARTDATE POSITION(121:128),
STOPDATE POSITION(129:136),
NOTETEXT POSITION(137:916))
;
|
|
|
|
|
|
| Re: Convert DB2 load scripts to Oracle Load scripts [message #647958 is a reply to message #647955] |
Fri, 12 February 2016 11:16 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your characterset is in the wrong place. Get rid of the recsize and rows and semicolon at the end.
Load Data
CHARACTERSET WE8EBCDIC500
infile 'C:\Users\lc7582\Desktop\Fortify\DAT files\DLSNotes.dat'
badfile 'C:\Users\lc7582\Desktop\Fortify\DAT files\DLSNotes.bad'
discardfile 'C:\Users\lc7582\Desktop\Fortify\DAT files\DLSNotes.dsc'
append into table UNTK48.Notes
TRAILING NULLCOLS
(ID POSITION(01:32),
CONTROLS POSITION(33:44),
DEALERNUMBER POSITION(45:50),
LOCATION POSITION(51:54),
TYPE POSITION(55:58),
NOTEKEY POSITION(59:101),
NOTETYPECODE POSITION(102:103),
NOTEPRIORITYCODE POSITION(104:104),
ORIGINALAUTHOR POSITION(105:112),
ORIGINALDATE POSITION(113:120),
STARTDATE POSITION(121:128),
STOPDATE POSITION(129:136),
NOTETEXT POSITION(137:146))
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Convert DB2 load scripts to Oracle Load scripts [message #647975 is a reply to message #647974] |
Fri, 12 February 2016 23:06 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you would provide your table structure by providing the results from SQL*Plus of:
DESCRIBE UNTK48.Notes
and also provide a few rows of sample data, we can just provide you with the code to load it. Many of us who are experienced with SQL*Loader don't know DB2.
The null value issue could be caused by attempting to load the wrong thing into that not null column; Seeing a few rows of sample data would clarify this. Sometimes things are not in the positions you think they are, due to different types of characters, some of which count as multiple spaces. There are timestamp formats. Loading data using SQL*Loader does not destroy the original data file, if that is what you mean by saving it.
[Updated on: Fri, 12 February 2016 23:11] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Convert DB2 load scripts to Oracle Load scripts [message #647991 is a reply to message #647990] |
Sat, 13 February 2016 14:42 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
What you have is not a fixed format file. It is delimited by whitespace and optionally enclosed by double quotation marks. I changed the data types of two of your columns to timestamps, since that seems to be what the data is. Please see the demonstration of a load below, including the control file. Note that the limited line length that I used on this forum may cause the display of the data file and display of the resulting data in the table to wrap around.
-- data file:
SCOTT@orcl> HOST TYPE dlsnotes.dat
"8a04586851d5bb110151d5bbbeda0001" "480630000000" "007310" "0003" "0001" "48063000000000731000030001" 2 1 "AP13032" "2015-12-24 14:43:49.850 " 24-DEC-15 23-DEC-18 "AP13032" "2015-12-24 14:44:46.170 " "Hi (AP13032)Bye (AP13032)"
-- control file:
SCOTT@orcl> HOST TYPE test.ctl
LOAD DATA
INFILE 'dlsnotes.dat'
BADFILE 'dlsnotes.bad'
DISCARDFILE 'dlsnotes.dsc'
APPEND INTO TABLE Notes
FIELDS TERMINATED BY WHITESPACE
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(ID,
CONTROLS,
DEALERNUMBER,
LOCATION,
TYPE,
NOTEKEY,
NOTETYPECODE,
NOTEPRIORITYCODE,
ORIGINALAUTHOR,
ORIGINALDATE "TO_TIMESTAMP(:ORIGINALDATE,'YYYY-MM-DD HH24:MI:SS.FF3')",
STARTDATE "TO_DATE(:STARTDATE,'DD-MON-YY')",
STOPDATE "TO_DATE(:STOPDATE,'DD-MON-YY')",
AUTHOR,
UPDATEDATE "TO_TIMESTAMP(:UPDATEDATE,'YYYY-MM-DD HH24:MI:SS.FF3')",
NOTETEXT)
-- table:
SCOTT@orcl> DESC notes
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
ID NOT NULL CHAR(32)
CONTROLS CHAR(12)
DEALERNUMBER CHAR(6)
LOCATION CHAR(4)
TYPE CHAR(4)
NOTEKEY NOT NULL VARCHAR2(160)
NOTETYPECODE NOT NULL NUMBER(4)
NOTEPRIORITYCODE NOT NULL NUMBER(2)
ORIGINALAUTHOR NOT NULL VARCHAR2(20)
ORIGINALDATE NOT NULL TIMESTAMP(6)
STARTDATE NOT NULL DATE
STOPDATE DATE
AUTHOR VARCHAR2(20)
UPDATEDATE TIMESTAMP(6)
NOTETEXT NOT NULL VARCHAR2(3600)
-- load:
SCOTT@orcl> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SQL*Loader: Release 12.1.0.2.0 - Production on Sat Feb 13 12:34:16 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 1
Table NOTES:
1 Row successfully loaded.
Check the log file:
test.log
for more information about the load.
-- results:
SCOTT@orcl> COLUMN notekey FORMAT A30
SCOTT@orcl> COLUMN originaldate FORMAT A30
SCOTT@orcl> COLUMN updatedate FORMAT A30
SCOTT@orcl> COLUMN notetext FORMAT A30
SCOTT@orcl> SELECT * FROM notes
2 /
ID CONTROLS DEALER LOCA TYPE NOTEKEY NOTETYPECODE NOTEPRIORITYCODE
-------------------------------- ------------ ------ ---- ---- ------------------------------ ------------ ----------------
ORIGINALAUTHOR ORIGINALDATE STARTDATE STOPDATE AUTHOR UPDATEDATE
-------------------- ------------------------------ ----------- ----------- -------------------- ------------------------------
NOTETEXT
------------------------------
8a04586851d5bb110151d5bbbeda0001 480630000000 007310 0003 0001 48063000000000731000030001 2 1
AP13032 2015-12-24 14:43:49.850 24-DEC-2015 23-DEC-2018 AP13032 2015-12-24 14:44:46.170
Hi (AP13032)Bye (AP13032)
1 row selected.
|
|
|
|
|
|
|
|
|
|
| Re: Convert DB2 load scripts to Oracle Load scripts [message #647995 is a reply to message #647993] |
Sat, 13 February 2016 22:05 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
drevalla wrote on Sat, 13 February 2016 16:57I have few questions.
1) The data type in the database should match the datatype in the loader syntax?
2)The no.of columns in the database should match the no.of columns in loader syntax. I see that destination database has 15 columns. However, source data file has data for only 13 columns. So, I did not mention it in the loader. Is that right?
The terminology for data types used in SQL*Loader is different from what is used in Oracle tables. For example, data in your data file may be specified as CHAR in your control file and VARCHAR2 in your table. Data in your data file may be specified as INTEGER EXTERNAL in your control file and NUMBER in your table. Data can also be converted from one data type to another using functions and expressions.
You may have more columns in your data file than your table or more columns in your table than your data file. When you have more columns in your data file, those that are not loaded may be reference as filler fields in your control file. When you have more columns in your table for which you do not have data from your file to load, those columns are just not listed in your control file. There may also be columns that are populated using sequences and constants and such that may be specified in the control file or populated via triggers and such.
[Updated on: Sat, 13 February 2016 22:06] Report message to a moderator |
|
|
|
| Re: Convert DB2 load scripts to Oracle Load scripts [message #647996 is a reply to message #647995] |
Sat, 13 February 2016 23:52 |
|
drevalla
Messages: 44
Registered: February 2016
|
Member |
|
|
Thank You.
I converted another DB2 script to oracle loader script.
I want you to help me find the replacement for "binarynumerics" and "packeddecimal" modifiers in DB2 with Oracle.
DB2 script :
load client from /export/alsdfbch/dfBatch/scripts/linux/db2/@region@/DWSVehicle.dat
of asc
modified by codepage=37
reclen=950
striptblanks
binarynumerics
packeddecimal
dateformat="YYYYMMDD"
dumpfile = @loaddumppath@_DWSVehicle.bad
dumpfileaccessall
method L
( 1 12, 13 18, 19 22, 23 26,
27 34, 37 65, 66 79, 82 93, 94 118, 133 140, 125 132,
141 148, 149 156, 157 164, 165 172, 197 197,
173 180, 181 188, 189 196, 240 242, 119 124,
198 201, 202 216, 217 231, 232 239, 249 249, 250 257, 258 267, 268 275 )
savecount 150000
messages /export/alsdfbch/dfBatch/scripts/linux/db2/@region@_DealerWholesaleStatement.log
insert into Vehicle(Controls, DealerNbr, Location, Type,
StatementDt, Commitment, AccountNbr, VINControls, VIN, OriginalAmt, PrincipalBalAmt,
CurrentInterest, CurrentFlats, CurrentInsurance, CurrentFees, CMTCode,
CMTCurrentAmtDue, CMTPastDueAmtDue, TotalAmtDue, OSDays, InterestRate,
Year, Make, Model, FloorDate, SUBVENEDIND, VINCurrTax, StockNbr, SUBVENEDDATE)
for exception X_Vehicle
copy yes to @loadcopypath@;
Oracle Script:
OPTIONS (ROWS=150000)
Load Data CHARACTERSET WE8EBCDIC37
infile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\DWSVehicle.dat'
badfile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\DWSVehicle.bad'
DISCARDDN 'C:\Users\lc7582\Desktop\Fortify\DAT_files\DWSVehicle.dsc'
append into table UNTK48.Vehicle
DATE FORMAT "YYYYMMDD"
(Controls POSITION(01:12),
DealerNbr POSITION(13:18),
Location POSITION(19:22),
Type POSITION(23:26),
StatementDt POSITION(27:34) DATE,
Commitment POSITION(37:65),
AccountNbr POSITION(66:79),
VINControls POSITION(82:93),
VIN POSITION(94:118),
OriginalAmt POSITION(133:140) DECIMAL (15,2),
PrincipalBalAmt POSITION(125:132) DECIMAL (15,2),
CurrentInterest POSITION(141:148) DECIMAL (15,2),
CurrentFlats POSITION(149:156) DECIMAL (15,2),
CurrentInsurance POSITION(157:164) DECIMAL (15,2),
CurrentFees POSITION(165:172) DECIMAL (15,2),
CMTCode POSITION(197:197),
CMTCurrentAmtDue POSITION(173:180) DECIMAL (15,2),
CMTPastDueAmtDue POSITION(181:188) DECIMAL (15,2),
TotalAmtDue POSITION(189:196) DECIMAL (15,2),
OSDays POSITION(240:242),
InterestRate POSITION(119:124) DECIMAL (10,7),
Year POSITION(198:201),
Make POSITION(202:216),
Model POSITION(217:231),
FloorDate POSITION(232:239) DATE,
SUBVENEDIND POSITION(249:249),
VINCurrTax POSITION(250:257) DECIMAL (15,2),
StockNbr POSITION(258:267),
SUBVENEDDATE POSITION(268:275) DATE)
I'm getting the below error in the log:
SQL*Loader: Release 12.1.0.2.0 - Production on Sun Feb 14 00:42:51 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Control File: C:\Users\lc7582\Desktop\Fortify\DAT_files\DWSLoadVehicle.ctl
Character Set WE8EBCDIC37 specified for all input.
Data File: C:\Users\lc7582\Desktop\Fortify\DAT_files\DWSVehicle.dat
Bad File: C:\Users\lc7582\Desktop\Fortify\DAT_files\DWSVehicle.bad
Discard File: C:\Users\lc7582\Desktop\Fortify\DAT_files\DWSVehicle.dsc
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 150000 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table UNTK48.VEHICLE, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CONTROLS 1:12 12 CHARACTER
DEALERNBR 13:18 6 CHARACTER
LOCATION 19:22 4 CHARACTER
TYPE 23:26 4 CHARACTER
STATEMENTDT 27:34 8 DATE YYYYMMDD
COMMITMENT 37:65 29 CHARACTER
ACCOUNTNBR 66:79 14 CHARACTER
VINCONTROLS 82:93 12 CHARACTER
VIN 94:118 25 CHARACTER
ORIGINALAMT 133:140 8 PACKED DECIMAL (15, 2)
PRINCIPALBALAMT 125:132 8 PACKED DECIMAL (15, 2)
CURRENTINTEREST 141:148 8 PACKED DECIMAL (15, 2)
CURRENTFLATS 149:156 8 PACKED DECIMAL (15, 2)
CURRENTINSURANCE 157:164 8 PACKED DECIMAL (15, 2)
CURRENTFEES 165:172 8 PACKED DECIMAL (15, 2)
CMTCODE 197:197 1 CHARACTER
CMTCURRENTAMTDUE 173:180 8 PACKED DECIMAL (15, 2)
CMTPASTDUEAMTDUE 181:188 8 PACKED DECIMAL (15, 2)
TOTALAMTDUE 189:196 8 PACKED DECIMAL (15, 2)
OSDAYS 240:242 3 CHARACTER
INTERESTRATE 119:124 6 PACKED DECIMAL (10, 7)
YEAR 198:201 4 CHARACTER
MAKE 202:216 15 CHARACTER
MODEL 217:231 15 CHARACTER
FLOORDATE 232:239 8 DATE YYYYMMDD
SUBVENEDIND 249:249 1 CHARACTER
VINCURRTAX 250:257 8 PACKED DECIMAL (15, 2)
STOCKNBR 258:267 10 CHARACTER
SUBVENEDDATE 268:275 8 DATE YYYYMMDD
value used for ROWS parameter changed from 150000 to 551
Record 3: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 4: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 5: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 6: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 7: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 8: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 9: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 11: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 12: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 13: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 14: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 16: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 17: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 18: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 19: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 21: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 22: Discarded - all columns null.
Record 23: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 24: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 26: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 27: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 28: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 29: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 30: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 31: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 32: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 33: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 34: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 35: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 36: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 37: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 38: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 39: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 40: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 41: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 42: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 43: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 44: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 46: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 47: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 50: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 51: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 52: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 53: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 54: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 55: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 56: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 57: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 58: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 59: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 61: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 62: Rejected - Error on table UNTK48.VEHICLE, column VINCURRTAX.
Value larger than specified precision allows for this column
Record 1: Rejected - Error on table UNTK48.VEHICLE, column OSDAYS.
ORA-01722: invalid number
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table UNTK48.VEHICLE:
0 Rows successfully loaded.
52 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
1 Row not loaded because all fields were null.
Space allocated for bind array: 255664 bytes(551 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 62
Total logical records rejected: 52
Total logical records discarded: 1
Run began on Sun Feb 14 00:42:51 2016
Run ended on Sun Feb 14 00:42:58 2016
Elapsed time was: 00:00:07.42
CPU time was: 00:00:00.19
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Convert DB2 load scripts to Oracle Load scripts [message #648006 is a reply to message #648004] |
Sun, 14 February 2016 18:46 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Did you successfully load the data from your first problem?
Are you trying to post data from the first problem or the second problem or both? You don't need to post the whole data file, just a few rows/records. You can do this by using whatever editor to you have to access your data file, highlighting the first few rows, then using the CTRL key and the C key to copy it, then clicking within your post on this forum and using the CTRL key and the V key to paste it. Alternatively, when you create a new post, you can click on "Upload File" and follow the instructions. If it is a large file, then you should just make a copy and save the first few rows, then upload that. What have you tried and what happened?
There are a lot of errors in your latest control file. I am not going to waste my time again doing anything until or unless you post some data.
If you want to continue on your own, you should try writing a control file to just load one column, test that, then add the next column, test that, and so forth. Don't continue adding columns until you have successfully tested a load with the previous columns.
[Updated on: Sun, 14 February 2016 18:51] Report message to a moderator |
|
|
|
| Re: Convert DB2 load scripts to Oracle Load scripts [message #648007 is a reply to message #648006] |
Sun, 14 February 2016 19:52 |
|
drevalla
Messages: 44
Registered: February 2016
|
Member |
|
|
Thank You Barbara.
For my first, I was using wrong data file, I guess that is why most of the records are null.
So, I started working on the second. Now, I'm able to add one row for my second script. However, there are 62 more records which are not getting loaded. And, I think this is because of not specifying record size for the data file.
If we do not specify this, how the loader will know where the second row is starting from ? The input data file is in EBCDIC fixed format and it is un-delimited. As you know using RECSIZE is throwing error. That is the reason I posted that question related to
"Specifying Datafile Format and Buffering'. According to oracle documentation,
When configuring SQL*Loader, you can specify an operating system-dependent file processing options string in the control file to control file processing. You use this string to specify file format and buffering. I think if I can get answer for this, my second file load other records too.
|
|
|
|
| Re: Convert DB2 load scripts to Oracle Load scripts [message #648008 is a reply to message #648007] |
Sun, 14 February 2016 21:23 |
|
drevalla
Messages: 44
Registered: February 2016
|
Member |
|
|
I added 'fix 950' and it worked. I was able to load 35477 rows successfully. 
Load script :
OPTIONS (ROWS=150000)
Load Data CHARACTERSET WE8EBCDIC37
infile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\DWSVehicle.dat' "fix 950"
badfile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\DWSVehicle.bad'
discardfile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\DWSVehicle.dsc'
append into table UNTK48.Vehicle
DATE FORMAT "YYYYMMDD"
(Controls POSITION(01:12),
DealerNbr POSITION(13:18),
Location POSITION(19:22),
Type POSITION(23:26),
StatementDt POSITION(27:34) DATE,
Commitment POSITION(37:65),
AccountNbr POSITION(66:79),
VINControls POSITION(82:93),
VIN POSITION(94:118),
OriginalAmt POSITION(133:140) DECIMAL (15,2),
PrincipalBalAmt POSITION(125:132) DECIMAL (15,2),
CurrentInterest POSITION(141:148) DECIMAL (15,2),
CurrentFlats POSITION(149:156) DECIMAL (15,2),
CurrentInsurance POSITION(157:164) DECIMAL (15,2),
CurrentFees POSITION(165:172) DECIMAL (15,2),
CMTCode POSITION(197:197),
CMTCurrentAmtDue POSITION(173:180) DECIMAL (15,2),
CMTPastDueAmtDue POSITION(181:188) DECIMAL (15,2),
TotalAmtDue POSITION(189:196) DECIMAL (15,2),
OSDays POSITION(240:242) DECIMAL (4,0),
InterestRate POSITION(119:124) DECIMAL (10,7),
Year POSITION(198:201),
Make POSITION(202:216),
Model POSITION(217:231),
FloorDate POSITION(232:239) DATE,
SUBVENEDIND POSITION(249:249),
VINCurrTax POSITION(250:257) DECIMAL (15,2),
StockNbr POSITION(258:267),
SUBVENEDDATE POSITION(268:275) DATE)
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 19:59:59 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
