Multiple RDBMS Tables CA-Ramis: UNION
search cancel

Multiple RDBMS Tables CA-Ramis: UNION

book

Article ID: 56109

calendar_today

Updated On:

Products

Ramis Reporter

Issue/Introduction

This article will illustrate the RDBMS Interface feature that allows access to multiple RDBMS tables using a single CA Ramis or CA Ramis Reporter report request. 

Environment

Release: 7.4

CA Ramis
CA Ramis Reporter

Resolution

This feature utilizes dynamically generated SELECT statements with a UNION operator.

To trigger this feature, Ramis must first recognize that multiple RDBMS tables will be involved in the single request. The following conditions must be met:

  • Each RDBMS table or view to be accessed for a report or a projection in a RELATE must have a corresponding Advantage CA-Ramis file description.
  • The same Ramis filename must be assigned to each RDBMS table or view description. This means that each Ramis file description must be stored on a different Ramis database.
  • Except for the attributes that identify the creator (owner) name, table or view name, and stop value, the multiple Ramis file descriptions must be identical.

When a report request or RELATE project for multiple tables or views is run, Ramis locates the first occurrence of the file description and builds an SQL SELECT request. After building the SELECT request, Ramis searches the remaining active Ramis databases for a matching RAMEXTMASTER file description. For each matching file description, the SELECT request is repeated with a UNION operator.

The RDBMS used in this example is DB2. The Ramis RDBMS Interface is used extensively in this example. The SQL utility is used to submit the SQL to create the two test DB2 tables. The RDBMS utility is used to connect to the DB2 subsystem and to automatically create the Ramis file descriptions. In addition, this example uses MAINTAIN to load the test data into the DB2 tables, USE to concatenate the Ramis databases, and TABLE to retrieve the data. Lastly, the RDBMS utility TRACE feature is used to show the dynamic SQL SELECT request generated by the Ramis RDBMS Interface.

The entire set of processes shown in this article, from the table creations and populations to the retrieval of the data, may be performed in a single Ramis session or batch step.

Once the RDBMS data is available and the file descriptions are set up, all that is required is the USE to concatenate the multiple databases containing the individual file descriptions and the report or RELATE request.

This example assumes that three Ramis databases already exist with the RAMSQLMASTER system file, and have DD/DLBL/FILEDEF names of RAMBAS1, RAMBAS2, and RAMBAS3. This example connects to DB2 subsystem D710 and creates test DB2 tables named RAMIS.TESTT1, RAMIS.TESTT2, and RAMIS.TESTT3 in DB2 dataspace.tablespace RAMDB71.RAMTS71.

Note: The use of the Ramis DMF (MAINTAIN) utility to load the test data is only available for DB2 and SQL/DS systems. For other RDBMS systems, use another method for loading the test data.

* Use the 1st Advantage CA-Ramis database named RAMBAS1:
USE
RAMBAS1
END
* Set MODE=BRF to minimize the runtime messages and output (optional):
SET MODE=BRF
*
* Using the Advantage CA-Ramis RDBMS utility, explicitly
* connect to the desired DB2 subsystem.
*
RDBMS CONNECT DB2 SUBSYSTEM=D710
*
* Using the Advantage CA-Ramis SQL utility,
* submit the SQL DROP and CREATE for the DB2 test
* tables named RAMIS.TESTT1, RAMIS.TESTT2, and RAMIS.TESTT3:
*
SQL
DROP TABLE RAMIS.TESTT1 ;
COMMIT ;
CREATE TABLE RAMIS.TESTT1 ( COL1 INTEGER  NOT NULL,
                            COL2 CHAR(20) NOT NULL )
     IN RAMDB71.RAMTS71 ;
COMMIT ;
DROP TABLE RAMIS.TESTT2 ;
COMMIT ;
CREATE TABLE RAMIS.TESTT2 ( COL1 INTEGER  NOT NULL,
                            COL2 CHAR(20) NOT NULL )
     IN RAMDB71.RAMTS71 ;
COMMIT ;
DROP TABLE RAMIS.TESTT3 ;
COMMIT ;
CREATE TABLE RAMIS.TESTT3 ( COL1 INTEGER  NOT NULL,
                            COL2 CHAR(20) NOT NULL )
     IN RAMDB71.RAMTS71 ;
