Re: Identifying record number for missing electronic document? (929 Views)
Reply
Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 1 of 20 (1,062 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.

Honored Contributor
EWillsey
Posts: 1,883
Registered: ‎04-20-2010
Message 2 of 20 (1,046 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.

Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 3 of 20 (1,037 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?

Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 4 of 20 (969 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?

Honored Contributor
EWillsey
Posts: 1,883
Registered: ‎04-20-2010
Message 5 of 20 (967 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>)

Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 6 of 20 (965 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.

Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 7 of 20 (937 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?

Honored Contributor
EWillsey
Posts: 1,883
Registered: ‎04-20-2010
Message 8 of 20 (935 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).

Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 9 of 20 (933 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?

Honored Contributor
EWillsey
Posts: 1,883
Registered: ‎04-20-2010
Message 10 of 20 (929 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.
Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 11 of 20 (928 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?

Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 12 of 20 (925 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?

Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 13 of 20 (910 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.

Honored Contributor
EWillsey
Posts: 1,883
Registered: ‎04-20-2010
Message 14 of 20 (907 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.  

Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 15 of 20 (905 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.

Honored Contributor
EWillsey
Posts: 1,883
Registered: ‎04-20-2010
Message 16 of 20 (903 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>
Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 17 of 20 (894 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?

Honored Contributor
EWillsey
Posts: 1,883
Registered: ‎04-20-2010
Message 18 of 20 (892 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.  

Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 19 of 20 (889 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.

Valued Contributor
raynebc
Posts: 518
Registered: ‎05-06-2009
Message 20 of 20 (821 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.

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.