Difference between revisions of "RMS Data Files"

From Recital Documentation Wiki
Jump to: navigation, search
 
(4 intermediate revisions by one user not shown)
Line 1: Line 1:
{{YLM to tidy up}}
+
On OpenVMS, Recital Terminal Developer and the Recital Database Server support access to the following fixed length RMS File types:
=Accessing RMS Data Files=
+
  
On OpenVMS, Recital Terminal Developer and the Recital Database and Mirage Servers support access to the following fixed length RMS File types:
 
  
 
* RMS Sequential
 
* RMS Sequential
 
* RMS Indexed Sequential
 
* RMS Indexed Sequential
 
* RMS Relative
 
* RMS Relative
 +
  
 
Data access is achieved through an RMS Bridge.  This requires the creation of a Bridge file and an empty Recital table that has the same structure as the RMS file.
 
Data access is achieved through an RMS Bridge.  This requires the creation of a Bridge file and an empty Recital table that has the same structure as the RMS file.
  
Creating the Recital Table
+
 
 +
==See Also==
 +
[[ALTER TABLE]], [[CREATE]], [[CREATE BRIDGE]], [[SQL CREATE BRIDGE|CREATE BRIDGE (SQL)]], [[CREATE TABLE]], [[DISPLAY STRUCTURE]],  [[LIST STRUCTURE]], [[MODIFY STRUCTURE]], [[SQL SELECT|SELECT]], [[USE]]
 +
 
 +
 
 +
==Creating the Recital Table==
 
Create a Recital table with the same structure as the RMS file.  The fields/columns in the structure file must exactly match the data type and length of those in the RMS file.  The Recital table will have one byte more in total record length due to the Recital record deletion marker.
 
Create a Recital table with the same structure as the RMS file.  The fields/columns in the structure file must exactly match the data type and length of those in the RMS file.  The Recital table will have one byte more in total record length due to the Recital record deletion marker.
  
To create the table, use the SQL CREATE TABLE command or the Recital Terminal Developer for OpenVMS CREATE worksurface.  The table should be given a ’.str’ file extension (rather than the default ’.dbf’) to signify that this is a structure file only.
+
To create the table, use the [[CREATE TABLE|SQL CREATE TABLE]] command or the Recital Terminal Developer for OpenVMS [[CREATE]] worksurface.  The table should be given a ’.str’ file extension (rather than the default ’.dbf’) to signify that this is a structure file only.
  
 
Please see the end of this document for information on accessing VAX COBOL data types.
 
Please see the end of this document for information on accessing VAX COBOL data types.
  
Creating the Bridge File
 
In Recital Terminal Developer for OpenVMS, the Bridge File can be created using the CREATE BRIDGE worksurface.  For Recital Database and Mirage Server clients, the Bridge File can be created in two ways: by using an ’ini’ file, or by the SQL CREATE BRIDGE command.
 
  
Maximums Widths
+
==Creating the Bridge File==
 +
In Recital Terminal Developer for OpenVMS, the Bridge File can be created using the [[CREATE BRIDGE]] worksurface.  For Recital Server clients, the Bridge File can be created in two ways: by using an ’ini’ file, or by the [[SQL CREATE BRIDGE]] command.
 +
 
 +
 
 +
====Maximums Widths====
 
The following maximum widths apply to the bridge elements:
 
The following maximum widths apply to the bridge elements:
  
{|
+
 
|Element||Maximum Width in Characters||Description
+
{| class="wikitable"
 +
!Element||Maximum Width in Characters||Description
 
|-
 
|-
 
|Type||10||Bridge type: RMSSEQ, RMSIDX, RMSREL
 
|Type||10||Bridge type: RMSSEQ, RMSIDX, RMSREL
Line 38: Line 45:
 
|}
 
|}
  
CREATE BRIDGE (SQL)
 
The CREATE BRIDGE SQL command defines and creates the bridge in one step:
 
  
e.g.
+
====CREATE BRIDGE (SQL)====
 +
The [[SQL CREATE BRIDGE|CREATE BRIDGE]] SQL command defines and creates the bridge in one step:
 +
 
 +
 
 +
<code lang="recital">
 
exec sql
 
exec sql
 
CREATE BRIDGE rmsseqdemo.dbf  
 
CREATE BRIDGE rmsseqdemo.dbf  
Line 48: Line 57:
 
METADATA "rmsseqdemo.str"
 
