session execution time.

[vikind]

Hi ,

I have a table in which i want to populate the session "run time."
to one of my target table fields in a mapping. I know that if i use workflows
i can get the start and end time between sessions but how do i populate that data
to my target fields or is there any other way..


thanks

 

[Sridharan]

You want to populate the Session Run Time for all the Sessions in a single table or for each Session you want to insert into different table...

Sri

 

[vikind]

I want to populate the Session Run Time for all the Sessions in
a single table. in each of my mappings i have a field audit key for all
my target tables so when the mapping runs i want to assign a value for audit key =1
next time if some other mapping is run its audit key value shud be 2 so each
time either the same or new mapping is run the audit key value shud increment by 1.

I have a seperate mapping for populating the session run time i.e for the AUDIT table

my Audit table shud contain record like

SESSION_NAME | AUDIT_KEY | RUNTIME

Mapping_1 1 01:23:00 (first time)
Mapping_2 2 00:45:00
Mapping_3 3 00:22:00
Mapping_1 4 00:34:00 (second time)
Mapping_5 5 00:12:00


how can i acheive this..as i have to associate the audit key with my session or mapping name and also get the
run time for that session all in one table. As in the above case target tables for mapping 1,2,3 have the same
audit key as in audit table.

thank you for your help!

 

[blom0344]

I think your are trying to invent the wheel here. The repository stores tables that log start and stop times for each session (at least in version 5 it does)

You could easily create a mapping on the repo table or create a trigger that writes information to a table after each insert in the logging table.

As an example, this is a query (DB2) I use for reporting on the repository(version 5.1.2 repo):

Select A.SESSION_ID,SESSNAME,B.TARGET_ID,C.TARGET_NAME,SUCC_ROWS,FAIL_ROWS,LAST_ERROR,
RANK() OVER ( PARTITION BY A.SESSION_ID ORDER BY SESSION_TIMESTAMP DESC) AS #RANK,
DATE(SESSION_TIMESTAMP) AS #DATE,TIME(SESSION_TIMESTAMP) AS #TIME,TIME(ACTUAL_START) AS #START,
TIMESTAMPDIFF(4,(CHAR(SESSION_TIMESTAMP-ACTUAL_START))) AS #DIFF,D.SUBJ_NAME AS SUB_NAME,SUBSTR(CHAR((TIME(SESSION_TIMESTAMP))),1,2) AS AMPM FROM
OPB_SESSION_LOG A,OPB_LOAD_SESSION B,OPB_TARG C, OPB_SUBJECT D
WHERE A.SESSION_ID = B.SESSION_ID AND B.TARGET_ID = C.TARGET_ID AND
D.SUBJ_ID=B.SUBJ_ID AND DATE(SESSION_TIMESTAMP) >= @Variable('Enter date YYYY-MM-DD')

T. Blom
Information analyst
tbl@shimano-eu.com

 

[vikind]

Hi blom,

thanks for your suggestion. But i am still confused regarding one thing.
Now since i want the audit key in the MEMBER table and AUDIT table to be in sync.

ie.. say after Mapping_1 is run the audit key in MEMBER table is 1 then the same value should
go for audit key in the AUDIT table in a seperate mapping.

From what you have suggested i can write a stored procedure such that I first run my MEMBER mapping
with a default value for AUDIT key and then immediately run the AUDIT mapping using REP_SESS_LOG with default value for
audit key then i can have a POST session sql such that it will update the table MEMBER and AUDIT with
the audit key value and increment it by 1 everytime the MEMBER mapping is run. this wud require all the
workflows to run AUDIT mapping and then a post session sql to update the audit key with the new incremented value.

is this approach right as i want to make it generic enough so that if there are 10 people running
different Mappings each time a mapping is the run the audit key shud increment.
that would require everybody to run a sepearte audit mapping and post sql after their individual mappings
are run.


MEMBER table:

MEMBER_ID | AUDIT_KEY | COST



AUDIT table:

AUDIT_KEY | MAPPING_NAME | RUNTIME


INSURER table:

INS_ID | AUDIT_KEY | Region


its mapping wud be like REP_SESS_LOG --> EXP -- > AUDIT (target)

 

[blom0344]

In my humble opinion you are attempting to CREATE metadata on session execution and I am trying to point out to you that such information already exists in the repository....

However, I base this on the version 5.1.2 repository, so things may be different with version 6. This being a monday morning I fail to grasp what you are trying the achieve with your AUDIT structure....

T. Blom
Information analyst
tbl@shimano-eu.com