Run the Script from any stage by updating data from Oracle Table.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Run the Script from any stage by updating data from Oracle Table.
# 1  
Old 01-25-2017
Run the Script from any stage by updating data from Oracle Table.

I have 100 Scripts, each containing 10-15 SQL's in them.

Each Script run for 40 mins to 1 hour 30 mins.

In the event of Failure in any step, if i re-start the Script, it will start running from the beginning. Which is waste of time. So in order handle this, i made the script to run from the failed point or to run it from any SQL the user want, by passing an argumnet for the Script while running It from the linux server box.

For example: If the Script has failed at 8th Step, when i re-run it will run from 8th Step. But, if user wants to run it from 6th Step, he can give ./Script_name 6. This will make the Script to run from 6th Step.


But now, i want to make the user to control the Script by passing argumnets from a table in ORACLE database.

There should be one Row entry for each of the 100 Scripts in one Table.


Code:
SCRIPT_NAME	SQL_NO	STATUS
Script 1	        1	                C (for completed)
Script 2	        3                       E (for Error)

Everytime the Script runs, the Row associated for the Script Should get dynamically updated with the SQL number that just completed.

And if there is any failure on 6th Step, when its re-run, it should run from the 6th Step. But if the user wants run from 5th Step, he should go to table and update the table with SQL_NO as 5 and should re-run the Script. Then the Script should start executing from the SQL_NO 5.

Kindle help me with your valuable suggestions.


Thanks for your time and help.

Last edited by rbatte1; 01-25-2017 at 10:52 AM.. Reason: Added CODE tags for table display
# 2  
Old 01-25-2017
It sounds like you are trying to duplicate what the oracle job scheduler already does for free. If a tool already exists, reinventing it makes for problems down the road.

You will want to consider a daemon running in the background as the oracle user. The process checks the table, say once a minute to look for jobs to start.

But. If a data problem caused the job to fail in the first place, it will continue failing.
Unless a human intervenes to fix the issue.
So, you will need a table with date stamped status flags to indicate:

Code:
Successful completion - completed all jobs in one stream
Forced completion - mark this job stream as complete even though it failed
Failure pending restart - Got fixed please restart, Mr. job controller <- this one does what you asked
Failure - bombed, needs user attention - has sent an email
New job -  added every day/hour/week(?) by cron job to allow daily processing

Status should be indexed by some kind of unique job identifier, so that all New jobs when complete relates to one of the completion status steps. This is how the controller knows what to do. Plus, you can see repeated failures for the same job stream.

You need to add in the steps to the status 'Please restart me', so the daemon knows exactly what to do. Same is true for job properties like user/password/parameters

You also need something to notify users/support when a job bombs.

If you do not know enough to write a daemon, consider a set of shell scripts that are under the control of one master script which runs from crontab every minute or so. You can use the at or batch facility to do the submission.
This User Gave Thanks to jim mcnamara For This Post:
# 3  
Old 01-25-2017
If you are talking shell scripts, then you could have this structure:-
Code:
step=$1                         # Read step as first parameter
step="${step:=1}"               # Default to step one

until [ $step -gt 99 ]
do
 case $step in
   1) function_1 ;;
   2) function_2 ;;
   3) function_3 ;;
   4) function_4 ;;
 esac
 ((step=$step+1))
done

An alternate might be to set step and have an if ... then ... fi round each chunk of code like this:-
Code:
step=$1                         # Read step as first parameter
step="${step:=1}"               # Default to step one

if [ $step -le 1 ]
then
  function_1
fi

if [ $step -le 2 ]
then
  function_2
fi
:
:

They would sort of get over your request, but honestly this might just end up in a huge script trying to do too much in one go. You would be better to have a single script for each task you need to perform and keep it simple. Even better, write a utility script that takes an SQL deck as an argument and runs the appropriate code. You can then use a proper scheduler to set up dependencies, change the sequence of your code or whatever far more flexibly.

