File Types

What’s an Archive type file?

An Archive type file is a write-once-read-many container. Archive files sequentially store the serialized representation of table data and metadata. More than one kind of table can be stored in an Archive file: for instance, parent and child records can be co-located.

There is one important consideration. In order to reconstitute the information from an Archive, its rows must be read in the same order as they were originally written. For example, if parent and child records are clustered by parent followed by its children followed by new parent, then the Archive file must be read in that same sequence.

Archives can be examined with the same command set as any other database. For example, arfilecontains three tables, W1, W2, and W3:

1> open arch archive arfile
2> list arch
Archive file SETS

Table Name RecSize FldCount
W1     66     3
W3     122 17
W2     28     5
3> show arch w1
Archive file table: W1

Field Name Field Type Offset
LNAME ORACLE CHAR(20) 1
FNAME ORACLE CHAR(20) ALLOW NULLS 21
VAL ORACLE NUMBER ALLOW NULLS 43

After I write floating point data to a TEXT format file why does the data look wrong when inspected in the editor?

Unreadable data in a TEXT file is unexpected. TEXT files should be used only when reading or writing character data, not binary data. Binary data such as numbers and dates are not implicitly converted by Warehouse. If you need to include binary data in a TEXT file, consider using the CONVERT or STRING functions to convert all binary data to ASCII before writing it out.

How do I OPEN an MPE/iX KSAM file?

You can OPEN the KSAM file as a Warehouse TEXT type. For example, opening a local KSAM filea.b.c read-only:
OPEN handle TEXT a.b.c mode=r

If you should see the following error when running a script that reads KSAM:

1> OPEN db REMOTE homer USER=qa/qa &
2> TEXT UCX01.QA.QA mode=r
Finding homer.taurus.com… Trying 66.47.128.44
Connected to: Warehouse 2.07.3530-M on homer
Unable to open file ‘UCX01.QA.QA’ using mode ‘r’:
“Dynamic LOCKING was specified on a previous OPEN of this file, and dynamic LOCKING was not spec…” (WHERR 16021) 

then KSAM is telling us that it requires a file equation in the job stream before the execution of the Warehouse script. This is a requirement if an OPEN wants to access a KSAM file that has been previously defined as multi-job/user enabled, even if this particular OPEN is not interested in modifying the KSAM file.

The file equation would take the form:;LOCK. Check your MPE/iX documentation for details associated with this equation.

How can I read a sorted CSV type file?

There are two approaches you can take. First, you can sort the file externally using a tool like Excel or the platform’s sort utility. Or else you can use a FORMAT statement in the READ… ORDER BY statement to have Warehouse return the CSV file in a sorted order.

This second approach requires that the FILENAMES attribute not be used in the CSV OPEN statement. For example, the CSV file below is ordered by empid and the READ returns rows sorted bylastname and firstname:

1>
2>  open db csv csvfile.csv mode=r
3>
4>  format afmt: record
   1 ->      empid: char(6)
   7 ->      firstname: char(16)
  23 ->      lastname: char(16)
  39 ->  end
9>
10>  read x = db format afmt order by lastname, firstname
Using serial read
11>    print x
12>  endread
13>

6      dino             flintstone
1      fred             flintstone
3      wilma            flintstone
5      bambam           rubble
2      barney           rubble
4      betty            rubble