Bulk Insert Data Restrictions in Maximo 7.5, different scripts for SQL and Oracle databases sequences

Goal:
I want to set a bunch of Data Restrictions in Maximo 7.5.0.5. To do this manually his is a dull job to do and it is easy to make a typo or an other error. 
So like every tedious job in IT we should be able to automate it and make our life easier.

Example:
I need to set some fields in my application to Readonly when the record reaches a certain status. I create a condition in the 'Conditional Expression Manager' to check for the status and then I create a 'Attribute Restriction' in the 'Data Restrictions' tab of the 'Security Groups' application in Maximo. 
This is the proces I want to automate.

Solution:
I will create a SQL script in SQL Developer to be able to insert a large amount of restrictions. I will need to use the sequences provided by Maximo. There is difference between Oracle and SQL Databases for this.


Step 1 - Get the insert statement

First created a Condition that will check the status of the record for not being equal to 'NEW'.

Then I created three Atrribute Restrictions by hand.

In the Security Groups application, tab 'Data Restrictions' subtab 'Attribute Restrictions'  

I do this so I can use this data as the tempalte for my script.

Next start SQL Developer and connect to the Database. Run the following script:

select * from SECURITYRESTRICT;

This will give you the records that are inserted in the database in the SECURITYRESTRICT table. In order to know what table the data is stored in use ALT+F1 in Maximo while the cursor is in the Maximo field. Maximo will show detailed information of the field.

The Export one of the results to a INSERT statement in SQL Developer.
To only get one record, just look into the previous results and narrow down the result by modifying your query.
For example, I only wanted the record with SECURITYRESTRICTID 25:

select * from SECURITYRESTRICT
where SECURITYRESTRICTID = 25;

Then in the results pane of SQL Developer, right click the result and choose 'Export'

In the Export Wizard, set the Table Name to 'SECURITYRESTRCIT' and choose 'Clipboard' in the 'Save As' drop down box and click the [Next] button.

In the next step click [Finish]

Now paste the result in your SQL Developer Worksheet.

REM INSERTING into SECURITYRESTRICT
SET DEFINE OFF;
Insert into SECURITYRESTRICT (SECURITYRESTRICTID,GROUPNAME,APP,OBJECTNAME,ATTRIBUTENAME,REEVALUATE,RESTRICTION,CONDITIONNUM,TYPE,SRESTRICTNUM,ROWSTAMP) values (25,'PIREG','PROJISSUE','SR','GMBPROJECT',1,'READONLY','GMB_PINOTNEW','ATTR','1002','1660508');


Delete the first two lines as we will not use them. I usually format it a bit like show below.

Step 2a - Modify the insert statement for a ORACLE database

Now we need to modify the insert statement. In this step the modifications for ORacle are described, see Step 2b for the SQL Server approach. 

The very first field that gets inserted is the SECURITYRESTRICTID. This field is a unique number, so we can't just insert the number 25 over and over again. Oracle keeps track of a lot of the 'sequences'. You can find them in the 'Sequences' section of the Database in Oracle. 

Usually the sequence name is somewhat the same as the field name. 

So we know we can use the sequence 'SECURITYRESTRICTSEQ to determine the number to use in our insert query.

I modify the insert query 

Insert into SECURITYRESTRICT 
 (SECURITYRESTRICTID, GROUPNAME, APP, OBJECTNAME, ATTRIBUTENAME, REEVALUATE, RESTRICTION, CONDITIONNUM, TYPE, SRESTRICTNUM) 
 values 
 (SECURITYRESTRICTSEQ.nextval,'PIREG','PROJISSUE','SR','DESCRIPTION',1,'READONLY','GMB_PINOTNEW','ATTR', '1002');

Modifications:

• Use 'SECURITYRESTRICTSEQ.nextval in the first row. This will give the next number in the sequence and the sequence is automatically updated in Oracle.
• Delete the ROWSTAMP field and value. This is a internal database value and will be fileld in by the database automatically.
• For the SRESTRICTNUM is no sequence, so leave it as is.

Now run the query. I get an error:

SQL Error: ORA-00001: unique constraint (MAXIMO.SECURITYREST_NDX6) violated

00001. 00000 -  "unique constraint (%s.%s) violated"

Seems like the SRESTRICTNUM needs to be unique. First I search if there is a sequence for this field. There is not. So I gues I can insert my own number there. Use a number that you can recognise, so if modifications are needed later on you can easily identify them.