Of course, there is a cost to a proper scheduler and you would likely have to justify it. There are many out there on the market from those massively over-engineered but can run work on (just about) any platform to those that run on the local host only (although with a bit of ssh or rsh knowledge you can run processing elsewhere too)

At worst, I would at least suggest having a directory holding the scripts you want to run. At the start of the schedule, copy in each script, making sure each starts with a sequence number so that they naturally sort in the required order so, for example:-
Code:
01_01_01_script_to_setup_something
01_02_01_doing_this_bit_next
01_03_01_and_then_this_thing

This would allow you to insert an ad-hoc job if you need to, say 01_02_05_extra-bit_this_time

You then need a script to simply loop over the contents of the directory and (re)move files when the script completes error free. You would, of course have to document a recovery procedure if there was a failure and enable whomever to copy back an completed steps that needed to be re-run, say after a data restore and adjustment.

Your script to run the schedule could be pretty simple:-
Code:
for file in $dir/*
do
   $file
   RC=$?
   if [ $RC -eq 0 ]
   then
      mv $dir/$file $archive_dir/$file
      logger "Scheduled job complete: $file"
   else
      echo "Exciting $file with return code $RC"
      logger "Scheduled job failure: $file"
   fi
done


That said, it is a poor substitute for a proper scheduler. How complex a batch schedule do you think this might grow to? There could really be many ways to do this, so you really need to be certain of your logic of how it would need to flow, allow for exceptions or ad-hoc processes, recovery steps, restart intervention, data corrections, etc. etc.

Overall, I'd really suggest a proper 3rd party scheduler. Search for schedulers from CA, BMC, UC4, Axway, or a myriad of other software companies out there. They generally all allow you to manipulate the batch, restart from a specific point, bypass steps, run tasks in parallel and various other things. There may even be a decent freeware one that I'm not aware of - perhaps sourceforge or similar have built something.

Some are pretty, some are text only and very basic, but it depends what you need and what you can justify.


Robin
This User Gave Thanks to rbatte1 For This Post:
# 4  
Old 01-26-2017
Thanks for your response.. But i am intended in creating such Script.. To read the input from Oracle table and start from the seq no based on the input obtained from table.

---------- Post updated at 05:08 AM ---------- Previous update was at 05:03 AM ----------

Thank you @rbatte1



The case statement which you have mentioned looks feasible. But, if we default every input to 1, won't the the script run from the beginning??


if i give the input as 6, i the script should run starting only from the 6th Step till the end.

Please give your thoughts on this.

Regards,
KIRAN
# 5  
Old 01-26-2017
You would (re)start by running the script like this:-
Code:
$ my_script             # Run from the top, i.e. default is step 1
$ my_script 6           # Run from step 6 (or next step if 6 is not defined)

The default setting line means that if $step is not set then it to 1, so if it is already set, it retains that value. Write a little test script to try it out.

You could even have a step zero defined that is never run unless you force it.

Overall though, I would strongly suggest that you split it up and have one script for one task, then use a proper scheduler.




I hope that this helps,
Robin

Last edited by rbatte1; 01-26-2017 at 07:56 AM.. Reason: Closing good wishes
This User Gave Thanks to rbatte1 For This Post:
# 6  
Old 01-26-2017
Thanks Robin. Will try it out.
# 7  
Old 01-30-2017
This is to make my requirement more clear


For Example:

Let's say I have 5 queries in my Script
  1. create table abc
  2. create table xyz
  3. create table ghf
  4. touch kiran.ksh
  5. rm das.sh

Now, in the beginning of the Script, I will read an input from oracle table, which gives the SeQ_NO to execute.

Now when I get the seq number, I want to say in the Script to execute all the statements starting from the seq_no obtained.
(if the Seq_no obtained is 2, I should say in the Script to start from statement 3. If the input from the Table is NULL, then the Script should run from the beginning.)

My main requirement is that, I want to write command/ piece of code in the Script, which says start from statement number(based on what input we get from table) and execute till the end.


Please help me with your thoughts.

Last edited by rbatte1; 01-31-2017 at 12:47 PM.. Reason: Converted textual list to formatted numbered list and corrected spellings
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

I want a script to view the complete log information of data stage job from UNIX IBM AIX.

Hi, I am working on data stage 8.7 version and I want a script a to view the all log information of the data stage job from UNIX environment. Can you please help me out by give the script. Thanks in advance... (7 Replies)
Discussion started by: victory
7 Replies

2. Shell Programming and Scripting

Shell script to export data from Oracle table .

Hi, I want to write a shell script which will export data from oracle table . I don't want to save that data . I want the queries . Right now i am right clicking on the table and clicking on export as to my desktop . Please let me know if any one have any idea . (2 Replies)
Discussion started by: honey26
2 Replies

3. Shell Programming and Scripting

sending mail using Mailx with data from oracle table

Hi, i need to send emails to the mail ids i receive using the sql query . SELECT L.ALARM_DEF,L.CRITERIA,L.DISTANCE_METRE,L.EMAIL,L.LAC_ID,L.LAST_CHECK,L.RANGE, L.RESOURCE_MSISDN,LM.ADDRESS,LM.NAME FROM LANDMARK_ALARM_CONF l, LANDMARK lm WHERE L.LANDMARK_ID=LM.LANDMARK_ID AND... (0 Replies)
Discussion started by: aemunathan
0 Replies

4. Shell Programming and Scripting

select data from oracle table and save the output as csv file

Hi I need to execute a select statement in a solaris environment with oracle database. The select statement returns number of rows of data. I need the data to be inserted into a CSV file with proper format. For that we normally use "You have to select all your columns as one big string,... (2 Replies)
Discussion started by: rdhanek
2 Replies

5. Shell Programming and Scripting

Script not reading Data from oracle table properly

Hi, I have a case where i am connecting to the oracle DB and reading a column value.... Script is in production...it was running fine,,,but suddenly now some times its started giving pain.... Script runs dailly....but sometimes its reading data from Oracle DB and sometimes its not rading the... (2 Replies)
Discussion started by: Sagarddd
2 Replies

6. Shell Programming and Scripting

load a data from text file into a oracle table

Hi all, I have a data like, 0,R001,2,D this wants to be loaded into a oracle database table. Pl let me know how this has to be done. Thanks in advance (2 Replies)
Discussion started by: raji35
2 Replies

7. Shell Programming and Scripting

To load data from variable to oracle table ....???

Hi all, How to load variable value into Oracle table? I have created a file validation shell script. After the validation completes, i need to update a Oracle table with the variable value... Can someone help me how to do it? I have searched, but not able to get answer. i have 4 variables... (2 Replies)
Discussion started by: Amit.Sagpariya
2 Replies

8. Shell Programming and Scripting

To load data from variable to oracle table

Hi all, How to load variable value into Oracle table? I have created a file validation shell script. After the validation completes, i need to update a Oracle table with the variable value... Can someone help me how to do it? I have searched, but not able to get answer. i have 4... (1 Reply)
Discussion started by: Amit.Sagpariya
1 Replies

9. Shell Programming and Scripting

script for updating table using file(

Hi, Data file path (.txt) Control file(.ctl) I have delimited file(|). Sample data: 1|name|50009|DS24|0|12 2|name|30009|DS24|0|13 3|name|20409|DS24|0|14 4|name|20009|DS24|0|15 5|name|10009|DS24|0|16 I want to load this data into a oracle table (update and insert) Please help me... (1 Reply)
Discussion started by: unihp1
1 Replies

10. Shell Programming and Scripting

updating a column in oracle table using shell script

Hi friends, i am having a variable declared in .profile.i am changing its value in a shell script and then i am connecting to oracle and then from there i am calling a .sql called update.sql STATUS is the variable declared in the .profile =============================== if sqlplus <<END... (3 Replies)
Discussion started by: sveera
3 Replies
Login or Register to Ask a Question