Harvest

 View Only
  • 1.  Database Schema

    Posted Nov 27, 2019 08:51 PM
    ​I am trying to get the list of all UDP properties specially content/value of Program from state of Project. If any one know where to find in Harvest schema.
    Thanks


  • 2.  RE: Database Schema
    Best Answer

    Broadcom Employee
    Posted Nov 28, 2019 01:10 PM
    Hi Ahmad,

    You will find the details of a UDP in HARUDP table in the HARVEST schema.

    The below query retrieves the list of all linked UDP processes along with its attributes (not all of them though).


    select he.environmentname, hs.statename, hu.processname,select he.environmentname, hs.statename, hu.processname, hu.programname, hu.inputparm, hu.description, hu.note, hu.udptype from harudp hu, harstateprocess hsp, harstate hs, harenvironment he  where hsp.stateobjid=hs.stateobjid  and hs.envobjid=he.envobjid  and hu.stateobjid=hsp.stateobjid  and hu.processobjid=hsp.processobjid;

    If you want to specify a specific project and state name, you may use the below query.

    select he.environmentname, hs.statename, hu.processname,select he.environmentname, hs.statename, hu.processname, hu.programname, hu.inputparm, hu.description, hu.note, hu.udptype from harudp hu, harstateprocess hsp, harstate hs, harenvironment he  where hsp.stateobjid=hs.stateobjid  and hs.envobjid=he.envobjid  and hu.stateobjid=hsp.stateobjid  and hu.processobjid=hsp.processobjid and he.environmentname = <projectname> and hs.statename = <statename>;


    Is that what you are looking for?


    Regards,

    Mallik.




  • 3.  RE: Database Schema

    Posted Dec 02, 2019 01:08 PM
    ​Hi Mallik,

    Thanks, this is exactly what I was looking for.


  • 4.  RE: Database Schema

    Broadcom Employee
    Posted Dec 02, 2019 10:38 AM
    Hello Ahmad,

    Here's an SQL query that will give you the "program" field for every UDP in every project in your database (both the pre-link/post-link UDPs and the stand-alone UDPs):

      SELECT * FROM (
        SELECT
          HE.ENVIRONMENTNAME,
          DECODE(HE.ENVISACTIVE,'Y','ACTIVE','N','INACTIVE','T','TEMPLATE') ENVTYPE,
          HS.STATENAME,
          HSP.PROCESSNAME AS PARENTPROCESS,
          DECODE(HLP.PROCESSPRELINK,'Y','PRELINK','POSTLINK') LINKTYPE,
          HU.PROCESSOBJID,
          HU.PROCESSNAME,
          HU.PROGRAMNAME
        FROM
          HARUDP HU
          INNER JOIN HARLINKEDPROCESS HLP ON HLP.PROCESSOBJID = HU.PROCESSOBJID
            AND HLP.PARENTPROCOBJID = HU.PARENTPROCOBJID
          INNER JOIN HARSTATEPROCESS HSP ON HSP.PROCESSOBJID = HLP.PARENTPROCOBJID
            AND HSP.STATEOBJID = HLP.STATEOBJID
          INNER JOIN HARSTATE HS ON HS.STATEOBJID = HSP.STATEOBJID
          INNER JOIN HARENVIRONMENT HE ON HE.ENVOBJID = HS.ENVOBJID
        UNION
        SELECT
          HE.ENVIRONMENTNAME,
          DECODE(HE.ENVISACTIVE,'Y','ACTIVE','N','INACTIVE','T','TEMPLATE') ENVTYPE,
          HS.STATENAME,
          'STANDALONE' PARENTPROCESS,
          ' ' LINKTYPE,
          HU.PROCESSOBJID,
          HU.PROCESSNAME,
          HU.PROGRAMNAME
        FROM
          HARUDP HU
          INNER JOIN HARSTATEPROCESS HSP ON HSP.PROCESSOBJID = HU.PROCESSOBJID
            AND HSP.STATEOBJID = HU.STATEOBJID
          INNER JOIN HARSTATE HS ON HS.STATEOBJID = HSP.STATEOBJID
          INNER JOIN HARENVIRONMENT HE ON HE.ENVOBJID = HS.ENVOBJID
      ) RESULT
      ORDER BY ENVIRONMENTNAME, STATENAME, PARENTPROCESS, PROCESSNAME

    ------------------------------
    Melinda Skelton
    ------------------------------



  • 5.  RE: Database Schema

    Posted Dec 02, 2019 01:21 PM
    Melinda, yes this SQL script provide the full list.
    Thanks lot