Is table B empty?
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
MLoad Inserts are row by rown no Insert/select.
And when you use Insert/Select outside of BEGIN/END MLOAD (= within Support Environment) it's like any SQL usiing the Transient Journal. That's why Ulrich asked if the table is empty, then it would be a Fast Path Insert Select without journaling.
When you expect errors you might also try to create an Error Table for the target and Insert/Select using the LOGGING ERRORS option to avoid a rollback.
Dieter
Yes the table B is empty.
Hi dnoeth,
Could you explain you answer in detaiil.
We are planning to use Mload as the table is very big. Mload would avoid journaling and in case aborted, the rollback would not have to happen.
Could you please share the code snippet for this.
I read through the available material, and all say that select is not allowed in the mload. So, i am little confused,
I tried the way you suggested..i am getting the below eror
#!/bin/ksh
mload <<EOF
.Logtable SAM4;
.LOGON id/pass;
.BEGIN MLOAD
Tables xx.TEST2 - my target table
WORKTABLES yy
ERRORTABLES zz bb;
;
.END MLOAD;
.CREATE ERROR TABLE TRAN_ERR FOR test2;
insert into test2
sel * from test;
.Logoff;
.exit
EOF
Error:
===
**** 18:17:26 UTY0830 Attempting to restart this MultiLoad import task from the application
phase.
**** 18:17:26 UTY0831 This MultiLoad import task was newly started or in the preliminary phase
and not in the application phase as expected, terminating.
General statement:
SQL statements cannot start with a period (".").
So, the CREATE ERROR TABLE statement should not have the period.
However, you are trying to use MultiLoad in a way that is not intended.
If you want to issue an INSERT-SELECT, use BTEQ.
Your MultiLoad script has no .IMPORT statement to run the acquisition phase of the MultiLoad job.
I tied this code as suggested ny Dnoeath. He suggested to do an insert - selct after the .end mload commad (Please refer to my initial question: i inted to use an insert-select in Mload)
I have read all of the posts here and I will repeat: you are trying to use MultiLoad in a way that is not proper.
MultiLoad is a load tool to provide the capability of performing insert, updates and deletes for 1-5 tables.
MultiLoad is expecting the data to come from flat files, INMODs, or Access Modules.
It is not intended for performing actions such as INS-SEL.
If you would like to use MultiLoad to load data from, say, a flat file, into a staging table first (and that type of task should really be performed by FastLoad), and then follow that up with an INSERT-SELECT, that might be possible (never tried it).
But for what you are trying to do, MultiLoad is not the correct tool.
You may want to "intend to use an insert-select in MLoad", but I am not sure why and that is not what MultiLoad is for.
Hi feinholz,
I totally agree with you.
However, i was told by my lead that pls use Mload for insert-select , which i am sure is not the right use of mload, but i am trying to know if that is really possible (even if in some wierd way!!) . Thing is that i can't go and ask my lead for the script and can't say no unless i am 100% sure of it !! Hope you understand
You should ask your lead why he insists on using MLoad for Insert/Select, maybe he doesn't know what he's talking about :-)
Are there any other steps within that process?
How do you submit them? Just add the Insert/Select to that script.
If you still need to use MLoad then remove the BEGIN/END MLOAD and anything between. Then it's Support Environment only and no MLoad stuff at all.
Dieter
I agree with Dieter. Ask your lead. MultiLoad is not intended for issuing insert-select statements.
You should be using a tool like BTEQ for that type of operation.
The one area where I disagree (as a viable solution) with Dieter is this:
"If you still need to use MLoad then remove the BEGIN/END MLOAD and anything between. Then it's Support Environment only and no MLoad stuff at all."
Yes, there will be no MLoad stuff at all, but you will be using a up a valuable load slot for a SQL-only operation. You will be connecting MultiLoad data sessions for no reason at all. That uses up sockets and other system resources that are unnecessary.
Guys,
I would like to know one thing.
Generally, in other DB - DDL statement is faster than any other DML statment due to it's logging mechanism. So, if op's table can be dropped & recreate as any intermediate staging tables then we can use the following statment.
create table scott.tt_emp
as
(
select *
from scott.emp_child
) with data;
Won't this be faster than the given one?
INSERT INTO scott.tt_emp Select * FROM scott.emp_child;
If there is no reference constraint imposed with the given table, in that case we can use this.
Kindly share your opinion.
Regards.
SD
Hello Experts,
Whenever we use to do fastload amp & disk skewness goes high to 98% on every run. INDEXES are properly defined on the target table
Please suggest what to do to avoid this
Many Thanks!
Regards
Hi,
I have a situation in which i have to insert into a table from another table. The size of the table is very huge (3+TB). Can i write a statement :
Insert into B
sel * from A
into mload and run.
I want to use mload as this will not have the problem of rollback if any issues in insert.
If someone has the code for this , then please share. It's really urgent.