UniVerse (CSS) - Getting Started

Get Connected

Install Putty
Connect to server over ssh
Login with username then password

If this is your first time accessing CSS, and this error occurs, you do not have proper permissions to access the system.

ERROR IN THE PERSONEL FILE (). NOTIFY DATABASE MANAGER IMMEDIATELY. >

Configure Putty

Settings > Terminal > Keyboard - Change Backspace to Ctrl-H

General Terms

CSS

The name for Concord’s servicing platform, literally "Concord Servicing System". Like “Phoenix” when describing the SQL based platform for servicing Marriott.

The program/system is a licenced 3rd-party product called 'UniVerse'.

MultiValue Product

When refering to MS SQL Server, we would refer to it as a 'relational' system.

In the same way we would refer to UniVerse as a MultiValue system.

Relational and MultiValue are just types of systems.

UniVerse

UniVerse is a 'MultiValue' product brand serviced by Rocket Software

UniVerse is like “MS SQL Server” or “Oracle.”

Pick

The Relational Model was created by Edgar F. Codd, and so could be called the 'Codd' model (but isn't).

MultiValue is a multidimensional data model and was first implemented by Dick Pick and so is called 'Pick'.

UniVerse Specific Terms

These terms primarily refer to how data is stored in UniVerse.

File

Analogous to a SQL table but a 'file' is an actual file stored on disk.

Attribute or Field

Attribute (or Field) – Analogous to a SQL column.

Item or Record

Item (or Record) – Analogous to a SQL row.

ID

Primary key for an item.

Value or MultiValue

Data in an attribute, possibly multiple discrete values. The attribute is populated by an array that may contain one or multiple (or none) elements.

E.g., The phone_number field might * contain a phone number or multiple phone numbers

List

It’s a generic list against which you will perform some operation. This is similar to using a map function.

SAVE-LIST - command to persist the data contained in the Active Select List
GET-LIST - command to retrieve an existing list and put the data into the Active Select List

TCL - Terminal Control Language

'Terminal Control Language' is like a command line tool for UniVerse. It can be used to query for information, start programs, and much more.

Terms ToDo

Program
Subroutine
Macro

Getting started in TCL

Once logged in UniVerse/CSS, type T to get into TCL.

# Once logged in...
# Open TCL
> T
# If this doesn't work, you may not have access

Simple a Query

Write a simple query that selects the first 10 rows from the CONTRACTS file.

DEV- SELECT CONTRACTS SAMPLE 10

The SAMPLE keyword is like SQL's LIMIT or TOP keywords

The command prompt will now say DEV+ which means there is an "Active Select List". This is symbolized with the + symbol.

Now, to display the results of the query:

DEV+ LIST CONTRACTS

You should see output similar to this:

LIST CONTRACTS 10:58:03am  09 Nov 2018  PAGE    1
CONTRACTS..

25140134309
07850034359
07850034360
07850034362
07850034363
07850034365
10351034332
07850034368
07850034370
07850034371

10 records listed.

You've now displayed the results of your first query.

Notice, the command prompt now is DEV- meaning you no longer have an Active Select List.

Storing Query Results

At times, you'll want to write a query and store the results for use later.

For example, certain programs take a saved list as input, so you could write a query to get the results you need, then save it, and start the program passing its name.

Let walk through an example.

Again, lets get a couple records from the system:

DEV- SELECT CONTRACTS SAMPLE 10

Now, with an Active Select List we can save a list, providing a name for the list:

DEV+ SAVE-LIST SOMECONTRACTS

Once executed, the Active Select List will no longer be active, but the query results will be available for retreval.

So, lets retrieve them with GET-LIST:

DEV- GET-LIST SOMECONTRACTS

We now have an Active Select List again (DEV+) and can continue to work with the query, or LIST CONTRACTS to display the results.

Summary

> SAVE-LIST <name>  # saves the previous query with a name `<name>`
                    # if the query name is already used it wil be over written

> GET-LIST <name>   # retrieves the saved search

Querying Files in Universe

How do we query UniVerse file, understand the underlying data structure, and traverse through existing data?

We'll try and address those questions here.

Concepts

Lets visualize a UniVerse file by using a JSON file to explore the concepts. In this case, each JSON object property can be zero, one or many values.

[{
  "ID": 12345678901,
  "FIRST.NAME": "John",
  "LAST.NAME": "Doe",
  "PHONE.NUMBER": [ "480-879-1234", "602-303-2312" ],
  "FAX.NUMBER": []
},{
  "ID": 23456789012,
  "FIRST.NAME": "Jane",
  "LAST.NAME": "Pie",
  "PHONE.NUMBER": [ "480-345-5443" ],
  "FAX.NUMBER": [ "602-733-0934" ]
}]

In this case there are two 'records' or 'items' in this file.

The JSON properties (FIRST.NAME, LAST.NAME, etc) are refered to as 'attributes' or 'fields'.

The values associated with the attributes ("John", "Pie", etc) are refered to as 'values'. Those with multiple values would be refered to as 'multivalues'.

Understanding File Dictionaries

Files have a defined schema. The schema is called the file's 'dictonary'.

Lets look at an existing file dictonary to understand what it consists of.

To see the details of a file's dictonary use the DICT keyword:

DEV- LIST DICT CONTRACTS

CONTRACTS is a very commonly used file.

The command will output something like this:

DICT CONTRACTS    01:58:26pm  13 Nov 2018  Page    1

Field......... Type & Field........ Conversion.. Column......... Output Depth &
Name.......... Field. Definition... Code........ Heading........ Format Assoc..
               Number

ORIG.BAL.TO.VA A    0 F;0;'*ORIG.BA MR2          ORIG BAL        10R    M
L                     L.TO.VAL';:;(
                      TOBLIG.CUSTOM
                      .DATA;X;;1)
                                                 TO VAL
