All Forums Tools
teradatauser2 236 posts Joined 04/12
14 Aug 2012
Insert select in Mload

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.

 

ulrich 816 posts Joined 09/09
14 Aug 2012

Is table B empty?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

dnoeth 4628 posts Joined 11/04
15 Aug 2012

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

Dieter

teradatauser2 236 posts Joined 04/12
15 Aug 2012

Yes the table B is empty.

teradatauser2 236 posts Joined 04/12
15 Aug 2012

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,

teradatauser2 236 posts Joined 04/12
15 Aug 2012

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.
 

feinholz 1234 posts Joined 05/08
15 Aug 2012

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.

--SteveF

teradatauser2 236 posts Joined 04/12
15 Aug 2012

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)

feinholz 1234 posts Joined 05/08
15 Aug 2012

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.

--SteveF

teradatauser2 236 posts Joined 04/12
15 Aug 2012

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

dnoeth 4628 posts Joined 11/04
16 Aug 2012

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

Dieter

feinholz 1234 posts Joined 05/08
16 Aug 2012

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.

 

 

--SteveF

Satyaki.De 4 posts Joined 08/12
08 Oct 2012

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

ratnamch 14 posts Joined 06/12
13 Oct 2012

 
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

vasudev 24 posts Joined 12/12
25 Oct 2013

Hi,
I am having a requirement like this. Need to load the data from file to sales table, which dont have any additional index.
Can i load directly from file to table using MLOAD.
Or, can i load from file to staging table using FLOAD and then use Insert select to load the sales table.
Which one is better in terms of performance.
Please help !!!!
Thanks in advance.

Raja_KT 1246 posts Joined 07/09
25 Oct 2013

Hi,

 

I am trying to answer based on your question. In fact, there are various technical and business implications when you decide :).

 

It depends on the table structure, data source and its size.However, a load to normal empty table with say 65 million records from a file when fload will be around 1 hour say and with mload it is less than 2 hours, my experience. But still it depends on data too, how skewed they are...blah blah..blah.

 

Your question is STRICLTY performance: So for your kind of data, table structure and (business) requirement and infrastructure you have,resource allocations etc, you can gauge both options.

 

Say your option to load to staging using fload and then use insert-select is a two-stage process.See your maintenance cost if it fails :). This option since it is a two step process is close to mload. 

 

The option of direct mload is also a good choice. 

 

 

Please vote for my new  marketing and consulting concept and  trademark "Know what you want and Know what you have" to provide cost-saving solutions for  companies  implementing DWH/BI projects :)

 

Cheers,

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.