Multiple RDBMS Tables in Advantage CA-Ramis: UNION

Document ID : KB000056109
Last Modified Date : 14/02/2018
Show Technical Document Details

This article will illustrate the RDBMS Interface feature that allows access to multiple RDBMS tables using a single Advantage CA-Ramis report request. This feature utilizes dynamically generated SELECT statements with a UNION operator.

To trigger this feature, Advantage CA-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 Advantage CA-Ramis filename must be assigned to each RDBMS table or view description. This means that each Advantage CA-Ramis file description must be stored on a different Advantage CA-Ramis database.


  • Except for the attributes that identify the creator (owner) name, table or view name, and stop value, the multiple Advantage CA-Ramis file descriptions must be identical.

When a report request or RELATE project for multiple tables or views is run, Advantage CA-Ramis locates the first occurrence of the file description and builds an SQL SELECT request. After building the SELECT request, Advantage CA-Ramis searches the remaining active Advantage CA-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 Advantage CA-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 Advantage CA-Ramis file descriptions. In addition, this example uses MAINTAIN to load the test data into the DB2 tables, USE to concatenate the Advantage CA-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 Advantage CA-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 Advantage CA-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 Advantage CA-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 Advantage CA-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

Here is the above set of RAMSYSIN statements:

Ramsysin.txt

Here is the output, the Advantage CA-Ramis RAMPRINT file that resulted when running the above statements in OS batch:

Ramprint.txt

Read More About It

For more information about the Advantage CA-Ramis RDBMS Interface, refer to the publication Advantage CA-Ramis RDBMS Interface. For the full set of Advantage CA-Ramis documentation and additional information, visit the Product News page of supportconnect.ca.com at http://supportconnectw.ca.com/public/app_dev/ca_ramis/ramis_supp.asp.

Contribute Your Ideas

If you have a tried and true technique or experience that you would like to share with the Advantage CA-Ramis community as a published article, please contact Computer Associates Support.