Getting data out of DS_AUDIT

Griffin07 · Post by **Griffin07** » Tue Mar 01, 2011 5:34 am

Hey folks, I am trying to get the last mod date and the last mod user from DS_AUDIT for a report. The below SQL is what I am using in a server job using an ODBC stage to connect to localuv. The job abends with the message below. I think its because the fields are multivalued and probally very long in some cases. Doees anyone know what options I have to get at this info. I really only want the last sub value. I tried making the column lenght large but that does nto work either

many thanks
Mark :D

DS_JobList1..Transformer_7.DSLink11: DSD.BCIGetNext call to SQLFetch failed.
SQL statement:SELECT INSTANCE, MODIFIER FROM DS_AUDIT
SQLSTATE=S1000, DBMS.CODE=930122
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: Row length exceeds buffer size.

DS_JobList2..Transformer_7.DSLink11: DSD.BCIGetNext call to SQLFetch failed.
SQL statement:SELECT INSTANCE, DTM FROM DS_AUDIT
SQLSTATE=S1000, DBMS.CODE=930122
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: Row length exceeds buffer size.

chulett · Post by **chulett** » Tue Mar 01, 2011 7:47 am

Have you tried using a UV stage rather than ODBC? Off the top of my head, there is an environment variable that may help with ODBC, something like MAXFETCHBUFFER ?? It's been mentioned here, perhaps a search would turn it up.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 01, 2011 3:21 pm

Code: Select all

SELECT INSTANCE, MODIFIER, MAX(DTM) AS MOST_RECENT_MOD
FROM UNNEST DS_AUDIT ON MODS
GROUP BY INSTANCE, MODIFIER;

111111111111111111 · Post by **111111111111111111** » Sat Jul 15, 2017 11:10 am

Ray: Does that works well for DS11.3? I used this query but it does not get the most recent results.

I had some jobs that were compiled yesterday (13-July) but this query got me the last timestamp as 31-05-17 which would probably the previous compile time of the job.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 17, 2017 3:55 am

DS_AUDIT is deprecated and not guaranteed to capture all changes. But it never caught compilations - that information is stored elsewhere - one of the records (the job record?) in RT_STATUSnnn if I recall correctly.

111111111111111111 · Post by **111111111111111111** » Mon Jul 17, 2017 5:10 am

Is there any other way to find out the last compile time of the job? Example: DS_JOBJECTS does provide things under its @RECORD<X,1> variable where X and Y has a wide range (1,1.. 26,1)

I couldn't see your content, am working with my organization for premium membership but need to work like this till then....