Script Extract Update

I previously blogged about extracting the script from the repository in order to perform post processing. This code is nice but I've been driven to update the script for some specific version over version comparisons.
Recently I blogged about holding multiple repositories in the shared development database in order to compare components.
I decided that I need to bring these two ideas together: why can't I do code metric calculations across versions to see how much technical debt we are accumulating in our systems.

I've updated the sql which is now produces a much larger output (depending on how many repositories you have). To get the code out, I couldn't use SSMS so I used BCP:

bcp "exec SIEBEL_CODE_DUMP" queryout file.xml -w -S <> -T

 I created a stored procedure called SIEBEL_CODE_DUMP which could be called from BCP which used the queryout parameter to dump the script.
We have 10 repositories in our database and the output file was over 20MB.
This is the schema:

Obviously, I now have a very rich model from which to build metrics from. I've just scratched the surface, but I'm happy with what I'm getting. I'm thinking of doing dependency crawling, but that will involve some scripting and I have to see how much time that will take.

Preparing for deployment

Migration to production is always a heart wrenching affair. With some Siebel releases, with multiple developers coming and going it gets worse.

We have attempted to keep a fairly strict deployment method as we do deployments entirely by hand; our admins have decided not to use ADM for better or worse. We're a multilingual installation and I think there were issues in getting the LOV migration to go well. In any case, using ADM or not, managing deployment artifacts is always tricky and the more time between releases the situation can get very grave.

For the latest release we've been doing a significant overhaul of some processes that use workflow processes, unfortunately some cruft has crept into the repository with multiple versions of the same process marked as Completed without being updated to Not In Use. Add to that, but how do we tell the deployment team what to deploy?

We have a series of deployment artifacts that document what goes into each release (and is maintained in a version control system):

