BookmarkSubscribeRSS Feed
strsljen
Obsidian | Level 7

Hi,

 

I need to add a row in existing Oracle table when SAS DI job starts (job name, timedate started, user who ran it...)

 

What would be a proper transformation to do so? Most of them expect input table and I really need only simple INSERT INTO .... statement (would like to avoid user written code if possible).

 

Thanks in advance!

 

Best regards,

 

 

--
Mario
9 REPLIES 9
Kurt_Bremser
Super User

Create a one-row table with your data from the existing oracle table:

data append_table;
if 0 then set oracle_table; /* this gets us all the variable attributes */
name = "&jobname";
/* and so on */
run;

Then use this table in an append transformation on the Oracle table.

LinusH
Tourmaline | Level 20

Since this is merely logging meta data I wouldn't necessarily create a transformation for it, since it doesn't tamper with the actual ETL data. So you could create a macro (assume you need this logging in several/all jobs), and call it from jobs pre-code, or even from SASBatchServer autoexec if you do it really generic...

Data never sleeps
AngusLooney
SAS Employee

Spooky!

 

Literally just wrote a macro for this, to go in the pre and post code of jobs (and nodes) to record "JobEvents" and log

 

things like user, job name, flow name, pid, lsfJobID, LSFFlowID, timesmap, username, compute hostname, metadata hostname etc.

 

The idea is to track job performance "mid-run" and also leverage things like the FlowID to allow multiple instances of the same flow to run happily synchronously, and to have a unique ID that is inherent across all jobs in the flow.

Here's the table properties:

 

Job Events Table.png

 

Invocation is 

 

%jobEvent(Event=Start) for example in the precode of the job, and %jobEvent(Event=End) in the post code,

and perhaps %jobEvent(Event=Start_Bulk_Load) and %jobEvent(Event=End_Bulk_Load) in the pre and post code of a heavy ligting table loader, for example.

 

The idea is the Event field is structured/categorical, and the Narrative field allows for semi-structured content, maybe like loop iteration counts, row counts, file sizes and names etc.

VenuKadari
SAS Employee

You could use Status Handling feature in SAS DI Studio.

Job -> Properties -> Status Handling Tab

New -> Select a condition (Perhaps "Send Job Status") 

For Action, select Custom

For the Value of Action Options, add an insert statement with the macro variables you want in the output

For example:

proc sql; insert into MYLIB.MyStatus_handlingTable set message="&SYSUSERID &SYSJOBID", datetime="&SYSDATE &SYSTIME"; quit;

 

 

AngusLooney
SAS Employee

Nice.

 

Mine is in effect slightly over engineered compared to that, but it does quite a bit more, and allows for mid-job events as well.

 

It grew out of a need for access to the LSF attributes of a job, the JobID and the FlowID of the instance of the flow the instance of the job is in, etc.

AngusLooney
SAS Employee

Have to agree with you on User Written code (see my DI Worst Practice in this Forum a few months ago), but a general purpose macro you can reuse is another matter.

strsljen
Obsidian | Level 7

@AngusLooney Thanks for this presentation. Now I am sure I am not the only one. 

If we ever meet f2f, beer is on me! 🙂

 

Trying to stop certain "influencers" in my company to convince the others that SAS DI is completely OK with bunch of User Written Code.

Even some SAS experts in the field (tbh, old school SAS coders tend to disrespect "modern graphical tools of the trade") think it's no  big deal.

 

But it is. It would give us nightmare in maintenance later and I am fighting very hard against it. 

 

As for my original question, macro function might be a nice and clean solution. Good advice! Unfortunately, I am far from expert in writing SAS macro functions. Will try to get help or it will really take time. 🙂

 

Best regards,

 

 

--
Mario
Patrick
Opal | Level 21

@strsljen

If you're using a macro then I'd implement this macro as a custom transformation. 

I personally "hate" pre- and post-code as it doesn't show in the job flow. So I rather have a custom transformation which I just place into my jobs wherever I need it.

 

I personally tend to implement by having the macro in an Autocall library and only have the macro call in the custom transformation as this allows me to eventually change macro code in a single place without the need to redeploy all the jobs using the custom transformation.

AngusLooney
SAS Employee

Definitely a fan of autocall macros, though there are pros and cons that are worth discussing at some point, wrt DI jobs etc.

 

One thing I really like about them is that you get the best of both worlds, you can use them in code AND in User Transforms, simultaneously without loss of control. I would suggest that best practice (or at least very good practice) is to treat UT as merely the design-time UI for calls to autocall macros.

 

Taking this approach, there's no reason not to use an explicit UT to host the calls to the likes of jobEvent, rather than putting the call into pre/post code route.

 

As you say, that avoids functionality being "hidden", and essentially, would be no less "efficient" (don't get me started on the over-obsession with "efficiency"), and you are sticking to the "it's obvious" principle,

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 3668 views
  • 1 like
  • 6 in conversation