Pick / MultiValue

Pick (also known as MultiValue or PICK) is a unique database management system with a multi-valued data model that allows multiple values in a single field. Variants include D3, UniVerse, UniData, jBASE, and OpenQM.

What You Can Upload

• ACCOUNT-SAVE account backups, read directly. No export needed
• Delimited text exports (LIST filename TO file.txt), tab, pipe or comma
• CSV exports from a Pick environment
• ZIP archive with complete file sets

The ACCOUNT-SAVE reader is verified against OpenQM and ScarletDME saves. Other Pick implementations (D3, mvBase, R83, jBASE) share the same overall design but have not been tested, so the report flags when a save file was read and asks you to check the output. UniVerse and UniData .uvb backups are a different format and are not read; export those with LIST instead.

A text export's header row names its columns. In an ACCOUNT-SAVE, each file's dictionary names the attributes it describes; the rest come out as ATTR_1, ATTR_2 and so on, after the item ID.

What You Get Out

DataMeans converts your data into multiple modern formats:

How to Export / Obtain Files

1. For a whole account, make an ACCOUNT-SAVE backup and upload it directly
2. Or export each file to delimited text: LIST filename TO file.txt
3. Include a header row of field names in a text export
4. Transfer the files to your computer via FTP (binary mode for a save)
5. Create a ZIP archive containing the files, or upload a single file directly

Supported Features

• ACCOUNT-SAVE backups read directly, with no running Pick system
• Multi-value and sub-value fields kept as lists (arrays in JSON; joined with | in CSV and Excel)
• Column names from each file's dictionary (ACCOUNT-SAVE) or the header row (text exports)
• Conversion codes (such as MD2 or D2/) shown with each column

Known Limitations

• Conversion codes are shown but not applied: an ACCOUNT-SAVE's numbers and dates come out as stored
• Multi-values stay in one column rather than separate tables
• Correlatives and I-type dictionary items are not evaluated
• Some Pick BASIC logic may need manual migration

Troubleshooting

Note on the Excel formats. An .xlsx sheet holds at most 1,048,575 rows of data and 16,384 columns, and a legacy .xls sheet 65,535 rows and 256 columns. Rather than ship a file that looks complete but is silently cut short, DataMeans leaves a table out of any Excel format it does not fit and notes it in the job report. The .xlsx copy is also left out for tables of more than 8,000,000 cells (1,048,575 rows of 12 columns is 12.6 million), which take too long and too much memory to write, and the .xls copy for tables of more than 150,000 cells, which the legacy format takes far too long to write. The CSV, JSON and SQL outputs always contain every row.

Overview

Pick (also known as MultiValue) is a post-relational database model that predates SQL and relational databases. It uses a hierarchical, multi-valued data structure where each field can contain multiple values, reducing the need for complex joins. The Pick system includes both an operating system and database, with implementations from various vendors using hash-based file storage.

History and Background

• 1965: Don Nelson and Dick Pick develop GIRLS (Generalized Information Retrieval Language System) at TRW for U.S. Army helicopter parts tracking.
• 1973: Microdata releases Reality, the first commercial implementation of the Pick system.
• Mid-1970s: Ken Simms creates Data/BASIC, the procedural programming language of Pick systems.
• c. 1978: The Ultimate Corp develops the second Pick implementation, delivered as firmware on a Honeywell Level 6 machine.
• 1979: Devcom, a Microdata reseller, writes INFORMATION in FORTRAN and assembler for Prime Computer 50-series systems; sold to Prime Computer, it becomes Prime INFORMATION.
• 1980s: Popular in business applications, especially in vertical markets.
• 1981: McDonnell Douglas buys Microdata.
• 1990s: New Pick-compatible implementations appear, including jBASE (1991) and D3 (1997).
• 2001: IBM acquires the database division of Informix, including the UniVerse and UniData (U2) products.
• 2005: InterSystems announces MultiValue extensions for its Caché database.
• 2010s: Rocket Software acquires the U2 family from IBM and the D3 and mvBase products from TigerLogic (2014).
• 2021: Rocket Software acquires the OpenQM and jBASE products.

File Format Specifications

Hash File System:

• Files stored as hash tables with fixed-size buckets
• Primary keys hashed to determine storage location
• Group assignment by remainder: the hashed key is divided by the modulo (group count) and the remainder selects the group
• Modulo values chosen as prime numbers for even record distribution; D3 additionally avoids multiples of 2 and 5
• Bucket sizing is vendor-specific: UniVerse separation sets group buffers in 512-byte units, while D3 uses uniform 4096-byte frames
• Collision resolution through overflow chains
• File extension typically none (files named by application)

MultiValue Structure:

• Records contain fields (attributes) with multiple values
• Item marks (ASCII 255) delimit complete records (items)
• Attributes separated by attribute marks (ASCII 254)
• Values separated by value marks (ASCII 253)
• Subvalues within values separated by subvalue marks (ASCII 252)
• Text marks (ASCII 251) for formatting
• No fixed record length - dynamic sizing

File Types:

• Data files: Primary storage for records
• Dictionary files: Metadata and field definitions
• Overflow areas: Handle hash collisions and oversized records
• Transaction logs: For recovery and replication
• UniVerse numbered file types: nonhashed (1, 19), static hashed (2-18, each tuned to different record-ID patterns), B-tree (25), dynamic (30)
• Dynamic (type 30) files resize automatically, splitting groups above a default 80% load and merging below 50%

Data Types and Structures

Pick treats all data as character strings, with no explicit data typing:

MultiValue Characteristics:

• One-to-many relationships stored within a single record
• Fewer join operations than normalized relational schemas
• Sparse data stored without placeholder fields
• Records are not constrained by a fixed schema
• Sorting and selection handled by the query language

Query Language:

• English-like syntax (LIST, SORT, SELECT), originally named ENGLISH and later ACCESS
• Retrieval and reporting without SQL
• Dictionary-driven reporting
• Procedural programming in Pick BASIC

Version Differences

Compatibility Notes:

• All implementations share core MultiValue concepts
• Delimiter conventions are shared, but on-disk file formats are vendor-specific
• Item size handling varies: D3 moves items larger than 1720 bytes entirely into overflow frames via pointer items, while UniVerse documentation recommends nonhashed files for records over 2000 bytes
• UniVerse BASIC exposes the delimiters as the system variables @FM, @VM, and @SM
• Some extensions are vendor-specific
• Migration tools available between implementations
• Later versions add SQL interfaces and web services support

Technical References

• Wikipedia: Pick operating system
• Wikipedia: MultiValue database
• History of Pick Reality (NEC Software Solutions)
• Pickwiki: MultiValue database wiki
• Pick is a living fossil of computer history