UNIX for Advanced & Expert Users

Hi,

Please let me know if you have any thoughts on how to read a table that has all the oracle sql files or shell scripts at the job and step level to identify all the tables that does merge, update, delete, insert, create, truncate, alter table (ALTER TABLE XYZ RENAME TO ABC) and call them out separately to write to table and to a file as well. We have thousands of jobs and trying to write a script to gather all these details into a table and file.

Table Name: Steps

Code:

JOB_ID	STEP_ID	Script_Name
12345   	1	                xfer_process.sh
12345     2	                merge_process.sql
12345     3	                load_step.sql

Table Name: Schedule

Code:

JOB_ID SCHEDULE
12345      DAILY 8 AM
5678       EVERY WEDNESDAY 10 AM
8989       DAILY 4 PM

Output

Code:

JOB ID	STEP_ID 	Script_Name	Dependent_Job	Succeeding _Job	Dependent_Job_Schedule 	Succeeding _Job_Schedule	SCHEMA_XF Read	SCHEMA_XF Build	SCHEMA_XF Insert	SCHEMA_XF Update	SCHEMA_XF Delete	SCHEMA_XF Truncate	SCHEMA_XF Merge	SCHEMA_XF Swap

Column SCHEMA_XF Build means create table as select * from table a
Column SCHEMA_XF Swap means alter table a rename to b

Thank you

Moderator's Comments:

Please use code tags for output and code. Thanks!

Thanks for your question, but to be honest, it is not clear to us what you are asking, exactly, because we do not have the same level of familiarity with the task at hand, as you do.

Please provide more of a high level overview of what you are trying to accomplish. A diagram could be helpful as well.

Thanks.