How To Uncompress Files in the Siebel File System with the Original File Names?

The Siebel File System stores all file attachments in a compressed format with a proprietary naming convention. Neither Siebel InterActive nor the Fulcrum SearchServer can access or read these files directly. If either of these modules is used, maintain a copy of all file attachments in an uncompressed format in a separate directory accessible to SearchServer or Siebel InterActive. Because the Siebel client also stores file attachments in a compressed format, Fulcrum cannot be used for local searches on the client unless an uncompressed directory is also maintained there.

Siebel Systems provides two utilities to maintain the uncompressed directories. The utilities are located in the SIEBEL_HOME\siebsrvr\bin directory and they were designed only for Windows Operating System. Also they were designed for Fulcrum search and Siebel interactive and then they should not be used for anything else. 

For example: When a file named "Timesheet.xls" is added to the Siebel File System, the Siebel application compresses the file and renames the file to the Table Name, Row Id and Revision Id and adds a .SAF as the extension. The compressed file name located in the Siebel File System directory would be in the format X.Y.Z.SAF for example:

S_LIT_1-JH_1-3.SAF

Where

• X is the Table Name, in this example, it has been set to the value S_LIT
• Y is the Row Id, in this example, it has been set to the value 1-JH
• Z is the Revision Id, in this example, it has been set to the value 1-3

After the ssemuzip.exe utility has uncompressed the files, the file names are modified and contain the following Siebel information. For the above example, the new file that is uncompressed by the ssemuzip.exe utility is called:

C:\siebsrvr\BIN>ssemuzip c:\lit c:\siebfile\lit S_LIT
....
Processing c:\siebfile\lit...
Finished Processing files for table S_LIT in the Siebel File System

For the above example, the uncompressed file located in the c:\lit directory would be called:

Timesheet_S_LIT_1-JH_1-3.xls

If you are interested in uncompressing the above file and maintaining the original file name, then apply the workaround that is documented below. NOTE: Examples have been provided for the Microsoft SQL Server and Oracle database as well as for the Windows platform. The workaround can also be applied to the DB2 database as well as the UNIX platforms.

The sseunzip.exe utility decompresses one file at a time and allows the uncompressed file to be renamed to the original name. After running the SQL queries, the results should be copied to a batch file that can be run in a DOS command window which will extract the files to a directory of choice with the original file names. The following SQL queries can be used as a guide. Run separate SQL queries for each table that contains documents. Use the values shown below as examples of what values to supply when you run the SQL queries in your own environment:

Siebel Server directory:           c:\sea\siebsrvr
Siebel File System directory:      c:\sea\siebfile\<subdirectory>
Export directory:                  c:\lit
Table Name:                        S_LIT

Microsoft SQL Server Database

1. Use the Microsoft SQL Server Query Analyzer tool to run the SQL query below. Log in and change to the Siebel Database.

2. Under the Tools > Option menu, choose the Results tab and do the following:

   1. Set the Default results target: Results to File...
   2. Uncheck the box: Print column headers (*)

    

3. Under the Tools > Option menu, choose the Connection Properties tab and do the following:

   1. Check the box: Set nocount

    

4. In a SQL query window, copy and paste the following select statement and change the bold values to match the values applicable to your Siebel environment:

   SELECT 'c:\sea\siebsrvr\bin\sseunzip.exe c:\sea\siebfile\lit\S_LIT_' + ROW_ID + '_' + FILE_REV_NUM + '.SAF "c:\lit\' + FILE_NAME + '.' + FILE_EXT + '"' FROM S_LIT WHERE FILE_NAME IS NOT NULL

   NOTE: Make sure the single and double quotes are straight quotes when you run the SQL query in the Microsoft SQL Server Query Analyzer. The results from running the above query should be saved to a file called extract.bat on your file system.

Oracle Database

1. Use the Oracle SQL*Plus tool to run the SQL query below. Log into the Siebel database instance.

