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 #648050 is a reply to message #648044] Mon, 15 February 2016 13:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
drevalla wrote on Mon, 15 February 2016 08:14
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.


If there is something in the data that can be used to distinguish which table it goes into then you can use

INTO TABLE <your table name> WHEN <some field name or position> = <some value>

with one such line followed by field sections for each table.

Otherwise, you will need one control file per table. It is easy enough to make multiple copies of the same control file, then just change the table names.

The data file names can be passed either in the command lines or control files.

Re: Convert DB2 load scripts to Oracle Load scripts [message #648051 is a reply to message #648050] Mon, 15 February 2016 14:10 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Thank You, Barbara.

Re: Convert DB2 load scripts to Oracle Load scripts [message #648060 is a reply to message #648051] Mon, 15 February 2016 22:35 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Barbara, 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 = ' ';
Re: Convert DB2 load scripts to Oracle Load scripts [message #648064 is a reply to message #648060] Mon, 15 February 2016 23:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
drevalla wrote on Mon, 15 February 2016 20:35
Barbara, 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 #648103 is a reply to message #648064] Tue, 16 February 2016 10:03 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Awesome. Thank You, Barbara.
Re: Convert DB2 load scripts to Oracle Load scripts [message #648106 is a reply to message #648103] Tue, 16 February 2016 10:34 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Barbara, I get below error after implementing this. Could you please check.

Record 1: Rejected - Error on table UNTK48.PARMSEQUATEDATA.
ORA-01400: cannot insert NULL into ("UNTK48"."PARMSEQUATEDATA"."CODE")

Control file:
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) "CASE WHEN :Code=' ' THEN :ElementId ELSE :Code END",
ElementId POSITION(23:30) "CASE WHEN :Code=' ' THEN 'LABEL' ELSE :ElementId END",
Description POSITION(64:103))


Re: Convert DB2 load scripts to Oracle Load scripts [message #648108 is a reply to message #648106] Tue, 16 February 2016 12:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Spaces and null values are not the same. If you have null values in your code data and you want to treat it the same as spaces, then use:

Lang POSITION(13:14) "NVL(:Lang,'EN')",
Code POSITION(31:40) "CASE WHEN :code=' ' OR :code IS NULL THEN :elementid ELSE :code END",
ElementId POSITION(23:30) "CASE WHEN :code=' ' OR :code IS NULL THEN 'LABEL' ELSE :elementid END",
Re: Convert DB2 load scripts to Oracle Load scripts [message #648109 is a reply to message #648106] Tue, 16 February 2016 12:45 Go to previous messageGo to next message
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 #648110 is a reply to message #648109] Tue, 16 February 2016 12:51 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Thank You, Barbara.

I did not see your response on this before posting. I will try that.
Re: Convert DB2 load scripts to Oracle Load scripts [message #648111 is a reply to message #648110] Tue, 16 February 2016 12:54 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Barbara, checking empty or null worked. Why not do the same for 'lang' ?
Re: Convert DB2 load scripts to Oracle Load scripts [message #648113 is a reply to message #648111] Tue, 16 February 2016 13:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You could probably use either one. By default, SQL*Loader trims the strings. So, if you do not specify keeping the trailing blank spaces, then spaces end up the same as null and you could just use nvl. If you want to keep blank spaces for any other purposes, then you will need to check for both spaces and null.

Re: Convert DB2 load scripts to Oracle Load scripts [message #648114 is a reply to message #648113] Tue, 16 February 2016 13:41 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Thank You, Barbara.

In DB2, 'IDENTITYMISSING' clause is used so that the import/load command knows that it has to generate identity values.
In Oracle, if we have a column which generates sequence number then we do not have to do anything else in the control file, right ?

Re: Convert DB2 load scripts to Oracle Load scripts [message #648118 is a reply to message #648114] Tue, 16 February 2016 15:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can generate sequences for a column from your control file, by declaring a column using a SQL*Loader sequence. Or, if you have a newer version of Oracle, you can specify the column as an identity column in the table creation. Or, if you have an older version of Oracle, you can use a database sequence and a trigger to accomplish the same as the newer identity column.
Re: Convert DB2 load scripts to Oracle Load scripts [message #648119 is a reply to message #648118] Tue, 16 February 2016 16:48 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
There is an identity column already for the table. So, I think, I do not need to generate in the control file.
Thank You, Barbara.
Re: Convert DB2 load scripts to Oracle Load scripts [message #648143 is a reply to message #648119] Wed, 17 February 2016 12:26 Go to previous messageGo to next message
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 #648144 is a reply to message #648143] Wed, 17 February 2016 13:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I'm having issues loading below data.
ISSUES?
What Issues?
I don't see any issues.
Re: Convert DB2 load scripts to Oracle Load scripts [message #648151 is a reply to message #648144] Wed, 17 February 2016 13:52 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Record 1: Rejected - Error on table UNTK48.REPORTS, column DATE.
ORA-01747: invalid user.table.column, table.column, or column specification
Re: Convert DB2 load scripts to Oracle Load scripts [message #648152 is a reply to message #648151] Wed, 17 February 2016 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
DATE is an Oracle KEYWORD & should NOT be used as a column name!
Re: Convert DB2 load scripts to Oracle Load scripts [message #648153 is a reply to message #648152] Wed, 17 February 2016 14:01 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Yes, I found it too Smile
Thank You.

Is it necessary to give the data type in control file for startPosition & length ?
Re: Convert DB2 load scripts to Oracle Load scripts [message #648155 is a reply to message #648153] Wed, 17 February 2016 14:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
drevalla wrote on Wed, 17 February 2016 12:01

Is it necessary to give the data type in control file for startPosition & length ?


It isn't necessary, but it can't hurt here either. By default, anything for which a type is not declared is treated as CHAR. It will attempt an implicit conversion to any other data types. It will only produce an error if the data cannot be converted to the data type in the table.


Re: Convert DB2 load scripts to Oracle Load scripts [message #648156 is a reply to message #648155] Wed, 17 February 2016 14:18 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 #648157 is a reply to message #648156] Wed, 17 February 2016 14:50 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Barbara, There is a work around to fix this issue if we do not like to change the column name. For reserved words as column names we could enclosed them with "".
Re: Convert DB2 load scripts to Oracle Load scripts [message #648158 is a reply to message #648157] Wed, 17 February 2016 14:56 Go to previous messageGo to next message
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 #648166 is a reply to message #648158] Wed, 17 February 2016 20:19 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Barbara, Is there any load command to clear the table data?

Below is the command in DB2 for clearing the table data.

import from /dev/null of del replace into NOTES;
Re: Convert DB2 load scripts to Oracle Load scripts [message #648167 is a reply to message #648157] Wed, 17 February 2016 20:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
drevalla wrote on Wed, 17 February 2016 12:50
Barbara, There is a work around to fix this issue if we do not like to change the column name. For reserved words as column names we could enclosed them with "".


Yes, but then everywhere you reference that column name in any code, you will need to make sure that it is in the right case, and enclosed in double quotes. It is considered a very bad practice to use reserved words as column names or table names or names for any other objects.
Re: Convert DB2 load scripts to Oracle Load scripts [message #648168 is a reply to message #648166] Wed, 17 February 2016 20:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
drevalla wrote on Wed, 17 February 2016 18:19
Barbara, Is there any load command to clear the table data?

Below is the command in DB2 for clearing the table data.

import from /dev/null of del replace into NOTES;


Use REPLACE instead of APPEND in your control file.


Re: Convert DB2 load scripts to Oracle Load scripts [message #648170 is a reply to message #648167] Wed, 17 February 2016 21:43 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Actually, that is the reason, I looked for workaround. The 'date' field is already used in other applications.
Re: Convert DB2 load scripts to Oracle Load scripts [message #648171 is a reply to message #648170] Wed, 17 February 2016 21:47 Go to previous messageGo to next message
drevalla
Messages: 44
Registered: February 2016
Member
Using Replace will delete the records and again adds the records, right ?
So, I have to send empty data file ?
Re: Convert DB2 load scripts to Oracle Load scripts [message #648173 is a reply to message #648171] Wed, 17 February 2016 22:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
drevalla wrote on Wed, 17 February 2016 19:47
Using 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 Go to previous message
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)
.........
..........
Previous Topic: Data Pump throwing errors
Next Topic: Import DMP file to new Table
Goto Forum:
  


Current Time: Fri Mar 29 03:09:17 CDT 2024