METADATA "rmsseqdemo.str"
 
ALIAS "rmsseqdemo";
 
ALIAS "rmsseqdemo";
 +
</code>
 +
  
 
or  
 
or  
  
 +
 +
<code lang="recital">
 
exec sql
 
exec sql
 
CREATE BRIDGE rmsseqdemo.dbf
 
CREATE BRIDGE rmsseqdemo.dbf
 
AS "type=RMSSEQ;external=rmsseq.dat;metadata=rmsseqdemo.str;alias=rmsseqdemo";
 
AS "type=RMSSEQ;external=rmsseq.dat;metadata=rmsseqdemo.str;alias=rmsseqdemo";
 +
</code>
 +
  
 
For RMS Indexed Sequential files, the RMS index keys to be used can also be included in the bridge definition.  Up to 7 different keys may be specified:
 
For RMS Indexed Sequential files, the RMS index keys to be used can also be included in the bridge definition.  Up to 7 different keys may be specified:
  
e.g.
+
 
 +
<code lang="recital">
 
exec sql
 
exec sql
 
CREATE BRIDGE rmsidxdemo.dbf  
 
CREATE BRIDGE rmsidxdemo.dbf  
Line 65: Line 81:
 
ALIAS "rmsidxdemo"
 
ALIAS "rmsidxdemo"
 
INDEX "acc_prefix+acc_no,acc_prefix+str(ord_total)";
 
INDEX "acc_prefix+acc_no,acc_prefix+str(ord_total)";
 +
</code>
 +
  
or
+
<code lang="recital">
 
+
 
exec sql
 
exec sql
 
CREATE BRIDGE rmsidxdemo.dbf
 
CREATE BRIDGE rmsidxdemo.dbf
 
AS "type=RMSIDX;external=rmsidx.dat;metadata=rmsidxdemo.str;alias=rmsidxdemo;;
 
AS "type=RMSIDX;external=rmsidx.dat;metadata=rmsidxdemo.str;alias=rmsidxdemo;;
 
indexkey1=acc_prefix+acc_no;indexkey2=acc_prefix+str(ord_total)";
 
indexkey1=acc_prefix+acc_no;indexkey2=acc_prefix+str(ord_total)";
 +
</code>
 +
  
 
For RMS Sequential and RMS Relative files, up to 7 Recital single indexes can be built and associated with the bridge.
 
For RMS Sequential and RMS Relative files, up to 7 Recital single indexes can be built and associated with the bridge.
  
e.g.
+
 
 +
<code lang="recital">
 
exec sql
 
exec sql
 
CREATE BRIDGE rmsreldemo.dbf  
 
CREATE BRIDGE rmsreldemo.dbf  
Line 83: Line 103:
 
ALIAS "rmsreldemo"
 
ALIAS "rmsreldemo"
 
INDEX "ind1.ndx,ind2.ndx,ind3.ndx";
 
INDEX "ind1.ndx,ind2.ndx,ind3.ndx";
 +
</code>
  
or
 
  
 +
<code lang="recital">
 
exec sql
 
exec sql
 
CREATE BRIDGE rmsreldemo.dbf
 
CREATE BRIDGE rmsreldemo.dbf
 
AS "type=RMSREL;external=rmsrel.dat;metadata=rmsreldemo.str;alias=rmsreldemo;;
 
AS "type=RMSREL;external=rmsrel.dat;metadata=rmsreldemo.str;alias=rmsreldemo;;
 
indexkey1=ind1.ndx;indexkey2=ind2.ndx,indexkey3=ind3.ndx";
 
indexkey1=ind1.ndx;indexkey2=ind2.ndx,indexkey3=ind3.ndx";
 +
</code>
  
CREATE BRIDGE FROM <ini>
 
  
 +
====CREATE BRIDGE FROM <ini>====
 
Firstly, an ’ini’ file should be created on the server in the data directory where the external data file is held.  The ini file has the following contents:
 
Firstly, an ’ini’ file should be created on the server in the data directory where the external data file is held.  The ini file has the following contents:
  
 +
 +
<pre>
 
[bridge]
 
[bridge]
 
bridgetype=<bridgetype>
 
bridgetype=<bridgetype>
Line 107: Line 131:
 
indexkey6=<optional RMS index key or Recital index filename>
 
indexkey6=<optional RMS index key or Recital index filename>
 
indexkey7=<optional RMS index key or Recital index filename>
 
