Re: Identifying record number for missing electronic document? (344 Views)
Reply
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 1 of 20 (517 Views)
Accepted Solution

Identifying record number for missing electronic document?

I was running my semiweekly document store audit and Trim indicated one file was missing, which isn't necessarily odd, I usually find it was probably caused by a malfunction of the Trim client somewhere in our organization.  Anyway, the audit log gives a URI number and a document store reference number:

Revisions Revision Number 1 (uri=102082) : store reference (006+00G+0E0H01GO070.PDF) does not exist.

 

To track down the record affected by this type of problem, I can usually perform a search of the latter in the workgroup server logging to find the number of the offending record.  To do this I go to the document store and look at the date created timestamps of the electronic documents that come before and after the missing document (since the documents are named incrementally) so I know which day's logs to search in.  However in this instance the filename in question does not appear in either the WG or audit logging.

 

Before resorting to having to look for SQL queries to perform on the database to identify the offending record, I figured I'd try some string searches in the client.  A URI search for the record (uri:102082) pulls up a completely unrelated record that was finalized years ago.  I saw in the appendix that there is a "store" string search method, but I couldn't get any results by using searches like "store:0E0H01GO070", "store:0E0H01GO070.PDF", or "store:006+00G+0E0H01GO070.PDF".

 

What string search, if any, can be used to identify the record with the missing document?  If the string search isn't capable of that, what would be an appropriate SQL query to use, and on which table?  Thank you in advance for your help.

Please use plain text.
Honored Contributor
EWillsey
Posts: 1,760
Registered: ‎04-20-2010
Message 2 of 20 (501 Views)

Re: Identifying record number for missing electronic document?

[ Edited ]

You can't use a string search. You can use this SQL query though:


select recordId from tsrecord where uri in (select evRecElecUri from tserecvsn where uri = 102082)

If you didn't know the URI (which you do because its' in the log) but you knew the file name in the store, you could use this:

select recordId from tsrecord where uri in (select evRecElecUri from tserecvsn where evSID='006+00G+0E0H01GO070.PDF')

 

EDIT: note this only works for revisions.  Use the same logic on the TSRECELEC table if it's not a revision.

Please use plain text.
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 3 of 20 (492 Views)

Re: Identifying record number for missing electronic document?

Thank you, it sounds like revisions have their own URI numbering that is separate from that of regular records and I didn't realize that was the case.  The query pointed out the correct record, thank you.

 

Do you think there's any chance HP would be willing to add a string search method to search for a revision URI instead of just a record URI?  It could be helpful in cases like this.  Also, do you know how the store search method would be used?

Please use plain text.
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 4 of 20 (424 Views)

Re: Identifying record number for missing electronic document?

By any chance, could you provide the SQL query needed to find the record number based on a rendition URI number?

Please use plain text.
Honored Contributor
EWillsey
Posts: 1,760
Registered: ‎04-20-2010
Message 5 of 20 (422 Views)

Re: Identifying record number for missing electronic document?

select recordId from tsrecord where uri in (select rrRecordUri from tsrenditio where uri = <insert_your_rendition_uri_here>)

Please use plain text.
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 6 of 20 (420 Views)

Re: Identifying record number for missing electronic document?

Thanks, I figured it would be that, but the SQL query gives no results?

 

That would be the right query to use based on this docstore audit log entry, right:

Renditions 055-00048 Warning Letter Response 062509.pdf (uri=38416) : store reference (002+00S+0D9U1C3T0IE.PDF) does not exist.

Please use plain text.
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 7 of 20 (392 Views)

Re: Identifying record number for missing electronic document?

I ran into a similar problem.  This morning's document store audit shows that a revision of a record has an electronic document with an "incorrect file size."  I don't know how this is possible except for a malfunction of the server/client, it's not like anybody directly manipulates the document store.  Re-running the docstore integrity check and selecting the option to correct any errors that are encountered does not correct this problem

 

Can the client do this any more cleanly than me re-adding the file to replace that revision so that Trim records the correct file size?  Is there a better way to resolve this issue?

Please use plain text.
Honored Contributor
EWillsey
Posts: 1,760
Registered: ‎04-20-2010
Message 8 of 20 (390 Views)

Re: Identifying record number for missing electronic document?

The client do anything other than what you described.   The DBA could go update it manually in the databaes though (not worth the effort in my opinion).

Please use plain text.
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 9 of 20 (388 Views)

Re: Identifying record number for missing electronic document?

I guess I was expecting there to be an obvious way to replace the revision via the thick client, but after I extracted that revision, I'm not seeing a way to overwrite the electronic document for that revision.  What am I missing here?