BOM.DATE       A    0 A;N(DEV.CODE) D2/          BOM DATE        8R     M
                      :N(PROJ.ID):N
                      (LEND.CODE)
                      TTRANS.SUM.FI
                      LE;X;;1
DEF.FLAG       A    0 A;IF                       DF              2L     M
                      N(POOL.ID) #
                      '' THEN 'D'
                      ELSE ''
GE.APP.ID      A    0 TGE.CAPITAL;X              APP ID          4R     M
                      ;;10
ME.NPD.DIFF    A    0 A;14-N(ME.NPD              ME NPD          5R     M

Each item in the list is a 'field', similar to the idea of a column in a relational database. The column on the left is the field name.

To exit listing the columns execute:

This quits the listing action and gets you back to a prompt.

Example File Set

Now lets look at a real example of two existing files, and how to query and extract information from the files.

We'll look at two files:

the CONTRACTS file contains contracts
the CON.TRANS file contains transactions for the contracts

Lets look at a small subset of each file using a JSON representation of records/items in the file:

//CONTRACTS file
[{
  "ID": "123",
  "TRANS.IDS": ["567", "678"] //Transaction Ids, which is in dictionary position 24
},{
  "ID": "234",
  "TRANS.IDS": ["789"]
}]
//CON.TRANS file
[{
  "ID": "567",  //Transaction Ids
  "CID": "123", //Contract Id
  "DATE": "11/08/18",
  "POST.TIME": "16:59:11",
  "TOT.AMT": "-1123.34",
  "PRINC.AMT": "-3555.09"
},{
  "ID": "678",
  "CID": "123",
  "DATE": "10/06/18",
  "POST.TIME": "09:03:40",
  "TOT.AMT": "-1994.32",
  "PRINC.AMT": "-504.38"
},{
  "ID": "789",
  "CID": "234",
  "DATE": "09/15/18",
  "POST.TIME": "14:51:33",
  "TOT.AMT": "0.00",
  "PRINC.AMT": "0.00"
}]

Things to notice:

Both CONTRACTS and CON.TRANS files have unique ID attributes ('primary keys')
CONTRACTS has a multivalue attribute (column), TRANS.IDS, containing IDs for all transactions associated with the contract
CON.TRANS has an attribute (CID) which serves as a 'pointer' back to the contract its associated with

Though there are no 'foreign keys' in UniVerse, structuring contracts and transactions where each has a reference to one another allows us to 'join' (in the SQL sense) records together.

Querying a File Set

Lets use the CONTRACTS and CON.TRANS files to query data from.

Here's the senario:

Get all transactions associated with contracts "123" and "234". Return the ContractId, Date, Total Amount, and Principle Amount for each transaction.

Step #1: Get List of Contract IDs

Lets start out by getting a list of the contracts we need:

DEV- SELECT CONTRACTS WITH @ID = "123""234"

Our Active Select list now looks like:

123
234

Step #2: Get Transaction IDs for a Set of Contracts

Next we need to get all the transaction IDs from the TRANS.IDS attribute. There are two different ways to do this.

Option #1:

We can use the QSELECT keyword to access values at dictionary 'attribute number' 24, because in the CONTRACTS file TRANS.IDS exists at 'index' 24.

DEV+ QSELECT CONTRACTS (24

Our Active Select List now will contain all of the transaction IDs for the Contract IDs "123" and "234".

567
678
789

What just happened!?!

The list of contract IDs in the Active Select List were used to 'filter' the data set to contain only Contract IDs "123" and "234", then all values for attribute number (24 were returned.

The TRANS.IDS's are similar to the idea of 'foreign keys' in a SQL context, but they are not enforced by UniVerse, rather the developers have to enforce them through code.

Option #2:

There is another way to get the same result.

With QSELECT we provided a attribute number ((24), but we can also use the SELECT keyword in conjunction with the name of the attribute.

In this case, (24 represents the attribute named TRANS.IDS.

DEV+ SELECT CONTRACTS TRANS.IDS

Once executed, the Active Select List will contain the same transaction IDs as it did in the QSELECT example above.

Step #3: Validate Transaction IDs are in the Transaction File

The Active Select List now has a list of transaction IDs: 567, 678, 789 (for contracts 123, 234).

This means we have the 'primary keys' of the transactions we want, which live in the CON.TRANS file. Let's execute a SELECT query against the CON.TRANS table.

DEV+ SELECT CON.TRANS

The output is

3 record(s) selected to SELECT list #0.

What happened? What did the command do?

Nothing really. Actually, all the SELECT did was verify that the ID's (567, 678, 789) existed in the CON.TRANS file.

If instead we queried against the CONTRACTS file, we'd get a different result:

DEV+SELECT CONTRACTS

0 record(s) selected to SELECT list #0.
"567" not found.
"678" not found.
"789" not found.

This is because the items in the Active Select List (567, 678, 789) do not exist in the CONTRACTS file.

Step #4: Display Transaction Details

The last step is to display the details for each transaction. Again, remember our Active Select List contains the IDs for the transactions we want to query (567, 678, 789), so we can simply

DEV+ LIST CON.TRANS CID DATE POST.TIME TOT.AMT PRINC.AMT

Lets break that down:

LIST is the command to display results
CON.TRANS is the file name (table in SQL)
CID, DATE, POST.TIME, TOT.AMT, PRINC.AMT are all attribute names (column names in SQL)

Here's an example of the output:

LIST CON.TRANS CID DATE POST.TIME TOT.AMT PRINC.AMT 12:57:31pm  14 Nov 2018  PA
GE    1
CON_TRANS... CONTRACT... DATE.... POST TIME. AMT APPL. PRINCIPAL

         567         123 11/08/18   16:59:11  -1123.34  -3555.09
         678         123 10/06/18   09:03:40  -1994.32   -504.38
         789         234 09/15/18   14:51:33      0.00      0.00

3 records listed.

Summary

We've now selected a subset of contracts, gotten their transaction IDs, and retrieved the details for those transactions.

You can run a similar query to see the results yourself:

DEV- SELECT CONTRACTS SAMPLE 10
DEV+ SELECT CONTRACTS TRANS.IDS
DEV+ LIST CON.TRANS CID DATE POST.TIME TOT.AMT PRINC.AMT

TCL Basic Query Syntax Summary

Below is a brief description of the syntax that can be used in UniVerse.

# Frequently used commands

SELECT [FILENAME] [WITH [ATTRIBUTE.NAME] [OPERATOR] [VALUE]] [SAMPLE [COUNT]]

# Get file IDs, COUNT determines how many are returned
SELECT [FILENAME] SAMPLE [COUNT]

# Select and Filter by
SELECT [FILENAME] WITH [ATTRIBUTE.NAME] [OPERATOR] [VALUE]

# Get attribute values by attribute number 
QSELECT [FILENAME] ([ATTRIBUTE.NUMBER]

# Get attribute values by attribute Name
SELECT [FILENAME] [ATTRIBUTE.NAME]

# Get the top COUNT, similar to SAMPLE
SELECT M.TRANS ([COUNT]


# Display values for attributes requested
LIST [FILENAME] [ATTRIBUTE.NAME1] [ATTRIBUTE.NAME...]

# Same as list but can be used to order the results
SORT [FILENAME]

Operators

The following operators can be used to filter:

# Greater/Less than - 'GE', 'LT', 'EQ'(?)
SELECT CONTRACTS WITH NPD GE "10/28/2018" SAMPLE 10

Shortcut Commands

There are several commands that make using TCL a bit easier.

> WHO    # Displays who you are AND clears an Active Select List
> CLS    # Whipes the screen
> .L     # List previous commands
> .X     # execute the previous command
> .X[n]  # execute command 'n' times ago (usage: `.X3`)

File Structure

Lets understand a little more about the structure of UniVerse files. This may not be too beneficial, but may be interesting.

Delimiters

Since UniVerse data files are simply text files, they need to be encoded to retrieve data quickly.

The file encoding uses high value ASCII characters to delimit between attributes, values and subvalues.

Attributes: 254 þ Value: 253 ý SubValue: 252 ü

gregberns/universe-css-getting-started.md