Automic Workload Automation

Expand all | Collapse all

database schema for Prompt Sets

Jump to Best Answer
  • 1.  database schema for Prompt Sets

    Posted 05-21-2019 11:59 AM

    where can I find the Prompt Sets in the database in order to create a report?



  • 2.  Re: database schema for Prompt Sets

    Posted 05-21-2019 12:17 PM

    select *
    from oh
    where oh_otype = 'PRPT'
    and oh_client = 100
    and OH_DeleteFlag = 0
    order by oh_name



  • 3.  Re: database schema for Prompt Sets

    Posted 05-21-2019 12:42 PM

    Thanks, that give me the names of the Prompt Sets.

    Another question is, can I list all the prompts inside each Prompt Set.

     

    Roger Shirley

    Ops Analyst

    702-281-7789



  • 4.  Re: database schema for Prompt Sets

    Posted 05-21-2019 12:52 PM

    select oh_name "Object Name", opse_name "Variable Name", opse_value "Value"
    from uc4.dbo.opse
    inner join uc4.dbo.oh on opse_oh_idnr = oh_idnr
    where oh_otype = 'PRPT' and oh_client = 100
    and OH_DELETEFLAG=0 and OH_REFIDNR=0
    order by oh_name, opse_name

     

    Also keep in mind that the values in these promptset objects are typically overridden in the parent objects that use them.



  • 5.  Re: database schema for Prompt Sets

    Posted 05-21-2019 02:03 PM

    Thanks, again

    I getting this error

    Query could not be executed: 'ORA-00933: SQL command not properly ended

     

    We have an Oracle database, I cannot find any reference to: uc4.dbo.opse

     

    Roger Shirley

    Ops Analyst

    702-281-7789



  • 6.  Re: database schema for Prompt Sets

    Posted 05-21-2019 02:05 PM

    Reduce the table names to just "oh" and "opse".



  • 7.  Re: database schema for Prompt Sets

    Posted 05-21-2019 04:47 PM

    This worked.

    select oh_name "Object Name", opse_name "Variable Name", opse_value "Value"

    from opse

    inner join oh on opse_oh_idnr = oh_idnr

    where oh_otype = 'PRPT' and oh_client = 693

    and OH_DELETEFLAG=0 and OH_REFIDNR=0

    order by oh_name, opse_name

     

    Now I would like to be able to list the Prompt Values that are used when the jobs run in process flow.

     

    Thanks,

     

    Roger Shirley

    Ops Analyst

    702-281-7789



  • 8.  Re: database schema for Prompt Sets

    Posted 05-21-2019 06:00 PM

    I have never accomplished what you are asking for.  Too complex.

     

    Say for instance you have a promptset that is used by a job that is used by a workflow that is used by another workflow that is used by a schedule.  Technically speaking the final value that is fed into the promptset at runtime could have come from any one of the objects in the mentioned parent/child relationships.

     

    On the other hand, maybe you'd like to go data mining in the run-history areas of the database?  Then you could capture what was actually used on the previous execution.  (This too is something I have never accomplished and would not be of much help.)



  • 9.  Re: database schema for Prompt Sets

    Posted 05-22-2019 11:17 AM

    Good morning Pete,

    thanks for all your help.

     

    Roger Shirley

    Ops Analyst

    702-281-7789



  • 10.  RE: Re: database schema for Prompt Sets
    Best Answer

    Posted 01-17-2020 11:58 AM
    Might these help?

    OH table with values:
    select OH_Client as OH_Client /* Number/ID of the client. */
    ,OH_OType as OH_OType /* Type of the object. */
    ,OH_Name as OH_Name /* Name of the object. */
    ,OH_Archive1 as OH_Archive1 /* Archive key #1 */
    ,OV_VName as OV_VName /* Name of the object variable. */
    ,OV_Value as OV_Value /* Value of the object variable */
    ,OH_CrDate as OH_CrDate /* Creation date of the object. */
    ,OH_Idnr as OH_Idnr /* ID of the object. */
    ,OV_OH_Idnr as OV_OH_Idnr /* ID of the object. */
    ,OH_DeleteFlag as OH_DeleteFlag /* Delete flag of the object. 0 = not deleted, 1 = deleted, 2 = versioned, 5 = archived/reorganized */
    ,OH_ExpFlag as OH_ExpFlag /* Marks object ready for transport/unload. 0 = not marked, 1 = object located in transport case */
    -- ,OPSEA_OH_Idnr as OPSEA_OH_Idnr
    -- ,OPSEA_OPSE_Name as name_of_promptset
    -- ,OPSEA_Value as prompt_value
    from OH, OV
    -- , OPSEA
    where oh_client = &$CLIENT#
    and oh_name like '&NAMEMASK#'
    and OH_OType like '&OBJTYP#'
    and oh_deleteflag = 0
    and OH_ExpFlag = 0
    and OH_OV_Flag = 1
    and oh_idnr = ov_oh_idnr
    and ov_vname like '&ScanVal#'
    order by oh_name
    ;

    AH table with values:
    select AH_Client as AH_Client /* Number/ID of the client. */
    ,AH_OType as AH_OType /* Type of the object. */
    ,AH_Name as AH_Name /* Name of the object. */
    ,AH_Alias as Name
    ,AH_Archive1 as AH_Archive1 /* Archive key #1 */
    ,AH_MaxRetCode as MaxRC
    ,AH_TimeStamp1 as activ_time
    ,AH_TimeStamp2 as start_time
    ,AH_TimeStamp4 as end_time
    ,AH_Queue as RunQue
    ,AH_Title as AH_Title /* description of the object */
    ,AH_Idnr as AH_Idnr /* ID of the object. */
    ,AV_AH_Idnr as AV_AH_Idnr /* ID of the object. */
    ,AV_VName as AV_VName /* Name of the object variable. */
    ,AV_Value as AV_Value /* Value of the object variable */
    ,AV_Source as AV_Source /* 0 own, 1 parent, 2 runtime mod */
    from AH, AV
    where Ah_client = &$CLIENT#
    and Ah_name like '&NAMEMASK#'
    and AH_OType like '&OBJTYP#'
    and Ah_idnr = Av_Ah_idnr
    and Av_vname like '&ScanVal#'
    order by AH_TimeStamp1, AH_Name
    ;

    Event Table with values:
    select EH_Client as EH_Client /* Number/ID of the client. */
    ,EH_OType as EH_OType /* Type of the object. */
    ,EH_Name as EH_Name /* Name of the object. */
    ,EH_Alias as Name
    ,EH_Archive1 as EH_Archive1 /* Archive key #1 */
    ,EH_ActivationTime as activ_time
    ,EH_StartTime as start_time
    ,EH_Status as job_status
    ,EH_Queue as RunQue
    ,EH_MaxRetCode as MaxRC
    ,EH_Title as EH_Title /* description of the object */
    ,EH_EV_Flag as EH_EV_Flag
    ,EH_AH_Idnr as EH_AH_Idnr /* ID of the object. */
    ,EV_AH_Idnr as EV_AH_Idnr /* ID of the object. */
    ,EV_VName as EV_VName /* Name of the object variable. */
    ,EV_Value as EV_Value /* Value of the object variable */
    ,EV_Source as EV_Source /* 0 own, 1 parent, 2 runtime mod */
    from EH, EV v
    where EH_client = &$CLIENT#
    and EH_name like '&NAMEMASK#'
    and EH_OType like '&OBJTYP#'
    and EH_EV_Flag = '1'
    and EH_AH_Idnr = EV_AH_idnr
    and EV_vname like '&ScanVal#'
    order by EH_ActivationTime, EH_name
    ;

    Prompt execution example of OH


    Be well,
    Phil