topic Re: Need help on datatrieve in Operating System - OpenVMS

Need help on datatrieve

vdya — Wed, 11 Nov 2009 12:43:21 GMT

I am just beginner in datatrieve. I am pretty much comfortable with writing records to retrieve data from DAT files. Now i need to combine few fields from 2 different DAT files and need to create new record. I wrote sample procedure and i am getting error which i explained in attached file with sample procedure. Like we define domain for records, do we need to do anything for procedures?
Thanks & Regards,
Vdya

Re: Need help on datatrieve

Hein van den Heuvel — Wed, 11 Nov 2009 14:41:36 GMT

You can DEFINE only once.
After that you need to REdefine.
Typically you get that automatically by using the EDIT command.

Check out DTR> help command REDEFINE_Command

The line "DEFINE PROCEDURE" ended up in the procedure.. remove it.

Free advice:

1) A name like SAMPLE or TEST is prone to be duplicate and confusing.
Consider names like SAMPLE_PROC and SAMPLE_REC
Admittedly that is redundant data.. but it helps keeping sane.

2) As you are learning Datatriece try to avoid FIND as soon as possible. Go for (nested) FOR loops if and when you can.

FOR IMC CROSS INC OVER ITM WITH IDE ...

Or stick the whole lot in the print statement.
Check: DTR> help command cross example

3) If you have further questions, condsider whether you can express them in a YACHTS and OWNERS example. By doing so, we will understand you better and may be able to coble up working examples.

4) When working with RMS INDEXED files, Start datatrieve with $ DATA /DEBUG
By doing so DTR will nicely tell you whether it is using an RMS INDEX.

5) Enjoy! DTR is a fine tool. It was years ahead of its time at its time. Which made is miss the SQL boat.

Good luck!
Hein

Re: Need help on datatrieve

Bill Pedersen — Wed, 11 Nov 2009 14:53:31 GMT

Ok am a bit confused. Your result of SH SAMPLE should not show the DEFINE it should just show the contents of the procedure.

There is nothing "special" you need to do for procedures when using multiple domains.

When you defined SAMPLE did you get the DFN> prompts?

Can you EDIT SAMPLE? If so what happens.

Re: Need help on datatrieve

vdya — Thu, 12 Nov 2009 10:47:13 GMT

Thanks alot Hein & Bill.

Now its working fine after redefine.
My procedure name is expiry_date_rec.dtr When i execute procedure first time its creating file called expiry_date_prc.dtr.
Later its giving expected output.

I have 2 records for example yacht_rec and owner_rec and domains defined for these records are yacht and owner respectively. Also RMS files are yachts.dat and owners.dat.

Now i have filtered few fields from both records and combining those records using procedure(using cross and over keywords) owner_info.

Now i want to write/store output in new DAT file. For records we can define file using domain name as below

DTR>define file for yacht
DTR>ready yacht write
I hope its correct and will overwrite exisiting dat file.
But how to create dat file and and write output for procedures?

Thanks & Regards,
Vidya

Re: Need help on datatrieve

vdya — Mon, 16 Nov 2009 06:18:31 GMT

Is there any way to create new DAT file to write output from procedure when we collect record from 2 different DAT files?

Regards,
Vidya

Re: Need help on datatrieve

Hein van den Heuvel — Mon, 16 Nov 2009 06:23:34 GMT

Easy...

DTR> help comm define_file

DTR> FINISH ALL
DTR> DEFINE FILE FOR
DTR> READY EXCLUSIVE WRITE

Hein.

Re: Need help on datatrieve

vdya — Mon, 16 Nov 2009 11:11:28 GMT

But how to define domain for procedure?

For record its

DOMAIN EM USING EMPDEF ON WMS$SEC:EMP.DAT;

where EM domain name, EMPDEF record name and WMS$SEC:EMP.DAT; is complete path for DAT file.

Is there any way to define domain for procedure since i have combined output from 2 different records

Thanks & Regards,
Vidya

Re: Need help on datatrieve

Hein van den Heuvel — Mon, 16 Nov 2009 12:28:23 GMT

>> But how to define domain for procedure?

Huh? Language problem!

>> For record its
>> DOMAIN EM USING EMPDEF ON WMS$SEC:EMP.DAT;

That's just 1 domain.
So is that there the desired OUTPUT domain?
Or is that 1 of the 2 inputs?
Or is that 1 domain, used twice for input crossed with itself?

>> Is there any way to define domain for procedure since i have combined output from 2 different records

Do you just want the output from a procedure to go into a file?

Check out DTR> HELP COMMAND ON
or DTR COMMAND PRINT ... and look for ON clause.

You can also combine record from multiple input domains into a single output domain and have the procedure store data in that output.

Three main methods

DEFINE FILE FOR EM;
READY EM EXCLUSIVE WRITE;
EM = IN_1 CROSS IN_2 OVER WHERE ...

or SINGLE loop with CROSS and IF-THEN-ELSEs as needed

DEFINE FILE FOR EM;
READY EM EXCLUSIVE WRITE;
FOR N_1 CROSS IN_2 OVER WHERE ...
STORE EM USING
BEGIN
EM_xxx = IN_yyy...
:
END

or NESTED LOOPS with IF-THEN-ELSEs as needed

FOR DEFINE FILE FOR EM;
READY EM EXCLUSIVE WRITE;
FOR N_1 WHERE ...
BEGIN
:
FOR IN_2 WHERE ...
BEGIN
:
STORE EM USING
BEGIN
EM_xxx = IN_yyy...
:
END
:
END
:
END

Thanks & Regards,
Hein.

Re: Need help on datatrieve

vdya — Tue, 17 Nov 2009 13:16:21 GMT

Thanks Hein.

I just want the output from a procedure to go into a file.
I tried with DTR>print on file_spec

Its working fine. It created specified file and dumped data into that.

>>That's just 1 domain.
>>So is that there the desired OUTPUT domain?
>>Or is that 1 of the 2 inputs?
>>Or is that 1 domain, used twice for input crossed with itself?

Its one of the 2 inputs. There are 2 domains IM and IN.

My procedure looks like

REDEFINE PROCEDURE EXPIRY_REC
READY IM SHARED
READY IN SHARED
FIND IM WITH IDE=70 OR IDE=82
FIND CURRENT CROSS IN OVER ITM
PRINT CURRENT ON EXPIRY_REC.DAT
END_PROCEDURE

Let me try with methods you mentioned.

Thanks & Regards,
Vidya

Re: Need help on datatrieve

Hein van den Heuvel — Tue, 17 Nov 2009 16:47:29 GMT

Sounds good.

>> FIND IM WITH IDE=70 OR IDE=82
>> FIND CURRENT CROSS IN OVER ITM
>> PRINT CURRENT ON EXPIRY_REC.DAT

And that's just fine for modest data volume like 10,000 records or so.

You should be able to do this all in one command:

PRINT IM CROSS IN OVER ITM WITH IDE=70 OR IDE=82 ON EXPIRY_REC.DAT

Collections like 'CURRENT' are convenients, but slow down the results when processing large amounts of records (50,000+ ?).
They make Datatrieve re-read data.

Hein