indexkey7=<optional RMS index key or Recital index filename>
 +
</pre>
 +
 +
 +
rmsreldemo.ini
  
e.g. rmsreldemo.ini
 
  
 +
<pre>
 
[bridge]
 
[bridge]
 
bridgetype=RMSREL
 
bridgetype=RMSREL
Line 117: Line 145:
 
indexkey1=ind1.ndx
 
indexkey1=ind1.ndx
 
indexkey2=ind2.ndx
 
indexkey2=ind2.ndx
 +
</pre>
  
e.g. rmsidxdemo.ini
 
  
 +
rmsidxdemo.ini
 +
 +
 +
<pre>
 
[bridge]
 
[bridge]
 
bridgetype=RMSIDX
 
bridgetype=RMSIDX
Line 127: Line 159:
 
indexkey1=acc_prefix+acc_no
 
indexkey1=acc_prefix+acc_no
 
indexkey2=acc_prefix
 
indexkey2=acc_prefix
 +
</pre>
  
NOTE: Recital Terminal Developer users can use the MODIFY BRIDGE to add in details of newly built Recital indexes.  In client/server environments the SQL CREATE BRIDGE or 4GL CREATE BRIDGE FROM <ini> command needs to be reissued.
 
  
Then the CREATE BRIDGE command should be issued:
+
NOTE: Recital Terminal Developer users can use the [[MODIFY BRIDGE]] to add in details of newly built Recital indexes.  In client/server environments the [[SQL CREATE BRIDGE]] or 4GL [[CREATE BRIDGE|CREATE BRIDGE FROM <ini>]] command needs to be reissued.
  
 +
Then the [[CREATE BRIDGE]] command should be issued:
 +
 +
 +
<code lang="recital">
 
create bridge rmsidxdemo.dbf from rmsidxdemo
 
create bridge rmsidxdemo.dbf from rmsidxdemo
 +
</code>
 +
  
Using the Bridge
+
==Using the Bridge==
 
The Bridge can now be used.  To access the RMS file, use the ’alias’ specified in the Bridge definition.
 
The Bridge can now be used.  To access the RMS file, use the ’alias’ specified in the Bridge definition.
  
e.g.
 
  
 +
<code lang="recital">
 
Select * from rmsseqdemo
 
Select * from rmsseqdemo
 +
</code>
  
Accessing VAX COBOL Data Types
+
 
 +
==Accessing VAX COBOL Data Types==
 
The following table provides details of the COBOL data types that can be directly accessed by RECITAL using the RECITAL RMS Bridge.
 
The following table provides details of the COBOL data types that can be directly accessed by RECITAL using the RECITAL RMS Bridge.
  
{|
+
{| class="wikitable"
|COBOL Picture Clause||
+
!COBOL Picture Clause||COBOL Usage Clause||RECITAL Data type||Storage in bytes
==COBOL Usage Clause==||
+
==RECITAL Data type==||
+
==Storage in bytes==
+
 
|-
 
|-
 
|PIC 9(n)[n <=18]||USAGE IS DISPLAY||(N)umeric||n
 
|PIC 9(n)[n <=18]||USAGE IS DISPLAY||(N)umeric||n
Line 213: Line 250:
 
|-
 
|-
 
|}
 
|}
 +
  
 
NOTE:
 
NOTE:
 
The storage occupied packed decimal data types is calculated as follows:
 
The storage occupied packed decimal data types is calculated as follows:
  
 +
<pre>
 
if (n+s) is odd then storage = ((n+s)+1)/2
 
if (n+s) is odd then storage = ((n+s)+1)/2
 
else storage = ((n+s)+2)/2
 
else storage = ((n+s)+2)/2
 +
</pre>
  
 
When defining the "width" for binary data types, this value denotes the output display width.  The storage occupied by the data type is as specified above.
 
When defining the "width" for binary data types, this value denotes the output display width.  The storage occupied by the data type is as specified above.

Latest revision as of 16:23, 5 July 2011

On OpenVMS, Recital Terminal Developer and the Recital Database Server support access to the following fixed length RMS File types:


  • RMS Sequential
  • RMS Indexed Sequential
  • RMS Relative


Data access is achieved through an RMS Bridge. This requires the creation of a Bridge file and an empty Recital table that has the same structure as the RMS file.


See Also

