I want create a table and load the data from another table on the same database. Hence I am planning to run SQL query as below. Which stage or method should I use to perform this? Please suggest.
Create table Table1 as (Select * from Table2);
Running SQL statements in datastage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 44
- Joined: Wed Sep 02, 2015 7:19 am
- Location: Brasilia, Brazil
IF this is a one time operation, craig got it.
But assuming that you want to schedule this, just build a simple job like this:
1-import table definition of table2
2- Build this:
connector------->transformer-------->connector
3- On connectors, load table2 definition.
4- On source connector, set generate SQL to yes, then write table2 on table name
4- On target connector, write table1 on table name, and change table action to create
5- on transformer there is a automatic correpondency button, just press it.
6- compile, run.
7- after this just change target table action to truncate and recompile.
This can be done in five minutes and i dont think there is a faster way.
But assuming that you want to schedule this, just build a simple job like this:
1-import table definition of table2
2- Build this:
connector------->transformer-------->connector
3- On connectors, load table2 definition.
4- On source connector, set generate SQL to yes, then write table2 on table name
4- On target connector, write table1 on table name, and change table action to create
5- on transformer there is a automatic correpondency button, just press it.
6- compile, run.
7- after this just change target table action to truncate and recompile.
This can be done in five minutes and i dont think there is a faster way.
Leandro Vieira
Data Expert - Brasilia, Brazil
Data Expert - Brasilia, Brazil
Keep in mind, though, that would take all of the records from the first table and shoot them across the network to the engine and then back across to the same instance it came from to load the table. There are ways to emulate the original "do all the work in the database" technique that the original post showed and something I do in other tools when I really don't want or need all that "back and forth".
Think about doing something to get that SQL into your target connector then have a source that generates zero records. In Oracle I would do something like "select 1 from DUAL where 1=2". Your target can be any table set to almost any action because you're not going to send any records to it. Then you can put your SQL from the original post in whatever passes for the "before or after SQL" portion of the connector. Job starts, connections are established, before SQL runs, job processes zero records and is done.
Me, I'd still just use Toad. Or have a DBA run it. They love doing stuff like that.
Think about doing something to get that SQL into your target connector then have a source that generates zero records. In Oracle I would do something like "select 1 from DUAL where 1=2". Your target can be any table set to almost any action because you're not going to send any records to it. Then you can put your SQL from the original post in whatever passes for the "before or after SQL" portion of the connector. Job starts, connections are established, before SQL runs, job processes zero records and is done.
Me, I'd still just use Toad. Or have a DBA run it. They love doing stuff like that.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Oracle's SQL Developer lets you right-click and select TABLE and COPY to quickly copy one table to a new table. There is a check-box for if you want data.
I just tested and it seemed to be reasonable speed-wise?
john3
I just tested and it seemed to be reasonable speed-wise?
john3
john3
----------------------------------------------------
InfoSphere 8.5.0.2; DataStage 8.5.0.0; OS-RHEL 6.6; DB-Oracle Enterprise Edition 11g (11.2.0.4)
----------------------------------------------------
InfoSphere 8.5.0.2; DataStage 8.5.0.0; OS-RHEL 6.6; DB-Oracle Enterprise Edition 11g (11.2.0.4)
If you want to automate it in DataStage and still keep all the data transfer in Oracle (for best performance):
1) Create a quick stored procedure that will handle it in Oracle and then
2) Use a stored Procedure stage in DataStage to kick it off when you need it.
The stored procedure can be parameterized to accept file names for flexibility.
1) Create a quick stored procedure that will handle it in Oracle and then
2) Use a stored Procedure stage in DataStage to kick it off when you need it.
The stored procedure can be parameterized to accept file names for flexibility.