I don't know if much is know about the origins of OID's in Service Desk, which is fine, but do we know if OID's are always ascending?
I was thinking about how to remove old scheduled tasks from the database without clearing the entire lot and if you know a current OID and the OID's are always acsending, it should be possible to remove Scheduled tasks for OID's less than the current number.
I'm not sure how I would find the current OID. Maybe make a code (as you suggested) and look up the OID in the database? Alternatively, you could run a report of the highest scheduled task OID and use that number.
Well, I found the idea interesting and I'm pretty sure I found a way to figure out how to distinguish between Scheduled Tasks.
Go to Business Logic/Scheduled tasks to list the scheduled tasks. Make sure that in the view that "Date", "Description" and "Instance" are present. Then select File/Export and export them to excel.
Open the excel file and instert a new column and name it "Searchcode". This will contain a formula that returns the first 30 characters of the "Instance". The excel function is LEFT( $CELL_REF;30 ) where $CELL_REF is the cell that contains an "Instance". (E,G LEFT(F2;30). Fill this columnt with this formula. Save the excel file.
Now execute the following query:
select JAV_OID, JAV_ENTITY, LEFT(JAV_INSTANCE,30) as SEARCHCODE from rep_javaobjects where jav_entity = ( select ent_oid from ifc_entities where ent_name = 'Scheduled Tasks' )
Export the results to another xls file.
Now open your access and import these two XLS files. Use the column searchcode as Unique Key. Create a query in the access that will display Date, Description and JAV_OID. From there you can now see what JAV_OID is correspond to what date/description.
That's a great idea to link the export of the tasks to the OID in the table.
The problem for us is that we have more than 40,000 tasks in the scheduled tasks table and you cannot see all of the tasks through the view and as far as I know, there is no way to increase the row limit of the view through the GUI. Anyone know a back door to increase the view limit?
Here's a 'backdoor' way to increase the row limit on the Scheduled Tasks view:
The MaxSize of the Scheduled Tasks view is stored on a record in the IFC_ENTITIES Table where ENT_NAME = 'Scheduled Taks'. The value to change is ENT_QUERYMAX.
Note: You will need to bounce the HPSD Service for this change to take effect
Note2: You should reset the value back to 1000 or less when you're finished. We had over 20,000 Scheduled Tasks, and it took 2-3 HOURS (I'm not kidding) for the view of all of them to be returned to the GUI. A LOT of processing happens on the APP server to crunch through this data, so BE WARNED.
Since the jav_oid is a sequence, if you idendify the first jav_oid you want to keep, then you can delete all of the old ones with one script. We had 115 old ones to delete from our test Oracle Service Desk db. 1) stop SD service on all application servers 2)connect to the SD db as system with sqlplus 3) select count(*) from SERVICEDESKREPO.rep_javaobjects where jav_entity = ( select ent_oid from SERVICEDESKREPO.ifc_entities where ent_name = 'Scheduled Tasks') and (jav_oid < 281483969410305);
The count returned will be 115. Do not run the delete if it is not 115.
delete from SERVICEDESKREPO.rep_javaobjects where jav_entity = ( select ent_oid from SERVICEDESKREPO.ifc_entities where ent_name = 'Scheduled Tasks') and (jav_oid < 281483969410305); commit;