ALTER TABLE, CREATE, CREATE BRIDGE, CREATE BRIDGE (SQL), CREATE TABLE, DISPLAY STRUCTURE, LIST STRUCTURE, MODIFY STRUCTURE, SELECT, USE


Creating the Recital Table

Create a Recital table with the same structure as the RMS file. The fields/columns in the structure file must exactly match the data type and length of those in the RMS file. The Recital table will have one byte more in total record length due to the Recital record deletion marker.

To create the table, use the SQL CREATE TABLE command or the Recital Terminal Developer for OpenVMS CREATE worksurface. The table should be given a ’.str’ file extension (rather than the default ’.dbf’) to signify that this is a structure file only.

Please see the end of this document for information on accessing VAX COBOL data types.


Creating the Bridge File

In Recital Terminal Developer for OpenVMS, the Bridge File can be created using the CREATE BRIDGE worksurface. For Recital Server clients, the Bridge File can be created in two ways: by using an ’ini’ file, or by the SQL CREATE BRIDGE command.


Maximums Widths

The following maximum widths apply to the bridge elements:


Element Maximum Width in Characters Description
Type 10 Bridge type: RMSSEQ, RMSIDX, RMSREL
External 80 External file name
Metadata 80 Recital ’structure’ table name
Alias 10 Alias name
Index 50 Index key or filename


CREATE BRIDGE (SQL)

The CREATE BRIDGE SQL command defines and creates the bridge in one step:


exec sql
CREATE BRIDGE rmsseqdemo.dbf 
TYPE "RMSSEQ"
EXTERNAL "rmsseq.dat"
METADATA "rmsseqdemo.str"
ALIAS "rmsseqdemo";


or


exec sql
CREATE BRIDGE rmsseqdemo.dbf
AS "type=RMSSEQ;external=rmsseq.dat;metadata=rmsseqdemo.str;alias=rmsseqdemo";


For RMS Indexed Sequential files, the RMS index keys to be used can also be included in the bridge definition. Up to 7 different keys may be specified:


exec sql
CREATE BRIDGE rmsidxdemo.dbf 
TYPE "RMSIDX"
EXTERNAL "rmsidx.dat"
METADATA "rmsidxdemo.str"
ALIAS "rmsidxdemo"
INDEX "acc_prefix+acc_no,acc_prefix+str(ord_total)";


exec sql
CREATE BRIDGE rmsidxdemo.dbf
AS "type=RMSIDX;external=rmsidx.dat;metadata=rmsidxdemo.str;alias=rmsidxdemo;;
indexkey1=acc_prefix+acc_no;indexkey2=acc_prefix+str(ord_total)";


For RMS Sequential and RMS Relative files, up to 7 Recital single indexes can be built and associated with the bridge.


exec sql
CREATE BRIDGE rmsreldemo.dbf 
TYPE "RMSREL"
EXTERNAL "rmsrel.dat"
METADATA "rmsreldemo.str"
ALIAS "rmsreldemo"
INDEX "ind1.ndx,ind2.ndx,ind3.ndx";


exec sql
CREATE BRIDGE rmsreldemo.dbf
AS "type=RMSREL;external=rmsrel.dat;metadata=rmsreldemo.str;alias=rmsreldemo;;
indexkey1=ind1.ndx;indexkey2=ind2.ndx,indexkey3=ind3.ndx";


CREATE BRIDGE FROM <ini>

Firstly, an ’ini’ file should be created on the server in the data directory where the external data file is held. The ini file has the following contents:


[bridge]
bridgetype=<bridgetype>
externalname=<name of the external data file>
databasename=<name of the Recital structure table>
alias=<the name to use to access your file>
indexkey1=<optional RMS index key or Recital index filename>
indexkey2=<optional RMS index key or Recital index filename>
indexkey3=<optional RMS index key or Recital index filename>
indexkey4=<optional RMS index key or Recital index filename>
indexkey5=<optional RMS index key or Recital index filename>
indexkey6=<optional RMS index key or Recital index filename>
indexkey7=<optional RMS index key or Recital index filename>


rmsreldemo.ini


[bridge]
bridgetype=RMSREL
externalname=rmsrel.dat
databasename=rmsreldemo.str
alias= rmsreldemo
indexkey1=ind1.ndx
indexkey2=ind2.ndx


rmsidxdemo.ini


[bridge]
bridgetype=RMSIDX
externalname=rmsidx.dat
databasename=rmsdemo.str
alias=rmsidxdemo
indexkey1=acc_prefix+acc_no
indexkey2=acc_prefix


