Re: Export and Import Utility

In article <3rhcrm$775_at_aplinfo.jhuapl.edu>, stan_novinsky_at_jhuapl.edu says...
>
>Rod Fergusson <rod.fergusson_at_canada.attgis.com> wrote:
>>
>
>
>>
>> From the scenario that you have painted...it looks like you are
>> hurting for disk space... It would be helpful to know whether
>> the USER will be repeatedly archiving files to the tape...or
>> will they be only exporting one file. If there are going to be
>> multiple exports on a tape..you are going to run into problems
>> with identifying which export file to retrieve for an
>> import..and possibly running out of space on the tape...what is
>> going to happen if they have to mount additional tape volumes?
>>
>>
>> If it is only one file at a time...I would launch a VMS command
>> procedure from within your application..perform the export to
>> disk...(it's faster)...then copy the file to the tape...delete
>> the disk file..and REWIND the tape...within the same command
>> procedure..that way...it's ready for a retrieval. LAUNCH THIS
>> PROCEDURE IN BATCH...
>>
>>
>> I don't see why you can't pseudo use the tape like a disk...as
>> long as you are aware that you have to rewind the tape in order
>> to read back off of it. I don't think it's is a solid solution
>> to a production application problem...but I don't know what the
>> whole situation is.
>>
>> I suppose it is also possible to implement this solution if
>> they were going to be doing multiple exports to the same
>> tape...but the solution will get a bit trickier because you
>> will have to keep track of the names and savesets of the
>> different exports. I would create a database table to contain
>> and track these.
>>
>> Hope this helps.... Sandra Fergusson
>>
>
>Sandra:
>
>Thanks for the reply..
>
>A little more detail may help. We will be logging messages (data)
>into multiple tables as they are received from a remote system
>via a DECNET type protocol.
>
>The user, as stated in our requirements document, must have an on-line
>option to save any of the messages in the DB tables to tape. We
>really don't have a disk space problem, just this requirement to
>adhere to. I guess a more important question is, and I think I may
>know the answer, can only certain rows of tables be exported from
>tables or does the whole table have to be exported ? If I
>remember correctly, the whole table. In that case, if the user
>wants to save messages from 45 different tables besd on a time/date
>key to tape, the entire table would have to be exported. Doesn't
>sound good considering that each message is only a row in a
>table.
>
>The user then must be able to restore the messages at any time so
>they can be viewed in a Form.
>
>Thanks again
>
>Stan
>

Hi Stan!

Hmmm...Yes you are correct...you have to export the whole table...you can't just export a row. Now...messages from 45 tables...1 row each...that is going to be brutal to track if you are exporting a slew of 1 record .DMP files to tape. Your solution will become very complicated if your client insists on storing the files on tape. By the way...I did a little research to make sure that streaming to tape was in fact a real possibility and not just a theory. I knew that it was possible to stream Oracle Archiver Logs directly to tape...but could not find any reference to export & import files. I called Oracle to verify that the export & import utilities would accept the specification of a tape device as opposed to a disk device and they said that yes you are able to do so...but they do not recommend doing so unless you files will be relatively small...which appears to be the situation in this case. The reason they deter people from doing this with larger files is the risk of transmission errors buggering up the export files. For your reference the Oracle TAR (Technical Application Request) number is #623519.

Based on the scenerio that you have painted...two of the biggest issues you have to resolve are; (1) tracking the date,time and filenames of export files written to tape (2) retrieving the data correctly from a sequentially written tape file. Before proceeding with trying to resolve these issues, I would suggest that you re-visit this issue with your client...if disk space isn't a problem, why don't they just let you stuff the records into a database table? The client is going to pay in a very big way in terms of application performance if they choose to write everything to tape. What happens if the tape fills up midday, the computer operator is on lunch and there is no one to pop a new tape in the drive...what is the application going to do if it can't write?? Or, what is going to happen if the client wants to retrieve some logs, but they were partially written on a previous tape...and now two tape mounts have to be performed..? How are they going to know which tape volume to tell the Operator to mount? Is your client prepared to wait the time necessary to mount the tapes...and for the application to try to find the desired files...load them...and finally view them? If these delays are acceptable...then I guess you will have to proceed as we had discussed earlier. Can you write to 1 table instead of 45? If yes..then what I think you should look at is writing AFTER INSERT triggers on the 45 various tables that essentially write whatever the log record was that you inserted into the (1 of 45) tables...into one "collector" table that you create. That way...you don't have to do 45 separate exports of 1 row...you can do intermittant exports of all the records that you have accumulated in your collector table. No matter which solution you choose to employ...you are going to have to maintain database tables to track the date,time,tape volume ID and filename for retrieval purposes. So...dependant on their rationale for wanting to cut everything to tape...this may/may not defeat the purpose of it. Let me know if you want to continue to work on a solution to manage the export/import of these records to tape...I am very curious as to why they want to use tape...any insights?

Sincerely, Sandra Fergusson Received on Wed Jun 14 1995 - 00:00:00 CEST