How do you call a file that has SQL scripts

[Mun]

I have a SQL file that has about 200 insert SQL statements. How do I call it from DataStage as a Before-Job routine or After-job routine to get the file from repository and run that file and load data into the database?

Currently, I have run the entire script in TOAD and execute as script and it populates correctly.

Any way to do the same in Datastage?

[chucksmith]

Can you build the insert statements into a .bat or .sh file? If so, you can execute the file from a job sequence using the ExecDOS or ExecSH routines.

Could you build a stored procedure based on the commands? Then you could run the stored procedure as before or after sql.

[RajaUsa75]

Mun... You didn't mention what database you are working with. You need to use command line database command to execute the SQLs kept in the file.

For example in UDB, you can use a shell script (say loadtbl.sh) as follows:

db2 connect to dbname user userid using password
db2 -tvf ASSETupd.sql > ASSETupd.log

(ASSETupd.sql is the file holding the SQLs to be executed and
ASSETupd.log is the file to store the results of SQL statements)

You may use the above script as a before or after job routine. You may have to find out the equivalent command line facility for other databases.

Good luck!

[Mun]

I am currently using Oracle database....

Mun... You didn't mention what database you are working with. You need to use command line database command to execute the SQLs kept in the file.

For example in UDB, you can use a shell script (say loadtbl.sh) as follows:

db2 connect to dbname user userid using password
db2 -tvf ASSETupd.sql > ASSETupd.log

(ASSETupd.sql is the file holding the SQLs to be executed and
ASSETupd.log is the file to store the results of SQL statements)

You may use the above script as a before or after job routine. You may have to find out the equivalent command line facility for other databases.

Good luck!

[Mun]

I thought about using Stored Procedure...a co-worker suggested placing EXECUTE IMMEDIATE before each of the insert statement in quotes but getting Oracle error such as


PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:

:= . ( @ % ; not null range default character
The symbol "." was substituted for "IMMEDIATE" to continue.

Or

PLS-00114: identifier 'INSERT INTO ..' too long

Can you build the insert statements into a .bat or .sh file? If so, you can execute the file from a job sequence using the ExecDOS or ExecSH routines.

Could you build a stored procedure based on the commands? Then you could run the stored procedure as before or after sql.

[ray.wurlod]

You're going to have to execute a different script from DataStage, one that establishes a sqlplus session and can itself run the SQL statements in the other file. Use the -s option for sqlplus.

[clshore]

Is your DS server running on UNIX or Wintel?

On UNIX I typed 'sqlplus -help', which gives you info for running from command line, such as this example:

sqlplus -S <userid>/<passwd>@<connect_identifier> @<scriptname>

The named script contains your multiple sql statements. Don't forget about handling commits/rollbacks, and of course, the exit statement at the end (else your sqlplus session will hang, waiting for more commands).

I strongly recommend that you capture the output by redirecting to a logfile, perhaps eliminating the -S option (sorry Ray, but when things go wrong, you need the detail).

Carter

[ray.wurlod]

Go wrong?!! Moi?!!

[chulett]

I agree - silent mode is not the way to go. The sqlplus session isn't all that good about handing back a meaningful exit status.

We always redirect sqlplus output to a file in /tmp with the current pid in the name to ensure uniqueness. It is then grep'd post session for things like 'ORA-' or 'SP2-' and if any number of those are found other than zero, we declare an emergency. We also cat the tmp file to standard out so the entire thing is captured in the calling job's log. Or you can just 'tee' off the redirected output, of course.