Unique job run id

dataservices job design
1

hi
We want to do job tracking. I want to know how can i attach unique job runid for each file i generated during the job execution like source ,target files,staging tables,log files,moniter files etc.
thks

msainath (BOB member since 2008-05-01)

2

I looked at that myself recently and did not find any way.
The RUNID used in the AL_HISTORY table is based on multiple things, but very likely the date_time portion is enough to make something unique with a good enough probability.
The way I thought getting the timestamp would be by using the get_tracelog_filename() function and substring the datetime out of it. But it is off by a second often, so was of no use.

Only way I could think about is checking what the last job run was and associate that with the last dataflow. e.g. in a first script perform a query against the AL_HISTORY repo table and read the latest row, assuming this is the job currently runinng.

Werner Daehn :de: (BOB member since 2004-12-17)

3

A suggestion…

An option when you are unlikely to have jobs starting simultaneously

Use an external table to record ‘runs’ and create a new record whenever you are starting a clean new run and increment an ID field (Oracle sequences, or SQL Server identities are great for this.

Once the record is created log the new id into a global variable which you can reference whenever you want.

dfoster (BOB member since 2008-01-08)

4

ADAPT01072946: get_RUNID() function required.

@dfoster: Yes, that would work. But still having the function would be nice, wouldn’t it?

Werner Daehn :de: (BOB member since 2004-12-17)

5

Thanks, Werner. We tried to solve this problem three years ago and ended up with a job runs table that generates a unique Process ID for each job. The run id will simplify things. The table does work, though, if anyone needs the code let me know and I can facilitate.

  • Ernie

eepjr24 :us: (BOB member since 2005-09-16)

6

Hi Ernie
can you post your code to capture job_run id for each job run,

I was trying to acess from AL tables about job runid and job run statics.
but i am getting many duplicates and the time given in Al tables are per each data flow not for whole complete job.
please share your thoughts.
thks

msainath (BOB member since 2008-05-01)

7

Sure. We put writes to a job execution table in our start and end scripts for every job. The relevant bit is below. We use a combination of the job name (n_job) and a process id (i_proc) for a unique job run identifier. The load type tells us what we are doing (INCR = incremental run).

# Get new starting extraction date based on last run
if (Upper($I_Type_Load) = 'INCR')
begin
   $T_Star = NULL;

   # Last job run completed successfully.
   if (Upper($I_Job_Stats_Init) = Upper('Done'))
   begin
      # $T_Star is set to T_End for previous job if available.
      $T_Star_Str = sql('STAGING', 'select to_char(T_End, \'YYYY.MM.DD hh24:mi:ss\') from tbl_Exec_Job where N_Job = {$N_Job} and I_Proc = (select MAX(I_Proc) from tbl_Exec_Job where N_Job = {$N_Job} and T_End is not null)');
      $T_End_Str  = to_char(sysdate(),'YYYY.MM.DD hh24:mi:ss');
      $I_Proc     = sql('STAGING', 'select MAX(I_Proc) from tbl_Exec_Job where Upper(N_Job) = Upper({$N_Job})');
      $I_Proc     = $I_Proc + 1;
      $T_Load     = sysdate();

      If (Upper($F_Debg_Glob) = 'Y')
      begin
         print( '[$N_Job] | [$N_Objc_Exec_Glob] - I_Proc:........[$I_Proc]');
         print( '[$N_Job] | [$N_Objc_Exec_Glob] - T_Load:........[$T_Load]');
         print( '[$N_Job] | [$N_Objc_Exec_Glob] - T_Star:........[$T_Star_Str]');
         print( '[$N_Job] | [$N_Objc_Exec_Glob] - T_End:.........[$T_End_Str]');
      end
   end

   # Last job run failed.
   if (Upper($I_Job_Stats_Init) = Upper('Failed'))
   begin
      $T_Load_Str  = sql('STAGING', 'select to_char(T_LOAD,\'YYYY.MM.DD hh24:mi:ss\') from tbl_Exec_Job where N_Job = {$N_Job} and I_Proc = (select MAX(I_Proc) from tbl_Exec_Job where N_Job = {$N_Job})');
      $T_Star_Str  = sql('STAGING', 'select to_char(T_STAR,\'YYYY.MM.DD hh24:mi:ss\') from tbl_Exec_Job where N_Job = {$N_Job} and I_Proc = (select MAX(I_Proc) from tbl_Exec_Job where N_Job = {$N_Job})');
      $T_End_Str   = sql('STAGING', 'select to_char(T_END,\'YYYY.MM.DD hh24:mi:ss\') from tbl_Exec_Job where N_Job = {$N_Job} and I_Proc = (select MAX(I_Proc) from tbl_Exec_Job where N_Job = {$N_Job})');
      $T_Load      = sysdate();
      $I_Proc      = sql('STAGING', 'select MAX(I_Proc) from tbl_Exec_Job where N_Job = {$N_Job}');

      If (Upper($F_Debg_Glob) = 'Y')
      begin
         print( '[$N_Job] | [$N_Objc_Exec_Glob] - I_Proc:........[$I_Proc]');
         print( '[$N_Job] | [$N_Objc_Exec_Glob] - T_Load:........[$T_Load]');
         print( '[$N_Job] | [$N_Objc_Exec_Glob] - T_Star:........[$T_Star_Str]');
         print( '[$N_Job] | [$N_Objc_Exec_Glob] - T_End:.........[$T_End_Str]');
      end
   end

eepjr24 :us: (BOB member since 2005-09-16)

8

Hi Ernie
thanks for your quick reply .I just want to know what changes i have to make to apply this code in my DI jobs.My job is not only get unique id but also get job stats like start time,end time ,row counts.

thks

msainath (BOB member since 2008-05-01)

9

Well, the job start and job end I record into my job execution table, so those are handled. Obviously I just gave you the code for the unique ID, but it looks like you want to use the AL tables to get other info. In that case, please do a search of the forums, this has been covered before in various posts, here is one:

  • Ernie

eepjr24 :us: (BOB member since 2005-09-16)