Running SQL statements in datastage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
chandu123
Premium Member
Premium Member
Posts: 40
Joined: Mon Sep 12, 2016 1:26 pm

Running SQL statements in datastage

Post by chandu123 »

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);
HB
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Toad.

You don't need a job to do that.
-craig

"You can never have too many knives" -- Logan Nine Fingers
leandrohmvieira
Participant
Posts: 44
Joined: Wed Sep 02, 2015 7:19 am
Location: Brasilia, Brazil

Post by leandrohmvieira »

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.
Leandro Vieira

Data Expert - Brasilia, Brazil
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
johnboy3
Premium Member
Premium Member
Posts: 52
Joined: Fri Jun 19, 2015 2:48 pm
Location: Jackson, MS, USA

Post by johnboy3 »

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
john3
----------------------------------------------------
InfoSphere 8.5.0.2; DataStage 8.5.0.0; OS-RHEL 6.6; DB-Oracle Enterprise Edition 11g (11.2.0.4)
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

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.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

asorrell wrote:The stored procedure can be parameterized to accept file names for flexibility.
Table names... and so can the Before SQL. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
chandu123
Premium Member
Premium Member
Posts: 40
Joined: Mon Sep 12, 2016 1:26 pm

Post by chandu123 »

If there was a stage called SQL in Datastage, that would have been more awesome for this requirement. However I have implemented Craig's solution of running SQL in Before/After SQL section with zero source records. It worked. thanks!
HB
Post Reply