Installing the ICD Demo Content along with the ICD Process Content Packs

Do NOT try to install the 7.5.1 demo data into 7.6. It really doesn't work well. I'm leaving this post intact because the steps are useful in general I believe.

If you try to install the IBM Control Desk Content Packs along with the 7.5.1 Demo Content, you're going to have problems. I already addressed a standalone problem with the Demo Content in an earlier post, and now I've gotten further, so wanted to share the wisdom I gained.

No matter which order you install - Demo Content then Process Packs (specifically the Change Management Content Pack) or the other way around - you're going to encounter the following error:

One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "MAXIMO.PLUSPSERVAGREE" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=4.11.69


The cause for this is that the Change Management Content Pack and also the Service Desk Content Pack specify hard-coded values for PLUSPSERVAGREEID in the DATA\PLUSRESPPLAN.xml file, when the inserts should be creating and using the next value of the PLUSPSERVAGREESEQ sequence.

In finding the above root cause, it means that there are two possible solutions to the problem, depending on the order you install things.

If you install the Content Packs before the Demo Content

So in my first run, I installed the Content Packs first, and then the Demo Content (after modifying it as explained in an earlier post). And the exact SQL statement causing this problem was:

SQL = [insert into pluspservagree ( active,calendar,changeby,changedate,createby,createdate,description,hasld,intpriorityeval,intpriorityvalue,langcode,objectname,orgid,ranking,sanum,pluspservagreeid,servicetype,shift,slanum,calendarorgid,slatype,status,statusdate,slaid,slahold,stoprpifjportt,billapprovedwork) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,nextval for PLUSPSERVAGREESEQ,?,?,?,?,?,?,?,?,?,?,0)]
 parameter[1]=1
 parameter[2]=BUS01
 parameter[3]=MAXADMIN
 parameter[4]=2007-10-12 12:33:55.0
 parameter[5]=MAXADMIN
 parameter[6]=2007-10-12 12:33:55.0
 parameter[7]=P1 Incident - Respond in 30 mins. Resolve in 2 hrs.
 parameter[8]=0
 parameter[9]=EQUALS
 parameter[10]=1
 parameter[11]=EN
 parameter[12]=INCIDENT
 parameter[13]=PMSCIBM
 parameter[14]=100
 parameter[15]=SRM1002
 parameter[16]=SLA
 parameter[17]=BUSDAY
 parameter[18]=SRM1002
 parameter[19]=PMSCIBM
 parameter[20]=CUSTOMER
 parameter[21]=ACTIVE
 parameter[22]=2011-09-14 13:26:13.247
 parameter[23]=1
 parameter[24]=0
 parameter[25]=0


To find the constraint causing the problem, I found this page:

https://bytes.com/topic/db2/answers/810243-error-messages-key-constraint-violations

Which showed that I could find the particular constraint with the following SQL:

SELECT INDNAME, COLNAMES
FROM SYSCAT.INDEXES
WHERE IID = 1
AND TABNAME = 'PLUSPSERVAGREE'

That basically showed an index named SQL160607091434350 consisting of just the column named PLUSPSERVAGREEID.

So each row in the PLUSPSERVAGREE table should have a unique value in the PLUSPSERVAGREEID column.


Then to find the existing values in the PLUSPSERVAGREEID column of the PLUSPSERVAGREE table, run:

SELECT PLUSPSERVAGREEID from PLUSPSERVAGREE

For me, this showed values 1 through 16.

Now, looking at the sequence itself, I found that the last value assigned was 5 with this query:

SELECT LASTASSIGNEDVAL from sysibm.syssequences where seqname = 'PLUSPSERVAGREESEQ'

So to fix the problem, I altered the PLUSPSERVAGREESEQ sequence to start at 17:

ALTER SEQUENCE PLUSPSERVAGREESEQ RESTART WITH 17

After I did that, I tried again to install the Demo Content and it worked!

If you installed the Demo Content first

I take lots of snapshots of my VMs, so I could easily go back to a snapshot where I had already installed the Demo Content, to then try to install the Content Packs. That led me to see that the Change Management Content Pack has hardcoded values in the DATA\PLUSRESPPLAN.xml file (by downloading the ChangeMgtPack7.6.zip file and opening up the file). On the positive side, it appears that nothing else in the Content Pack actually references these hardcoded values, so we have the option of changing them as needed.

In my particular case, I found that the following values in the PLUSPSERVAGREE table for the PLUSPSERVAGREEID column

9
10
11
12
13
14
24
25

I also found that the LASTASSIGNEDVAL for the PLUSPSERVAGREESEQ sequence was 25, so that matches up with the data.

The very lucky part for me is that there are exactly 8 rows that get inserted by the PLUSRESPPLAN.xml file, and the PLUSPSERVAGREE table doesn't have any rows with values 1 through 8!

So the solution I applied was I manually edited the PLUSRESPPLAN.xml file to set the PLUSPSERVAGREEID values to 1 through 8. Then I saved the edited file back into the zip file, created a valid ContentSource.xml file to point to it (so I could install from my local copy of the Content Pack), added my new Content Source to the Content Installer, and I was able to successfully install the Change Process Content Pack!

However, I then found that there's also a similar problem with the Service Desk Content Pack, but the same solution can't be applied. Specifically, in the Service Desk Content Pack, the DATA\SLA.xml file uses hardcoded values for the same column, but those values are 1, 3, 4 and 5, which I just used in my workaround for the Change Management Content Pack. So to fix this correctly, I looked in the Demo Content Content Pack to find out how to reference the PLUSPSERVAGREESEQ sequence, and it's actually not too bad.

So the fix I went through was to manually modify the DATA\SLA.xml file to change every element that looked like this:

<column dataType="java.lang.Long" name="PLUSPSERVAGREEID">
      <value>3</value>
    </column>

to this:

<column dataType="java.lang.Long" name="PLUSPSERVAGREEID">
    <columnOverride>
        <sequence mode="nextval" name="PLUSPSERVAGREESEQ"/>
      </columnOverride>
    </column>

Then like above, I saved the edited file back into the zip file, created a valid ContentSource.xml file to point to it (so I could install from my local copy of the Content Pack), added my new Content Source to the Content Installer, and I was able to successfully install the Service Desk Content Pack!

After installing, I checked the PLUSPSERVAGREE table again, and I saw that the values 26 through 29 were there, so I know my change worked.

So in my case I didn't have to change the start value for the PLUSPSERVAGREESEQ sequence, which is nice.

It was a painful afternoon, but well worth it in the end.