2. Under the Options > Environment menu, select the following:

   • Buffer Width = 300
   • Line Size = 300
   • Page Size = 50000
   • Heading = Off

    

3. Copy and paste the following select statement and change the bold values to match the values applicable to your Siebel environment:

   SELECT 'c:\sea\siebsrvr\bin\sseunzip.exe c:\sea\siebfile\lit\S_LIT_' || ROW_ID || '_' || FILE_REV_NUM || '.SAF "c:\lit\' || FILE_NAME || '.' || FILE_EXT || '"' FROM S_LIT WHERE FILE_NAME IS NOT NULL;

   NOTE: Make sure the single and double quotes are straight quotes when you run the SQL query in the Oracle SQL*Plus tool. The results from running the above query should be saved to a file called extract.bat on your file system.

The results file, extract.bat file, should look like the following:

c:\sea\siebsrvr\bin\sseunzip.exe c:\sea\siebfile\lit\S_LIT_1-JH_1-3.SAF "c:\lit\Timesheet.xls"
c:\sea\siebsrvr\bin\sseunzip.exe c:\sea\siebfile\lit\S_LIT_00-1ACQ6_0-4L.SAF "c:\lit\Narrative Service Top 10 Most Serviced Products.doc"
c:\sea\siebsrvr\bin\sseunzip.exe c:\sea\siebfile\lit\S_LIT_00-192ZO_0-VK.SAF "c:\lit\Product Line Briefing.doc"
c:\sea\siebsrvr\bin\sseunzip.exe c:\sea\siebfile\lit\S_LIT_0-AIXG_0-VL.SAF "c:\lit\Sales Pipeline Briefing.doc"

NOTE: Make sure there is a carriage return at the end of the file and remove any extraneous information like the select statement or number of records returned from the query. From a Microsoft DOS command window, run the extract.bat file.

c:\temp> extract.bat

NOTE: The extract batch file will call the sseunzip.exe utility once for each file. This means that if there are 500 files in a particular table, then the sseunzip.exe utility is called 500 times. This process can be time consuming since it has to uncompress all the files. The results from running the sseunzip.exe will look like the following:

c:\temp> extract.bat

c:\sea\siebsrvr\bin\sseunzip.exe c:\sea\siebfile\lit\S_LIT_1-JH_1-3.SAF "c:\lit\Timesheet.xls"

Unzipped 1 files.

c:\sea\siebsrvr\bin\sseunzip.exe c:\sea\siebfile\lit\S_LIT_00-1ACQ6_0-4L.SAF "c:\lit\Narrative Service Top 10 Most Serviced Products.doc"

Unzipped 1 files.

c:\sea\siebsrvr\bin\sseunzip.exe c:\sea\siebfile\lit\S_LIT_00-192ZO_0-VK.SAF "c:\lit\Product Line Briefing.doc"

Unzipped 1 files.

c:\sea\siebsrvr\bin\sseunzip.exe c:\sea\siebfile\lit\S_LIT_0-AIXG_0-VL.SAF "c:\lit\Sales Pipeline Briefing.doc"

Unzipped 1 files.

Below is possible error that you might encounter when running the ssesunzip.exe utility and actions that you can take to try to resolve the error:

Error: Unable to open compressed file (d:\sea752_o\fs\att\S_SR_ATT_99-4GVR4_99-1DW2.SAF).
SSEUNZIP: Unable to open 'd:\sea752_o\fs\att\S_SR_ATT_99-4GVR4_99-1DW2.SAF'.
Please verify that you are using the correct version of SSEUNZIP.

Suggestion: Confirm that the file listed above does exist in the directory that is specified. In some cases, it could be that the file name is not specified correctly or the directory is not specified correctly. If there are problems with the file or directory, make the necessary corrections to the extract.bat file and rerun it again.

If the file does not exist, then edit the extract.bat file to remove the file reference. Another cause could be that the file does exist but is located in a different Siebel File System on a different Siebel Server. You will need to rerun the above SQL query and point to the other Siebel Server and the other Siebel File System.