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...

Siebel script export

From time to time, it would be very useful to conduct code analysis on the script that is buried within the multiple tables of the Siebel Repositories.
Things like:

• line count
• code complexity calculations (edge detection)
• release over release comparisons
  
• etc

Getting the code out of the repository is a pain. I can use the repository search functionality to find specific things, but its tough.
To solve this I fired up my sql editor and using the xml features of sql 2005, I built a query that exports the script into a big xml document.
One can then run xslt's or other scripts against the code file.
Below is the script:

DECLARE @APPLET_BROWSER XML
DECLARE @APPLET_SERVER XML
DECLARE @BUSINESS_COMP_BROWSER XML
DECLARE @BUSINESS_COMP_SERVER XML
DECLARE @BUSINESS_SERVICE_BROWSER XML
DECLARE @BUSINESS_SERVICE_SERVER XML
DECLARE @APPLICATION_BROWSER XML
DECLARE @APPLICATION_SERVER XML

SET @APPLET_SERVER =
(
SELECT
'Applet Browser Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_APLT_BRSSCRPT D
WHERE D.APPLET_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
FOR XML PATH('Method'), Type
)
FROM S_APPLET A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_APLT_BRSSCRPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.APPLET_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @APPLET_BROWSER =
(
SELECT
'Applet Server Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_APPL_WEBSCRPT D
WHERE D.APPLET_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
ORDER BY SEQUENCE
FOR XML PATH('Method'), Type
)
FROM S_APPLET A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_APPL_WEBSCRPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.APPLET_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @BUSINESS_COMP_SERVER =
(
SELECT
'Business Component Server Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_BUSCOMP_SCRIPT D
WHERE D.BUSCOMP_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
ORDER BY SEQUENCE
FOR XML PATH('Method'), Type
)
FROM S_BUSCOMP A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_BUSCOMP_SCRIPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.BUSCOMP_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @BUSINESS_COMP_BROWSER =
(
SELECT
'Business Component Browser Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_BC_BRS_SCRPT D
WHERE D.BUSCOMP_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
FOR XML PATH('Method'), Type
)
FROM S_BUSCOMP A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_BC_BRS_SCRPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.BUSCOMP_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @BUSINESS_SERVICE_SERVER =
(
SELECT
'Business Service Server Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_SERVICE_SCRPT D
WHERE D.SERVICE_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
ORDER BY SEQUENCE
FOR XML PATH('Method'), Type
)
FROM S_SERVICE A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_SERVICE_SCRPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.SERVICE_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @BUSINESS_SERVICE_BROWSER =
(
SELECT
'Business Service Browser Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_SVC_BRS_SCRPT D
WHERE D.SERVICE_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
FOR XML PATH('Method'), Type
)
FROM S_SERVICE A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_SVC_BRS_SCRPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.SERVICE_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @APPLICATION_SERVER =
(
SELECT
'Application Server Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_APPL_SCRIPT D
WHERE D.APPLICATION_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
ORDER BY SEQUENCE
FOR XML PATH('Method'), Type
)
FROM S_APPLICATION A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_APPL_SCRIPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.APPLICATION_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)
SET @APPLICATION_BROWSER =
(
SELECT
'Application Browser Script' "@name",
(
SELECT
A.NAME "@Name",
(
SELECT
D.NAME "@name",
D.LAST_UPD "@lastUpdated",
D.SCRIPT "text()"
FROM S_APPL_BRSSCRPT D
WHERE D.APPLICATION_ID = A.ROW_ID AND D.REPOSITORY_ID = C.ROW_ID
FOR XML PATH('Method'), Type
)
FROM S_APPLICATION A
INNER JOIN S_REPOSITORY C ON A.REPOSITORY_ID = C.ROW_ID
WHERE
C.NAME = 'Siebel Repository'
AND A.INACTIVE_FLG = 'N'
AND EXISTS(SELECT * FROM S_APPL_BRSSCRPT B WHERE B.INACTIVE_FLG = 'N' AND A.ROW_ID = B.APPLICATION_ID)
FOR XML PATH('Object'), Type
)
FOR XML PATH('Type')
)


SELECT @BUSINESS_COMP_SERVER.query('//Type')
UNION ALL
SELECT @BUSINESS_COMP_BROWSER.query('//Type')
UNION ALL
SELECT @BUSINESS_SERVICE_SERVER.query('//Type')
UNION ALL
SELECT @BUSINESS_SERVICE_BROWSER.query('//Type')
UNION ALL
SELECT @APPLICATION_SERVER.query('//Type')
UNION ALL
SELECT @APPLICATION_BROWSER.query('//Type')
UNION ALL
SELECT @APPLET_BROWSER.query('//Type')
UNION ALL
SELECT @APPLET_SERVER.query('//Type')
FOR XML PATH(''), ROOT('Scripting'), TYPE