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 Go to next message
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 #647944 is a reply to message #647930] Fri, 12 February 2016 02:23 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Not directly, but this might help.
Re: Convert DB2 load scripts to Oracle Load scripts [message #647952 is a reply to message #647944] Fri, 12 February 2016 07:48 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Thank you
Re: Convert DB2 load scripts to Oracle Load scripts [message #647953 is a reply to message #647952] Fri, 12 February 2016 07:50 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
However. I checked that already. It has just few commands to compare and not comprehensive.
Re: Convert DB2 load scripts to Oracle Load scripts [message #647955 is a reply to message #647952] Fri, 12 February 2016 07:52 Go to previous messageGo to next message
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 #647956 is a reply to message #647955] Fri, 12 February 2016 07:55 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
What is the error? And please format your code to make it easier to read.
Re: Convert DB2 load scripts to Oracle Load scripts [message #647958 is a reply to message #647955] Fri, 12 February 2016 11:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 #647960 is a reply to message #647958] Fri, 12 February 2016 11:34 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Thank You Barbara.
Yes, I figured it out that characterset is in wrong place and also the semi-colon.
However, how about the RECSIZE because in DB2 load script they used RECLEN ?
Re: Convert DB2 load scripts to Oracle Load scripts [message #647961 is a reply to message #647960] Fri, 12 February 2016 11:44 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
I ran the code and I get an error. Is this error due to data ?


SQL*Loader: Release 12.1.0.2.0 - Production on Fri Feb 12 12:34:41 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Control File: C:\Users\lc7582\Desktop\Fortify\DAT_files\Notes.ctl
Character Set WE8EBCDIC500 specified for all input.

Data File: C:\Users\lc7582\Desktop\Fortify\DAT_files\DLSNotes.dat
Bad File: C:\Users\lc7582\Desktop\Fortify\DAT_files\Notes.bad
Discard File: C:\Users\lc7582\Desktop\Fortify\DAT_files\Notes.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table UNTK48.NOTES, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID 1:32 32 CHARACTER
CONTROLS 33:44 12 CHARACTER
DEALERNUMBER 45:50 6 CHARACTER
LOCATION 51:54 4 CHARACTER
TYPE 55:58 4 CHARACTER
NOTEKEY 59:101 43 CHARACTER
NOTETYPECODE 102:103 2 CHARACTER
NOTEPRIORITYCODE 104:104 1 CHARACTER
ORIGINALAUTHOR 105:112 8 CHARACTER
ORIGINALDATE 113:120 8 CHARACTER
STARTDATE 121:128 8 CHARACTER
STOPDATE 129:136 8 CHARACTER
NOTETEXT 137:916 780 CHARACTER

Record 1: Rejected - Error on table UNTK48.NOTES, column NOTEKEY.
ORA-01400: cannot insert NULL into ("UNTK48"."NOTES"."NOTEKEY")


Table UNTK48.NOTES:
0 Rows successfully loaded.
1 Row 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.


Space allocated for bind array: 60416 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 1
Total logical records rejected: 1
Total logical records discarded: 0

Run began on Fri Feb 12 12:34:41 2016
Run ended on Fri Feb 12 12:34:44 2016

Elapsed time was: 00:00:02.98
CPU time was: 00:00:00.10
Re: Convert DB2 load scripts to Oracle Load scripts [message #647962 is a reply to message #647961] Fri, 12 February 2016 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-01400: cannot insert NULL into (%s)
 *Cause: An attempt was made to insert NULL into previously listed objects.
 *Action: These objects cannot accept NULL values.

Check data if there is a NULL or missing field for a column that should not be null.
Check table definition if there is any column declared NOT NULL when it can be.

Re: Convert DB2 load scripts to Oracle Load scripts [message #647963 is a reply to message #647962] Fri, 12 February 2016 12:15 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Thank you Michel.
I checked the table and it cannot be null.
I cannot check the data in the .DAT file because it is in EBCIDC format.
Re: Convert DB2 load scripts to Oracle Load scripts [message #647964 is a reply to message #647963] Fri, 12 February 2016 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I checked the table and it cannot be null.

If this is what it must be?

Quote:
I cannot check the data in the .DAT file because it is in EBCIDC format.


You can always use an hexadecimal editor.

Anyway, given your control file uses only position definition for the fields, a column can be null only if the record is smaller than expected: 916 bytes (not counting the end of line).

Re: Convert DB2 load scripts to Oracle Load scripts [message #647965 is a reply to message #647964] Fri, 12 February 2016 12:36 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
I used 'TRAILING NULLCOLS' to replace for 'striptblanks'(DB2 syntax). Is that right?
Re: Convert DB2 load scripts to Oracle Load scripts [message #647967 is a reply to message #647965] Fri, 12 February 2016 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I can't answer your question but if you use this syntax (TRAILING NULLCOLS) then you tell SQL*Loader that the last and not provided columns are NULL.

Searching on the web I found https://www1.columbia.edu/sec/acis/db2/db2n0/sqln0066.htm which says:

Quote:
striptblanks Truncates any trailing blank spaces when loading data into a variable-length field. If this option is not specified, blank spaces are kept.


So this is not the same thing.

Re: Convert DB2 load scripts to Oracle Load scripts [message #647968 is a reply to message #647967] Fri, 12 February 2016 12:56 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Thank you.
So, I need to use Trimming Whitespaces at column level to achieve Striptblanks ?
Re: Convert DB2 load scripts to Oracle Load scripts [message #647969 is a reply to message #647968] Fri, 12 February 2016 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Barbara better knows SQL*Loader than I do and may confirm but I think Oracle automatically trims white spaces and you have to specify it if you want to keep these ones (this is the opposite of DB2).

https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#SUTIL1210

Quote:
By default, SQL*Loader removes trailing spaces from CHAR data before passing it to the database.


Re: Convert DB2 load scripts to Oracle Load scripts [message #647970 is a reply to message #647969] Fri, 12 February 2016 13:19 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Thank You
Yes, I read this one. However, was not sure.
Re: Convert DB2 load scripts to Oracle Load scripts [message #647971 is a reply to message #647970] Fri, 12 February 2016 13:42 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
I see that there is a DATE FORMAT clause. Is there any Timestamp format clause ?
Re: Convert DB2 load scripts to Oracle Load scripts [message #647972 is a reply to message #647971] Fri, 12 February 2016 13:51 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
One more question. COPY YES in DB2 load syntax specifies that a copy of the loaded data will be saved. Is there any command in Oracle loader ?
Re: Convert DB2 load scripts to Oracle Load scripts [message #647973 is a reply to message #647971] Fri, 12 February 2016 14:13 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
One more question. COPY YES in DB2 load syntax specifies that a copy of the loaded data will be saved. Is there any command in Oracle loader ?
Re: Convert DB2 load scripts to Oracle Load scripts [message #647974 is a reply to message #647973] Fri, 12 February 2016 15:13 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
'codepage=37' in DB2 corresponds to 'characterset=WE8EBCDIC37' or 'characterset=WE8EBCDIC500' in Oracle ? Need help.
Re: Convert DB2 load scripts to Oracle Load scripts [message #647975 is a reply to message #647974] Fri, 12 February 2016 23:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 #647986 is a reply to message #647975] Sat, 13 February 2016 11:09 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
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 CHAR(26)
STARTDATE NOT NULL DATE
STOPDATE DATE
AUTHOR VARCHAR2(20)
UPDATEDATE CHAR(26)
NOTETEXT NOT NULL VARCHAR2(3600)



One record of Data :
"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)
"
Re: Convert DB2 load scripts to Oracle Load scripts [message #647989 is a reply to message #647986] Sat, 13 February 2016 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

gazzag wrote on Fri, 12 February 2016 14:55
What is the error? And please format your code to make it easier to read.


If you make no effort in your post, I don't see any reason we make some to help you.
And do NOT repeat your post after half an hour, I'm tired to remove the duplicates.
Be patient and wait for someone to answer.
In the meantime, post a correctly formatted message.

Re: Convert DB2 load scripts to Oracle Load scripts [message #647990 is a reply to message #647989] Sat, 13 February 2016 12:37 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
i was not trying to repeat the post. when i post, I still see that my post is not posted and I post it again, it gets posted twice.
Re: Convert DB2 load scripts to Oracle Load scripts [message #647991 is a reply to message #647990] Sat, 13 February 2016 14:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 #647992 is a reply to message #647991] Sat, 13 February 2016 18:41 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Thank You Barbara.
However, the data record I gave you is from the destination database which is oracle. The source data is coming from IBM host in EBCDIC format which is fixed and un-delimited.
Re: Convert DB2 load scripts to Oracle Load scripts [message #647993 is a reply to message #647992] Sat, 13 February 2016 18:57 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
I 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?
Re: Convert DB2 load scripts to Oracle Load scripts [message #647994 is a reply to message #647992] Sat, 13 February 2016 22:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
drevalla wrote on Sat, 13 February 2016 16:41
Thank You Barbara.
However, the data record I gave you is from the destination database which is oracle. The source data is coming from IBM host in EBCDIC format which is fixed and un-delimited.


What belongs in your control file depends upon what is in your data file, which is why I asked for a few rows of your data file. Please provide an actual copy and paste of a few rows from your actual data file, whatever it is.

Re: Convert DB2 load scripts to Oracle Load scripts [message #647995 is a reply to message #647993] Sat, 13 February 2016 22:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
drevalla wrote on Sat, 13 February 2016 16:57
I 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 Go to previous messageGo to next message
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 #647997 is a reply to message #647996] Sun, 14 February 2016 00:12 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
I could not upload or copy the data file.
Re: Convert DB2 load scripts to Oracle Load scripts [message #647999 is a reply to message #647997] Sun, 14 February 2016 10:42 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
is there any other way to send the data file ?
Re: Convert DB2 load scripts to Oracle Load scripts [message #648004 is a reply to message #647999] Sun, 14 February 2016 18:17 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Specifying Datafile Format and Buffering :

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.

Additional Information: For details on the syntax of the file processing options string, see your Oracle operating system-specific documentation.

I need help on this syntax. Please let me know where to find this.

Re: Convert DB2 load scripts to Oracle Load scripts [message #648005 is a reply to message #648004] Sun, 14 February 2016 18:39 Go to previous messageGo to next message
BlackSwan
Messages: 26719
Registered: January 2009
Location: SoCal
Senior Member
https://docs.oracle.com/database/121/nav/portal_booklist.htm

above is where you can find all the manuals
Below is the Utilties Guide where SQL*Loader is documented

https://docs.oracle.com/database/121/SUTIL/toc.htm
Re: Convert DB2 load scripts to Oracle Load scripts [message #648006 is a reply to message #648004] Sun, 14 February 2016 18:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
I added 'fix 950' and it worked. I was able to load 35477 rows successfully. Smile

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)
Re: Convert DB2 load scripts to Oracle Load scripts [message #648044 is a reply to message #648008] Mon, 15 February 2016 10:14 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Barbara, I know we could have multiple infile(s) in one control file. However, my situation is that I need to execute multiple data files in the control file where one data file should be loaded into one table only.
Like, I have example1.DAT which should be loaded into EXAMPLE1 table. example2.DAT into EXAMPLE2 table. Could you help me with the syntax.
Re: Convert DB2 load scripts to Oracle Load scripts [message #648049 is a reply to message #648044] Mon, 15 February 2016 11:52 Go to previous messageGo to previous message
Michel Cadot
Messages: 67223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 13 February 2016 19:33

gazzag wrote on Fri, 12 February 2016 14:55
What is the error? And please format your code to make it easier to read.


If you make no effort in your post, I don't see any reason we make some to help you.
And do NOT repeat your post after half an hour, I'm tired to remove the duplicates.
Be patient and wait for someone to answer.
In the meantime, post a correctly formatted message.

Previous Topic: Data Pump throwing errors
Next Topic: Import DMP file to new Table
Goto Forum:
  


Current Time: Thu Jul 02 18:20:51 CDT 2020