Home » RDBMS Server » Server Utilities » Convert DB2 load scripts to Oracle Load scripts (SQL Loader)
|
|
|
Re: Convert DB2 load scripts to Oracle Load scripts [message #648064 is a reply to message #648060] |
Mon, 15 February 2016 23:15 |
|
Barbara Boehmer
Messages: 9090 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
drevalla wrote on Mon, 15 February 2016 20:35Barbara, in the DB2 script, the following script is written after the load command. Is there any way to do this in Oracle loader control file?
'ParmsEquateData' is the table name. And, 'lang', 'code' & 'elementid' are the column names.
update ParmsEquateData
set lang = 'EN'
where lang is null;
update ParmsEquateData
set code = elementid, elementid = 'LABEL'
where code = ' ';
You can do this in your field declarations in your control file. Please see the demonstration below.
SCOTT@orcl> host type test.dat
lang1,code1,elem1,
, ,elem2,
lang3,code3,elem3,
SCOTT@orcl> host type test.ctl
load data
infile test.dat
into table parmsequatedata
fields terminated by ',' trailing nullcols
( lang "NVL(:lang,'EN')"
, code "CASE WHEN :code=' ' THEN :elementid ELSE :code END"
, elementid "CASE WHEN :code=' ' THEN 'LABEL' ELSE :elementid END" )
SCOTT@orcl> create table parmsequatedata
2 (lang varchar2(5),
3 code varchar2(5),
4 elementid varchar2(5))
5 /
Table created.
SCOTT@orcl> host sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 12.1.0.2.0 - Production on Mon Feb 15 21:13:37 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 3
Table PARMSEQUATEDATA:
3 Rows successfully loaded.
Check the log file:
test.log
for more information about the load.
SCOTT@orcl> select * from parmsequatedata
2 /
LANG CODE ELEME
----- ----- -----
lang1 code1 elem1
EN elem2 LABEL
lang3 code3 elem3
3 rows selected.
|
|
|
|
|
|
Re: Convert DB2 load scripts to Oracle Load scripts [message #648109 is a reply to message #648106] |
Tue, 16 February 2016 12:45 |
|
drevalla
Messages: 44 Registered: February 2016
|
Member |
|
|
Barbara, checking empty string is not working. However, below code worked.
OPTIONS (ROWS=150000)
Load Data CHARACTERSET WE8EBCDIC37
infile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\EquData1.dat' "fix 240"
badfile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\PEDMigrateEquData1.bad'
discardfile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\PEDMigrateEquData1.dsc'
append into table UNTK48.ParmsEquateData
DATE FORMAT "YYYYMMDD"
(Controls POSITION(01:12),
Lang POSITION(13:14) "NVL(:Lang,'EN')",
Code POSITION(31:40) "NVL(:Code, :ElementId)",
ElementId POSITION(23:30) "NVL(:Code, 'LABEL')",
Description POSITION(64:103))
|
|
|
|
|
|
|
|
|
Re: Convert DB2 load scripts to Oracle Load scripts [message #648143 is a reply to message #648119] |
Wed, 17 February 2016 12:26 |
|
drevalla
Messages: 44 Registered: February 2016
|
Member |
|
|
Hi Barbara, I'm having issues loading below data. I need your help.
Data file content :
DW106,20151110,48,000,000,0000,000000,0000,0000,00000,/secure/reports/20151110.html,188,129143,USA,EN,
DW389,20151110,48,063,000,0000,000000,0000,0000,00063,/secure/reports/20151110.html,129517,5187,0,EN,
DW500,20151110,48,063,000,0000,006132,0003,0001,00063,/secure/reports/20151110.html,134928,2926,0,EN,
Table description :
Name Null Type
------------- -------- -------------
REPORTID NOT NULL VARCHAR2(16)
DATE NOT NULL DATE
CONTROL1 NOT NULL CHAR(2)
CONTROL2 NOT NULL CHAR(3)
CONTROL3 NOT NULL CHAR(3)
CONTROL4 NOT NULL CHAR(4)
DEALERNUMBER NOT NULL CHAR(6)
DEALERLOC NOT NULL CHAR(4)
DEALERTYPE NOT NULL CHAR(4)
BRANCH NOT NULL CHAR(5)
REPORTURL NOT NULL VARCHAR2(100)
STARTPOSITION NOT NULL NUMBER(10)
LENGTH NOT NULL NUMBER(10)
OTHERBREAK NOT NULL VARCHAR2(100)
LANGUAGE NOT NULL VARCHAR2(6)
Control file code :
OPTIONS (ROWS=150000)
Load Data
infile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\Reports.dat'
badfile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\REPLoad.bad'
discardfile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\REPLoad.dsc'
Append into table UNTK48.Reports
fields terminated by ',' trailing nullcols
(reportId,
date DATE "YYYYMMDD",
control1,
control2,
control3,
control4,
dealerNumber,
dealerLoc,
dealerType,
branch,
reportURL,
startPosition,
length,
otherBreak,
language)
|
|
|
|
|
|
|
|
|
|
Re: Convert DB2 load scripts to Oracle Load scripts [message #648158 is a reply to message #648157] |
Wed, 17 February 2016 14:56 |
|
drevalla
Messages: 44 Registered: February 2016
|
Member |
|
|
Below script worked.
OPTIONS (ROWS=150000)
Load Data
infile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\Reports.dat'
badfile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\REPLoad.bad'
discardfile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\REPLoad.dsc'
Append into table UNTK48.Reports
fields terminated by ',' trailing nullcols
(reportId,
"DATE" DATE "YYYYMMDD",
control1,
control2,
control3,
control4,
dealerNumber,
dealerLoc,
dealerType,
branch,
reportURL,
startPosition,
length,
otherBreak,
language)
|
|
|
|
|
|
|
|
Re: Convert DB2 load scripts to Oracle Load scripts [message #648173 is a reply to message #648171] |
Wed, 17 February 2016 22:16 |
|
Barbara Boehmer
Messages: 9090 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
drevalla wrote on Wed, 17 February 2016 19:47Using Replace will delete the records and again adds the records, right ?
So, I have to send empty data file ?
I presumed that you wanted to empty the data file prior to loading new data, so that is why I suggested REPLACE. If you just want to delete all data from the table, then you can use either TRUNCATE or DELETE from SQL, instead of SQL*Loader. If, for some strange reason, you just want to remove the data from SQL*Loader, then you will need to use a data file with just one or more lines of spaces.
|
|
|
Re: Convert DB2 load scripts to Oracle Load scripts [message #648174 is a reply to message #648173] |
Wed, 17 February 2016 22:58 |
|
drevalla
Messages: 44 Registered: February 2016
|
Member |
|
|
Currently DB2 is using the .ld file to clear the tables. I posted that code to you earlier
import from /dev/null of del replace into NOTES;
I'm just trying to do everything similar to what Db2 does currently. As I mentioned in my first post that there is an application which does batch job where all the data is being loaded. They have these clear tables too.
Below is the script worked in clearing the data.
Load Data
infile 'C:\Users\lc7582\Desktop\Fortify\DAT_files\ClearAllTables.dat'
Replace into table UNTK48.BRANCHTYPEBATCH(Controls)
into table UNTK48.CASHMANAGEMENT(Controls)
into table UNTK48.CATEGORYCODEPARM(Controls)
.........
..........
|
|
|
Goto Forum:
Current Time: Fri Apr 26 14:28:15 CDT 2024
|