Harvest

Expand all | Collapse all

Tue Tip: SQL Script to Provide Report of all UDPs in SCM with DBrowse Utility

  • 1.  Tue Tip: SQL Script to Provide Report of all UDPs in SCM with DBrowse Utility

    Posted 06-06-2013 11:48 AM
      |   view attached

    Sometimes as a harvest administrator there may be a need to generate a custom report
    that provides a list of all UDPs that are in the SCM repository.

    Following is a script by Tom Cameron that generates such a report.
    Attached is a screenshot of running the report on DBrowse against an Oracle test
    scm database.

    You can get the DBrowse install zip file that is freeware from CA from here: DBrowse
    DBrowse is a database browser utility that allows a user to login and connect
    to the Harvest database. For Oracle, you will need to have the Oracle
    client installed and an odbc connection object created by the ODBC Manager
    utility (c:\windows\syswow64\odbcad32.exe). For SQL Server, you must have the

    SQL Server client installed and the ODBC connection object created through

    the same odbcad32.exe.


    Here is the SQL Script:

    select
    E.environmentname, S.statename, U.processname, '<Standalone>' as LINKEDTO,
    ' ' as PRELINK, U.udptype, U.programname, U.inputparm as INPUT from harudp
    U, harenvironment E, harstate S
    where U.stateobjid = S.stateobjid and S.envobjid = E.envobjid
    union select
    E.environmentname, S.statename, U.processname, P.processname as LINKEDTO,
    L.processprelink as PRELINK, U.udptype, U.programname, U.inputparm as INPUT
    from harudp U, harstateprocess P, harlinkedprocess L, harenvironment E,
    harstate S
    where U.parentprocobjid = P.processobjid and L.processobjid =
    U.processobjid and P.stateobjid = S.stateobjid and S.envobjid = E.envobjid
    order by 1,2,3,4,5,6;



  • 2.  RE: Tue Tip: SQL Script to Provide Report of all UDPs in SCM

    Posted 06-07-2013 05:09 AM
    Thanks Dave for this informative Tips.
    Regards,
    Ollivier