Please use plain text.
Honored Contributor
EWillsey
Posts: 1,760
Registered: ‎04-20-2010
Message 10 of 20 (384 Views)

Re: Identifying record number for missing electronic document?

Users can't replace revisions. They could just extract it, put it back in, and then remove the original revision.

The integrity check fix feature should solve the problem. Here is the documentation from that feature:

The integrity check can fix reported incorrect file size.

The method depends on whether or not there is a backup path specified in the field Restored Backup Folder For Store:

If there is no backup folder specified, by updating the dataset's last known file size to match the size of the item as it actually is in the store
If there is a backup folder specified, HP TRIM looks in the specified backup folder for the document.
If HP TRIM finds it and the document is the expected size, HP TRIM restores it to the document store and changes the references to point to the restored item.

If HP TRIM does not find the document, it will take no action.
Please use plain text.
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 11 of 20 (383 Views)

Re: Identifying record number for missing electronic document?

It wouldn't allow me to begin the integrity check with the option to repair errors unless I specified a backup folder, so I made sure that the file from the document store was at the root of the backup folder path.  This check takes a long time to finish so I won't know for a while if it worked.  It doesn't need me to build the entire folder structure to the document in the backup folder, does it?

Please use plain text.
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 12 of 20 (380 Views)

Re: Identifying record number for missing electronic document?

It didn't work, or at least the integrity check's logging says it didn't correct the issue.  Unless it has a requirement like having some kind of folder structure in the backup folder, I can't tell that the integrity check will be able to fix it.  What would be the appropriate SQL query to update the file size?

Please use plain text.
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 13 of 20 (365 Views)

Re: Identifying record number for missing electronic document?

Today is the day I regularly run the second document store audit of the week.  Since I need to get this fixed, I guess I'll have to open a support ticket.

Please use plain text.
Honored Contributor
EWillsey
Posts: 1,760
Registered: ‎04-20-2010
Message 14 of 20 (362 Views)

Re: Identifying record number for missing electronic document?

The backup location you use must maintani the full structure.  So if you're bad file is in "TP\2\001\000" off the original document store, copy that full path structure and the file into a new place.  Then point the backup field to that new place.  

Please use plain text.
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 15 of 20 (360 Views)

Re: Identifying record number for missing electronic document?

After looking at the documentation (help file) again, that option will not resolve the problem anyway.  The help file says that if the backup folder is specified AND the document in question is found there with the size Trim is expecting, it will put it in the document store so that the file with the expected size is present.  There is no file with the expected size that I can provide since I suspect that it was a malfunction of the software that caused the wrong file size to be recorded into the database.  Since this is an old revision of the file, I don't want to make a mess of things by deleting the revision and adding a new revision, since as you said, Trim has no way for me to replace the document on a past revision.

 

This leaves editing the size in the database to be the last option I can think of, but I need the relevant SQL queries to do so.

Please use plain text.
Honored Contributor
EWillsey
Posts: 1,760
Registered: ‎04-20-2010
Message 16 of 20 (358 Views)

Re: Identifying record number for missing electronic document?

update tserecvsn set evBytes = 1234 where uri = someUriFortheRevision

OR

update tserecvsn set evBytes = 1234 where evRecElecUri = (select uri from tsrecord where recordId = '<yourRecordNumberHere>') AND evVersionNbr = <yourRevisionNumberHere>
Please use plain text.
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 17 of 20 (349 Views)

Re: Identifying record number for missing electronic document?

Thank you, that worked.  The solution suggested by HP support was to delete the offending revision and optionally add it in again as a new revision, but that would put it out of order with the other past revisions and I wanted to keep the record as intact as possible.  Altering the file size in the database seemed to be the only easy way to correct the problem without deleting the revision, would you consider that an accurate conclusion?

Please use plain text.
Honored Contributor
EWillsey
Posts: 1,760
Registered: ‎04-20-2010
Message 18 of 20 (347 Views)

Re: Identifying record number for missing electronic document?

Yeah, that's what I would do. Maintains the history of the record without a bunch of fuss.  

Please use plain text.
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 19 of 20 (344 Views)

Re: Identifying record number for missing electronic document?

Thanks again.  I'm going to ask HP support to correct the documentation to match the Trim client's behavior regarding a specified backup folder being mandatory when running the integrity check with the option to correct errors.

Please use plain text.
Valued Contributor
raynebc
Posts: 506
Registered: ‎05-06-2009
Message 20 of 20 (276 Views)

Re: Identifying record number for missing electronic document?

It looks like the inability to start the integrity check with the option to correct errors unless a restored backup folder is specified was a bug with Trim 7.1.x, 7.3.4 allows this to be done.

Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation