UniVerse External Database Access (EDA) with I-type field dictionary items against SQL Server

Overview

External Database Access (EDA) enables you to convert data stored in Rocket UniVerse and Unidata databases to a first normal form (1NF) database, such as Microsoft SQL Server, then access that data using existing UniVerse BASIC programs, RetrieVe, or UniData/UniVerse SQL. For the UniVerse I-type fields, they can be mapped in many ways to SQL Server. This post will expand the mapping section of the External Database Access manual. Some additional mapping for I-type field dictionary items will be supported in UV 11.3.1.6018 or later version.

In this post, you will learn how to work with the UniVerse EDA solution with I-type field dictionary items. I will provide you with sample steps for each of the different I-type field conversions against SQL Server.

Note

You must order an additional UniVerse EDA package license to work on this solution. The EDA license information could be verified using the “uvregen -z” command.

Requirements

Rocket MultiValue product Version
UniVerse 11.3.1.6018 (4/26/2018) or later
UniVerse EDA license Yes
EDA Schema Manager 4.33.1

(U2 DB Tools – 4.3.0.2720)

 

Microsoft Version
SQL Server or SQL Server Express 2012 or higher

The UniVerse I-type field used to be mapped to SQL Server View only. On newer UniVerse versions, it can be mapped to a data type field of a SQL Server table that could be easily consumed by SQL Server tables .

EDA schema I-type field mapping examples for SQL Server

  • EXPRESSION: used for I-descriptors. Enter the SQL expression for the I-descriptor attribute in the Expr Body field, such as FNAME + ‘ ‘ + LNAME.

If you choose the EXPRESSION type mapping, the FULLNAME I-type field will be part of the CUSTOMER.CUSTOMER_V view.

If you choose the DATA type mapping, the FULLNAME I-type field will be part of the CUSTOMER.CUSTOMER table. 

Another example is to convert the last name to upper case using the EXPRESSION type. The UPCASE_LNAME field is part of the CUSTOMER.CUSTOMER_V view. Assume you have the following I-descriptor attribute defined for the CUSTOMER file in your UniVerse database:

:AE DICT CUSTOMER UPCASE.LNAME
Top of “UPCASE.LNAME” in “DICT STUDENT”, 6 lines, 25 characters.
001: I
002: UPCASE(LNAME)
003:
004:
005: 30L
006: S
This I-descriptor converts the customer’s last name to uppercase.

If it is mapped to the DATA type, the UPCASE_LNAME field will be part of the CUSTOMER.CUSTOMER table. 

  • TRANS functionThis example illustrates the TRANS function, used for virtual attributes/I-descriptors containing a TRANS clause. 

The TRANS field can be mapped to View all or part of a table filed on latest UniVerse version.

Assume you have the following I-descriptor attribute defined for the CUSTOMER file in your UniVerse database:

:AE DICT CUSTOMER STATENAME
Top of “STATENAME” in “DICT CUSTOMER”, 20 lines, 672 characters.
001: I State full name
002: TRANS(STATES,STATE,NAME,”C”)
003:
004: State name
005: 14T
006: S
This I-descriptor retrieves the state name from the STATES file.

When it is using the TRANS type to create a view, the STATES file must be converted to an EDA file first. The new STATES.STATES table should be created in the same SQL Server database. The STATENAME I-type field will look up the state name from the STATES.STATES table.

In the STATENAME attribute details, it should input the following information. 

1. To convert this virtual attribute to a TRANS function using the EDA Tool, drag the STATENAME from the U2 File Dictionary pane under the CUSTOMER node of CUSTOMER in the EDA Map Schema pane, as shown in the following example: 

This I-descriptor executes a translate from the STATES file to the CUSTOMER file and returns
STATENAME.

2. In the Attribute Details pane, change the Type to TRANS.
3. In the Data Type field, define the data type for the output.
4. In the Reference field, enter the name of the external table that contains the STATENAME information.
In this example, the information resides in STATES.STATES/NAME.

5. In the Parameters field, click the plus sign (+) and enter the field to pass to the TRANS
In this example, STATE is passed to the TRANS function.

The STATENAME field is part of the CUSTOMER.CUSTOMER_V view.

If it is mapped to the DATA type, the STATENAME field will be part of the CUSTOMER.CUSTOMER table. 

  • SUBR functionThis example illustrates the SUBR function, used for virtual attributes/I-descriptors containing a SUBR clause. 

The SUBR I-type field can only be mapped to the part of the table filed on the latest UniVerse version. The “*HS.DSICNT” subroutine has been cataloged on the UniVerse server.

Assume you have the following I-descriptor attribute defined for the CUSTOMER file in your UniVerse database:

Top of “DISCOUNT” in “DICT CUSTOMER”, 20 lines, 920 characters.
001: I Percent discount
002: SUBR(“*HS.DISCNT”,LIST_PRICE,PRICE)
003: MD1
004: Discount
005: 6R
006: M
007: ORDERS

You must map this SUBR I-type field to the DATA type; the DISCOUNT field will be part of the CUSTOMER.ORDERS_MV table. The DISCOUNT field is part of the ORDERS associated fields, so you need to drag that field into the ORDERS_MV object.

  • EXTRACT and FIELD functionThis example illustrates the EXTRACT and FIELD function, used for virtual attributes/I-descriptors containing aEXTRACT or FIELD clause. 

These two I-type fields can only be mapped to the DATA type filed on the latest UniVerse version.

Assume you have the following I-descriptor attribute defined for the CUSTOMER file in your UniVerse database:

16 ED DICT CUSTOMER GET_FIRST_PRODID
This is a Type “I” Descriptor last compiled on 05/01/18 at 12:17.
0001: I
0002: EXTRACT(PRODID,1,1)
0003:
0004: FIRST PRODID
0005: 10L
0006: S

16 ED DICT CUSTOMER GET_FIRST_PRODID
This is a Type “I” Descriptor last compiled on 05/01/18 at 12:17.
0001: I
0002: FIELD(PRODID,@VM,1)
0003:
0004: FIRST PRODID
0005: 10L
0006: S

  • OCONV functionThis example illustrates the OCONV function, used for virtual attributes/I-descriptors containing aOCONV clause. 

This I-type field can only be mapped to the DATA type filed on latest UniVerse version. 

The following CREATE_DATETIMEX_UV basic program will create a sample file with the OCONV I-type field.

FILENAME='DATETIMEX'
CMD='CREATE.FILE':FILENAME:'211'
EXECUTE CMD
SLEEP 3
OPEN"DICT",FILENAME TO FILE1 ELSE STOP"CAN NOT OPEN THE DICTIONARY FILE"
DATAX="D":@FM:"1":@FM:"D4/":@FM:"T_DATE":@FM:"10R":@FM:"S"
WRITE DATAX TO FILE1,"T_DATE"
DATAX="D":@FM:"2":@FM:"MTH":@FM:"T_TIME":@FM:"7R":@FM:"S"
WRITE DATAX TO FILE1,"T_TIME"
DATAX="I":@FM:"OCONV(T_DATE,'D-YMD'):'':OCONV(T_TIME,'MTS')":@FM:"":@FM:"Date & time":@FM:"19R":@FM:"S"
WRITE DATAX TO FILE1,"T_DATETIME"
DATAX="PH":@FM:"@ID T_DATE T_TIME T_DATETIME"
WRITE DATAX TO FILE1,"@"
DATAX="PH":@FM:"@ID T_DATE T_TIME T_DATETIME"
WRITE DATAX TO FILE1,"@SELECT"
CLOSE FILE1

OPEN FILENAME TO FILE2 ELSE STOP “CAN NOT OPEN THE”:FILENAME:”FILE”
DATAX = “17480”:@FM:”67512″
WRITE DATAX TO FILE2, “A0001”
DATAX = “17482”:@FM:”67612″
WRITE DATAX TO FILE2, “80001”
CLOSE FILE2

END

The following CREATE_DATETIMEX_UV basic program will create a sample file with the OCONV I-type field.

Here is the sample T_DATETIME OCONV I-type field.

>CT DICT DATETIMEX T_DATETIME

T_DATETIME
0001 I
0002 OCONV(T_DATE,’D-YMD’):’ ‘:OCONV(T_TIME,’MTS’)
0003
0004 Date & Time
0005 19R
0006 S

You will create a new DATETIMEX schema using DATA type and convert it to SQL Server

Paul Chang 5 Posts

Paul Chang is a Senior Technical Support Engineer at Rocket Software. He is responsible for supporting all U2 common middleware client products, Java products and .NET clients. He has 35 years of experience in computing and holds a master’s degree in Computer Science with an emphasis on Relational Database Design.

0 Comments

Leave a Comment

Your email address will not be published. Required fields are marked *