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?
How do you call a file that has SQL scripts
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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.
Could you build a stored procedure based on the commands? Then you could run the stored procedure as before or after sql.
Chuck Smith
www.anotheritco.com
www.anotheritco.com
Re: How do you call a file that has SQL scripts
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!
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!
Re: How do you call a file that has SQL scripts
I am currently using Oracle database....
RajaUsa75 wrote: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!
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
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
chucksmith wrote: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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers