Automic Workload Automation

When we do code promotions between clients (transport case export / import), we occasionally run into problems where we forgot to transport a random object or two from the old client.  
I've already got a query for recognizing missing objects within a workflow (and I'm happy to share that if there's interest), but that won't catch missing include objects.

Has anyone out there developed a SQL query (for v9) that we can use to catch references to include objects that don't exist in the current client?

Thanks in advance!

Disclaimer:
Ran against V9/MSSQL
Needs to be cleaned up for sure, and modified to make it more scalable/efficient (eg: use variables for the client number instead of hard-coding it, maybe use a function instead to hide all the replace and trim, etc).

See attached missingIncludeFile2.txt.
Sorry it's a bit messy - but hopefully you get the gist :smile:

PS. Attaching as .txt instead of .sql since .sql is not allowed

Good start!  

Should check for both INC and INCLUDE.  

And I wonder if it might throw off the query if an INC statement had optional parameters in play?  (Probably doesn't happen in typical solutions.)

petwir
The original one that I wrote checks for INCLUDE but I simplified it to make it easier to read and understand the logic. It should be easy enough to check for INCLUDE. The more 
read/user friendly" I think would be to use a UNION ALL (copy/paste the query and change INC to INCLUDE) or you can edit the query itself to check for INCLUDE.

The script considers Include statements with parameters. This is a bit convoluted.. anybody who have a cleaner script, please share :)

charindex(' ',ltrim(replace(ot_content, ':INC ','')),0) include_LEN, 

- replace :INC with ''
- remove any white space at the beginning
- search for the first occurrence of a white space, start at index 0

case charindex(' ',ltrim(replace(ot_content, ':INC ','')),0)

when 0 ...

Just found another bug on this.

If the Include line is :inc API.DATE.GET_DAY "VARIABLE" = "paypal_date#"
Then the inc_File is API.DATE.GET_DAY. 
Because there is a space between the actual Include file and the variable/parameter

If the Include line is :INC JOBI.PF.PREP
Then the inc_File is :INC JOBI.PF.PREP
Because the script does not find a white space after the actual Include file.

*** incorrect output ***

If the Include line is :INC JOBI.POST_PROCESS,nofound=ignore
Then the inc_File is JOBI.POST_PROCESS,nofound=ignore
Because the script does not find a white space after the actual Include file.

You could add another condition/logic that checks for comma (,) 
As mentioned, please update the script accordingly :)

It looks like this script would also need to handle a couple other things:
- case-insensitivity (e.g., ":inc <whatever>" and ":include <whatever>")
- indentation (e.g., ":      INC <whatever>")

I appreciate the script you posted, though -- it at least illustrates what tables I need to be looking at.

MSSQL is not case sensitive. For Oracle, you can add UPPER( ***). I think the indentation is handled by LTRIM or RTRIM in the script.

Hi daryl.brown_ACI, 

We've run into some requirements that your missing objects script may be helpful for. Would you mind sharing this/do you still have access to it? We would sincerely appreciate it, if so - thanks for taking a look!

We do this sort of validation during batch promotion, by examining the transport case file directly.

If this approach seems interesting, you’re welcome to take a look at the scripts we use. During batch promotion, we use the parse_transport_case.sh script to, among other things, extract the entire content of the scripting tabs of all executable objects in the file. We then examine the scripting tab content using other scripts, including a script that includes some regular expressions that are helpful for identifying :INCLUDE statements in AE scripting tabs and extracting the name of the referenced JOBI object for each.

Here is an example based on an excerpt of that script:

regex="((^$)|(^\!.*$)|(^:.*$)|(^ *$))"
if [[ "${OT_Content}" =~ $regex ]]; then # AE JCL line
  echo "AE script line               -> ${OT_Content}"
  regex2="^(: *INC|: *INCLUDE).*$"
  if [[ "${OT_Content}" =~ $regex2 ]]; then
    echo ":INCLUDE statement           -> ${OT_Content}"
    JOBI_Name=$(echo "${OT_Content}" | awk -F'(: *INC |: *INCLUDE |!)' '{print $2}' )
echo "JOBI object                  -> ${JOBI_Name}"
 fi
fi


Knowing the JOBI object name, you could then check to see if the object is in the transport case file, or in the target AE system.

(I updated the awk command that extracts the JOBI name. It now ignores end-of-line AE scripting comments.)

Also, can you share the script for the workflow wit missing objects?

Hi Spencer, 

Here's the code we were using to identify missing objects.  (This only works *after* you've imported them to the new client (e.g., client 100 in this example) and only catches whether the objects are missing outright, not whether you've neglected to import an updated version of an object.)

-- check for invalid objects within workflows  (catch objects that do not exist in the client)

-- NOTE: this will *not* catch missing include objects

select oh_name,jpp_object from oh,jpp

where oh_idnr=jpp_oh_idnr and oh_client=100

and oh_refidnr=0 and oh_name like '{whatever}%' and jpp_otype not like '<%' and jpp_object not in (select oh_name from oh where oh_client=100 and oh_refidnr=0);