NOTE: Recital Terminal Developer users can use the MODIFY BRIDGE to add in details of newly built Recital indexes. In client/server environments the SQL CREATE BRIDGE or 4GL CREATE BRIDGE FROM <ini> command needs to be reissued.

Then the CREATE BRIDGE command should be issued:


create bridge rmsidxdemo.dbf from rmsidxdemo


Using the Bridge

The Bridge can now be used. To access the RMS file, use the ’alias’ specified in the Bridge definition.


Select * from rmsseqdemo


Accessing VAX COBOL Data Types

The following table provides details of the COBOL data types that can be directly accessed by RECITAL using the RECITAL RMS Bridge.

COBOL Picture Clause COBOL Usage Clause RECITAL Data type Storage in bytes
PIC 9(n)[n <=18] USAGE IS DISPLAY (N)umeric n
PIC 9(n)[n <=18] USAGE IS COMP-3 (P)acked Variable
PIC 9(n)[n <=4] USAGE IS COMP (S)hort 2
PIC 9(n)[5 <=n <=9] USAGE IS COMP (I)nteger 4
PIC 9(n)[10 <=n <=18] USAGE IS COMP (Q)uad 8
PIC S9(n)[n <=4] USAGE IS COMP (S)hort 2
PIC S9(n)[5 <=n <=9] USAGE IS COMP (I)nteger 4
PIC S9(n)[10 <=n <=18] USAGE IS COMP (Q)uad 8
PIC S9(n)[10 <=n <=18] USAGE IS INDEX (I)nteger 4
PIC S9(n)[10 <=n <=18] USAGE IS POINTER (I)nteger 4
PIC S9(n)[10 <=n <=18] USAGE IS COMP-1 (R)eal 4
PIC S9(n)[10 <=n <=18] USAGE IS COMP-2 (F)loat 8
PIC S9(n)[n <=18] USAGE IS COMP-3 (P)acked Variable
PIC 9(n)[n <=18] USAGE IS COMP-3 (P)acked Variable
PIC X(n)[n <=254] USAGE IS DISPLAY (C)haracter n
PIC A(n)[n <=254] USAGE IS DISPLAY (C)haracter n
PIC 9(n)V9(s) USAGE IS DISPLAY (S)hort 2
PIC S9(n)V9(s)[(n+s) <=4] USAGE IS COMP (S)hort 2
PIC S9(n)V9(s)[5<=(n+s)<=9] USAGE IS COMP (I)nteger 4
PIC S9(n)V9(s)[10<=(n+s)<=18] USAGE IS COMP (Q)uad 8
PIC 9(n)V9(s)[n <=18] USAGE IS COMP-3 (P)acked Variable
PIC S9(n)V9(s)[n <=18] USAGE IS COMP-3 (P)acked Variable
PIC S9(n)[n <=18] USAGE IS DISPLAY not supported
PIC S9(n)[n <=18] USAGE IS DISPLAY SIGN IS TRAILING not supported
PIC S9(n)[n <=18] USAGE IS DISPLAY SIGN IS LEADING not supported
PIC S9(n)[n <=18] USAGE IS DISPLAY SIGN IS TRAILING SEPARATE not supported
PIC S9(n)[n <=18] USAGE IS DISPLAY SIGN IS LEADING SEPARATE not supported
PIC S9(n)V9(s)[(n+s) <=18] USAGE IS DISPLAY SIGN IS TRAILING not supported
PIC S9(n)V9(s)[(n+s) <=18] USAGE IS DISPLAY SIGN IS TRAILING not supported
PIC S9(n)V9(s)[(n+s) <=18] USAGE IS DISPLAY SIGN IS TRAILING SEPARATE not supported
PIC S9(n)V9(s)[(n+s) <=18] USAGE IS DISPLAY SIGN IS LEADING SEPARATE not supported


NOTE: The storage occupied packed decimal data types is calculated as follows:

if (n+s) is odd then storage = ((n+s)+1)/2
else storage = ((n+s)+2)/2

When defining the "width" for binary data types, this value denotes the output display width. The storage occupied by the data type is as specified above.

When defining he number of decimal places for binary data types, this value represents the "scale" of the value. When the field is referenced, RECITAL scales it down by successive divisions of 10, as specified by "scale", and evaluates all arithmetic in double precision floating point. When fields of this type are updated, then the result to be stored in the field is again re-scaled.