How to execute SQL query in sequence.

[stiwari]

Hi All,

I am loading the data from difference schema using Sequence and in this i am using Startloop,Activity and Endloop to change schema names at run time.....the main thing is that i am droping the index of the target table before loading and creating after load for that i am using open and close command at job level.....but whenever it runs the job for the different schema it drops and recreate the index....i want to drop the index once and it should create index after loading all the data from all the schema.....is there any way to create index after the loop ends finally....



Thanks in advance

Sumit...

[chulett]

SQL script in the Execute Command stage.

[stiwari]

Can I directly put SQL query in Execute command?.The query that i want to run is as follows.

ALTER TABLE SCHEMA_NAME.TABLE_NAME DROP PRIMARY KEY.

Where exactly do i need to put this stage so that it executes only once.

Thanks
in advance

[chulett]

No, you need to create a script to 'wrapper' it and log into your command line sql tool of choice. Can't get more specific than that as you've haven't mentioned what database you are using.

As to the where, put it after the End Loop.

[sud]

Can I directly put SQL query in Execute command?.The query that i want to run is as follows.

ALTER TABLE SCHEMA_NAME.TABLE_NAME DROP PRIMARY KEY.

Where exactly do i need to put this stage so that it executes only once.

Thanks
in advance

Hey,

If you are trying to do the execution of the sequel just before a job, it is a good idea to put the sequel as an open command in a database stage within the job itself.

[stiwari]

Hey Sud,


Ya, i can use open command and close command. but the problem is different, Actually while loading the record i am using startLoop and Endloop....and suppose if i give that SQL command(which drops and create the primary keys) in the open and close command of jobs, then what it will do...whenever loop will pick the different schema name it will drop and recreate the primary key, which will reduce the performace... , I want to drop the primary key only once when Sequence will start the job...and after loading all the data from all the schemas it should recreate the primary key....thatwhy i want to use some thing in Sequence itself.........i don't have much knowledage of execute command and UNIX so can you plz give me the solution in detail.....


Thanks in advance

Sumit....

[ray.wurlod]

There are plenty of examples, which you could find in a search.

You use a UNIX editor, such as vi, to create a script that executes the appropriate command (for example sqlplus for Oracle) to issue the SQL statement against the database.

Use a chmod command to add execute permission to the script.

[jking123]

If you can afford the overead of another job you could use e.g. the Dynamic RDBMS stage to run a sql command/file in a job and call that from the sequence.