• Bill of Materials.docx - general document that holds a list of the specific files, images, etc that must be installed, instructions etc.
• Dispatch Rule Sets.xlsx - a spreadsheet with dispatch rules
• List Of Values.xlsx - a spreadsheet with the LOV changes (adds in green, updates in black, and deletions (deactivations) in red
• Siebel System Configuration.docx - the standard reference bible for our implementation. Includes the component configurations, workflow policies, repeating jobs, workflows, web services, etc. Basically, the reference guide to our environment

I was having trouble getting a good list of workflow processes that were added, updated and deactivated between this upcoming release and the last one, so I wrote some SQL that helped me come up with a proper list (and helped me clean up the WF repository).

The prerequisite to using these statements is to import the last release repository into the development database under a different name. We typically just increment the version number so we have these repositories to choose from:

EXPIRED WORKFLOWS BETWEEN REPOSITORIES

SELECT DISTINCT A.PROC_NAME
FROM S_WFR_PROC A
    INNER JOIN S_REPOSITORY B ON A.REPOSITORY_ID = B.ROW_ID
WHERE
    B.NAME = 'Siebel Repository 1.9'
    AND A.STATUS_CD = 'COMPLETED'
    AND A.PROC_NAME IN
    (
        SELECT DISTINCT PROC_NAME
        FROM S_WFR_PROC A
            INNER JOIN S_REPOSITORY B ON A.REPOSITORY_ID = B.ROW_ID
        WHERE
            B.NAME = 'Siebel Repository'
            AND NOT EXISTS
            (
            SELECT * FROM S_WFR_PROC A1
                INNER JOIN S_REPOSITORY B1 ON A1.REPOSITORY_ID = B1.ROW_ID
            WHERE
                B1.NAME = 'Siebel Repository'
                AND A1.PROC_NAME = A.PROC_NAME
                AND A1.STATUS_CD = 'COMPLETED'
            )   
    )

 NEW AND UPDATED WORKFLOWS BETWEEN REPOSITORIES

SELECT A.PROC_NAME, A.VERSION [v.NEXT], C.VERSION [v.1.9]
FROM S_WFR_PROC A
    INNER JOIN S_REPOSITORY B ON A.REPOSITORY_ID = B.ROW_ID
    LEFT OUTER JOIN
    (
        -- GETS MOST CURRENT VERSION
        SELECT A.PROC_NAME, A.VERSION
        FROM S_WFR_PROC A
            INNER JOIN S_REPOSITORY B ON A.REPOSITORY_ID = B.ROW_ID
            INNER JOIN
                (
                    SELECT PROC_NAME, MAX(VERSION) [VERSION]
                    FROM S_WFR_PROC A1
                        INNER JOIN S_REPOSITORY B1 ON A1.REPOSITORY_ID = B1.ROW_ID
                    WHERE
                        B1.NAME = 'Siebel Repository 1.9'
                    GROUP BY A1.PROC_NAME
                ) C ON C.PROC_NAME = A.PROC_NAME AND C.VERSION = A.VERSION
        WHERE
            B.NAME = 'Siebel Repository 1.9'
            AND A.STATUS_CD = 'COMPLETED'
    ) C ON A.PROC_NAME = C.PROC_NAME
WHERE
B.NAME = 'Siebel Repository'
AND A.STATUS_CD = 'COMPLETED'
AND A.PROC_NAME LIKE 'ABC%'
AND (A.VERSION != C.VERSION OR C.VERSION IS NULL)
ORDER BY A.PROC_NAME

Search Criteria and index field order

I was working on some complex workflows the other day and after several days I completed the logic, ran the simulator a thousand times and was finally satisfied with the outcome.

Then I deployed it into test...performance was terrible.

I checked the indexes on the S_EVT_ACT table and found a custom index that matched the columns in the query. We had added a new column called "X_CHANNEL" to indicate how the activity was completed (email, fax, phone, etc). My query was looking for [Status] = 'Done' and [Channel] = 'Email' (with a sort on the started date).
Everything should be running fine...after scratching my head for quite a while, creating and dropping indexes on the table.

The existing (custom) index on the table was:

CREATE NONCLUSTERED INDEX [ABC_S_EVT_ACT_X9_X] ON [dbo].[S_EVT_ACT]
(
    [X_CHANNEL] ASC,
    [EVT_STAT_CD] ASC,
    [TODO_ACTL_START_DT] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

I changed the order of the search expression to [Channel] = 'Email' and [Status] = 'Done', and the query took 62ms instead of 5 minutes (300000ms). I had never put much thought into the order of the fields in the index until now. I'll be keeping that in mind from now on...

Workflow runtime events

A lot of information is available in bookshelf and online regarding workflows and runtime events so I won't cover that again. I would like to share a diagram I created for a co-worker that is getting up to speed on Siebel workflows and run-time events.
I was trying to explain how the same workflow needs to be duplicated in order to capture the same run-time event but under different business objects.

 

Hope someone finds this helpful.

Drilldowns using Minibuttons on form applets

I was looking for a convenient way for users to navigate to the service request from an activity form applet. From a list applet a drill down is a very effective tool for users to navigate - the breadcrumb provides context and quick backwards navigation, but how can we use drill downs from a form applet?

This is the final product:

Obviously, using scripting we can use GoToView, but I wanted to create a code free solution, so I used a "Named Method". On my form (which is based on the Action bc)

Create two user properties on the applet:
Name: CanInvokeMethod: GoToSR
Value: [SR Number] is not null

Name: Named Method: GoToSR
Value: 'INVOKE', 'DrillDown', '"Service Request"'

Add a new control:

Name: xxx
HTML Type: MiniButton
Method Invoked: GoToSR
Runtime: true
Field: Activity SR Id

I bolded the Field property because it's non-intuitive: why would I define a field for a button? The field on the button is the context for the drilldown object:

Name: Service Request
Hyperlink Field: Activity SR Id
View: Service Request Detail View
Source Field: Activity SR Id
Business Component: Service Request
Destination Field: Id

If the field is left blank on the button an error like the one below will appear:

The drilldown isn't defined or enabled on the button because it doesn't have a field.

Siebel monitoring with bubblegum (awk)

If your organization hasn't made a sizable investment in Third-Party Siebel monitoring tools (or even leveraged the impressive monitoring available from Oracle), you are risking business processes that are dependent on the background processes that keep the trains running on time.

Our Siebel installation has several integrations based on asynchronous workflow processes and workflow policies. Both of these processes take work off of the main user focused object managers to maintain responsiveness and insulate our users from outages in our integrated system.

That's great, except when the background processes in Siebel fail. Unless your users are very observant, it's easy for a workflow manager to go offline and no one notices until business processes and customer are impacted.

Without access to the fantastic (and fantastically expensive monitoring tools available) one is left with waiting for alert email's from Siebel and/or periodic checking.

Rules of Thumb

1. don't depend on the system being monitored to tell you when it's down. If it's not available, it probably can't send email
2. Periodic checking is a waste of everyone's time. If your organization can spend money having someone check on components manually, you are lucky (unless you are the one doing the checking).
3. Siebel has some funny defenitions of "down" especially concerning workflow monitors (or background components)

I authored this mashup of SRVRMGR.exe, dos batch, blat, and awk for text parsing.

1. srvrmgr.exe is the commandline window into Siebel. Basically everything that can be done on the Administration - Configuration and Administration - Management screens can be done with the command line (actually more...)
2. dos batch (for windows) ties the output from the srvrmgr tool and pipes it into awk. I probably could have done everything in awk, but this got the job done
3. blat is a command line tool for sending email (very old school, but hey...)
4. awk (gawk actually) is a command line scripter's text wrangler. I consider it perl lite - not as capable but easy to create text parsing scripts

So...here are the scripts:

Monitor.bat

@echo off

set srvrmgr=E:\sea78\siebsrvr\BIN\srvrmgr.exe

set gateway=GATEWAY

set enterprise=SIEBEL

set query=list components for server SIEBELSRVR show CC_ALIAS, CC_NAME, CP_DISP_RUN_STATE, CP_END_TIME

REM REM REM REM REM REM REM REM REM REM REM REM REM REM

"%srvrmgr%" /g %gateway% /e %enterprise% /u SADMIN /p Sadm!n!! /c "%query%" /b | gawk.exe -W re-interval -f parse_siebel.awk

parse_siebel.awk

 BEGIN {


#
# Comma seperated list of component aliases to monitor
#
components = "AsgnSrvr,WorkMonActivity,WorkMonAsset,WorkMonT4Hist,MailMgr"

#
# Comma seperated list of email addresses (no spaces please)
#
emailto = "SOMEONE@SOMEWHERE.COM"
#
# (Fictional) Email address that message is from
#
emailfrom = "NO_REPLY@SOMEWHERE.COM"
#
# Subject line
#
subject = "\"Siebel Component Failure\""
#
# SMTP server name
#
smtp = "SMTP.SERVER.COM"

################---Don't edit below this line---###################
FS = "[ ]{2,}"
blat = "blat.exe"
split(components, comps, ",")
};
$3 ~ /Starting Up/ { (msg $2 " is starting up\n") }
$3 ~ /Shutdown|Offline|Shutting Down|Unavailable/ {
    for (c in comps) {
        if ($1 == comps[c]) {
            msg = (msg $2 " (" $3 ") @ " $4 "\n")
        }
    }
}
END {
    if (msg != "") {
        msg = ("Some components are in an invalid state, please investigate:\n\n" msg)
        cmd = (blat " - -to " emailto " -subject " subject " -server " smtp " -f " emailfrom " -q")
        print msg | cmd
        print "Sent mail!"
    }
};

 I know that this script could be much enhanced, but it works well for a couple hours of work

Consuming Siebel Web Services with InfoPath

Continuing my work on InfoPath I've completed the web service interface in Siebel based on very simple web service contracts that are easy for the rules engine in InfoPath to use. I will walk through the process of setting a reference to the web service and configuring an InfoPath form to use the service.

Firstly - InfoPath uses the WSDL file that Siebel produces on the Inbound Web Service page to create the internal proxy. If you don't know what WSDL is then it's best to learn about it and then continue.

1. Save the wsdl from Siebel into a convenient location
2. Open up an InfoPath form and open up the Data Connections window available from Tools, Data Connections

 3. Click on  Add...

A wizard starts - it's important that the Receive Data option is chosen; with this method you can specify inbound parameters in our case, we want to specify the SR number as well as the name of the activity plan to attach.
4. The next dialog asks what the source/destination of the data connection. Select Web Service

5. Here we have to specify the location of the WSDL file we saved from Siebel earlier. If we were connecting to a .Net web service we could provide the URL that would auto generate the wsdl on the fly.

6. The next step involves picking the specific operation we want to use. If the web service has multiple operations we need to pick only one. I've created a web service from a workflow process so there will only be one operation.

7.The next dialog asks us if we want to specify any default values. In this case, there are none, but there are scenarios where we want to call a staticly defined service (let's say to get a specific List Of Values type) and we don't want to use rules to set the initial parameters.

8. We can choose to fire the web service call when the form is launched - perhaps to refresh a set of values retrieved from an external source (again the List Of Values idea comes to the fore). In this scenario, we don't want to choose this option as the form will execute the service based on rules.

 9. On the next page we can choose to pre-cache the data from the data connection. Again there are valid scenarios for this option (like a list of values query) where we want to pre-cache a set of values in the InfoPath template. It will save on network traffic if constant refreshing is not needed. (Great for combo-boxes on the form)

10. That's it. Quite a few steps, but the wizard interface breaks all of the options down into an easily digestible set of steps.

I'll go over how to actually use the web service in a future post. (Code free!)