COMMIT ;
END
*
* Using the RDBMS utility, delete any existing version,
* then generate the file description for the 1st DB2 table
* on the 1st Advantage CA-Ramis database.
*
RDBMS DELETE  DB2 FILENAME=TESTTAB
RDBMS EXTRACT FILENAME=TESTTAB OWNER=RAMIS TABLE=TESTT1
*
* Using the Advantage CA-Ramis RAMDB2INDEX utility,
* show the generated file description on the 1st database:
*
RAMDB2INDEX TESTTAB
*
* Using Advantage CA-Ramis DMF (MAINTAIN) utility,
* load 2 test records into the DB2 table named
* RAMIS.TESTT1, filename TESTTAB:
*
MAINTAIN FILE 'DB2 TESTTAB'
SOURCE IS IMBEDDED
FORMAT IS FIXED
LAYOUT IS COL1/4, COL2/20
WHEN NOT FOUND
INCLUDE THE ROW
DATA
0001RECORD 1 IN TABLE1
0002RECORD 2 IN TABLE1
END
*
* Using the RDBMS utility, turn on the trace facility
* to show the dynamically generated SQL SELECT:
*
RDBMS TRACE 1
*
* Run a reporter request to show the records retrieved
* when there is only 1 occurrence of the DB2 file
* description in the currently accessed Advantage
* CA-Ramis databases:
*
TABLE
' SHOW THE RESULTS FROM ONLY THE 1ST TABLE'
FILE TESTTAB PRINT COL1 AND COL2 END
*
* Turn off tracing until the next request you wish to trace.
*
RDBMS TRACE OFF
*
* Now, use the 2nd Advantage CA-Ramis database and build the
* Advantage CA-Ramis file description for the 2nd table and
* load some test data. Follow the same steps as were done
* for the 1st table.
*
USE 1
RAMBAS2
END
RDBMS DELETE  DB2 FILENAME=TESTTAB
RDBMS EXTRACT FILENAME=TESTTAB OWNER=RAMIS TABLE=TESTT2
RAMDB2INDEX TESTTAB
MAINTAIN FILE 'DB2 TESTTAB'
SOURCE IS IMBEDDED
FORMAT IS FIXED
LAYOUT IS COL1/4, COL2/20
WHEN NOT FOUND
INCLUDE THE ROW
DATA
0001RECORD 1 IN TABLE2
0003RECORD 2 IN TABLE2
END
RDBMS TRACE 1
TABLE
' SHOW THE RESULTS FROM ONLY THE 2ND TABLE'
FILE TESTTAB PRINT COL1 AND COL2 END
RDBMS TRACE OFF
*
* Now, use the 3rd Advantage CA-Ramis database and build the
* Advantage CA-Ramis file description for the 3rd table and
* load some test data. Follow the same steps as were done
* for the 1st table.
*
USE 1
RAMBAS3
END
RDBMS DELETE  DB2 FILENAME=TESTTAB
RDBMS EXTRACT FILENAME=TESTTAB OWNER=RAMIS TABLE=TESTT3
RAMDB2INDEX TESTTAB
MAINTAIN FILE 'DB2 TESTTAB'
SOURCE IS IMBEDDED
FORMAT IS FIXED
LAYOUT IS COL1/4, COL2/20
WHEN NOT FOUND
INCLUDE THE ROW
DATA
0002RECORD 1 IN TABLE3
0004RECORD 2 IN TABLE3
END
RDBMS TRACE 1
TABLE
' SHOW THE RESULTS FROM ONLY THE 3RD TABLE'
FILE TESTTAB PRINT COL1 AND COL2 END
RDBMS TRACE OFF
*
* Now, use the three Advantage CA-Ramis databases:
*
USE
RAMBAS1
RAMBAS2
RAMBAS3
END
*
* Run a request to show that 3 file descriptions exist
* with the same filename:
*
TABLE
' SHOW THAT 3 FILENAMES EXIST'
FILE RAMEXTMASTER COUNT FILE AND FILETYPE IF FILE IS 'TESTTAB' END
*
* Show the results when reporting from 3 tables.
* Three Advantage CA-Ramis databases have file descriptions
* that meet the qualifications for generating a UNION:
*
RDBMS TRACE 1
TABLE
' SHOW THE RESULTS FROM MULTIPLE TABLES: UNION'
FILE TESTTAB PRINT COL1 AND COL2 END

 

Additional Information

For more information about the RDBMS Interface, refer to the publication RDBMS Interface via the CA Ramis Version 7.4 set of documentation at CA Ramis Bookshelves and PDFs.   

Powered by Wolken Software