I modify the query like shown below:

Insert into SECURITYRESTRICT 

 (SECURITYRESTRICTID, GROUPNAME, APP, OBJECTNAME, ATTRIBUTENAME, REEVALUATE, RESTRICTION, CONDITIONNUM, TYPE, SRESTRICTNUM) 

 values 

 (SECURITYRESTRICTSEQ.nextval,'PIREG','PROJISSUE','SR','DESCRIPTION',1,'READONLY','GMB_PINOTNEW','ATTR', 'GNZ0001');

I used GNZ0001 as the SRESTRICTNUM and ran the query again.

Now below the error in the Script Outup I get '1 rows inserted' 

Press the 'commit' button or run the commit; statement to save the changes to the database. 

Next I log in to Maximo as the user who should not be able to modify the field, but it doesn't work. THe DESCRIPTION field is still available for the user...

I decide to restart the MXServer.

After the restart I login, and yes! The field is now readonly. Seems like the MXServers needs to be restarted when doing this with the script. Ow well, as you can read in step 2b, this is also needed for the SQL Server approach. 

Step 2b - Modify the insert statement for a SQL database

The difference between Oracle and SQL Server is the way the sequence works. The same sequence is used, but Oracle is keeping track of the sequence number by its own sequences (see step 2a) and as SQL Server doesn't do that, Maximo has to do it. Maximo uses the MAXSEQUENCE table for that job.

Now we need to modify the insert statement. In this step the modifications for SQL Server are described, see Step 2a for the Oracle approach. 

The very first field that gets inserted is the SECURITYRESTRICTID. This field is a unique number, so we can't just insert the number 25 over and over again. Maximo keeps track of a lot of the 'sequences' You can find them in the MAXSEQUENCE table for SQL Server.

Usually the sequence name is somewhat the same as the field name, so run a query like this:

select * from MAXSEQUENCE
where lower(sequencename) like '%securityrestrict%';

This wil give the result shown below

So we know we can use the sequence 'SECURITYRESTRICTSEQ' to determine the number to use in our insert query.

In Oracle we just use .nextval in the insert, in SQL we need to put the number in manually.

First we determine the next value by getting the highest value and adding 1 to it:

select max(SECURITYRESTRICTID)+1 from SECURITYRESTRICT;

Note:
The results shown here are a bit different than in the Oracle example because I use a different test environment with a SQL Database.

If the ID field is empty (NULL value) the secuence isn't used before. This way the 'max' function will not work. If this is the case you need to use the query below first:

update MAXSEQUENCE set MAXRESERVED = 1 

where TBNAME = 'SECURITYRESTRICT' and NAME = 'SECURITYRESTRICTID' and MAXRESERVED is NULL;



Then modify the insert query 

Insert into SECURITYRESTRICT 
 (SECURITYRESTRICTID, GROUPNAME, APP, OBJECTNAME, ATTRIBUTENAME, REEVALUATE, RESTRICTION, CONDITIONNUM, TYPE, SRESTRICTNUM) 
 values 
 ((select max(SECURITYRESTRICTID)+1 from SECURITYRESTRICT),'PIREG','PROJISSUE','SR','DESCRIPTION',1,'READONLY','GMB_PINOTNEW','ATTR', 'GNZ0001');

For the SECURITYRESTRICTID simply use select max(SECURITYRESTRICTID)+1 from SECURITYRESTRICT between brackets.

Then update the sequence.

update MAXSEQUENCE set MAXRESERVED = (select max(SECURITYRESTRICTID)+1 from SECURITYRESTRICT) 
where TBNAME = 'SECURITYRESTRICT' and NAME = 'SECURITYRESTRICTID';

Check the sequence

select * from maxsequence 
where tbname = 'SECURITYRESTRICT';

Check the Data restriction:

select * from SECURITYRESTRICT;

Next restart the MXServer and you are done.


Note: In order to create the SQL statements I use a Excel sheet. Example:

and the formula to get you started:

=[@[Insert part 1]] & [@[Insert Oracle]] & " ,'" & [@GROUP] & "', '" & [@APPLICATION] & "', '" & [@OBJECT] & "', '" & [@ATTRIBUTE] & "', " &[@REEVALUATE] & ", '" & [@RESTRICTION] & "', '" & [@CONDITION] & "', '" & [@[Type Restriction]] & "', '" &[@SRESTRICTNUM] & "');"


GNZ.